mysql排序与索引关系

MySQL排序与索引的关联

sql语句：

EXPLAIN SELECT
	id 
FROM
	account 
	LIMIT 99999,
	20


EXPLAIN SELECT id FROM account WHERE id >( SELECT id FROM account LIMIT 99999, 1 ) 
LIMIT 0,
20
    
    这两种效率是类似的，网上不晓得是如何弄得，亲测
    https://www.cnblogs.com/wanghongsen/p/12616558.html

使用Explain看Extra。

Extra

​ Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：

■ Using where

​ 表示查询需要通过索引回表查询数据。

■ Using index

​ 表示查询需要通过索引，索引就可以满足所需数据。

■ Using filesort

​ 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort，建议优化。

■ Using temprorary

​ 查询使用到了临时表，一般出现于去重、分组等操作。

索引与排序

如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。

​ 如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用index排序方式，建议大家尽可能采用覆盖索引。

​ MySQL查询支持filesort和index两种方式的排序。

​ filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。

​ index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

猜测：在文件排序中limit 0,2与limit 500000,2效率差不多，相当于整个查询结果都需要进行排序

但在index排序中limit 0，2和limit 500000,2的差距就非常明显了。

但是结果是两者的limit 0,2 和limit 500000,2效率相差很大。
考虑原因

文件排序（FileSort）分为两种：

​ 双路排序（又叫回表排序模式）：先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

​ 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

举个例子，下面有一段sql：

 select * from user where name = "自由的辣条" order by age

双路排序过程:
MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取主键id 和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

1.从索引 name 找到第一个满足 name = ‘自由的辣条’ 的主键id
2.根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中
3.从索引 name 取下一个满足 name = ‘自由的辣条’ 记录的主键 id
4.重复 3、4 直到不满足 name = ‘自由的辣条’
5.对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序
6.遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端

单路排序过程：

1.从索引name找到第一个满足 name = ‘自由的辣条’ 条件的主键 id
2.根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
3.从索引name找到下一个满足 name = ‘自由的辣条’ 条件的主键 id
4.重复步骤 2、3 直到不满足 name = ‘自由的辣条’
5.对 sort_buffer 中的数据按照字段 age 进行排序
6.返回结果给客户端	

从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer(排序缓存) 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。

对比：

​ 其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

选型：

​ 至于mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。
注意：

​ 如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

order by关键字优化：

​ ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。MySQL支持二种方式的排序FileSort和Index，Index效率较高，FileSort方式效率较低。尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。

原文链接：https://blog.csdn.net/xianyun1992/article/details/107651184

order by 排序原理及性能优化

​ https://blog.csdn.net/u011296165/article/details/89855549

​ 如果有where条件，需要与where条件中的字段一起看。

以下几种情况，会使用index方式的排序。

​ (1)ORDER BY 子句索引列组合满足索引最左前列

​ explain select id from user order by id; //对应(id)、(id,name)索引有效

​ (2)WHERE子句+ORDER BY子句索引列组合满足索引最左前列

​ explain select id from user where age=18 order by name; //对应 (age,name)索引

以下几种情况，会使用filesort方式的排序。

​ (1)对索引列同时使用了ASC和DESC

​ explain select id from user order by age asc,name desc; //对应(age,name)索引

​ (2)WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询(例如>、

​ explain select id from user where age>10 order by name; //对应(age,name)索引

​ (3)ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

​ explain select id from user order by name; //对应(age,name)索引

​ (4)使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

​ explain select id from user order by name,age; //对应(name)、(age)两个索引

​ (5)WHERE子句与ORDER BY子句，使用了不同的索引

​ explain select id from user where name=‘tom’ order by age; //对应 (name)、(age)索引

​ (6)WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

​ explain select id from user order by abs(age); //对应(age)索引

原文链接：https://blog.csdn.net/weixin_32523999/article/details/113269079

orderBy与limit

​ 如果order by的列有相同的值时，mysql会随机选取这些行，为了保证每次都返回的顺序一致可以额外增加一个排序字段（比如：id），用两个字段来尽可能减少重复的概率。

https://www.cnblogs.com/cjsblog/p/10874938.html

SQL执行过程

​ https://www.cnblogs.com/whgk/p/10993436.html