mysql索引实现原理与常见问题

1、数据库中最常见的慢查询优化方式是什么？

加索引

 

2、为什么加索引能优化慢查询？

因为索引是一种优化查询的数据结构，比如MySQL中的索引是B+树实现的，而B+树就是一种数据结构，可以优化查询速度，可以利用索引快速查找数据，所以能优化查询！

 

3、你知道哪些数据结构可以提高查询速度？

哈希表、完全平衡二叉搜索树、B树、B+树等等；

 

4、那这些数据结构既然都能优化查询速度，那MySQL为何选择使用B+树？

（1）哈希表的特点就是可以快速的精确查询，但是不支持范围查询！

（2）完全平衡二叉搜索树

图中的每一个节点实际上应该有四部分：

• 左指针，指向左子树
• 键值（key）
• 键值所对应数据的存储地址（data域中的值）
• 右指针，指向右子树

需注意：完全平衡二叉搜索树是有序的，简单的说就是 "左边的小于右边的"，假如我们现在来查找 '周瑜' ，需要查找2次（第一次操作，第二次周瑜），比哈希表要多一次。而且由于完全平衡二叉搜索树是有序的，所以支持范围查找。

（3）B树

可以发现同样的元素，B树表示的要比完全平衡二叉搜索树要 "矮",原因在于B树中的一个节点可以存储多个元素！

（4）B+树

我们可以发现同样的元素，B+树的表示要比B树要 "胖"，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连！

完全平衡二叉树：每个节点只能存储一个元素，每访问一个节点都要进行一次IO操作。

B树的优点：一个节点可以存储多个元素，相对于完全平衡二叉树所以整棵树的高度就降低了，磁盘IO效率提高了。而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。

到这里可以总结出来，Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

 

5、一个B+树的节点中到底存储多少个元素合适呢？

B+树中一个节点为一页或页的倍数最为合适。因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，会造成资源的浪费；如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费；所以为了不造成资源的浪费，最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适！

 

6、MySQL中B+树的一个节点大小为多大？

一页，这里说的 "页" 是MySQL自定义的单位（其实和操作系统类似），MySQL的Innodb引擎中一页的默认大小是16K（如果操作系统中一页大小是4K，那么MySQL中1页 = 操作系统中的4页），这样存取数据的时候都是一页一页的获取索引文件中节点数据的！

 

7、MySQL中MyISM和Innodb使用B+树

通常我们认为B+树的非叶子节点不存储数据，只有叶子节点才存储数据；而B树的非叶子节点和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，树的高度相对会比B+树高，平均的I/O效率会比较低，所以使用B+树作为索引的数据结构，再加上B+树的叶子节点之间使用了指针相连，也方便进行范围内查找，上面的data区域两个存储引擎会有区别！

（1）MyISM中的B+树

MyISQM中叶子节点的数据区域存储的是数据记录的地址

MyISAM存储引擎在使用索引查询数据时，会先根据索引查找到数据地址，再根据地址查询到具体的数据。并且主键索引和辅助索引没有太多区别。

（2）Innodb中的B+树

Innodb中主键索引的叶子节点的数据区域存储的是数据记录，辅助索引存储的是主键值，所以查询时候少一次IO操作，效率更高

Innodb中的主键索引和实际数据时绑定在一起的，也就是说Innodb的一个表一定要有主键索引，如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引（用户不可见）。另外，Innodb的主键索引要比MyISAM的主键索引查询效率要高（少一次磁盘IO），并且比辅助索引也要高很多。所以，我们在使用Innodb作为存储引擎时，我们最好：

• 手动建立主键索引
• 尽量利用主键索引查询

 

8、为什么一个节点为1页（16K）就够了？

对着上面Mysql中Innodb中对B+树的实际应用（主要看主键索引），可以发现B+树中的一个节点存储的内容是：

• 非叶子节点：主键 + 指针
•  叶子节点：数据

那么，假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，那么一颗高度为2的B+树能存储的数据为：1170 * 16=18720条，一颗高度为3的B+树能存储的数据为：1170 * 1170 * 16=21902400（千万级条）。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。所以也就回答了我们的问题，1页=16k这么设置是比较合适的，是适用大多数的企业的，当然这个值是可以修改的，所以也能根据业务的时间情况进行调整。