MySQL索引详解（二）：实战篇

一身都是月-追逐

已于 2024-09-06 10:51:55 修改

阅读量932

点赞数 31

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-05-25 16:29:33 首次发布

本文链接：https://blog.csdn.net/prop_erty/article/details/139199419

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL索引详解（二）：实战篇

基础查询语句执行顺序解读
key与index
索引操作
索引分析
- explain说明
- type重要字段举例说明
联合索引

本系列文章分为两部分

原理篇: MySQL索引详解（一）：原理篇
实战篇

基础查询语句执行顺序解读

查询语句：

select 字段列表
from  表名列表
join 关联表
on 连接方式
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数

执行顺序：

from  表名列表
on 连接方式
join 关联表
where 条件列表
group by 分组字段列表
having 分组后条件列表
select 字段列表
order by 排序字段列表
limit 分页参数

当然还有如over 窗口函数, union连接结果等

key与index

有两种方式建立索引，key 与 index

key通常包括两层含义：一是约束，而是索引。其分为以下3类

primary key 主键：约束该列为主键，添加索引。
unique key 唯一约束：约束该列为数据必须唯一，不能重复，添加索引。
foreign key 外键：约束该列为外键，添加索引。

index关键词——用于建立索引。

unique index 表示建立唯一索引

单独使用key也行与index作用相同。

在该SQL建表语句中，id设置为主键。user_name 设置为唯一索引。device_id设置为外键，关联emp表中的主键id属性。(user_id, name, age)设置为联合索引，使用key也行。

create table user(  
    -- primary key 表示 为该字段添加主键索引  
    id int primary key comment '主键id,设为主键索引',  
    device_id int unsigned comment '设备id, 设为外键索引',  
    user_name varchar(20) unique key comment '用户名，设置为唯一索引',  
    user_id int,  
    name varchar(10) not null,  
    age int ,  
    -- 添加联合索引
    index idx_userId_name_age (user_id, name, age), -- 使用key关键词也行  
    foreign key(device_id) references emp(id) -- 设置外键， 参考字段必须是emp主键  
);

结果：可以看到生成的user表中，有6列数据，2个非外键，一个外键，4个索引。出了我们用index生成的联合索引外，其他key关键词生成的键也自动添加了索引。
请添加图片描述

索引操作

每个都有两种语句，直接操作索引， alter 修改表结构修改索引信息

创建索引

create index index_name on table_name (column1 [ASC|DESC], [ASC|DESC]);

alter table table_name add index index_name (column1 [ASC|DESC], [ASC|DESC]);

ASC和DESC可选，用于指定索引的排序顺序，默认以升序（ASC）排序。

删除索引

drop index index_name on table_name;

alter table table_name drop index index_name;

查看索引信息

show index from table_name\G; -- \G表示格式化输出信息

索引分析

explain说明

当我们想为一些常用的查询命令添加索引的时候，我们就需要进行分析，查看我们的索引是否添加正确，这些查询命令是否按照我们所设想的使用了该索引进行操作。查询命令的效率如何等，
通过这个命令，我们可以很好的进行分析，并采取相应措施进行优化。

用法： explain+查询语句。如下所示：
请添加图片描述

字段说明：
- id：标识符，显示了运行顺序。数字越大优先级越高，同等数字下上面先运行。
- select_type：显示了查询的类型。
- table：显示了操作的表格，有别名会显示别名。
- partitions：匹配的分区，没有分区会显示null
- type： 很重要，显示了性能好坏。性能从好到坏依次为：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL（全表查询）。
- key： 实际使用的索引。
- possible_key: 可能使用到的索引值。
- key_len：索引字段长度，如果是联合索引，会计算你使用的列数。
- ref：列与索引的比较。
- rows：重要，显示此次操作需要扫描的行数。
- filtered：百分比，剩余的记录数。
- extra: 很重要： 比较重要的值有， Using filesort（需要额外的排序操作，可以优化） Using index：使用了覆盖索引（不需要回表）Using temporary (使用了临时表，可以优化)， Using index condition：使用了索引，但需要回表查询。Using WHERE：使用了where 语句，并且结果不是一行。比较常见。

type重要字段举例说明

直接使用唯一性索引（包括主键）等值查询（结果只有一行）：type: const
在join操作中，使用主键或唯一非空索引的时候（一次匹配单行），可用于=号的比较操作： type: eq_ref
只使用联合索引的最左边前缀，或者不是唯一索引（一次可能匹配多行）进行等值操作时： type: ref
1. 当为emp的dept_id 创建普通索引后，同样的语句运行顺序发生改变，且对emp表格进行操作时，使用了普通索引，所以类型为ref。
2. 直接使用非唯一索引进行等值判定，也会出现ref
3. 综合上述两张表，可以发现join操作会进行两次操作，对其优化可以单独看每张表使用的索引。
fulltext 使用全文索引的时候
ref_or_null : ref中有判定null操作(为什么没有eq_ref_or_null，因为eq_ref使用的是唯一非空索引，没有空值。)
index merge：表示使用了索引合并优化
unique subquery: 见名知意，子查询使用了唯一索引，即结果唯一。
index subquery: 与上面类似，子查询使用了非唯一索引。
range：范围操作与常值比较的时候。除基础的不等式，还包含in， between，like的前缀匹配(%x不行)等
index：使用覆盖索引
全表扫描：没使用索引，或者获取全部数据。

联合索引

最左匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
举例，当使用user_id检索时，使用了联合索引，idx_userId_name_age，联合索引跟顺序一样，当使用name检索时，就没有使用联合索引了。![[Pasted image 20240525160151.png]]
当where判定值为索引一部分，搜索值使用了覆盖索引的时候也会显示使用了该联合索引，但当where判定值为非索引的时候，不会使用覆盖索引。