性能优化：减少或者消除那些对获得查询结果来说不必要的工作
程序性能瓶颈可能有很多因素：
①、外部资源，比如调用了外部的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：利用索引来扫描一个子查询表，可以从每个子查询的值群组中选出一个单一的值。
网友评论