【MySQL】-- 索引（面试必问）

06-01 1519阅读

文章目录

1. 简介

1.1 索引是什么？
1.2 为什么要使用索引
2. 索引应该选择哪种数据结构

2.1 HASH
2.2 二叉搜索树
2.3 N叉树
2.4 B+树

2.4.1 简介
2.4.2 B+树的特点
2.4.3 B+树与B树的对比
3. MySQL中的页

3.1 为什么要使用页
3.2 页文件头和页文件尾
3.3 页主体
3.4 页目录
3.5 数据页头
4. B+树在MySQL索引中的应用

4.1 计算三层树高的B+树可以存放多少条记录
5. 索引分类

5.1 主键索引
5.2 普通索引
5.3 唯一索引
5.4 全文索引
5.5 聚集索引
5.6 非聚集索引
5.7 索引覆盖
6. 使用索引

6.1 自动创建
6.2 手动创建

6.2.1 主键索引
6.2.2 唯一索引
6.2.3 普通索引
6.3 创建复合索引
6.4 查看索引
6.5 删除索引

6.5.1 主键索引
6.5.2 其他索引
6.6 创建索引的注意事项

7. 怎么查看自己写的sql走没走索引

1. 简介

1.1 索引是什么？

MySQL的索引是⼀种数据结构，它可以帮助数据库⾼效地查询、更新数据表中的数据。索引通过⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。

不同的数据结构有自己的实现规则，不同的规则导致不同的数据结构的效率不同，最终时间复杂度和空间复杂度也不同。

数据库最重要的功能是存储数据，在保证数据安全的基础上尽可能的提升效率。

MySQL索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如汉语字典的目录（索引）页，我们可以按笔画、偏旁部首、拼音等排序的目录（索引I）快速查找到需要的字。

笔画索引

确认要查询的字的笔画数–>再去笔画数的栏目里找到目标字–>再去翻看对应的页

偏旁部首索引

先确认目标字的偏旁部首–>再去对应的画数里面找目标字–>得到对应的页数

拼音索引

先找声母–>再找韵母–>确认一个所在的范围–>在这个范围内找目标字–>得到目标字的页数

1.2 为什么要使用索引

使用索引的目的只有一个，就是提升数据检索的效率，在应用程序的运行过程中，查询操作的频率远远高于增删改的频率。

写入数据的时候按一定的规则把数据组织好（放在一个合适的位置）查看的时候也按照这个规则去找相应的目标。

2. 索引应该选择哪种数据结构

2.1 HASH

时间复杂度：0(1)，最重要的数据结构，没有之一。但是哈希不支持范围查找。

2.2 二叉搜索树

中序遍历是一个有序的序列，且支持范围查询。

时间复杂度：O(N)，可能会退化成一个单边树。

由于数据是在磁盘上保存的，每一次访问树的子节点都会发生一次磁盘I/O ，磁盘I/O是制约数据库性能的主要因素。

二叉树的高度是2，在数据量变大的时候，无法保证树高，树越高，在进行查询操作时的I/O次数就越多，性能就越低。

2.3 N叉树

通过观察，相同数据量的情况下，N叉树的树高可以得到有效的控制，也就意味着在相同数据量的情况下可以减少IO的次数，从而提升效率。但是MySQL认为N叉树做为索引的数据结构还不够好。

时间复杂度：O(logN）

2.4 B+树

2.4.1 简介

B+树是一种经常用于数据库和文件系统等场合的平衡查找树，MySQL索引采用的数据结构，以4阶B+树为例，如下图所示：

2.4.2 B+树的特点

能够保持数据稳定有序，插入与修改有较稳定的时间复杂度。
非叶子节点仅具有索引作用，不存储数据，所有叶子节点保真实数据。
所有叶子节点构成一个有序链表，可以按照key排序的次序依次遍历全部数据。

2.4.3 B+树与B树的对比

【面试题】B+树与B树的区别：

叶子节点之间有一个相互连接的引用，可以通过一个叶子节点找到它相邻的兄弟节点（MySQL 在组织叶子节点的时候使用的是双向链表）。
非叶子节点的值都包含在叶子节点中。（MySQL非叶子节点只保存了对子节点的引用，没有保存真实的数据，所有的真实数据全都在叶子节点中保存）。
对于B+树而言，在相同树高的情况下，查找任一元素的时间复杂度都一样，性能均衡。

3. MySQL中的页

3.1 为什么要使用页

在.ibd文件中最重要的结构体就是Page（页)，页是内存与磁盘交互的最小单元，默认大小为16KB，每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这样做，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是临近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时就可以从内存中直接读取，从而减少磁盘1/O提高性能。

局部性原理：是指程序在执行时呈现出局部性规律，在一段时间内，整个程序的执行仅限于程序中的某一部分。相应地，执行所访问的存储空间也局限于某个内存区域，局部性通常有两种形式：时间局部性和空间局部性。

时间局部性（TemporalLocality）：如果一个信息项正在被访问，那么在近期它很可能还会被再次访问。

空间局部性（SpatialLocality）：将来要用到的信息大概率与正在使用的信息在空间地址上是临近的。

每一个页中即使没有数据也会使用16KB的存储空间，同时与索引的B+树中的节点对应。可以通过系统变量innodb_page_size查看页的大小。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 | -- 16384是字节数，相当于16KB
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

为什么要定义每页的大小为16KB？

Linux操作系统中管理文件的最小单位是4KB，MySQL作为一个数据库程序，用4KB大小管理显然太小，所以定义了16KB的大小。

那么，在从磁盘上往内存上读取时也是4KB作为一份来进行读取，如果在读取的时候发生了错误，导致读取中断，是不是会导致数据丢失？

其实并不是，读取的过程中，在数据真正落盘之前会记录各种日志。

在MySQL中有多种不同类型的页，最常用的就是用来存储数据和索引的**“索引页”，也叫做"数据页”，但不论哪种类型的页都会包含页头（FileHeader）和页尾（FileTrailer)，页的主体信息使用数据"行"**进行填充，数据页的基本结构如下图所示：

页的结构：文件头、文件尾、页主体。

3.2 页文件头和页文件尾

文件头中记录着当前页文件的主要信息。

这里我们只关注，上一页页号和下一页页号，通过这两个属性可以把页与页之间链接起来，形成一个双向链表。

在B+树中每一个节点就是一个页

3.3 页主体

页主体部分是保存真实数据的主要区域，每当创建一个新页，都会自动分配两个行，一个是页内最小行Infimun，另一个是页内最大行Supremun，这两个行并不存储任何真实信息，而是作为数据行链表的头和尾，第一个数据行有一个记录下一行的地址偏移量的区域next_record将页内所有数据行组成了一个单向链表，此时新页的结构如下所示：

当向一个新页插入数据时，将Infimun连接第一个数据行，最后一行真实数据行连接Supremun，这样数据行就构建成了一个单向链表，更多的行数据插入后，会按照主键从小到大的顺序进行链接，如下图所示

3.4 页目录

当按主键或索引查找某条数据时，最直接简单的方法就是从头行infimun开始，沿着链表顺序逐个比对查找，但一个页有16KB，通常会存在数百行数据，每次都要遍历数百行，无法满足高效查询，为了提高查询效率，InnoDB采用二分查找来解决查询效率问题。
具体实现方式是，在每一个页中加入一个叫做页目录PageDirectory的结构，将页内包括头行、尾行在内的所有行进行分组，约定头行单独为一组，其他每个组最多8条数据，同时把每个组最后一行在页中的地址，按主键从小到大的顺序记录在页目录中在，页目录中的每一个位置称为一个槽，每个槽都对应了一个分组，一旦分组中的数据行超过分组的上限8个时，就会分裂出一个新的分组。
后续在查询某行时，就可以通过二分查找，先找到对应的槽，然后在槽内最多8个数据行中进行遍历即可，从而大幅提高了查询效率，这时一个页的核心结构就完成了。

例如要查找主键为6的行，先比对槽中记录的主键值，定位到最后一个槽2，再从最后一个槽中的第一条记录遍历，第二条记录就是我们要查询的目标行。

3.5 数据页头

数据页头记录了当前页保存数据相关的信息：

4. B+树在MySQL索引中的应用

非叶子节点保存索引数据，叶子节点保存真实数据，如下图所示：

非叶子节点记录的是索引信息。索引页保存的是主键的值和子节点的引用。

页与页之间建立关联关系，最终形成一个双向循环链表。

以查找id为5的记录，完整的检索过程如下：

首先判断B+树的根节点中的索引记录，此时5加载索引页2–>加载数据页3

如果将索引页缓存到内存中，那么一次I/O就可以找到目标数据，进一步提升查询性能。

4.1 计算三层树高的B+树可以存放多少条记录

假设一条数据记录大小为1KB，那么一个16KB的数据页，就可以保存16条数据
索引页中存的是主键值和子节点的引用，也就是下一个节点的偏移量(地址)
主键bigint 8byte，下一页地址6byte，也就是说一条索引引记录占了8+6=14byte
—个索引页可以存16*1024／14=1.170(也就说一个索引的度是1170，可以有1170个子节点)
理论上一个三层树高的B+树可以存1170117016=21,902,400条记录.
在当前的场景下，表中有2000W+条数据，通过三次I/O就可以完成数据的查找，而且性能均衡。

5. 索引分类

5.1 主键索引

当在一个表上定义一个主键PRIMARYKEY时，InnoDB使用它作为聚集索引。

如果为表定义了主键，则自动创建主键索引。

推荐为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键，则添加一个自增列。

如果没有为表定义主键索引，innodb会使用唯一列构建索引树，如果没有唯一列，则自动生成一个row_id构建索引树。

5.2 普通索引

普通索引是最基本的索引类型，没有唯一性的限制。

为了提升查询效率，可以为查询频繁的列创建普通索引。

比如：.....where name = 'xxx'使用name作为条件查询的比较频繁，那么就可以为name列单独创建一个索引，如果手动创建了索引，都会生成一个与之对应的索引树。

可为多个列创建组合索引，称为复合索引或组合索引。

索引中包含多个列，列在索引中的排序按创建时指定的顺序排序，同时每个索引行都会包含主键值。

5.3 唯一索引

当在一个表上定义一个唯一键UNQUE 时，自动创建唯一索引。
唯一索引与普通索引类似，但区别在于唯一索引的列不允许有重复值。

5.4 全文索引

全文索引是基于文本列(CHAR、VARCHAR或TEXT列)上创建，以加快对这些列中包含的数据查询和DML操作。

varchar或text用于大文本类型。

全文索引用于全文搜索，仅MyISAM和InnoDB引擎支持。

有专门的文档数据库，可以高效的处理文档搜索。

5.5 聚集索引

聚集索引与主键索引是同义词
如果没有为表定义PRIMARYKEY，InnoDB使用第一个UNIQUE和NOT NULL的列作为聚集索引。
如果表中没有PRIMARYKEY或合适的UNIQUE索引l，InnoDB会为新插入的行生成一个行号并用6字节的ROW_ID字段记录，ROW_ID单调递增，并使用ROW_ID做为索引。

5.6 非聚集索引

集索引以外的索引称为非聚集索引或二级索引。
二级索引中的每条记录都包含该行的主键列，以及二级索引指定的列。

InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询。

select * from student where name = '张三' and sno = '100';

普通索引（name, sno），通过name,sno查询学生的详细信息。

我们的目的是查询学生的详细信息，但是此时我们数据页中的数据并不完整，所以会以普通索引的列为条件查到主键值，再用主键值去主键索引树种差学生的详细信息，涉及到两个索引树，这个过程叫回表查询。

5.7 索引覆盖

当一个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖。

select name from student where name = '张三' and sno = '100';

这条sql语句只要求查询出name，查询的列包含在创建索引的树中，就可以直接从普通索引中返回结果，这个现象叫索引覆盖。

6. 使用索引

6.1 自动创建

当我们为一张表加主键约束(Primary key)，外键约束(Foreign Key)，唯一约束(Unique)时,MySQL会为对应的的列自动创建一个索引。
如果表不指定任何约束时，MySQL会自动为每一列生成一个索引I并用ROW_ID进行标识。

6.2 手动创建

6.2.1 主键索引

mysql> create table t_pk1(
    ->   id bigint primary key auto_increment,
    ->  name varchar(10)
    -> );
Query OK, 0 rows affected (0.09 sec)
mysql> desc t_pk1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> show index from t_pk1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_pk1 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

6.2.2 唯一索引

mysql> create table t_un1(
    ->   id bigint primary key auto_increment,
    ->   name varchar(10) unique
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc t_un1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show index from t_un1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_un1 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_un1 |          0 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.03 sec)

6.2.3 普通索引

普通索引创建的时机：

创建表的时候，明确知道某些列是频繁查询的列，就直接创建普通索引（当表中的数据比较少的时候，全表扫描的效率可能会比使用索引的效率还要高）。
在工作中，一般当表中的数据量到了十几万的时候才创建索引。
随着业务的不断发展，在版本迭代的过程中添加索引。

-- 定义完表结构之后单独指定索引列
mysql> create table t_index1(
    ->   id bigint primary key auto_increment,
    ->   name varchar(10) unique,
    ->   sno varchar(10),
    ->   index(sno)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t_index1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  | UNI | NULL    |                |
| sno   | varchar(10) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

-- 使用create index语句创建索引
mysql> create table t_index3(
    ->   id bigint primary key auto_increment,
    ->   name varchar(10) unique,
    ->   sno varchar(10)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc t_index3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  | UNI | NULL    |                |
| sno   | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> create index idx_index3_sno on t_index3(sno);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t_index3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  | UNI | NULL    |                |
| sno   | varchar(10) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

create index：是创建索引的关键字。

idx_表名_索引包含的列名：索引命令。

6.3 创建复合索引

创建语法与创建普通索引相同，只不过指定多个列，列与列之间用逗号隔开。

方式一：定义完表结构之后指定索引列

-- 定义完表结构之后指定索引列
mysql> create table t_index4(
    ->   id bigint primary key auto_increment,
    ->   name varchar(10) unique,
    ->   sno varchar(10),
    ->   class_id bigint,
    ->   index (sno, class_id)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc t_index4;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | YES  | UNI | NULL    |                |
| sno      | varchar(10) | YES  | MUL | NULL    |                |
| class_id | bigint      | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show index from t_index4;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_index4 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_index4 |          0 | name     |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index4 |          1 | sno      |            1 | sno         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index4 |          1 | sno      |            2 | class_id    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.02 sec)

方式二：单独创建索引并指定索引名（推荐使用这种方式）

mysql> create table t_index6(
    ->   id bigint primary key auto_increment,
    ->   name varchar(10) unique,
    ->   sno varchar(10),
    ->   class_id bigint
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> create index idx_index6_sno_classId on t_index6(sno, class_id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from t_index6;
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_index6 |          0 | PRIMARY                |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_index6 |          0 | name                   |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            1 | sno         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            2 | class_id    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.03 sec)

6.4 查看索引

方式一：show keys from 表名;

mysql> show keys from t_index6;
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_index6 |          0 | PRIMARY                |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_index6 |          0 | name                   |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            1 | sno         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            2 | class_id    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

方式二：show index from 表名;

mysql> show index from t_index6;
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_index6 |          0 | PRIMARY                |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t_index6 |          0 | name                   |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            1 | sno         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            2 | class_id    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

方式三：desc 表名

mysql> desc t_index6;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | YES  | UNI | NULL    |                |
| sno      | varchar(10) | YES  | MUL | NULL    |                |
| class_id | bigint      | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

6.5 删除索引

6.5.1 主键索引

语法

alter table 表名 drop peimary key;

删除主键索引时不用指定索引名，因为表中只有一个主键。

删除之前要把自增取消，如果存在自增列则报错。

mysql> alter table t_index6 drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> alter table t_index6 modify id bigint;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t_index6 drop primary key;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from t_index6;
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_index6 |          0 | name                   |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            1 | sno         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            2 | class_id    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

6.5.2 其他索引

语法

alter table 表名 drop index 索引名;

mysql> alter table t_index6 drop index name;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from t_index6;
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t_index6 |          1 | idx_index6_sno_classId |            1 | sno         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t_index6 |          1 | idx_index6_sno_classId |            2 | class_id    | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
mysql> alter table t_index6 drop index idx_index6_sno_classId;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from t_index6;
Empty set (0.00 sec)

6.6 创建索引的注意事项

索引应该创建在高频查询的列上。
索引需要占用额外的存储空间。
对表进行插入、更新和删除操作时，同时也会修索引，可能会影响性能。
创建过多或不合理的索引会导致性能下降，需要谨慎选择和规划索引。

7. 怎么查看自己写的sql走没走索引

查询所有

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

2. 使用主键查询

mysql> select * from student where id = 1;
+----+--------+--------+------+--------+-------------+----------+
| id | name   | sno    | age  | gender | enroll_date | class_id |
+----+--------+--------+------+--------+-------------+----------+
|  1 | 唐三藏 | 100001 |   18 |      1 | 1986-09-01  |        1 |
+----+--------+--------+------+--------+-------------+----------+
1 row in set (0.00 sec)
mysql> desc student;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | bigint      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | NO   |     | NULL    |                |
| sno         | varchar(10) | NO   |     | NULL    |                |
| age         | int         | YES  |     | NULL    |                |
| gender      | tinyint(1)  | YES  |     | NULL    |                |
| enroll_date | date        | YES  |     | NULL    |                |
| class_id    | bigint      | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> explain select * from student where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3. 子查询中使用索引

mysql> explain select * from student where id = (select id from student where name = '唐三藏');
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | student | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | student | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    8 |    12.50 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

为name列创建索引

mysql> create index idx_student_name on student(name);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from student where id = (select id from student where name = '唐三藏');
+----+-------------+---------+------------+-------+------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+------------------+------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | student | NULL       | const | PRIMARY          | PRIMARY          | 8       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | student | NULL       | ref   | idx_student_name | idx_student_name | 82      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+-------+------------------+------------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

4. 创建复合索引

mysql> create index idx_student_sno_classId on student(sno, class_id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from student;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY                 |            1 | id          | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_student_name        |            1 | name        | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_student_sno_classId |            1 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_student_sno_classId |            2 | class_id    | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.05 sec)

问：下面这条sql语句走不走索引？发不发生回表查询？

mysql> select * from student where sno = '100002';
+----+--------+--------+------+--------+-------------+----------+
| id | name   | sno    | age  | gender | enroll_date | class_id |
+----+--------+--------+------+--------+-------------+----------+
|  2 | 孙悟空 | 100002 |   18 |      1 | 1986-09-01  |        1 |
+----+--------+--------+------+--------+-------------+----------+
1 row in set (0.00 sec)

答：会走索引，会发生回表查询。

mysql> explain select * from student where sno = '100002';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_student_sno_classId | idx_student_sno_classId | 42      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id, sno, class_id from student where sno = '100002' and class_id = 1;
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys           | key                     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_student_sno_classId | idx_student_sno_classId | 50      | const,const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id, sno, class_id from student where sno = '100002';
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_student_sno_classId | idx_student_sno_classId | 42      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 创建复合索引
mysql> create index idx_name_sno on student(name, sno);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from student;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| student |          0 | PRIMARY                 |            1 | id          | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_student_name        |            1 | name        | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_student_sno_classId |            1 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_student_sno_classId |            2 | class_id    | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_name_sno            |            1 | name        | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| student |          1 | idx_name_sno            |            2 | sno         | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.01 sec)

mysql> explain select * from student where name = '孙悟空';
+----+-------------+---------+------------+------+-------------------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                 | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_student_name,idx_name_sno | idx_student_name | 82      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-------------------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name, sno from student where name = '孙悟空';
+----+-------------+---------+------------+------+-------------------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                 | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-------------------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_student_name,idx_name_sno | idx_student_name | 82      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-------------------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

理论上使用索引覆盖效率高一些，但是真实发生了全表扫描，最终MySQL使用哪个索引或是哪种方式执行查询由优化器决定。

指定查询时使用哪个索引

mysql> explain select name, sno from student force index(idx_name_sno) where name = '孙悟空';
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name_sno  | idx_name_sno | 82      | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们。

« 2025年9月 »
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

文章目录

1. 简介

1.1 索引是什么？

1.2 为什么要使用索引

2. 索引应该选择哪种数据结构

2.1 HASH

2.2 二叉搜索树

2.3 N叉树

2.4 B+树

2.4.1 简介

2.4.2 B+树的特点

2.4.3 B+树与B树的对比

3. MySQL中的页

3.1 为什么要使用页

3.2 页文件头和页文件尾

3.3 页主体

3.4 页目录

3.5 数据页头

4. B+树在MySQL索引中的应用

4.1 计算三层树高的B+树可以存放多少条记录

5. 索引分类

5.1 主键索引

5.2 普通索引

5.3 唯一索引

5.4 全文索引

5.5 聚集索引

5.6 非聚集索引

5.7 索引覆盖

6. 使用索引

6.1 自动创建

6.2 手动创建

6.2.1 主键索引

6.2.2 唯一索引

6.2.3 普通索引

6.3 创建复合索引

6.4 查看索引

6.5 删除索引

6.5.1 主键索引

6.5.2 其他索引

6.6 创建索引的注意事项

7. 怎么查看自己写的sql走没走索引

相关阅读

Linux Shell异常处理，常见问题与解决方案？Shell脚本报错怎么解决？Shell脚本报错如何快速解决？

Linux中高效复制多级目录与文件的技巧与方法？如何快速复制Linux多级目录？Linux多级目录如何快速复制？

IDA Pro监听Linux进程，逆向工程中的动态调试技术？IDA如何动态调试Linux进程？IDA如何逆向调试Linux进程？

Linux下按pip安装与管理Python包指南？Linux如何用pip管理Python包？Linux下pip怎么管理Python包？

目录[+]