十二、索引重要性和原理

 一、索引的重要性
  • 一般的应用系统，读操作比写操作多很多，并且通常出现问题的，一般都是比较复杂的读操作，写操作很少出现问题。所以对读操作，也就是查询语句的优化至关重要。
  • 索引在MYSQL中也叫作`键`，是存储引擎用于快速查找记录的一种数据结构。而索引对于查询的优化极为有效，尤其是当数据量越来越大的时候，索引可以轻易将查询性能提升好几个数量级。
  • 但是在另一方面，索引太多，会对应用程序的性能造成负面影响。而索引太少，又会对查询性能产生负面影响，所以找到这样一个平衡点也是至关重要的。
 
二、索引原理
原理：
    索引的目的在于提高查询效率，其本质是： 通过不断缩小想要获取的数据范围，进而筛选出自己想要的结果，同时尽量简化这种缩小范围的方法，将随机事件变成顺序事件，将查找方法固定为一种通用的方法，适用于该种类型的所有查找。
而实际中，数据库具体实现这种功能是比较复杂的，一方面数据是存储在磁盘上的，为了提高性能，需要把数据读入内存来操作计算，另一方面，磁盘访问速度大概是内存访问成本的十万分之一，频繁的访问磁盘会造成读取成本过大，这时就需要考虑如何尽量减少磁盘的io读写次数，尽量将大部分时间都放在内存操作上，减少在磁盘读写上浪费的时间。
 
磁盘IO和预读：
    磁盘读取数据（IO）靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟1传输时间三个部分，时间总共花费9ms左右。而在这9ms时间里，cpu可以发出约450万条指令来操作数据，对于百万乃至千万级的数据来说，每读一条数据要花费9ms，那么完成这些操作千万级数据的指令花费的时间可想而知要多久。
    这时预读就运用而生，当做一次IO时，不仅把当前当前磁盘地址的数据读取，同时也把相邻的数据也都读取到内存缓冲区。这样读取的数据我们称之为一页，具体有多大数据跟操作系统有关，一般为4k到8k，也就是我们读取4k到8k数据的时候，才发生了一次IO。
三、索引是什么工作机制？
    索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。其工作机制如下图：
 
 
 
 三、MySQL数据库为什么使用B+TREE作为索引的数据结构？
2.1 二叉树为什么不可行
        二叉树搜索相当于一个二分查找，二分查找能大大提升查询的效率。但是他有一个问题，也许因为输入值不够随机，也许因为输入顺序的原因，还或许一些插入、删除操作，会使得二叉搜索树失去平衡，造成搜索效率低落的情况。
 
    
    比如上面两个树，在平衡树上寻找15只要2次查找，在非平衡树上却要5次查找才能找到，效率明显下降。
（右图是一个线性链表结构。如果我们要查询的数据为15，则需要遍历所有的节点才能找到15，即，相当于全表扫描，就是由于存在这种问题，所以二叉查找树不适合用于作为索引的数据结构）
 
什么叫"平衡",直观上平衡条件就是每个节点的左右子树有着相同高度，但这确实太过苛刻。平衡二叉树AVL TREE退而求其次，要求任何节点的左右节点的左右子树高度差不超过1。
 
2.2 平衡二叉树为什么不可行   
        为了解决二叉树存在线性链表的问题，会想到用平衡二叉树来解决。下面看看平衡二叉树是什么样的：
        
 
 
平衡二叉树AVL TREE，要求任何节点的左右节点的左右子树高度差不超过1。
保证二叉树平衡的方式为左旋，右旋等操作，至于如何左旋右旋，参考https://blog.csdn.net/yixianfeng41/article/details/53229734
 
如果上图中平衡二叉树保存的是id索引，现在要查找id = 8的数据，过程如下：
  1. 把根节点加载进内存，用8和10进行比较，发现8比10小，继续加载10的左子树。
  2. 把5加载进内存，用8和5比较，同理，加载5节点的右子树。
  3. 此时发现命中，则读取id为8的索引对应的数据。
 
索引保存数据的方式一般有两种：
  • 数据区保存id 对应行数据的所有数据具体内容。
  • 数据区保存的是真正保存数据的磁盘地址。
 
到这里，平衡二叉树解决了存在线性链表的问题，数据查询的效率好像也还可以，基本能达到O(log2(n))， 那为什么mysql不选择平衡二叉树作为索引存储结构，他又存在什么样的问题呢？
  1. 搜索效率不足。一般来说，在树结构中，数据所处的深度，决定了搜索时的IO次数（MySql中将每个节点大小设置为一页大小，一次IO读取一页 / 一个节点）。如上图中搜索id = 8的数据，需要进行3次IO。当数据量到达几百万的时候，树的高度就会很恐怖。
  2. 查询不稳定。如果查询的数据落在根节点，只需要一次IO，如果是叶子节点或者是支节点，会需要多次IO才可以。
  3. 存储的数据内容太少。没有很好利用操作系统和磁盘数据交换特性，也没有利用好磁盘IO的预读能力。因为操作系统和磁盘之间一次数据交换是以页为单位的，一页大小为 4K，即每次IO操作系统会将4K数据加载进内存。但是，在二叉树每个节点的结构只保存一个关键字，一个数据区，两个子节点的引用，并不能够填满4K的内容。幸幸苦苦做了一次的IO操作，却只加载了一个关键字。在树的高度很高，恰好又搜索的关键字位于叶子节点或者支节点的时候，取一个关键字要做很多次的IO。
 
那有没有一种结构能够解决二叉树的这种问题呢？有，那就是多路平衡查找树。
 
1. 多路平衡查找树(Balance Tree)
B树也称B-树,它是一颗多路平衡查找树。我们描述一颗B树时需要指定它的阶数，阶数表示了一个结点最多有多少个孩子结点，一般用字母m表示阶数。当m取2时，就是我们常见的二叉搜索树。
 
一颗m阶的B树定义如下：
1）每个结点最多有m-1个关键字。
2）根结点最少可以只有1个关键字。
3）非根结点至少有Math.ceil(m/2)-1个关键字。
4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。
 
 
为什么说这种结构能够解决平衡二叉树存在的问题呢？
 
    B Tree 能够很好的利用操作系统和磁盘的交互特性， MySQL为了很好的利用磁盘的预读能力，将页大小设置为16K，即将一个节点（磁盘块）的大小设置为16K，一次IO将一个节点（16K）内容加载进内存。
    这里，假设关键字类型为 int，即4字节，若每个关键字对应的数据区也为4字节，不考虑子节点引用的情况下，则上图中的每个节点大约能够存储（16 * 1000）/ 8 = 2000个关键字，共2001个路数。对于二叉树，三层高度，最多可以保存7个关键字，而对于这种有2001路的B树，三层高度能够搜索的关键字个数远远的大于二叉树。
 
 
1.2 B树的插入操作
        插入操作是指插入一条记录，即（key, value）的键值对。如果B树中已存在需要插入的键值对，则用需要插入的value替换旧的value。若B树不存在这个key,则一定是在叶子结点中进行插入操作。
1）根据要插入的key的值，找到叶子结点并插入。
2）判断当前结点key的个数是否小于等于m-1，若满足则结束，否则进行第3步。
3）以结点中间的key为中心分裂成左右两部分，然后将这个中间的key插入到父结点中，这个key的左子树指向分裂后的左半部分，这个key的右子支指向分裂后的右半部分，然后将当前结点指向父结点，继续进行第3步。
下面以5阶B树为例，介绍B树的插入操作，在5阶B树中，结点最多有4个key,最少有2个key
 
 
a）在空树中插入39
此时根结点就一个key，此时根结点也是叶子结点
 
 
b）继续插入22，97和41
 
根结点此时有4个key
 
 
c）继续插入53
 
 
插入后超过了最大允许的关键字个数4，所以以key值为41为中心进行分裂，结果如下图所示，分裂后当前结点指针指向父结点，满足B树条件，插入操作结束。当阶数m为偶数时，需要分裂时就不存在排序恰好在中间的key，那么我们选择中间位置的前一个key或中间位置的后一个key为中心进行分裂即可。
 
 
 
d）依次插入13，21，40，同样会造成分裂，结果如下图所示。
 
 
 
e）依次插入30，27, 33 ；36，35，34 ；24，29，结果如下图所示。
 
 
 
f）插入key值为26的记录，插入后的结果如下图所示。
 
 
当前结点需要以27为中心分裂，并向父结点进位27，然后当前结点指向父结点，结果如下图所示。
 
进位后导致当前结点（即根结点）也需要分裂，分裂的结果如下图所示。
 
分裂后当前结点指向新的根，此时无需调整。
 
 
g）最后再依次插入key为17,28,29,31,32的记录，结果如下图所示。
 
 
在实现B树的代码中，为了使代码编写更加容易，我们可以将结点中存储记录的数组长度定义为m而非m-1，这样方便底层的结点由于分裂向上层插入一个记录时，上层有多余的位置存储这个记录。同时，每个结点还可以存储它的父结点的引用，这样就不必编写递归程序。
 
一般来说，对于确定的m和确定类型的记录，结点大小是固定的，无论它实际存储了多少个记录。但是分配固定结点大小的方法会存在浪费的情况，比如key为28,29所在的结点，还有2个key的位置没有使用，但是已经不可能继续在插入任何值了，因为这个结点的前序key是27,后继key是30,所有整数值都用完了。所以如果记录先按key的大小排好序，再插入到B树中，结点的使用率就会很低，最差情况下使用率仅为50%。
 
 
1.3 B树的删除操作
删除操作是指，根据key删除记录，如果B树中的记录中不存对应key的记录，则删除失败。
1）如果当前需要删除的key位于非叶子结点上，则用后继key（这里的后继key均指后继记录的意思）覆盖要删除的key，然后在后继key所在的子支中删除该后继key。此时后继key一定位于叶子结点上，这个过程和二叉搜索树删除结点的方式类似。删除这个记录后执行第2步
2）该结点key个数大于等于Math.ceil(m/2)-1，结束删除操作，否则执行第3步。
3）如果兄弟结点key个数大于Math.ceil(m/2)-1，则父结点中的key下移到该结点，兄弟结点中的一个key上移，删除操作结束。
否则，将父结点中的key下移与当前结点及它的兄弟结点中的key合并，形成一个新的结点。原父结点中的key的两个孩子指针就变成了一个孩子指针，指向这个新结点。然后当前结点的指针指向父结点，重复上第2步。
有些结点它可能即有左兄弟，又有右兄弟，那么我们任意选择一个兄弟结点进行操作即可。
下面以5阶B树为例，介绍B树的删除操作，5阶B树中，结点最多有4个key,最少有2个key
 
a）原始状态
    
 
b）在上面的B树中删除21，删除后结点中的关键字个数仍然大于等2，所以删除结束。
 
 
 
 
c）在上述情况下接着删除27。从上图可知27位于非叶子结点中，所以用27的后继替换它。从图中可以看出，27的后继为28，我们用28替换27，然后在28（原27）的右孩子结点中删除28。删除后的结果如下图所示。
        
 
删除后发现，当前叶子结点的记录的个数小于2，而它的兄弟结点中有3个记录（当前结点还有一个右兄弟，选择右兄弟就会出现合并结点的情况，不论选哪一个都行，只是最后B树的形态会不一样而已），我们可以从兄弟结点中借取一个key。所以父结点中的28下移，兄弟结点中的26上移,删除结束。结果如下图所示。
 
    
 
 
d）在上述情况下接着32，结果如下图。
 
 
 
当删除后，当前结点中只key，而兄弟结点中也仅有2个key。所以只能让父结点中的30下移和这个两个孩子结点中的key合并，成为一个新的结点，当前结点的指针指向父结点。结果如下图所示。
 
 
当前结点key的个数满足条件，故删除结束。
 
 
e）上述情况下，我们接着删除key为40的记录，删除后结果如下图所示。
 
同理，当前结点的记录数小于2，兄弟结点中没有多余key，所以父结点中的key下移，和兄弟（这里我们选择左兄弟，选择右兄弟也可以）结点合并，合并后的指向当前结点的指针就指向了父结点。
 
 
 
 
同理，对于当前结点而言只能继续合并了，最后结果如下所示。
 
 
合并后结点当前结点满足条件，删除结束。
 
 
 
 
2.B+树
2.1 B+树的定义
 
 
 
各种资料上B+树的定义各有不同，一种定义方式是关键字个数和孩子结点个数相同。这里我们采取维基百科上所定义的方式，即关键字个数比孩子结点个数小1，这种方式是和B树基本等价的。上图就是一颗阶数（m）为4的B+树。
 
除此之外B+树还有以下的要求:
1）B+树包含2种类型的结点：内部结点（也称索引结点）和叶子结点。根结点本身既可以是内部结点，也可以是叶子结点。根结点的关键字个数最少可以只有1个。
2）B+树与B树最大的不同是内部结点不保存数据，只用于索引，所有数据（或者说记录）都保存在叶子结点中。
3） m阶B+树表示了内部结点最多有m-1个关键字（或者说内部结点最多有m个子树），阶数m同时限制了叶子结点最多存储m-1个记录。
4）内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
5）每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
 
 
2.2 B+树的插入操作
1）若为空树，创建一个叶子结点，然后将记录插入其中，此时这个叶子结点也是根结点，插入操作结束。
 
2）针对叶子类型结点：根据key值找到叶子结点，向这个叶子结点插入记录。插入后，若当前结点key的个数小于等于m-1，则插入结束。否则将这个叶子结点分裂成左右两个叶子结点，左叶子结点包含前m/2个记录，右结点包含剩下的记录，将第m/2+1个记录的key进位到父结点中（父结点一定是索引类型结点），进位到父结点的key左孩子指针向左结点,右孩子指针向右结点。将当前结点的指针指向父结点，然后执行第3步。
 
3）针对索引类型结点：若当前结点key的个数小于等于m-1，则插入结束。否则，将这个索引类型结点分裂成两个索引结点，左索引结点包含前(m-1)/2个key，右结点包含m-(m-1)/2个key，将第m/2个key进位到父结点中，进位到父结点的key左孩子指向左结点, 进位到父结点的key右孩子指向右结点。将当前结点的指针指向父结点，然后重复第3步。
 
下面是一颗5阶B树的插入过程，5阶B数的结点最少2个key，最多4个key。
 
 
a）空树中插入5
 
b）依次插入8，10，15
 
 
c）插入16
插入16后超过了关键字的个数限制，所以要进行分裂。在叶子结点分裂时，分裂出来的左结点2个记录，右边3个记录，中间key成为索引结点中的key，分裂后当前结点指向了父结点（根结点）。结果如下图所示。
 
当然我们还有另一种分裂方式，给左结点3个记录，右结点2个记录，此时索引结点中的key就变为15。
 
 
d）插入17
 
e）插入18，插入后如下图所示
 
 
 
 
当前结点的关键字个数大于5，进行分裂。分裂成两个结点，左结点2个记录，右结点3个记录，关键字16进位到父结点（索引类型）中，将当前结点的指针指向父结点。
 
 
当前结点的关键字个数满足条件，插入结束。
 
f）插入若干数据后
 
 
 
g）在上图中插入7，结果如下图所示
 
 
当前结点的关键字个数超过4，需要分裂。左结点2个记录，右结点3个记录。分裂后关键字7进入到父结点中，将当前结点的指针指向父结点，结果如下图所示。
 
当前结点的关键字个数超过4，需要继续分裂。左结点2个关键字，右结点2个关键字，关键字16进入到父结点中，将当前结点指向父结点，结果如下图所示。
 
 
当前结点的关键字个数满足条件，插入结束。
 
 
 
2.3 B+树的删除操作
如果叶子结点中没有相应的key，则删除失败。否则执行下面的步骤
1）删除叶子结点中对应的key。删除后若结点的key的个数大于等于Math.ceil(m/2) – 1，删除操作结束,否则执行第2步。
2）若兄弟结点key有富余（大于Math.ceil(m/2) – 1），向兄弟结点借一个记录，同时用借到的key替换父结（指当前结点和兄弟结点共同的父结点）点中的key，删除结束。否则执行第3步。
3）若兄弟结点中没有富余的key,则当前结点和兄弟结点合并成一个新的叶子结点，并删除父结点中的key（父结点中的这个key两边的孩子指针就变成了一个指针，正好指向这个新的叶子结点），将当前结点指向父结点（必为索引结点），执行第4步（第4步以后的操作和B树就完全一样了，主要是为了更新索引结点）。
4）若索引结点的key的个数大于等于Math.ceil(m/2) – 1，则删除操作结束。否则执行第5步
5）若兄弟结点有富余，父结点key下移，兄弟结点key上移，删除结束。否则执行第6步
6）当前结点和兄弟结点及父结点下移key合并成一个新的结点。将当前结点指向父结点，重复第4步。
注意，通过B+树的删除操作后，索引结点中存在的key，不一定在叶子结点中存在对应的记录。
下面是一颗5阶B树的删除过程，5阶B数的结点最少2个key，最多4个key。
 
a）初始状态
 
b）删除22,删除后结果如下图
 
 
删除后叶子结点中key的个数大于等于2，删除结束
 
 
c）删除15，删除后的结果如下图所示
 
 
 
删除后当前结点只有一个key,不满足条件，而兄弟结点有三个key，可以从兄弟结点借一个关键字为9的记录,同时更新将父结点中的关键字由10也变为9，删除结束。
 
 
 
 
d）删除7，删除后的结果如下图所示
 
 
 
当前结点关键字个数小于2，（左）兄弟结点中的也没有富余的关键字（当前结点还有个右兄弟，不过选择任意一个进行分析就可以了，这里我们选择了左边的），所以当前结点和兄弟结点合并，并删除父结点中的key，当前结点指向父结点。
 
此时当前结点的关键字个数小于2，兄弟结点的关键字也没有富余，所以父结点中的关键字下移，和两个孩子结点合并，结果如下图所示。
 
 
记忆！！
2.4 B树和B+树区别是什么？
  1. B+Tree 关键字的搜索采用的是左闭合区间，之所以采用左闭合区间是因为他要最好的去支持自增id，这也是mysql的设计初衷。即，如果id = 1命中，会继续往下查找，直到找到叶子节点中的1。
  2. B+Tree 根节点和支节点没有数据区，关键字对应的数据只保存在叶子节点中。即只有叶子节点中的关键字数据区才会保存真正的数据内容或者是内容的地址。而在B树中，如果根节点命中，则会直接返回数据。
  3. 在B+Tree中，叶子节点不会去保存子节点的引用。
  4. B+Tree叶子节点是顺序排列的，并且相邻的节点具有顺序引用的关系，如上图中叶子节点之间有指针相连接。
 
2.5 MySQL为什么最终要去选择B+Tree？
  1. B+Tree是BTREE的变种，BTREE能解决的问题，B+TREE也能够解决（降低树的高度，增大节点存储数据量）
  2. B+Tree扫库和扫表能力更强。如果我们要根据索引去进行数据表的扫描，对B TREE进行扫描，需要把整棵树遍历一遍，而B+TREE只需要遍历他的所有叶子节点即可（叶子节点之间有引用）。
  3. B+TREE磁盘读写能力更强。他的根节点和支节点不保存数据区，所以根节点和支节点同样大小的情况下，保存的关键字要比B TREE要多。而叶子节点不保存子节点引用，能用于保存更多的关键字和数据。所以，B+TREE读写一次磁盘加载的关键字比B TREE更多。
  4. B+Tree排序能力更强。上面的图中可以看出，B+Tree天然具有排序功能。
  5. B+Tree查询性能稳定。B+Tree数据只保存在叶子节点，每次查询数据，查询IO次数一定是稳定的。当然这个每个人的理解都不同，因为在BTREE如果根节点命中直接返回，确实效率更高。
 
 
3 MySQL B+Tree 具体落地形式
    这里主要讲解的是MySQL根据B+Tree索引结构不同的两种存储引擎   （MYISAM 和 INNODB）
首先找到 MySQL保存数据的文件夹，看看MySQL是如何保存数据的：
        SHOW VARIABLES LIKE '%datadir%'
 
进入到这个目录下，这个目录下保存的是所有数据库，再进入到具体的一个数据库目录下。就能够看到MySQL存储数据和索引的文件了。
 
 
这里创建了两张表，user_innod和user_myisam，分别指定索引为innodb和myisam。对于每张表，MySQL会创建相应的文件保存数据和索引，具体如下：
 
从图中可以看出：
  • MYISAM存储引擎存储数据库数据，一共有三个文件：
    • Frm：表的定义文件。
    • MYD：数据文件，所有的数据保存在这个文件中。
    • MYI：索引文件。
  • Innodb存储引擎存储数据库数据，一共有两个文件(没有专门保存数据的文件)：
    • Frm文件： 表的定义文件。
    • Ibd文件：数据和索引存储文件。数据以主键进行聚集存储，把真正的数据保存在叶子节点中。
 
3.1 MyISAM存储引擎
    在MYISAM存储引擎中，数据和索引的关系如下：
 
 
 
所以在MYISAM存储引擎中，主键索引和辅助索引是同级别的，没有主次之分。
 
 
3.2 Innodb存储引擎
    Innodb主键索引为聚集索引，首先简单理解一下聚集索引的概念：数据库表行中数据的物理顺序和键值的逻辑顺序相同。
    Innodb以主键索引来聚集组织数据的存储，下面看看Innodb是如何组织数据的。
 
如上图中，叶子节点的数据区保存的就是真实的数据，在通过索引进行检索的时候，命中叶子节点，就可以直接从叶子节点中取出行数据。mysql5.5版本之前默认采用的是MyISAM引擎，5.5之后默认采用的是innodb引擎。
 
在innodb中，辅助索引的格式如下图所示？
 
如上图，主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值。
假如要查询name = C 的数据，其搜索过程如下：
  • 先在辅助索引中通过C查询最后找到主键id = 9.
  • 在主键索引中搜索id为9的数据，最终在主键索引的叶子节点中获取到真正的数据。
 
所以通过辅助索引进行检索，需要检索两次索引。
 
之所以这样设计，一个原因就是：如果和MyISAM一样在主键索引和辅助索引的叶子节点中都存放数据行指针，一旦数据发生迁移，则需要去重新组织维护所有的索引。
 
 
把Innodb 和 MYISAM区别放在一张图中看，就如下所示：
 
 
 
4 创建索引的几大原则
4.1 列的离散型
        离散型的计算公式：count(distinct column_name):count(*)，就是用去重后的列值个数比个数。值在 (0,1] 范围内。离散型越高，选择型越好。
 
如下表中各个字段，明显能看出Id的选择性比gender更高。
 
为什么说离散型越高，选择型越好？
因为离散度越高，通过索引最终确定的范围越小，最终扫面的行数也就越少。
 
 
4.2 最左匹配原则
        对于索引中的关键字进行对比的时候，一定是从左往右以此对比，且不可跳过。之前讲解的id都为int型数据，如果id为字符串的时候，如下图：
 
 
当进行匹配的时候，会把字符串转换成ascll码，如abc变成97 98 99，然后从左往右一个字符一个字符进行对比。所以在sql查询中使用like %a 时候索引会失效，因为%表示全匹配，如果已经全匹配就不需要索引，还不如直接全表扫描。
 
 
4.3 最少空间原则
前面已经说过，当关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。创建索引的关键字要尽可能占用空间小。
 
 
5 联合索引
  • 单列索引：节点中的关键字[name]
  • 联合索引：节点中的关键字[name, age]
可以把单列索引看成特殊的联合索引，联合索引的比较也是根据最左匹配原则。
 
5.1 联合索引列的选择原则
  1. 经常用的列优先（最左匹配原则）
  2. 离散度高的列优先（离散度高原则）
  3. 宽度小的列优先（最少空间原则）
 
5.2 实例分析
下面简单举例平时经常会遇到的问题：
如，平时经常使用的查询sql如下：
  • select * from users where name = ?
  • select * from users where name = ? and age = ?
为了加快检索速度，为上面的查询sql创建索引如下：
  • create index idx_name on users(name)
  • create index idx_name_age on users(name, age)
在上面解决方案中，根据最左匹配原则，idx_name为冗余索引， where name = ?同样可以利用索引idx_name_age进行检索。冗余索引会增加维护B+TREE平衡时的性能消耗，并且占用磁盘空间。
 
 
 
6. 覆盖索引
如果查询的列，通过索引项的信息可直接返回，则该索引称之为查询SQL的覆盖索引。覆盖索引可以提高查询的效率。
 
    
如上图，如果通过name进行数据检索：
select * from users where name = ?
        需要需要在name索引中找到name对应的Id，然后通过获取的Id在主键索引中查到对应的行。整个过程需要扫描两次索引，一次name，一次id。
 
如果我们查询只想查询id的值，就可以改写SQL为：
 
select id from users where name = ?
        因为只需要id的值，通过name查询的时候，扫描完name索引，我们就能够获得id的值了，所以就不需要再去扫面id索引，就会直接返回。
 
当然，如果你同时需要获取age的值：
select id，age from users where name = ?
这样就无法使用到覆盖索引了。
 
知道了覆盖索引，就知道了为什么sql中要求尽量不要使用select *，要写明具体要查询的字段。其中一个原因就是在使用到覆盖索引的情况下，不需要进入到数据区，数据就能直接返回，提升了查询效率。在用不到覆盖索引的情况下，也尽可能的不要使用select *，如果行数据量特别多的情况下，可以减少数据的网络传输量。当然，这都视具体情况而定，通过select返回所有的字段，通用性会更强，一切有利必有弊。
 
 
7 、总结
  • 索引列的数据长度满足业务的情况下能少则少。
  • 表中的索引并不是越多越好，冗余或者无用索引会占用磁盘空间并且会影响增删改的效率。
  • Where 条件中，like 9%， like %9%， like%9，三种方式都用不到索引。后两种方式对于索引是无效的。第一种9%是不确定的，决定于列的离散型，结论上讲可以用到，如果发现离散情况特别差的情况下，查询优化器觉得走索引查询性能更差，还不如全表扫描。
  • Where条件中IN可以使用索引， NOT IN 无法使用索引。
  • 多用指定查询，只返回自己想要的列，少用select *。
  • 查询条件中使用函数，索引将会失效，这和列的离散性有关，一旦使用到函数，函数具有不确定性。
  • 联合索引中，如果不是按照索引最左列开始查找，无法使用索引。
  • 对联合索引精确匹配最左前列并范围匹配另一列，可以使用到索引。
  • 联合索引中，如果查询有某个列的范围查询，其右边所有的列都无法使用索引。
 
 
posted @ 2020-10-26 20:07  别把最疼爱的人弄丢了  阅读(283)  评论(0)  编辑  收藏  举报