MySQL 主键索引比二级索引慢？

声明

本次测试与提问是为了了解其 原因与原理，非优化与解决问题的方案

缓存已经关闭

1、MySQL版本

5.7.22

2、第一次数据量

约 840 万

3、第二次数据量

约 100 万

4、建表语句

CREATE TABLE `user` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`girl_name` VARCHAR(20) NULL COMMENT '名称',

PRIMARY KEY (`id`),

INDEX `key` (`girl_name`),

FULLTEXT INDEX `girl_name` (`girl_name`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

AUTO_INCREMENT=8484622

;

5、查询语句 （QL_1 明显快于 SQL_2）

// 二级索引，并且使用了覆盖索引
SQL_1：SELECT id FROM `user` ORDER BY `girl_name` LIMIT 8000000,100
// 主键索引
SQL_2：SELECT id FROM `user` ORDER BY `id` LIMIT 8000000,100
//  SQL_1 明显快于 SQL_2 

6、测试与分析

• 主键检索往往快于二级索引
• 第一次约 840 万的数据，考虑到可能是因为表数据量过大导致 IO 次数过多所以 主键 慢于 二级索引
• 第二次把数据降到约 100 万的，多次测试后，还是同样结果。

7、使用的分析工具

1、使用了 Explain 、OP trace 、Profile

2、Explain 结果：除使用到的索引外，全部一致

3、OP 结果：对比一致

4、Profile 结果：是在 sending data 耗时长，无从下口

8、思考与疑问

• 不是数据量过大导致 IO 次数多于二级索引，如何确定不是主键 IO 多于二级 ？ 查看 IO 次数？统计读入页数？
• LIMIT 偏移量越大，性能越差。SQL_1 与 SQL_2 LIMIT 速度是相同的吗？

• 康："可能是主键每行单独 IO 不能批量一次读取，只能顺序。推断，是不是二级索引是一次读多条？"