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 查询,查询中不包含子查询或者UNIONPRIMARY
>> 查询中若包含任何复杂的子部分，最外层查询则被标记为PrimarySUBQUERY
>> 在SELECT或WHERE列表中包含了子查询DERIVED
>> 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。DEPENDENT SUBQUERY
>> 在SELECT或WHERE列表中包含了子查询,子查询基于外层> UNCACHEABLE SUBQUREY> 无法被缓存的子查询UNION
>> 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVEDUNION RESULT
>> 从UNION表获取结果的SELECT- table 显示这一行的数据是关于哪张表的
- type 访问类型排列 显示查询使用了何种类型
性能从最好到最差依次排列如下:
system
>> 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计const (主键单行)
>> 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快> 如将主键置于where列表中，MySQL就能将该查询转换为一个常量>> ```mysqlEXPLAIN SELECT * FROM t_emp WHERE id = 1;
eq_ref (索引单行)
>> 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描ref (索引多行)
>> 非唯一性索引扫描，返回匹配某个单独值的所有行.> 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，> 它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体range (索引范围)
>> 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引> 一般就是在你的where语句中出现了between、<、>、in等的查询> 这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。>> ```mysqlEXPLAIN SELECT * FROM t_emp WHERE id BETWEEN 1 AND 3;
index （遍历全表索引）
>> Full Index Scan，>> index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。>> ```mysqlEXPLAIN 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。 子查询中的唯一索引>> ```mysqlEXPLAIN 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 buffe
使用了连接缓存：
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类型查询字段后面的索引无效
- 继续优化
- 删除之前的索引
```mysqlDROP INDEX idx\_article\_ccv ON article;```- 新建索引
```mysqlCREATE 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!
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。