Python之MySQL优化（下）

1.0 排序优化

1.1 永远是小表驱动大表

ordel by 优化

#ordel 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_agebirth on tbla(age,birth);

MySQL⽀持两种⽅式的排序,filesort和index,index效率⾼,MySQL扫描索引本 身完成排序。filesort⽅式效率较低

order by 满⾜两种情况下,会使⽤index⽅式排序 1.order by 语句使⽤索引最左前列 2.使⽤where⼦句与order by⼦句条件组合满⾜索引最左前列

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

MySQL 中的 Filesort 并不⼀定是在磁盘⽂件中进⾏排序的，也有可能在内存 中排序，内存排序还是磁盘排序取决于排序的数据⼤⼩和 sort_buffer_size 配 置的⼤⼩。 如果 “排序的数据⼤⼩” < sort_buffer_size: 内存排序 如果 “排序的数据⼤⼩” > sort_buffer_size: 磁盘排序

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

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

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

• 优化策略调整MySQL参数

1 增加sort_buffer_size参数设置 
2 增⼤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

2.0 分页查询优化

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

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

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

2.1 分析两种情况的分⻚查询

• 根据⾃增且连续主键排序的分⻚查询
  • ⾸先来看⼀个根据⾃增且连续主键排序的分⻚查询的例⼦

		select * from t1 limit 99000,2;
		#该 SQL 表示查询从第 99001开始的两⾏数据，没添加单独 order by，表示通 过主键排序。我们再看表 t1，因为主键是⾃增并且连续的，所以可以改写成按 照主键去查询从第 99001开始的两⾏数据，如下：
		select * from t1 where id >99000 limit 2;
		#查询的结果是⼀致的
		#原 SQL 中 key 字段为 NULL，表示未⾛索引，rows 显示 99965，表示扫描 的⾏数 99965⾏； 改写后的 SQL key 字段为 PRIMARY，表示⾛了主键索引，扫描了1000⾏	
		#显然改写后的效率更高（自行实验一下，我就不插图了）

- 但是，这条 SQL 在很多场景并不实⽤，因为表中可能某些记录被删后，主键空 缺，导致结果不⼀致，
- 如下实验（整个实验过程为：先删除⼀条前⾯的记 录，然后再测试原 SQL 和优化后的 SQL），可以发现两条 SQL 的结果并不⼀样，因此，如果主键不连续，不能使⽤上⾯描 述的优化⽅法。
- 另外如果原 SQL 是 order by ⾮主键的字段，按照上⾯说的⽅法改写会导致两 条 SQL 的结果不⼀致。所以这种改写得满⾜以下两个条件： 
	- 主键⾃增且连续 
	- 结果是按照主键排序的

• 查询根据⾮主键字段排序的分⻚查询
  • 再看⼀个根据⾮主键字段排序的分⻚查询，SQL 如下：

select * from t1 order by a limit 99000,2;
#查询结果用时0.08秒
#其实关键是让排序时返回的字段尽可能少，所以可以让排序和分⻚操作先查出 主键，然后根据主键查到对应的记录，SQL 改写如下：
select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id = g.id;

#结果与原 SQL ⼀致，但执⾏时间为 0.02 秒，是原 SQL 执⾏时间的四分之 ⼀，我们再对⽐优化前后的执⾏计划：

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

3.0 MySQL整体优化思路

3.1 硬件相关优化

3.1.1 CPU相关

• A.关闭 CPU 节能，设定为最⼤性能模式。
  • 原因是：考虑到在⾼并发之前没有任何连接的情况，机器可能会处于节电模 式，⾼并发场景来临时可能导致处理不过来新的请求
• B.配置合理的 CPU 核数和选择合适的 CPU 主频。
  • 原因是： CPU 核数越多，⽀持的并发也越⾼； CPU 主频越⾼，处理任务的速度越快。

3.1.2 内存相关

内存对 MySQL 数据库影响是⾮常⼤的。InnoDB 使⽤ InnoDB buffer pool 缓 存数据、索引等内容，从⽽加快访问速度。因此 MySQL 运⾏的物理机上，内 存配置也是⽐较重要的，在应⽤数据库实例前，应该预估活跃的数据⼤⼩，然后根据这个合理配置数据 库服务器内存的⼤⼩。

3.1.3 硬盘相关
对于 OLTP 的数据库，⼀般场景是 IO 密集型的操作。因此，对于这类情况， 应该把更多的注意⼒放在提⾼磁盘 IO 上。

• 对于磁盘相关的优化，这⾥聊聊⼏种⽅法：
  • 1 使⽤ SSD(固态硬盘) 或者 Pcle SSD 设备；
    • 为什么 SSD ⽐传统机械硬盘快？ 传统的机械硬盘需要耗费⻓时间的磁头旋转和定位来查找数据。 ⽽ SSD 其内部是由闪存组成的。闪存延迟低、功耗低。 因此 SSD ⽐传统机械硬盘更快。
  • 2 系统层⾯优化
    当硬件层优化的差不多了之后，系统层部分配置也应该去做⼀些优化。这⾥就 来介绍部分系统层⾯的优化⽅法：
    • 2.1 调整 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 调度算法）。
    • 2.2 ⽂件系统选择
      优先选⽤ xfs 或 ext4，坚决不⽤ ext3。 原因是： ext3 在 fsck 时需要耗费⼤量时间，⽂件越多，时间越⻓ 。
    • 2.3 调整内核参数