当前位置：首页 > article >正文

MySQL - 索引【index】

article 2025/3/26 6:03:56

概述

索引：帮助MySQI高效获取数据的数据结构(有序)。

数据库系统除维护数据外，还维护满足特定查找算法的数据结构，即索引。

演示

表结构及数据如下：

假设执行SQL语句：select * from user where age = 45;

无索引情况：要从第一行扫描到最后一行，即全表扫描，性能很低

有索引情况：假设对该表建立了索引，索引结构是二叉树，那就意味，会对age字段建立一个二叉树的索引结构。

此时再进行查询时，扫描三次就能找到数据，极大提高了查询效率。

索引的优点

提高数据检索的效率，降低了数据库的IO成本

通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

索引的缺点（几乎可以忽略）

索引列也要占用空间

索引提高了表的查询效率，但降低更新速率，即对表进行增、删、改时，效率降低。

索引结构

MySQL的索引是在存储引擎层实现的，不同存储引擎有不同的索引结构。

MySQL支持的全部索引结构

索引结构

描述

B+Tree索引

最常见，大部分引擎都支持 B+ 树索引

Hash索引

用哈希表实现的，只能精确查询，不能范围查询

R-tree (空间索引）

MyISAM引擎的一种特殊索引，主要用于地理空间数据类型，使用较少

Full-text (全文索引)

一种通过建立倒排索引，快速匹配文档的方式。

索引结构	描述
B+Tree索引	最常见，大部分引擎都支持 B+ 树索引
Hash索引	用哈希表实现的，只能精确查询，不能范围查询
R-tree (空间索引）	MyISAM引擎的一种特殊索引，主要用于地理空间数据类型，使用较少
Full-text (全文索引)	一种通过建立倒排索引，快速匹配文档的方式。

不同存储引擎支持的索引

索引

InnoDB

MyISAM

Memory

B+tree索引

支持

Hash 索引

不支持
不支持
支持

R-tree 索引

不支持
支持不支持

Full-text

5.6版本后支持
支持不支持

注：我们平常说的索引，若无特别指明，都指 B+ 树索引

索引	InnoDB	MyISAM	Memory
B+tree索引	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

二叉树、红黑树为什么不用作索引结构

假设 MySQL 的索引结构采用 二叉树 或 红黑树 的数据结构

理想情况如图1

但如果主键是顺序插入的，则会形成一个单向链表，查询性能大大降低（图 2）

那选择红黑树呢（图 3）？
红黑树是自平衡二叉树，即使顺序插入数据，最终也会形成一棵平衡的二叉树。但红黑树本质上仍是二叉树，大数据量情况下，层级较深，检索慢。

所以，MySQL的索引结构中并没有二叉树或红黑树，而是默认 B+ 树

B Tree （多路平衡查找树）

以一棵最大度数为5(5阶)的b-tree为例，则该 B树每个节点最多存储4个key，5 个指针：

5阶 B树的特点：

每个节点最多存储4个key，5个指针。
每个节点存储的key达到 5 就会裂变，中间元素向上分裂。
在B树中，每个节点都存放数据。

模拟网站：B-Tree Visualization

B+ Tree

以4阶 B+ 树为例：

5阶 B+ 树的特点：

每个节点最多存储4个key，5个指针。
每个节点存储的key达到 5 就会裂变，中间元素向上分裂并保留。
在B+ 树中，仅叶节点都存放数据。非叶子节点仅仅起到索引数据作用

叶子节点形成一个单向链表。

模拟网站：B+ Tree Visualization

MySQL优化后的 B+Tree

MySQL索引对经典的B+Tree进行优化。在原B+Tree基础上，增加一个指向相邻叶子节点的链表指针，形成了带有顺序指针的 B+Tree，提高区间访问的性能，利于排序。

Hash

哈希索引采用hash算法，将键值换算成hash值，映射到对应的槽位，然后存储在hash表中。
如果多个键值，映射到同一槽位，即产生hash冲突（也称hash碰撞），可以通过链表解决。

hash索引的特点：

不能完成排序操作
通常只需一次检索即可，效率通常高于B+tree
MySQL中，支持hash索引的是Memory存储引擎，其他引擎不支持。但 InnoDB 有自适应hash功能，InnoDB 根据B+Tree 索引在指定条件下自动构建hash索引。

面试题：为什么InnoDB存储引擎选择使用B+tree索引结构?

相对于二叉树，B+tree的层级更少，检索效率高；
B-tree的每个节点都保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，且只能有一个	primary
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	fulltext

而在InnoDB存储引擎中，根据索引的存储形式，又分2种：

分类	含义	特点
聚集索引	将数据与索引存储在一起，索引结构的叶子节点保存了行数据	必须有，且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点保存的是对应的主键	可以有多个

聚集索引选取规则：

如果有主键，主键索引就是聚集索引。
如果没有主键，将第一个唯一索引作为聚集索引。
如果表没有主键、唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

聚集索引的叶子节点下保存的是这一行的数据。

二级索引的叶子节点下保的是对应的主键值。

当执行SQL语句时，具体的过程如下：

由于根据name字段查询，所以先根据name= 'Arm'，到name字段的二级索引中查找。注意：在二级索引中只能得到Arm对应的主键值10
由于查询返回的数据是*，所以还要根据主键值10，到聚集索引中查找，最终找到10对应的行row
最终拿到这一行的数据，直接返回即可

上面的过程又称为回表查询

回表查询：先到二级索引中查找，找到主键值，然后到聚集索引中根据主键值获取数据

思考题：以下SQL语句，哪个执行效率高

A select * from user where id = 10 ;

B select * from user where name = 'Arm' ;

备注: id为主键，name字段创建了索引；

解答：A 语句的执行性能要高于B 语句。

因为A语句直接走聚集索引，直接返回数据。而B语句需要先查询name字段的二级索引，然

后再查询聚集索引，也就是需要进行回表查询。

思考题：InnoDB 主键索引的B+树的高度是多少？

索引的语法

-- 1. 创建索引：
-- 创建一个普通索引
-- 语法：create index 索引名 on 表名(字段名);
-- 创建一个唯一索引
-- 语法：create unique index 索引名 on 表名(字段名);
-- 创建一个主键索引
-- 语法：create primary key index 索引名 on 表名(字段名);
-- 创建一个全文索引
-- 语法：create fulltext index 索引名 on 表名(字段名);

-- 2. 查看索引：
-- 语法：show index from 表名;

-- 3. 删除索引：
-- 语法：drop index 索引名 on 表名;

接下来演示一个案例：首先创建表，并插入数据

-- 1. 创建一个系统用户表tb_user，包含以下字段：
-- primary key：主键
-- auto_increment：自增主键
-- not null：非空
-- unsigned：无符号
create table tb_user(
                        id int primary key auto_increment comment '主键',
                        name varchar(50) not null comment '用户名',
                        phone varchar(11) not null comment '手机号',
                        email varchar(100) comment '邮箱',
                        profession varchar(11) comment '专业',
                        age tinyint unsigned comment '年龄',
                        gender char(1) comment '性别 , 1: 男, 2: 女',
                        status char(1) comment '状态',
                        createtime datetime comment '创建时间'
) comment '系统用户表';

-- 2. 插入数据
insert into tb_user (name, phone, email, profession, age, gender, status,createtime) values
            ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1','6', '2001-02-02 00:00:00'),
            ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,'1', '0', '2001-03-05 00:00:00'),
            ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1','2', '2002-03-02 00:00:00'),
            ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,'1', '0', '2001-07-02 00:00:00'),
            ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,'2', '1', '2001-04-22 00:00:00'),
            ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2','0', '2001-02-07 00:00:00'),
            ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24,'2', '0', '2001-02-08 00:00:00'),
            ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,'1', '5', '2001-05-23 00:00:00'),
            ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,'1', '0', '2001-09-18 00:00:00'),
            ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00'),
            ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00'),
            ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1','0', '2001-05-11 00:00:00'),
            ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',44, '1', '1', '2001-04-09 00:00:00'),
            ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,'2', '3', '2001-02-12 00:00:00'),
            ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,'2', '0', '2001-01-30 00:00:00'),
            ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,'2', '0', '2000-05-03 00:00:00'),
            ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1','1', '2001-08-08 00:00:00'),
            ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',30, '1', '0', '2007-03-12 00:00:00'),
            ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51,'2', '0', '2001-08-15 00:00:00'),
            ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52,'1', '2', '2000-04-12 00:00:00'),
            ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19,'1', '3', '2002-07-18 00:00:00'),
            ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20,'1', '0', '2002-03-10 00:00:00'),
            ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29,'1', '4', '2003-05-26 00:00:00');

然后我们先查看表的索引，然后再根据需求创建索引

-- 3. 查看表tb_user的索引
-- 可以看到，表tb_user有一个主键索引，主键索引的名称为PRIMARY，主键索引的字段为id。
show index from tb_user;

-- 4. 创建索引
-- 需求如下：
-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
-- phone手机号字段的值，是非空，且唯一的，为该字段创建唯一引。
-- 为profession、age、status创建联合素引。
-- 为email建立合适的索引来提升查询效率
create index idx_name on tb_user(name);
create unique index idx_phone on tb_user(phone);
create index idx_profession_age_status on tb_user(profession,age,status);
create index idx_email on tb_user(email);

SQL性能分析

SQL执行频率

-- 查看SQL的执行频率：show [session|global] status like 'Com%';
-- session: 查看当前会话的SQL执行频率
-- global：查看全局的SQL执行频率
-- like: 模糊查询
-- %: 通配符，匹配任意字符
-- _: 通配符，匹配一个字符

show global status like 'Com%';
show global status like 'Com_______';
-- 可以看到，Com_select是查询语句的执行频率最高的语句。
-- Com_delete：删除次数
-- Com_insert：插入次数
-- Com_update：更新次数
-- Com_select：查询次数

通过上述指令，就能知道当前数据库是以查询为主，还是以增删改为主，为数据库优化提供参考
如果以增删改为主，我们可以不优化索引。如果以查询为主，就要对数据库的索引进行优化

那假如数据库是以查询为主，我们又该对哪些查询语句进行优化？ ==> 借助于慢查询日志。

慢查询日志

索引设计原则

对数据量较大，且查询较频繁的表建立索引。

针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

尽量用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间避免回表，提高查询效率。

要控制索引的数量，索引不是越多越好，索引越多，维护索引结构的代价就越大，会影响增删改的效率。

若索引列不能存储NULL值，请在创建表时用非空约束，当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询。

查看全文

原文地址:https://blog.csdn.net/qq_64518205/article/details/146439526
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.kler.cn/a/600569.html 如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！