MySQL中的索引之分类，原理，作用，优缺点和执行计划

Wang's Blog

墨衍会员 · AI 创作全网分发码龄12年

关注

原创 2023-12-30 12:24:15 1.1k 阅读 AI 写同款· GEO 优化 ›

mysql索引详解 MySQL的存储引擎架构将查询处理与数据的存储/提取相分离阅读详情

索引

索引的作用：加速查找
- 例如: 300w条数据的表中查询，无索引需要700s, 利用索引可能只需要1s
- 用索引的时机是，数据量巨大，并且搜索快速
索引为什么能实现加速查找
- 基于索引的内部存储结构
- 索引底层基于 B+Tree 的数据结构存储的
- 在创建一张表的时候，将数据按照先后顺序放在一个文件里
- 如果你为表里的数据创建索引后，会将数据生成额外的数据结构
- 在这个数据结构中，将索引的这一列按照另外的规则进行存储，即 B+Tree 的结构
只要给一个字段添加索引，就会为这个字段新增一个 B+Tree 的结构
背后的缺点是额外维护数据结构，并且新增或删除数据时，速度比之前要慢
只有查找会变快，新增，修改，删除都会变慢
MySQL中的索引都是基于 B+Tree 实现的
在MySQL中，如果要创建一张表，可以指定不同的引擎
- myisam 引擎，用的是非聚簇索引 (数据和索引结构分开存储)
  - 在这个表里创建索引，称为非聚簇索引
  - 表是表，索引结构式索引结构，拆开放的
- innodb 引擎，用的是 聚簇索引 (数据和主键索引结构存在一起)
  - 创建表的时候，实际上是没有表的，而是将主键通过树形结构存储起来
  - 没有这个表的，节点不仅存储主键，而且把每行存储的信息存储在上面
基于两种引擎创建的表，底层都用 B+Tree 来存储，但是存储中也是不太一样的
有了索引结构的查询效率，比表中逐行查询的速度要快很多，且数据量越大越明显
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

mysiam引擎

create table 表名(
  id int not null auto_increment primary key,
  name varchar(32) not null,
  age int
) engine=myisam default charset=utf8;

在索引中的节点存储了表中数据行的内存地址, 这样就可以直接找到当前行的数据

聚簇引擎

create table 表名(
  id int not null auto_increment primary key,
  name varchar(32) not null,
  age int
) engine=innodb default charset=utf8;

根据索引结构，通过主键拿到当前行的所有数据
如果除了主键索引，还需要创建如 name 的索引
这时候，就会创建一个辅助索引，生成另外一套数据结构
按照name来存放，会存储主键id, 再根据主键根据主键索引文件中查找

两种索引的文件对比

开发中，一般使用 innodb 引擎，支持事务，行级锁，外键等特点
在mysql5.5之后，所有默认引擎也是 innodb
可以找到 mysql的安装目录，比如：/usr/local/mysql/data/userdb
常见不同引擎的表，生成的文件也不一样
比如 big 这张表，是基于 innodb 引擎的
- big.frm 表结构
- big.ibd 数据和索引结构
对于表 t2 是 myisam 引擎的话
- t2.frm 表结构
- t2.MYD 数据
- t2.MYI 索引结构
- 它的底层帮我们创建3个文件
我们后续用的 innodb ，都是聚簇索引

索引的优缺点

优点: 查找速度快，约束 (主键, 唯一, 联合唯一)
缺点: 插入、删除、更新速度比较慢, 因为每次操作都需要调整整个B+Tree的数据结构关系
所以，在表中不要无节制的创建索引，不使用索引反而会适得其反

查询要命中索引

比如有一张300w数据量的用户表

表结构示例

create table `users` (
  `id` int(11) not null auto_increment,
  `name` varchar(32) default null,
  `email` varchar(64) default null,
  `password` varchar(64) default null,
  `age` int(11) default null,
  primary key (`id`),         --- 主键索引
  unique key `big_unique_email` (`email`),         --- 唯一索引
  index `ix_name_pwd` (`name`, `password`),         --- 联合索引
) engine=InnoDB default charset=utf8;

以上表结构中有三个索引，2个 key, 1个 index(普通)
一般，基于索引列搜索都可命中索引，加速查找
注意，联合索引，查询其中之一也是快的

查询示例

select * from big where id=5;
select * from big where id>5;
select * from big where email='xxxx@qq.com';
select * from big where name='xxx';
select * from big where name='sss' and password='ssdd';

命中索引的场景

以下几种情况是常用的是否命中索引的场景

1 ）类型不一致场景

select * from users where name = 123;     -- 不会命中索引
select * from users where email = 123;    -- 不会命中索引

-- 下面用主键
select * from users where id='123';       -- 会命中索引

2 ）使用不等于

select * from users where name != 'xxxx';            -- 不会命中索引
select * from users where email != 'xxxx@qq.com';    -- 不会命中索引

-- 主键
select * from users where id != 123;                 -- 不会命中索引

3 ）使用 or

select * from users where id = 123 or password = 'x';     -- 不会命中 这里后面联合索引中只用了一个
select * from users where name = 'xx' or password = 'y';    -- 不会命中 用 or 将联合索引拆成了两个

-- 下面会命中
select * from users where id = 10 or password='xx' and name='yy';  -- 命中 这里 or 前后都是索引

4 ）使用排序

根据索引排序时，选择的映射列不是索引，则不走索引

select * from users order by name asc;       -- 未命中
select * from users order by name desc;      -- 未命中

-- 主键会命中
select * from users order by id desc;        -- 会命中

5 ）like 模糊匹配时

通配符在最后面可以命中

select * from users where name like '%xxx'       -- 不会命中
select * from users where name like '_xxx'       -- 不会命中
select * from users where name like 'xx%xx'      -- 不会命中

-- 通配符在最后，会命中
select * from users where name like "xxxx%"      -- 命中
select * from users where name like "xxxx_"      -- 命中

6 ）使用函数

select * from users where reverse(name) = 'xxxx'; --- 不会命中

-- 特别的
select * from users where name = reverse('abc')  -- 会命中

7 ）联合索引

如果是联合索引，最遵循最左前缀原则
如果联合索引为 (name, password)
- name and passsword 命中
- name 命中
- password 不会命中
- name or password 不会命中
最左边用可以命中，用or连接则不能

关于执行计划 explain

mysql中提供了执行计划, 用于预判sql的执行效率
不能准确预判，只作为参考
语法：explain sql语句

1 ）使用

explain select * from users
这会输出当前sql的分析表格

2 ）解析 type

基于输出表格字段中的 type 来看，它是一个重要的性能指标
- 其值的性能依次排序为：
- all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
详解如下
- ALL，全表扫描，数据从头到尾找一遍，(一般没有命中索引，都会执行全部扫描)
  - select * from users 全部扫描
  - select * from users limit 1; 这里特别，遇到 limit 结束后不再扫描
- INDEX，全索引扫描，对索引从头到尾扫描一遍
  - explain select id from users;
  - explain select name from users;
- RANGE, 对索引列进行范围查找
  - explain select * from users where id > 10;
  - explain select * from users where id in (1,2,3);
- INDEX_MERGE 合并索引，即使用了多个单列索引
  - explain select * from users where id = 10 or name='xxx'
- REF, 根据索引直接去查找 (非键)
  - select * from users where name = 'xxx'
- EQ_REF, 连表操作时常见, 也是根据索引查询
  - explain select article.title, users.id from article left join users on user.id = article.uid
- CONST, 常量，表最多有一个匹配行，因为仅有一行，在这行的列值可被优化器剩余部分认为是常数，const表很快
  - explain select * from users where id = 123; 这里是主键
  - explain select * from users email = 'xxxx@qq.com 唯一索引
- SYSTEM, 系统，表仅有一行(=系统表) 这里是 const连接类型的一个特例
  - explain select * from (select * from users where id=1 limit 1) as A;
综合以上，一般来说，性能在 RANGE 及其以上，性能算是 OK的
当然，这不是最终结果，只是初步的评价，和最终效率一定会有差异

3 ）其他字段

id 执行顺序
select_type 查询类型
- SIMPLE 简单查询
- PRIMARY 最外层查询
- SUBQUERY 映射为子查询
- DERIVED 子查询
- UNION 联合
- UNION RESULT 使用联合的结果
- …
table 正在访问的表名
partitions, 涉及的分区，不常用，mysql将数据划分到不同的 idb文件中，箱单与数据的拆分
- 分区是指，一个特别大的文件拆分成多个小文件
possible_keys, 查询涉及到的字段上若存在索引，则该索引将被列出
- 即：可能使用的索引
key, 在查询中实际使用的索引，若没有使用索引，显示为 null
- 比如，有索引，但未命中，则 possible_keys显示，key则显示为 null
key_len, 表示索引字段最大可能的长度
- 类型字节长度 + 变长2 + 可空1
- 例如，key_ken = 195, 类型 varchar(64)
- 195 = 64 * 3 + 2 + 1
ref, 连表时显示的关联信息
- 例如，A和B连表，显示连表的字段信息
rows, 估计读取的数据行数 (只是预估值)
filtered, 返回结果的行栈需要读到行的百分比
- explain select * from users where id = 1; 100, 这里只读了一行，返回结果也是1行
- explain select * from big where password = 'xxx' 10, 读取了10行，返回了1行，注意，这里 xxx的 password在第10行
extra, 该列包含mysql解决查询的详细信息
- Using index 表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index的访问类型弄混了
- Using where
  - 表示mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里设计索引中的列
  - 当(并且如果)它读取索引时，就能被存储引擎检验
  - 因此，不是所有带 where 子句的查询都会显示 Using where
  - 有时，Using where的出现就是一个暗示，查询可受益于不同的索引
- Using temporary
  - mysql在对查询结果排序时会使用一个临时表
- Using filesort
  - mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行
  - mysql有两种文件排序算法，这两种排序方式都可以在内存或磁盘上完成
  - explain 不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成
- Range checked foreachrecord(index map: N)
  - 这意味着没有好用的索引，新的索引将在连接的每一行上重新估算
  - N是显示在possible_keys列中索引的位图，并且是冗余的