作者：微信小助手
发布时间：2021-09-27T10:14
今天和大家聊一个常见的问题：慢SQL。 通过本文你将了解到以下内容： 后续均以MySQL默认存储引擎InnoDB为例进行展开，话不多说，开搞！ 慢SQL，就是跑得很慢的SQL语句，你可能会问慢SQL会有啥问题吗？ 试想一个场景： 大白和小黑端午出去玩，机票太贵于是买了高铁，火车站的人真是乌央乌央的。 马上检票了，大白和小黑准备去厕所清理下库存，坑位不多，排队的人还真不少。 小黑发现其中有3个坑的乘客贼慢，其他2个坑位换了好几波人，这3位坑主就是不出来。 等在外面的大伙，心里很是不爽，长期占用公共资源，后面的人没法用。 小黑苦笑道：这不就是厕所版的慢SQL嘛！ 这是实际生活中的例子，换到MySQL服务器也是一样的，毕竟科技源自生活嘛。 MySQL服务器的资源(CPU、IO、内存等)是有限的，尤其在高并发场景下需要快速处理掉请求，否则一旦出现慢SQL就会阻塞掉很多正常的请求，造成大面积的失败/超时等。 客户端和MySQL服务端的交互过程简介： 客户端和MySQL服务端的交互过程简图： 俗话说"条条大路通罗马"，优化器的作用就是找到这么多路中最优的那一条。 存储引擎更是决定SQL执行的核心组件，适当了解其中原理十分有益。 InnoDB存储引擎(Storage Engine)是MySQL默认之选，所以非常典型。 存储引擎的主要作用是进行数据的存取和检索，也是真正执行SQL语句的组件。 InnoDB的整体架构分为两个部分：内存架构和磁盘架构，如图： 存储引擎的内容非常多，并不是一篇文章能说清楚的，本文不过多展开，我们在此只需要了解内存架构和磁盘架构的大致组成即可。 InnoDB 引擎是面向行存储的，数据都是存储在磁盘的数据页中，数据页里面按照固定的行格式存储着每一行数据。 行格式主要分为四种类型Compact、Redundant、Dynamic和Compressed，默认为Compact格式。 当计算机访问一个数据时，不仅会加载当前数据所在的数据页，还会将当前数据页相邻的数据页一同加载到内存，磁盘预读的长度一般为页的整倍数，从而有效降低磁盘IO的次数。 MySQL中磁盘的数据需要被交换到内存，才能完成一次SQL交互，大致如图： MySQL的数据是一行行存储在磁盘上的，并且这些数据并非物理连续地存储，这样的话要查找数据就无法避免随机在磁盘上读取和写入数据。 对于MySQL来说，当出现大量磁盘随机IO时，大部分时间都被浪费到寻道上，磁盘呼噜呼噜转，就是传输不了多少数据。 一次磁盘访问由三个动作组成： 对于存储引擎来说，如何有效降低随机IO是个非常重要的问题。 可以实现增删改查的数据结构非常多，包括：哈希表、二叉搜索树、AVL、红黑树、B树、B+树等，这些都是可以作为索引的候选数据结构。 结合MySQL的实际情况：磁盘和内存交互、随机磁盘IO、排序和范围查找、增删改的复杂度等等，综合考量之下B+树脱颖而出。 B+树作为多叉平衡树，对于范围查找和排序都可以很好地支持，并且更加矮胖，访问数据时的平均磁盘IO次数取决于树的高度，因此B+树可以让磁盘的查找次数更少。 在InnoDB中B+树的高度一般都在2~4层，并且根节点常驻内存中，也就是说查找某值的行记录时最多只需要1~3次磁盘I/O操作。 MyISAM是将数据和索引分开存储的，InnoDB存储引擎的数据和索引没有分开存储，这也就是为什么有人说Innodb索引即数据，数据即索引，如图： 说到InnoDB的数据和索引的存储，就提到一个名词：聚集索引。 聚集索引将索引和数据完美地融合在一起，是每个Innodb表都会有的一个特殊索引，一般来说是借助于表的主键来构建的B+树。 假设我们有student表，将id作为主键索引，那么聚集索引的B+树结构，如图： 聚集索引按照如下规则创建： 假如我们要查找id=10的数据，大致过程如下： 非聚集索引的叶子节点中存放的是二级索引值和主键键值，非叶子节点和叶子节点都没有存储整行数据值。 假设我们有student表，将name作为二级索引，那么非聚集索引的B+树结构，如图： 由于非聚集索引的叶子节点没有存储行数据，如果通过非聚集索引来查找非二级索引值，需要分为两步： 我们把通过非聚集索引找到主键值，再根据主键值从聚集索引找对于行数据的过程称为：回表查询。 换句话说：select * from student where name = 'Bob' 将产生回表查询，因为在name索引的叶子节点没有其他值，只能从聚集索引获得。 所以如果查找的字段在非聚集索引就可以完成，就可以避免一次回表过程，这种称为：覆盖索引，所以select * 并不是好习惯，需要什么拿什么就好。 假如我们要查找name=Tom的记录的所有值，大致过程如下： 上述查询就包含了一次回表过程，因此性能比主键查询慢了一倍，因此尽量使用主键查询，一次完事。 出现慢SQL的原因很多，我们抛开单表数亿记录和无索引的特殊情况，来讨论一些更有普遍意义的慢SQL原因和解决之道。 我们从两个方面来进行阐述： 程序员的角度和存储引擎的角度是不一样的，索引写的好，SQL跑得快。 假如表中有1000w记录，其中有status字段表示状态，可能90%的数据status=1，可以不将status作为索引，因为其对数据记录区分度很低。 每个索引都需要占用磁盘空间，修改表数据时会对索引进行更新，索引越多，更新越复杂。 因为每添加一个索引，.ibd文件中就需要多维护一个B+Tree索引树，如果某一个table中存在10个索引，那么就需要维护10棵B+Tree，写入效率会降低，并且会浪费磁盘空间。 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，属于热门字段，为其建立索引非常必要。 对于需要经常使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段建立索引，可以有效借助B+树的特性来加速执行。 主键可以用来创建聚集索引，外键也是唯一的且常用于表关联的字段，也需要建索引来提高性能。 如果数据库表的索引设置比较合理，SQL语句书写不当会造成索引失效，甚至造成全表扫描，迅速拉低性能。 我们在写SQL的时候在某些情况下会出现索引失效的情况： select id from std upper(name) = 'JIM'; select id from std where id+1=10; select id from std where name != 'jim'; select id from std name like '%jim'; 比如：字符串类型索引字段不加引号，select id from std name = 100;保持变量类型与字段类型一致 并不是所有的or都会使索引失效，如果or连接的所有字段都设置了索引，是会走索引的，一旦有一个字段没有索引，就会走全表扫描。 联合索引包含key1，key2，key3三列，但SQL语句没有key1，根据联合索引的最左匹配原则，不会走联合索引。 使用连接代替子查询 对于数据库来说，在绝大部分情况下，连接会比子查询更快，使用连接的方式，MySQL优化器一般可以生成更佳的执行计划，更高效地处理查询 禁止分页查询偏移量过大，如limit 100000,10 使用覆盖索引 多表关联查询时，小表在前，大表在后 在MySQL中，执行from后的表关联查询是从左往右执行的，第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。 MySQL采用从左往右的顺序解析where子句，可以将过滤数据多的条件放在前面，最快速度缩小结果集。 使用小范围事务，而非大范围事务 遵循最左匹配原则 使用联合索引，而非建立多个单独索引 在分析慢SQL之前需要通过MySQL进行相关设置： explain命令只需要加在select之前即可，例如: explain select * from std where id < 100; 该命令会展示sql语句的详细执行过程，帮助我们定位问题，网上关于explain的用法和讲解很多，本文不再展开。 本文从慢SQL的危害、Innodb存储引擎、聚集索引、非聚集索引、索引失效、SQL优化、慢SQL分析等角度进行了阐述。
1.慢SQL的危害
2.SQL语句执行过程
3. 存储引擎和索引的那些事儿
3.1 存储引擎
磁盘预读机制和局部性原理
磁盘和内存的交互
随机磁盘IO
3.2 索引
聚集索引
非聚集索引
4. 慢SQL解决思路
4.1 索引设置原则
4.2 SQL的优化
索引失效
select name from table where key2=1 and key3=2;好的建议
而子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引， 因此效率会更低。
减少select * 借助覆盖索引，减少回表查询次数。
4.3 慢SQL的分析
开启：SET GLOBAL slow_query_log = 1;
开启状态：SHOW VARIABLES LIKE '%slow_query_log%';
设置阈值：SET GLOBAL long_query_time=3;
查看阈值：SHOW GLOBAL VARIABLES LIKE 'long_query_time%'; explain分析SQL
5. 小结