VitePress

Appearance

MySQL 视图、索引 ​

MySQL索引可以分为哪些类型？ ​

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。

存储方式区分 ​

根据存储方式的不同，MySQL 中常用的索引在物理上分为  B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

1) B-树索引 ​

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。

B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：

  • 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
  • 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
  • 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：

  • 查询必须从索引的最左边的列开始。
  • 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
  • 存储引擎不能使用索引中范围条件右边的列。

2) 哈希索引 ​

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

  • MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
  • 不能使用 HASH 索引排序。
  • HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
  • HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

逻辑区分 ​

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1) 普通索引 ​

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

普通索引允许在定义索引的列中插入重复值和空值。

创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

例 1 ​

下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引。

CREATE INDEX index_id ON tb_student(id);

2) 唯一索引 ​

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引通常使用 UNIQUE 关键字。

例 2 ​

下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：

CREATE UNIQUE INDEX index_id ON tb_student(id);

其中，id 字段可以有唯一性约束，也可以没有。

3) 主键索引 ​

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4) 空间索引 ​

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。

创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。

空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

例 3 ​

下面在 tb_student 表中的 line 字段上建立名为 index_line 的索引，SQL 语句如下：

CREATE SPATIAL INDEX index_line ON tb_student(line);

其中，tb_student 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

5) 全文索引 ​

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

全文索引允许在索引列中插入重复值和空值。

不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。

例 4 ​

在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：

CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

实际使用区分 ​

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

1）单列索引 ​

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

例 5 ​

下面在 tb_student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4)。SQL 语句如下：

CREATE INDEX index_addr ON tb_student(address(4));

这样，查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。

2）多列索引 ​

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

例 6 ​

下面在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：

CREATE INDEX index_na ON tb_student(name,address);

该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

索引在什么情况下不会被使用？ ​

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。

1. 查询语句中使用LIKE关键字 ​

在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

例 1 ​

为了便于理解，我们先查询 tb_student 表中的数据，SQL 语句和运行结果如下：

sql
mysql> SELECT * FROM tb_student;
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  1 | 张三 |   12 | 男   |
|  2 | 李四 |   12 | 男   |
|  3 | 王五 |   13 | 女   |
|  4 | 张四 |   13 | 女   |
|  5 | 王四 |   15 | 男   |
|  6 | 赵六 |   12 | 女   |
+----+------+------+------+
6 rows in set (0.03 sec)

下面在查询语句中使用 LIKE 关键字，且匹配的字符串中含有“%”符号，使用 EXPLAIN 分析查询情况，SQL 语句和运行结果如下：

sql
mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '%四'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.01 sec)
mysql> CREATE INDEX index_name ON tb_student(name);
Query OK, 6 rows affected (0.13 sec)
mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: range
possible_keys: index_name
          key: index_name
      key_len: 77
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

第一个查询语句执行后，rows 参数的值为 6，表示这次查询过程中查询了 6 条记录；第二个查询语句执行后，rows 参数的值为 1，表示这次查询过程只查询 1 条记录。同样是使用 name 字段进行查询，因为第一个查询语句的 LIKE 关键字后的字符串是以“%”开头的，所以第一个查询语句没有使用索引，而第二个查询语句使用了索引 index_name。

2. 查询语句中使用多列索引 ​

多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

例 2 ​

在 name 和 age 两个字段上创建多列索引，并验证多列索引的使用情况，SQL 语句和运行结果如下：

sql
mysql> CREATE INDEX index_name_age ON tb_student(name,age);
Query OK, 6 rows affected (0.11 sec)
mysql> EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: range
possible_keys: index_name_age
          key: index_name_age
      key_len: 77
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.05 sec)
mysql> EXPLAIN SELECT * FROM tb_student WHERE age LIKE '12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

第一条查询语句的查询条件使用了 name 字段，分析结果显示 rows 参数的值为 1，且查询过程中使用了 index_name_age 索引。第二条查询语句的查询条件使用了 age 字段，结果显示 rows 参数的值为 6，且 key 参数的值为 NULL，这说明第二个查询语句没有使用索引。

因为 name 字段是多列索引的第一个字段，所以只有查询条件中使用了 name 字段才会使 index_name_age 索引起作用。

3. 查询语句中使用OR关键字 ​

查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引。如果 OR 前后有一个条件的列不是索引，那么查询中将不使用索引。

例 3 ​

下面演示 OR 关键字的使用。

sql
mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or sex='男'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: index_name,index_name_age
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 30.56
        Extra: Using where
1 row in set, 1 warning (0.06 sec)
mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or id='12'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,index_name,index_name_age
          key: index_name,PRIMARY
      key_len: 77,4
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using union(index_name,PRIMARY); Using where
1 row in set, 1 warning (0.01 sec)

由于 sex 字段没有索引，所以第一条查询语句没有使用索引；name 字段和 id 字段都有索引，所以第二条查询语句使用了 index_name 和 PRIMARY 索引 。

总结 ​

使用索引查询记录时，一定要注意索引的使用情况。例如，LIKE 关键字配置的字符串不能以“%”开头；使用多列索引时，查询条件必须要使用这个索引的第一个字段；使用 OR 关键字时，OR 关键字连接的所有条件都必须使用索引。

怎么提升索引的使用效率，设计出更高效的索引 ​

索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。本节将介绍一些索引的设计原则。

1. 选择唯一性索引 ​

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2. 为经常需要排序、分组和联合操作的字段建立索引 ​

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3. 为常作为查询条件的字段建立索引 ​

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

4. 限制索引的数目 ​

索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。

如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

5. 尽量使用数据量少的索引 ​

如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

6. 数据量小的表最好不要使用索引 ​

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

7. 尽量使用前缀来索引 ​

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

8. 删除不再使用或者很少使用的索引 ​

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

总结 ​

选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能只拘泥于上面的准则。应该在学习和工作中不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。