mysql联合索引和复合主键

标签：name 查询索引联合 mysql 主键

最近优化一个页面，数据5GB（可能要算上索引），单个查询要300s左右，数据表建有联合索引，顺便复习总结下联合索引的东西，本文内容皆来自网络，如有侵权，请联系我删除。

联合索引

单一索引是指索引列为一列的情况，另外，用户可以在多个列上建立索引，这种索引叫做复合索引，也叫组合索引、联合索引。
联合索引在数据库操作期间所需的开销更小，效率高（索引列越多，通过索引筛选出的数据越少），覆盖索引，可以代替多个单一索引。
联合索引遵循最左前缀（连续）匹配原则，即最左优先，查询的时候会优先匹配最左边的索引，所以在建立联合索引的时候查询最频繁的条件要放在左边。
当我们在 (a,b,c) 三个字段上创建联合索引时，实际上是创建了三个索引，分别是(a)、(a,b)、(a,b,c)。查询条件中包含这些索引的时候，查询就会用到索引。
条件的比较要求精确匹配（即"="和"IN"）
索引是B+树实现，b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了
查询的时候，mysql查询优化器会优化 sql 语句为对应索引的顺序，即：（a,c,b）也是可以用索引的。

索引失效的条件

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），否则会导致索引失效而转向全表扫描
存储引擎不能使用索引范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
mysql在使用范围查询（！=或者<> 、>、<、between、NOT IN）的时候无法使用索引会导致全表扫描
is null,is not null也无法使用索引
like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

创建联合索引时列的选择原则

经常用的列优先（最左匹配原则）
离散度高的列优先（离散度高原则）
宽度小的列优先（最少空间原则）

列的离散性计算：count（distinct col）/ count(col)
例如：
id列一共9列都不重复 9/9 = 1
性别列一共9列只有（男或者女）两列 2/9 约等于0.2
离散性越高选择性越大

索引跳跃扫描（Index Skip Scan）

（本部分转载自https://juejin.cn/post/7127656601044910094 作者：一灯架构）

MySQL8.0版本开始增加了索引跳跃扫描的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。

CREATE TABLE `user` ( 
 `id` int NOT NULL AUTO_INCREMENT  , 
 `name` varchar(50) NOT NULL , 
 `gender` tinyint NOT NULL  ,  
PRIMARY KEY (`id`),  KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB;

 explain select * from user where name='一灯';

虽然SQL查询条件只有name字段，但是从执行计划中看到依然是用了联合索引。

并且Extra列中显示增加了Using index for skip scan，表示用到了索引跳跃扫描的优化逻辑。