Mysql---索引篇

1、索引的分类

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引
创建的主键索引和二级索引默认使用的是 B+Tree 索引。
B+树详解
https://mp.weixin.qq.com/s/w1ZFOug8-Sa7ThtMnlaUtQ
https://zhuanlan.zhihu.com/p/86137284
https://mp.weixin.qq.com/s?__biz=MzUxODAzNDg4NQ==&mid=2247502059&idx=1&sn=ccbee22bda8c3d6a98237be769a7c89c&scene=21#wechat_redirect

——

按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）
主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

——

按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

——

按「字段个数」分类：单列索引、联合索引。
建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。区分度=distinct(column)/count(*)

 2、什么时候需要 / 不需要创建索引？

需要考虑
需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

3、有什么优化索引的方法？

前缀索引优化；
使用某个字段中字符串的前几个字符建立索引，前缀索引可以增加一个索引页中存储的索引值的同时帮助我们减小索引项的大小
但是前缀索引有一定的局限性，order by 就无法使用前缀索引；无法把前缀索引用作覆盖索引；

——

覆盖索引优化；
覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引

——

主键索引最好是自增的；
要考虑到插入数据时导致页分裂的情况，页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

——

防止索引失效；
https://mp.weixin.qq.com/s/lEx6iRRP3MbwJ82Xwp675w
还有一种情况就是，当索引的区分度很低并且查询范围很大时，mysql优化器也会考虑到回表的情况，选择全表扫描，而不是走索引

4、为什么 MySQL 采用 B+ 树作为索引？

https://xiaolincoding.com/mysql/index/why_index_chose_bpuls_tree.html
MySQL 是会将数据持久化在硬盘，而存储功能是由 MySQL 存储引擎实现的，所以讨论 MySQL 使用哪种数据结构作为索引，实际上是在讨论存储引擎使用哪种数据结构作为索引，InnoDB 是 MySQL 默认的存储引擎，它就是采用了 B+ 树作为索引的数据结构。

——

要设计一个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘，硬盘是一个非常慢的存储设备，我们在查询数据的时候，最好能在尽可能少的磁盘 I/0 的操作次数内完成。

——

二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致二分查找树退化成一个链表，此时查询复杂度就会从 O(logn)降低为 O(n)。

——

为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。

——

而树的高度决定于磁盘 I/O 操作的次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

——

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

——

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

——

①B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

②B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

5、MySQL 单表不要超过 2000W 行，靠谱吗？

靠谱，假设，非叶子节点内指向其他页的数量为 x，x=15*1024/12≈1280
页的大小是16K，File Header(38 byte)、Page Header(56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概1k左右，剩下15k用于存数据

叶子节点内能容纳的数据行数为 y，按一条行数据 1k 来算，那一页就能存下 15 条，Y = 15*1024/1000 ≈15

B+ 数的层数为 z，（2或者3）

Total =x^(z-1) *y 也就是说总数会等于 x 的 z-1 次方 与 Y 的乘积。
所以B+ 树是两层时，Total=（1280 ^1 ）*15 = 19200，为三层时，Total = （1280 ^2） *15 = 24576000 （约 2.45kw）

——

MySQL 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。
页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。
在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。
索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

 6、索引失效有哪些？

：当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
：当我们在查询条件中对索引列使用函数，就会导致索引失效。
：当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
：MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
：联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
：在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
：还有一种情况就是，当索引的区分度很低并且查询范围很大时，mysql优化器也会考虑到回表的情况，选择全表扫描，而不是走索引