MySQL原理解读——索引
我们主要以InnoDB引擎来了解索引
1、索引的分类
索引都存储在磁盘的数据页中
索引在大体上分为两类：聚簇索引、非聚簇索引
他们都通过B+数实现
1.1、聚簇索引
把索引值和数据存储在一起的索引叫聚簇索引
mysql的主键默认使用聚簇索引
尽量使用自增字段作为主键，防止后续行数据插入导致索引树中的节点分裂甚至是磁盘的数据页分裂
1.2、非聚簇索引
把索引值和指向数据的值存储在一起的索引叫非聚簇索引
像联合索引、前缀索引、唯一索引都属于非聚簇索引
mysql中非聚簇索引把主键当做指向数据的值，在使用非聚簇索引时，会先从非聚簇索引树中遍历到对应索引，获取叶子结点中指向聚簇索引树的主键，然后遍历聚簇索引树，找到对应的主键索引，从而获取叶子结点中的整条行数据，从非聚簇索引指向聚簇索引的过程叫回表
2、非聚簇索引实现分类
2.1、联合索引
多个字段组合在一起的索引叫联合索引，会把组成中的所有字段值存储在叶子结点
联合索引有一个特点，允许一个查询只使用联合索引最左侧的部分字段，查询条件的字段顺序必须与联合索引组合的字段顺序一致，也叫做最做匹配原则
联合索引的好处有两个：
- 省空间。只要符合使用规则，建立一个索引相当于建立了三个索引
- 效率高。如果索引字段包含所有的查询字段，可以避免回表，这样的优化方法也叫覆盖索引
2.2、前缀索引
使用字段的前多少位作为索引值的索引叫前缀索引
前缀索引的好处也有两个：
- 省空间和效率高。在数据量大的情况下，前缀索引比普通索引占用更小的空间，减少遍历非聚簇索引树的次数，提高获取主键的效率
前缀索引的创建长度涉及到索引选择性的问题：
索引选择性是指不重复的索引值和表记录的比值，即
。当
和原字段索引选择性最接近时，这时前缀索引的长度即为最佳长度
2.3、唯一索引
保证值唯一的普通索引叫唯一索引
跟主键的区别：一个表可以有多个唯一索引，但只能有一个主键
唯一索引的特点：在执行插入更新事务时会先判断值是否唯一，保证了唯一性但降低了效率
3、索引优化
- 列与列对比：索引失效
select * from A where a = b;- 查询列值为null的行数据：type不为const，效率较低
select * from A where a is not null;#type为range
select * from A where a is null;#type为ref- in范围查询：当in的个数超过一定值或一定比例，type可能由range退化为all
select * from A where a in (xxx,yyy,zzz)- like模糊查询：匹配规则最多包含一个后置通配符时才会走range索引，其余不走索引
select * from A where a like 'xxx';# 走索引但是好像没什么意义
select * from A where a like 'xxx%';# 走索引
select * from A where a like '%xxx';# 不走索引
select * from A where a like '%xxx%';# 不走索引- 函数的使用：在条件查询时，对条件字段使用函数肯定不会走索引，对值使用一部分函数不会走索引
select * from A where upper(a) = 'xxx';# 对条件字段使用函数不会走索引
select * from A where a = lower('xxx'); # 对值使用一部分函数会走索引
select * from A where a = str_to_date('2021-09-21','%Y-%m-%d');# 对值使用一部分函数不会走索引- 联合索引：前导列区分度大，查后导列时，前导列的分裂会很耗资源，mysql认为不如全表扫描
- 数据类型隐式转换：
select * from A where a = 12345; # a是varchar，条件值为int，会产生隐式转换使索引失效- 运算：对条件字段进行运算不会走索引
select * from A where a/2 = 12345;# 运算作用在字段不走索引
select * from A where a = 12345*2;# 运算作用在值走索引
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
