MySQL — 索引
1、索引简介
索引(Index)是高效获取数据的数据结构,就像书的目录,提高检索数据的效率。
优点:提高数据检索效率,降低数据库的 IO 成本;通过索引列对数据进行排序,降低数据排序的成本,降低 CPU 的消耗。
缺点:索引列会占用空间;索引提高查询效率的同时也降低了更新表的速度,如对表进行增删改时效率降低。
2、索引结构
MySQL 的索引结构主要包含以下几种:
索引 | 描述 |
---|---|
B+Tree | 最常见的索引类型,大部分存储引擎都支持 B+ 树索引 |
Hash | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-Tree | 空间索引时 MyISAM 引擎的一个特殊索引类型,主要用于地理空间数据类型,较少使用 |
Full-Text | 是一种通过建立倒排序索引,快速匹配文档的方式,类似于 ES、Solr |
MySQL 的索引是在存储引擎层实现的,不同的存储引擎对于索引结构的支持:
索引 | InnoDB | MyISAM | Memory |
---|---|---|---|
B+Tree | 支持 | 支持 | 支持 |
Hash | × | × | 支持 |
R-Tree | × | 支持 | × |
Full-Text | 5.6版本之后支持 | 支持 | × |
-
二叉树
Binary tree,二叉树特点是每个节点最多只能有两棵子树,且有左右之分。如果选择二叉树作为索引结构,会存在以下缺点:顺序插入时,会形成一个链表,查询性能大大降低;大数据量情况下,层级较深,检索速度慢。 -
B树
B-Tree,B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。以一颗最大度数(max-degree,一个节点的子节点个数)为5阶的B树为例,那这个B树每个节点最多存储4个key,5个指针。一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂。在B树中,非叶子节点和叶子节点都会存放数据。 -
B+树
相对于B树的区别:所有的数据都出现在叶子节点;叶子节点形成一个单向链表;非叶子节点仅起到索引数据作用。MySQL 索引数据结构在原B+树上进行了优化,增加了一个相邻叶子节点的链表指针,形成带有顺序指针的B+树,提高区间访问性能,利于排序。 -
Hash
哈希索引就是采用一定的hash算法,将键值换算成新的 hash 值,映射到对应的槽位上,然后存储在 hash 表中。如果两个或多个键值映射到一个相同的槽位上,他们就产生了 hash 冲突/碰撞,可以通过链表来解决。Hash 索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,...),无法利用索引完成排序操作,查询效率高,通常不存在 hash 冲突的情况下只需要一次检索就可以了,效率通常要高于B+树索引。
3、索引分类
MySQL 数据库中索引的具体类型主要分为以下几类:
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 表中主键创建的索引 | 默认自动创建,只有能一个 | primary |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | unique |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | fulltext |
而在 InnoDB 存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引(ClusteredIndex) | 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 | 必须有,且只有一个 |
二级索引(SecondaryIndex) | 将数据与索引分开存储,索引结构的叶子节点关联的是该字段值对应的主键值 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引;
- 如果不存在主键,将使用第一个唯一索引作为聚集索引;
- 如果表没有主键,或没有合适的唯一索引,则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
4、索引语法
4.1、查看索引
查看数据库所有索引
select * from mysql.innodb_index_stats a where a.database_name = '库名';
方式一:查看表中所有索引
select * from mysql.innodb_index_stats a where a.database_name = '库名' and a.table_name = '表名';
方式二:查看表中所有索引
show index from 表名;
4.2、创建索引
方式一:建表时创建
create table 表名( 字段名 类型[(长度)] [约束条件] [comment '字段说明'], [unique|fulltext] index 索引名(字段名) );
方式二:建表后创建
create [unique|fulltext] index 索引名 on 表名 (字段名, ...);
方式三:修改表结构
alter table 表名 add index 索引名(字段名, ...);
4.3、删除索引
方式一:直接删除
drop index 索引名 on table;
方式二:修改表结构
alter table 表名 drop index 索引名;
5、性能分析
5.1、执行频次
MySQL 客户端连接成功后,通过如下指令可以查看当前数据库 insert、delete、update、select 的访问频次。
#查询全局数据:global #查看当前会话:session show global status like 'Com_______'; #七个下划线 #执行命令 mysql> show global status like 'Com_______'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_binlog | 0 | | Com_commit | 0 | | Com_delete | 2 | #删除次数 | Com_import | 0 | | Com_insert | 5 | #插入次数 | Com_repair | 0 | | Com_revoke | 0 | | Com_select | 10 | #查询次数 | Com_signal | 0 | | Com_update | 2 | #更新次数 | Com_xa_end | 0 | +---------------+-------+
5.2、慢日志查询
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。
mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+
配置文件:MySQL Server 8.0\my.ini
#慢日志查询开关 slow-query-log=1 #设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志 long_query_time=10
暂略。
5.3、profile
show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费在哪里。
查看当前 MySQL 是否支持 profile 操作:
mysql> select @@have_profiling; +------------------+ | @@have_profiling | +------------------+ | YES | +------------------+
profile 是默认关闭的,查看 profile 是否开启:
mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | #0表示未开启 +-------------+
通过 set 语句在 session / global 级别开启 profiling:
mysql> set profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | #1表示开启 +-------------+
此时执行一系列的业务SQL操作:
select count(*) from student; select * from student; select * from student where name = '张三';
通过以下命令查看指令的执行耗时:
#查看每一条SQL的耗时基本情况 show profiles; #查看指定 query_id 的SQL语句各个阶段的耗时情况 show profile for query query_id; #查看指定 query_id 的SQL语句CPU的使用情况 show profile cpu for query query_id;
#查看每一条SQL的耗时基本情况 mysql> show profiles; +----------+------------+-------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------+ | 1 | 0.00012400 | select @@profiling | | 2 | 0.00010975 | select * from student | | 3 | 0.00017200 | SELECT DATABASE() | | 4 | 0.00020550 | select count(*) from student | | 5 | 0.09767125 | select * from student | | 6 | 0.00056350 | select * from student where name = '张三' | +----------+------------+-------------------------------------------+ #查看指定 query_id 的SQL语句各个阶段的耗时情况 mysql> show profile for query 4; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000042 | | Executing hook on transaction | 0.000005 | | starting | 0.000006 | | checking permissions | 0.000004 | | Opening tables | 0.000234 | | init | 0.000004 | | System lock | 0.000006 | | optimizing | 0.000003 | | statistics | 0.000007 | | preparing | 0.000011 | | executing | 0.097268 | | end | 0.000010 | | query end | 0.000003 | | waiting for handler commit | 0.000009 | | closing tables | 0.000007 | | freeing items | 0.000047 | | cleaning up | 0.000008 | +--------------------------------+----------+
5.4、explain
explain 或 desc 命令获取 MySQL 如何执行 select 语句的信息,包括在 select 语句执行过程中表如何连接和连接的顺序。
[explain | desc] select * from 表名 [where 条件列表];
mysql> desc 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 | 3 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
explain 执行计划中各个字段的含义:
字段 | 含义 |
---|---|
id | 查询序列号,表示查询中执行 select 子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行) |
select_type | 查询类型,常见的取值有:simple(简单表,即不使用表连接或者子查询)、primary(主查询,即外层的查询)、union(union 中的第二个或者后面的查询语句)、subquery(select / where 之后包含子查询)等 |
table | 表名 |
partitions | 涉及到的分区 |
type | 连接类型,性能由好到差的顺序为:null、system、const、eq_ref、ref、range、 index、all |
possible_keys | 可能应用在这张表上的索引,一个或多个 |
key | 实际使用的索引,如果为 null,则没有使用索引 |
key_len | 索引中使用的字节数, 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下, 长度越短越好 |
ref | 引用到的上一个表的列 |
rows | MySQL 认为必须要执行查询的行数,在 InnoDB 引擎的表中,是一个估计值,可能并不总是准确的 |
filtered | 返回结果的行数占需读取行数的百分比, 值越大越好 |
Extra | 额外的信息说明 |
6、索引使用
6.1、单列联合
单列索引:一个索引只包含单个列。
联合索引:一个索引包含了多个列。
在 and 连接的两个字段上都有单列索引,但 mysql 最终只会选择一个索引,只能走一个字段的索引,此时容易造成回表查询。
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引。
6.2、最左前缀
- 索引列全部存在,索引生效;
- 索引列部分存在:
- 不存在最左列索引,索引失效;
- 存在最左列索引:
- 最左列索引在第一个条件,后续条件按索引列顺序编写则该部分索引全部生效,若跳过某个索引列则后续索引列失效;
- 最左列索引不在第一个条件,只有最左列索引生效。
6.3、范围查询
联合索引中,出现范围查询大于或小于的,范围查询右侧的列索引失效。
6.4、索引失效
- 索引列上进行运算操作;
- 字符串类型字段使用时不加引号;
- 头部模糊匹配;
- or 前的条件有索引,or 后的条件没有索引;
- MySQL 评估使用索引比全表更慢(数据量大于等于表中一半)。
6.5、SQL 提示
如果列 A 既是单列索引,又是联合索引的最左列。此时根据 A 字段条件进行查询,MySQL 优化器会自动选择一个索引来使用。
假如在某些场景下,我想告诉数据库,如果 A 字段有多个索引,则需要按照我指定的索引来进行查询,此时需要 SQL 提示。
SQL 提示是优化数据库的一个重要手段,就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
- use index:建议 MySQL 使用哪一个索引完成此次查询,MySQL 内部还会再次进行评估。
- ignore index:忽略指定索引。
- force index:强制使用指定索引。
语法:
select * from 表名 [use|ignore|force index(索引名) where 条件列表];
6.6、覆盖索引
尽量使用覆盖索引(查询使用了索引,且需要返回的列在该索引中能全部找到);也就是只返回必要的列,最好不要使用 select * 语句。
只返回必要的行,使用 limit 语句来限制返回的数据。
Extra | 含义 |
---|---|
Using where; Using Index | 查询使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据 |
Using index condition | 查找使用了索引,但是需要回表查询数据 |
Null | 需要回表查询数据 |
6.7、前缀索引
当字段类型为字符串(varchar,text,longtext 等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
语法:
create index 索引名 on 表名(字段名(n)); #n:前缀长度
前缀长度:
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值。
索引选择性越高则查询效率越高, 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
#建立前缀索引,降低前缀索引的体积,指定前N个字符 select count(distinct substring(字段名, 1, n)) / count(*) from 表名; #值=1最好
7、索引设计
- 针对于数据量较大,且查询比较频繁的表建立索引;
- 针对于常作为查询条件(where)、排序(order by)、分组(group by)之后的字段建立索引;
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高;
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引;
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率;
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率;
- 如果索引列不能存储 null 值,在建表时使用 not null 约束它。当优化器知道每列是否包含 null 值时,它可以更好的确定哪个索引最有效地用于查询。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤