索引是什么

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

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

索引类型

InnoDB中，索引类型有三种，普通索引，唯一索引（主键索引是特殊的唯一索引），全文索引

普通（Normal）：非唯一索引，没有任何限制

唯一（Unique）：要求键值不能重复。主键索引是特殊的唯一索引，它还要求键值不能为空。主键索引用primay key创建。

全文（Fulltext）：针对比较大的数据，比如存放的是消息内容，一篇文章，如果需要解决like查询再全文匹配的时候效率较低的问题，可以创建全文索引。只有文本类型的字段才能创建全文索引。

CREATE TABLE 'fulltext_test'(
	'content' varchar(50) DEFAULT NULL,
  FULLTEXT KEY 'content'('content')
);

查询语法

select * from fulltext_test where match(context) against('young' IN NATURAL LANGUAGE MODE);

MyISAM和InnoDB支持全文索引。

B+树

MySQL中的B+Tree特点

关键字的数量跟路数相同
B+Tree的根节点和枝节点中不会存放数据，只有叶子结点才存储数据

搜索到关键字的时候不会直接返回，全部数据都在叶子结点上，所以会找到到最后一层的叶子节点上面。
B+Tree的每个叶子结点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子结点的第一个数据，形成了一个有序链表的结构

数据搜索过程

精确查找：命中之后向下搜寻叶子节点，然后找到需要的数据

范围查询：找到最小的key，顺着节点的和指针顺序遍历就可以访问到所有的数据，极大的提高了区间查询的效率。

InnoDB中B+Tree带来的优势

是BTree的变种，B Tree能解决的问题都能解决。每个节点存储更多的关键字；路数更多
扫库扫表能力更强，如果要对表进行全表扫描，只需要遍历叶子结点，不需要遍历整个B+Tree
B+Tree的磁盘读写能力相对B Tree更强，根节点和枝节点不保存数据区，所以一个节点能保存更多的关键字，一次磁盘加载的关键字更多
排序能力更强，叶子结点上有下一个数据区的指针，数据形成了链表
效率更稳定，B+Tree永远是在叶子结点拿到数据，所以IO次数是稳定的。

HASH

以KV的形式检索数据，根据索引字段生成哈希吗和指针，指针指向数据。

Hash索引的特点

复杂度O(1)，查询速度快。哈希索引里的数据不是顺序存储的，所以不能用于排序
查询数据时需要根据键计算哈希值，所以只能支持等值查询，不支持范围查询。
字段重复时，会出现大量的哈希冲突，采用拉链寻址法解决，效率会低

在InnoDB中，不能显式的创建哈希所有，所谓的支持哈希索引，指的是AHI，自适应哈希，他是InnoDB自动为buffer pool中的热点页创建的索引。

memory存储引擎可以使用哈希索引。

B+Tree落地形式

mysql的数据都是以文件的形式存放在磁盘中的。

show variables like 'datadir';

每张InnoDB的表有两个文件.frm和.ibd，MyISAM的表有三个文件.frm,.MYD，.MYI

有一个相同的文件,frm文件，他是MySQL里面定义表结构的问题件。

MyISAM

MYD文件，D表示Data，是MyISAM的数据文件，存放数据记录

MYI文件，I表示Index，是MyISAM的索引文件，存放索引。比如我们在ID字段创建了一个主键索引，那么这个主键索引就在这个索引文件汇总。一个索引就会有一颗B+Tree，所有额B+Tree都在这个MYI文件中。

数据查找

MyISAM的B+Tree中，叶子节点存储的都是数据文件对应的磁盘记录。所以从索引文件中找到键值后，会到数据文件MYD中获取相应的数据记录。

非主键索引与主键索引一样，都存在MYI文件中，且查找方式也是一样的。

InnoDB

InnoDB的某个叶子节点上，直接存储了数据。

所以在InnoDB中，索引即数据，数据即索引。

聚集索引（聚簇索引）

索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。

InnoDB组织数据的方式就是狙击索引组织表（clustered index organize table）。如果一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序。

除了主键索引之外的索引，不会存放完整的数据记录。

在InnoDB中，如果有注解索引，那么主键索引就是聚簇索引，除了主键索引，其他索引统一叫二级索引。

二级索引存储的是二级索引的兼职。例如在name上建立索引，节点上存的是name的值，他的键值逻辑顺序跟物理行的顺序是不一致的。

二级索引的叶子节点，存的是这条记录对应的主键的值。

所以，二级索引的检索流程是：当用name索引查询一条记录，他会在二级索引的叶子节点找到匹配的值，然后拿到主键，然后再到主键索引的叶子节点拿到数据。

因为存储的地址会发生变化，所以二级索引存储的是主键的值，而不是数据的地址。

因为主键索引比二级索引少扫描了一颗B+Tree，避免的回表，所以它的速度会快一些。

InnoDB创建聚集索引的规则

如果定义了主键，那么InnoDB会选择主键作为聚集索引
如果没有显式的定义主键，则会选择第一个不含有NULL值的唯一索引作为主键
如果没有满足条件的唯一索引，InnoDB会选择内置6字节长的ROWID因为隐藏的聚集索引，他会随着行记录的写入而逐渐递增。

索引使用原则

列的离散度

公式：count(distinct(column_name)):count(*)

列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。

如果在B+Tree里面的重复值太逗，MySQL优化器发现有索引跟用全表扫描差不了多少时，就算建了索引也不一定会走索引。

联合索引最左匹配

联合索引在B+Tree中是复合的数据结构，它是按照从左到右的顺序来简历搜索树的。

最左边的字段是有序的，左边字段相同的时，右边的字段才会有顺序。

所以在创建联合索引时，要把最常用的列放在最左边。

当创建联合索引时，按照最左匹配謮，使用左边的字段去查询时，也能用到索引。

相当于，创建 name和phone的联合索引，相当于创建了name的索引和 name和phone的联合索引

覆盖索引

回表

非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，他比基于主键索引的查询多扫描了一棵树，这个过程就叫回表

在二级索引中，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能取到，不必从数据区中读取，这时候用的索引就叫做覆盖索引，这样就避免的了回表。

索引条件下推（ICP）

索引条件下推（Index condition pushdown），是5.6以后完善的功能，只适用于二级索引。ICP的目标是减少访问表的完整行的读取量从而减少I/O操作。

这里说的下推，实际意思就是把过滤动作在存储引擎中做完，不需要到Server层过滤。

索引的创建和使用

索引的创建

在用于where判断，order排序和join的on，group by的字段上创建索引
索引的个数不要过多，浪费空间且会导致更新变慢。

过长的字段，建立前缀索引

CREATE TABLE 'per_test'(
	'content' VARCHAR(20) DEFAULT NULL,
  KEY 'pre_idx' ('content'(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

区分度低的字段，例如性别，不要建立索引。离散度低，会导致扫描行数过多。
频繁更新的值，不要作为主键或者索引，会导致页分裂
随机无序的值，不建议作为索引，例如身份证，UUID。因为其无需，所以会导致页分裂
组合索引把散裂性高（区分度高）的值放在前面
创建复合索引，而不是修改单列索引

索引失效

在列上使用函数（replace，substr，concat，sum，avg，count等），表达式计算（±*/）
字符串不加引号，出现隐式转换
like条件中，%放在前面。（过滤的开销太大，可以考虑使用全文索引）
负向查询

NOT LIKE 不会使用索引

!=(<>) 和NOT IN 在某些情况下可以使用索引

mysql索引