SQL语句分析与优化

数据量小的时候，看不出来，当数据量急剧上升的时候。sql的效率问题就会暴露出来。

显示数据库中sql语句的状态信息

show status like 'Com_______' ; 查询当前连接的信息
查看 每一项命令执行的频次信息

show global status like 'Com_______' 查询全局的连接信息

上面的状态信息是针对数据库的，还有针对innodb的查询语句

show global status like 'Innodb_rows_%'

定位低效率的sql语句

可以通过下面两种方式来定位执行效率低的sql语句

慢查询日志

某条sql语句执行的时间超过了我们所设定的时间之后，就会将该sql语句记录到慢查询日志里面。
具体操作为，用 –log-slow-queries[=file_name]选项启动的时候，mysql写一个包含所有执行时间超过long_query_time 秒的
SQL语句的日志文件。

show processlist

慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MYSQL在进行的线程包括线程的状态，是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。
如果某一条sql语句的执行比较快速，info的位置就会立即显示show processlist,否则的话会显示执行的时间，并且状态为Sending data,info的信息为 当前执行的sql语句。

explain指令

explain 指令分析执行计划

explain 需要执行的sql 语句

explain 之id

id 的值分为三种情况

id 值全部一样，表示加载表的顺序，从上到下

id 不同，id的值越大，优先级越高，越先被执行

id部分相同，部分不同

explain 之 select_type

根据上面表格的顺序，查询的效率越来越低

explain 之 table

表示当前所查询的数据来源于哪一张表

explain 之 type

通过type就可以确定当前sql的执行情况以及效率的情况。

执行效率一般是从上到下依次降低，一般进行优化的时候只需要达到range级别就好，最好到达ref的级别。

explain 之 key

possibale_keys : 可能用到的key
key: 现在在使用的key
key_len : key 的长度

explain 之 rows

指的是扫描的行数

explain 之 extra

show profile 分析sql语句

mysql从5.0.37 开始了 支持了show profile的语句
show profile 能够帮助我们分析，在执行sql 语句的时候，时间的耗费情况都在哪里了。

1. 查看当前的系统是否已经开始了show profile

   select @@have_profiling;

2. 开启show profile的执行语句

   set profiling = 1;

3. 查看sql 语句的执行耗时

   show profiles;

4. 该sql 执行过程中，每个线程的状态和消耗的时间

   show profile for query query_id

   

该语句如果添加上 show profile all for query query_id;就会查询出来关于cpu以及磁盘等的一些信息。

大批量的插入数据该如何优化

1. 是用load 指令导入数据，适当的设置可以提高导入的效率（数据逗号隔开，用换行符结尾）

• 如果表使用的是innodb 这个存储引擎，那么我们导入数据的时候可以给数据提前设置好自增id.(因为如果没有设置的话，系统会默认创建一个内部的列作为主键，如果不是有顺序的，那个对于b+树来讲的话，就需要调整树的结构，耗费一定的时间。

2. 手动提交事务
   如果是应用使用自动提交的方式，建议在导入数据之前执行set autocommit = 0;关闭自动提交。导入结束后在执行set autocommit = 1
   打开自动提交，也可以提高导入效率。

sql优化insert 优化

1. 如果需要对同一张表插入很多行数据的时候，应该尽量使用多个值表的insert 语句，这将会大大缩减客户端与数据库之间的连接，关闭消耗等。
   例如：

   insert into table t_user values(1,'tom');
   insert into table t_user values(2,'tim');
   我可以转化为
   insert into table t_user values(1,'tom'),(2,'tim');

2. 执行多条sql 语句的时候，将事务的提交方式转为 手动提交
3. 数据有效的插入

sql 优化 之order by 优化

order by 排序，在mysql 中支持两种排序方式

1. filesort 排序，通过文件系统进行排序，不会使用索引机制。所有不是通过索引直接返回排序结果的排序都叫做filesort 排序
2. 通过有序索引顺序直接返回有序数据，这种情况就是using index。不需要额外的排序，操作效率高。

也就是说在进行order by 的时候 如果查询的字段添加了索引，那么在进行order by的时候就会使用using index 这种排序方式。
如果在进行查找的时候 select的包含非索引字段，就会转变为using index。但是如果根据多字段排序的时候，并且两个排序的字段不是同时排序或者同时降序，就会出现不使用using index 的情况。

因此在使用order by的时候，where 条件和order by 使用相同的索引，并且order by 的顺序和索引的顺序相同，并且order by的字段都升序或者都是降序。

如果是filesort 无法避免的话，就需要对filesort进行优化，关于Filesort 有两种扫描算法：

1. 两次扫描算法
   Mysql 4.1 之前使用这个方式排序。首先根据这个方式取出排序字段和行指针信息，然后在排序区sort buffer 中排序，如果sort buffer 不够的话，就在临时表temporary table 中存储排序结果。完成排序之后，在根据行指针回表读取记录，该操作可能会导致大量随机I/O的操作。
2. 一次扫描算法
   一次性取出满足条件的所有字段，然后在排序区sort buffer 中排序后直接输出结果。排序时内存开销较大，但是排序效率比两次扫描算法要高

sql优化 之group by

使用group by的时候会出现自动排序的情况,并且排序一般使用的是filesort，因此 我们可以在group by 后面添加order by null的操作。

优化嵌套查询

尽量使用多表联查替换 子查询。
select * from user where id in (select id from school)
可以优化为：

select * from user as u,school as s where u.id=s.id;

优化or条件

对于包含or 的子查询，如果要利用索引，则or 之间的每个条件列都必须用到索引，而且不能用到符合索引；如果没有索引，就应该增加索引。
并且如果在条件列使用的是复合索引，那么也会失效，必须建立单列索引。
建议使用union 替换or。

sql优化之limit

例如在使用limit n,10 的时候，mysql需要排序前n 条记录，然后保留n- n+10的记录。前面 的都会丢弃，查询排序的代价是非常大的。
因此，我们可以进行如下优化：

1. 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列的内容

   select * from tb_item as a,(select id from tb_item order by id limit 2000,10) as b where a.id = b.id;

2. 对于主键自增的表，可以把limit 查询转换成某个位置的查询

   select * from tb_item where id>n limit 10;
   

   这个的查询效率最高,(但是也存在问题，比如id 被删除，不再连续)。

使用sql提示

sql 提示是优化数据库的一个重要的手段，简单来说就是在sql语句中加入一些人为的提示来达到优化的目的。

1. use index 在查询语句的表名后面，添加use index 来提供希望mysql去参考的索引列表，就可以让mysql 不在考虑使用其他可用索引。（mysql不一定会按照这个提供的方案去执行）
2. 使用ignore index 可以实现让用户单纯的忽略一个或者多个索引。
3. 使用force index 为强制Mysql使用一个特定的索引，

覆盖索引的情况

尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列))，避免select *
Extra 的四种tips

1. using index : 使用覆盖索引的时候会出现
2. using where : 在查找使用索引的情况下，需要回表去查询所需要的数据
3. using index condition :查找使用了索引，但是需要回表查询数据
4. using index ;using where : 查找使用了索引，但是需要的数据都在索引列中能找到 ，所以不需要回表查询数据。