mysql性能优化之sql优化

1、SQL优化

（1）show status 命令

mysql> show status like'Com_%';
mysql> SHOW STATUS LIKE 'THREADS _%';

        当然除了上述操作之外，还存在有累积表操作的所有的统计参数，比如Com_insert，Com_delete，Com_update，Com_select：表示执行插入，删除，更新和选择的次数。对于批量插入，只添加一次。不同于其他引擎，在innodb中：

mysql> SHOW STATUS LIKE 'INNODB_ROWS_%';

    Innodb_rows_deleted，Innodb_rows_inserted，Innodb_rows_updated，Innodb_rows_read表示删除，插入，更新和选择行的数量。通过这些参数，可以清楚地了解当前数据库的基础，并且有四种类型的SQL实现率。对于更新计数，无论提交或回滚的增加如何，都会计算计数。Com_commit和Com_rollback可以知道提交和回滚的数量。如果回滚操作频繁，则需要查看代码中的是否应该修复一下以此避免这种问题。

查看连接MySQL服务器的次数：

mysql> show status like 'connections';

查看慢查询的次数：

mysql> show status like 'slow_queries';

除了上述的几种之外，还存在许多其他的show status命令来监控服务器的状态。下面例出一些常用的命令：

Aborted_clients	由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
Aborted_connects	尝试已经失败的MySQL服务器的连接的次数
Connections	试图连接MySQL服务器的次数
Created_tmp_tables	当执行语句时，已经被创造了的隐含临时表的数量
Delayed_insert_threads	正在使用的延迟插入处理器线程的数量
Delayed_writes	用INSERT DELAYED写入的行数
Delayed_errors	用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数
Flush_commands	执行FLUSH命令的次数
Handler_delete	请求从一张表中删除行的次数
Handler_read_first	请求读入表中第一行的次数
Handler_read_key	请求数字基于键读行
Handler_read_rnd	请求读入基于一个键的一行的次数
Handler_update	请求更新表中一行的次数
Handler_write	请求向表中插入一行的次数
Slow_queries	要花超过long_query_time时间的查询数量
Threads_connected	当前打开的连接的数量
Threads_running	不在睡眠的线程数量
Uptime	服务器工作了多少秒

（2）定位效率比较低的SQL

        慢查询日志记录所有SQL缓慢超出阈值。其中SQL的阈值可以自行设置，对于设置慢查询日志的阀值直接使用set global long_query_time=8;这样既可设置阀值为8秒，由于此设置的是全局变量，设置完需要重新登录才能生效。一般来讲慢查询日志都是开放的，可以直接进行查看日志。首先，你可以mysql的启动时设置的阈值。超过阈值的SQL将被记录在日志缓慢。该阈值是根据所述参数long_query_time时间，默认为10，这意味着10秒为单位设置。其中开启慢查询日志如下：

mysql> set global slow_query_log=on;

查看慢日志的开启状态和存放位置：

mysql> show variables like 'slow_%';

需要打开log_queries_not_using_indexes参数，以便不使用索引的慢速SQL语句也会记录在日志中。其中开启不使用索引的的sql记录使用如下命令：

mysql> set global log_queries_not_using_indexes=on;

查询出来的结果如下：

mysql> show variables like 'log_%not_using%';

结果如下：

Variable_name	Value
log_queries_not_using_indexes	ON

    log_queries_not_using_indexes参数用于指示每分钟的SQL语句数和允许登录慢查询日志的未使用索引。默认值为0，表示没有限制。但是，在生产环境中，如果同时连接了10,000个人，则此SQL语句的执行没有索引，该语句将频繁记录在慢速日志中，导致慢速日志文件急剧增加，所以DBA可以传递此参数设置的次数。通常不设置此参数。此参数是动态全局的，可以直接使用set进行设置。

当慢速查询日志变得越来越大时，分析日志文件将不会那么直观。使用mysqldumpslow命令获取10个SQL语句的最长执行时间

再通过show processlist可以实时的查看正在执行慢查询的线程，线程状态等信息

（3）EXPLAIN低效SQL执行计划的分析

现在，你已经知道SQL速度慢，你可以使用命令解释和DESC得到这些SQL语句的执行计划和表结构的信息。您可以使用SHOW INDEX从表中查看表的索引信息。与此信息相结合，就可以判断SQL语句是否被索引。对于大多数SQL，索引是有效的

explain select * from order_info where item_title LIKE '%衣服%';

列的含义：

id：MySQL数据库连接线程号。

select_type：查询的类型，取值有SIMPLE、PRIMARY、UNION（UNION中的第二个或后面的查询语句）、SUBQUERY(位于子查询中的第一个SELECT语句)等。

table：输出结果集的表名。

type：访问类型，常见的是ALL，index，range，ref，eq_ref（使用唯一索引），常量/系统（查询非常快），NULL（不用访问表或索引，直接得到结果），性能丛差到高排序。

possible_keys：可能使用的索引。

key：实际使用的索引。

key_len：使用到索引字段的长度。

rows：扫描行的数量。

注意：执行的说明和描述，包括不适合于在其他列的显示，但重要的是要执行计划的附加信息。

explain SQL后，如果有warnings，马上show warnings查看一下。如果一个表刚刚被大量修改，最好是手动收集统计：ANALYZE TABLE表。

（4）show profile分析SQL

有时候通过explain命令分析执行计划不能很快的定位SQL的问题，可以联合show profile一起分析。

mysql> show variables like '%profiling';

OFF表示profiling是关闭的，MySQL是从5.0.37开始增加了对这个参数的支持，having_profiling是yes说明支持profile，这是一个动态参数，可以直接set设置。

mysql> set @@profiling=on;
mysql> select @@profiling;

 

select count(*) from order_info ;
 show profiles;

发送数据表明，数据库的线程开始通过查询数据行返回查询到客户端的结果。

 

（5）使用跟踪分析优化器选择的执行计划

跟踪文件让我们知道为什么优化器选择该计划并没有选择执行计划。首先，你需要打开跟踪并设置格式JSON。为了避免在解析过程的默认存储器太小而不能完全显示，可以被用于设置跟踪的最大存储器大小是百万。

查看和trace相关的参数：

mysql> show variables like 'optimizer_trace%';

 

打开trace：

mysql> set optimizer_trace="enabled=on",END_MARKERS_IN_JSON=on;

设置trace最大能够使用的内存：

mysql> SET optimizer_trace_max_mem_size=1000000;

根据explain的显示的执行计划结合trace查看优化器优化路径的选择的具体情形进行具体的性能分析。