MySQL性能分析和索引优化

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

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

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

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

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

使用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就能将该查询转换为一个常量

  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认为它执行查询时必须检查的行数。

• 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(*)操作，不必等到执行阶段再进行计算，
  查询执行计划生成的阶段即完成优化。

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');

• 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!