索引定义#

MySQL 官方对索引的定义为：索引（index）是帮助 MySQL 高效获取数据的数据结构（有序）。索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :

其实简单来说，索引就是一个排好序的数据结构

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

索引优势#

• 加快查找和排序的速率，降低数据库的 IO 成本以及 CPU 的消耗
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引劣势#

1. 索引实际上也是一张表，保存了主键和索引字段，并指向实体类的记录，本身需要占用空间
2. 虽然增加了查询效率，但对于增删改，每次改动表，还需要更新一下索引
   1. 新增：自然需要在索引树中新增节点
   2. 删除：索引树中指向的记录可能会失效，意味着这棵索引树很多节点，都是失效的
   3. 改动：索引树中节点的指向可能需要改变

但实际上呢，我们 MySQL 中并不是用二叉查找树来存储，为何呢？

要知道，二叉查找树，此处一个节点只能存储一条数据，而一个节点呢，在 MySQL 里边又对应一个磁盘块，这样我们每次读取一个磁盘块，只能获取一条数据，效率特别的低，所以我们会想到采用 B 树这种结构来存储。

索引结构#

索引是在 MySQL 的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，而且也不是所有的引擎都支持所有的索引类型。

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有 Memory 引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB 从 Mysql5.6 版本开始支持全文索引。

MyISAM、InnoDB、Memory 三种存储引擎对各种索引类型的支持

我们平常所说的索引，如果没有特别指明，都是指 B + 树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

BTREE#

多路平衡搜索树，一棵 m 阶 (m 叉) BTREE 满足：

• 每个节点最多 m 个孩子
  • 孩子个数：ceil (m/2) 到 m
  • 关键字个数：ceil (m/2)-1 到 m-1

ceil 表示向上取整，ceil (2.3)=3

插入关键字案例#

保证不破坏 m 阶 B 树的性质#

由于 3 阶，最多只能 2 个节点，所以一开始 26 和 30 在一起，之后再来个 85 就要开始分裂了，30 作为中间上位，26 保持，85 去到右边
即：中间位置上位，然后左边留在旧节点，右边去到新结点

如图中的 70 再插入的时候，70 刚好是中间位置上位，然后 62 保持，85 又去分一个新节点出来

上位后又需要分裂#

继续向上分裂即可，同理的

相比优势#

相比二叉搜索树，高度 / 深度更低，自然查询效率更高。

B+TREE#

• B + 树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点。
• 内部结点中的 key 都按照从小到大的顺序排列，对于内部结点中的一个 key，左树中的所有 key 都小于它，右子树中的 key 都大于等于它。叶子结点中的记录也按照 key 的大小排列。
• 每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
• 父节点存有右孩子的第一个元素的索引。

​

相比优势#

• B+Tree 的查询效率更加稳定。由于 B+Tree 只有叶子节点保存 key 信息，查询任何 key 都要从 root 走到叶子，所以更稳定。
• 只需遍历叶子节点，就可以实现整棵树的遍历。

MySQL 中的 B+Tree#

MySql 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针 (整体类似一个双向链表的结构)，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

细心的同学可以看出，这张图跟我们的二叉查找树简图的一个最大区别是什么？

• 从二叉查找树过渡到 B 树，有一个显著的变化就是，一个节点可以存储多个数据了，相当于一个磁盘块里边可以存储多个数据，大大减少了我们的 IO 次数！！

MySQL 中的 B+Tree 索引结构示意图：

二叉查找树简图：

索引原理#

BTree 索引：#

初始化介绍#

浅蓝色的称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示)
如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3,
P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。

• 真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。`
• 非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中。`

查找过程#

如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO。在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中通过二分查找搜索到 29，结束查询，总计三次 IO。

真实的情况是，3 层的 B + 树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

🎈索引分类#

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B + 树索引模型，所以数据都是存储在 B + 树中的。

每一个索引在 InnoDB 里面对应一棵 B + 树。
假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。
这个表的建表语句是：

mysql> create table T( 
  id int primary key, 
  k int not null,  
  name varchar(16), 
  index (k))engine=InnoDB; 
Copy

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下：

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引#

数据表的主键列使用的就是主键索引，且会默认创建，这也是为什么，我们还没学索引的时候，老师经常跟我们说根据主键查会快一点，原来主键本身就建好了索引。
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

辅助索引#

辅助索引的叶子节点内容是主键的值。在 InnoDB 里，辅助索引也被称为二级索引（secondary index）。

如下图：

• 主键索引存放了整行数据
• 辅助索引只存放了自己本身，以及 id 主键用于回表查询

根据上面的索引结构，我们来讨论一个问题：基于主键索引和辅助索引的查询有什么区别？

• 如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B + 树；
• 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于辅助索引的查询需要多扫描一棵索引树。因此，我们在应用中应当尽量使用主键查询。

除非说，我们所要查询的数据，刚好就是我们索引树上存在的，此时我们称之为覆盖索引–即索引列中包含了我们要查询的所有数据。

同时，二级索引又分为了如下几种 (先简单略过即可，后续我们再慢慢了解)：

• 唯一索引 (Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
• 普通索引 (Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
• 前缀索引 (Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
• 全文索引 (Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引

🧵扩展–索引下推#

所谓下推，顾名思义，其实是推迟我们的回表操作，MySQL 不会轻而易举让我们去回表，因为很浪费。什么意思呢？来看下边这个例子。

我们建立了一个复合索引 (name，status)，索引中也是按这个字段来存储的，类似图中这样：

复合索引树 (只存储索引列和主键用于回表)

我们执行这样一条语句：

SELECT * FROM tb_seller WHERE name like '小米%' and status ='1' ;
Copy

1. 首先我们在复合索引树上，找到了第一个以小米开头的 name – 小米 1

2. 此时我们不着急回表 (回到主键索引树搜索的过程，我们称为回表)，而是先在复合索引树判断 status 是否 = 1，此时 status=0，我们直接就不回表了，直接继续找下一个以小米开头的 name

3. 找到第二个– 小米 2，判断 status=1，则根据 id=2 去主键索引树上找，得到所有的数据

这种先在自身索引树上判断是否满足其他的 where 条件，不满足则直接 pass 掉，不进行回表的操作，就叫做索引下推。

最左前缀原则#

所谓最左前缀，可以想象成一个爬楼梯的过程，假设我们有一个复合索引：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：

1. 按楼梯从低到高，无出现跳跃的情况–此时符合最左前缀原则，索引不会失效

1. 出现跳跃的情况

• 直接第一层 name 都不走，当然都失效

• 走了第一层，但是后续直接第三层，只有出现跳跃情况前的不会失效（此处就只有 name 成功）

• 同时，这个顺序并不是由我们 where 中的排列顺序决定，比如：
  • where name=’小米科技’ and status=’1’ and address=’北京市’
  • where status=’1’ and name=’小米科技’ and address=’北京市’

这两个尽管 where 中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的

其实是因为我们 MySQL 有一个 Optimizer（查询优化器），查询优化器会将 SQL 进行优化，选择最优的查询计划来执行。

• 关于这个查询优化器，后续文章我们也会谈谈 MySQL 的逻辑架构与存储引擎

🎐索引设计原则#

针对表#

1. 查询频次高，且数据量多的表

针对字段#

1. 最好从 where 子句的条件中提取，如果 where 子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

🎡其他原则#

1. 最好用唯一索引，区分度越高，使用索引的效率越高

2. 不是越多越好，维护也需要时间和空间代价，建议单张表索引不超过 5 个

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

比如：

我们创建了三个单列索引，name，status，address

当我们 where 中根据 status 和 address 两个字段来查询时，数据库只会选择最优的一个索引，不会所有单列索引都使用。

最优的索引：具体是指所查询表中，辨识度最高 (所占比例最少) 的索引列，比如此处 address 中有一个辨识度很高的 ‘西安市’数据；

1. 使用短索引 , 索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率。

2. 利用最左前缀，比如有 N 个字段，我们不一定需要创建 N 个索引，可以用复合索引

也就是说，我们尽量创建复合索引，而不是单列索引

创建复合索引:
    CREATE INDEX idx_name_email_status ON tb_seller(name,email,status);

就相当于
    对name 创建索引 ;
    对name , email 创建了索引 ;
    对name , email, status 创建了索引 ;
Copy

⏰举个栗子#

假设我们有这么一个表，id 为主键，没有创建索引：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB
Copy

如果要在此处建立复合索引，我们要遵循什么原则呢？

通过调整顺序，可以少维护一个索引#

• 比如我们的业务需求里边，有如下两种查询方式：
  1. 根据 name 查询
  2. 根据 name 和 age 查询

如果我们建立索引（age,name），由于最左前缀原则，我们这个索引能实现的是根据 age，根据 age 和 name 查询，并不能单纯根据 name 查询（因为跳跃了），为了实现我们的需求，我们还得再建立一个 name 索引；

而如果我们通过调整顺序，改成（name，age），就能实现我们的需求了，无需再维护一个 name 索引，这就是通过调整顺序，可以少维护一个索引。

考虑空间 -> 短索引#

• 比如我们的业务需求里边，有以下两种查询方式：
  1. 根据 name 查询
  2. 根据 age 查询
  3. 根据 name 和 age 查询

我们有两种方案：

1. 建立联合索引 (name,age)，建立单列索引：age 索引。
2. 建立联合索引 (age,name)，建立单列索引：name 索引。

这两种方案都能实现我们的需求，这个时候我们就要考虑空间了，name 字段是比 age 字段大的，显然方案 1 所耗费的空间是更小的，所以我们更倾向于方案 1。

何时建立索引#

1. where 中的查询字段
2. 查询中与其他表关联的字段，比如外键
3. 排序的字段
4. 统计或分组的字段

何时达咩索引#

1. 表中数据量很少
2. 经常改动的表
3. 频繁更新的字段
4. 数据重复且分布均匀的表字段（比如包含了很多重复数据，那此时多叉树的二分查找，其实用处不大，可以理解为 O (logn) 退化了）

索引相关语法#

创建索引#

默认会为主键创建索引–primary

CREATE     [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]
Copy

查找索引#

结尾加上 \G，可以变成竖屏显示

select index from tbl_name\G;
Copy

删除索引#

drop INDEX index_name on tbl_name ;
Copy

变更索引#

1). alter  table  tb_name  add  primary  key(column_list); 
    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL    

2). alter  table  tb_name  add  unique index_name(column_list);
    这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

3). alter  table  tb_name  add  index index_name(column_list);
    添加普通索引， 索引值可以出现多次。

4). alter  table  tb_name  add  fulltext  index_name(column_list);
    该语句指定了索引为FULLTEXT， 用于全文索引
Copy

查看索引使用情况#

show status like 'Handler_read%';     -- 查看当前会话索引使用情况

show global status like 'Handler_read%';    -- 查看全局索引使用情况
Copy

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY … DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要 MySQL 扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

🎆总结#

1. 索引简单来说就是一个排好序的数据结构，可以方便我们检索数据，而不需要盲目的进行全表扫描。

• 索引底层有很多种实现结构，这篇主要只是讲解了 BTREE 索引，如果对树这一数据结构还不太熟悉的小伙伴，可以关注我后续数据结构专栏，会整理关于普通树，二叉树，二叉排序树的文章。

2. 索引分类：

   1. 主键索引
   2. 辅助索引

这里我们还扩展了索引下推，是一个十分重要的知识点，需要仔细回味。

1. 索引的相关设计原则，索引虽好，但也不可贪杯，不能为了用索引而建索引。
2. 索引的相关语法，很容易上手的。
3. 查看索引的使用情况。

转载链接：www.cnblogs.com/melojun/p/mysql-in...

本作品采用《CC 协议》，转载必须注明作者和本文链接