Mysql 索引

发表于 | 分类于
mysql 索引浅探

Mysql 索引

索引的分类

mysql 索引分为 聚簇索引 和 非聚簇索引

聚簇索引：数据的物理存储顺序和索引的顺序是一致的

非聚簇索引：数据的物理存储顺序和索引的顺序不一致

很明显数据的物理存储顺序只有一种，所以 聚簇索引 只能有一个

关于联合索引

1
2
3
4
5
6
7
8
9
CREATE TABLE `grade_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `grade` double NOT NULL DEFAULT '0' COMMENT '成绩',
  `teacher_id` varchar(20) NOT NULL DEFAULT '' COMMENT '教师id',
  `class_id` varchar(20) NOT NULL DEFAULT '' COMMENT '课程id',
  `student_id` varchar(20) NOT NULL DEFAULT '' COMMENT '学生id',
  PRIMARY KEY (`id`),
  KEY `grade_info_teacher_id_IDX` (`teacher_id`,`class_id`,`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4

先创建一个表，这个表中有个联合索引 grade_info_teacher_id_IDX

下面插入几条数据：

图片

下面开始讨论下面几个问题:

`grade_info_teacher_id_IDX` (teacher_id, class_id, student_id) 这个索引如果查询条件只有teacher_id，会用上索引么？<br />
如果查询条件中有teacher_id, student_id会用上索引么？<br />
如果查询条件中有class_id, student_id会用上索引么？<br />
如果查询条件中有teacher_id, class_id会用上索引么？<br />

图片

很明显当查询条件中使用了联合索引的第一列的时候会用上索引

图片

这里当查询条件使用联合索引的第一列和第三列的时候也会用上索引，
但是这里有个问题， 图中的查询条件只会查询出一条，但是现在问题出现了， 但explain 结果中的row 是 2， 这里得知 索引只用到了第一列

图片
很明显，这种查询条件没用上索引

图片
这里使用到了索引，而且索引的长度和 只使用第一列的时候相比更长； 可以确定这里使用了索引的前两列

索引的限制

这里说的索引的限制将能解释上面联合索引的问题

1. 如果不是按照索引的最左列开始查询，则无法使用索引

如果查询条件中有class_id, student_id会用上索引么？
这里就能回答这个问题，不会；

2. 不能跳过索引中的列

如果查询条件中有teacher_id, student_id会用上索引么？
这里跳过了第二列，这个时候只使用索引的第一列

3. 如果查询中有某列的范围查询，则其右边的所有列的查询都不能使用索引优化查找

图片
从 explain 中 row 是 3， 但是查询到结果只有一个，而只按照第一列范围查询结果是3，这个例子可以印证上述限制

索引的选择

突然发现一个问题，mysql 是如何选择索引的？

下面看看这个表：

1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS `school`;
CREATE TABLE `school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '学校名称',
  `phone` varchar(20) NOT NULL DEFAULT '' COMMENT '学校电话',
  `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '学校地址',
  `email` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_name_phone_email` (`name`,`phone`,`email`),
  KEY `idx_name_phone` (`name`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='学校'

idx_name_phone_email 和 idx_name_phone 这两个索引，设置的 极不合理 ，这里 不推荐使用 ， 但是这种情况下，mysql 会选择哪个索引呢？

插入一条测试数据
图片

1
desc select * from school where name = '重庆邮电大学' and phone = '76822313'\G

执行一下上面这个 sql 语句，两个索引都可能被用到，会是哪一个呢？

这里我们能看到，使用的是 idx_name_phone_email
图片

这里做一个简单的猜想，为什么使用的是 idx_name_phone_email, 这和索引的声明顺序有关吗？

重新建表看看：

1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS `school`;
CREATE TABLE `school` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '学校名称',
  `phone` varchar(20) NOT NULL DEFAULT '' COMMENT '学校电话',
  `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '学校地址',
  `email` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_name_phone` (`name`,`phone`),
  KEY `idx_name_phone_email` (`name`,`phone`,`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='学校'

修改了索引定义顺序之后， 使用的索引也随之改变了

图片

好像，我们的猜想被证实了

如果我们需要制定使用哪个索引呢？

图片

force index(idx_name): 使用这个关键字强制指定要用的索引即可

还有其他方式吗？

可以 use index(idx_name) 推荐使用的索引

图片

use index 和 force index 的区别
If you use USE INDEX then you RECOMMEND optimizer to use this index, but it can use a table scan if optimizer thinks it will be faster. If you use FORCE INDEX then you MAKE optimizer to use this index even if it thinks a table scan is more efficient. Optimizer will use a table scan only if there is no way to use index to find rows.

也就是说， 使用 USE INDEX 时 sql 优化可能会进行全表扫描，如果全表扫描更快的话； 而使用 FORCE INDEX 时，只能是使用索引，除非查询条件没有命中索引

指定使用索引还有一个使用场景，就是解决之前说到的 范围查询时，索引无效

图片

除了指定要使用的索引，还能指定不使用哪些索引

图片

排除掉不使用的索引，就能使用想要使用的索引