[置顶] MySQL常用性能分析方法-profile,explain,索引

时间:2022-10-06 21:16:18

1.查版本号

无论做什么都要确认版本号,不同的版本号下会有各种差异。

>Select  version();

[置顶]        MySQL常用性能分析方法-profile,explain,索引


2.执行状态分析

显示哪些线程正在运行

>show processlist;

[置顶]        MySQL常用性能分析方法-profile,explain,索引

下面是完整的信息

[置顶]        MySQL常用性能分析方法-profile,explain,索引


3.show profile

show profile默认的是关闭的,但是会话级别可以开启这个功能,开启它可以让MySQL收集在执行语句的时候所使用的资源。

显示数据库列表

>show databases;

切换数据库

>use test;

>SELECT @@profiling;

[置顶]        MySQL常用性能分析方法-profile,explain,索引

输出0说明profiles功能是关闭的

开启profiles功能

> set profiling=1;

[置顶]        MySQL常用性能分析方法-profile,explain,索引


需要注意为了明确看到查询性能,我们启用profiling并关闭query cache:

>SET profiling = 1;
>SET query_cache_type = 0;
>SET GLOBAL query_cache_size = 0;


执行2条SQL语句

> show profiles;

[置顶]        MySQL常用性能分析方法-profile,explain,索引


根据query_id 查看某个查询的详细时间耗费
> show profile for query 3;

[置顶]        MySQL常用性能分析方法-profile,explain,索引


ALL
显示所有性能信息

>show profile all for query 3;

[置顶]        MySQL常用性能分析方法-profile,explain,索引


ALL
显示所有性能信息
BLOCK IO
显示块IO(块的输入输出)的次数
CONTEXT SWITCHES
显示自动和被动的上下文切换数量
IPC
显示发送和接收的消息数量。
MEMORY
MySQL5.6中还未实现,只是计划实现。
SWAPS
显示swap的次数。


show profile for cpu只显示cpu信息

[置顶]        MySQL常用性能分析方法-profile,explain,索引

show profile for cpu,block io 显示cpu和io信息

[置顶]        MySQL常用性能分析方法-profile,explain,索引


参考:http://dev.mysql.com/doc/refman/5.7/en/show-profile.html

http://wing324.github.io/2016/02/02/MySQL的SHOW-PROFILE详解/?utm_source=tuicool&utm_medium=referral


4.分析执行计划和最左前缀原理

>explain + sql

[置顶]        MySQL常用性能分析方法-profile,explain,索引


关于分析结果需要注意索引有没有用到。如果显示type=ALL就是全表扫描了。

当用Explain查看SQL的执行计划时,里面有列显示了 key_len 的值,根据这个值可以判断索引的长度,在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。

如果只用到一个int那么就是4,比如上面的联合索引只用到一个就是4,用到2个就是8以此类推。

联合索引需要注意最左前缀原理,就是说匹配最左边的字段,如果你的索引使用到abc三个字段,那么查a,ab,abc都可以用到索引,查ac和bc和b和c是不行的,必须从左到右逐渐增多。

最左前缀参考:http://blog.codinglabs.org/articles/theory-of-mysql-index.html


联合索引优化策略:
如何选择索引列的顺序
1.经常会被使用到的列优先
2.选择性高的列优先
3.宽度小的列优先


如果不是int型数据,对于varchar,假如设定长度是255,由于你使用的是UTF-8字符集占3个字节,255*3+2=767,如上图所示。

具体公式如下:

key_len的长度计算公式:
varchr(10)变长字段且允许NULL    =  10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL =  10 *( character set:utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL        =  10 * ( character set:utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL        =  10 * ( character set:utf8=3,gbk=2,latin1=1)


关于key_len的计算参考:http://www.cnblogs.com/gomysql/p/4004244.html


explain小技巧,可以加 \G以按行来显示分析结果,避免因为按列显示不下的情况:

[置顶]        MySQL常用性能分析方法-profile,explain,索引


5.索引选择性与前缀索引

[置顶]        MySQL常用性能分析方法-profile,explain,索引因为索引虽然加快了查询速度,但索引也是有代价的,另外,MySQL在运行时也要消耗资源维护索引,因此索引并不是越多越好。

一般两种情况下不建议建索引。
1.表记录比较少,超过2000条可以酌情考虑索引。
2.索引的选择性较低。所谓索引的选择性(Selectivity),是指不重复的索引值(也叫基数,Cardinality)与表记录数(#T)的比值:
Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。

参考:http://blog.codinglabs.org/articles/theory-of-mysql-index.html


其实就是算索引的不重复度

SELECT count(DISTINCT(ID))/count(*) AS Selectivity FROM xxxx;
SELECT count(DISTINCT(xx))/count(*) AS Selectivity FROM xxxx;
SELECT count(DISTINCT(concat(xx, xxx)))/count(*) AS Selectivity FROM xxxx;
SELECT count(DISTINCT(concat(xxx, left(xx, 2))))/count(*) AS Selectivity FROM xxxx;

[置顶]        MySQL常用性能分析方法-profile,explain,索引

很明显因为主键id是不会重复的所以不重复度是100%也就是1.


6.覆盖索引

覆盖索引是指索引的叶子节点已包含所有要查询的列,因此不需要访问表数据所谓“回表”。

覆盖索引的优点
1.优化缓存,减少磁盘IO
2.减少随机IO,变随机IO为顺序IO
3.避免对Innodb主键索引的二次查询
4.避免MyISAM表进行系统调用


下面是《高性能MySQL(第3版)》中关于explain的Extra列的信息:

[置顶]        MySQL常用性能分析方法-profile,explain,索引

下面举例

第一条sql和第二条sql都是where带相同的查询条件,这个已经建了索引,但是select里第一条是都覆盖索引的,而第二条是*,自然有不覆盖的,所以需要回表

这样性能就差很多了。

[置顶]        MySQL常用性能分析方法-profile,explain,索引

再开启profile,很明显回表的要慢0.012秒,也就是12毫秒。

[置顶]        MySQL常用性能分析方法-profile,explain,索引


7.文件排序和索引排序

使用索引扫描来优化排序条件
1.索引的列顺序和Order by子句的顺序完全一致
2.索引中所有列的方向(升序,降序)和Order by子句完全一致
3.Order by中的字段全部在关联表中的第一张表中

[置顶]        MySQL常用性能分析方法-profile,explain,索引

上面是一些不同的场景,其实就是如果排序可以利用索引就可以避免文件排序。

要想知道真实的时间还是需要执行SQL,然后比较。

[置顶]        MySQL常用性能分析方法-profile,explain,索引

上图中使用主键索引id排序的查询是最快的,而使用二级索引,或者无索引的排序是非常耗时的需要10秒和17秒。使用了二级索引稍微好点,但是效果也不好。