【MySQL系列】MySQL数据库索引详解

迈不过友情╰ 2024-03-03 02:48 52阅读 0赞

### **目录** ###

一、为什么要用索引？

二、什么是索引？

三、MySQL索引使用场景

四、索引的原理

五、MySQL的存储引擎

六、索引的数据结构

七、索引如何使用

八、实际使用示例

九、优缺点、使用建议和注意事项

十、为什么Mysql不选择Hash索引？

十一、总结

--------------------

#### 一、为什么要用索引？ ####

索引，可能让好很多人望而生畏，毕竟每次面试时候 MySQL 的索引一定是必问内容，哪怕先撇开面试，就在平常的开发中，对于 SQL 的优化也而是重中之重。 为什么要用索引呢？

1.  提高查询性能：索引可以减少数据库查询时需要扫描的行数，通过直接访问索引来快速定位所需数据，从而加快查询速度。
2.  加速排序和分组：索引可以帮助加速ORDER BY和GROUP BY操作，减少排序和分组的时间消耗。
3.  加速连接操作：当进行连接查询时，索引可以帮助优化连接操作的效率，减少连接操作的时间。
4.  提高数据唯一性和完整性：通过在列上创建唯一索引或主键索引，可以确保数据的唯一性和完整性，避免重复数据或无效数据的存在。
5.  减少磁盘IO和系统负载：通过使用索引，可以有效减少磁盘IO操作和系统负载，提高系统的整体性能。

#### 二、什么是索引？ ####

索引是数据库管理系统中用于提高查询速度的一种数据结构。在MySQL中，索引可以看作是一种特殊的表，其中包含了对数据表中特定列的值及其在数据表中的位置信息。通过使用索引，MySQL可以在不需要扫描整个表的情况下快速找到与查询条件匹配的记录。

在MySQL中，主要有以下几种类型的索引：

![cc67a70cc89242fcba8969fdb52ef85f.png][]

1.  **B+tree索引**：是在B树基础上的一种优化，其更适合做存储索引结构。在 B+tree 中，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。此外在 B+tree 中各个数据页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
2.  **哈希索引**：哈希索引是一种基于哈希表实现的索引，它支持全值匹配，但不支持范围查询和前缀匹配。在MySQL中，哈希索引主要应用于MEMORY存储引擎。
3.  **全文索引**：全文索引是一种用于文本数据模糊查询的特殊索引，它基于倒排索引实现。在MySQL中，全文索引主要应用于MyISAM和InnoDB存储引擎。
4.  **B-Tree索引**：B树索引是MySQL中最常见的索引类型，适用于大部分场景。它支持全值匹配、范围查询和前缀匹配。
5.  **聚簇索引**：就是按照每张表的主键构造一颗 B+tree，同时叶子节点中存放的就是整张表的行记录数据，聚集索引的叶子节点被称为数据页。
6.  **非聚集索引（也叫二级索引、辅助索引）**：非聚集索引的结构和聚集索引基本相同（非叶子结点存储的都是索引指针），区别在于叶子节点存放的不是行数据而是数据主键。因此在使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找。
7.  **主键索引**：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
8.  **普通索引**：最基本的索引，它没有任何限制，用于加速查询。
9.  **联合索引**：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
10. **唯一索引**：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
11. **空间索引**：空间索引是一种用于地理空间数据查询的特殊索引，它基于R树实现。在MySQL中，空间索引主要应用于MyISAM和InnoDB存储引擎。

#### 三、MySQL索引使用场景 ####

以下是一些常见的索引使用场景：

1.  **大表查询优化**：对于包含大量数据的表，使用索引可以大幅提高查询速度。
2.  **主键和外键约束**：在创建主键和外键约束时，MySQL会自动创建相应的索引，以确保数据的唯一性和引用完整性。
3.  **范围查询和排序**：对于范围查询和排序操作，使用B-Tree索引可以显著提高查询效率。
4.  **文本数据模糊查询**：对于文本数据的模糊查询，使用全文索引可以提高查询速度。

#### 四、索引的原理 ####

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引的基本原理：

1.  把创建索引列的内容进行排序
2.  对排序的结果生成倒排表
3.  在倒排表内容上拼接上数据行地址
4.  查询数据时，先拿到倒排表内容，在取出数据行地址，从而拿到具体的数据

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址，如果没有索引的话，我们要从千万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。但是我们有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是 特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

#### 五、MySQL的存储引擎 ####

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，**一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求**，使用合适的存储引擎，将会提高整个数据库的性能 。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

##### **5.1查看存储引擎** #####

-- 查看支持的存储引擎
    show engines;
    
    -- 查看默认存储引擎
    SHOW VARIABLES LIKE 'storage_engine'
    
    --查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
    show create table tablename
    
    --准确查看某个数据库中的某一表所使用的存储引擎
    show table status like 'tablename'
    show table status from database where name="tablename"

![61bfbb07fe1f40b3a58b358b25c9638e.png][]

##### **5.2设置存储引擎** #####

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
    CREATE TABLE t1 (i INT) ENGINE = INNODB;
    CREATE TABLE t2 (i INT) ENGINE = CSV;
    CREATE TABLE t3 (i INT) ENGINE = MEMORY;
    
    -- 修改存储引擎
    ALTER TABLE t ENGINE = InnoDB;
    
    -- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
    SET default_storage_engine=NDBCLUSTER;

##### 5.3如何选择合适的存储引擎 #####

1.  MyISAM：如果应用是以查询和插入操作为主，只有极少更新和删除操作，并且对事务的完整性没有要求，没有并发写操作。
2.  InnoDB：用于事务处理应用程序，支持外键。对事务的完整性有较高要求，除了查询和插入操作外，还有很多更新和删除操作。有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚。实现了四个标准的隔离级别，默认级别是可重复读。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。
3.  MEMORY：数据存在内存中，默认使用HASH索引，访问速度快，服务关闭数据就会丢失。通常用于更新不太频繁的小表，可以快速得到访问结果。缺陷是对表的大小有限制，表太大无法缓存在内存中，其次要确保表的数据可以恢复。
4.  MERGE：是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身没有数据，查询、更新、删除操作实际上是对内部的MyISAM表进行的。DROP操作只是删除MERGE表的定义，对内部的表没有任何影响。优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。

#### 六、索引的数据结构 ####

索引的定义：索引(Index)是帮助MySQL高效获取数据的排好序的数据结构。

索引中常见的数据结构有以下几种：

*  Hash表
 *  二叉树
 *  红黑树
 *  B-Tree
 *  B+Tree

##### **6.1Hash表** #####

通过索引的key进行一次hash计算，就可以快速获取磁盘文件指针，对于指定索引查找文件非常快，但是对于范围查找没法支持，有时候也会出现Hash冲突的情况。

![0a9e6a22a0d2c46b9ef7b72b50b9f641.jpeg][]

##### **6.2二叉树** #####

二叉树的特点：左边子节点的数据小于父节点数据，右边子节点的数据大于父节点数据。如下图所示，如果col2是索引，查找索引为65的行元素，只需要查找两次，就可以获取到行元素所在的磁盘指针地址。

![6866fb558d0a96cc63a2ce1000de9f65.jpeg][]

但如果是一个按照顺序递增的值，例如为col1建立索引，不再适合使用二叉树建立索引，因为此时使用二叉树建立索引将会变成一个链式索引，此时的索引结构如下图所示，如果查找6节点需要6次遍历才能找到。

![a3e62f7290d149b88ca9badcb5782955.jpeg][]

##### **6.3红黑树** #####

红黑树是一种二叉平衡树，可以提高查询效率，此时若再查找6节点只需要遍历3次就能找到了。但红黑树也有缺点，当存储大数据量时，树的高度就会变的不可控， 数量越大，树的高度越高，查询的效率将会大大降低。

![86f3aa0529c115d96c6b285cee9f114c.jpeg][]

##### **6.4B-Tree** #####

B-Tree是一种多路二叉树，所具有的特点：1 叶节点具有相同的深度，叶节点的指针为空；2 所有索引元素不重复；3 节点中的数据索引从左到右递增排列。

![d16fd5a2812e3eea8e4f56f657874115.jpeg][]

##### **6.5B+Tree** #####

B+Tree是B-Tree的变种，所具有的特点：1 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引；2 叶子节点包含所有索引字段；3 叶子节点用指针连接，提高区间访问的性能。

![a5702fbc1580e9c4fa3d5d150066bb2c.jpeg][]

与红黑树相比，B-Tree和B+Tree两种数据结构都更加矮胖，存储相同数量级的索引数据时，层级更低。

B-Tree和B+Tree之间一个很大的不同，是B+Tree的节点上不储存value，只储存key，而叶子节点上储存了所有key-value集合，并且节点之间都是有序的。这样的好处是每一次磁盘IO能够读取的节点更多，也就是树的度(Max.Degree)可以设置的更大一些，因为每次磁盘IO读取的磁盘页数是一定的。例如，每次磁盘IO能够读取1页=4kb，那么省去value的情况下同样一页数据能够读取更多的key，这样就大大减少了磁盘的IO次数。

此外，B+Tree也是排好序的数据结构，数据库中><或者order by等都可以直接依赖这一特性。

MySQL中对于索引使用的主要数据结构也是B+Tree，目的也是在读取数据时能够减少磁盘IO。

#### 七、索引如何使用 ####

在MySQL中，创建索引有以下几种方法：

1.  **在创建表时创建索引**：在创建表时，可以使用`INDEX`、`UNIQUE INDEX`、`PRIMARY KEY`或`FOREIGN KEY`关键字创建索引。
    
    示例：
    
        CREATE TABLE users (
             id INT AUTO_INCREMENT PRIMARY KEY,
             username VARCHAR(255) UNIQUE,
             email VARCHAR(255) UNIQUE,
             age INT,
             INDEX (age)
         );
2.  **在已有表上创建索引**：可以使用`CREATE INDEX`或`CREATE UNIQUE INDEX`语句创建索引。
    
    示例：
    
        CREATE INDEX idx_age ON users (age);
         CREATE UNIQUE INDEX idx_email ON users (email);
3.  **使用ALTER TABLE创建索引**：可以使用`ALTER TABLE`语句添加或删除索引。
    
    示例：
    
        ALTER TABLE users ADD INDEX (age);
         ALTER TABLE users DROP INDEX idx_age

#### 八、实际使用示例 ####

假设我们有一个用户表`users`，表结构如下：

CREATE TABLE users (
         id INT AUTO_INCREMENT PRIMARY KEY,
         username VARCHAR(255) UNIQUE,
         email VARCHAR(255) UNIQUE,
         age INT
     );

现在，我们将演示如何使用索引优化以下查询：

1.  **根据ID查询用户**：在这种情况下，我们可以使用主键索引加速查询。
    
    示例：
    
        SELECT * FROM users WHERE id = 123;
2.  **根据用户名查询用户**：在这种情况下，我们可以使用唯一索引加速查询。
    
    示例：
    
        SELECT * FROM users WHERE username = 'john';
3.  **根据年龄范围查询用户**：在这种情况下，我们可以使用B-Tree索引加速查询。
    
    示例：
    
        SELECT * FROM users WHERE age BETWEEN 18 AND 25;
         
         为了加速此查询，我们需要为`age`列创建一个索引：
         
         CREATE INDEX idx_age ON users (age);

#### 九、优缺点、使用建议和注意事项 ####

##### 1、优点 #####

*  **提高查询速度**：索引可以显著提高数据库查询速度。
 *  **确保数据唯一性和引用完整性**：通过使用主键和外键索引，可以确保数据的唯一性和引用完整性。

##### 2、缺点 #####

*  **增加存储空间和维护成本**：索引会占用额外的存储空间，并增加数据更新和维护的成本。
 *  **影响写操作性能**：由于在插入、更新和删除操作时需要维护索引，索引可能会降低写操作的性能。

##### 3、使用建议和注意事项 #####

*  **选择合适的索引类型**：根据查询需求选择合适的索引类型，如B-Tree索引、哈希索引、空间索引或全文索引。
 *  **为常用查询列创建索引**：根据实际查询需求，为常用查询条件和排序列创建索引。
 *  **避免过度索引**：过多的索引可能会降低写操作性能并占用额外的存储空间。在创建索引时，要权衡查询优化和空间、性能成本。
 *  **定期评估索引效果**：通过使用慢查询日志、性能监控工具和`EXPLAIN`语句，定期评估索引的效果并调整索引策略。

#### 十、为什么Mysql不选择Hash索引？ ####

Hash索引的优势是精确查找的话，速度会更快，为什么不选择Hash索引

1.  Hash索引不适合范围查找，而B+树特别适合范围查找（特别是聚簇索引的时候）
2.  Hash索引每次查询要加载所有的索引数据到内存当中，而B+树只需要根据匹配规则选择对应的叶子数据加载即可
3.  另外B+树引入了缓存机制 和 数据页技术来提升性能（不过理论上来说，这两个特性Hash索引也可以实现）

#### 十一、总结 ####

1.  使用索引可以帮助我们从海量的数据中快速定位想要查找的数据，不过索引也存在一些不足，比如占用存储空间、降低数据库写操作的性能等，如果有多个索引还会增加索引选择的时间。当我们使用索引时，需要平衡索引的利和弊。
2.  在实际工作中，我们还需要基于需求和数据本身的分布情况来确定是否使用索引，尽管索引不是万能的，但数据量大的时候不使用索引是不可想象的，毕竟索引的本质，是帮助我们提升数据检索的效率。

[cc67a70cc89242fcba8969fdb52ef85f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/08fd5d8604764da1a113495640e8c2c4.png
[61bfbb07fe1f40b3a58b358b25c9638e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/9499096dad43437b870b1906c26a895d.png
[0a9e6a22a0d2c46b9ef7b72b50b9f641.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/018cbcb8374944d3bf697f4c0d60fb3a.png
[6866fb558d0a96cc63a2ce1000de9f65.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/fee3b561fcbd46ffac963d50392468a1.png
[a3e62f7290d149b88ca9badcb5782955.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/82623c73c2244afbaed03bc24ed67c96.png
[86f3aa0529c115d96c6b285cee9f114c.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/b89b55128d704aa0b7e9cbdc71a51e3a.png
[d16fd5a2812e3eea8e4f56f657874115.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/da47c9ffb3f04ff4aa2c4864a5dadf81.png
[a5702fbc1580e9c4fa3d5d150066bb2c.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/03/f9745880310d4ed1a604b14512c38b64.png