0%

MySQL优化-(4)-慢查询SQL优化-1

1. 慢查询SQL优化思路

1.1 优化什么样的SQL?

优化是有成本的, 首先选择给系统带来最大收益的sql

10000次20次io/h; 10次20000次io/h

优化哪个? 优化考虑高并发的SQL;

1.2 确定优化对象的性能瓶颈是CPU还是IO?

数据访问耗时太多?–>有无索引?是不是没索引,走的全表搜啊秒? 需不需要建索引?

是CPU的瓶颈? –> 分组/排序是不是有问题?

1.3 优化目标是什么? 优化到什么标准?

硬件的承受范围

业务的承受范围

能够给用户带来的体验

1.4 从explain分析入手

1.5 join中用小集驱动大集(把小结果集放外层)

2个表做join查询, 相当于两个for循环, 外层一个, 内层一个; 把小的结果集放在外层, 这样, 外层循环的次数就小; 比如外层20个记录,内层100万记录, 这样外层循环20次, 每次取一条取匹配100万中符合的数据, 形成的数据集就相对压力小;

但是如果把100万放外层, 相当于至少得100万次循环!

1.6 尽量在索引中完成排序

如果order by 的字段都有索引, 就会用索引来进行排序, 因为索引本身就是有序的;

最好的就是 select的列和order by的列都有索引, 这样用索引就可以完成;

如果order by 的列没有索引, 会从表中把数据取出, 开辟单独内存空间来进行排序, 如果内存空间也不够, 只能借助临时文件进行排序, 甚至可能落盘(写入临时文件-增加物理IO);

1.7 杜绝select*, 只取需要的列

节约网络带宽, select * 有可能不走索引;

但是如果 select 指定列上有索引, 是会走索引的;

1.8 使用最有效的过滤条件

假如where的3个字段都有索引, 选哪个呢?后面续.

1.9 避免复杂的join和子查询

join的表越多, 数据库压力越大, 可以分步结果, 用程序处理, 得到结果;然后二次查;

1.10 小心 order by, group by, distinct语句

需要分组/排序的, 让目标列走索引!

这样就不需要额外的内存空间和临时表

1.11 合理设计并利用索引

2. join的优化

2.1 join中用小集驱动大集(把小结果集放外层)

2.1.1 驱动表

  1. 指定了连接条件: 满足条件的记录少的表(where过滤后);
  2. 未指定连接条件: 表中行数少的表;

2.1.2 left-join, right-join, inner-join的区别

join的原理

通过驱动表的结果集作为循环基础数据, 然后一条一条通过该结果集中的数据, 作为过滤条件, 到下一个表查询数据, 最后合并结果;

1). 左连接驱动表就是左表;

左连接驱动表就是左表; 左表数据全部展示, 不属于左表的字段全部NULL展示;

2). 右连接驱动表就是右表

右连接驱动表就是右表; 右表数据全部展示, 不属于右表的字段全部NULL展示;

3). 内连接不指定驱动表, mysql自己选行数少的(一般情况)

内连接只显示交集部分; 内连接不指定驱动表, MySQL自己根据哪个表数据少来作为驱动表; 下面两个等价, 都是内连接:

select * from t1 join t2 on t1.id=t2.id;
select * from t1, t2 where t1.id=t2.id;

有一点需要注意: 假如有以下前提:

t1表: id无索引, 有10万条记录;

t2表: id有索引, 有10条记录;

如下:

mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | NO   |     | 0       |       |
| name  | varchar(10)         | YES  |     | NULL    |       |
| price | double              | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   150000 |
+----------+
1 row in set (0.05 sec)

mysql> desc t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | NO   | PRI | 0       |       |
| name  | varchar(10)         | YES  | MUL | NULL    |       |
| price | double              | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> explain select * from t1 join t2 on t1.id=t2.id;
+----+-------------+-------+------------+--------+---------------+------+---------+------------+--------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref        | rows   | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+--------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL | NULL    | NULL       | 149692 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | id            | id   | 8       | test.t1.id |      1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

explain select * from t1 join t2 on t1.id=t2.id; 可以看出 第一条是 t1表, 也就是说, 驱动表是t1表.

这跟上面说的内连接的驱动表由mysql自己选, 选记录少的来做驱动表不符啊!

问题就在索引: MySQL在选驱动表时, 看到 t2 表是有索引的, 所以, 驱动表就选择了没有索引的表–因为驱动表是用来遍历的, 不用索引的功能;

我们看查询的性能:

mysql> select * from t1 join t2 on t1.id=t2.id;
+----+---------+--------+----+---------+--------+
| id | name    | price  | id | name    | price  |
+----+---------+--------+----+---------+--------+
|  1 | 商品1   | 200.17 |  1 | 商品1   | 200.17 |
|  2 | 商品2   | 200.87 |  2 | 商品2   | 200.87 |
|  3 | 商品3   | 200.81 |  3 | 商品3   | 200.81 |
|  4 | 商品4   | 200.43 |  4 | 商品4   | 200.43 |
|  5 | 商品5   | 200.73 |  5 | 商品5   | 200.73 |
|  6 | 商品6   | 200.36 |  6 | 商品6   | 200.36 |
|  7 | 商品7   | 200.61 |  7 | 商品7   | 200.61 |
|  8 | 商品8   | 200.98 |  8 | 商品8   | 200.98 |
|  9 | 商品9   | 200.06 |  9 | 商品9   | 200.06 |
| 10 | 商品0   | 200.38 | 10 | 商品0   | 200.38 |
+----+---------+--------+----+---------+--------+
10 rows in set (0.43 sec)

查询花费了0.43秒;

4). 自己指定驱动表;

我们可以怎么优化呢? 自己指定驱动表为t2, 用记录少的表来驱动: 改为right join 即可用右表驱动:

mysql> select * from t1 right join t2 on t1.id=t2.id;
+------+---------+--------+----+---------+--------+
| id   | name    | price  | id | name    | price  |
+------+---------+--------+----+---------+--------+
|    1 | 商品1   | 200.17 |  1 | 商品1   | 200.17 |
|    2 | 商品2   | 200.87 |  2 | 商品2   | 200.87 |
|    3 | 商品3   | 200.81 |  3 | 商品3   | 200.81 |
|    4 | 商品4   | 200.43 |  4 | 商品4   | 200.43 |
|    5 | 商品5   | 200.73 |  5 | 商品5   | 200.73 |
|    6 | 商品6   | 200.36 |  6 | 商品6   | 200.36 |
|    7 | 商品7   | 200.61 |  7 | 商品7   | 200.61 |
|    8 | 商品8   | 200.98 |  8 | 商品8   | 200.98 |
|    9 | 商品9   | 200.06 |  9 | 商品9   | 200.06 |
|   10 | 商品0   | 200.38 | 10 | 商品0   | 200.38 |
+------+---------+--------+----+---------+--------+
10 rows in set (0.27 sec)

可见性能得到了提升; 这时候, 如果我们给t1表加上了索引, 性能会进一步提升:

mysql> alter table t1 add unique index(id);
Query OK, 0 rows affected (2.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 right join t2 on t1.id=t2.id;
+------+---------+--------+----+---------+--------+
| id   | name    | price  | id | name    | price  |
+------+---------+--------+----+---------+--------+
|   10 | 商品0   | 200.38 | 10 | 商品0   | 200.38 |
|    1 | 商品1   | 200.17 |  1 | 商品1   | 200.17 |
|    2 | 商品2   | 200.87 |  2 | 商品2   | 200.87 |
|    3 | 商品3   | 200.81 |  3 | 商品3   | 200.81 |
|    4 | 商品4   | 200.43 |  4 | 商品4   | 200.43 |
|    5 | 商品5   | 200.73 |  5 | 商品5   | 200.73 |
|    6 | 商品6   | 200.36 |  6 | 商品6   | 200.36 |
|    7 | 商品7   | 200.61 |  7 | 商品7   | 200.61 |
|    8 | 商品8   | 200.98 |  8 | 商品8   | 200.98 |
|    9 | 商品9   | 200.06 |  9 | 商品9   | 200.06 |
+------+---------+--------+----+---------+--------+
10 rows in set (0.00 sec)

得嘞~~您瞅瞅!! 看下 explain 分析:

mysql> explain select * from t1 right join t2 on t1.id=t2.id;
+----+-------------+-------+------------+--------+---------------+--------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key    | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+--------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index  | NULL          | name_2 | 42      | NULL       |   10 |   100.00 | Using index |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | id            | id     | 8       | test.t2.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+--------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from t1 join t2 on t1.id=t2.id;
+----+-------------+-------+------------+--------+---------------+--------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key    | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+--------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index  | id            | name_2 | 42      | NULL       |   10 |   100.00 | Using index |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | id            | id     | 8       | test.t2.id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+--------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

加不加 right , 都使用 t2 表作为驱动表了!!

2.2 MySQL的join算法-Nest-Loop-Join原理

mysql只支持一种join算法: Nested-Loop Join(嵌套循环连接)

但是 它又有3中变种:

1). Simple 嵌套循环连接-简单嵌套循环链接连接

现在已经不会走这种模式了; 会直接选择 嵌套循环连接;

image-20200914135816800

2). Index 嵌套循环连接-索引嵌套循环链接连接

在简单 嵌套循环连接的 基础上, 拿每次第一个表的一行记录, 去第二个表的索引中匹配, 而不是扫表;

image-20200914140027180

3). Block 嵌套循环链接-块嵌套循环链接连接

image-20200914140300845

假如上面的t1/t2都没有索引, 这时, mysql也不会使用 简单嵌套循环连接, 而是使用 块嵌套循环连接:

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | id       |            1 | id          | A         |      149999 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          0 | id       |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t2    |          1 | name     |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | name_2   |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | name_2   |            2 | price       | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> drop index id on t1;
Query OK, 150000 rows affected (1.05 sec)
Records: 150000  Duplicates: 0  Warnings: 0

mysql> drop index id on t2;
Query OK, 10 rows affected (0.08 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 join t2 on t1.id=t2.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     10 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 149994 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Using join buffer (Block Nested Loop)

说明, 使用了内存 join buffer, 默认大小是 256k;

优化思路:

  1. 尽可能减少join中的 Nested-Loop 循环总次数;
  2. 优先优化 Nested Loop的内层循环; (比如索引 嵌套循环连接)
  3. 在join语句中被驱动表上建立索引; (就是第二条的实际优化方法)
  4. 在join语句中被驱动表上无法建立索引; 那么在内存充裕的条件下, 充分使用 join buffer的设置;

2.3 Mysql的 inner join的算法选择

t1, t2表特征如下:

mysql> show index from t1;
Empty set (0.00 sec)

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          0 | t2       |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t2    |          1 | name     |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | name_2   |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | name_2   |            2 | price       | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   150000 |
+----------+
1 row in set (0.05 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

2.3.1 case1-大表无索引, 小表有索引:

t1有15条行数据, 没有索引;

t2有10条数据, id上有索引;

  1. 如果join不指定连接条件:
mysql> explain select * from t1 join t2;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+---------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | name_2 | 42      | NULL |     10 |   100.00 | Using index                           |
|  1 | SIMPLE      | t1    | NULL       | ALL   | NULL          | NULL   | NULL    | NULL | 149994 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql会选用 块 嵌套循环连接; 如果指定了 连接条件呢?

mysql会选用无索引表, 作为驱动表, 索引表作为内层循环, 请看:

mysql> explain select * from t1 join t2 on t1.id=t2.id;
+----+-------------+-------+------------+--------+---------------+------+---------+------------+--------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref        | rows   | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+--------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL | NULL    | NULL       | 149994 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | t2            | t2   | 8       | test.t1.id |      1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+------+---------+------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

2.3.2 case2-大表无索引, 小表无索引:

我们把小标t2的id的索引删掉再看:

mysql> alter table t2 drop index t2;
Query OK, 10 rows affected (0.08 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | name     |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | name_2   |            1 | name        | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | name_2   |            2 | price       | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

此时 t2.id 已无索引, 再看看, mysql是怎么选择 Nested-Loop Join的算法的:

mysql> explain select * from t1 join t2 on t1.id=t2.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     10 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 149994 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以看到: 第一个步骤又是 t2 表了, mysql “聪明地” 选择了小标作为驱动表, 在内层嵌套循环连接时, “机智的”使用了 (Block Nested Loop), 我们看到, Block 循环嵌套连接, 使用了 join buffer;

2.4 Nest Loop Join算法小结

事实上, Simple Nested-Loop Join已经不会使用了, 所以, 在实际中; mysql会自行选择:

要么使用 Index Nested-Loop Join, 要么使用 Block Nested-Loop Join;

这取决于join的两个表, 是大表还是小表, 有索引还是没有索引;

  • 如果2个表join, 一个无索引, 一个有索引, 就使用无索引表作为驱动表, 内层嵌套循环表使用有索引的表(Index 循环嵌套算法);
  • 如果2个表join, 都没有索引, 就使用小表作为驱动表, 大表作为内层循环嵌套表, 同时选用Block 嵌套循环算法;

3. 优化思路小结

1). 并发量太高时, 系统性能下降很快, 不要超过2个表join;

2). 复杂的join语句, 需要锁定的资源也更多, 阻塞的其他线程也更多;

3). 复杂的query语句, 拆分成多个简单的, 分步执行;

4). 实在不行, 加热点数据存 Redis等其他 NoSQL组件;

5). 只取出需要的列, 不要 select*

5.1) 取的越多, 传给客户端的越大, 浪费带宽;

5.2) 排序时输出列越多, 浪费内存越大(Using Filesort)

以 t2 (小表, 只有3列, 10行记录), select* 和select name:

mysql> explain select * from t2 order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from t2 order by id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from t2 order by name;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | name | 33      | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

前两个都使用了 filesort: 可以得出结论:

  1. 如果 order by 的字段没有索引, 排序会 Using filesort;
  2. 如果select* 了, 即使order by的字段有索引, 也会 Using filesort;
  3. 如果select的列和order by的列都有索引, 那么就只使用索引 Using index;

5.3) select* 改变执行计划

6). 当where条件可以使用的多个列都有索引时, 选择key_len最短的;

本质就是列的类型的长度(如果建索引没有指定长度的话), 比如 name varchar(100), key_len=103;

varchar另外加3个字节;

7). 尽可能在索引中完成排序-order by 字段加索引;

如果 order by 的字段没有索引, 需要再内存中开辟空间, 甚至还要生成临时文件落盘增加IO开销;

Using filesort