mysql优化个人理解

红太狼 2022-05-24 04:21 143阅读 0赞

## mysql优化个人理解 ##

**MySQL逻辑架构**

![MySQL逻辑架构，来自：高性能MySQL][MySQL_MySQL]

> MySQL逻辑架构，来自：高性能MySQL

mysql支持多种存储引擎，这里只讨论innodb。存储引擎负责和底层打交道，类似linux。中间的服务层通过API与存储引擎通信，接口隔离实现。

**MySQL查询过程**

![MySQL查询过程][MySQL]

> MySQL查询过程

总结一下MySQL整个查询执行过程，总的来说分为6个步骤：

1.  客户端向MySQL服务器发送一条查询请求
2.  服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3.  服务器进行SQL解析、预处理
4.  优化器生成对应的执行计划
5.  MySQL根据执行计划，调用存储引擎的API来执行查询
6.  将结果返回给客户端，同时缓存查询结果

复制比较合适的做法：

翻看回前面mysql查询的图，我们发现，除了sql，请求，其他事基本是mysql内部完成的，我们试试总结几个靠谱的经验。

1 表设计小而简单，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少，网络通信成本更低。

2 **尽量保持查询简单且只返回必需的数据**，减小通信间数据包的大小和数量，在查询中尽量避免使用SELECT \*以及加上LIMIT限制，IO总是慢的，何况是多余的IO。

另一个参考的理由： MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。

3 **慎用mysql缓存**，缓存命中条件要求很高，并且数据更新需要刷新缓存，生产环境基本不可用，特别是写密集型应用。（具体分析看引用文章）我司线上db缓存是关闭的。

4 **不使用用户自定义函数、存储过程**。MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。udf，存储过程的操作成本无法准确统计，使用可能导致mysql选择非最优的执行计划。我司规定禁用自定义函数、存储过程。

**索引**

mysql使用索引的数据结构为B+树，所有的关键字都存在叶子节点，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次，所有的叶子节点由指针连接。  
生产应用中，树的深度一般不超过3层，m一般超过100（m叉树）。

![B+树][B]

一般索引较大，不能常驻内存，以文件的形式维护。mysql索引文件的大小为页的整数倍，操作系统对硬盘读取时会进行预读，页是连续被读进去的最小单位。比如将节点的大小设置为1页，则每次只需要产生一次系统io就能读取完一个节点的数据。

> 页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后一起返回，程序继续运行。

直接继续总结：

1 MySQL不会使用索引的情况：非独立的列  
“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：  
select \* from where id + 1 = 5  
也可以反着用，比如order by 字段1，不想走索引，可以强制改成order by 字段1+0

2 多列索引和索引顺序  
同张表存在多个索引，mysql无法识别哪个索引效率更高，应该通过多列索引替代多个索引。索引顺序以索引选择性高的优先。

> 索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1，这时最好的索引选择性，性能也是最好的。

3 避免多个范围条件  
因为无法同时使用两个索引

4 覆盖索引  
如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

a 索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量  
b 索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

实质上，就是只通过对索引文件进行查找，就能返回查询所需的字段。量小并且几乎都是顺序读，性能强悍。

我们平时使用索引查询，远远没这么理想。索引一般建立在关键业务字段上。比如查询条件带上关键业务字段的范围，定位到一段索引文件（几乎是连续的）。之后是扫描索引文件的行，一行行回表查行记录（随机读，比连续读写性能差6000倍）

5 聚簇索引  
聚簇索引，实际存储的循序结构与数据存储的物理机构是一致的，比如说最长用的主键id。  
我们试着理解一种情况，为啥全表扫描，有时比走索引性能还要快?  
因为全表扫描是顺序读，直接拿到树的最左边节点，最右边节点，就能过滤出范围（大神勘正，这里只是简单理解）  
走索引时，如果不是覆盖索引，是需要产生回表查询的，大量的随机IO加起来，就远远不如全表扫描。之前一个经验数字是5%，如果通过索引过滤出来的数据量超过全表5%，则通过该索引查询，性能不如全表扫描。

6 批量处理  
查询时尽量用批量查询，可以减少客户端和服务端握手的消耗，如果用in，控制in参数个数，阿里规范是要求1000以内。

批量insert尽量采用合并事务的方式，如果条件允许，有序执行（最好是按id或者唯一索引有序执行）  
合并事务的sql，合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

![性能对比][70]

批量更新，批量删除雷同。

另外，批量事务，也要控制好事务的大小，一个原因是事务大，整体事务耗时大，中间发生回滚，成本就提高。另一个原因是，提交事务，mysql需要维护undo日志，undo日志是保持在共享表空间中，被“撑大”的共享表空间是不会也不能自动收缩的，撑大的过程（脑补的）应该类似jvm的内存申请，耗时较大，一般批量事务控制在1w以内。（经验结论，大神补）

参考：  
[万字总结：学习MySQL优化原理，这一篇就够了！][MySQL 1]  
[MySQL实现批量插入以优化性能的教程][MySQL 2]  
[MySQL 中的重做日志，回滚日志以及二进制日志的简单总结][MySQL 3]

[MySQL_MySQL]: /images/20220524/4bc6629f07b14aa2ba2b20aa356b8745.png
[MySQL]: /images/20220524/e6618dcb90f142999db71ca2455f2d82.png
[B]: /images/20220524/daff01aaa03543d19a40e66a3ea381b0.png
[70]: /images/20220524/de2745bbd2b84249a12fd7cd73c4db67.png
[MySQL 1]: https://mp.weixin.qq.com/s/OUWW9_4Biudi7zYKwNNAhw
[MySQL 2]: http://www.jb51.net/article/63895.htm
[MySQL 3]: https://mp.weixin.qq.com/s/vhVDGol2P5EltPtytX2QkQ