【mysql索引】之使用索引扫描做排序

前言

今天看了《高性能MySQL》的索引扫描做排序章节,并且亲身实践了一下,发现有些结果与原书不一样,个人猜测是MySQL版本不一样造成的,下面分享一下我个人的笔记。

简介

MySQL 有两种方式生成有序结果:通过排序操作或者按索引顺序扫描。

如果EXPLAIN出来type列的值为index,则说明MySQL使用索引扫描来做排序。(这句有疑问,很多情况下都type都不是index,却可以进行索引排序)

扫描索引本身是很快的,因为只需从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O(有关IO我找到一篇描述生动的文章:理解I/O:随机和顺序),因此按索引顺序读取的速度通常要比顺序地全表扫描慢,尤其是在I/O密集型的工作负载时。

MySQL可以同时使用同一个索引既满足查找,又满足排序。因此,如果可能,设计索引是应该尽可能
地同时满足这两个任务,这样是最好的。


排序成立的条件

只有当索引的列顺序和ORDER BY子句的顺序完全一致时,并且所有列的排序方向(倒序或正序)都一样时,
MySQL才能使用索引来对结果排序。如果需要关联表来查询,则只有当ORDER BY子句引用的字段全部为第一个表时,才会使用索引做排序。
ORDER BY子句和查找型查询限制是一样的,需要满足索引的最左前缀的要求,否则MySQL都需要执行排序操作,而无法利用索引排序。
不过有一种情况是ORDER BY子句可以不满足索引的最左前缀要求,就是前导列为常量的时候,如果WHERE子句或者JOIN子句中对这些列指定了常量,就可以弥补索引的不足(下面会有例子)


例子

下面的例子都会以实例数据库sakila(MySQL安装成功后自带的一个示例数据库)的rental表作为演示,其中表上有
rental_date  (rental_date,inventory_id,customer_id)这个多列索引,我们从下面一例中EXPLAIN中可以看出Extra里并没有出现文件排序(filesort)操作( Using filesort(MySQL中无法利用索引完成的排序操作称为“文件排序”)当我们试图对一个没有索引的字段进行排序时,就是filesoft。它跟文件没有任何关系,实际上是内部的一个快速排序。  ):
-- 即使ORDER BY子句不满足最左前缀要求,也可用于查询排序,因为第一列被指定为一个常数
EXPLAIN SELECT rental_id, staff_id FROM rental
WHERE rental_date = '2005-05-25'
ORDER BY inventory_id, customer_id;


-- 第一列提供常量条件,使用第二列进行排序,这两列组合在一起,形成索引最左前缀,所以可以索引排序,没有出现filesort
EXPLAIN SELECT rental_id, staff_id FROM rental
WHERE rental_date = '2005-05-25'
ORDER BY inventory_id DESC;


-- 原书:这个也没问题,因为ORDER BY使用的两列就是索引的最左前缀 
-- 我个人实践:我这里试过是不行的,难道是MySQL的改良舍弃掉了某种算法?
EXPLAIN SELECT rental_id, staff_id FROM rental
WHERE rental_date > '2005-05-25'
ORDER BY rental_date, inventory_id;



-- 原书:这个查询使用了两种不同的排序方向,不能进行索引排序 
-- 我个人实践:我试过是可以的,可能MySQL改良了吧 
EXPLAIN SELECT rental_id, staff_id FROM rental
WHERE rental_date = '2005-05-25'
ORDER BY rental_date DESC, inventory_id ASC;



-- WHERE 和 ORDER BY 的列无法组成索引最做前缀 
EXPLAIN SELECT rental_id, staff_id FROM rental
WHERE rental_date = '2005-05-25'
ORDER BY customer_id;


-- 索引列在第一列上是范围条件,因此MySQL无法使用索引的其余列 
EXPLAIN SELECT rental_id, staff_id FROM rental
WHERE rental_date > '2005-05-25'
ORDER BY inventory_id, customer_id;




-- inventory_id上有多个条件,对于排序莱说,这也属于范围查询
EXPLAIN SELECT rental_id, staff_id FROM rental
WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2)
ORDER BY customer_id;



/*
理论上是可以用索引进行关联排序的,但由于优化器在优化时把film_actor表当作关联的第二张表,
因此实际上无法使用索引
*/
EXPLAIN SELECT actor_id, title FROM film_actor
INNER JOIN film USING(film_id) ORDER BY actor_id;





©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页