mysql优化之慢SQL_sql优化之慢sql耗时排查

sql 语句性能分析

1、看 sql 语句执行时间

2、看 sql 的执行计划

3、查看 sql 的执行中各个环节耗时时间

4、查看mysql的执行进程，处理锁表的情况，命令 show PROCESSLIST， state 为LOCKED，说明产生锁表，ID为进程id，直接执行kill ID，就可以停止这个进程；

MySQL整个查询执行过程：

1、客户端同数据库服务层建立TCP连接。 2、客户端向MySQL服务器发送一条查询请求。 3、连接线程接收到SQL语句之后，将语句交给SQL语句解析模块进行语法分析和语义分析。 4、先看查询缓存中是否有结果，如果有结果可以直接返回给客户端。 5、如果查询缓存中没有结果，就需要真的查询数据库引擎层了，于是发给SQL优化器，进行查询的优化，生成相应的执行计划。 6、MySQL根据执行计划，调用存储引擎的API来执行查询 7、使用存储引擎查询时，先打开表，如果需要的话获取相应的锁。 查询缓存页中有没有相应的数据，如果有则可以直接返回，如果没有就要从磁盘上去读取。 8、当在磁盘中找到相应的数据之后，则会加载到缓存中来，从而使得后面的查询更加高效，由于内存有限，多采用变通的LRU表来管理缓存页，保证缓存的都是经常访问的数据。 9、最后，获取数据后返回给客户端，关闭连接，释放连接线程。

Procedure Analyse优化表结构

PROCEDURE ANALYSE() ，在优化表结构时可以辅助参考分析语句。通过分析select查询结果对现有的表的每一列给出优化的建议。

利用此语句，MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。 【只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。】

PROCEDURE ANALYSE的语法如下：

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]]) max_elements:指定每列非重复值的最大值，当超过这个值的时候，MySQL不会推荐enum类型。(默认值256) max_memory (默认值8192)analyse()为每列找出所有非重复值所采用的最大内存大小。 执行返回中的Optimal_fieldtype列是mysql建议采用的列。

样例程序 mysql> DESC user_account; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | USERID | int(10) unsigned | NO | PRI | NULL | auto_increment | | USERNAME | varchar(10) | NO | | NULL | | | PASSSWORD | varchar(30) | NO | | NULL | | | GROUPNAME | varchar(10) | YES | | NULL | | +-----------+------------------+------+-----+---------+----------------+ rows in set (0.00 sec)

mysql> select * from user_account PROCEDURE ANALYSE(1)\G; *************************** 1. row *************************** Field_name: ibatis.user_account.USERID Min_value: 1 Max_value: 103 Min_length: 1 Max_length: 3 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 51.7500 Std: 50.2562 Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL *************************** 2. row *************************** Field_name: ibatis.user_account.USERNAME Min_value: dfsa Max_value: LMEADORS .........................................................

从第一行输出我们可以看到analyze分析ibatis.user_account.USERID列 最小值1，最大值103，最小长度1，最大长度3，字符串或0的个数为0；值为空的字段数为0；平均每个字段的值为51.7500 并给出了该字段的优化建议：建议将该字段的数据类型改成TINYINT(3) UNSIGNED NOT NULL。

查询语句出现sending data耗时解决

执行SHOW PROFILES 查出Query_ID后在执行 show profile for query Query_ID ，或者是show processlist，查看查询所耗时资源 得知查询到语句耗时主要集中在 sending data上

解决步骤： 1、查询资料需要开启查询缓存，执行命令查看开启情况 show variables like '%query_cache%'，No则是未开启，改配置文件设置 query_cache_type=YES后重启

2、查看buffer 执行 show variables like 'innodb_buffer_pool%'，可以看出innodb_buffer_pool_size只有8M大小 这里设置成3G，SET GLOBAL innodb_buffer_pool_size = 3221225472，然后重启mysql(参考：https://www.cnblogs.com/wanbin/p/9530833.html)

3、查看表索引，show index from table_name发现cardinality的值为1，表的cardinality(可以翻译为“散列程度”)，它表示某个索引对应的列包含多少个不同的值。如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。

使用命令：analyze table table_name修复索引，如果修复失败，查看是否因为新添加的字段设置了相同的默认值

总结 从上面这个例子我们可以看出analyze能根据目前表中的数据情况给出优化建议。当数据库在生产环境运行一定时间以后，开发或是DBA能参考analyze的分析结果来对表结构做出一定的优化。