mysql进阶：mysql优化篇——sql优化

作为一个程序员，我们也许不清楚线上正式的服务器硬件配置，我们不可能像DBA那样专业的对数据库进行各种实践测试与总结，但我们都应该非常了解我们SQL的业务逻辑，我们清楚SQL中访问表及字段的数据情况，我们其实只关心我们的SQL是否能尽快返回结果。那程序员如何利用已知的知识进行数据库优化？如何能快速定位SQL性能问题并找到正确的优化方向？

今天说的sql优化主要从两个方面

在这里对explain的各个字段进行详细的分析，来帮助大家分析自己所写的sql是否最佳的使用了索引。


首先是select_type:将select查询分为简单(simple)和复杂两种类型
复杂类型又分为子查询(subquery)和from列表中包含子查询(drived)就type进行详细的介绍：
System,const,eq_ref,ref,range,index,all
all : 即全表扫描
index : 按索引次序扫描，先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的。
range:以范围的形式扫描。
explain select * from a where a_id > 1\G
ref:非唯一索引访问(只有普通索引)
create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a where a_id=1\G
eq_ref:使用唯一索引查找(主键或唯一索引)
const:常量查询
在整个查询过程中这个表最多只会有一条匹配的行，比如主键 id=1 就肯定只有一行，只需读取一次表数据便能取得所需的结果，且表数据在分解执行计划时读取。
当结果不是一条时，就会变成index或range等其他类型
system:系统查询
null:优化过程中就已经得到结果，不在访问表或索引
possible_keys:可能用到的索引
key:实际用到的索引
key_line:索引字段最大可能使用长度
ref:
指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时，此值也相应的为 NULL 。
rows:估计需要扫描的行数
Extra:显示以上信息之外的其他信息
Using index
此查询使用了覆盖索引(Covering Index)，即通过索引就能返回结果，无需访问表。
若没显示"Using index"表示读取了表数据。
Using where
表示 MySQL 服务器从存储引擎收到行后再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。
Using temporary
使用到临时表
建表及插入数据：
create table a(a_id int, b_id int);
insert into a values(1,1),(1,1),(2,1),(2,2),(3,1);
mysql> explain select distinct a_id from a\G
        Extra: Using temporary
MySQL 使用临时表来实现 distinct 操作。


Using filesort
若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，否则，在取得结果后，还需要按查询所需的顺序对结果进行排序，这时就会出现 Using filesort 。
select * from a order by id;
对于没有索引的列进行order by 就会出现filesort

二、优化sql语句

2.1 优化insert

在标准SQL里面，我们通常会写下如下的SQL insert语句。

INSERT INTO TBL_TEST (id) VALUES(1);
 
很显然,在MYSQL中，这样的方式也是可行的。但是当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。

正是由于性能的瓶颈问题，MYSQL官方文档也就提到了使用批量化插入的方式，也就是在一句INSERT语句里面插入多个值。即，

INSERT INTO TBL_TEST (id) VALUES (1), (2), (3)
 
这样的做法确实也可以起到加速批量插入的功效，原因也不难理解，由于提交到服务器的INSERT语句少了，网络负载少了，最主要的是解析和优化的时间看似增多，但是实际上作用的数据行却实打实地多了。所以整体性能得以提高。

2.2 优化orderby

通过索引优化来实现MySQL的ORDER BY语句优化：
1、ORDER BY的索引优化。如果一个SQL语句形如：

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
2、WHERE + ORDER BY的索引优化，形如：

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。
注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化
复制代码 代码如下:

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
3、WHERE+ 多个字段ORDER BY
复制代码 代码如下:

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多
在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。
例如：下列sql可以使用索引。
复制代码 代码如下:

    SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
    SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情况不使用索引：


①SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by的字段混合ASC和DESC
②SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
--用于查询行的关键字与ORDER BY中所使用的不相同
③SELECT * FROM t1 ORDER BY key1, key2；
--对不同的关键字使用ORDER BY： 

2.3 优化group by

一。 松散索引扫描
1.满足条件
  查询针对一个表。
 GROUP BY 使用索引的最左前缀。
 只可以使用MIN()和MAX()聚集函数，并且它们均指向相同的列。
2.示例
表t1(c1,c2,c3,c4) 有一个索引 idx(c1，c2，c3)：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
 
SELECT DISTINCT c1, c2 FROM t1;
 
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
 
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
 
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
 
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
 
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2；

不满足条件示例：

1. 除了MIN()或MAX()，还有其它累积函数，例如：

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
2. GROUP BY子句中的域不引用索引开头，例如：

SELECT c1,c2 FROM t1 GROUP BY c2, c3;
3. 查询引用了GROUP BY 部分后面的关键字的一部分，并且没有等于常量的等式，例如：  

SELECT c1,c3 FROM t1 GROUP BY c1, c2；

二。紧凑索引扫描

如果不满足松散索引扫描条件，执行GROUP BY仍然可以不用创建临时表。如果WHERE子句中有范围条件，该方法只读取满足这些条件的关键字。
否则，进行索引扫描。该方法读取由WHERE子句定义的范围。

1. GROUP BY 中有一个漏洞，但已经由条件c2 = 'a'覆盖。

SELECT c1，c2，c3 FROM t1 WHERE c2 = 'a' GROUP BY c1，c3;

2. GROUP BY 不是满足最左前缀，但是有一个条件提供该元素的常量：

SELECT c1，c2，c3 FROM t1 WHERE c1 = 'a' GROUP BY c2，c3;

2.4 优化分页

一般刚开始学SQL的时候，会这样写 

SELECT * FROM table ORDER BY id LIMIT 1000, 10; 

但在数据达到百万级的时候，这样写会慢死 

SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 

也许耗费几十秒 

网上很多优化的方法是这样的 

SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10; 

是的，速度提升到0.x秒了，看样子还行了 
可是，还不是完美的！ 

以下这句才是完美的！ 

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010; 

比上面那句，还要再快5至10倍