MySQL数据库-索引
1. 索引概述
1.1. 什么是索引
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示：
- 索引的本质是：一种数据结构
- 索引的作用是：高效获取数据
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。
1.2. 索引的优势
- 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
1.3. 索引的代价（劣势）
1.3.1. 空间上的代价
每建立一个索引都要为它建立一棵 B+树，每一棵 B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的 B+树由许多数据页组成会占据很多的存储空间。
实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。1.3.2. 时间上的代价
虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE等操作。因为更新表时，MySQL 不仅要保存数据，还要保存索引文件因每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息，即需要修改各个B+树索引。
B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的 B+树都要进行相关的维护操作，这必然会对性能造成影响。2. 索引的基础语法
InnoDB 和 MyISAM 会自动为主键或者声明为UNIQUE的列去自动建立B+树索引。如要给表中其他列创建索引则需要通过sql语句去指定2.1. 查看索引
``` SHOW INDEX FROM table_name\G
### 2.2. 创建/修改索引- 建表同时创建索引
CREATE TALBE 表名 ( 各种列的信息 ··· , [KEY|INDEX] 索引名 (需要被索引的单个列或多个列) )
- 单独创建索引
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
- 修改索引
ALTER TABLE 表名 ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
示例：
— 普通索引方式1-创建表的时候直接指定 create table student( sid int primary key, card_id varchar(20), name varchar(20), gender varchar(20), age int, birth date, phone_num varchar(20), score double, index index_name(name) — 给name列创建索引 ); — 普通索引方式2-直接创建 create index index_gender on student(gender);
— 普通索引方式3-修改表结构(添加索引) alter table student add index index_age(age);
— 唯一索引方式1-创建表的时候直接指定 create table student2( sid int primary key, card_id varchar(20), name varchar(20), gender varchar(20), age int, birth date, phone_num varchar(20), score double, unique index_card_id(card_id) — 给card_id列创建索引 ); — 唯一索引方式2-直接创建 create unique index index_card_id on student2(card_id);
— 唯一索引方式3-修改表结构(添加索引) alter table student2 add unique index_phone_num(phone_num)
— 创建索引的基本语法 create index indexname on table_name(column1(length),column2(length));
### 2.3. 删除索引
— 方式一 DROP INDEX [indexName] ON mytable;
— 方式二 ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;
## 3. 索引结构索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：- **B-TREE 索引**：最常见的索引类型，大部分索引都支持B树索引。- **HASH 索引**：只有Memory引擎支持，使用场景简单。- **R-tree 索引（空间索引）**：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。- **Full-text（全文索引）**：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。|索引| InnoDB引擎| MyISAM引擎| Memory引擎|| --- | --- | --- | --- ||BTREE索引| 支持| 支持| 支持||HASH 索引| 不支持| 不支持| 支持||R-tree 索引| 不支持| 支持| 不支持||Full-text| 5.6版本之后支持| 支持| 不支持|注：平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中**聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引**### 3.1. BTREE 结构BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：- 树中每个节点最多包含m个孩子。- 除根节点与叶子节点外，每个节点至少有`[ceil(m/2)]`个孩子。- 若根节点不是叶子节点，则至少有两个孩子。- 所有的叶子节点都在同一层。- 每个非叶子节点由n个key与n+1个指针组成，其中`[ceil(m/2)-1] <= n <= m-1`以5叉BTree为例，由key的数量（即是`key=5`）根据公式`[ceil(m/2)-1] <= n <= m-1`推导可得知，所以`2 <= n <=4`。当`n>4`时，中间节点分裂到父节点，两边节点分裂。<br />插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例，其演变过程如下：1. 插入前4个字母 C N G A2. 插入H，`n>4`，中间元素G字母向上分裂到新的节点3. 插入E，K，Q不需要分裂4. 插入M，中间元素M字母向上分裂到父节点G5. 插入F，W，L，T不需要分裂6. 插入Z，中间元素T向上分裂到父节点中7. 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂8. 最后插入S，NPQR节点`n>5`，中间节点Q向上分裂，但分裂后父节点DGMT的`n>5`，中间节点M向上分裂<br />到此，该BTREE树就已经构建完成了，BTREE树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。### 3.2. B+TREE 结构B+Tree为BTree的变种，B+Tree与BTree的区别为：1. n叉B+Tree最多含有n个key，而BTree最多含有n-1个key1. B+Tree的叶子节点保存所有的key信息，依key大小顺序排列1. 所有的非叶子节点都可以看作是key的索引部分<br />由于B+Tree只有叶子节点保存key信息，查询任何key都要从root节点走到叶子节点。所以B+Tree的查询效率更加稳定### 3.3. MySQL中的B+TreeMySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。<br />MySQL中的 B+Tree 索引结构示意图：<br /><br />**特别注意，一个索引对应一个B+Tree**### 3.4. 磁盘和B+树的关系关系型数据库都选择了B+树数据结构，这个和磁盘的特性有着非常大的关系。#### 3.4.1. 磁盘结构<br />盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元也是最小读写单元。现在磁盘扇区一般是 512 个字节~4k 个字节。<br />磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、扇区号。读/写磁盘上某一指定数据需要下面步骤：1. 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。1. 所有磁头都定位到磁道上后，这时根据盘面号来确定指定盘面上的具体磁道。1. 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。经过上面步骤，指定数据的存储位置就被找到。这时就可以开始读/写操作。磁盘读取依靠的是机械运动，分为寻道时间、旋转延迟、传输时间三个部分，这三个部分耗时相加就是一次磁盘 IO 的时间，一般大概 9ms 左右。<br />为了提高效率，要尽量减少磁盘 I/O。为此，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，这个称之为**预读**。这样做的理论依据是计算机科学中著名的局部性原理：**当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中**。<br />磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间）。- 机械磁盘的顺序读的效率是随机读的 40 到 400 倍。顺序写是随机写的 10 到 100 倍- SSD 盘顺序读写的效率是随机读写的 7 到 10 倍#### 3.4.2. B+树结构在磁盘的存储预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页，页大小通常为 4k 当然也有 16K 的，主存和磁盘以页为单位交换数据。<br />按照磁盘的这种性质，如果是一个页存放一个 B+树的节点，自然是可以存很多的数据的，比如 InnoDB 里，默认定义的 B+树的节点大小是 16KB，这就是说，假如一个 Key 是 8 个字节，那么一个节点可以存放大约 1000 个 Key，意味着 B+数可以有 1000 个分叉。同时 InnoDB 每一次磁盘 I/O，读取的都是 16KB 的整数倍的数据。也就是说 InnoDB 在节点的读写上是可以充分利用磁盘顺序 IO 的高速读写特性。<br />按照 B+树逻辑结构来说，在叶子节点一层，所有记录的主键按照从小到大的顺序排列，并且形成了一个双向链表。同一层的非叶子节点也互相串联，形成了一个双向链表。那么在实际读写的时候，很大的概率相邻的节点会放在相邻的页上，又可以充分利用磁盘顺序 I/O 的高速读写特性。所以**对 MySQL 优化的一大方向就是尽可能的多让数据顺序读写，少让数据随机读写**。### 3.5. B+树作用与索引总结- 在块设备上，通过 B+树可以有效的存储数据；- 所有记录都存储在叶子节点上，非叶子(non-leaf)存储索引(keys)信息；而且记录按照索引列的值由小到大排好了序。- B+树含有非常高的扇出（fanout），通常超过 100，在查找一个记录时，可以有效的减少 IO 操作；> Tips:> - 扇出 是每个索引节点(Non-LeafPage)指向每个叶子节点(LeafPage)的指针；> - 扇出数 = 索引节点(Non-LeafPage)可存储的最大关键字个数 + 1## 4. 索引分类- **单值（列）索引**：即一个索引只包含单个列，一个表可以有多个单列索引- **普通索引**- **唯一索引**- **主键索引**- **复合（组合）索引**- **全文索引**- **空间索引**### 4.1. 普通索引MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值### 4.2. 唯一索引唯一索引与前面的普通索引类似，不同的就是：**索引列的值必须唯一，但允许有空值**。如果是组合索引，则列值的组合必须唯一。### 4.3. 主键索引每张表一般都会有自己的主键，在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键是具有唯一性并且不允许为NULL，所以主键索引是一种特殊的唯一索引。### 4.4. 复合（组合）索引组合索引也叫复合索引，指的是在建立索引的时候使用多个字段（_例如同时使用身份证和手机号建立索引_），同样的可以建立为普通索引或者是唯一索引。<br />注：复合索引在使用时，需要符合最左原则。### 4.5. 全文索引#### 4.5.1. 概述全文索引的关键字是 `fulltext`。全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的`where`语句的参数匹配。<br />用 `like + %` 就可以实现模糊匹配了，为什么还要全文索引？`like + %` 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 `like + %` 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。#### 4.5.2. 注意事项- MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引- MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引- 只有字段的数据类型为 `char`、`varchar`、`text` 及其系列才可以建全文索引- 在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用 `create index` 创建 `fulltext` 索引，要比先为一张表建立 `fulltext` 然后再将数据写入的速度快很多；#### 4.5.3. 最小搜索长度和最大搜索长度MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。这两个的默认值可以使用以下命令查看:
show variables like ‘%ft%’;
部分参数解析|参数名称| 默认值| 最小值| 最大值| 作用|| --- | --- | --- | --- | --- ||ft_min_word_len| 4| 1| 3600| MyISAM 引擎表全文索引包含的最小词长度||ft_query_expansion_limit| 20| 0| 1000| MyISAM引擎表使用 with query expansion 进行全文搜索的最大匹配数||innodb_ft_min_token_size| 3| 0| 16| InnoDB 引擎表全文索引包含的最小词长度||innodb_ft_max_token_size| 84| 10| 84| InnoDB 引擎表全文索引包含的最大词长度|#### 4.5.4. 全文索引创建语法语法格式：
— 创建表的时候添加全文索引 create table 表名 ( … fulltext (字段名) — 创建全文检索 );
— 修改表结构添加全文索引 alter table 表名 add fulltext index_content(字段名)
— 直接添加全文索引 create fulltext index index_content on 表名(字段名);
示例：
create table t_article ( id int primary key auto_increment , title varchar(255) , content varchar(1000) , writing_date date, fulltext (content) — 创建全文检索 );
alter table t_article add fulltext index_content(content)
create fulltext index index_content on t_article(content);
#### 4.5.5. 使用全文索引使用全文索引和常用的模糊匹配使用 `like + %` 不同，全文索引有自己的语法格式，使用 `match` 和 `against` 关键字，格式:
match (col1,col2,…) against(expr [search_modifier])
示例：
select from t_article where match(content) against(‘yo’); — 没有结果 单词数需要大于等于3 select from t_article where match(content) against(‘you’); — 有结果
### 4.6. 空间索引(少用，了解)- MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型- 空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。|类型| 含义| 说明|| --- | --- | --- ||Geometry| 空间数据| 任何一种空间类型||Point| 点| 坐标值||LineString| 线| 有一系列点连接而成||Polygon| 多边形| 由多条线组成|- MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。- 创建空间索引的列，必须将其声明为NOT NULL。语法：
create table 表名 ( … spatial key geom_index(列名) );
## 5. InnoDB 中的索引InnoDB 中的索引也是按照 B+树来组织的。### 5.1. 聚集索引/聚簇索引InnoDB 中使用了聚集索引，就是将表的主键用来构造一棵 B+树，并且将整张表的行记录数据存放在该 B+树的叶子节点中。**索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。**<br /><br />聚集索引的叶子节点就是数据页，数据页上存放的是完整的每行记录。聚集索引的优点如下：1. 通过过聚集索引能获取完整的整行数据。1. 对于主键的排序查找和范围查找速度非常快。如果表没有定义主键，MySQL 会使用唯一性索引建立聚集索引。如果也没有唯一性索引，MySQL 也会创建一个隐含列 RowID 来做主键，然后用这个主键来建立聚集索引。### 5.2. 辅助索引/二级索引#### 5.2.1. 索引结构聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。如果以别的列作为搜索条件时，通常会给这些列建立索引，这些索引被称为辅助索引/二级索引。<br />辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。<br /><br />辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。#### 5.2.2. 回表当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表。<br />> 根据辅助索引的值查询一条完整的用户记录需要使用到 2 棵 B+树 ----> 一次辅助索引，一次聚集索引。辅助索引叶子节点不会像聚集索引一样，不会将完整的行记录放到叶子节点中。虽然如果将完整行记录放到叶子节点是可以不用回表，但相当于每建立一棵 B+树都需要把所有的记录再都拷贝一份，太过占用存储空间。而且每次对数据的变化要在所有包含数据的索引中全部都修改一次，性能也非常低下。<br />回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。至于采取何种扫描方式是由查询优化器决定。查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引+回表的方式。### 5.3. 联合索引/复合索引构建索引可以包含多个字段，将表上的多个列组合起来进行索引，称之为联合索引或者复合索引。<br />**值得注意的是：联合索引只会建立1棵B+树；而对多个列分别建立索引（辅助索引）则会分别以每个列则建立B+树，有几个列就有几个B+树**<br /><br />联合索引的结构是按定义时的列的顺序，逐个排序建立索引。如`index(note,b)`在索引构建如下：1. 先把各个记录按照 note 列进行排序1. 在记录的 note 列相同的情况下，采用 b 列进行排序1. 最终叶子节点存储聚集索引的主键列### 5.4. 覆盖索引/索引覆盖InnoDB 存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的 IO 操作。**注意：覆盖索引并不是索引类型的一种**。<br />### 5.5. 自适应哈希索引B+树的查找次数，取决于 B+树的高度，在生产环境中，B+树的高度一般为 3~4 层，故需要 3~4 次的 IO 查询。<br />InnoDB 存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个 hash 索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过 hash 算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree 索引中查询三四次节点的效率高了不少。<br />InnoDB 存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，用户并不能对其进行干预。查询当前自适应哈希索引的使用状况的命令如下：
show engine innodb status\G;
<br />哈希索引只能用来搜索等值的查询，如`SELECT* FROM table WHERE indexcol=xxx`。而对于其他查找类型，如范围查找，是不能使用哈希索引。> 由于 AHI 是由 InnoDB 存储引擎控制的，因此这里的信息只供我们参考。不过我们可以通过观察 SHOW ENGINE INNODB STATUS 的结果及参数 innodb_adaptive_hash_index 来考虑是禁用或启动此特性，默认 AHI 为开启状态。> 什么时候需要禁用呢？如果发现监视索引查找和维护哈希索引结构的额外开销远远超过了自适应哈希索引带来的性能提升就需要关闭这个功能。> 同时在 MySQL 5.7 中，自适应哈希索引搜索系统被分区。每个索引都绑定到一个特定的分区，每个分区都由一个单独的 latch 锁保护。分区由innodb_adaptive_hash_index_parts 配置选项控制 。在早期版本中，自适应哈希索引搜索系统受到单个 latch 锁的保护，这可能成为繁重工作负载下的争用点。innodb_adaptive_hash_index_parts 默认情况下，该 选项设置为 8。最大设置为 512。禁用或启动此特性和调整分区个数是 DBA 的工作，我们了解即可。### 5.6. 全文检索之倒排索引全文检索（Full-Text Search）：是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。比较熟知的如 Elasticsearch、Solr 等就是全文检索引擎，底层都是基于 Apache Lucene 的。<br />倒排索引就是，将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字是，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。> _注：具体如何使用 InnoDB 存储引擎的全文检索，查阅相关官方文档或者书籍_## 6. 索引在查询中使用的原理1. **一个索引对应一个B+树，索引让查询可以快速定位和扫描到需要的数据记录上，加快查询的速度。**1. **一个`select`查询语句在执行过程中一般最多能使用一个二级索引，即使在`where`条件中用了多个二级索引。**### 6.1. 扫描区间对于某个查询来说，最简单粗暴的执行方案就是扫描表中的所有记录，判断每一条记录是否符合搜索条件。如果符合，就将其发送到客户端，否则就跳过该记录。这就是**全表扫描**。<br />对于使用 InnoDB 存储引擎的表来说，全表扫描意味着从聚簇索引第一个叶子节点的第一条记录开始，沿着记录所在的单向链表向后扫描，直到最后一个叶子节点的最后一条记录。虽然全表扫描是一种很笨的执行方案，但却是一种万能的执行方案，所有的查询都可以使用这种方案来执行，只是效率不高。<br />有了索引，利用 B+树查找索引列值等于某个值的记录，这样可以明显减少需要扫描的记录数量。由于 B+树叶子节点中的记录是按照索引列值由小到大的顺序排序的，所以即使只扫描某个区间或者某些区间中的记录也可以明显减少需要扫描的记录数量。
SELECT * FROM order_exp WHERE id >= 3 AND id<= 99;
以上语句是查找 id 值在`[3,99]`区间中的所有聚簇索引记录。以通过聚簇索引对应的 B+树快速地定位到 id 值为 3 的那条聚簇索引记录，然后沿着记录所在的单向链表向后扫描,直到某条聚簇索引记录的 id 值不在`[3,99]`区间中为止。<br />与全表扫描相比，扫描 id 值在[3,99]区间中的记录已经很大程度地减少了需要扫描的记录数量，所以提升了查询效率。其实所谓的全表扫描，可以理解为扫描的区间是[负无穷，正无穷]或者[第一条记录，最后一条记录]。
SELECT * FROM order_exp WHERE order_no < ‘DD00_10S’ AND expire_time > ‘2021-03-22 18:28:28’ AND order_note > ‘7 排’;
上述语句中，order_no 和 expire_time 都有索引，order_note 没有索引。这个不会出现两个扫描区间。一个`select`查询语句在执行过程中一般最多能使用一个二级索引。<br />无论用哪个索引执行查询，都需要获取到索引中的记录后，进行回表，获取到完整的用户记录后再根据判定条件判断这条记录是否满足 SQL 语句的要求。### 6.2. 范围区间扫描对于B+树索引来说，只要索引列和常数使用`=`、`<=>`、`IN`、`NOT IN`、`IS NULL`、`IS NOT NULL`、`>`、`<`、`>=`、`<=`、`BETWEEN`、`!=`（不等于也可以写成`<>`）或者 `LIKE` 操作符连接起来，就可以产生一个区间。1. `IN` 操作符的效果和若干个等值匹配操作符`=`之间用`OR`连接起来是一样的，也就是说会产生多个单点区间，比如下边这两个语句的效果是一样的：
SELECT FROM order_exp WHERE insert_time IN (2021-03-22 18:23:42, yyyy); SELECT FROM order_exp WHERE insert_time= 2021-03-22 18:23:42 OR insert_time = yyyy;
2. `!=` 一般会产生的两个扫描区间，即[第一条记录, 不等于表达式的值]和[不等于表达式的值, 最后一条记录]
SELECT * FROM order_exp WHERE order_no != ‘DD00_9S’;
3. `LIKE` 操作符比较特殊，只有在匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间。对于某个索引列来说，字符串前缀相同的记录在由记录组成的单向链表中肯定是相邻的。比如有一个搜索条件是 `note LIKE 'b%'`，对于二级索引 idx_note 来说，所有字符串前缀为`'b'`的二级索引记录肯定是相邻的。这也就意味着只要定位到 idx_note 值的字符串前缀为`'b'`的第一条记录，就可以沿着记录所在的单向链表向后扫描，直到某条二级索引记录的字符串前缀不为`'b'`为止。<br />### 6.3. 所有搜索条件都可以使用某个索引的情况每个搜索条件都可以使用到某个索引
— AND 的方式连接 SELECT FROM order_exp WHERE order_no > ‘DD00_6S’ AND order_no > ‘DD00_9S’; — OR 的方式连接 SELECT FROM order_exp WHERE order_no > ‘DD00_6S’ OR order_no > ‘DD00_9S’;
`AND`连接搜索条件时，取交集；`OR`连接搜索条件时，取并集；### 6.4. 有的搜索条件无法使用索引的情况
SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ AND order_note = ‘abc’;
上面示例这个查询语句中能利用的索引只有 idx_expire_time 一个，而 idx_expire_time 这个二级索引的记录中又不包含 order_note 这个字段，所以在使用二级索引 idx_expire_time 定位记录的阶段用不到 `order_note = 'abc'`这个条件，而范围区间是为了到索引中取记录中提出的概念，所以在确定范围区间的时候不需要考虑 `order_note = 'abc'`这个条件。即可以将语句简化成：
SELECT * FROM order_exp WHERE expire_time > ‘2021-03-22 18:35:09’;
如果使用`or`连接搜索条件：
SELECT * FROM order_exp WHERE expire_time > ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;
一个使用到索引的搜索条件和没有使用该索引的搜索条件使用`OR`连接起来后是无法使用该索引的。因为索引expire_time列不包含非索引的order_note字段，所以无法判断非索引的order_note字段是否满足搜索条件，又因为是`OR`条件连接，所以必须要在主键索引中从第一条记录到最后一条记录逐条判定非索引的order_note字段是否满足条件。即可以将语句简化成：
SELECT * FROM order_exp ;
### 6.5. 复杂搜索条件下找出范围匹配的区间当搜索条件可能特别复杂时。比如：
SELECT * FROM order_exp WHERE (order_no > ‘DD00_9S’ AND expire_time = ‘2021-03-22 18:35:09’ ) OR (order_no < ‘DD00_6S’ AND order_no > ‘DD00_9S’) OR (order_no LIKE ‘%0S’ AND order_no > ‘DD00_12S’ AND (expire_time < ‘2021-03-22 18:28:28’ OR order_note = ‘abc’)) ;
首先查看 WHERE 子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引。这个查询的搜索条件涉及到了 order_no、expire_time、order_note 这 3 个列，然后 order_no 列有二级索引 idx_order_no，expire_time 列有二级索引idx_expire_time。对于那些可能用到的索引，分析它们的范围区间。- **使用 idx_order_no 执行查询**分析时可以先那些用不到该索引的搜索条件暂时移除掉。把所有用不到的搜索条件视为True来进行中间替换，
— 使用true代替无使用索引的搜索条件 (order_no > ‘DD00_9S’ AND TRUE ) OR (order_no < ‘DD00_6S’ AND order_no > ‘DD00_9S’) OR (TRUE AND order_no > ‘DD00_12S’ AND (TRUE OR TRUE))
— 再次化简 (order_no > ‘DD00_9S’) OR (order_no < ‘DD00_6S’ AND order_no > ‘DD00_9S’) OR (order_no > ‘DD00_12S’)
— 替换掉永远为 TRUE 或 FALSE 的条件 (order_no > ‘DD00_9S’) OR (order_no > ‘DD00_12S’)
最后搜索条件是使用`OR`操作符连接起来的，意味着要取并集，所以最终的结果化简的到的区间就是：`order_no > 'DD00_12S'`。也就是说：上边那个复杂搜索条件的查询语句如果使用 idx_order_no 索引执行查询的话，需要把满足`order_no > 'DD00_12S'`的二级索引记录都取出来，然后拿着这些记录的 id 再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。记住，说的是如果使用 idx_order_no 索引执行查询，不代表 MySQL 一定会使用，因为MySQL 需要做整体评估，才能确定是否使用这个索引还是别的索引，或者是干脆全表扫描。- **使用 idx_expire_time 执行查询**把那些用不到该索引的搜索条件暂时使用 TRUE 条件替换掉，其中有关 order_no 和 order_note 的搜索条件都需要被替换掉，替换结果就是：
(TRUE AND expire_time = ‘2021-03-22 18:35:09’ ) OR (TRUE AND TRUE) OR (TRUE AND TRUE AND (expire_time < ‘2021-03-22 18:28:28’ OR TRUE))
— 替换掉永远为 TRUE 或 FALSE 的条件 expire_time = ‘2021-03-22 18:35:09’ OR TRUE
— 最终结果 TRUE
这个结果也就意味着如果我们要使用 idx_expire_time 索引执行查询语句的话，需要扫描 idx_expire_time 二级索引的所有记录，然后再回表，这种情况下为啥 MySQL 不直接全表扫描呢？所以一定不会使用 idx_expire_time 索引的。### 6.6. 使用联合索引执行查询时对应的扫描区间联合索引的索引列包含多个列，B+树每一层页面以及每个页面中的记录采用的排序规则较为复杂，以 order_exp 表的 u_idx_day_status 联合索引为例，它采用的排序规则如下所示：- 先按照 insert_time 列的值进行排序- 在 insert_time 列的值相同的情况下，再按照 order_status 列的值进行排序- 在 insert_time 和 order_status 列的值都相同的情况下，再按照 expire_time 列的值进行排序<br />以下用几个查询示例来说明：
SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’;
> 由于二级索引记录是先按照 insert_time 列的值进行排序的，所以所有符合`insert_time = '2021-03-22 18:34:55'`条件的记录肯定是相邻的，可以定位到第一条符合`insert_time = '2021-03-22 18:34:55'`条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合`insert_time = '2021-03-22 18:34:55'`条件为止(当然，对于获取到的每一条二级索引记录都要执行回表操作)。> 扫描区间就是`['2021-03-22 18:34:55', '2021-03-22 18:34:55']`，形成这个扫描区间的条件就是 `insert_time = '2021-03-22 18:34:55'`。
SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0;
> 由于二级索引记录是先按照 insert_time 列的值进行排序的；在 insert_time 列的值相等的情况下，再按照 order_status 列进行排序。所以符合 `insert_time = '2021-03-22 18:34:55' AND order_status = 0` 条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合 `insert_time='2021-03-22 18:34:55' AND order_status=0` 条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合`insert_time='2021-03-22 18:34:55'`条件或者`order_status=0`条件为止。> 形成扫描区间`[('2021-03-22 18:34:55', 0), ('2021-03-22 18:34:55', 0)]`，形成这个扫描区间的条件就是 `insert_time = '2021-03-22 18:34:55' AND order_status = 0`。
SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND order_status = 0 AND expire_time = ‘2021-03-22 18:35:13’;
> 由于二级索引记录是先按照 insert_time 列的值进行排序的；在 insert_time 列的值相等的情况下，再按照 order_status 列进行排序；在 insert_time 和order_status 列的值都相等的情况下，再按照 expire_time 列进行排序。所以符合insert_time = '2021-03-22 18:34:55' AND order_status = 0 AND expire_time = '2021-03-22 18:35:13'条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合 insert_time='2021-03-22 18:34:55' AND order_status=0 AND expire_time='2021-03-22 18:35:13'条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 insert_time='2021-03-22 18:34:55'条件或者order_status=0 条件或者 expire_time='2021-03-22 18:35:13'条件为止。> 如果使用 u_idx_day_status 索引执行查询时，可以形成扫描区间`[('2021-03-22 18:34:55', 0, '2021-03-22 18:35:13'), ('2021-03-22 18:34:55', 0, '2021-03-22 18:35:13')]`，形成这个扫描区间的条件就是`insert_time = '2021-03-22 18:34:55' AND order_status = 0 AND expire_time = '2021-03-22 18:35:13'`。
SELECT * FROM order_exp WHERE insert_time < ‘2021-03-22 18:34:55’;
> 由于二级索引记录是先按照 insert_time 列的值进行排序的，所以所有符合`insert_time < '2021-03-22 18:34:55'`条件的记录肯定是相邻的，我们可以定位到第一条符合`insert_time < '2021-03-22 18:34:55'`条件的记录(其实就是u_idx_day_status 索引第一个叶子节点的第一条记录)，然后沿着记录所在的链表向前扫描，直到某条记录不符合 `insert_time < '2021-03-22 18:34:55'`为止。> 使用 u_idx_day_status 索引执行查询时，形成扫描区间`(第一条记录, '2021-03-22 18:34:55')`，形成这个扫描区间的条件就是`insert_time < '2021-03-22 18:34:55'`。
SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND order_status >= 0;
> 由于二级索引记录是先按照 insert_time 列的值进行排序的；在 insert_time 列的值相等的情况下，再按照 order_status 列进行排序。也就是说在符合 insert_time = '2021-03-22 18:34:55'条件的二级索引记录中，是按照 order_status 列的值进行排序的，那么此时符合 `insert_time = '2021-03-22 18:34:55' AND order_status >= 0;`条件的二级索引记录肯定是相邻的。我们可以定位到第一条符合 `insert_time = '2021-03-22 18:34:55' AND order_status >= 0;`条件的记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 `insert_time='2021-03-22 18:34:55'`条件或者`order_status >= 0`条件为止。> 使用 u_idx_day_status 索引执行查询时，形成扫描区间，条件就是`insert_time = '2021-03-22 18:34:55' AND order_status >= 0;`
SELECT * FROM order_exp WHERE order_status = 1;
> 由于二级索引记录不是直接按照 order_status 列的值排序的，所以符合`order_status = 1`的二级索引记录可能并不相邻，也就意味着不能通过这个`order_status = 1`搜索条件来减少需要扫描的记录数量。在这种情况下，是不会使用 u_idx_day_status 索引执行查询的。
SELECT * FROM order_exp WHERE insert_time = ‘2021-03-22 18:34:55’ AND expire_time = ‘2021-03-22 18:35:12’;
> 由于二级索引记录是先按照 insert_time 列的值进行排序的，所以符合`insert_time = '2021-03-22 18:34:55'`条件的二级索引记录肯定是相邻的，但是对于符合 `insert_time = '2021-03-22 18:34:55'`条件的二级索引记录来说，并不是直接按照 expire_time 列进行排序的，也就是说我们不能根据搜索条件`expire_time = '2021-03-22 18:35:12'`来进一步减少需要扫描的记录数量。那么如果使用u_idx_day_status索引执行查询的话，可以定位到第一条符合`insert_time='2021-03-22 18:34:55'`条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合`insert_time = '2021-03-22 18:34:55'`条件为止。> 使用 u_idx_day_status 索引执行查询时，对应的扫描区间其实是`['2021-03-22 18:34:55', '2021-03-22 18:34:55']`，形成该扫描区间的搜索条件是`insert_time = '2021-03-22 18:34:55'`，与`expire_time = '2021-03-22 18:35:12'`无关。
SELECT * FROM order_exp WHERE insert_time < ‘2021-03-22 18:34:57’ AND order_status = 1;
```
由于二级索引记录是先按照 insert_time 列的值进行排序的，所以符合
insert_time < '2021-03-22 18:34:57'条件的二级索引记录肯定是相邻的，但是对于符合insert_time < '2021-03-22 18:34:57'条件的二级索引记录来说，并不是直接按照order_status列进行排序的，也就是说我们不能根据搜索条件order_status = 0来进一步减少需要扫描的记录数量。使用 u_idx_day_status 索引执行查询的话，可以定位到第一条符合 insert_time 的记录，其实就是u_idx_day_status索引第一个叶子节点的第一条记录，所以在使用u_idx_day_status索引执行查询的过程中，对应的扫描区间其实是
[第一条记录,'2021-03-22 18:34:57')。
7. MyISAM 中的索引（了解）
MyISAM 存储引擎中的索引方案，虽然也使用树形结构，但是却将索引和数据分开存储的。
MyISAM 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中存多少条记录。可以通过行号而快速访问到一条记录。
由于在插入数据的时候并没有刻意按照主键大小排序，所以并不能在这些数据上使用二分法进行查找。
使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值+行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录。这一点和 InnoDB 是完全不相同的，在 InnoDB 存储引擎中，只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次回表操作，意味着 MyISAM 中建立的索引相当于全部都是二级索引。
如有需要，也可以对其它的列分别建立索引或者建立联合索引，原理和 InnoDB 中的索引差不多，不过在叶子节点处存储的是相应的列+行号。这些索引也全部都是二级索引。
8. 索引其他相关
8.1. 复合主键
复合主键是指表的主键含有一个以上的字段组成，不使用无业务含义的自增id作为主键。
举个例子，在表中创建了一个ID字段，自动增长，并设为主键，这个是没有问题的，因为“主键是唯一的索引”，ID自动增长保证了唯一性，所以可以。
此时，再创建一个字段name，类型为varchar，也设置为主键，就会发现，在表的多行中是可以填写相同的name值的
当表中只有一个主键时，它是唯一的索引；当表中有多个主键时，称为复合主键，复合主键联合保证唯一索引。某几个主键字段值出现重复是没有问题的，只要不是有多条记录的所有主键值完全一样，就不算重复。
8.2. 联合主键
联合主键就是多个主键联合形成一个主键组合
联合主键的意义：用2个字段(或者多个字段)来确定一条记录，说明，这2个字段都不是唯一的，2个字段可以分别重复。联合就在于主键A跟主键B形成的联合主键是唯一的。
若有收获，就点个赞吧
0 人点赞
