union all 索引_关于索引你必须搞懂的概念

 索引的定义

   索引用官方的话来讲，在关系型数据库中，是以一种物理的对数据库一列或多列值进行排序的存储结构，还包含了这些值数据页的指针

   用大白话来说，索引就相当于我们的字典的目录，而目录的中的页码就是对应着索引中指向数据页的指针，根据页码我们能快速的查到我们想要的内容。

 索引的数据结构

  当我们谈论mysql索引类型的时候，多半说的是B+Tree树(InnoDB)，当然了，不同的存储引擎会使用不同的存储结构   

  接下来我们来撩一撩B+Tree树怎么组织数据的，它为什么能加快我们的查询？

如上图，是一个三层的B+Tree树，简单的介绍一下上面的几个概念，浅蓝色的部分代表着一个磁盘块，每个磁盘块包含了深蓝色数据项和黄色部分指针，比如磁盘块1包含了17，35两个数据项，p1,p2,p3三个指向其它磁盘块的指针，p1表示数据小于17的磁盘块，p2表示数据17-35的磁盘块，p3表示数据大于35的磁盘块。非叶子节点不存储真实数据，存储数据的是最下面一层的叶子节点。

   B+Tree树查找过程

   比如要查询10这个数据项，首先从根节点开始会把磁盘块1加载到内存，此时发生一次IO，在把10跟17和35做比较，10<17，定位到p1指针指向的磁盘块2，在把磁盘块2加载到内存(一次io)，在把10与磁盘块2的数据8,12做比较，10>8,定位到p2指针指向的磁盘块6，在把磁盘块6加载到内存(一次io),10与磁盘块6数据对比，最终找到了要查询的数据。

   从上面的查找过程，我们可以看到找到10这个数据总共发生了三次io,假如没有索引，在百万级别的表中查找10这个数据，需要进行全盘扫描，将发生百万次随机io,这个对于数据库来说是个灾难，所以合适的索引能显着的提升我们的查询性能。

聚族索引和辅助索引(二级索引)

1 聚族索引

在innoDB存储引擎中，每张表都会以主键构建一个B+Tree树，叶子节点存储着整张表的行数据，这就是聚族索引。所以这就为什么当我们建表最好指定自增长主键。如果不指定主键indoDB会默认创建

为什么要自增长呢？自增长，可以保证插入的行是顺序的，当主键作为关联时性能也会更好。在《高性能Mysql》书中 UUID和自增长id同时插入100万记录性能对比中，uuid做为主键插入的时间更长，而且索引占用的空间更大(因为插入的id无序导致索引页频繁分裂)

2 二级索引

 在我们平常对表加的索引都是二级索引，如单列，组合，唯一索引等。二级索引B+Tree树的叶子节点并不记录整行的记录，而是存储着列值对应的主键值，查找整行记录还需要进行回表才能找到，如何避免回表查询呢？

覆盖索引

   覆盖索引就能避免我们的回表查询，就是一个索引包含了我们所有的查询字段。如下查询就是覆盖索引查询：

通过explain查询执行计划可以看到Extra列显示 “Using index” ,这就有用到覆盖索引啦！

三星索引设计

  在《数据库索引设计与优化中》描述了一个最佳索引设计系统，三星索引设计。这是在的理想的情况下能同时满足三颗星，其实在现实的业务中很难达到，这需要我们根据实际情况来进行取舍，接下来我们来看一下三星索引的定义：

  第一颗星

   取出WHERE条件后面的等着条件把这些列作为索引，使我们的索引片尽可能窄，从而减少索引扫描的行数，例如：

上面这条sql语句查询我们要满足一颗星的话需要建立索引

idx_status_type(article_status,article_type) 我们通过explain关键字来看一下它的执行计划：

 第二课星

   ORDER BY 排序字段需要用到索引，避免文件排序(filesort),使用索引进行排序

   在上个示例的索引进行扩展，加入publish_time列做为索引的一部分，

idx_status_type_time

第三颗星

使用的索引包含了我们所有的查询的列，也就是覆盖索引，这样不需要回表查询，索引中就包含了我们所要查找的值

如：

关键字explain相关列释义

  1 id:查询编号，对应一个select 查询，如果没有子查询和关联查询，那么这个select是唯一的,每一行这个列都将会显示为1，如果内部有子查询将会顺序编号如下：

2 select_type:查询类型，它主要包括,Simple(简单查询，不包含子查询和union),Primary(如果查询中包括任何子查询，子查询的最外层会被标记为primary),Subquery(在select或where后面使用子查询),Derived(from后面的子查询会被标记为衍生查，询会把结果存放在一张临时表中),Union(在两个select查询中，使用union连接，第二个查询会被标记为union，结果会被标记为union result)

3 table:表名 

4 possible_key:查询可能使用的索引  

5 key:查询实际使用到的索引  

6 key_len:索引使用的长度，这个长度是最大长度，不是建表时字段的长度，索引的长度在不损,失精度的情况下，越短越好，这可以使B+Tree能存放更多的索引列，提升查询效率 

7 ref:关联字段 如果是常数则显示 const  

8 rows:Mysql查询需要的数据，预估要读取的行数，rows越小越好

9 filtered:查询记录数所占的百分比

10 Extra(优化时重点关注的列):额外信息,它重要的值如下：

Using index:使用了覆盖索引(very good)

Using index condition: 使用了条件索引(索引下推ICP)(mysql5.6的优化)

Using where:通过索引查询到结果后，在通过where后面的条件过滤(还行)

Using filesort:使用文件排序(尽量避免)

Using temporary:对查询的结果排序时，会使用临时表

11 type(优化时重点关注的列)

访问类型，这列是我们在需要重点关注的列，它主要以下类型：

systerm:这是在我们访问mysql系统表的时候才会出现，我们平常写的sql不会出现这种类型，这种类型查询是最快的，因为，在启动mysql时就把数据加载到内存，不需要访问表对的const:当我们的查询使用到主键索引或唯一索引查询时，这列会标为const如：

eq_ref:多表查询时，连接的条件是唯一索引或主键索引，也就是说a表一行记录在b表中只能找到唯一的一行记录与其对应,如：

ref:命中了我们的普通索引 如：

range:对我们的索引区进行了范围扫描，一般是在where条件出现了范围谓词，像between and < > in等

index:如果你的查询出现了index需要注意了，这个其实跟全表扫描差不多，只不过      它是按索引的次序扫描表，避免了排序,如：

all:全表扫描 没有命中我们的索引 如：

type性能依次排序为systerm>const>eq_ref>ref>range>index>all,一般对我们sql性能要求至少要到达range级别，最好是const

组合索引的最左匹配原则

  最左优先，意味着我们where条件的列必须包含组合索引的最左列，并且当遇到范围条件会终止匹配，举个栗子：

  组合索引 idx_a_b_c(a,b,c)

1 where b=1 and c=1 不会命中索引，不包含最左列a

2 where a=1 and c=3 and b=2 或者是 where a=1 and b=2 and c=3  a,b,c三列都会命中索引，前面一种情况mysql优化器会帮我们调整顺序满足最左匹配

3 where a=1 b=2 and d>4 and c=3  a,b列会命中索引 c列不会命中. 

使用索引注意事项

   1 like关键字在索引匹配时，左模糊 (like ‘%name’) 和全模糊(like ‘%name%’)不会命中索引，尽量避免使用

   2 算术计算的列，where a+1=2,隐式类型转换的列 where name=1，函数运算的列不会命中索引

   3 索引列注意非空约束，not null 这种是不会命中索引，建议所有表中不能允许有null值,不仅仅是索引层面，应用层有时候也会出现莫名其妙的NEP问题

   4 在使用组合索引时，注意最左匹配原则

   5 索引优化时，尽量在原有的索引基础上拓展索引，而不是建立一个新的索引

总结

        上面介绍的都是关于索引的基础理论知识，属于纸上谈兵，更多的实战优化经验还需我们在开发当中摸索，我是僵小鱼，一个进击的弱鸡，keep foolish keep hungry！与君共勉