6-MySQL原理-索引匹配原则

专栏目录

1. 1-MySQL原理-设计架构
2. 2-MySQL原理-InnoDB架构与内存管理
3. 3-MySQL原理-InnoDB架构与数据一致性
4. 4-MySQL原理-SQL执行原理
5. 5-MySQL原理-存储引擎与索引结构
6. 6-MySQL原理-索引匹配原则
7. 7-MySQL原理-事务底层实现
8. 8-MySQL原理-MySQL回表与解决方案
9. 9-MySQL原理-执行计划最全详解
10. 10-MySQL原理-索引失效索引匹配最全详解

索引知识

离散性最好的列：

越大离散型越好 count(distinct col):count(col) 理解为差异性。结论：离散性越高选择性就越好，比如有一个性别的字段的索引，假设男为1，女为0：就会生成一下一个索引树：

这个时候要搜索女的数据，那么在根节点触发，可以由两条路可以走，从中间走下去的话发现可以选择的线路太多了，这样会导致搜索引擎懵逼，优化器觉得既然要搜索这么多数据，还不如全表扫描呢，这就导致离散型降低。不利于性能。

最左匹配原则：

对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过，在创建数据库的时候需要选择字符集及排序规则，这都是有用的 ，比如一棵B-tree中的根节点为一个字符串 abc ，那么我现在要搜索一个为 adc的索引关键字的数据，根节点abc的ASCII 码为 97 98 99，而 adc的为 97 100 99，那么和3个数字会逐一比对，且100>98，接下去一定会走右子树。

联合索引：

单列索引：节点中关键字[name] 及索引的关键字的值为那么对应的值 ，比如 张三。

联合索引：节点中关键字[name,phoneNum]比如张三，138888888。

联合索引列选择原则。

1. 经常用的列优先 【最左匹配原则】。
2. 选择性（离散度）高的列优先【离散度高原则】。
3. 宽度小的列优先【最少空间原则】。

示例：经排查发现最常用的sql语句：Select * from users where name = ? ;Select * from users where name = ? and phoneNum = ?;

机灵的李二狗的解决方案：create index idx_name on users(name);–（冗余索引 最左原则，下面这个联合索引适用于以上2个sql语句）；create index idx_name_phoneNum on users(name,phoneNum);

所以在这种情况下只需要建立一个联合索引即可，会根据最左匹配原则去匹配的。

覆盖索引：

如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引。覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能。比说所建立了一个联合索引 reate index idx_name_phoneNum on users(name,phoneNum);而此刻有sql select name phoneNum from 。。。。这个就是覆盖索引。

总结及验证：

索引列的数据长度能少则少。索引一定不是越多越好，越全越好，一定是建合适的。查询条件上有计算函数无法命中索引。

匹配列前缀：like 9999%（最原则上按照左匹配上来说是可以的，但是不一定能用到索引，当离散性太差的时候就不行），like %9999%（不行）、like %9999（不行）用不到索引；

Where 条件中 not in 和 <>操作无法使用索引；匹配范围值，order by 也可用到索引；多用指定列查询，只返回自己想到的数据列，少用select *；

联合索引中如果不是按照索引最左列开始查找，无法使用索引；

联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；比如联合索引【name，phoneNum】，当SQL为：select …where name=‘1’ and phoneNum>xxxxxxx.

联合索引中如果查询中有某个列的范围（大于小于）查询，则其右边的所有列都无法使用索引；

所有文章都是以专栏系列编写，建议系统性学习，更容易成为架构师！
博主每天早晚坚持写博客给与读者价值提升，为了让更多人受益，请多多关照，如果觉得文章质量有帮助到你，请关注我的博客，收藏此文，持续提升，奥利给！
另外我不打算靠运营方式拿到博客专家的认证，纯纯的科技与狠活来征服读者，就看读者的感恩之心了，祝你好运连连。