mysql优化之性能剖析

首先声明，非本人同意不得转载！！！

性能优化：减少或者消除那些对获得查询结果来说不必要的工作

 

程序性能瓶颈可能有很多因素：

①、外部资源，比如调用了外部的WEB服务或者搜索引擎。

②、应用需要处理大量的数据，比如分析一个超大的XML文件。

③、在循环中执行昂贵的操作，比如滥用正则表达式。

④、使用了低效率算法等。

 

对MySQL查询进行性能剖析有两种方式：

1.剖析整个数据库服务器，这样可以分析出哪些查询是主要的压力来源。

2.定位具体需要优化的查询后，可以对这些查询进行单独的剖析，分析哪些子任务是影响时间的主要消耗者。

 

 

慢查询日志

#是否开启慢查询日志，1/on表示开启，0/off表示关闭。

show VARIABLES like 'slow_query_log';

#未使用索引的查询也被记录到慢查询日志中，on表示开启，off表示关闭(默认值)。

show VARIABLES like 'log_queries_not_using_indexes';

#慢查询阈值（秒级），当查询时间大于设定的阈值时，记录日志。

show VARIABLES like 'long_query_time';

#慢查询日志存储路径

show variables like 'slow_query_log_file';

 

set global slow_query_log = on;

set global log_queries_not_using_indexes = on;

set global long_query_time = 0;

 

 

pt-query-digest

 

第一部分：总体统计结果

​​

Exec time：执行时间

Lock time：锁定时间

Rows sent：发送行数

Rows examine：扫描行数

Query size：查询字符数

 

 

第二部分：查询分组统计结果

​​

Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定

Query ID：语句的ID，（去掉空格和查询条件中的文本值，计算hash值）

Response：总的响应时间

time：该查询在本次分析中总的时间占比

calls：执行次数，即本次分析总共有多少条这种类型的查询语句

R/Call：平均每次执行的响应时间

V/M：方差均值比（Variance-to-mean），也就是常说的离差指数。

Item：查询对象

 

第三部分：每一种查询的详细统计结果

查询各项数据的百分比、总数、最小、最大、平均、95%等各项目的统计，包括SQL执行次数、执行时间、锁占用时间、发送行数、扫描行数、查询字符数，表格中也统计了查询涉及的数据库、查询时间直方图等信息。

​​

扫描的行数(Rows Examine)远远大于发送的行数(Rows sent) , 有问题， 需要优化， 索引利用差

Query_time distribution：查询时间分布图——————直方图

 

 

哪些SQL需要优化：

1.查询次数多，且每次查询占用时间长的SQL：通常为pt-query-digest分析的前几个查询

2.IO大的SQL：注意pt-query-digest分析中的Rows examine

3.未使用索引的SQL：通过pt-query-digest分析中的Rows examine与Rows Send对比

 

 

剖析单条查询

使用SHOW PROFILE

#开启：

SET profiling = 1;

#查看开启工具后的每条SQL执行总体情况

SHOW PROFILES;

#根据query_id查看某个查询的详细时间耗费

SHOW PROFILE FOR QUERY 1;

#查看cpu、IO等信息

SHOW PROFILE BLOCK IO,CPU FOR QUERY 1;

#对每一个子任务的花费时间进行已统计排序

SELECT state, SUM(duration) AS Total_R,

ROUND(100 * SUM(duration) / (SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1), 2) AS Pct_R,

COUNT(*) as Calls, SUM(duration) /COUNT(*) AS "R/Call"

FROM information_schema.profiling

WHERE query_id = 1 GROUP BY state ORDER BY total_r DESC;

 

Creating sort index：当前的SELECT中需要用到临时表在进行ORDER BY排序。建议：创建适当的索引

Sending data：发送数据

table lock：表锁。

System lock：系统锁。建议确认是由于哪个锁引起的，通常是因为MySQL或InnoDB内核级的锁引起的

Sorting result：结果的排序

copying to tmp table：将数据复制到临时表

Creating tmp table：创建临时表

 

执行计划：Explain

table：对应的表

type：连接类型（system、const、eq_ref、ref、range、index、all）

possible_keys：可能使用的索引

key：实际使用的索引

key_len：使用索引长度

rows：预计扫描行数

Extra：解析查询的额外信息（using index、using where、using temporary、using filesort）

 

连接类型（type）

#all 全表扫描

explain select * from address;

#index 全索引扫描

explain select city_id from address;

#range < > in() between 根据索引范围查找

explain select * from address where city_id>2;

#ref 根据索引 查询匹配某个值的行

explain select * from address where city_id=200;

#eq_ref

explain select a.* from store a INNER JOIN address b using(address_id) where b.address='47 MySakila Drive';

#const

explain select * from address where address_id=1;

 

MySQL解析额外信息（Extra）

1、Using index：列数据仅仅使用了索引中的信息而没有读取实际的表

Select address_id from address where address_id=1

2、Using where：MySQL服务器将在存储引擎检索行后，通过Where子句条件进行过滤

Select * from address where city_id>12;

3、Using temporary：MYSQL需要创建一个临时表来存储结果，用于排序

Select DISTINCT district from address;

4、Using filesort：MySQL将对结果进行外部排序

Select * from address order by district;

 

 

MySQL执行计划的局限

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

部分统计信息是估算的，并非精确值

EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

 

LooseScan：利用索引来扫描一个子查询表，可以从每个子查询的值群组中选出一个单一的值。