MySQL - 索引

2020-11-25 本文已影响0人 Whyn

索引

对于大多数应用系统，对数据库的读写比例大致在 10:1 左右，也就是查询操作行为远大于插入与更新行为，因此，数据库绝大多数性能问题，往往是出现在查询操作上，尤其是复杂的数据查询操作。

当我们对数据表进行查询时，通常会附带一些字段值作为过滤条件，比如SELECT * FROM table_name WHERE id = 10000，如果直接对表进行查找，则需要从头到尾顺序进行遍历比较，直到找到id = 10000的记录，这种全表扫描查找的时间复杂度为 $O(n)$ ，对于现在动辄十万百万甚至是千万级别的数据库而言，这种查询速度显然是不可行的。

因此，需要一种机制来满足对数据库的高效查找行为，而这种机制就是 索引（Index）。

本质上，索引就是一个具备高效查找的数据结构（比如哈希表、二叉搜索树、B-/B+树...），其执行逻辑可以大致理解为：当对经常查询的一个或多个字段（列）进行索引时，会将该字段各个值与其对应的数据记录地址（物理地址）关联起来，然后，每次进行查询操作时，数据库判断若查询字段已被索引，则会首先从索引文件中获取得到查询字段值对应的记录（即数据行）在磁盘上的地址，然后依据该地址就可以加载到完整记录内容，完成查询操作。比如假设使用哈希索引，那么理论上无论数据表多大，依据查找字段直接以 $O(1)$ 的时间复杂度就可以检索得出记录，查询效率堪称完美。

当然，对于不同的存储引擎，其底层对于数据的存储组织方式不同，因此，对于索引，也有不同的存储结构，但无论是使用哪种结构，这种结构都满足高效查找特性。但是，由于数据库的特殊性，比如存储数据量特别大，导致磁盘 IO 操作特别多，因此不是所有的高效查找数据结构都满足数据库查找要求。比如，常见的高效查找数据结构有如下几种：

有序数组：对于有序数组，可以采用二分查找算法，以 $O(logn)$ 的时间复杂度进行查找操作，理论上效率完全满足查找需求，但是数据库数据量太大，无法一次性载入内存，因此这种数据结构会涉及太多次 IO 操作，实际性能非常低效。
哈希表：以哈希结构组织各字段值（哈希索引），理论上无论数据量有多大，任何查找操作都只需 $O(1)$ 的时间复杂度，这是最可观的查找速度了，但是由于对数据库的查询操作中，经常伴随排序、范围等操作，哈希表是无序存储，无法很好满足这些查询操作，因此，大多数数据库不会使用哈希索引。
注：如果查找操作只涉及对字段的等值比较查询，而很少进行排序、范围比较等，则可以使用哈希索引，性能会更高。
注：更多哈希表特性简介，可参考：[数据结构 - 哈希表]
二叉搜索树：二叉搜索树满足左子树 < 根结点 < 右子树，对二叉搜索树以中序遍历也可以得到一个有序数组，其平均查找时间复杂度也为 $O(logn)$ ，但存在极端情况可能将字段值构造成左/右斜树，此时查找效率退化为 $O(n)$ ，这种情况可通过平衡二叉树进行解决。
注：更多二叉搜索树特性简介，可参考：[数据结构 - 树]
平衡二叉树：平衡二叉树可以保证其左右子树高度相当，因此其查找时间复杂度理论上可保证为 $O(logn)$ ，但是其每个结点只存储一个数据，对于大数据量来说，会造成其高度急剧增大，导致查找效率严重下滑，同时也存在过多 IO 操作，实际性能无法直视。
注：更多平衡二叉树特性简介，可参考：[数据结构 - 树]
B树（B-树，B-Tree，Balanced Tree）：B树是一种多路自平衡查找树，它的主要特性是树中的每个结点最多可以有 m 棵子树，称之为 m 阶B树，每个结点最多可以带有 m-1 个数据域以及 m 个指向下一个结点的指针域。
注：更多 B树特性简介，可参考：[数据结构 - 树]

数据库中，索引文件本身是存储在磁盘上的，当索引字段数据量比较大的时候，索引文件可能有几个 G 大小，因此，索引除了本身结构具备高效查找功能外，另一个更加重要的功能是保证以尽可能少的磁盘 IO 加载，完成同样的查询操作。因为相较于在内存中进行比较查找，加载磁盘数据而进行的 IO 操作会更加耗时（磁盘访问为毫秒级别操作），比之内存慢约 十万倍 左右（磁盘访问比之高速缓存甚至慢约 百万倍 左右），因此类似二叉树这种数据结构就不适合作为数据库的索引结构，因为二叉树每个结点存储的数据量只有一个，这会导致树高太深，IO 操作太多。而像 B树（以及后文要介绍的 B+树等）就专门是为磁盘或其他存储设备访问而设计的一种数据结构，其可有效减少磁盘 IO 加载以及本身具备高效查找特性，具体如下：
- 减少 IO 加载：比如对于 m 阶B树，其每个树枝结点最少可容纳 m/2-1 个数据，最多可容纳 m-1 个数据（以及 m 个指针域），这就可以大幅减少树的高度，一般会将树高控制在最多 3 层左右（通过控制阶数 m 从而控制树高），这样无论查找任何数据，理论上最多只需进行 3 次磁盘 IO 加载。
- 高效查找：B树本身也具备高效查找特性，其每个结点中的数据按由小到大排列，子结点从左往右也是有序排列，因此块内可以对每个结点数据（即关键字）进行二分查找，命中则结束查找，否则进入关键字所属范围子结点块内，重复上述查找过程，直至找到或子结点指针域为空（未找到），其时间复杂度为 $O(logn)$ 。
注：相较于基于内存的高效查找，B树索引大幅减少磁盘 IO 操作才是其最大提升查找性能的地方。

计算机科学中有一个理论称为 局部性原理：即当一个数据被用到时，其附近的数据也通常会马上被使用。
而且由于磁盘的顺序读取效率很高（不需要寻址时间，只需少量的旋转时间），因此，在实际运行中，计算机存在『磁盘预读机制』：即哪怕只读取一个字节数据，磁盘也会从该字节位置开始，顺序向后读取一定长度的数据放入内存。一般预读的长度为一页（page）或一页的整数倍。
注：页是计算机管理存储器的逻辑块，硬件及操作系统往往将主内存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为 4K）

MySQL 中也存在页的概念，比如对于 InnoDB 存储引擎，就是将记录以页的方式进行管理，每页的大小默认为 16K（支持修改）。

由于磁盘存在预读机制，且 MySQL 以页的方式管理记录，因此，为了获取更好的磁盘访问性能，通常会将 B树结点设置为一页大小（通过调节阶数 m），也即一个结点大小为 16K，这样每查找一个结点，只需进行一次 IO 加载，这个时候只需控制 B树高度，就能确保 IO 操作次数上限，从而提高查找效率。

举个例子：比如对于下图所示的 4阶B树，其查找过程如下所示：
B树
上图 4阶B树中，一个结点就是一个磁盘块，其内数据总共占一页大小（数据 = 关键字域 + 数据域 + 指针域），在数据库中，B树结点内的数据域（即data）一般为指向关键字的数据表记录地址（比如data = 0xA7）。
当向数据库进行查询操作时，比如这里我们查找关键字49，其查找过程如下所示：
1. 首先读取索引文件（一次磁盘 IO），获取一页大小数据，存储到内存 B树根结点中（数据库会依据一页数据大小自动控制构造的 B树的阶数 m），然后会对根结点关键字进行二分查找，若命中则返回关键字对应的data域，这里显然49不存在于根结点中，无法直接命中索引。
2. 当步骤 1 无法命中关键字时，则此时依据关键字范围找到其子结点，这里49位于关键字15和56内，因此其子结点指向磁盘块3，此时对磁盘块3地址（即p2）进行一次 IO 加载，同样加载一页大小数据，存储到新构建的 B树子结点中，然后重复步骤1的查找过程，此时就可以找到关键字49，索引命中，直接返回49对应的data域即可。
3. 数据库中，依据查询得到的关键字对应记录地址，就可以加载该地址，获取关键字对应的数据记录全部内容。
  注：这里其实也进行了一次 IO 加载记录操作，但是不属于索引 IO 范围。
以上，就是一个 B树索引查找数据库记录的基本过程。
B+树：B+树是 B树的一种变体，也是一种多路查找树。其相对于 B树，最大的不同在于以下几点：
- 树枝结点只存储关键字，不存储数据域，所有数据都存储到叶子结点中。
- 树枝结点的关键字域与指针域的的数量相等。
- 树枝结点的指针域p[i]，指向关键字属于[k[i],k[i+1])的子树（注意区间为左闭右开）。
- 树枝结点和叶子结点都包含所有的关键字。
- 所有的叶子结点都增加了一个指向下一个叶子结点的链指针。
注：更多 B+树特性简介，可参考：[数据结构 - 树]

相对于 B树，B+树的这种设计主要是为了单个结点内能容纳更多的关键字，因为 B树所有的内部结点都会存储数据域，由于一次磁盘 IO 加载的数据是固定大小（一页）的，但是 B树由于数据域的占据，会导致每个结点内关键字的缩减，从而导致树的高度增加，磁盘 IO 操作变多，使得查询性能下降。而 B+树只存储关键字域，不存储数据域，这样单位结点能容纳更多关键字，有效减少树高，从而使得查找性能增加。

B+树的搜索与 B树搜索逻辑基本一致，区别在于 B树可能在树中间某个层级就能命中并返回，但是 B+树由于所有数据都存储在叶子结点中，因此每次查询都必然会到达叶子结点，树枝结点相当于是叶子结点的关键字索引，提供快速定位到关键字命中的叶子结点中，因此，B+树每次查找的路径长度都相同，查询效率很稳定。同时，由于 B+ 所有叶子结点具备链指针，因此其也支持关键字排序、范围等查询功能，只需找到终端端点值，向右进行遍历即可。甚至只要找到最左端的叶子结点，就可以一直向右遍历，直至完成整棵树（也即是全部关键字数据集）的遍历。

举个例子：比如对于下图所示的 3阶B+树，其查找过程如下所示：
B+树
当向数据库进行查询时，比如这里我们查找关键字49，其查询过程如下：
1. 首先加载索引文件，取出一页大小（一次磁盘 IO），内存创建一个 B+树根结点，存储索引关键字，然后查找关键字49（二分查找），无法直接命中，但是15 < 49 < 56，满足[15, 56)范围，故取p1指向的子结点。
2. 再次加载索引文件，取出磁盘块2数据（一次磁盘 IO），存储到子结点中，然后查找关键字49，此时可以命中关键字，关键字49数据存储在p3指向的叶子结点中（49 <= 49，满足[49,xx)）。
3. 此时继续加载磁盘块7数据（一次磁盘 IO），存储到叶子结点中，找到关键字49，返回其数据域data。
4. 加载data指向的记录地址，就可以得到关键字49对应的数据库完整记录。
B+树的稳定、高效、磁盘读写代价更低、以及支持范围遍历等特性，使得其特别适合作为磁盘数据库索引结构。

索引是由数据库存储引擎实现的，不同的存储引擎支持的索引类型可能不一样。在 MySQL 中，所有存储引擎支持每个表最少能建立 16 个索引，总索引长度至少为 256 字节。

MySQL 中索引的底层存储类型只有两种：BTREE 和 HASH，具体哪种由表的存储引擎决定：
- MyISAM 和 InnoDB 存储引擎只支持 BTREE 索引。
- MEMORY / HEAD 存储引擎同时支持 BTREE 和 HASH 索引。
除了底层实际存储结构不同外，在 MySQL 中，还从应用层面对索引进行了分类，主要有如下几种索引：
- 普通索引：允许在定义索引的字段中插入重复值和空值。
- 唯一索引：定义索引的字段值必须唯一，但允许有空值。
  如果是组合索引，则字段的组合值必须唯一。
- 主键索引（Primary Key）：对主键字段定义索引。
  注：主键索引是一种特殊的唯一索引，不允许有空值。
- 辅助索引（Secondary Key）：在非主键字段上定义的索引。
  注：辅助索引允许重复值。
- 单列索引：即对一个字段进行索引。
  注：一个表可以有多个单列索引。
- 组合索引：指同时对表的多个字段组合上创建的索引。
  注：组合索引遵从最左前缀原则。
- 全文索引：全文索引类型为FULLTEXT，在定义索引的字段上支持值的全文查找，全文索引允许重复值和空值。
  全文索引可以在CHAR、VARCHAR或TEXT类型的字段上进行创建。
  注：在 MySQL 中，只有 MyISAM 存储引擎支持全文索引。
- 空间索引：空间索引是对空间数据类型（即GEOMETRY、POINT、LINESTRING、POLYGON）的字段上建立的索引。
  注：创建空间索引的字段必须声明为NOT NULL，且其只能在 MyISAM 存储引擎中创建。
下面简单介绍下在 MySQL 中，存储引擎 MyISAM 和 InnoDB 对索引的具体实现原理：
- MyISAM 索引实现原理：MyISAM 存储引擎对索引文件（.MYI）和数据文件（.MYD）是分开存储的，索引文件保存查询关键字及其数据记录所在页的指针（即数据记录物理地址），查询通过索引文件获取得到关键字对应记录指针，通过指针来读取数据记录页，即可获取得到记录完整内容。
  
  MyISAM 引擎使用 B+树作为索引结构，其主键索引结构图（假设Col1为主键）如下所示：
  MyISAM - 主键索引
  MyISAM 中主键索引和辅助索引结构相同，唯一区别是辅助索引允许重复关键字。
  
  MyISAM 存储引擎将关键字与实际数据记录分离开存储的索引方式称之为 非聚簇索引（非聚集索引）。
- InnoDB 索引实现原理：相对于 MyISAM 将索引文件和数据文件分开存储，InnoDB 引擎将表数据文件直接以 B+树结构进行存储（以主键为索引进行组织），所以其数据文件本身也是索引文件。
  
  InnoDB 索引文件默认对主键进行索引（即主键索引），因此 InnoDB 要求数据表必须具备主键，如果没有显示指定主键，则 MySQL 会自动选择第一个NOT NULL且唯一（UNIQUE）的列作为主键，如果不存在唯一标识列，则 MySQL 会自动为表生成一个长度为 6 字节的长整型隐含字段作为主键。
  
  InnoDB 对主键索引和辅助索引的结构相同，但是一个重大的区别是：对于主键索引，其叶子结点数据域（data）存储的是完整的数据记录，而辅助索引数据域存储的是主键字段值。两者的结构图如下所示：
  - 主键索引：InnoDB 的主键索引结构如下图所示（假设Col1为主键）：
    InnoDB - 主键索引
    从上图可以看出，InnoDB 主键索引相当于是对关键字（即主键）全集内做了一个二分查找，其时间复杂度为 $O(logn)$ 。
    
    InnoDB 这种将关键字与实际数据记录组织在一起（即数据文件与索引记录为同一文件）的存储方式，称之为 聚簇索引（聚集索引）。当对具有聚簇索引的表进行查询时，直接依据关键字找到对应叶子结点，即可获取对应数据记录内容。
    注：Innodb 使用主键来进行聚簇索引。
    
    每个表只能有一个聚簇索引，因为无法一次将数据记录保存在两个地方。
    注：可以使用『覆盖索引』模拟出多个聚簇索引效果，具体详情请参考后文。
  - 辅助索引：InnoDB 的辅助索引结构如下图所示（假设以Col3进行索引）：
    InnoDB - 辅助索引
    可以看到，对于 InnoDB 辅助索引，依据关键字查找得到的是数据表主键值，因此，还需通过该主键值在主键索引中进行二次查询，才能最终找到该关键字对应的实际数据记录。

此外，数据库索引还涉及以下几个重要的概念：

最左前缀原则：前面有提及到，组合索引遵从最左前缀原则。具体来说，当建立组合索引时，字段的先后顺序存在优先级区分，最先指定的字段（最左字段）优先级最高，其余字段优先级依次递减（从左到右）。当进行查询时（多字段查询），会按组合索引定义次序从左往右依次进行匹配，直至遇到不符合索引字段。

举个例子：比如数据表对字段a、b和c创建了组合索引(a,b,c)，则其查询语句对应的最左前缀如下表所示：

查询语句	索引命中
`where a`	命中索引`a`
`where a and b`	命中索引`a`和`b`
`where a and b and c`	命中索引`a`、'b'和`c`
`where a and b > 10 and c`	命中索引`a`和`b`
`where a and c`	命中索引`a`
`where b and c`	索引未命中

总结一下，创建组合索引，比如(a,b,c)，其实就相当于创建了索引(a)、(a,b)和(a,b,c)，因此索引可以搜索(a,b,c)、(a,c)和(a)字段组合。
实际使用中，可以通过EXPLAIN指令来查看查询语句是否命中索引。todo::

回表查询：对于 InnoDB 而言，对于非主键索引查询，会经历两次索引树扫描，第一次从辅助索引获取关键字对应主键值，第二次从聚簇索引中扫描获取该主键值的记录内容。这种需要经历两次索引扫描的过程称为回表查询。
覆盖索引：当索引本身包含要查询的字段时，此时无需进行回表操作，直接从索引中返回字段即可，这种行为称之为覆盖索引。

举个例子：比如对数据表tmp建立联合索引(a,b)，如果执行select b from tmp where a = 1 and b = 2，查询字段b存储于联合索引中，且该查询语句命中联合索引，所以查询结果直接从索引返回即可，无需进行回表查询。

虽然索引大大提高了查询速度，但同时却也会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE操作时，MySQL不仅要保存新数据，也要动态更新索引文件（一个关键字的增加、删除或修改可能会导致 B+树结点大幅度变化）。通常应该只为最经常查询和最经常排序的数据列建立索引。

最后，介绍下在 MySQL 中对索引的常用操作命令：

创建索引：MySQL 支持多种方法在单个或多个字段上创建索引，主要方法有如下几种：

创建表时创建索引：使用命令CREATE TABLE创建表时，当定义了主键约束、外键约束或者唯一性约束中的任意一种或多种时，都同时相当于在该字段上创建了索引。其语法如下所示：

CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

其中：

col_name：表示需要创建索引的字段。
UNIQUE：表示唯一约束，同时也相当于创建唯一索引。
FULLTEXT：表示创建全文索引。
SPATIAL：表示创建空间索引。
INDEX | KEY index_name：INDEX和KEY为同义词，用来表示创建索引。index_name表示创建索引的名称。
注：如果未指定index_name，则索引名默认与字段名相同。
length：表示索引的长度，只有字符串类型的字段才能指定索引长度。
ASC | DESC：表示升序或降序存储索引值。

举个例子：分别在建表时，创建各类型索引：

创建普通索引：
```
mysql> CREATE TABLE tmp1 (
    -> name VARCHAR(100) NOT NULL,
    -> INDEX(name) # 为字段 name 创建索引
    -> );
Query OK, 0 rows affected (2.07 sec)
```
创建表完成后，可以通过命令show create table查看表结构：
```
mysql> show create table tmp1 \G
*************************** 1. row ***************************
       Table: tmp1
Create Table: CREATE TABLE `tmp1` (
  `name` varchar(100) NOT NULL,
  KEY `name` (`name`) # 索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
```
可以看到，KEY name (name)表示成功为字段name创建了索引。

最后可以使用EXPLAIN语句来查看查询时是否使用到了索引：
```
mysql> EXPLAIN SELECT * FROM tmp1 WHERE name = 'test index' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tmp1
   partitions: NULL
         type: ref
possible_keys: name
          key: name # 本次查询使用了索引
      key_len: 402
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
```
EXPLAIN返回的结果集中：
- select_type：表示此次查询所使用的SELECT类型，其中，SIMPLE表示简单查询，即未使用UNION或子查询。其他可选择还有PRIMARY、UNION、SUBQUERY等。
- table：表示数据库读取数据表名，如果存在多表查询，则依读取的先后顺序进行排列。
- type：表示本数据表与其他数据表之间的关联关系，其可选值有：system、const、eq_ref、ref、range、index和All。
- possible_keys：表示查询时可选用的各个索引。
- key：表示本次查询实际使用的索引。
- key_len：表示索引按字节计算的长度。key_len越小，表示速度越快。
- ref：给出关联数据表的列名。
- rows：表示本次查询预计读取的记录数（行数）。
- Extra：提供了与关联操作有关的信息。

创建唯一索引：唯一索引可以大幅减少索引查询的时间。

mysql> CREATE TABLE tmp2 (
    -> id INT NOT NULL,
    -> UNIQUE INDEX unique_idx(id)
    -> );
Query OK, 0 rows affected (1.90 sec)

mysql> SHOW CREATE TABLE tmp2 \G
*************************** 1. row ***************************
       Table: tmp2
Create Table: CREATE TABLE `tmp2` (
  `id` int NOT NULL,
  UNIQUE KEY `unique_idx` (`id`) # 唯一索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

创建单列索引：即对一个字段进行索引：

mysql> create table tmp3 (
    -> name char(50) NULL,
    -> INDEX SingleIdx(name(20))
    -> );
Query OK, 0 rows affected (2.37 sec)

mysql> show create table tmp3 \G
*************************** 1. row ***************************
       Table: tmp3
Create Table: CREATE TABLE `tmp3` (
  `name` char(50) DEFAULT NULL,
  KEY `SingleIdx` (`name`(20)) # 单列索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

由上述结果可以看到，name字段创建单列索引成功，索引名为SingleIdx，索引长度为20。

创建组合索引：即对多个字段建立一个索引：

mysql> CREATE TABLE tmp4 (
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT NOT NULL,
    -> index MultiIdx(id, name, age)
    -> );
Query OK, 0 rows affected (2.14 sec)

mysql> SHOW CREATE TABLE tmp4 \G
*************************** 1. row ***************************
       Table: tmp4
Create Table: CREATE TABLE `tmp4` (
  `id` int NOT NULL,
  `name` char(30) NOT NULL,
  `age` int NOT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`) # 组合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

在已存在的表上创建索引：对于已经存在的数据表，可通过ALTER TABLE或CREATE INDEX语句来创建索引：

ALTER TABLE：其语法如下所示：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

举个例子：

# 创建新表
mysql> CREATE TABLE tmp5 (msg varchar(20));
Query OK, 0 rows affected (1.83 sec)

# 添加索引
mysql> ALTER TABLE tmp5 ADD INDEX(msg);
Query OK, 0 rows affected (1.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM tmp5 \G
*************************** 1. row ***************************
        Table: tmp5
   Non_unique: 1   # 非唯一索引
     Key_name: msg # 索引名称
 Seq_in_index: 1
  Column_name: msg # 索引字段名称
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.07 sec)

CREATE INDEX：该语句也可用来在已存在的表上添加索引，在 MySQL 中，CREATE INDEX被映射到一个ALTER TABLE语句上。其语法如下所示：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

举个例子：

# 创建新表
mysql> CREATE TABLE tmp6 (msg varchar(20));
Query OK, 0 rows affected (1.46 sec)

# 创建索引，索引长度为 10
mysql> CREATE INDEX msg_idx ON tmp6(msg(10));
Query OK, 0 rows affected (1.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看表结构
mysql> SHOW CREATE TABLE tmp6 \G
*************************** 1. row ***************************
       Table: tmp6
Create Table: CREATE TABLE `tmp6` (
  `msg` varchar(20) DEFAULT NULL,
  KEY `msg_idx` (`msg`(10)) # 索引创建成功
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

索引查询：有如下几种方法对表索引进行查询：

通过命令SHOW CREATE TABLE进行查询：其语法如下所示：

SHOW CREATE TABLE tbl_name

举个例子：

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` char(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

通过命令SHOW INDEX进行查询：其语法如下所示：

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

举个例子：查询表City的索引信息：

mysql> SHOW INDEX FROM City\G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 4188
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: CountryCode
 Seq_in_index: 1
  Column_name: CountryCode
    Collation: A
  Cardinality: 232
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

其中：

Table：表示创建索引的表。
Non_unique：表示是否是唯一索引。其中：0表示唯一索引，1表示非唯一索引。
Key_name：表示索引的名称。
Seq_in_index：表示该字段在索引中的位置。对于单列索引，该值为1，对于组合索引，该值为每个字段在索引定义中的顺序。
Column_name：表示创建索引的字段名。
Sub_part：表示索引的长度。
Null：表示该字段是否能为空值。
Index_type：表示索引结构。

删除索引：MySQL 中可通过命令ALTER TABLE或DROP INDEX删除索引。DROP INDEX语句内部被映射到一个ALTER TABLE语句中。

ALTER TABLE：其语法如下所示：

ALTER TABLE table_name DROP INDEX index_name

举个例子：删除表tmp1的索引：

mysql> SHOW CREATE TABLE tmp1 \G
*************************** 1. row ***************************
       Table: tmp1
Create Table: CREATE TABLE `tmp1` (
  `name` varchar(100) NOT NULL,
  KEY `name` (`name`)                                              # 存在索引 name
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE tmp1 DROP INDEX name;                           # 删除索引 name
Query OK, 0 rows affected (0.42 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tmp1 \G                                   # 索引删除成功
*************************** 1. row ***************************
       Table: tmp1
Create Table: CREATE TABLE `tmp1` (
  `name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

DROP INDEX：其语法如下所示：

DROP INDEX index_name ON table_name

举个例子：删除表tmp2的索引：

mysql> SHOW INDEX FROM tmp2 \G         # 显示索引信息
*************************** 1. row ***************************
        Table: tmp2
   Non_unique: 0
     Key_name: unique_idx              # 索引名称
 Seq_in_index: 1
  Column_name: id                      # 索引字段
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

mysql> DROP INDEX unique_idx ON tmp2;  # 删除索引
Query OK, 0 rows affected (2.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM tmp2 \G
Empty set (0.01 sec)                   # 删除索引成功

当删除表中的列时，如果该列为表中索引的组成部分，则该列也会从索引中进行删除。如果索引对应的列都被删除了，则该索引也将自动被删除。

MySQL - 索引

索引

猜你喜欢

热点阅读