前言

mysql的优化最常见的就是对索引的优化，在《一文彻底读懂Explain》中有对索引的优化做过一些介绍，思来想去还是不够，特地在做一些梳理。

数据准备

员工表提前准备10万条数据

员工表表准备10万条数据，方便对索引的优化进行比较。该表的联合索引字段是name,age,position。

联合索引优化

在《一文彻底读懂Explain》中有介绍过联合索引结构及优化总结，这边做点小补充：联合索引中第一个字段就用范围查询，是不会走索引的。

联合索引第一个字段的查询就是范围查询

name是联合索引的第一个字段，where name > 'beige'按道理是有序的才对，为什么不走索引呢？这其实涉及到索引的选择问题，联合索引第一个字段就用范围查找，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描。
那这种情况有没有优化空间呢？既然它没有走索引，是不是可以用force index强制走索引，试下。

强制走索引

用了force index让它强制走索引，符合我们的预期type=range，rows是上面的一半。但mysql为何不选这个呢，而是认为全表扫描更快，可以做个对比。
‐‐ 执行时间0.312s
SELECT * FROM employees WHERE name > 'beige';
 ‐‐ 执行时间0.421s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'beige';
确实是强制索引反而更慢，看来mysql底层的选择是正确的。
其实这种情况可以用覆盖索引来优化避免它去回表，这样就能达到走索引的目的，而且还不用回表。

用覆盖索引优化

既然mysql认为你范围大回表的效率不高，那就用覆盖索引的方式避免回表这样就会正常用到索引的功能了。

Order by与Group by优化

order by1

order by 有没有走索引看Extra列，keys和key列主要是针对where条件的。这条语句order by age是会走索引的，因为where那边是先根据name查找的，所以对age来讲name都一样age是有序的。利用最左前缀法则：中间字段不能断，查询用到了name索引，，age索引列用在排序过程中。

order by2

这条order by position就没有用到索引，用的文件排序。原因很简单，就是where是name，但是age是不确定的，所以对position来说是无序的。

order by3

查找只用到索引name，age和position用于排序，都是按顺序的，索引order by 有走索引。

order by4

跟上面的语句很像，就是order by顺序换了一下就不走索引了，原因很简单就是没有按照索引的顺序来排序。索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

order by5

虽然排序的字段列与索引顺序一样，但是age 升序，position降序，明显与索引的排序方式（从小到大）不同，所以也没有走索引。

order by6

in语句查询，对where条件来说有可能会走索引有可能不会走索引。但是对于order by来讲只要出现了in语句肯定是不会走索引的，因为 in语句是范围查询，相当于多个结果集合在一起排序，排序的时候，它是没有办法保证age，position的有序性的。
group by 和 order by 原理是相似的，group by 底层也是在order by的基础上在分组，所以就不在表述了。
MySQL的排序有两种方式分别是filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。没有where语句的时候order by要满足最左前缀原则才会走索引。有where的时候要与order by子句条件列组合满足索引最左前缀原则才会走索引。
using index是直接在索引树上进行排序，using filesort是在文件上进行排序，其实filesort还分单路排序和双路排序。单路排序就是是一次性取出满足条件结果集的所有字段，然后在排序内存（如果数据太大，内存放不下就放在临时文件中排序）中进行按排序字段排序。双路排序（又叫回表排序模式）就是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在排序内存中（如果数据太大，内存放不下就放在临时文件中排序）进行排序，排序完后需要再根据ID取回其它需要的字段

分页查询优化

很多时候我们业务系统实现分页功能可能会用如下sql实现
select * from employees limit 10000,10;
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。常见的深度分页问题就是这个原因。那该如何优化呢？
1.如果是连续自增主键可以这样优化而且是根据主键ID排序
select * from employees where id > 10000 limit 10;
这样就是走主键索引，很快就能定位到id=10000的位置然后向下取5条。但是这样对条件要求太苛刻，如果中间删掉几条记录这样查的数据就不准确了，工作中这种方式优化也是比较少见。
2.非主键字段排序的分页查询
select * from employees ORDER BY name limit 10000,10;
根据name排序并且分页。可以这样优化select * from employees e inner join (select id from employees order by name limit 10000,10) ed on e.id = ed.id;
ed子查询，只查ID这样就用到联合索引中的第一个字段name，而且只查ID就避免了回表，然后在关联查询此时，10个id都已经知道了而且只有10个查询起来就非常的快。
这样做的思想就是利用覆盖索引避免回表，再让排序的时候参与的字段尽量少一点，第一种需要回表并且要将所有的数据拿回来排序肯定比较慢，第二种，只要将ID和name参与排序计算并且不需要回表，最后在join的时候范围就已经非常的小了，而且还是主键关联走的索引类型是eq_ref。

Join关联查询优化

假设有两张表t1和t2，两张表的字段a，b和属性都一样，t1表有10000条记录，t2有100条记录。假设a字段都有索引，b字段都没有索引。常见的join关联语句如下：
select * from t1 inner join t2 on t1.a= t2.a;
此时两张表的a字段都有索引，mysql底层是这样操作的：
t2表只有100条记录是小表，小表作为驱动表，先一行一行扫描（共扫100行），扫描到一行的数据去t1表也就是大表扫描，而t1的a字段是有索引的，所以t1是走a索引取的，可以看成只有一次IO或者说只有1次扫描。那这样一条语句就是t2扫描100行，再去t1那边根据索引取也是100行，总共是扫描200行。这个就是嵌套循环连接 Nested-Loop Join(NLJ) 算法。

NLJ算法只扫描200次

在做一条语句如下：
select * from t1 inner join t2 on t1.b= t2.b;
此时两张表的b字段都没有索引，mysql底层是这样操作的：
 还是以小表t2作为驱动表，先把t2 的所有数据放入到 join_buffer 中，把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比。整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。这就是基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

BNL算法扫描10100次

没有索引的关联走的是BNL算法，所幸的是它只扫描10100次磁盘，100万次内存比对。如果没有索引的关联走的是NLJ算法的话就是扫描100万次磁盘。因为t2的b去t1扫描的时候是没有索引的，没有索引就是全表扫描10000*100。
也就是说join关联的时候有索引关联是嵌套循环连接，没有索引的时候join关联是基于块嵌套循环算法。那join关联优化的主要思想就是在关联字段加索引。
对于关联sql的优化：
1.关联字段加索引，让mysql做join操作时尽量选择NLJ算法
2.小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间。比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

in和exsits优化

select * from A where id in (select id from B)
假设有这样一条语句，如果B表的数量小于A表的数量，那in语句是优于exsits语句的。
因为它的逻辑是子查询先执行的相当于：
for(select id from B){
  select * from A where A.id = B.id
}
那如果B表的数量大于A表呢？那就是exsits语句优于in语句的，就要改写成：
select * from A where exists (select 1 from B where B.id = A.id)
因为exists语句是外层先执行的，在去匹配是否存在，就相当于：
for(select * from A){
   select * from B where B.id = A.id
}
所以，不管是in还是exsits原则就是要小表驱动大表让小表先执行才是比较好的方案。

count()查询优化

我们工作中最常见的count语句无非就是下面几种情况：
select count(*) from employees;
select count(1) from employees;
select count(id) from employees;
select count(name) from employees;
其实这四种的查询效率几乎是一样的，但是非要纠结他们之间的效率高低其实还是可以分析的：
先比表count(id)和count(name),在name有索引的情况下,count(name)>count(id)的，原因是name是二级索引，id是主键索引也就是聚集索引，二级索引的数据量比聚集索引的数据量小所以count(name)>count(id)。在name没有索引的情况下count(name)没有走索引，count(id)还是走主键索引，count(name)<count(id)。
count(1)和count(name)执行过程是很像的，count(1)和count(name)都会走二级索引（这边name有索引），count(name)是在二级索引树上统计将name拿出来放到内存中在计算name的数量，而count(1)是在二级索引树上统计只要有数据就将1放到内存中在计算1的数量，所以count(1)>count(name)
count(1)和count(*)两者都一样都不会把字段取出来放内存在统计数量，且两个都是走二级索引。count(*)不但不会把字段取出来放内存在统计而且也不会在内存中放1在计算数量，而是直接按行累加数量效率比count(1)还高。
所以大体来说他们之间的效率高低如下：
count(*)≈count(1)>count(有索引字段)>count(主键 id)
或
count(*)≈count(1)>count(主键 id)>count(无索引字段)
但是如果数据量非常大的话，count统计还是比较慢的，可以尝试以下几种优化方式：
1.如果对结果的准确度要求不高，只要一个估值，可以用show table status

show table status

2.将总数维护到Redis里
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性。
3.增加数据库计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

索引下推

索引下推

上面这两条语句一个是> 一个是 like，但是呢第一条却只用到索引的第一个字段，这个好理解，因为他是范围查询，最左前缀（关于mysql如何选择索引，下面会介绍mysql的cost计算）。奇怪的是第二条是like语句，为什么却用到了索引的三个字段。这其实是mysql5.6版本之后内部做了一个优化叫索引下推。没有索引下推的情况，第一条语句，索引树匹配name之后，回表之后在比较age和position。索引下推，第二条语句，索引树匹配name之后，先不回表而是继续在索引树上匹配age和position。简单的说，所谓的索引下推就是继续在索引树上匹配下一个字段。总之索引下推就是在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

mysql如何选择索引

mysql是如何选择索引？

这两个查询语句一模一样，表一样，查询字段一样，只有查询的值不一样，那为什么一个走索引一个又没有走索引呢？这其实是mysql内部的一个成本的计算决策的结果。
我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭。trace相当于查看mysql语句的执行过程，可以这样使用：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace
mysql> select * from employees where name > 'b' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
这样控制台就会输出select * from employees where name > 'b' order by position;这条语句的执行过程。内容较多，关键的节点截图看下：

1,2阶段

可能索引分析

索引成本

最终选择全表扫描

所以，有的时候查询条件是有针对索引，但是mysql偏偏不走，因为可能数据太少根本没有必要走索引，也有可能是综合考虑回表的情况等多种因素，总之mysql内部是有这么一个cost计算的一个过程，最终选择一个最优的方案。

索引设计原则

代码先行，索引后上。等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
联合索引尽量覆盖条件。可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。不要搞太多单键索引，索引太多会占更多的空间，而且一般查询都是多个条件查询，而都是单键索引的话只用到其中一个单键索引树，其他的单键索引树就浪费了。
不要在小基数字段上建立索引。索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
长字符串我们可以采用前缀索引。尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
where与order by冲突时优先where。在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。

索引设计实战

以社交场景APP来举例，我们一般会去搜索一些好友。
1.在momo上一般都是会选择同城的异性朋友是吧！where province=xx and city=xx and sex=0那是不是可以创建一个联合索引 (province,city,sex)这些字段好像基数都不大，但是这些字段查询太频繁了还有就是联合起来做一个索引基数或者说离散型就高了，所以还是有必要的。
2.但是除了查找同城女性外，还经常需要过滤年龄比如说要18岁到30岁的同城朋友。那就将年龄加入到联合索引中(province,city,sex,age)，那要对年龄过滤是不是就变成了where province=xx and city=xx and age>=xx and age<=xx但是这样根据索引的顺序age是不走索引的，可以稍微优化下这个语句：where province=xx and city=xx and sex in (0,1) and age>=xx and age<=xx这样联合索引都用上了。
3.如果想根据爱好来筛选好友呢？在联合索引中在加一个爱好(province,city,sex,hobby,age)。这边不把爱好hobby放在联合索引末尾是因为age经常是范围查询，如果hobby放末尾就走不了索引了。所以放在age前面，这个age范围查询的时候也能用上索引。where province=xx and city=xx and sex in (0,1) and hobby = xxx and age>=xx and age<=xx
4.如果要查最近7天登录的用户呢？where province=xx and city=xx and sex in (0,1) and age>=xx and age<=xx and latest_login_time>= xx 这样latest_login_time如果加到联合索引中，它也还是不能走索引，因为前面的age已经是范围查询了,两个都是范围查询总有一个不能走索引。稍微改造下把latest_login_time改成is_login_in_latest_7_days字段，这样就可以放入联合索引中(province,city,sex,hobby,is_login_in_latest_7_days,age)还是把age放在最末尾，不让age的范围查询影响到其他字段走索引。语句就变成：where province=xx and city=xx and sex in (0,1) and is_login_in_latest_7_days= 1 and age>=xx and age<=xx 。

总之，尽量利用一两个复杂的多字段联合索引，满足80%以上的查询，有必要的话加一两个辅助索引尽量满足剩余的一些非典型查询，保证查询尽可能多的都能充分利用索引，这样就能保证查询速度和性能了。