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系统变量的大小来增大排序区的大小,提高排序效率。