深入理解 MySQL 索引底层原理
什么是 MySQL 索引
MySQL 索引是用于加速查询的一种数据结构,可以将数据按照某种特定的方式排列,以便于快速查找和检索数据。与没有索引的表相比,使用索引可以显著提高查询效率和性能。
MySQL 索引的分类
MySQL 索引可以分为主键索引、唯一索引、普通索引、全文索引等多种类型。
- 主键索引:用于唯一标识每条记录的索引。
- 唯一索引:保证索引列的值唯一的索引。
- 普通索引:没有限制的普通索引。
- 全文索引:用于全文搜索的索引。
MySQL 索引底层原理
MySQL 索引底层实现原理是使用 B+ 树数据结构,而且每个索引都对应着一棵 B+ 树。当 MySQL 执行查询语句时,会根据查询条件在 B+ 树中查找相应的索引值,进而找到对应的数据行。
B+ 树的基本特点是:
- 每个节点存储多个键值,将键值按照大小排序。
- 叶子节点都是存储实际数据的节点。
- 所有叶子节点形成了一个双向链表,可以方便地进行遍历。
MySQL 索引的使用示例
示例一:普通索引
首先,我们创建一个表,并给其中一列添加普通索引。
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT,
INDEX idx_age(age)
);
然后,我们向表中插入 10000 条数据。
INSERT INTO users(name, age)
SELECT CONCAT('user', id), FLOOR(RAND() * 100)
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3;
接下来,我们使用 EXPLAIN 关键字来查看查询语句的执行计划。
EXPLAIN SELECT * FROM users WHERE age > 50;
执行结果如下:
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ref | idx_age | idx_age | 5 | const | 4889 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
可以看到,查询使用了 idx_age 索引,key 列展示了具体使用的索引名称,Extra 列显示了 Using where,表示会在索引基础上进一步筛选符合条件的记录。
示例二:联合索引
接着,我们为上述表增加联合索引,来看看索引的效率提升情况。
ALTER TABLE users ADD INDEX idx_name_age(name, age);
查询语句仍然是查找年龄在 50 岁以上的记录,但是使用的是新创建的联合索引:
EXPLAIN SELECT * FROM users WHERE age > 50;
执行结果如下:
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | users | NULL | ref | idx_age,idx_name_age | idx_name_age | 152 | | 4889 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
查询结果可以看到,使用了联合索引,查询效率得到了提升。
总结
MySQL 索引的底层实现其实并不复杂,只需要掌握 B+ 树的基本特点,就能够了解索引的使用方式。在实践中,我们可以根据不同的查询需求来选择合适的索引类型,从而提升查询效率。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:深入理解 MySQL 索引底层原理 - Python技术站