Mysql关于索引问题的问答

索引的目的

是加快IO速度。
有两个维度设计索引，减少IO量、减少IO次数。
这个sql会查出innodb相关参数
SHOW VARIABLES LIKE “%INNODB%”;
不同的存储引擎存的数据文件和索引文件是不一样的。

问题1：索引存在内存中还是存在磁盘中？

答案：两者都存

引申问题：为什么两者都存？

1. 内存关机后数据就不在了，如果宕机重启后重新建立索引，那么代价索引会很大。
2. 内存资源宝贵，不肯能把索引全部加载到内存，而是每次次加载页大小（16K）的整数倍的索引到内存。这个情况下索引文件如果为1T，那么只要读某几块的热数据就好了
3. 因为操作系统系统有局部性原理和磁盘预读，
   局部性原理： 数据和程序都有聚集成群的倾向，之前被加载过的数据会很快再次加载。
   磁盘预读： 在进行数据交换的时候，会有一个基本的逻辑单位，成为页（4K、8K、16K），每次至少加载一页或者页的整数倍数据。
4. 为什么要每次加载页的整数倍数据？
   防止下次查询同一页数据，需要再次从硬盘加载。

问题2：Mysql索引采用的数据结构是什么结构

答案：B+Tree
叶子节点才存数据，非叶子节点，存索引，不存数据。
B+Tree每一个节点大小是固定的。默认16K

引申问题：为什么不使用AVL树（自平衡二叉查找树）、红黑树、平衡二叉树、BST（二叉搜索树）？

他们的共同特征有且至多有两个子节点，那么深度必定很大。查找效率就会降低。
红黑树、AVL树、BST 是有序树。

引申问题：为什么不适用Hash表？

hash索引在做等值查询的时候，效率会优于B+Tree

1. hash表在做范围查找会很麻烦。
2. hash表必定会出现hash冲突，如果像Java那样hashmap的结构，那么会极度的浪费存储空间。数组加链表，数组会有很多空间是空闲的。
3. hash不能分块读取。
4. 但是Memory存储引擎中用的是Hash索引。

引申问题：为什么不用BTree？

1. B+Tree的数据都集中在叶子节点。分支节点 只负责索引。 BTree的分支节点也有数据 。 b+树的层高 会小于 B树 平均的Io次数会远大于 B+树
2. B+Tree更擅长范围查询。叶子节点 数据是按顺序放置的双向链表。BTree范围查询只能中序遍历。
3. 同样的空间，B+Tree比BTree能存更多的索引。

假设一页为16K，一行数据128字节，索引的值为8字节。页指针8字节。

三层BTree能存多少数据?
一层113 条数据
二层 113* 113 = 12769
三层 113 * 113 * 113 = 1,442,897。
可知三层的BTree只能存150万左右的数据。

三层B+Tree能存多少数据？考虑聚簇索引
一层 1024
二层 1024 * 1024 = 1,048,576
三层需要存数据，三层的空间为 1048576 * 16384 字节。
那么存数据能存1,1848,9088条,也就是1亿多条

如果考虑极限数据16368字节 + 索引8字节 + 指针8字节 总共16384 字节，那么B+Tree能存多少数据？
1层1024
2层 1024 * 1024 = 1,048,576
3层 因为存数据，一行就一页，
那么 也只能存 1,048,576 条，一百多万条？

问题3：Mysql索引中存储什么样的数据？

innoDB中存储引擎中

1. 聚簇索引
   索引 非叶子节点 仅仅存 索引值 加 页指针， 叶子节点 存 索引值 + 数据。
2. 非聚簇索引
   索引 非叶子节点 仅仅存 索引值 加 页指针， 叶子节点 存 索引值 + 主键值。

问题4：为什么要使用长度尽量短的字段建立索引？为什么尽量使用定长数据类型建立索引

定长字段建立索引这样索引的结构更规整。
尽量短的字段建立索引，每一页数据会存更多的索引。
另外尽量使用数值类型索引，也是因为这样索引好排序。

问题5：聚簇索引和非聚簇索引区别

• 聚簇索引：数据和索引放在一起
  innoDB 插入数据的时候必须和一个索引值进行绑定，一般是主键，没有主键用唯一列，没有唯一列使用一个6字节的row_id。
  聚簇索引叶子节点除了保存索引值还保存了这一行数据。
  数据一定会和索引聚集存放。.ibd 文件中既有数据也有索引。
• 非聚簇索引：数据和索引不放在一起，叶子节点存的是索引值和主键值。通过非聚簇索引查数据，需要扫描两颗树（先扫自己的非聚簇索引树，再扫聚簇索引树，然后返回对象。）

问题6：一个表中，最多有多少索引？

答案：聚簇索引只有一个，非聚簇索引n个。
因为聚簇索引和数据放在一块的，如果每一个索引都和数据放一起，那么数据就冗余了很多份，浪费了空间。

问题7： 什么是回表？

从非聚簇索引跳转到聚簇索引，查找数据的过程，称之为回表。回表效率比较高，但是还是尽量避免回表。
什么是索引覆盖？
查找的值在索引中就有，那么就直接返回
比如：select id, name from test where name = “zhangsan”; name为普通索引。这个语句索引就能返回结果，不需要回表。

问题8：什么是最左匹配？

假设为某张表建立 index(a, b, c)
下面语句会满足最左匹配原则

1. where a = 1 and b =1
2. where a = 1
3. where b=1 and a= 1 , 这个语句会被SQL执行优化器优化成 where a = 1 and b =1

问题9：什么是索引下推？

select id, name from test where name = “zhangsan” and age = 18， name，age为组合索引。
在没有索引下推之前： 会先根据name从存储引擎拉取数据，在到sever中过滤。
有了索引下推： 会根据name，age直接从存储引擎中检索数据，不需要再sever过滤。
索引下推mysql5.6 之前不支持。