性能分析.md

MySQL性能分析和索引优化

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

MySQL常见瓶颈

CPU

SQL中对大量数据进行比较、关联、排序、分组

IO

• 实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。 注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 查询执行效率低，扫描过多数据行。

锁

• 不适宜的锁的设置，导致线程阻塞，性能下降。
• 死锁，线程之间交叉调用资源，导致死锁，程序卡住。

服务器硬件

服务器硬件的性能瓶颈：top,free, iostat和vmstat来查看系统的性能状态

Explain

是什么(查看执行计划)

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

语法

EXPLAIN DQL语句;

能干嘛

• 表的读取顺序

• 哪些索引可以使用

• 数据读取操作的操作类型

• 哪些索引被实际使用

• 表之间的引用

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

结果分析

EXPLAIN
SELECT *
FROM t_emp
         JOIN t_dept
              ON t_emp.deptId = t_dept.id
WHERE t_emp.age > 18;

输出

• [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就能将该查询转换为一个常量
>
>   ```mysql

EXPLAIN SELECT * FROM t_emp WHERE id = 1;

- ### eq_ref (索引单行)

>
>   唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

• ref (索引多行)

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

• range (索引范围)

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

EXPLAIN SELECT * FROM t_emp WHERE id BETWEEN 1 AND 3;

- ### index （遍历全表索引）

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

EXPLAIN SELECT id FROM t_emp;

- ### all （遍历全表 硬盘）

>
>    Full Table Scan，将遍历全表以找到匹配的行
>   ​ index_merge
>   ​ 在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中
>   ​ ref_or_null
>   ​ 对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
>   ​ index_subquery
>   ​ 利用索引来关联子查询，不再全表扫描。
>   ​ unique_subquery
>   ​ 该联接类型类似于index_subquery。 子查询中的唯一索引
>
>   ```mysql

EXPLAIN SELECT * FROM t_emp;

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

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

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

• [key] 实际使用的索引

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

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

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

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

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

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

  

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

• Using filesort （避免） > 说明mysql会对数据使用一个外部的要求排序，而不是按照表内的索引顺序进行读取。 > MySQL中无法利用索引完成的排序操作称为“文件排序”

• Using temporary （避免） > 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

• USING index （） > 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ > 如果同时出现using where，表明索引被用来执行索引键值的查找; > 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。 > 覆盖索引(Covering Index) > > ```mysql 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, #作者ID
    category_id INT(10) UNSIGNED NOT NULL, #分类ID
    views       INT(10) UNSIGNED NOT NULL, #浏览量
    comments    INT(10) UNSIGNED NOT NULL, #评论
    title       VARBINARY(255)   NOT NULl, #标题
    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的情况下，浏览量最多的文章ID

• SQL语句

SELECT id
FROM article
WHERE category_id = 1
  AND comments > 1
ORDER BY views DESC
LIMIT 1;

• 利用EXPLAIN分析SQL语句

• 分析结果

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

• 开始优化

  新建索引

  CREATE INDEX idx_article_ccv ON article(category_id,comments,views)

• 继续分析该SQL语句

  

• 继续分析

  • 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语句

  

  • type从range索引范围扫描优化到ref索引多行扫描
  • 索引的匹配值从NULL变为常量
  • 也不会进行文件排序

索引优化两表案例

建表及初始化数据

#书籍表 by shaoxiongdu 2021/10/04
CREATE TABLE IF NOT EXISTS book
(
    id       INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, #书籍编号
    class_id INT(10) UNSIGNED NOT NULL,                #分类ID
    PRIMARY KEY (id)
);

#分类表 by shaoxiongdu 2021/10/04
CREATE TABLE IF NOT EXISTS class
(
    id   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, #分类编号
    name INT(10) UNSIGNED NOT NULL,                #分类名 用数字代替
    PRIMARY KEY (id)
);
#随机插入 执行多次
INSERT INTO class(name)
VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO book(class_id)
VALUES (FLOOR(1 + (RAND() * 20)));

SELECT *
FROM class
         LEFT join book oN class card= book card

• explain分析

  

  结论:type均有ALL

• 第一次优化 建立右表索引

  CREATE INDEX idx_book_classid ON book(class_id);

• 分析

  

• 第2次优化 建立左表索引 并删除之前的索引

  CREATE INDEX idx_class_id ON class(id);
  DROP INDEX idx_book_classid ON book;

• 继续分析

  

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

• 结论

  查询的时候，最好把有索引的表当作从表进行左右连接查询。

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