Mysql优化高级篇

最新推荐文章于 2024-04-18 03:29:48 发布

Mr_代码先生

最新推荐文章于 2024-04-18 03:29:48 发布

阅读量296

点赞数 1

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_62866676/article/details/126926355

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

explain性能分析

MySQL Query Optimizer：Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划，但不见得最优

MySQL常见瓶颈（通过cpu 、io 、服务器的硬件进行分析）

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

通过explain的关键字进行分析

explain的使用：explain + sql语句

EXPLAIN能干嘛？

可以查看以下信息：

id：表的读取顺序。
select_type：数据读取操作的操作类型。
possible_keys：哪些索引可以使用。
key：哪些索引被实际使用。
ref：表之间的引用。
rows：每张表有多少行被优化器查询。

id（查询序列号）

表示查询中执行select子句或操作表的顺序

三种情况：

id相同，执行顺序由上至下，t1->t3->t2（id都是1，执行顺序从上到下）

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行，如果没有子查询，那么sql语句中看看有没有括号，有括号的先执行（在内层的子查询序列，其等级越高，t3表在t2表的子查询中，所以t3的优先级高于t2，t2表在t1表的子查询中，所以t2的优先级高于t1）

id相同不同，同时存在，id如果相同，可以认为是一组，从上往下顺序执行，id值越大，优先级越高，越先执行，下面图中表<derived2>示该表是从id=2的表t3中衍生出来的虚表，表<derived2>与表t3的id相同，所以按顺序执行，t3--> <derived2> -->t2

select_type（查询类型）

主要是用于区别普通查询、联合查询、子查询等的复杂查询

具体的类型有：

SIMPLE - 简单的select查询,查询中不包含子查询或者UNION。
PRIMARY - 查询中若包含任何复杂的子部分，最外层查询则会被标记。（最外层的查询）
SUBQUERY - 在SELECT或WHERE条件中包含了子查询。（内层）越内层等级越高，越先执行

DERIUED - 在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
UNION - 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中外层SELECT将被标记为：DERIVED。
UNION RESULT - 使用了union合并两个查询以后会出现

table(显示数据是来自哪张表的)

type（访问类型）

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

工作中常见的指标主要有：
system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了，const用于比较主键索引或者unique索引。因为只匹配一行数据，所以很快。比如将主键置于where列表中，MySQL就能将该查询转化为一个常量，select语句的type类型就会出现const

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描（下图，t2表全表扫描以后，其主键id与t1表的主键id匹配的数据只有一行，那么t1表的type类型就是eq_ref, 而t2表的type为All这是避免不了的）

ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。（下图，col1列是一个普通索引，根据索引查询出来col1 = "ac"的数据可能会有很多行，那么这条select查询语句的type类型就会为ref）

range：只检索给定范围的行，使用一个索引来选择行。where语句中索引列出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的（下图，select后面查询的是id这个主键，那么mysql会去扫面索引树不会去进行全表扫描，select查询语句的type类型就是index）

all：Full Table Scan，将遍历全表以找到匹配的行（也就是通过 select * 全部数据读取）

possible_keys 、key 和 key_len（可能用到索引、实际用到索引、长度）

possible_keys（理论上要用到的索引）：显示可能要应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引会被列出，但不一定被查询实际使用
key（实际用到的索引）：实际使用的索引。如果为NULL，则表示没有使用索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中
有一种情况：

理论上不用索引，实际用到了

分析：

因为设置了（col1,col2）组合索引，select col1,col2......要查询的字段以及顺序刚好与组合索引（col1,col2）一致，所以就使用了全表扫描组合索引树（col1,col2）

key_len （估计用到的长度）
表示索引中使用的字节数，通过where后面使用到的索引列计算而得。在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

分析：

第一个查询where后面的条件只有一个，第二个查询where后面的条件有两个，所以key_len的长度更大，长度虽然更大，但是开销更大
如果更少的查询条件与更多的查询条件得到的结果是一样的，那么应该选择使用更少的查询条件，来减少开销

2.4.5 ref（条件查询）

显示where条件后面索引列使用到的条件是什么，如果是常量就为ref就会出现const，如果是另一个表的列，那么久ref就会出现数据库.表.列这种形式

结果分析

由key_len可知t1表的组合索引（col1，col2）被充分使用，col1匹配t2表的col1，t1表的col2匹配了一个常量，即 ‘ac’，以t2表为驱动，全表扫描t2表查找出col1列来与t1.col1匹配，再通过and条件连接t1.col2 = "ac"条件，所以ref就会出现shared(数据库).t2(表).col1(列)和const(常量)

rows（行数）

找到目标数据所需要读取的行数。（越小越好）

Extra

不适合在其他列中显示但十分重要的额外信息，有下面几种情况：

Using filesort（低效，重点关注）：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件内排序"。

结果分析

t1表有一个组合索引（col1,col2,col3）,第一个查询因为order by 后面跟的是col3，违背了最左匹配原则，order by 后面必须先要有索引列a，才会避免出现using filesort

Using temporary（更低效，重点关注）：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表，常见于排序order by和分组查询group by。临时表対系统性能损耗很大。

Using index（好，重点关注）：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

结果分析

t1表设置了组合索引（col1,col2）,第一个查询使用col1列作为条件，遵询组合索引的最左匹配原则，所以会去扫描组合索引树（col1,col2），而select后面查询的列col2刚好在组合索引树中，不需要再回表了，所以Extra种 using where,using index 同时出现
第二个查询没有where条件，相当于就是读取t1表数据，没有进行查找，只要select后面的列与组合索引（col1,col2）的顺序保持一致，或者select后面的列包含在组合索引（col1,col2）中，Extra都会只出现using index
上面都使用到了覆盖索引：
- 除了主键索引之外的其他索引都叫辅助索引，也叫二级索引。辅助索引的叶子节点除了存储自身的索引键值以外，还存储了主键索引的键值，因此辅助索引扫描完之后，如果没有select后面的字段，那么还会根据主键索引的键值去扫描主键索引，这也叫回表
- 如果查询的列（select后面的字段）恰好都包含在辅助索引叶子节点中，就不会再回表了，这也叫覆盖索引
  - 例如：
       为表的age字段建立了索引
       执行语句：explain select age from user where age = 10;
       查询到的结果：Extra的值为 Using index表示使用了覆盖索引