SQL 优化方法

1.执行show status 查看各种sql命令执行的效率，通过比较类型命令执行次数，可以分析出数据库的业务是以查询为主，还是插入更新为主。然后分析主要业务类型的执行效率。

查看主要参数的值：

slow_queries慢查询的次数

com_select  执行select次数

com_insert  执行insert次数，批量插入时候，只累加1次

com_update 执行Update操作次数

com_delete 执行删除操作次数

handler_read_key 索引值被读取的次数

handler_read_rnd_next在数据文件中读下一行的请求数

2.一般的系统造成效率较低的往往是慢查询语句。 通过慢查询日志定位哪些是执行效率的sql语句。通过配置文件

slow_query_log=ON //开启慢查询日志

slow_query_log_file=/usr/local/mysql/log/slow_query_log.log//慢查询日志文件位置
long_query_time=0.2//超过多少秒的查询会被记录的日志中

这样我们就可以找到效率低的SQL语句了，接下来分析这些语句

3.通过EXPLAIN分析低效率SQL的执行计划。

 

找到排位靠前的几个慢查询语句后就对语句查看执行计划“explainselect * from **”

计划结果中，各个参数的含义
id：列数字越大越先执行

select_type:表示select类型。常见的取值有SIMPLE（简单表，即不使用连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（union中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
table：输出结果集的表。
type:表的连接类型。性能由高到底：system（表中仅有一行）、const（表中最多有一个匹配行）、eq_ref、ref、ref_null、index_merge、unique_subquery、index_subquery、range、idnex等
possible_keys:查询时，可能使用的索引
key:实际使用的索引
key_len:索引字段的长度
rows：扫描行的数量 。rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。
Extra：执行情况的说明和描述

 

4.优化低效率的SQL。常用的手段有：调整索引；分表；优化SQL语句。

 

a.调整索引。指当前表中的索引不能够很好的起到加速查询的作用，或者索引字段被使用很少，这样就需要

根据实际使用查询字段的情况调整索引字段或者建立新的索引。如果索引正在工作，handler_read_key的值

将很高，这个值代表了一个行被索引值读的次数，很低的值表名增加索引得到的性能改善不高，因为索引并

不经常使用。handler_read_rnd_next值高则意味着查询运行低效，并且应该建立索引补救。

b.分表。当表内数据记录太多时，比如2000万条数据，这是单纯的索引还是无法满足速度上的要求。可以考虑分表。

比如将表分成100份，每份存储20万条数据，同时建立一个用于查询这100份子表的新表。查询变为现在新表中，定位

记录存储在那种表中，然后再去相应的表里查询记录。

c.优化SQL语句。例如：

这条语句适用于A表比B表大的情况

select * from A where id in(select id from B);

这条语句适用于B表比A表大的情况

select * from A where EXISTS(select id from B where A.id = B.id);