深入了解MySQL中的索引(用处、分类、匹配方式)

雨墨同学_7071

雨墨同学_7071

2021-09-26

6479人浏览

转载

本篇文章带大家深入了解mysql中的索引,介绍一下索引的优点、用处、分类、技术名词以及匹配方式,希望对大家有所帮助!

对于高级开发,我们经常要编写一些复杂的sql,那么防止写出低效sql,我们有必要了解一些索引的基础知识。通过这些基础知识我们可以写出更高效的sql。【相关推荐:mysql视频教程】

01 索引的优点

  • 大大减少服务器需要扫描的数据量,也就是IO量
  • 帮助服务器避免排序和临时表(尽量避免文件排序,而是使用索引排序)
  • 将随机IO变成顺序IO

02 索引的用处

  • 快速查找匹配where子句中的行
  • 如果可以在多个索引中选择,mysql通常会使用找到最少行的索引
  • 如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行
  • 当有表连接的时候,从其他表检索行数据
  • 查找特定索引列的min和max的值
  • 如果排序或者分组时可用索引的最左前缀完成的,则对表进行排序和分组
  • 在某些情况下,可以优化查询以检索数据值而无需查找数据行

03 索引的分类

数据库默认建立的索引是给唯一键建立的

  • 主键索引(唯一且非空)
  • 唯一索引(唯一可为空)
  • 普通索引(普通字段的索引)
  • 全文索引(一般是varchar,char,text类型建立的,但很少用)
  • 组合索引(多个字的建立的索引)

04 索引的技术名词

1. 回表

name字段是普通索引,从name列的B+树找到主键,再从主键的B+树找到最终的数据,这就是回表。(主键索引的叶子节点保存的是列的所有数据,但是普通所有的叶子结点保存的是对应的主键ID)

如图:一个use表中name建立的索引结构sql是select * from use where name='sun'首先会通过name这个非主键索引找到sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据,并返回,这个就是回表。

1.png

2. 覆盖索引

在非主键索引上可以查询到所需要的字段,不需要回表再次查询就叫覆盖索引。

如上图name索引,sql是 select id,name from user where name ="1" ,id的值在第一步非主键索引就已经有了,就不需要根据ID到主键索引中查询行数据了。

3. 最左匹配

组合索引中 先匹配左边,再继续向后匹配;比如user表中有name+age组成的联合索引,select * from user where name="纪先生" and age = 18 就符合最左匹配,可以用的索引。而select * from user where age = 18就不符合,用不到这个索引。

扩展;

如果是下面两个sql怎么建立索引

select * from user where name="纪先生" and age = 18;
select * from user where age = 18;

由于最左匹配原则:只需要建立一个组合索引age+name即可

如果是下面三个sql呢

select * from user where name="纪先生" and age = 18;
select * from user where name= "纪先生";

建立name+age和age索引,或者建立age+name和name索引,看着两个都可以。

其实name+age和age更好,因为索引也是需要持久化存储的,占用磁盘空间,读取的时候也是占用内存的,name+age和age+name这两个占用是一样的,但是name和age单独比较,肯定age占用空间更少,name更长(索引越大,IO次数可能更多)

注意!注意!注意!:

在看很多文章的时候,经常看到一些对于最左匹配错误的举例:

如果索引是name+age的组合索引,sql是select * from user where age = 18 and name="纪先生"很多人认为这种是不能走索引,实际上可以的。mysql的优化器会优化调整顺序的,调整成 name="纪先生" and age = 18

4. 索引下推

组合索引中尽量利用索引信息,来尽可能的减少回表的次数

案例:还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据,然后回表根据ID查询对于的数据行,之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID,然后回表根据ID查询对应行数据,明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库,可以直接下载通过source导入到我们自己的数据库

官网地址:dev.mysql.com/doc/index-o…

2.png

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库,表和数据的创建脚本。

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况,执行计划以前有文章详细讲过,具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配,例如使用数据库sakila中的staff表

新建一个三个字段的联合索引:

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer' and username='Mike'复制代码

3.png

其中的ref是三个const, 用到三个字段,能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

4.png

ref只出现2个const,比上面全值匹配少一个,就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分,像like属性

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

5.png

type=range ,是个范围查询,可以匹配一个字段的一部分,而不需要全值匹配

如果有模糊匹配的字段不要放在索引的最前面,否则有索引也不能使用,如下

6.png

4. 匹配一个范围值

可以查找某一个范围的数据

mysql> explain select * from staff where first_name > 'Mike';

7.png

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

8.png

6. 只访问索引的查询

查询的时候只需要访问索引,不需要访问数据行,其实就是索引覆盖

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

9.png

extra=Using index 说明是使用了索引覆盖,不需要再次回表查询。

其实一张表中有索引并不总是最好的。总的来说,只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时,索引才是有效的。对应很小的表,大部分情况下没有索引,全表扫描更高效;对应中大型表,索引时非常有效的;但是对于超大的表,索引的建立和使用代价也就非常高,一般需要单独处理特大型的表,例如分区,分库,分表等。

更多编程相关知识,请访问:编程视频!!

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

0

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

0

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

0

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

0

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

0

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

0

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

160

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

120

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

100

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程