Mysql中慢查询语句分析_org

• 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：包含不适合在其他列中显式但十分重要的额外信息，如下

最后

针对最近很多人都在面试，我这边也整理了相当多的面试专题资料，也有其他大厂的面经。希望可以帮助到大家。

最新整理面试题

上述的面试题答案都整理成文档笔记。也还整理了一些面试资料&最新2021收集的一些大厂的面试真题

最新整理电子书

最新整理大厂面试文档

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

也还整理了一些面试资料&最新2021收集的一些大厂的面试真题

最新整理电子书

[外链图片转存中…(img-CoCgfDoM-1714423610836)]

最新整理大厂面试文档

[外链图片转存中…(img-AikR9Bdc-1714423610836)]

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录