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

程序性能瓶颈可能有很多因素：
①、外部资源，比如调用了外部的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.profilingWHERE 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_refexplain select a.* from store a INNER JOIN address b using(address_id) where b.address='47 MySakila Drive';#constexplain 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：利用索引来扫描一个子查询表，可以从每个子查询的值群组中选出一个单一的值。

转自：https://www.jianshu.com/p/423bc2e9f599