Mysql的索引

为什么要有索引

因为加速查询，快呀！！！这是我回答面试官的第一句话，哈哈。

首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果
没有索引的话，要从 500 万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。

但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊
的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据
了

在 InnoDB 里面，索引类型有三种

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一
种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key
创建。
全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数
据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型
的字段才可以创建全文索引，比如 char、varchar、text 。全文检索的时候可以使用match匹配内容

mysql的存储结构

表空间、段、簇、页、行。

表空间 table space：表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、临时表空间、Undo 表空间。
段 segment:表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等，段是一个逻辑的概念创建一个索引会创建两个段，一个是索引段:leaf node segment，一个是数据段: non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以2。
簇 Extent:一个段(Segment)又由很多的簇(也可以叫区)组成，每个区的大小是 1MB(64 个连续的页)。每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇
页 Page:

为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的页(Page) 组成的空间，一个簇中有 64 个连续的页。 (1MB/16KB=64)。这些页面在物理上和逻辑上都是连续的。
跟大多数数据库一样，InnoDB 也有页的概念(也可以称为块)，每个页默认 16KB。页是 InnoDB 存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。
一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据。

注：page是操作系统的叫法，在硬件中被称为块。
SHOW VARIABLES LIKE 'innodb_page_size'; 查看innodb的page_size;

B+Tree特点

它的关键字的数量是跟路数相等的;
B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的单元(键值+指针)，代表有 1170 个指针。
树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170117016=21902400。
在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
它是根据左闭右开的区间 [ )来检索数据
数据查找过程：
1. 比如我们要查找 28，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，28 是[28,66)的左闭右开的区间的临界值，所以会走中间的子节点，然后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的数据。
2. 如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高21了区间查询效率(不需要返回上层父节点重复遍历查找)

为啥不用红黑树？

二叉树，数据多就会导致数太高，分路次数就会多；还有就是红黑树基本上都是放在内存里的。

mysql中的自适应hash

show variables like 'innodb_adaptive_hash_index' 可以查看是否开启自适应hash。
它是对访问热点B+索引增加hash索引，方便直接定位到B+的索引所对应的数据。

聚簇索引

它实际上是一种数据的存储顺序，逻辑上我们认为数据会按照主建进行排序，实际上这个是和聚簇索引一致的。也就是说聚簇索引就是物理存储顺序。
这就是为什么说要手动指定一个主建索引，并且最好是自增整数。如果指定了唯一主建是那种hash值，就没有顺序可言了。查询的时候就会出现随机IO了。

索引使用原则

离散度高的效果更好
联合索引最左前缀匹配
高效使用覆盖索引

这样就不要回表查询数据了。也是为什么不建议使用* 代替字段，因为不可能给所有字段加进去

索引下推

所有的查询先是查询的执行引擎进行查询，然后会把结果交给server过滤。如果能让查询直接在执行引擎中完成过滤，这样的过程被称为是下推。
通过设置打开下推：set optimizer_switch='index_condition_pushdown=on';

创建索引建议

在用于 where 判断 order 排序和 join 的(on)字段上创建索引
索引的个数不要过多。浪费空间，更新变慢。
区分度低的字段，例如性别，不要建索引。离散度太低，导致扫描行数过多。
频繁更新的值，不要作为主键或者索引。页分裂
组合索引把散列性高(区分度高)的值放在前面。
创建复合索引，而不是修改单列索引
过长的字段，建议使用前缀索引。前缀太长浪费空间，太短可能冲突，自己试着来。
不建议使用无序的值作为索引

a).插入性能：B+tree按照顺序插入的时候能避免分裂。物理磁盘上连续，顺序写入会更快。
b).查询性能：局部性原理，数据库通常会使用缓存来提高查询性能，查询的时候会查出来一整页。不连续的数据查出来的意义不大，浪费空间。如果遇到范围查询，就无法发挥所有的优势。
c).由于无序值的插入和更新会导致索引频繁地调整，随着时间的推移，索引结构中会产生大量的碎片。这些碎片是指索引页中未被充分利用的空间或者是不连续的索引块。对于无序的长字符串索引（如 UUID），由于其本身长度较长且没有规律，索引占用的空间会比较大。