本系列文章目录
展开/收起
- Mysql索引篇（一） 索引的数据结构B+树
- Mysql索引篇（二） Myisam和Innodb的索引
- Mysql索引篇（三） 善于explain分析sql语句
- Mysql索引篇（四） 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary)
- Mysql索引篇（五） Sql优化建议和例子
80%的Sql优化都是通过合理使用索引就能完成的。
合理使用索引意味着要建立索引并且不让索引失效。如何避免索引失效
A.尽量用全值匹配
B.尽量满足最左前缀原则
C.不在索引列上做任何的操作（计算、函数、自动或手动转换类型）
D.对索引按范围条件查找的操作尽可能放在最后，因为范围作为条件之后的条件不会用到索引
E.尽量使用覆盖索引，少用select *
F.对索引字段使用 != 的时候索引会失效
G.Is null，is not null 会索引失效
H.Like “%...%”模糊匹配会索引失效（like “xxx%”会用到索引，type访问类型为range）
I.字符串不加单引号会索引失效
J.用where ... or... 会索引失效
具体例子分析：
记住一句话，分析一个sql有没有用到索引，一定要先画图，在脑袋里画一个B+树的图，然后用我在Mysql索引篇的第二篇文章中的划线法去验证。
例子1：
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
`col3` int(11) DEFAULT NULL,
`extra_file` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ccc` (`col1`,`col2`,`col3`)
) ENGINE=InnoDBexplain select * from t where col1=3 and col2=12 and col3=11; # 3个字段都用到索引
explain select * from t where col2=12 and col3=11; # 没用到索引
explain select * from t where col2=12 and col3=11 and col1=3; # 3个字段都用到索引。只是把col1放到后面，和第一句完全没区别，mysql还不至于笨到把条件的位置换一下就索引失效。（其实是mysql的查询优化器optimizer会对sql语句进行调整把它变成了where col1=3 and col2=12 and col3=11）
explain select * from t where col3=11 and col1=3; # 只有col1用到索引，col3=11只能等到把叶子节点的数据加载到内存后再用查找算法查找了
explain select * from t where col2>11 and col1=3; # col1和col2都用到索引
explain select * from t where col2=11 and col1>3; # col1用到了索引
explain select * from t where col1=13 and col2>15 and col3=10; # 只有col3没用到索引
Explain select * from t where id<15 and col1>17; # id用到了主键索引，但是col1没用到。因为where以两个索引为条件，mysql不可能对一个查询同时使用两个独立的索引，所以会挑一个效率更高的索引。而id是主键索引，是一个聚集索引，索引和数据绑定在一块，而col1位于一个二级索引，如果用了二级索引还得再去主键索引查。所以当然优先使用id索引。分析这条语句的时候，脑海里应该浮现出一个主键索引的b+树和一个二级索引的b+树。
explain select * from t where col1<13; # 没有用到索引，全表扫描，因为虽然符合最左前缀原则，但是满足col1<13条件的条数有25条，占了25/45=5/9 占了一半以上，而且还要根据二级索引对应的主键再到主键索引的B+树中找对应的数据。所以mysql认为还不如全表扫描的快，于是直接全表扫描。其实是因为我这个表只有45行，数据量比较少，如果是在一个几千几万行的表中就不会这样。
select id from t where col1<13; # col1用到了ccc索引（type是一个range），而且还用到了覆盖索引（using index）。
Select * from t where col1 = 10 and col2 like “kk%” and col3 = 14 # col1,col2都用到了索引，col3没用到
Select * from t where col1 = 10 and col2 like “%kk%” and col3 = 14 # col1用到了索引，col2,col3没用到
Select * from t where col1 = 10 and col2 like “k%kk%” and col3 = 14 # col1,col2都用到了索引，col3没用到
例子2：
还是使用例子1中的数据和索引。下面不但要说出有没有使用索引，还要说出那些字段用了索引，用于排序还是查找。
Explain select * from t where col1 = 15 and col3=20 order by col2 # col1用到索引的查找，col2用到了索引的排序，没有出现using filesort，col3没有用到索引（当where和order同时出现的时候，先看where后看order。在col1是15的情况下，col3是乱序的；所以会将满足col1=15的行全读到内存，然后内存再筛选出col3=20的行，由于这些行的col2在col1=15的情况下在树中已经排好序，所以不会再再内存中排序，所以col2用到了索引的排序）
Explain select * from t where col1 = 15 order by col3 # col1 用到了索引的查找，col3没用到排序，会在内存中对col3排序，出现了using filesort
Explain select * from t where col1 = 15 order by col2,col3 # col1 用到了索引的查找，col2和col3用到了索引的排序，没出现 using filesort
Explain select * from t where col1 = 15 order by col3,col2 # col1 用到了索引的查找，col2和col3都没用到索引的排序，出现 using filesort，内存中会对行进行两次排序，一次对col3排序，再对col2排序。
Explain select * from t where col1 = 15 and col2= 20 order by col3,col2 # col1用到了索引的查找，col2也用到了查找，col3也用到了索引的排序（结果集中col2全都是20，对col2还排个鬼的序呀）
Explain select * from t where col1 = 15 group by col2,col3 # col1用到了索引的查找，col2和col3用到了索引的排序
Explain select * from t where col1 = 15 group by col3,col2 # col1用到了索引的查找， c3 和 c2都没用到索引的排序功能
难度升级：
Explain select * from t where col2 > 20 order by col1 # 查询条件违反了最左前缀原则，此时不会走二级索引，而是直接走主键索引进行一个全表扫描。主键索引中的 col1是乱序的，所以order by col1会在内存中进行排序，而无法用到索引B+树的排序，所以会出现 Using filesort。
一个sql是走二级索引还是走全表扫描，主要看where语句，不是看order by语句。上面的where语句中，col2是一个乱序的（col1相同的节点下的col2才是一个有序的）所以col2>20根本用不了二级索引，所以会去走全表扫描。
还是那句话，脑袋里展开一个B+树的草图才能分析。
Explain select col1,col2,col3 from t where col2>20 order by col1 # 虽然where条件每遵循最左前缀，但是select后跟的字段全在二级索引的树中，而且没有其他多余的字段，所以会用到覆盖索引，用到覆盖索引肯定是走二级索引不走聚集索引啦。然后，先看where，col2>20违反了最左前缀原则，所以会扫描所有二级索引的叶子节点到内存中去筛选满足col2>20的行；满足col2>20的行的col1是排好序的，所以不会再在内存中对col1排序。所以，用到了 Using index 覆盖索引，但是没有Using filesort。不过，col2>20这个条件的判断筛选会在内存中发生，所以，type不是range而是index。
Explain select col1,col2,col3 from t order by col1 asc , col2 desc # col1用到了索引的排序，因为二级索引中col1本身就是排好序的，但是 col1相同的叶节点下的col2是一个升序排序的，如果你希望把他变为降序就只能在内存中重新排了。所以用到了Using filesort
如果 order by col1 desc , col2 desc 那就都用到了索引的排序。
小总结：
一个字段用到了索引的排序功能时，它的好处是避免了mysql在内存中对这个字段排序，减少计算量而提高性能（在内存中排序的过程是一个cpu密集型操作），体现在explain中就是没有Using filesort的出现。
上面的例子2中的语句
如果sql语句中出现order by A，但是A字段没用到索引的排序功能，就会出现 using filesort
如果sql语句中出现group by A，但是A字段没用到索引的排序功能，就会出现 using filesort 和 using temporary。
分组的前提是排序，所以分析group by 的时候只要把它当成order by来分析即可。
例子3：用覆盖索引优化 like “%…%”
现在有一个100w数据的innodb文章表，我要做根据关键词搜索文章的，例如，在搜索框搜索“金融交易”，就能把含有金融关键词的标题的文章按发布时间字段倒序排序查出所有字段，我只看前30篇。
一开始，只有主键id有索引。
版本1:
Select * from arts where title like “%金融交易%” order by create_time desc limit 30;
分析：这是一个全表扫描，mysql会一个一个的把主键索引的叶节点从磁盘读取到内存，并在内存用字符串查找的方式找title是否有“金融交易”这个关键字。Mysql读取够30条满足条件的行时，就会停止读取后面的叶节点。
所以如果包含“金融交易”的文章刚好放在100W个数据的前1000条，那么恭喜你，查找的时间会很短，但是如果这些文章集中在100W个数据的最后1000条，那么基本上你差不多执行100W次io，真正的遍历了整个表。
用这个sql查了我整整一分多钟。
为了使得where title like “%xxx%” 不发生全表扫描，我们可以使用覆盖索引优化。
版本2：
建立一个联合索引 index ct_title (create_time, title)
分两句sql来查：
Select id from arts where title like “%金融交易%” order by create_time desc limit 30;
使用了覆盖索引。而且没有出现 Using filesort ，说明order by create_time排序也用到了索引的排序，没有在内存中进行排序。
底层发生了什么事呢？首先，mysql把联合索引的所有叶节点从右到左一个个的从磁盘读进内存，并在内存查找title是否包含“金融交易”关键字。直到找到30个的时候，停止读取，但是如果没找到30个，就会一直往下读取下一个叶节点直到把叶子节点全部读完（但是这个过程很快，因为二级索引的叶节点只存着索引值和id值）。
这样就获取到了30个满足条件的文章的id。
Select * from arts where id in (刚刚查到的文章id)。
这个时候，就会往聚集索引的树中去逐一对每个id从根节点往下找到叶子节点，假如树只有3层，这句sql也就一个共发生了 (3-1)*30 = 60次sql。
这两句合起来一共就花了0.3秒。
不要这样写，因为子查询不能用limit
select * from arts where id in (Select id from arts where title like “%金融交易%” order by create_time desc limit 30);
以上例子中，例子1研究了精确匹配和范围匹配下是否使用了索引，例子2研究了排序和分组是否使用了索引，是否有using filesort 文件排序的出现，例子3研究了using index 覆盖索引。
但是，万变不离其宗，只要不是复杂的关联查询或者子查询，画一个B+树的草图来分析，一切问题迎刃而解。
=============================================================
SQL优化建议：
1.Insert优化
用一个insert插入多条数据（批量插入）。
按照主键顺序插入
使用手动提交事务
2.Order by优化
Order by后面的排序字段尽量按照建联合索引时的字段顺序来放（遵循最左前缀原则），从而避开文件排序(Using filesort)。如果可以的话最好能用上覆盖索引（Using index），不过非要用到覆盖索引的话select 查的字段只能是索引的字段了。
对于多字段排序：遵循最左前缀原则，而且不要对一个字段升序对另一个字段降序，否则也会使用到Using filesort（要么都升序排序，要么都降序排序）。
如果一定会发生 Using filesort，那么可以通过提高 sort_buffer_size 和 max_length_for_sort_data来增大排序缓冲区的大小，减小创建临时表的可能。
我再小结一下order by的优化：
遵循最左前缀原则,避开文件排序,最好能用上覆盖索引
不要对一个字段升序对另一个字段降序
如果一定会发生文件排序，可以增大排序缓冲区的大小，减小创建临时表的可能。
3.Group by优化
Group by是会先进行排序后分组的。所以所有能用于order by的优化都可以用于group by
如果我们只想分组不用排序，就可以使用order by null;
Select age, count(age) from t group by age order by null
4.子查询优化
尽量用多表联查来代替子查询；你可以用explain分析一下用子查询和用join联查的差别
5.Or 优化
Or的左右两边都必须是索引字段，而且or两边尽量不要是复合索引的两个字段，否则都会导致索引失效变成一个全表扫描（最好是or两边都是同一个字段的条件或者是两个单列索引）。
尽量用union代替or
你可以对比一下
Select * from t where id=1 or id = 10;
Select * from t where id=1 union select * from emp where id = 10;
前者是一个type为range的查询，后者是2个type为const的查询
Select * from t where id=1 or age = 20;
Select * from t where id=1 union select * from emp where age = 20;
前者是一个type为 index_merge , 后者是1个type为const和ref的查询。
6.Limit分页查询优化
随着偏移量的增加，limit的查询效率越低，limit 20w,10会扫描200w零10条记录，但只返回200w到200万零10这10条数据。
优化1：可以用到覆盖索引，现在二级索引上得到分页的id，再根据二级索引上的id找到聚集索引查询所需要的的其他列内容
如： select * from t order by create_time limit 20w, 10; (create_time)加了索引
优化为：
Select * from t t join (select id from t order by create_time limit 20w, 10) a where t.id=a.id
一个是全表扫描All ，一个是覆盖索引 index
优化2：如果我的分页是根据id排序的,而且id没有断层，那么可以先获取200w页的最后一个id，然后根据这个id往后查10条。
Select * from t where id>200w limit 10;
7.大批量插入数据的优化（一次性插入100万或1000万的数据）
对于innodb而言，考虑到B+树的结构，在插入数据的时候应该按主键顺序从小到大插入，主键类型选择自增的整型。使用整型是为了减小索引占用的存储空间，减小非叶子节点中每个元素的大小，使得一个非叶子节点能够容纳更多的索引，使得在B+树占用空间大小相同的情况下这棵树的层数更少。而按从小到大的插入可以减少构建B+树过程中页分裂的次数，提高插入效率。
关闭唯一性校验： set unique_checks = 0; 插入结束后可以把它设置会1。
手动提交事务： set autocommit=0; 每插入1w条数据手动提交1次。插入完成后在设置回1。
用一个insert命令插入多条数据。
