索引基本知识

索引的本质：索引是数据结构。
innoDB 存储引擎支持以下几种常见的索引： B+树索引(重点)、全文索引、哈希索引
哈希索引缺点:

  1. 不能实现范围查找
  2. 不能支持orderby排序
  3. 无法支持联合索引a ab 只能abc
  4. 大数据量时hash冲突概率大

**

B+树

什么是B树

b树是平衡二叉树演化而来,但不是二叉树,是平衡查找树(多叉)

image.png
B+树与B树区别在于B+树只在叶子节点上存放真正的数据
image.png
(是个双向链表)

特点:
1、相同节点数量的情况下，B+树高度远低于平衡二叉树；
2、非叶子节点只保存索引信息和下一层节点的指针信息，不保存实际数据
记录；
3、每个叶子页（LeafPage）存储了实际的数据，比如上图中每个叶子页就
存放了 4 条数据记录，当然可以更多，叶子节点由小到大（有序）串联在一起，
叶子页中的数据也是排好序的；
4、索引节点指向该节点的左子树比这个索引值小，而右子树大于等于这个
索引值。

数据库选择b+树的原因

机械磁盘原理图如下
image.png
根据局部性原理(当一个数据被用到时，其附近的数据也通常会马上被使用。),所以为了减少IO磁盘采用预读策略,预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，
硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储 块称为一页，页大小通常为 4k 当然也有 16K 的，主存和磁盘以页为单位交换数据。按照磁盘的这种性质，如果是一个页存放一个 B+树的节点，自然是可以存 放很多的数据的，比如 InnoDB 里，默认定义的 B+树的节点大小是 16KB，这就是 说，假如一个 Key 是 8 个字节，那么一个节点可以存放大约 1000 个 Key，意味 着 B+数可以有 1000 个分叉。同时 InnoDB 每一次磁盘 I/O，读取的都是 16KB 的 整数倍的数据。也就是说 InnoDB 在节点的读写上是可以充分利用磁盘顺序 IO 的 高速读写特性。
同时按照 B+树逻辑结构来说，在叶子节点一层，所有记录的主键按照从小 到大的顺序排列，并且形成了一个双向链表。同一层的非叶子节点也互相串联， 形成了一个双向链表。那么在实际读写的时候，很大的概率相邻的节点会放在相 邻的页上，又可以充分利用磁盘顺序 IO 的高速读写特性。所以我们对 MySQL 优 化的一大方向就是尽可能的多让数据顺序读写，少让数据随机读写。

innodb索引分类

聚集索引/聚簇索引: 用主键构建的b+树,叶子结点是该行所有数据.通过过聚集索引能获取完整的整行数据。
另一个优点是：对于主键的排序查找和范围查找速度非常快。
辅助索引/二级索引: 以其他列作为索引,叶子节点为此数据+主键(因为是主键而不是全部数据所以会导致回表).叶子节点除了包含键值以外，每个叶子节点中的索
引行中还包含了一个书签( bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可 以找到与索引相对应的行数据。因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键
image.png
什么是回表

image.png
联合索引/复合索引: **，将表上的多个列组合起来 进行索引我们称之为联合索引或者复合索引，比如 index(a,b)就是将 a,b 两个 列组合起来构成一个索引。，建立联合索引只会建立 1 棵 B+树，多个列分别建立索引 会分别以每个列则建立 B+树，有几个列就有几个 B+树

image.png
覆盖索引/索引覆盖: 直接从辅助索引中查就能得到数据,而不是回表从主键索引中查,使用覆盖索 引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的 IO 操作。所以记住，覆盖索引并不是索引类型的一种。
image.png
自适应哈希索引 :
image.png
image.png
image.png
全文检索之倒排索引
image.png
image.png
**

请记住：
1、一个索引就是一个 B+树，索引让我们的查询可以快速定位和扫描到我们
需要的数据记录上，加快查询的速度。
2、一个 select 查询语句在执行过程中一般最多能使用一个二级索引，即
使在 where 条件中用了多个二级索引。

扫描区间


image.png
image.png
image.png

详见享学3期,mysql笔记 3.2.3

**

高性能的索引创建策略

1.索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有
TTNYINT、NEDUMNT、INT、BIGTNT 这么几种，它们占用的存储空间依次递增，
我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数
范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整
数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不
要使用 BIGINT，能使用 NEDIUMINT 就不要使用 INT，这是因为:
·数据类型越小，在查询时进行的比较操作越快（CPU 层次)
·数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下
更多的记录，从而减少磁盘/0 带来的性能损耗，也就意味着可以把更多的数据
页缓存在内存中，从而加快读写效率。
这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，
其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的
数据类型，也就意味着节省更多的存储空间和更高效的 I/0。

2. 索引选择性和前缀索引

索引应该尽量离散
有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提
到过的模拟哈希索引。
模拟哈希索引： order_exp 表中 order_note 字段很长，想把它作为一个索引，我们可以增加
一个 order_not_hash 字段来存储 order_note 的哈希值，然后在 order_not_hash 上
建立索引，相对于之前的索引速度会有明显提升，一个是对完整的 order_note
做索引，而后者则是用整数哈希值做索引，显然数字的比较比字符串的匹配要高
效得多
但是缺陷也很明显：
1、需要额外维护 order_not_hash 字段；
2、哈希算法的选择决定了哈希冲突的概率，不良的哈希算法会导致重复值
很多；
3、不支持范围查找。
还可以做些什么改进呢？还可以索引开始的部分字符，这样可以大大节约索
引空间，从而提高索引效率。但这样也会降低索引的选择性。一般情况下我们需
要保证某个列前缀的选择性也是足够高的，以满足查询性能。（尤其对于 BLOB、
TEXT 或者很长的 VARCHAR 类型的列，应该使用前缀索引，因为 MySQL 不允许索
引这些列的完整长度）。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便
节约空间)。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换
句话说，前缀的“基数”应该接近于完整列的“基数”。
为了决定前缀的合适长度，可以找到最常见的值的列表
image.png
缀列表进行比较image.png

3.只为用于搜索、排序或分组的列创建索引

也就是说，只为出现在 WHERE 子句中的列、连接子句中的连接列创建索引，
而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引；
又或者为出现在 ORDER BY 或 GROUP BY 子句中的列创建索引

4. 多列索引

image.png