关于MySQL中索引的一些问题

1、在哪些情况下适合添加索引：

　　1）DML语句很少出现

　　2）查询语句中多出现where限制条件

　　3）数据量大

 

2、为什么索引底层采用B+tree的数据结构？

　　在常用的DQL中我们可以知道索引数据结构必须满足以下几点要求：

　　　　1）根据某个值快速查找

　　　　2）根据某个区间快速查找

　　　　3）支持快速排序查找和逆序查找

　　哈希索引并不是按照索引值顺序排序的，因此虽然哈希索引查询单条纪录的时候很快，但是无法根据区间快速查找，且不支持范围查找。

　　B+树是由平衡二叉树演变而来的

　　　　平衡二叉树具有的性质：

　　　　　　1）若左子树不空，则左子树上所有节点的值均小于它的根节点的值；

　　　　　　2）若右子树不空，则右子树上所有节点的值均大于或等于它的根节点的值；

　　　　　　3）每个非叶子节点的左右子树的高度之差的绝对值（平衡因子）最多为1。

　　从其特性我们可以看出平衡二叉树查找结点的时间复杂度是O(log2n)

　　然后我们在这里引入Btree，Btree又叫多路平衡搜索树，其数据结构特性是（m叉树）：

　　　　1、树中每个节点最多包含m个孩子

　　　　2、除根节点与子节点外，每个节点至少有[ceil(m/2)]个孩子

　　　　3、若根节点不是叶子节点，则至少有两个孩子

　　　　4、所有的叶子节点都在同一层

　　　　5、每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2) - 1] < n < m-1

 

 

　　Btree和二叉树相比，查询相率更高，因为更多的分叉减少了树深，从而搜索速度就更快。假设有1亿个节点，我们查找时，使用二叉树需要的次数为log21亿=27次，log101亿=8次，这就是Btree的查找性能优于二叉树的原因。我们观察可以发现，理想情况下似乎树分叉越多越好，但是需要考虑到MySQL中页的默认值为16k（使用SHOW GLOBAL STATUS LIKE 'Innodb_page_size%'指令可以查询，在5.7版本中Innodb_page_size 默认设置为16384，也就是16k，如果要更改数据库的页通常设置为操作系统页的整数倍），因此Btree树的节点最好设置为页的大小为最佳。

 

补充一个关于页溢出的问题：

　　innodb的页块中，但如果表中一行的数据长度超过了16k，这时候就会出现行溢出，溢出的行是存放在另外的地方，存放该溢出数据的页叫uncompresse blob page。

　　还需注意的是，innodb采用聚簇索引的方式把数据存放起来，即B+TREE结构，因此每个页块中至少有两行数据，否则就失去了B+TREE的意义（每一个页中只有一条数据，整个树就退化成为了一条双向链表），这样就得出了一行数据的最大长度就限制为了8k。

 

 

　　B+tree是Btree的变形，二者的区别为：

　　　　1、n叉B+tree最多含有n个key，而Btree最多含有n-1个key

　　　　2、B+tree的叶子节点保存所有的key的信息，依key大小顺序排列

　　　　3、所有的非叶子节点都可以看作是key的索引部分

　　二者相比，B+tree的优势在于：

　　　　1、由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。从磁盘读取时，同样大小的页可以包含更多的key，也就意味着数据量相同时，B+tree比Btree树深更矮，因此查询时IO次数也会更少。

　　　　2、B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

　　　　3、由于非叶子节点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引，所以任何索引的查找必须走一条从根节点到叶子结点的路，所有关键字查询的路径长度相同，使每一个数据的查询效率稳定。

　　补充一点，页的大小并不是越大越好，虽然越大节点能够容纳的数据就越多，树高就越小，IO次数会变少，但是InnoDB是通过内存中的缓存池来管理从磁盘中读取的页数据的，页太大很快就会把缓存池撑满，可能会造成页在内存与磁盘间频繁换入换出，影响性能。

 

3、为什么主键推荐使用自增id而不是自建主键

　　如果单是考虑主键不能重复以及不能为空的要求，我们在建表时可以使用如用户名及客户身份证等不能重复的字段作为主键。但是B+树为了维护索引的有序性，每插入或更新一条记录的时候，会对索引更新，所以如果以像身份证这样的完全随机无序的值在插入的时候就会很容易造成页分裂，分裂造成的调整必然容易导致性能下降，但是如果采用自增id作为主键，那么新插入的id则一定比其他主键值都大，要么就会直接放入到已存在但未满的节点中，要么就会放入新建节点中，就不存在页分裂的问题，性能就会得到提升。

 

4、索引失效的情况

　　1）模糊查询时，第一个通配符使用的是%，这时索引就会失效，还是采用全表扫描

　　2）使用OR时前后没有同时使用索引

　　3）数据类型出现隐式转换（varchar不加单引号可能会自动导致转换为int型） 。发生隐式转换时，如果是把数字类型的字段写成字符串影响不是特别大，但是如果把字符类型的字段写成数字会扫描全部索引。

 

5、如何查看扫描方式

　　在DQL前添加explain则可以查看到扫描方式

 

6、索引优化

　　1）独立的列：索引列不是表达式的一部分，也不能是函数的表达式

　　2）多列索引：在需要使用多个列作为查询条件的时候，使用多列联合索引比使用多个单列索引性能要好

　　3）前缀索引：对BLOG、TEXT和VARCHAR类型的列，必须使用前缀索引，只索引开始的部分字符，前缀索引的选取需要根据索引选择性来确定

　　4）覆盖索引：索引包含需要查询的字段的值，因为索引通常远小于数据行的大小，只读取索引能大大减小数据访问量，也不用回表。