排序优化

永远小表驱动大表

#连接5次 每次查询1000条数据
for i in range(5):
    for j in range(1000):
        pass

#连接1000次 没次查询5条数据
for i in range(1000):
    for j in range(5):
        pass

可见上面这个要好

order by优化

order by子句,尽量使用index方式排序,避免使用filesort方式排序

• 建表插入数据

create table tbla(
    age int,
    birth timestamp not null
);

• 插入数据

insert into tbla(age,birth) values(22,now());
insert into tbla(age,birth) values(23,now());
insert into tbla(age,birth) values(24,now());

• 建立索引

create index idx_tbla_agebrith on tbla(age,birth);

分析
MySQL支持两种方式的排序,filesort和index,index效率高,MySQL扫描索引本身完成排序。filesort方式效率较低

order by 满足两种情况下,会使用index方式排序

• order by 使用索引最左前列（满足最佳左前缀法则）
• 使用where子句与order by子句条件组合满足最左前列（满足最佳左前缀法则）

Filesort 是在内存中还是在磁盘中完成排序的？

MySQL中的Filesort并不一定是在磁盘文件中进行排序，也有可能在内存中进行排序，内存排序还是磁盘排序取决于排序的数据大小和sort_buffer_size 配置的大小。

• 如果“排序的数据大小” < sort_buffer_size :内存排序
• 如果“排序的数据大小” > sort_buffer_size :磁盘排序

filesort有两种算法-双路排序和单路排序

双路排序，MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表读取对应的数据输出。

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

优化策略调整MySQL参数

增加sort_buffer_size参数设置
增大max_lenght_for_sort_data参数的设置

提高order by的速度

• order by是select * 是一个大忌，只写需要的字段
  • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用改进后的算法--单路排序
  • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O
• 尝试提高sort_buffer_size
• 尝试提高max_length_for_sort_data

练习

索引 a_b_c(a,b,c)

order by a,b（Using index）
order by a,b,c（Using index）
order by a desc,b desc,c desc（Using index）（排序顺序同一顺序）

where a = const order by b,c（Using index）
where a = const and b = const order by c（Using index）
where a = const and b > const order by b,c（Using index）

where a = const and b > const order by c（Using filesort）
order by a asc,b desc,c desc  （Using filesort）（排序顺序不一样有升序，有降序）
where g = const order by b,c   （Using filesort）
where a = const order by c    （Using filesort）
where a = const order by by a,d（Using filesort）

分页查询优化

很多时候，业务上会有分页操作的需求，对应的SQL类似下面这条

select a,b,c from t1 limit 10000,10;

表示从表 t1 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

分析两种情况的分页查询

• 根据自增且连续主键排序的分页查询
• 查询根据非主键字段排序的分页查询

根据自增且连续主键排序的分页查询

首先来看一个根据自增且连续主键排序的分页查询的例子

select * from t1 limit 99000,2;

image.png

该 SQL 表示查询从第 99001开始的两行数据，没添加单独 order by，表示通过主键排序。我们再看表 t1，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 99001开始的两行数据，如下：

select * from t1 where id >99000 limit 2;

image.png

查询的结果是一致的。我们再对比一下执行计划：

image.png

原 SQL 中 key 字段为 NULL，表示未走索引，rows 显示 99965，表示扫描的行数 99965行；
改写后的 SQL key 字段为 PRIMARY，表示走了主键索引，扫描了1000行。

显然改写后的 SQL 执行效率更高。

但是，这条 SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图的实验（整个实验过程为：先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL）：

image.png

可以发现两条 SQL 的结果并不一样，因此，如果主键不连续，不能使用上面描述的优化方法。
另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：

• 主键连续且自增
• 结果是按照主键排序的

查询根据非主键字段排序的分页查询

再看一个根据非主键字段排序的分页查询，SQL 如下：

select * from t1 order by a limit 99000,2;

image.png

查询时间是 0.08 秒。

我们来看下这条 SQL 的执行计划：

image.png

其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下

select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id = g.id;）

image.png

需要的结果与原 SQL 一致，执行时间 0.02 秒，是原 SQL 执行时间的四分之一，我们再对比优化前后的执行计划：

image.png

对于其它一些复杂的分页查询，也基本可以按照这两个思路去优化，尤其是第二种优化方式。第一种优化方式需要主键连续，而主键连续对于一个正常业务表来说可能有点困难，总会有些数据行删除的，但是占用了一个主键 id。

MySQL整体优化思路

硬件相关优化

在 MySQL 整体的优化环节中，硬件相关的优化必不可少，因此首先来聊聊这一方面的优化策略：

CPU相关

• 关闭CPU节能，设定为最大性能模式
  原因是：考虑到在高并发之前没有任何连接的情况，机器可能会处于节电模式，高并发场景来临时可能导致处理不过来新的请求。

• 配置合理的CPU核数，和选择合适的CPU主频
  原因是：
  CPU 核数越多，支持的并发也越高；
  CPU 主频越高，处理任务的速度越快。

内存相关

内存对MySQL数据库影响是非常大的。InnoDB 使用 InnoDB buffer pool 缓存数据、索引等内容，从而加快访问速度。因此MySQL运行的物理机上，内存配置也是比较重要的。

在应用数据库实例前，应该预估活跃的数据大小，然后根据这个合理配置数据库服务器内存大小。

磁盘相关

对于 OLTP 的数据库，一般场景是IO密集型的操作。因此，对于这种情况，应该把更多的注意力放在提高磁盘IO上。

对于磁盘相关的优化，这里聊聊几种方法：

• 使用 SSD(固态硬盘) 或者 Pcle SSD 设备；
  为什么 SSD 比传统机械硬盘快？
  传统的机械硬盘需要耗费长时间的磁头旋转和定位来查找数据。
  而 SSD 其内部是由闪存组成的。闪存延迟低、功耗低。
  因此 SSD 比传统机械硬盘更快。

系统层面优化

硬件层优化的差不多了之后，系统层部分配置也应该去做一些优化。这里就来介绍部分系统层面的优化方法：

调整 I/O 调度算法
MySQL 运行的物理机上，I/O 调度算法建议使用：deadline/noop 尽量不适用CFQ
原因是 CFQ 把 I/O 请求按照进程分别放入进程对应的队列中。CFQ 的公平是针对进程而言，每一个提交 I/O 请求的进程都会有自己的 I/O 队列，以时间算法为前提，轮转调动队列，默认从当前队列中取出 4 个请求处理，然后处理下一个队列的 4 个请求，确保每个进程享有的 I/O 资源是均衡的。因此高并发场景，CFQ 很可能会导致 I/O 的响应缓慢。（参考《深入浅出 MySQL》第二版：22.6 调整 I/O 调度算法）

文件系统选择

优先选择xfs和etx4，坚决不用etx3

原因是：etx3在fsck是需要耗费大量时间，文件越多，时间越长

调整内核参数

vm.swappiness ≤ 10

降低使用 swap 的概率，但是尽量不要设置为 0，可能引起 OOM。

vm.dirty_ratio ≤ 5

这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如10%），系统不得不开始处理缓存脏页（因为此时脏页数量已经比较多，为了避免数据丢失需要将一定脏页刷入外存）；在此过程中很多应用进程可能会因为系统转而处理文件 IO 而阻塞。

vm.dirty_background_ratio ≤ 10

避免因为 IO 压力瞬间飙升导致内核进程卡死，操作系统 hung 住。这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时，就会触发 pdflush/flush/kdmflush 等后台回写进程运行，将一定缓存的脏页异步地刷入外存。

MySQL 层优化

在之前的部分章节中，我们知道了，MySQL 自身很多地方是可以优化的，这里再对 MySQL 层的优化做一个总结：

参数优化

在启动 MySQL 之前，一些参数合理的设置，可以大大提升 MySQL 的性能。这里就来介绍一部分相对比较重要的参数：

innodb_buffer_pool_size

该参数控制 InnoDB 缓存表和索引数据的内存区域大小。对性能影响非常大，建议设置为机器内存的 50-80%。

innodb_flush_log_at_trx_commit

InnoDB 的 redo 日志刷新方式，对 InnoDB 的影响会很大。

sync_binlog

控制累积多少个事务后才将二进制日志 fsync 到磁盘。

innodb_file_per_table

开启独立表空间。

max_connection

最大连接数。不能设置的过小，防止客户端连接失败；也不能设置的过大，防止数据库内存资源过多消耗。

long_query_time

慢查询时间阀值。

query_cache_type
query_cache_size

建议这两个参数都设置为 0。

MySQL设计优化

• 使用InnoDB存储引擎，不建议使用MyISAM存储引擎
• 预估表数据量和访问量，如果数据量和访问量比较大，则需要提前考虑分库分表
• 指定合适的数据库规范，在设计表、执行SQL语句时按照数据库规范来进行

总结

具体从三个大方面聊到了优化方式：硬件、操作系统、MySQL server。

硬件方面需要考虑的优化内容是：

• CPU
• 内存
• 磁盘

操作系统层面需要考虑的优化为

• 调整合适的 I/O 调度算法
• 选择合适的文件系统；
• 调整部分会影响 MySQL 的内核参数。

在 MySQL 层

• 调整部分会影响性能的参数；
• MySQL 设计时的优化。