MySQL联合索引是否支持不同排序规则.pdfVIP

MySQL联合索引是否⽀持不同排序规则 篇⾸语： 截⽌到⽬前的5.7 .4版本为⽌，MySQL 的联合索引仍⽆法⽀持联合索引使 ⽤不同排序规则， 如：ALTER TABLE t ADD INDEX idx(col 1, col2 DESC 。 先来了解下MySQL关于索引的⼀些基础知识要点： • a、EXPLAIN结果中的key_len只显示了条件检索⼦句需要的索引⻓度，但 ORDER BY • b、 合索引(composi e index)：多个字段组成的索引，称为 合索引； 例如：ALTER TABLE ADD INDEX `idx` (col1, col2, col3) • c、覆盖索引(covering index)：如果查询需要读取到索引中的⼀个或多个字段，则 例如：SELECT col1, col2 FROM ; • d、最左原则(prefix index)：如果查询条件检索时，只需要匹配 合索引中的最左 例如：SELECT * FROM WHERE col1 = ? AND col2 = ?; • e、在⽼版本 （⼤概是5.5以前，具体版本号未确认核实）中，查询使⽤ 合索引时，可 例如：SELECT * FROM WHERE col3 = ? AND col1 = ? AND col2 = ?; • f、MySQL截⽌⽬前还只⽀持多个字段都是正序索引，不⽀个别字段持倒序索引； 例如：ALTER TABLE ADD INDEX `idx` (col1, col2, col3 DESC)，这⾥的DE • g、 合索引中，如果查询条件中最左边某个索引列使⽤范围查找，则只能使⽤前缀索引 例如：SELECT * FROM WHERE col1 = ? AND col2 = ? AND col3 = ?; 这 • h、InnoDB引擎中，⼆级索引实际上包含了主键索引值； 关于 key_len 的计算规则： • 当索引字段为定⻓数据类型，⽐如：char，in ，da e ime，需要有是否为空的标记， • 当索引字段为变⻓数据类型，⽐如：varchar，除了是否为空的标记外，还需要有⻓度 • 当字段定义为⾮空的时候，是否为空的标记将不占⽤字节； • 同时还需要考虑表所使⽤字符集的差异，la in1编码⼀个字符1个字节，gbk编码⼀个 因此，key_len长度的计算公式 • varchr(10)变⻓字段且允许NULL : 10*(Charac er Se ：u f8=3,gbk=2,la i • varchr(10)变⻓字段且不允许NULL : 10*(Charac er Se ：u f8=3,gbk=2,la • char(10)固定字段且允许NULL : 10*(Charac er Se ：u f8=3,gbk=2,la in1 • char(10)固定字段且不允许NULL : 10*(Charac er Se ：u f8=3,gbk=2,la i 附，关于 filesort 排序算法： 光看 filesort 字⾯意思，可能以为是要利⽤磁盘⽂件进⾏排序，实则不全然。 当MySQL不能使⽤索引进⾏排序时，就会利⽤⾃⼰的排序算法(快速排序算法 在内存 (sort buffer 中对数据进⾏排序，如果内存装载不下，它会将磁盘上的数据进⾏分块， 再对各个数据块进⾏排序，然后将各个块合并成有序的结果集 （实际上就是外排 序）。 对于filesort ，MySQL有两种排序算法： 1、两遍扫描算法(Two passes 实现⽅式是先将须要排序的字段和可以直接定位到相关⾏数据的指针信息取出，然后 在设定的内存 （通过参数 sort_buffer_size 设定）中进⾏排序，完成排序之后再次通过 ⾏指针信息取出所需的列。 注：该算法是4 .1之前只有这种算法，它需要两次访问数据，尤其是第⼆次读取操作会 导致⼤量的随机I/O操作。不过，这种⽅法内存开销较⼩。 2、⼀次扫描算法(single pass 该算法⼀次性将所需的列全部取出，在内存中排序后直接将结果输出。 注：从 MySQL 4 .1 版本开始⽀持该算法。它减少了I/O的次数，效率较⾼，但是内存 开销也较⼤。如果我们将并不需要的列也取出来，就会极⼤地浪费排序过程所需要的 内存。在 MySQL 4 .1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控 制 MySQL 选择第⼀种排序算法还是第⼆种。当取出的所有⼤字段总⼤⼩⼤于 max_length_for_sort_d