4.Mysql 优化

1.ORDER BY的优化        某些情况下，MySQL使用索引排序，尽量避免使用 filesort         即使ORDER BY与索引不完全匹配，也可以使用索引，只要索引的未使用部分和额外的ORDER BY列都是WHERE子句中的常量 constants 。如果索引不包含查询访问的 列，则仅当索引访问比其他访问方法有效时才使用索引，使用索引是否比扫描全表更有效。  SELECT * FROM t1 ORDER BY key_part1, key_part2;        在key_part1,key_part2 建立索引         查询使用SELECT*，它可以选择比key_part1和key_part2更多的列。在这种情况下，扫描整个索引并查找表行以查找不在索引中的列可能比扫描表和对结果排序更昂贵。如果是这样，优化器可能不使用索引。如果SELECT*只选择索引列，则使用索引并避免排序。

        在这个查询中，key_part1是常量，因此通过索引访问的所有行都是按key_part2的顺序进行的，并且如果WHERE子句的选择性足以使索引范围扫描比表扫描便宜，则索引（key_part1，key_part2）可以避免排序 SELECT * FROM t1 WHERE key_part1 = constantORDER BY key_part2;

假设 key_part1不是索引或索引的一部分，在条件中作为常量条件存在，则优化器也会使用索引 SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2  ORDER BY key_part2;

不会使用索引： 查询中，order by 中的列是非连续的部分索引 SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

用于获取行的索引与ORDER BY中使用的索引不同 SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

查询将ORDER BY与包含索引列名以外的表达式或函数一起使用 SELECT * FROM t1 ORDER BY ABS(key)； SELECT * FROM t1 ORDER BY -key; 查询具有不同的ORDER-BY和GROUP-BY表达式         只有ORDER BY子句中名为的列的前缀上有索引。在这种情况下，索引不能用于完全解析排序顺序。例如，如果只对CHAR（20）列的前10个字节进行了索引，则索引无法区分超过10个字节的值，因此需要进行filesort。 索引没有按顺序存储行。例如，对于内存表中的哈希索引

SELECT ABS(a) AS a FROM t1 ORDER BY a;  该查询使用到了别名，别名覆盖了查询列明，order by 不能使用索引

FileSort         如果索引不能用于满足ORDER-BY子句，MySQL将执行一个filesort操作，读取表行并对其进行排序。文件排序在查询执行中构成了一个额外的排序阶段。         为了获得文件排序操作的内存，从MySQL8.0.12开始，优化器会根据需要递增地分配内存缓冲区，直到达到sort_buffer_size系统变量指定的大小，而不是像MySQL8.0.12之前那样预先分配固定数量的sort_buffer_size字节。对于较大的缓冲区，用户可以使用较大的缓冲区进行排序，而不必为较大的缓冲区设置较大的排序速度。 如果结果集太大而无法放入内存，则filesort操作将根据需要使用临时磁盘文件。某些类型的查询特别适合完全在内存中的文件排序操作。

    增加sort_buffer_size变量值。理想情况下，该值应该足够大，使整个结果集能够放入排序缓冲区，该值受max_sort_length值的限制。      通过 Sort_merge_passes 变量可以监控 排序时，文件(merge temporary files)合并数量     增大read_rnd_buffer_size变量值，以便一次读取更多行     更改tmpdir系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径；您可以使用此功能将负载分散到多个目录中。在Unix上用冒号字符（：）分隔路径，在Windows上用分号字符（；）分隔路径。路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

使用 EXPLAIN 查看查询计划： 如果Extra 列没有包含 Using filesort，则说明没有执行filesort，否则说明使用了文件排序 "filesort_summary": { "rows": 100, "examined_rows": 100, "number_of_tmp_files": 0, "peak_memory_used": 25192, "sort_mode": ""} peak_memory_used  ：表示排序过程中任何一次使用的最大内存。 sort_mode: : 这表示排序缓冲区元组是包含原始表行的排序键值和行ID的对。元组按排序键值排序，行ID用于从表中读取行。 :这表示排序缓冲区元组包含排序关键字值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。 : 与前面的变量一样，但是附加的列被紧密地打包在一起，而不是使用固定长度的编码。