【MySQL】（9）索引

基础知识

局部性原理

局部性原理是计算机科学中的一个基本概念，指的是在程序执行期间，访问的内存地址往往具有一定的集中性。也就是说，程序执行期间访问的数据通常集中在较小的一部分内存中，而不是分散在整个内存中。

局部性原理可以分为两种类型：

1. 时间局部性：指一个内存地址一旦被访问，接下来一段时间内它很可能会被多次访问。

2. 空间局部性：指一旦一个内存地址被访问，它附近的内存地址很可能也会被访问。

这个原理是计算机体系结构中的一个重要概念，因为它可以帮助优化内存访问，提高程序的性能和效率。许多计算机系统都利用了这个原理，例如缓存、分页机制等。

扇区，磁盘块，页

扇区 sector

磁盘的物理读写以扇区为基本单位。磁盘上的每个磁道被等分为若干个弧段，这些弧段称之为扇区。通常情况下每个扇区的大小是 512 字节。

注意：扇区是磁盘物理层面的概念，操作系统是不直接与扇区交互的，而是与多个连续扇区组成的磁盘块交互。由于扇区是物理层面的概念，所以无法在系统中进行大小的更改。

磁盘块 IO Block

磁盘块是文件系统读写数据的最小单位，也叫磁盘簇。操作系统将相邻的扇区组合在一起，形成一个块，对块进行管理。每个磁盘块可以包括 2、4、8、16、32 或 64 个扇区。磁盘块是操作系统所使用的逻辑概念，而非磁盘的物理概念。一般是 4KB。

为了更好地管理磁盘空间和更高效地从硬盘读取数据，操作系统规定一个磁盘块中只能放置一个文件，因此文件所占用的空间，只能是磁盘块的整数倍，那就意味着会出现文件的实际大小，会小于其所占用的磁盘空间的情况。

页 page

页是内存的最小存储单位。页的大小通常为磁盘块大小的 2^n 倍，一般也是 4KB。

总结

扇区是磁盘的物理读写单位。

页和磁盘块是逻辑单位：

• 页，内存操作的基本单位
• 磁盘块，磁盘操作的基本单位

磁盘预读

概念：

磁盘预读（Disk Pre-fetching）是一种磁盘I/O优化技术，它通过在读取磁盘数据时，读取比当前需要的数据更多的数据，并缓存到内存中，以提高磁盘I/O的效率和性能。

磁盘预读的原理是利用磁盘的顺序读取比随机读取快的特性，提前预读一部分附近的数据，根据局部性原理，下一次读取数据时很可能需要的就是上一次数据附近的数据，这样就可以直接从内存中读取，而不是去磁盘中读，从而提高效率。

举个例子：

我们可以把磁盘想象成一个菜单，每次点餐都需要告诉服务员需要哪些菜品，然后服务员才能把菜品送上桌。如果我们每次只点一道菜，那么服务员每次都需要走来走去，效率很低。但如果我们一次性把需要的菜品都点好，服务员就可以一次性把所有菜品送上桌了，这样就可以显著提高点餐的效率。

预读的长度：预读的长度是页的整数倍

B树

B+树，B+树和B树的区别

B树和B+树是两种常见的数据结构，它们都是多路搜索树。它们的主要区别在于内部节点和叶子节点的不同使用方式和结构。

B+树的特点：

• 有k个子结点的结点必然有k个关键字
• 非叶子结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
• 树的所有叶结点构成一个有序链表，可以按照关键字排序的次序遍历全部记录。

因此，B+树比B树更适合于范围查询和顺序访问，因为B+树的叶子节点之间是相互连接的，对整棵树的遍历就是对叶子结点的线性遍历。而B树更适合于随机访问，因为内部节点也存储数据，所以不需要搜索到叶子结点就可以找到需要的数据。

而且由于B+树在内部节点上不包含实际的数值信息，因此在页中能存放更多的key，减少I/O次数。

总的来说，B+树更适合作为数据库索引的数据结构

MySQL 与磁盘交互的基本单位

MySQL 作为一款应用软件，可以想像成一个特殊的文件系统，它有着更高的IO场景，所以，为了提高效率，MySQL 进行IO的基本单位是 16KB

MariaDB [(none)]> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 | --16384B=16KB
+------------------+-------+
1 row in set (0.00 sec)

MySQL 和磁盘进行数据交互的基本单位是 16KB，这个基本的数据单元，在 MySQL 中也叫作 page（注意和操作系统的 page 区分）

MySQL一次读取的数据，相当于操作系统中的16KB/4KB=4页，相当于磁盘中的16KB/512B=32个扇区

索引是什么

索引（Index）是数据库中用于提高数据检索效率的一种数据结构。索引可以分为多种类型，例如B+树索引、哈希索引、全文索引等。

在数据库中，当执行查询语句时，如果没有索引，数据库会逐行扫描数据表进行匹配，查询速度会非常慢。而使用索引可以将查找范围缩小到特定的行或数据块，从而减少扫描的数据量，提高查询效率。

虽然索引可以提高查询效率，但是它也会增加数据的存储空间和维护成本，并且在进行修改、插入或删除操作时，也会影响性能，因此需要在索引的使用和维护方面进行合理的平衡和优化。

聚簇索引与非聚簇索引

1. 聚簇索引（Clustered Index）

   聚簇索引又称为聚集索引或主索引，是按照索引列的顺序将表中的记录存储在硬盘上的一种索引方式。叶子节点中存放的就是整张表的行记录数据。这种数据与索引在一起的索引方案，叫做聚簇索引。一个表只能有一个聚簇索引。

   如果表有主键，那么主键就会成为聚簇索引，如果没有主键，则第一个唯一索引会成为聚簇索引。

   优点：

   • 访问速度更快，因为索引和数据在同一个 B+ 树中
   • 对于主键的范围查找速度快

   缺点：

   • 插入速度严重依赖于插入顺序，顺序插入最快，否则会出现页分裂
   • 更新主键的速度很慢

2. 非聚簇索引（Non-Clustered Index）

   非聚簇索引又称为二级索引或次要索引，它不会改变表的物理存储顺序，而是在另外的数据结构中维护索引信息。这种数据与索引分离的索引方案，叫做非聚簇索引。非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

   非聚簇索引可以在任何列上创建，包括主键列。非聚簇索引的叶节点中存储的是对应数据行的主键值以及指向对应数据行的指针，因此通过非聚簇索引查找记录需要二次查找，先找到主键，然后到主索引中找到数据记录，这个过程叫做回表查询。

注：InnoDB 存储引擎默认使用聚簇索引。MyISAM 存储引擎默认使用非聚簇索引

索引操作

创建主键索引

1. 在创建表时使用 PRIMARY KEY 约束：

   CREATE TABLE table_name (
       column1 INT NOT NULL,
       column2 VARCHAR(50) NOT NULL,
       PRIMARY KEY (column1)
   );
   

   这个语句会在 table_name 表上创建一个名为 PRIMARY 的主键索引，包括 column1 列。

2. 使用 ALTER TABLE 语句添加主键索引：

   ALTER TABLE table_name ADD PRIMARY KEY (column1);
   

   这个语句会在 table_name 表上添加一个名为 PRIMARY 的主键索引，包括 column1 列。

如果在创建主键索引时没有指定列名，则会默认使用表的第一个列作为主键列。如果需要修改主键索引，可以使用 ALTER TABLE 语句删除原有的主键索引，并重新添加新的主键索引。

主键索引的特点：

• 一个表中，最多有一个主键索引
• 主键索引的效率高（主键不可重复）
• 创建主键索引的列，它的值不能为 null，且不能重复
• 主键索引列的数据类型基本上是 int

创建唯一索引

唯一索引的创建方式与创建主键索引的方式差不多，就是给字段添加 unique 约束。

唯一索引的特点：

• 一个表中，可以有多个唯一索引
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定 not null，等价于主键索引

普通索引的创建

1. 创建表时定义索引

   CREATE TABLE table_name (
       column1 data_type [NOT NULL],
       column2 data_type [NOT NULL],
       ...
       INDEX idx_name (column1, column2, ...)
   );
   

2. CREATE INDEX 语句：

   CREATE INDEX idx_name ON table_name (column1, column2, ...);
   

   这个语句会在 table_name 表上创建名为 idx_name 的索引，包括 column1、column2 等列。可以指定多个列来创建联合索引。

3. ALTER TABLE 语句：使用 ALTER TABLE 语句可以在已有的表上添加、修改或删除索引。例如：

   ALTER TABLE table_name ADD INDEX idx_name (column1, column2, ...);
   ALTER TABLE table_name DROP INDEX idx_name;
   

普通索引的特点：

• 普通索引是最基本的索引类型，也是最常见的索引类型之一

• 一个表中可以有多个普通索引

• 如果某列需要创建索引，但是该列有重复的值，那么我们就可以使用普通索引

全文索引的创建使用

全文索引通常用于处理长文本字段，如文章内容、评论、电子邮件、论坛帖子等。

全文索引会对文本字段中的单词进行分词，并将分词后的单词作为索引的关键字。例如，如果要对一个包含“MySQL is a popular database”文本的字段进行全文索引，索引会将“MySQL”、“popular”、“database”等单词作为关键字，用于后续的全文搜索。

MySQL提供全文索引机制，但是有要求：

• 表的存储引擎必须是MyISAM
• 默认全文索引支持英文，不支持中文。如果要对中文进行全文检索，可以使用 sphinx 的中文版(coreseek)
• 需要创建全文索引的字段通常是文本类型的字段，如VARCHAR、TEXT等。

例：

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
)engine=MyISAM;

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

MariaDB [test_db]> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.01 sec)

查询具有 DataBase 的数据记录：

可以使用模糊查询：

MariaDB [test_db]> select * from articles where body like '%database%';
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

但是这种查询效率是比较慢的，没有用到全文索引。

我们可以 explain 工具来看一下有没有用到索引

MariaDB [test_db]> explain select * from articles where body like '%database%';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | articles | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

key 列显示为 NULL，看来确实没有用到索引

使用全文索引：

语法：

SELECT * FROM mytable
WHERE MATCH(title, body) AGAINST('database');

上述语句将返回包含单词 “MySQL” 的行。MATCH AGAINST 关键字用于指定要进行全文索引的列和要搜索的关键词。

MariaDB [test_db]> SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

使用 explain 分析：

MariaDB [test_db]> explain SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database');
+------+-------------+----------+----------+---------------+-------+---------+------+------+-------------+
| id   | select_type | table    | type     | possible_keys | key   | key_len | ref  | rows | Extra       |
+------+-------------+----------+----------+---------------+-------+---------+------+------+-------------+
|    1 | SIMPLE      | articles | fulltext | title         | title | 0       |      |    1 | Using where |
+------+-------------+----------+----------+---------------+-------+---------+------+------+-------------+
1 row in set (0.00 sec)

key 用到了 title

查询索引

1. show keys from 表名;
   

2. show index from 表名;
   

3. desc 表名;
   

删除索引

1. 删除主键索引

   alter table 表名 drop primary key
   

2. 其他索引的删除

   alter table 表名 drop index 索引名;
   

   索引名就是 show keys 中的 Key_name 字段

drop index 索引名 on 表名;

索引创建原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合创建索引
• 不会出现在 where 子句中的字段不适合创建索引

复合索引与最左匹配原则

复合索引是指包含多个列的索引。与单列索引相比，使用复合索引可以更好地支持多个列的查询，从而提高查询性能。

例如，下面的 SQL 语句创建了一个包含两个列的复合索引：

CREATE INDEX idx_name_age ON mytable (name, age);

---

索引最左匹配原则是指在查询时，只有使用了索引的最左边的列，索引才会生效。例如，如果使用上面的复合索引进行查询：

SELECT * FROM mytable WHERE age = 18;

虽然该查询中使用了复合索引中的列 age，但是它并没有使用最左边的列 name，因此该索引不会被使用。

索引覆盖

索引覆盖是指查询只需要使用到了索引中的数据，而不需要访问表中的实际数据行。这样可以提高查询性能，因为索引比表数据更小，可以更快地从磁盘加载到内存中。

例如，假设有如下查询：

SELECT name, age FROM mytable WHERE name = 'John';

如果 mytable 表上有一个包含 name 和 age 列的复合索引 idx_name_age，且该索引包含所有查询所需的列，那么 MySQL 可以直接从索引中获取到所需的数据，而不需要访问表中的实际数据行，这种情况就称为索引覆盖。