MySQL数据库优化：SQL优化

SQL优化步骤：   

       在应用开发过程中，由于初期数据量较小，或者赶进度等原因，开发人员对于SQL更偏向于功能的实现。随着生产数据量的不断增加，SQL开始出现性能问题，对生产的影响也越来越大，这时候SQL性能问题就会成为系统的瓶颈。因此必须对SQL进行优化。

1. 查看SQL执行频率

连接MySQL后，可以通过下面语句来查看数据库中全局或当前连接的增删改查SQL的执行频率：

show [global|session] status like 'Com_______'; //模糊查询，7个下划线

 

2. 定位低效率执行的SQL

1） 慢查询日志：可以通过设置来记录执行效率缓慢的SQL语句。

查询慢查询日志是否开启：
show variables like 'slow_query_log';
开启慢查询日志：
set global slow_query_log = 1;
设置慢查询日志的存放地址：
set global slow_query_log_file='/home/mysql/data/slowlog';
设置慢查询超时阈值：
set global long_query_time=3;（默认为10S）

2）show processlist：慢查询是在SQL执行结束之后才记录，不能在应用反映执行效率出现问题的时候定位问题，通过show processlist可以查看当前MySQL在进行的线程，线程的状态、耗时等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

3. explain分析SQL执行计划

通过上面定位到执行效率低的SQL，可以通过explain语句来查看SQL的执行计划。

3.1 explain之id

同时操作多张表时，这个id表示的就是操作表的顺序。

1）id值一样：表示所有表的操作顺序是从上而下顺序操作。

2）id值不一样：id值越大表示表操作的优先级越高。

3.2 explain之select_type

表示select的类型，常见的类型如下：

select_type	含义
SIMPLE	简单的查询，不包含子查询和UNION查询
PRIMARY	查询中若包含复杂的子查询，最外层查询标记为该标识
SUBQUERY	select或where列表中包含了子查询
DERIVED	在FROM列表中包含的子查询，会被标记为DERIVED(衍生)，MySQL会递归这些子查询，将结果放到临时表中
UNION	如果第二个select出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层将被标记为DRIVED
UNION RESULT	从UNION表获取结果的select

3.3 explain之table

展示这一行的数据是属于哪个表。

3.4 explain之type

type	含义
NULL	不访问任何表，索引，直接返回数据
system	表中只有一行数据（等于系统表），是const类型的特例，一般不会出现
const	通过索引一次就找到了。const用于比较primary key和unique索引。因为只匹配一行数据，所以速度很快。const将主键索引和唯一索引的所有部分与常量值比较
eq_ref	类似ref，区别是使用唯一索引，主键的关联查询，关联查询的记录只有一条。常见于主键和唯一索引扫描。
ref	非唯一性索引扫描，返回匹配某个值的所有记录
range	只检索给定返回的行，使用一个索引来选择行。where之后出现in，between，<>等操作
index	index和All的区别是index只是遍历了索引树，通常比All快，All是遍历了数据文件。
all	遍历全表以找到匹配的记录

上表中的type由上而下性能逐渐下降，一般我们优化至少要达到range级别，最好达到ref。

3.5 explain之key

possible_keys: 可能用到的索引

key：实际SQL执行用到的索引

key_len: 实际用到的索引的长度

3.6 explain之rows

扫描行的数量。

3.7 explain之extra

其他的额外执行计划信息：

extra	含义
using filesort	MySQL会使用一个外部的索引排序，而不是按照表中的索引顺序进行读取，称为“文件排序”，效率低。
using temporary	对查询结果排序时使用了临时表，常见于order by和group by。效率低。
using index	表示对select使用了覆盖索引，避免了返表查询，效率不错。

4. show profile分析SQL

show profile可以在SQL优化时帮助我们了解时间都耗费了在哪里。

通过have_profiling参数查看当前MySQL版本是否支持profile。

select @have_profiling;

profile默认是关闭的，我们可以用set来开启profiling：

set profiling = 1;//开启profiling

使用show profiles；查看执行过的SQL的profile；

show profiles;

然后通过下面语句来查看具体某条SQL的profile。

show profile for query [id];// id为该SQL执行的profile id

也可以使用下面的指令来查询SQL的CPU,磁盘IO消耗时间等。

show profile [all|cpu|...] for query [id];

5. SQL优化

我们知道索引是可以加快数据库获取数据的数据结构，但是有时候SQL语句执行并不一定会使用索引，所以我们设计SQL语句时应该避免让索引失效。

1） 全值匹配

对索引列的值全部都指定具体值。

2）最左前缀法则

如果对多个列创建了一个组合索引，要遵循最左前缀法则。即查询使用的条件从最左列开始，并且中间不跳过任何索引列。

例如爬三层楼，创建复合索引123（相当于创建1,2,3三个索引）。下面各种情景索引实际使用情况：

（1）使用索引1,2,3（顺序不受影响，mysql会自动优化），复合索引有效，即三个索引都会用到。

（2）使用索引1，3。索引1有效，索引3不起作用。

（3）使用索引2,3。没有使用最左的索引1，实际没有索引起效。

（4）使用索引1，2。索引1，2都起效。

3）范围查询右边的列索引失效。

select * from user where age > 14 and name = 'duke';// name索引失效

4）索引列进行运算会失效

select * from user where substring(name,2,1) = 'k';// name索引失效

5）字符串不加单引号，索引会失效

select * from user where name = duke;//name索引失效

6）尽量使用覆盖索引，避免使用select *。

select * from user;//全表扫描
select name from user;//使用name索引，不用返表查询

7）or造成索引失效

//假如name有索引，age没有索引，那整个SQL索引会失效。也就是说or左边有索引，右边没有索引，索引失效。
select name, age from user where name = 'duke' or age = 17;

8）%开头的模糊查询索引失效

select name from user where name like '%ke';//name索引失效

9）is null, is not null判断，有时索引失效。

//1.假如表中绝大部分name字段都是null
select * from user where name is null;//此时索引失效，直接扫描全表
select * from user where name is not null;//走索引
//2. 假如表中绝大部分name字段都是not null
select * from user where name is null;//走索引
select * from user where name is not null;//不走索引，直接扫描全表

10）尽量使用组合索引，少使用单列索引

11）order by优化

（1）order by多字段进行排序时，顺序和索引列顺序一致。

（2）order by多字段进行排序时，要么同时升序要么同时降序。

（3）order by使用索引排序时，select 的字段最好全部都是覆盖索引的字段，这样避免using filesort排序。

13）filesort优化

    对于filesort，mysql有两种排序算法：

（1）两次扫描算法：在mysql4.1之间都是使用的这种算法。先根据条件取出排序字段和行指针信息，然后在sort buffer中排序，如果sort buffer空间不够，在临时表中temporary table中存储排序结果。完成排序之后，再按行指针到表中按排序结果取出每行的数据。

（2）一次扫描算法：一次取出所有满足条件的字段，然后再sort buffer中进行排序输出结果，排序时内存消耗比较大，但是效率快。

mysql通过比较max_length_for_sort_data和query语句查询出的字段总大小进行比较，如果max_length_for_sort_data比较大，则使用第二种算法，否则使用第一种算法。

优化：可以适当提高sort_buffer_size和max_length_for_sort_data系统变量的大小来增大排序区的大小，提高排序效率。