Mysql中慢查询语句分析

序言

学习方法这是大部分人都可以掌握的，但是学习的驱动力可能每个人都不太相同。有的人是真正的热爱，有的人是为生活所迫，而有的人是为了证明自己。

我不算是一个热爱编程的人，至少之前一直不算是，对于自己的职业生涯也没有一个很好的规划，一直不清楚自己适合做哪一行。我爸妈就是想让我找一份清闲的工作，朝九晚五那种且不用加班的，让我努力试一下看能不能进学校，前几个月就一直都在准备行测和自然专技的内容，本来也想做一个分享，但是因为行测的某些内容涉及某些敏感词汇无法发表。慢慢已经下定决定要在技术这条路一直走下去，加油！

下面都是一些基础性的东西，没有什么难度，但MYSQL的内容绝不仅仅只是下面的一个大概，可以深入的东西还是挺多的。

 

MYSQL

Mysql是关系型数据库管理系统RDBMS（Relational Database Management System），在Web应用上十分常用。

 

存储引擎

MYSQL在数据持久化上我们通常都是使用的InnoDB，但除了它以外还包含以下几种存储引擎：

• MyISAM：MySQL 5.0 之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务。
• InnoDB：事务型数据库的首选引擎，支持ACID（Atomicity,Consistency,Isolation,Durability）事务，支持行级锁定（默认隔离级别Repeatable Read）, MySQL 5.5 起成为默认数据库引擎。
• BDB：源自Berkeley DB，事务型数据库的另一种选择，支持Commit 和Rollback 等其他事务特性。
• Memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失。
• Merge：将一定数量的 MyISAM 表联合而成一个整体，在超大规模数据存储时很有用。
• Archive：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差。
• Federated：将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用。
• Cluster/NDB：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用。
• CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。
• BlackHole：黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继。
• EXAMPLE：存储引擎是一个不做任何事情的存根引擎。它的目的是作为 MySQL 源代码中的一个例子，用来演示如何开始编写一个新存储引擎。同样，它的主要兴趣是对开发者。EXAMPLE 存储引擎不支持编索引。
• 其他：MySQL 的存储引擎接口定义良好。有兴趣的开发者可以通过阅读文档编写自己的存储引擎。

注意：InnoDB在默认隔离级别下仍然可能出现幻读（已提交事务B对事务A产生影响），InnoDB使用MVCC和next-key locks解决幻读问题，MVCC（Multi-Version Concurrency Control）解决普通读（Consistent Read，快照读）的幻读，next-key locks（锁住本条记录以及索引区间）解决当前读（Locking Read）情况下出现的幻读。

应用架构

• 单点（Single）：适合小规模应用
• 复制（Replication）：适合中小规模应用
• 集群（Cluster）：适合大规模应用

Mysql架构与应用

主从复制结构中读写分离，主写从读：

 

索引

索引提供指针以指向存储在表中指定列的数据值，再根据制定的排序次序排列这些指针。使用索引类似于使用书的目录，利用索引可以快速查找到所需要的信息。

在数据库中由于数据存储在数据库表中，因此索引是创建在数据库表对象上的，由表中的一个字段或多个字段生成的键组成，这些键存储在数据结构（B-树或哈希表）中，通过Mysql可以快速有效地查找与键值相关联的字段，根据索引的存储类型，可以将索引氛围B-树索引（BTREE）和哈希索引（HASH）。InnoDB和MyISAM存储引擎支持B-树索引。

《Mysql数据库开发实践》一书中是提到Mysql使用InnoDB采用的是B-树，但网上有很多都说是B+树，比如这篇文章：https://zhuanlan.zhihu.com/p/139002393

B-树是一颗平衡多路搜索树，类似于平衡二叉树，不过每个节点可以有更多的子节点，查找性能接近二分查找，使用平衡多路搜索树可以降低平衡二叉树的深度。B+树是B-树的变体，所有关键字存储在叶子结点且所有叶子结点增加一个链指针，查询时间复杂度O(logn)。B+树很好的运用了空间局部性原理（如果存储器某个位置被访问，它附近的位置也会被访问），InnoDB存储引擎的最小存储单元是页（Page），大小16K，一次IO就是读一页。因为索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数所以采用B树结构。

我们可以去Mysql官网看文档：https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html，可以看到文档中提到“Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.”即Mysql大部分索引都是存储在B-树（B树）结构上。除了某些特殊的数据类型使用R树，内存表支持Hash索引，InnoDB使用倒排表用于全文索引。

 

索引类别

1. 普通索引：（由关键字 KEY 或 INDEX 定义的索引）的任务是加快对数据的访问速度。因此，应该只为那些最经常出现查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。
2. 索引：普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个索引。
3. 主索引：必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引区别是：前者在定义时使用的关键字是 PRIMARY 而不是 UNIQUE。
4. 外键索引：如果为某个外键字段定义了一个外键约束条件，MySQL 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
5. 复合索引：索引可以覆盖多个数据列，如像 INDEX (columnA, columnB) 索引。这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX(columnA, columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX (A，B，C) 可以当做 A 或 (A,B) 的索引来使用，但不能当做 B、C 或 (B,C) 的索引来使用。复合索引也叫联合索引，遵循最左匹配原则（where后的索引字段可以打乱，MySQL的查询优化器可以优化成索引识别的形式）。

主索引和唯一索引是有区别的，Mysql Innodb中的索引数据结构是 B+ 树，普通索引，也叫做辅助索引，叶子节点存放的是主键值。主键上的索引叫做聚集索引，表里的每一条记录都存放在主键的叶子节点上。唯一索引本质上是辅助索引，然后加了唯一约束。

 

SQL优化

项目初期数据量相对较少，很多问题可能并不会暴露出来，比如分页查询使用物理分页通过LIMIT关键字取偏移量后面的数据，但是数据量一大，效率就会越来越低。SQL执行效率会对程序运行效率产生影响，所以SQL语句的优化就变得很有必要。

SQL优化的方法总结：

• 查询库表避免全表扫描，首先应考虑在where和order by涉及的列上建立索引。
• 尽量避免where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。
• 尽量避免在 where 子句中使用!=或<>操作符。
• 尽量避免在 where 子句中使用 or 来连接条件。
• in 和 not in 也要慎用，否则也会导致全表扫描。
• 模糊查询Like效率很低也要慎用，像 like ‘%...%’ 是无法使用索引的。
• 尽量避免在 where 子句中对字段进行表达式、函数或运算操作，这也会导致引擎放弃使用索引而进行全表扫描。
• 使用复合索引遵循最左匹配原则，必须使用最左开始连续匹配的字段才能使用到索引。eg：联合索引(a,b,c)，则where子句a='?' and b='?' and c='?'；a='?' and b='?';a='?'都是走了索引，a='?' and c='?'则只有a走索引，c='?' and b='?' and a='?'也走了联合索引（查询优化器自动优化顺序）。
• 通常EXISTS效率要比IN高，因为IN不走索引，具体情况具体使用。IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。插入时EXISTS可以防止重复插入。
• 并不是所有索引对查询都是有效的，当索引列有大量数据重复时，SQL查询可能不会去利用索引。比如性别字段建立索引对查询效率没有什么提升。
• 索引不是越多越好，使用联合索引需慎重，索引在提高 select 查询效率同时，也降低了 insert 及 update 的效率。
• 尽量使用数字型字段代替字符串字段，比如使用tinyint(4)的整数定义不同含义的内容，而不直接使用字符串，只需要在提供接口的时候把字段含义规约好。
• 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间。
• 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
• 避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。
• 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table（先创建空临时表再插入），避免造成大量 log ，以提高速度；
• 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table(删除表中的所有行，但表结构及其列、约束、索引等保持不变，auto_increment计数器也清零) ，然后 drop table（删除表结构及所有数据，并将表所占用的空间全部释放） ，这样可以避免系统表的较长时间锁定。    
• 尽量避免使用游标CURSOR，游标只能用于存储过程，存储过程处理完成后，游标就“消失”了。在性能上，游标会吃更多的内存，减少可用的并发，占用宽带，锁定资源以及更多的代码量。
• 与临时表一样，游标并不是不可使用，但通常游标都不是首选项。不过对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
• 尽量避免大事务操作，提高系统并发能力。
• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

 

分析慢查询语句

除了SQL语句的优化，我们通常也需要分析一些可能造成慢查询的语句，我们先看Mysql官网（8.0版本）对long_query_time参数的定义，long_query_time变量默认值时10秒，作用域是全局和Session（session会话）。

可以通过Explain关键字（Explain关键字模拟优化器执行SQL查询语句）分析慢查询语句。

举个例子：对基于角色的访问权限控制模型的表结构进行下面的SQL语句，

Explain SELECT `name` FROM `user` where id in (select user_id from `user_role` where role_id in (SELECT id from role where role.name = 'p8'))

上面的分析结果字段含义如下：

• id：从上往下顺序执行，id相同则为一组，id越大优先级越高，越先被执行。
• select_type：表示当前记录的select扮演了什么角色，如下

SIMPLE	PRIMARY	UNION	SUBQUERY	DERIVED	UNION RESULT	DEPENDENT SUBQERY	MATERIALIZED
简单的select查询，查询中不包含子查询或者UNION	查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY	若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED	在SELECT或WHERE列表中包含了子查询	在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中	从UNION表获取结果的SELECT	如果包含子查询的查询语句不能够转为对应的半连接semi-join的形式，并且该子查询是相关子查询，该子查询的第一个select关键字代表的查询类型就是dependent subquery	子查询执行后的物化表与外层查询进行连接查询时

 

 

 

 

 

• table：指的就是当前执行的表。
• partition：看当前表是否使用了分区。
• type：查询使用了哪种类型，查询级别system(系统表) > const(常量通过索引一次找到) > eq_ref(唯一性索引扫描) > ref(非唯一性索引扫描) > range(只检索给定范围的行) > index(Full Index Scan) > all(Full Table Scan)
• possible_keys：显示可能应用在这张表中的索引，一个或多个。
• key：实际使用的索引，如果为NULL，则没有使用索引。
• key_len：索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
• ref：显示索引的哪一列被使用。
• rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
• filtered：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
• Extra：包含不适合在其他列中显式但十分重要的额外信息，如下

Using filesort	Using temporary	Using index	Using where	Using join buffer	impossible where	select tables optimized away	distinct
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取	使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。	表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。	表明使用了where过滤	表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些	where子句的值总是false，不能用来获取任何元组	在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

 

 

 

 

 

 

上面的例子里，第一行是select的子查询语句通过全表扫描进行查询，没有走索引。第二行则是从user表中查对应的信息通过唯一性索引进行查询，走了主键索引（索引占用4字节），索引引用的是子查询结果集的user_id。第三行和第四行记录都是子查询执行后与外层查询进行连接查询，第三行查的是role表，第四行查的是user_role表。

其实这个查询就是查询为P8级别的角色的用户，这样去查效率较低。可以通过JOIN把多个表关联起来，如下可以达到相同的效果，再EXPLAIN一下，结果如下，

SELECT u.`name` FROM `user` AS u JOIN `user_role` AS ur ON u.id = ur.user_id JOIN role as r on r.id = ur.role_id WHERE r.`name`='P8'

 

总结

上面只是简单的Mysql基本内容和一些例子，只是起到一个引导作用。Mysql查询性能问题大部分都是SQL语句效率太低，也要看业务的数据量大小，具体问题具体分析。

补充：索引覆盖（Covering Index）即不需要回表操作，在explain分析时显示为Using index，MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。

另外，在实战中，比如使用Springboot + mybatis-plus + druid搭建，需要分析自己的SQL语句之前可以在配置中加上如下配置开启SQL语句输出打印，

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

在跑单测试就可以输出下面的预编译语句和影响的行，然后粘贴SQL去数据库进行分析。