性能分析

文章首发于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;

输出

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 BETWEEN 1 AND 3;
    
  • index (遍历全表索引)

    Full Index Scan,

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

    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。 子查询中的唯一索引

    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, #作者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语句

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/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分析

    image-20211004141354884

    结论:type均有ALL

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

    CREATE INDEX idx_book_classid ON book(class_id);
    
  • 分析

    image-20211004142734872

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

    CREATE INDEX idx_class_id ON class(id);
    DROP INDEX idx_book_classid ON book;
    
  • 继续分析

    image-20211004142819428

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

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

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

最后更新于:
2021.10.04