深挖MySQL —— 索引结构与优化（三）索引的设计与优化

        不管对MySQL学习打算浅尝即止，还是打算深入研究，相信都是对这部分内容最感兴趣的。这是与应用设计、开发与实施有着最直接关系的一个模块。

        这里说个题外话，在很多公司里，我们开发往往是无法掌权数据库管理的生杀大权的，索引的设计与创建一般掌控在项目经理和dba手中（稍微大点的团队中新手开发人员一般人数不少，暴露权限确实可能会建的乱七八糟）。不过，事实上开发参加索引的建立是能让索引更加符合逻辑的，毕竟一般情况下开发才是离代码逻辑、sql语句设计距离最近的人。

        因此对于索引的建立、sql的优化，是开发人员必不可少的技能，一般我们设计索引、优化sql按照下面这个思维顺序进行：

        1、尽量使用主键查询

        上一篇B+树索引的结构可以直观的发现，主键查询是可以避免回表的，一次查询搞定，速度肯定最快。所谓回表，说的是辅助索引查询时，一般是在辅助索引B+树找到该数据的关联主键，然后再在聚集索引树上查找对应数据，这将导致多了将近一倍的IO次数。 

        2、考虑使用覆盖索引

        覆盖查询同样也是避免回表的一种操作，因为需要的数据列在辅助索引树上都能取到，因此在辅助索引树得到目标行后，不需要回到聚集索引树查询。像一般管理系统的分页查询一般是展示用户所需的摘要信息列表，一般这里可以考虑将所有被查询列和查询条件设计为联合索引。提速效果和主键查询一致。但是联合索引并非没有限制，我们可以先来看看简单的联合索引结构：

        可以看到联合索引树的键值是所有索引列组成的，所以要注意避免总占用字节数过多，导致树深度太大。第二个要注意的则是联合索引的最左匹配原则。

        3、注意联合索引的最左匹配原则

        根据联合索引树可以看到，B+树是根据最左边的索引来做大方向上的B+树的key构建的：根据最左边的列排序，最左列值一致的根据第二个列排序，依此类推。因此查询条件一定要带上最左边的列，联合索引才会被使用。

        4、开启索引下推

        索引下推是MySQL5.6添加的，用于优化数据查询。默认是开启状态的，我们要做的只是不要手贱去关闭它。下面是在“初识MySQL”篇MySQL的结构图简略版，我们简单的回顾下来了解索引下推的原理：

        

         sql查询时，请求从客户端=>后台服务层=>存储引擎，存储引擎层将数据返回到server层，然后到客户端。在没有索引下推的时候，使用联合索引的列做条件查询时，根据最左原则是只会用最左边的索引查询出数据的聚集索引，然后回表扫描整行数据返回给服务server层，最后再根据其它条件筛选。而使用了索引下推之后，会直接根据索引的信息在联合索引树中筛选好数据返回。减少了大量的回表的次数，从而提升效率。

        5、索引列的选择

        对于索引列的选择，除了上篇博文提到的，过大的列不会被考虑在内外，值范围太小的列也是不会被考虑建立索引的，比方说：sex性别，一般情况下，系统的性别是只有男/女两个取值的，联想一下B+树的结构也能想的出来，这种索引建了是没什么意义的（还会让数据库多维护一个没有意义的B+树）。当你无法预估一个列的值范围时，可以使用命令show index from ‘表名’显示的cardnality列来计算，当cardnality÷行数的值越接近1，值范围便越大。

        6、使用查询计划观察、调整（索引失效问题）

        索引并不是说建立了，sql语句上用到了，真实查询的时候就会使用的。MySQL有一个叫做优化器的东西来主动判断是否使用哪些索引：比方说，如果有更小的辅助索引出现，执行select count(*) from ‘表明’时，如果有更小的辅助索引，优化器可能不会去使用聚集索引来做统计操作。

        因此，判断一个sql的好坏不能全凭主观的臆想推论，需要借助查询计划来用数据来说话。一般想使用查询计划可以在select语句前加上explain关键字，或者在客户端如navicat上右键sql语句，点击查询计划查看。下面来看看查询字段各列的含义：

id：select查询的序列号，包含三种情况 

1、id相同时，从上往下执行；
2、id不同时，值越大优先级越高；
3、相同不同都有时，值越大优先级越高，相同id的语句从上往下执行

select_type：查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询

SIMPLE 简单的select查询，查询中不包含子查询或者union 
PRIMARY 查询中包含任何复杂的子部分，最外层查询则被标记为primary 
SUBQUERY 在select 或 where列表中包含了子查询 
DERIVED 在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表里 
UNION select出现在union之后，则被标记为union；union包含在from子句的子查询中，union前面的select由primary变成drived 
UNION RESULT 从union表获取结果的select 

 table：表名，当有别名时显示别名

partitions：查询将用到的分区，非分区表显示null

type：访问类型，sql查询优化中一个很重要的指标

结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL；一般来说，好的sql查询至少达到range级别，最好能达到ref，对每个类型的具体解释看官网：MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format这个真的要看看（过一遍），比较重要，官网上有枚举说明和sql对应的案例

possible_keys：可能用到的索引

keys：实际上用到的索引，查询优化器会判断有没有需要使用索引的必要

key_len：索引中使用的字节数

查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。长度是根据表定义计算而得的，不是通过表内检索出的

ref：与索引比较的列

rows：要查询的行数的预估值

filtered：按表条件过滤的行百分比

extra：附加信息

这也是很重要的一列信息，查询是否很慢往往可以参考这里，常出现的有：

Using filesort 无法利用索引完成排序，将查询结果是哦那个“文件排序” 
Using temporary 使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 和 group by 
Using index 表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高 
如果同时出现Using where，表明索引被用来执行索引键值的查找
如果没用同时出现Using where，表明索引用来读取数据而非执行查找动作
Using where 使用了where过滤
Using join buffer (Block Nested Loop)，Using join buffer (Batched Key Access) 来自早期连接的表被分部分读入连接缓冲区，然后它们的行从缓冲区中用于执行与当前表的连接
Impossible WHERE where子句的值总是false，不能用来获取任何元祖
distinct 优化distinct操作，在找到第一个匹配的元祖后即停止找同样值得动作

        具体出现的各个情况及含义可以查看官网：MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

        7、优秀的sql结构

        很显然优秀的sql结构是对多表查询来说的，单表查询直来直去，不存在什么优秀的sql结构。多表查询分为连接查询和子查询两种方式，所谓连接查询，本质上就是在多个表的笛卡尔积里面查询数据；而子查询是将内层的查询结果当作外层查询的参数，本质上也可以当作多个表的笛卡尔积里查询结果。

        子查询分为select/where后跟着的select和from后面跟着的select；连接查询分为内连接inner join、左/右连接left join和全连接（union配合left join实现）查询。这意味着一个查询逻辑的实现可以有多种方法。而不同的方法孰优孰劣是由优化器层层算法决定的，不同的数据量和表设计下，千万不能迷信大部分博文说的那样哪种查询一定比另一种方式的效率高（我看过一些确实连接查询效率高的概率大一些，但是不同的业务案例是无法放在一起比较的，就像你无法用你的城市的平均工资去衡量另一个城市的平均工资一样）。若要真正比较，务必还是要查看执行计划来观察比较。

        下面我么们建两张字段相同含有id(primaryKey), name(index), age的表，各自插入10万测试数据，看看不同方式的关联查询效率有什么区别：

1、普通连接查询

 2、内连接查询

 3、左连接查询

 4、全连接查询（伪全连接，由left join和union合作完成）

 5、子查询（在where）

6、子查询（在select） 

7、子查询（在from）+全连接，这个故意写的复杂些 

        可以看到，上面查询的结果基本上是一致的，此中除了4、全连接查询和7、故意写复杂的查询外，其它效率基本是一致的。甚至符合业务的情况下5、子查询由于id=1的查询type为const，效率最高。这里并不是要给各种查询效率做排序，只是证明不要迷信网络所说的某某查询效率一定快，不同的业务要求下、不同的数据量下，个终查询效率的快慢并非是有固定排名的，要比较的话一定要自己观察查询计划来调整、优化。

如有错误，敬请斧正；欢迎转载，但请务必注明出处；最后，在此向神奇的海螺保证，绝不太监！！！