MySQL索引-基础

索引

索引 （index）是帮助MySQL高效获取数据的数据结构（有序）

索引存在的意义：省略遍历的过程，加快查找速度

查找速度加快的同时付出了一定代价：

• 需要付出额外的空间代价来保存索引数据
• 索引可能会拖慢新增、删除、修改的速度
• 总体来说是比较划算的，实际开发中查询场景一般比增删改频率高很多

索引背后的数据结构：B+树

B/B-树：B树可以认为是一个 N 叉搜索树，降低了树的高度，减少了访问磁盘的次数

B+树：

• 一个节点可以存储 N 个 key，N 个 key 划分出了 N 个区间
• 每个节点中的 key 值 ，都会在子节点中存在（同时该 key 是子节点的最大值） 整个树的所有数据都是包含在叶子节点中的
• B+ 树的叶子节点，是首尾相连，类似于一个链表
• 由于叶子节点是完整的数据集合，只在叶子节点这一行存储数据表每一行的数据，而非叶子节点只存储 key 值本身即可

B+树的优势：

1.当前一个节点保存更多的 key ，最终树的高度是相对更矮的，查询的时候减少了 IO 访问次数

2.索所有的查询最终都会落到叶子节点上（查询任何一个数据，经过的 IO 访问次数是一样的）

3.B+树的所有叶子节点构成链表，此时比较方便进行范围查询

4.由于数据都在叶子节点上，而非叶子节点只存储 key，导致非叶子节点占用的空间是比较小的，这些非叶子节点可能在内存中缓存，进一步减小了 IO 次数

如果表中存在多个索引怎么处理：针对 id 有主键索引，针对 name 又有一个索引

​ 表的数据还是按照 id 为主键，构建出 B+ 树，通过叶子节点组织所有的数据行

​ 其次，针对 name 这一列，会构建另一个 B+树索引，但这个 B+ 树的叶子节点就不在存储这一行的完整数据，而是存主键 id

​ 此时，如果通过 name 来查询，查询到的叶子结点只是主键 id ，还需要再通过主键 id 去主键的 B+ 树再查询一次，称作 回表

Hash索引：

采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储到hash表中，产生hash冲突使用链表来解决

特点：

• hash索引只能用于对等比较（ =、in ） ，不支持范围查询（between，>，<）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就够了，效率通常要高于B+tree索引

MySQL中，支持hash索引的是Memory引擎，InnoDB中有自适应hash功能，hash索引是存储引擎根据B+索引在指定条件下自动构建的

为什么InnoDb存储引擎采用 B+tree 索引结构

• 相对于二叉树层级更少，搜索效率更高
• 对于 B-tree，无论是叶子还是非叶子节点都会保存数据，这样会导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
• 相对 Hash 索引，B+tree支持范围查询及排序操作

索引分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

根据索引的存储索引分类

分类	含义	特点
聚集索引	将数据存储与索引放到了一块，索结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

回表查询：二级索引查到主键值，到聚集索引中查找行数据

InnoDB主键索引的B+tree高度为多高？

假设：
	一行数据大小为1K，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8
高度为2：
	n * 8 + （n + 1） * 6 = 16 * 1024，得 n 为 1170
	1171 * 16 = 18736
高度为3：
	1171 * 1171 * 16

索引语法

SHOW INDEX FROM table_name	查看索引
CREATE INDEX index_name ON table_name(col_name)	创建索引
DROP INDEX index_name ON table_name	删除索引

注：创建索引操作很危险! 如果表里的数据很大，这个建立索引的开销会很大