mysql排序与索引关系

最新推荐文章于 2024-09-20 14:44:22 发布

夜不过如此

最新推荐文章于 2024-09-20 14:44:22 发布

阅读量1.5k

点赞数 1

分类专栏： mysql java 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_43517448/article/details/121736624

版权

java 同时被 2 个专栏收录

37 篇文章 1 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

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)索引