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信息
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秒。使用了二级索引稍微好点,但是效果也不好。
本文由来源 21aspnet,由 system_mush 整理编辑,其版权均为 21aspnet 所有,文章内容系作者个人观点,不代表 Java架构师必看 对观点赞同或支持。如需转载,请注明文章来源。