sql优化之排序 | 孺子牛的博客

sql优化索引排序

mysql

发布日期: 2020-03-06

作者: 孺子牛

简介

本文介绍在sql中进行 order by 操作的时候，如何进行分析和优化sql执行效率。

order by 索引优化总结：

避免出现 using filesort(文件排序)，常见的方法如下：
1. 单值索引：
  1. order by列和 select列一致，可以实现using index(索引覆盖)
  2. order by 只能使用一个索引，无法同时使用多个索引
2. 复合索引：
  1. order by列的顺序需要和复合索引创建顺序一致，不能跨列(最佳左前缀)
  2. where + order by 列需要和索引创建顺序一致，不能跨列(最佳左前缀)
  3. 不能对 order by 列同时进行正向和反向排序

预备概念

索引：将表中的一列或者多列按照指定的规则进行排序的数据结果。

索引覆盖：通过遍历索引表，就可以返回满足条件的数据

sql编写顺序：select … distinct … join … on …. where … group by … having … order by limit

sql执行顺序：from … join … on … where … group by … having … select … distinct … order by limit

预备数据

create database demo;

use demo

create table book(
    bid bigint(11),
    author varchar(10),
    typeid bigint(11)
)engine=innodb default charset=utf8;

insert into book values (1,'tz',1);
insert into book values (2,'tz',2);
insert into book values (3,'tz',3);
insert into book values (4,'tw',1);
insert into book values (5,'ta',1);

order by 避免use filesort

order by列和 select列需要为同一个索引

explain select typeid from book order by author;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

/*一致则索引覆盖*/
alter table book add index idx_author(author);
explain select author from book order by author;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_author | 33      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

每次排序只能使用一个索引，不能同时多个

alter table book add index idx_typeid(typeid);
/*Using filesort 无法使用多个索引*/
explain select author from book order by author,typeid;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

当需要多列进行排序时，需要创建复合索引

alter table book add index idx_author_typeid(author,typeid);
explain select author from book order by author,typeid;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_author_typeid | 42      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+

order by 多列的使用顺序，需要和复合索引的创建顺序一致

/*此时复合索引的顺序为author_typeid，不一致则出现use filesort*/
explain select author from book order by typeid,author;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_author_typeid | 42      | NULL |    5 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+

order by 多列的时候不能同时进行正向和反向排序

explain select author from book order by author,typeid desc;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_author_typeid | 42      | NULL |    5 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------+

(using index 最优方案) order by 列和 select 列一致，可以实现索引覆盖

/*此时复合索引的顺序为author_typeid*/
explain select author,typeid from book order by author,typeid;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_author_typeid | 42      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+

/*select 列顺序无关*/
explain select typeid,author from book order by author,typeid;
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_author_typeid | 42      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+

wher + order by 优化

首先删除全部索引，避免干扰

drop index idx_author on book;
drop index idx_typeid on book;
drop index idx_author_typeid on book;

单值索引使用中：where ，order by 和 select 需要使用一个索引列

create index idx_author on book(author);
create index idx_typeid on book(typeid);
explain select author from book where  author='tz' order by typeid;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | idx_author    | NULL | NULL    | NULL |    5 |    60.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

/*在 where 和 order by 为同一单值索引时候，select 也为索引列，则可以索引覆盖*/
explain select author from book where  author='tz' order by author;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | ref  | idx_author    | idx_author | 33      | const |    3 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

如果 where 和 order by 不为一个索引，则需要创建复合索引

/*根据where和order by 顺序，创建复合索引*/
alter table book add index idx_author_typeid(author,typeid);
explain select author from book where  author='tz' order by typeid;
+----+-------------+-------+------------+------+------------------------------+-------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys                | key               | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+------------------------------+-------------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | book  | NULL       | ref  | idx_author,idx_author_typeid | idx_author_typeid | 33      | const |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+------------------------------+-------------------+---------+-------+------+----------+--------------------------+

复合索引的创建顺序和 where+order by 顺序需要一致

explain select bid from book where  typeid=1 order by author;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+

拓展知识

在mysql底层会使用两种排序方式：

index_sort 索引排序，效率高
file_sort 文件排序，效率低

file_sort

在文件排序中，根据I/O读取次数又分为了：

双路排序(mysql 4.1以前使用，2次I/O操作，效率相对低)：从表中获取符合where条件的order by 列和行索引，添加到排序 buffer 中进行排序，根据排序后结果的行指针，扫描全表输出结果。
单路排序(mysql 4.1以后使用，1次I/O操作，效率相对高)：从表中获取符合where条件的所有数据(order by 列和select …. distinct 列)，添加到排序buffer中，再给根据order by 列进行排序后输出结果。
```
 1. 如果获取的数据超过了排序 buffer 长度，则mysql会自动转为为双路排序，进行多次获取和排序。
 2. 可以通过修改 buffer 长度和减少不必要的列添加到buffer中，来减少I/O次数。
```

/*修改排序buffer长度*/
set max_length_of_sort_data=1024;

mysql集群配置

简介mysql集群配置，采用的是主从模式。可以将一个数据库的数据复制到另外一个数据库中，其中主数据库称为master,从数据库称为slave,关系为1对多。复制操作为异步，从数据库不需要一直连接着主数据库。常见错误解决方案文档：http

2020-03-07 mysql

集群

多表sql优化实战

简介了解在多表查询的时候，如何使用索引来优化sql执行效率多表查询，索引添加原则：根据程序循环设计原则：外循环为小循环，内循环为大循环得出—>小表驱动大表在表中索引建立在经常使用的字段预备数据create database

2020-03-02 mysql

sql优化索引

简介