SQL优化一般步骤

一、优化SQL语句的一般步骤

1. show status命令了解各种SQL的执行频率

选择会话级（当前连接）或自数据库启动至今。不写默认是session

show [session|global] status

像我们一般都是使用Innodb，可以通过下面参数判断数据库的操作次数。如果想查询所有的统计可以用

show global status like 'Innodb_rows_%'

• Innodb_rows_read：SELECT查询返回的行数
• Innodb_rows_inserted：执行INSERT操作插入的行数
• Innodb_rows_updated：执行UPDATE操作更新的行数
• Innodb_rows_deleted：执行DELETE操作删除的个数

还有些比较需要关注的如：Slow_queries慢查询的次数，Com_commit和Com_rollback了解事务提交和回滚的情况。如果回滚次数比较多，就可能有异常了。

2. 定位执行效率较低的SQL语句

开启慢查询日志相关

# 查看慢查询记录数
show status like "slow_queries%"
select SLEEP(3)
# 查看慢查询记录数（表级）
select * from mysql.slow_log
# 查看慢查询日志是否开启
show VARIABLES like '%slow_query_log%'
# 开启慢查询日志·
set global slow_query_log = 1;
# 查看输出格式
show variables like '%log_output%';
# 设置输出格式
set global log_output='FILE';
set global log_output='TABLE';
set global log_output='FILE,TABLE';
# 查看慢查询设定阈值，单位秒
show variables like "%long_query_time%"
# 设置慢查询阈值，单位秒
set long_query_time = 2;

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

通过慢查询日志取得效率低的SQL语句，针对效率低的SQL语句通过EXPLAIN或DESC命令

select_type：表示SELECT的类型，常见的取值有

• SIMPLE（简单表，即不使用表连接或者子查询）
• PRIMARY（主查询，即外层的查询）
• UNION（UNION中的第二个或者后面的查询语句）
• SUBQUERY（子查询中的第一个SELECT）等。

table：输出结果集的表

type：表示MYSQL在表中找到所需行的方式

从左至右，性能由最差到最好

• ALL，全表扫描，MySQL遍历全表来找到匹配的行
• index，全索引扫描
• range，索引范围扫描，常见于<、<=、>、>=、between等操作符
• ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的所有记录行
• equ_ref，eq_ref用于联表查询的情况，按联表的主键或唯一键联合查询
• const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key或者唯一索引 unique index进行的查询。
• NULL，不用访问表或者索引，直接能得到结果

类型type还有其他值，如ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery（in 的后面是一个查询主键字段的子查询）、index_subquery（与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）等。

possible_keys：查询时可能用到的索引

key：查询时实际用到的索引

key_len：使用索引字段的长度

ref：指明当前表所参照的字段

rows：扫描行的数量

Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

4.通过show profile分析SQL

开启：

# 查看是否支持
select @@have_profiling
# 查看是否开启
select @@profiling
# 设置会话级别的开启
set profiling = 1

通过show profiles查看本次会话执行id、sql语句和运行时间，然后通过show profile for query Id查看具体执行过程

分析到主要消耗时间的状态。

5. 解决

一般通过对where后的某些字段加索引。

二、索引问题

1. 基础

常见索引类型

• FULLTEXT：全文索引。MyISAM
• NORMAL：普通索引。可以将多个字段作为复合索引。
• UNIQUE：唯一索引。被索引的字段不能包含重复的值。

索引方法

• BTREE：InnoDB引擎，MyISAM引擎
• HASH：Memory引擎支持

创建

可以指定前缀进行索引。

但是前缀索引在排序和分组操作的时候无法使用。

create index idx_column_name on table(column_name(10));

设计原则

• 最适合的索引是where子句中出现的列
• 使用具有唯一值的索引
• 使用短索引
• 利用最左前缀
• 不要过度索引
• InnoDB存储引擎的表，记录默认会按照一定顺序保存，如果明确定义主键，按主键，没有主键，但是有唯一索引，按照唯一索引的顺序保存。如果都没有，自动生成一个内部列。尽量自己设定主键，如果有多个列都是唯一的，可以选择访问最多的作为主键。
• 主键尽可能选择较短的数据类型。因为普通索引都会保存主键的键值。

2. 经典使用场景

1. 匹配全值

对索引所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。

create index idx_col1_col2_col3 on table(col1,col2,col3);
select * from table where col1 = 'value1' and col = 'value2' and col3 = 'value3'

2. 匹配值的范围查询

select * from table where col1 > value1;

3. 匹配最左前缀

仅仅使用索引中的最左边列进行查找，比如在 col1 + col2 + col3 字段的复合索引。

完全能够使用索引：

• col1、col1,col2、col1,col2,col3、col1,col3

不全使用索引：

• col2、col2,col3、col3

4. 仅仅查询索引列

select后面跟索引列，查询效率更高

能使Extra部分变成Using index：覆盖索引扫描，只访问必须访问的数据。

5. 匹配列前缀

仅仅使用索引中的第一列，且以只包含第一列的开头一部分进行查找。

6. 索引匹配部分精确其他部分范围

7. 列名是索引，使用列名+is null可以使用索引

3. 存在但不能使用的典型场景（索引失效）

1. 以%开头的LIKE查询不能够利用B-TREE索引

explain select province_name from j_position_province where province_name like '%山'

可以使用instr函数稍稍提升一点，使用以后会发现filtered提高了。感兴趣的读者可以自行查找相关原理。

SELECT * FROM user WHERE INSTR(username,'2')>0;
# 上述语句相当于使用like
SELECT * FROM user WHERE username like '%2%'

2. 数据类型隐式转换也不会使用索引

尤其是需要查询的是字符串，where条件中一定要用引号引起来。

3. 不满足最左前缀

4. 如果MySQL估计使用索引比全表扫描更慢，则不使用索引

筛选行越高，越容易用到

5. 用or分割开的条件，如果or前的条件中的列有索引，而后面没有，那么涉及到的索引都不会用到

​ 因为 or 后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加I/O访问，一次全表扫描过滤条件就足够了。

用union

4. 索引使用情况

show status like 'Handler_read%';

• Handler_read_key：如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
• Handler_read_rnd_next：如果这个值较高则意味着查询运行低效，并且应该简历索引补救。

三、常用SQL优化

1. 大批量导入数据

• 导入的文件按表主键顺序存储时比不按主键顺序存储时快
• 导入前关闭唯一性校验SET UNIQUE_CHECKS = 0。导入结束后再开启
• 导入前关闭自动提交，导入后再开启

2. 优化INSERT语句

• 尽量是使用 insert into table values(1,2),(1,3),(1,4)…
• 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。通常比使用很多INSERT语句快20倍。

3. 优化ORDER BY语句

有两种排序

1. 有序索引顺序扫描，直接返回有序数据

2. 对返回数据进行排序，FileSort排序。所有不通过索引直接返回排序结果的都叫FileSort排序。

针对1情况，我们可以：尽量减少额外的排序，通过索引直接返回有序数据。

• WHERE条件和ORDER BY使用相同的索引
• ORDER BY的顺序和索引顺序相同
• ODDER BY 的字段都是升序或者都是降序

针对2情况，也即不得不排序。我们可以调整参数使其选择合适的排序算法。

• 两次扫描：排序时候内存开销较少
• 一次扫描：效率比两次快，但是内存开销较大。

通过适当增大max_length_for_sort_data，让MySQL选择更优化的FileSort排序算法。

max_length_for_sort_data过大，会造成CPU利用率降低和磁盘I/O过高。

适当增大sort_buffer_size排序区，尽量让排序在内存中完成。而不是创建临时表放在文件中完成。

尽量只使用必要的字段，减少排序区的使用。

4. 优化GROUP BY 语句

默认情况下，MySQL对所有GROUP BY col1,col2,…的字段排序。与查询中指定ORDER BY col1,col2,…类似。因此显示包含相同列的ORDER BY子句，则对MySQL的实际执行性能没有什么影响。

可以通过指定ORDER BY NULL禁止排序

5. 优化嵌套查询

有些情况下，join的效率比子查询高。因为不需要再内存中创建临时表来完成查询工作。

6. MySQL如何优化OR条件

对于含有or的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引。

7. 优化分页查询

常见分页场景：“limit 1000,20”

第一种思路

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。扫描尽可能少的页面提高分页效率。

explain select a.film_id, a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_id

第二种思路

转换成特定位置的查询。

假设id是连续的，或某个字段是连续的，且不重复。

select * from table limit 1000,20;可以转换成

select * from table where id < 1021 order by id desc limit 20

8. 使用SQL提示

在SQL语句中加入一些认为的提示来达到优化操作的目的。

1. USE INDEX

提供希望MYSQL去参考的索引列表

explain select count(*) from table use index (idx_col1)

2. IGNORE INDEX

忽略一个或多个索引

explain select count(*) from table ignore index(index_col1)

3. FORCE INDEX

强制使用索引，尽管在有的时候效率不高，但是还是会选择使用索引

explain select count(*) from table where id >1 force index(index_col1)