MySQL索引篇

1 为什么需要索引？

提高数据库的查找性能。但是查找性能的提高是以插入、更新、删除速度的降低为代价，因为这些是写操作，增加了大量IO。它的价值，在于提高海量数据的检索速度。

IO操作效率低的原因：
IO其实是在写磁盘，因为它本身的物理结构，磁头扫描磁盘，肯定不如CPU、内存这些使用晶体管作为内部结构的电子元件的速度快。相差太多了。

2 mysql与磁盘的交互为什么采用页page的方案而不是采用用多少，加载多少？

首先，结构方面：
数据库的索引和数据都存储在硬盘。
1 mysql中的数据文件以页page为单位保存在磁盘当中。
MySQL InnoDB引擎 和磁盘进行数据交互的基本单位是 16KB，这一个个的基本数据单位，在 MySQL 中叫做page（注意和系统的page区分）
mysql的CRUD操作需要计算插入对应位置。需要找到对应要修改的或查询的数据。以页为单位操作，也就是16KB为单位操作方便管理，方便写磁盘。
2 其次，效率方面：
页管理的方式，能减少IO次数，提升效率。IO效率低下，是因为IO次数太多，而不是每次操作太慢，所以要减少IO次数。 把多个内容放在一个表里，通过索引的方式快速定位 ，而不是说分散一条数据一个很特别的位置。

3 索引为什么是B+树结构？为什么B+树优于AVL树、红黑树？跳表行不行？（谈谈你对mysql索引的理解）

1 MySQL 是会将数据持久化在硬盘，而存储功能是由 MySQL 存储引擎实现的，所以讨论 MySQL 使用哪种数据结构作为索引，实际上是在讨论存储引使用哪种数据结构作为索引，InnoDB 是 MySQL 默认的存储引擎，它就是采用了 B+ 树作为索引的数据结构。
2
简答：
我们希望IO操作次数，用树结构，一层深度就是一次查表操作。
细说：
1 mysql的文件的存储以page页为单位，每次IO其实是访问磁盘KV，但我们直接用一张表存所有页，是存不下的，而用多级页表的形式，既节省存储查找页表索引的空间，也可以通过索引的方式快速定位到所需要的页，从而提高效率。所以mysql数据中查找是索引结构的，我们查找数据是需要靠索引查找的 。
2 mysql中每个表都有一个主键，表的行数据需要通过主键索引可以获得，也就是KV结构。这种KV结构适合用树来存储 。
3 然后用B+树是因为B+树只用叶子节点存值而非叶子节点只存索引的话，这样3层的B+树就能存2千190万以上条数据。而且3层B+树的查找，只需要3次IO。而B树，因为叶子节点和非叶子节点都是既存索引又存值，所以它的3层结构没有B+树更优，能存的数据更多。
1170117016=21902400行数据。
4 为什么不用跳表：因为跳表无法满足mysql的排序要求，因为其实是链表结构。此外，不利于查找区间信息，效率和空间利用率较低。

4 为什么3层B+树可以存2100万条以上数据？

1 innodb存储引擎中，最小的存储单位是页，而一个页默认大小是16KB，代表B+树每个节点可以存16KB数据（叶子节点）。
2 假设一行数据是1KB（1024字节），一个节点能存16行数据。非叶子节点存放的是主键和指针，假设主键类型：8字节，指针是6字节。1个节点大概存1170个主键和指针。1页16KB，所以1个节点，也就是1个页存放161024/14 = 1170个主键和指针。所以用1170条索引。而这1170条索引指向1170个节点，每个节点里面又能存16行数据，161170 = 18720 条索引。前两层都是非叶子节点。所以到了第三层的时候，前面的18720条索引能指向18720个节点，每个节点能存1170个主键和指针，相当于1170条数据的索引，所以3层的表结构，就能指向18720*1170条数据。所以3层的B+树能做2千1博万条以上的数据的映射。这些映射都对应着具体的某一表中的行数据。
所以最多4次IO，就能拿到mysql中的某一行数据。
简单说，B+树是最胖的树。

为什么指针可以用6字节，来自于计算机存储体系结构中的一个常见规律：指针通常需要与计算机的字长相同或稍小，以便在内存中存储和处理。由于现代计算机一般采用64位（8字节）字长，因此可以假设指针占用6个字节，留2个字节作为补充空间或其他元数据的存储。

4 为什么比AVL和红黑树好，红黑树是更自由的AVL树，减少了插入不平衡时候的旋转调整，效率高于AVL树，且对于单支AVL树的情况在红黑树上它不会出现，但是它是二叉树，每次查索引表都是一次IO，简单说，红黑树太高了，需要的IO操作次数远多于B+树。

5 常见索引类型

聚簇索引：根据索引，能查到数据本身，叶子存的是实际数据。
非聚簇索引（非聚簇索引）：根据索引，查到的叶子是个主键值，需要回表操作，也就是再根据这个主键去查。
主键索引：就是在主键上建立的索引，最好是自增的。
唯一索引：限制字段值必须唯一，以便更快查询
普通索引：普通字段上加的索引。
全文索引：对整个内容做索引，如NLP场景文章长度较短时。
复合索引：多个列组成的索引。又叫联合索引。
覆盖索引：select的数据只用从索引中就能获得，不必读取数据行，简单说，查询列要被索引覆盖。覆盖索引可能比聚簇索引快，因为不必往下一级级查找，叶子就包含它的数据。

6 什么时候为要用非聚簇索引（二级索引）？如何加？

1 比如常排序的列，它不是主键列（主键列是聚簇索引），所以这里一般是非聚簇索引，而且主键必须唯一不重复，而经常排序的列，可能有重复值，比如学生成绩表中的成绩，所以难免重复，所以这列肯定不能用聚簇索引。一般主键列就是聚簇索引，在值不唯一的列上，使用的肯定是非聚簇索引。
2 频繁修改和更新的索引列，比如销售平台某些商品的数量，不能用聚簇索引，商品数量非常容易相等，不唯一那就是非聚簇索引。此外，联合索引和非聚簇是交集的关系。它除了是非聚簇索引，可能还属于其它类型索引。聚簇和非聚簇只是按物理存储分类的。
3 外键列和主键列。有时候非聚簇索引也可以建立在主键列上，因为它占用
4 经常被用于分组的列可以建主键，也可以建立非聚簇索引。

· 非聚簇索引的优点
· 非聚簇索引的优势是占用空间小，远小于聚簇索引列。虽然可能涉及回表，但是扫描速度快。

方法：
建表的时候，加入index()，也可以用alter改变表结构，再加上index()和需要的字段。

7 什么时候需要索引？什么时候不需要索引？

索引的本质：是一种排序好的数据结构。一般是B+树。
需要：索引是用B+树维护的，当场景是查找频繁时最适合。
详细：
频繁用where条件查询：频繁查询的字段列创建索引可以避免查询数据走全表扫描。效率就提升了。
关联字段：一般用主键来进行两张表的关联。
排序字段：B+树结构的索引天然有序。
分组字段：可以建立索引，因为分组的前提是排序
统计字段：可以建立索引，经过索引用B+树，利用天然的去重后，数据都比较聚集，也方便统计。

不需要：表数据比较少、频繁更新【因为B+树需要频繁修改啊，肯定不行，可能是修改、也能是增、删等操作，影响太大了】、参与计算的列、where条件中用不到的字段不适合建立索引。

8 聚簇索引和非聚簇索引

只要主键存在，聚簇索引就是主键索引；当没有给定主键，mysql默认选择唯一不重复的列做主键索引。
非聚簇索引：非主键索引，因为该索引建立在非主键字段，它的底层叶子节点是主键索引，因为根据主键索引能唯一定位到一行数据。而非聚簇索引一般建立在普通字段，值并不唯一，叶子节点不能放它的值，所以需要回表。

8 如何减少非聚簇索引的回表操作

首先，非聚簇索引不一定会做回表查询，因为可能该列命中了所查值。就不需要回表了。
1 使用覆盖索引，索引能覆盖所查列，就能避免回表。
2 尽可能覆盖所查的列。
3 进来使用聚簇索引。
4 mysql5.6之后有索引下推，对索引中包含的字段先做判断，过滤掉不满足条件的记录。减少回表次数。
比如如下关联索引中，使用的B+树键值是：名字+年龄，做的是模糊查询：
where name link’张%’ and age = 10。
比如以姓名和年龄建的B+树如下：

mysql存储索引层根据id(1, 4)逐一做回表扫描，去聚簇索引找到完整的行记录。定位到id为1~4，但是其实1~4中还包含了其它不合符条件的数据，因为年龄不是10，根据两条记录1,4都做了回表两次。然后 通过这个1~4范围内的数据让server筛选age == 10的数据。如下做了两次回表。

而用到索引下推，1次回表就行了，第二个回表就不做了，因为已经发现这个范围超了。

索引下堆适合条件：

1 只能用于range、 ref、 eq_ref、ref_or_null访问方法；
2 只能用于InnoDB和 MyISAM存储引擎及其分区表；

对存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

9 索引失效场景

1 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
使用了通配符使mysql无法使用B+树索引，只能全表扫描。
模糊查询范围较大，没必要建立索引了。
因为索引不知道怎么建立。
当模糊查询的两个字段都包含在索引中，覆盖索引，会不会失效。
2 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；因为索引是根据当前列做的，如果当前列的值改变，已经改变了。
3 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
4 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。因为索引要包括全部的条件字段。

10 索引优化手段：

0 选用经常作为查询条件的列进行索引，且避免重复值太多的列作为索引，要选合适的列去建索引。
1 使用复合索引而不是索引组合。
2 索引长度不要太长，越短效率越高
3 索引命名应该可读性高
4 定期监控索引的使用情况，通过慢查询日志来发现哪些查询慢，而考虑增加或删除某些索引。
简单说，选择正确的索引类型、正确的列、使用多列索引代替索引组合、避免删除和修改并且要定期监控和优化索引。

---

以下是不太重要了解即可

11 什么是最左前缀原则

就是判断几个条件时，先判断最左条件是否满足。