mysql 组合主键索引_mysql 组合索引带主键ID的问题

场景：

mysql 5.7 某表 t_apply_info 上的2个索引，一个组合索引带了 主键字段 ID，另一个是同字段的单列索引

例如：

KEY idx_1 (apply_serial_no,id)

KEY idx_2 (apply_serial_no)

--此处id为表的主键，自增字段

讨论：

这个组合索引的建立是否无意义，主键ID字段多余并且影响的性能。

理论方面：

mysql innodb中 组合索引为二级索引，由键值，主键，地址(指针)，以page的形式存储。

如果是以ID为主键，那么单列索引里面已经存储了键值。

那么使用索引在相关条件下 ，可以优化使用id的 查询 例如

select id from xxx where

等等sql

--如果谓词中出现主键id 条件，那么会使用主键

推论：

带键值的组合索引 增加了索引长度，那么会导致索引占的空间更大，扫描索引耗时更长，在理论情况下，带主键的组合索引效率要低于其单列索引

对比oracle：

如果是在oracle中，索引为普通B+树索引，其中存储的rowid，不包含主键。那么上述讨论的两种索引 适用的范围 是 带ID的组合索引>单列索引。oracle 中需要根据具体情况讨论

实验：

在表数据量为5千万的量级中

--分别测试 单列索引和组合索引的查询性能

查询sql 为

select count(*) from t_apply_info where apply_serial_no >=xxxx

可以 发现使用的是单列索引(mysql选择最优索引)，查询时间为12s

--扫描 数据量为4千万行

删除单列索引，此时查询只能走 组合索引

然后再次查询：select count(*) from t_apply_info where apply_serial_no >=xxxx

观察执行计划发行，出了使用的索引有区别外，执行计划无太大差别

查询时间为：

12.3s

--见此处多了一个ID的索引长度对范围扫描性能有些影响，但是不是很大，基本上可以忽略(大概2-3%)

结论：

在mysql 环境中，对于带主键的组合索引 无意义，因为二级索引的构成已经包含了主键值，索引mysql 会自动选择更优的单列索引。

在5千万行数据量下的唯一键值查询 两个索引的性能无差别，但是涉及到范围查询，扫描的数据量越大，性能差异越明显(组合索引所占空间大，扫描需要更多的性能)

所以 组合索引带主键ID ，在mysql中是多余的行为，并不能起到优化效果，反而在某些时候会产生负面影响(在5千万数据量时，降低索引性能越3%)。