文章首发于 GitHub 开源项目: Java 成长之路 欢迎大家 star!

MySQL 常见瓶颈 CPUSQL 中对大量数据进行比较、关联、排序、分组

IO 实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。查询执行效率低，扫描过多数据行。锁不适宜的锁的设置，导致线程阻塞，性能下降。死锁，线程之间交叉调用资源，导致死锁，程序卡住。服务器硬件服务器硬件的性能瓶颈：top,free, iostat 和 vmstat 来查看系统的性能状态

Explain 是什么(查看执行计划)使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

语法 EXPLAIN DQL 语句;能干嘛

表的读取顺序

哪些索引可以使用

数据读取操作的操作类型

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

结果分析 EXPLAINSELECT*FROM t_empJOIN t_deptON t_emp.deptId

t_dept.idWHERE t_emp.age

18;输出

image-20211003213247496

[id] select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序

id 相同，执行顺序由上至下 id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行复合 id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

[select_type] 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

SIMPLE 简单的 select 查询,查询中不包含子查询或者 UNION

PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary

SUBQUERY 在 SELECT 或 WHERE 列表中包含了子查询

DERIVED 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)MySQL 会递归执行这些子查询, 把结果放在临时表里。

DEPENDENT SUBQUERY 在 SELECT 或 WHERE 列表中包含了子查询,子查询基于外层 UNCACHEABLE SUBQUREY​ 无法被缓存的子查询

UNION 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为：DERIVED

UNION RESULT 从 UNION 表获取结果的 SELECT

[table] 显示这一行的数据是关于哪张表的

[type] 访问类型排列 显示查询使用了何种类型

性能从最好到最差依次排列如下:

system 表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计

const (主键单行)表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量

EXPLAIN SELECT*FROM t_emp WHERE id

1;eq_ref (索引单行)唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref (索引多行)非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

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

EXPLAIN SELECT*FROM t_emp WHERE id BETWEEN1AND3;index （遍历全表索引）Full Index Scan，

index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。

EXPLAIN SELECT id FROM t_emp;all （遍历全表 硬盘）Full Table Scan，将遍历全表以找到匹配的行​ indexmerge​ 在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的 sql 中​ refornull​ 对于某个字段既需要关联条件，也需要 null 值得情况下。查询优化器会选择用 refornull 连接查询。​ indexsubquery​ 利用索引来关联子查询，不再全表扫描。​ uniquesubquery​ 该联接类型类似于 indexsubquery。 子查询中的唯一索引

EXPLAIN SELECT*FROM t_emp;一般来说，过百万的数据量，得保证查询至少达到 range 级别，最好能达到 ref。

[possible_keys] 显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

[key] 实际使用的索引

。如果为 NULL，则没有使用索引查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠

[key_len] 表示索引中使用的字节数

显示的值为索引字段的最大可能长度 并非实际使用的长度。根据表的定义算出。并不是根据实际的检索情况得出

ref 显示索引的匹配目标值的类型

如果值为 const，则索引匹配的值是一个常数。哪些列或常量被用于查找索引列上的值

rows 显示 MySQL 认为它执行查询时必须检查的行数。image-20211003222935228

Extra 包含不适合在其他列中显示但十分重要的额外信息

Using filesort （避免） 说明 mysql 会对数据使用一个外部的要求排序，而不是按照表内的索引顺序进行读取。 MySQL 中无法利用索引完成的排序操作称为“文件排序”Using temporary （避免） 使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。USING index （） 表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ 如果同时出现 using where，表明索引被用来执行索引键值的查找; 如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。 覆盖索引(Covering Index)EXPLAIN SELECT col2 FROM t1 WHERE co11

'XX';Using where 表明使用了 where 过滤 using join buffer 使用了连接缓存：impossible where where 子句的值总是 false，不能用来获取任何元组 select tables optimized away 在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算， 查询执行计划生成的阶段即完成优化。索引优化单表案例建表及初始化/**table article by shaoxiongdu 2021/10/04*/CREATE TABLE IF NOT EXISTS article(id INT(10)UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

author_id INT

(10)UNSIGNED NOT NULL,

#作者 IDcategory_id INT(10)UNSIGNED NOT NULL,

#分类 IDviews INT(10)UNSIGNED NOT NULL,

#浏览量 comments INT(10)UNSIGNED NOT NULL,

#评论 title VARBINARY(255)NOTNULl,

#标题 content TEXT NOT NULL#正文);INSERT INTO article(author_id,category_id,views,comments,title,content)VALUES(1,

1,

1,

1,

'1',

'1'),

(2,

2,

2,

2,

'2',

'2'),

(1,

1,

3,

3,

'3',

'3');查询分类为 1 且评论大于 1 的情况下，浏览量最多的文章 IDSQL 语句 SELECT idFROM articleWHERE category_id

1AND comments

1ORDER BY views DESCLIMIT1;利用 EXPLAIN 分析 SQL 语句 image-20211004132638356

分析结果很显然 type 是 ALL,即最坏的情况。Exta 里还出现了 Using filesort,也是最坏的情况。优化是必须的

开始优化新建索引

CREATE INDEX idx_article_ccv ON article(category_id,comments,views)继续分析该 SQL 语句 image-20211004133514420

继续分析 type 从 ALL 全表硬盘扫描优化为 range 索引范围扫描，但是 exta 里使用 Using filesort 仍是无法接受的。因为按照 BTree 素引的工作原理先排序 category_ id 如果遇到相同的 category_ id 则再排序 comments 如果遇到相同的 comments 再排序 vews 当 comments 字段在联合索引里处于中间位置时，因 comments>1 条件是一个范围值(所谓 range)MySQL 无法利用索引再对后面的 vews 部分进行检索,即 range 类型查询字段后面的索引无效继续优化删除之前的索引 DROP INDEX idx_article_ccv ON article;新建索引 CREATE INDEX idx_article_cv ON article(category_id,views);继续分析 SQL 语句 image-20211004134822202

type 从 range 索引范围扫描优化到 ref 索引多行扫描索引的匹配值从 NULL 变为常量也不会进行文件排序索引优化两表案例建表及初始化数据 #书籍表 by shaoxiongdu 2021/10/04CREATE TABLE IF NOT EXISTS book(id INT(10)UNSIGNED NOT NULL AUTO_INCREMENT,

#书籍编号 class_id INT(10)UNSIGNED NOT NULL,

#分类 IDPRIMARY KEY(id));#分类表 by shaoxiongdu 2021/10/04CREATE TABLE IF NOT EXISTSclass(id INT(10)UNSIGNED NOT NULL AUTO_INCREMENT,

#分类编号 name INT(10)UNSIGNED NOT NULL,

#分类名 用数字代替 PRIMARY KEY(id));#随机插入 执行多次 INSERT INTOclass(name)VALUES(FLOOR(1

+(RAND()

20)));INSERT INTO book(class_id)VALUES(FLOOR(1

+(RAND()

20)));SELECT*FROMclassLEFT join book oNclasscard

book cardexplain 分析 image-20211004141354884

结论:type 均有 ALL

第一次优化 建立右表索引 CREATE INDEX idx_book_classid ON book(class_id);分析 image-20211004142734872

第 2 次优化 建立左表索引 并删除之前的索引 CREATE INDEX idx_class_id ONclass(id);DROP INDEX idx_book_classid ON book;继续分析 image-20211004142819428

可以看到建立右表索引时第二行的 type 是 ref r ows 也比左表索引明显低这是由左连接特性决定的。 LEFT JOIN 条件用于确定如何从右表搜素行左边一定都有 所以右边是我们的关键点,一定需要建立素引结论查询的时候，最好把有索引的表当作从表进行左右连接查询。

文章首发于 GitHub 开源项目: Java 成长之路 欢迎大家 star!