专栏首页Java架构师必看MySQL常用性能分析方法-profile,explain,索引

MySQL常用性能分析方法-profile,explain,索引

1.查版本号无论做什么都要确认版本号,不同的版本号下会有各种差异。>Select version(数据库

1.查版本号

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

>Select  version();

2.执行状态分析

显示哪些线程正在运行

>show processlist;

下面是完整的信息

3.show profile

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

显示数据库列表

>show databases;

切换数据库

>use test;

>SELECT @@profiling;

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

开启profiles功能

> set profiling=1;

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

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

执行2条SQL语句

> show profiles;

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

ALL 显示所有性能信息

>show profile all for query 3;

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

show profile for cpu只显示cpu信息

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

参考: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

关于分析结果需要注意索引有没有用到。如果显示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以按行来显示分析结果,避免因为按列显示不下的情况:

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

因为索引虽然加快了查询速度,但索引也是有代价的,另外,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;

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

6.覆盖索引

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

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

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

下面举例

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

这样性能就差很多了。

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

7.文件排序和索引排序

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

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

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

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

mysql(23)

本文由来源 21aspnet,由 system_mush 整理编辑,其版权均为 21aspnet 所有,文章内容系作者个人观点,不代表 Java架构师必看 对观点赞同或支持。如需转载,请注明文章来源。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一句话说清聚集索引和非聚集索引以及MySQL的InnoDB和MyISAM

    聚集索引和非聚集索引以及MySQL的InnoDB和MyISAM经常遇到有人向我咨询这个问题,其实呢,数据库 ...

    Java架构师必看
  • MySQL索引原理及慢查询优化

    MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。

    Java架构师必看
  • Flink在美团的应用与实践听课笔记

    原始视频视频资源已经在优酷公开:2018.8.11 Flink China Meetup·北京站-Flink在美团的应用与实践

    Java架构师必看
  • PostgreSQL在线创建索引你不得不注意的"坑"

    我们知道数据库创建索引可能会锁住创建索引的表,并且用该表上的一次扫描来执行整个索引的构建,这样在创建索引时会影响在线业务,非常大的表创建索引可能会需要几个小时,...

    数据库架构之美
  • 性能优化-索引优化SQL的方法

    重复索引: 重复索引是指相同的列以相同的顺序建立的同类型的索引,如下表中的 primary key和ID列上的索引就是重复索引

    cwl_java
  • 《Oracle Concept》第三章 - 1

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/bisal/article/details/90206...

    bisal
  • Phoenix index 二级索引

    一、索引的创建 通过二级索引,索引的列或表达式形成一个备用行键,以允许沿着这个新轴进行点查找和范 围扫描。 (1)覆盖索引 覆盖索引,一旦找到索引的条目...

    用户7625070
  • 猿思考系列7——索引不就那么点儿事儿?

    看完上一个章节,相信你已经充分的掌握了数据库事务的一些事情,猿人工厂君也知道,内容对于新手而言,理解起来还是比较很吃力的,文中提到的原理和内容,有兴趣的可以和我...

    山旮旯的胖子
  • 无语,我差点被面试官怼坏了,又给我问到MySQL索引

    前一阵子,又跑出去搞了一场面试,心态算是崩了,关于MySQL索引的原理及使用被面试官怼的体无完肤,立志要总结一番,然后一直没有时间(其实是懒……),准备好了吗?

    Java程序猿阿谷
  • 为什么大家都说“SELECT *”效率低?

    无论在工作还是面试中,关于 SQL 中不要用“SELECT *”,都是大家听烂了的问题,虽说听烂了,但普遍理解还是在很浅的层面,并没有多少人去追根究底,探究其原...

    架构师修炼

扫码关注云+社区

领取腾讯云代金券