MYSQL必知的知识点-----Mysql索引

        要弄清楚Mysql索引，必须清楚Mysql的索引是什么。首先需要知道，索引是一种数据结构，用于从大量数据中快速的查找我们需要查找的数据。所以类似于书本的目录，需要书本的内容足够多，才有目录存在的意义。所以，索引只有在大数据量下才会显得有意义，若数据量特别小，则就没必要费力气去建索引了，毕竟有索引的表在做数据操作时有一定的成本。

        索引在Mysql中分为三类：B+树索引，Hash索引，全文索引。

        我们只讨论在开发过程中使用最多的innodb存储引擎中的B+树索引。在讨论B+树之前，我们必须知道二叉树、平衡二叉树、B树的数据结构，然后我们才能更好的理解为什么使用B+树。

二叉树：

        如上图所示，user表的数据建立了一张二叉树的索引，其中，圆形为二叉树的节点，节点中保存了key以及value。

        二叉树的特点为：任何节点的左子节点的键值都要小于当前节点的键值，任何右边节点的键值都要大于当前节点的键值。

        此时，我们要想查找id=12的表数据，则我们的查找流程为：

        1. 从根节点出发，我们发现跟节点的10<12，则我们把10这个阶段的右子阶段作为当前节点

         2. 此时的节点为13，我们发现13>12，则需要把当前节点设置为13的左子节点

        3. 此时的节点为12，我们发现12=12，则返回当前行数据

        此时我们发现，如果一条一条的找的话，我们需要6次，如果使用二叉树的话，我们只需要查找3次即可找到我们所需要的数据，从而发现，二叉树比直接查找效率高。

平衡二叉树

        根据上述可知，二叉树比一条条找效率明显升高，不过，效率的高低与二叉树的结构紧密相关，如果我们的二叉树变为如下结构，会发现，效率跟一条条找是一模一样的。

        此时二叉树变成了一个单向链表，若我们想查找id=17的数据，则需要依次往下找。

        出现这种情况的原因为此时的二叉树不平衡了，也就是说此时的二叉树太深了，从而导致查询效率急剧下降，为了解决此问题，我们就需要让二叉树尽可能的矮一点，于是就有平衡二叉树的加入。

        平衡二叉树要求：每个节点的左右子树的高度差不能大于1，如下图：

        此时，查询效率就不会有太大的波动

   B树

        上述情况下，二叉树查找的效率不会出现太大的波动，但是为了更好的提升性能，我们引入了B树。如何引入的呢？我们知道，查找效率一般是由IO决定的，而数据全是存储在磁盘空间上，这时，我们如果可以尽可能少的做IO操作，就可以大大提升查询性能，但是对于二叉树来说，我们需要一次一次的读取数据，并且每次拿到的数据只有一个节点，即一条数据。这是，我们可以在一个节点上多存储一些数据，保证一次IO操作可以拿出较多的数据，于是就出现了一个单节点上存储多个键值的平衡树，这就是B树。如下图

        此时我们发现，一个节点上存储了好几个键值对，这样做的好处就是可以降低数的高度，从而减少IO次数。

        此时我们在想查找id=28，那我们的操作就是：

        1. 根节点页1出发，发现28在17跟35之间，那我们根据p2指针找到页3

        2. 此时当前节点为页3,28在26与30之间，则我们根据p2的指针继续找到页8

        3. 在也8中将数据比对得出我们要查找的数据。

B+树

        根据上述，此时的效率已经很高了，但是我们发现一个问题，加入每一页的存储空间是一定的，那我们每一页存储的键值的多少跟值就有很大的关系，若值过大，则每页存储的键值就会很少，我们也知道，innodb中页的默认大小为16K。如果我们在节点中不存储值，那我们就可以存储更多的键，那不是IO次数更少吗？于是我们在B树的基础上改良了一下，称为B+树。

        我们将所有的值存储在叶子节点，将所有的键存储在父节点，并且叶子节点数据是排序过的，于是我们就可以让范围查询、排序查询、分组查询更简单。如下：

        上图的B+树就是mysql innodb中B+树索引真正的样子了。数据页通过双向链表链接，叶子节点数据通过单向链表链接，就可以很快速的查找数据了。

回表

        说完索引的方式，我们就得说一下索引的方式，B+数索引按照存储方式不同分为聚集索引跟非聚集索引。

        聚集索引：以innodb为存储引擎的表中，都会有一个主键，即使你自己不创建主键，系统也会帮你创建一个隐藏主键。在把数据存储到B+树中的时候，B+树上的键就是主键，叶子节点的值为表的所有数据。这种以主键作为B+树索引的键值而构建B+树索引，称为聚集索引。

        非聚集索引：跟聚集索引相反，以非主键的其他字段作为B+树索引的键值而侯建B+树索引，称为非聚集索引。我们需要着重知道的是，非聚集索引的叶子节点上存储的不是表数据，而是该列对应的主键，如果想要查找数据，我们还需要根据主键再去聚集索引中进行查询，这个过程我们称之为回表。

        聚集索引查找：

        非聚集索引查找：

此图中，叶子节点中，1-1，左边的1表示键值，右边的1表示主键值。

        知道上述一些概念了，我们还需要知道一些概念：覆盖索引、索引下推

        覆盖索引：

        我们在上述的讨论中得知，非聚集索引存在一个回表过程，则会导致查询效率变慢，若直接在一棵B+树上查询出数据，效率会大大降低，这就是覆盖索引，此时我们需要做的是查询的列需要是聚集索引中的字段，这样的话，非聚集索引B+树中的键包含了所需要的的字段，就不需要回表查询了。说通俗点，就是非聚集索引B+树的键值中包含了所有需要查询的列，此时可以直接拿出该数据，而不是需要根据主键再去聚集索引中获取其他字段数据。

        假如有一张表，有id，有name，有sex三个字段，索引为name，此时：

        select id,name from table_name where name=''

        此时命中了name索引，并且该索引树上，存储了id跟name两个值，并且查询的字段没有其他字段，此时就不需要回表查询。

        select id,name,sex from table_name where name=''

        此时命中了name索引，但是该索引树上没有sex字段的数据，则覆盖索引失效，需要回表。

        索引下推：

        索引下推是5.6以上版本退出的，即在需要回表之前，优先对索引中包含的字段做判断，筛除掉一部分数据之后，在回表，减少回表次数。

        讨论完上面的内容后，我们在谈一谈索引使用过程中的一些注意点：

        1. 查询条件包含or，可能导致索引失效

        原因分析：假如加了or，即使走了索引，后面的条件没有索引，则还是需要全表扫描。那还不如不走索引直接扫描表。不过如果or中的所有条件都加了索引，可能会走索引。这就跟mysql的优化器有关了。

        2. 如果字段类型是字符串，where中一定要讲字段用引号引起来，否则索引会失效

        原因分析：在mysql中，若字符串不加引号，就是整型跟字符串做比较了，此时类型不匹配，则mysql会做隐式转换，此时就会索引失效

        3. 使用like关键字，可能导致索引失效

        原因分析：并不是所有的like索引都会失效，只有当like以%开头时会失效，这个也可以理解，索引前部分不清楚的话，就无法根据索引得到具体的值。而如果%在后的话，索引不会失效，若必须查询%在前的数据，则可以使用覆盖索引解决。

        4. 最左原则：

        原因分析：在建立联合索引时，index(v1,v2,v3)，则相当于建立了(v1),(v1,v2),(v1,v2,v3)三个索引，此时若不是最左，则会索引失效。

        5. 索引列上加mysql内置函数，则索引失效

        原因说明：这个没啥所得，需要根据内置函数计算

        6. 对索引列进行计算，索引失效

        原因说明：无

        7. 索引字段使用<>  ！=  not in时，可能导致索引失效

        原因说明：not in 可酌情使用exist代替

        8. 索引字段使用is null  is not null 时，可能会失效

        原因说明：无

        9. 联合查询关联字段编码不一致时，索引可能会失效

        原因说明：无

        10. 胳膊拧不过大腿，若Mysql觉得走索引没有全表扫描快的话，索引失效

        原因说明：出于效率成本的考量，mysql优化器会择优选择。

        sql简单分析：

        1. 使用explain查看执行计划，查看表的扫描类型，着重看type字段，type取值为all<index<range<ref<eq_ref<const。

        2. 使用show [session|gloable] status 查看数据库的具体操作统计：show GLOBAL status，着重看Com_select，Com_insert，Com_update，Com_delete。及时的了解当前数据库是哪种密集型操作。