MySQL高性能索引策略(二)

这是我参与更文挑战的第6天，活动详情查看： 更文挑战

前言

没有什么比数据库的一个高性能的索引更能减少查询时间的了，一本字典没有前面的目录，想找一个字那可真的是要费死劲了。

多列索引

当sql中有多个and条件时，建立一个包含所有相关列的多列索引，而不是多个独立的单列索引
下面这是一个多列索引的表

CREATE TABLE `chinese_score` (
  `exam_id` int(11) NOT NULL COMMENT '考试Id ，外键',
  `exam_code` int(11) NOT NULL COMMENT '考号',
  `user_name` char(5) DEFAULT NULL,
  `class_name` int(11) DEFAULT NULL COMMENT '班级名称',
  `total_score` int(11) DEFAULT NULL,
  `area_ranking` int(11) DEFAULT NULL,
  `school_ranking` int(11) DEFAULT NULL,
  `class_ranking` int(11) DEFAULT NULL,
  PRIMARY KEY (`exam_code`) USING BTREE,
  UNIQUE KEY `idx_exam_id_name` (`exam_id`,`class_name`,`user_name`) USING BTREE,
  KEY `idx_total_score` (`total_score`) USING BTREE,
  KEY `idx_exam_id` (`exam_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

我们如果想要查找表里的exam_id=1且为15班的学生时sql如下

select * from `chinese_score` where exam_id=1 and class_name=15
复制代码

可以看到使用了我们创建的多列索引idx_exam_id_name
而如果我们搜索exam_id=1并且分数为90分的学生

可以看到MySQL找到了多个包含我们查询条件的索引，那么它究竟选择哪个呢？
它选择的是idx_total_score,而不是多个单列索引，选择分数作为索引来查找，区分度高，返回的数据最少，而idx_exam_id则因为区分度低返回的数据更多而被排除了。

选择合适的索引列顺序

在一个多列 B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
选择性最高的列放在前面通常是最好的，如果要考虑排序和分组的话，需要注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，从而避免出现file_sort的情况，影响查询性能。
正例：where a =? and b =? order by c; 索引： a _ b _ c
比如

select * from `chinese_score` where exam_id=1 and class_name=15 ORDER BY user_name
复制代码

对应的解释如下

可以看到Extra 这里因为where条件和order by 中正好和我们的idx_exam_id_name所以匹配上了，所以直接就是一个Using index condition

select * from `chinese_score` where exam_id=1 and class_name=15 ORDER BY class_ranking
复制代码

如果Order By 使用了不在索引中的列进行排序，那么

我们可以看到在 extra 列中除了Using index condition之外还多了一个 Using filesort，因为多了一个不在索引中的class_ranking，所以不得不加了一个文件排序来对数据进行排序。
反例：索引中有范围查找，那么索引有序性无法利用，如： WHERE a >10 ORDER BY b; 索引a _ b 无法排序
比如我们想查询1班和2班都有多少人参加了exam_id=1的考试

select * from `chinese_score` where exam_id=1 and class_name<=2 ORDER BY user_name
复制代码

解释如下：

可以看到因为有了一个范围查询，导致了出现file_sort的文件排序影响了性能。

总结

今天我们讲解了MySQL如何创建高性能的索引的介绍，明天我们还将继续介绍其他建立高性能索引的方法，敬请期待，下篇再见！