MySQL的最左前缀原则

举报
上善若水. 发表于 2022/07/24 23:29:09 2022/07/24
【摘要】 MySQL的最左前缀原则

讲一讲MySQL的最左前缀原则?
最左前缀原则就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

讲一讲前缀索引?
因为可能我们索引的字段非常长,这既占内存空间,也不利于维护。所以我们就想,如果只把很长字段的前面的公共部分作为一个索引,就会产生超级加倍的效果。但是,我们需要注意,order by不支持前缀索引 。

流程是:

先计算完整列的选择性 :select count(distinct col_1)/count(1) from table_1

再计算不同前缀长度的选择性 :select count(distinct left(col_1,4))/count(1) from table_1

找到最优长度之后,创建前缀索引 :create index idx_front on table_1 (col_1(4))

了解索引下推吗?
MySQL 5.6引入了索引下推优化。默认开启,使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。

有了索引下推优化,可以在减少回表次数

在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下:

在 people_table中有一个二级索引(zipcode,lastname,address),查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

如果没有使用索引下推技术,则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件

如果使用了索引下推技术,则MYSQL首先会返回符合zipcode=’95054’的索引,然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。

怎么查看MySQL语句有没有用到索引?
通过explain,如以下例子:

EXPLAIN SELECT * FROM employees.titles WHERE emp_no=‘10001’ AND title=‘Senior Engineer’ AND from_date=‘1986-06-26’;

id select_type table partitions type possible_keys key key_len ref filtered rows Extra
1 SIMPLE titles null const PRIMARY PRIMARY 59 const,const,const 10 1
id:在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ,如explain select * from s1 where id = (select id from s1 where name = ‘egon1’);第一个select的id是1,第二个select的id是2。有时候会出现两个select,但是id却都是1,这是因为优化器把子查询变成了连接查询 。

select_type:select关键字对应的那个查询的类型,如SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION 。

table:每个查询对应的表名 。

type:type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引) 。

通常来说, 不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的. 而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.

possible_key:查询中可能用到的索引(可以把用不到的删掉,降低优化器的优化时间) 。

key:此字段是 MySQL 在当前查询时所真正使用到的索引。

filtered:查询器预测满足下一次查询条件的百分比 。

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数. 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。

extra:表示额外信息,如Using where,Start temporary,End temporary,Using temporary等。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200