背景

需要根据工号或英文名带出中文名，但数据量过大，导致响应时间过长

查询工号为 x20230506 或者英文名是 codeporter 的用户信息

select * from user_test where login="x20230506" or login_id="codeporter"

查看MySQL版本：

select version();
version()
8.0.18

创建普通索引

alter table  user_test add index user_idx_login(login)
alter table  user_test add index user_idx_login_id(login_id)

使用EXPLAIN分析后面SELECT语句的执行情况，并且能够分析出所查询表的一些特征。 

explain select * from user_test where login="x20230506" or login_id="codeporter"

查询条件中有or，即使两个条件都带索引也会失效  

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE user_test NULL ALL user_idx_login,user_idx_login_id NULL NULL NULL 1 100 Using where

解决办法：优化的方法是改成 UNION或者UNION ALL，分成多个 sql，走各自的索引。

UNION和UNION ALL的区别

1.对重复结果的处理：UNION会去掉重复记录，UNION ALL不会

2.对排序的处理：UNION会排序，UNION ALL只是简单地将两个结果集合并

3.效率方面的区别：因为UNION 会做去重和排序处理，因此效率比UNION ALL慢很多

explain select * from ddc.user_test where login="x20230506" union all select * from ddc.user_test where login_id="codeporter";
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY user_test NULL ref user_idx_login user_idx_login 202 const 1 100 NULL
2 UNION user_test NULL ref user_idx_login_id user_idx_login_id 203 const 1 100 NULL

explain关注点

重点要关注如下几列：

列名 备注
type 本次查询表联接类型，从这里可以看到本次查询大概的效率。
key 最终选择的索引，如果没有索引的话，本次查询效率通常很差。
key_len 本次查询用于结果过滤的索引实际长度。
rows 预计需要扫描的记录数，预计需要扫描的记录数越小越好。
Extra 额外附加信息，主要确认是否出现 Using filesort、Using temporary 这两种情况。

 其中，type包含以下几种结果，从上之下依次是最差到最好：

类型 备注
ALL 执行full table scan，这是最差的一种方式。
index 执行full index scan，并且可以通过索引完成结果扫描并且直接从索引中取的想要的结果数据，也就是可以避免回表，比ALL略好，因为索引文件通常比全部数据要来的小。
range 利用索引进行范围查询，比index略好。
index_subquery 子查询中可以用到索引。
unique_subquery 子查询中可以用到唯一索引，效率比 index_subquery 更高些。
index_merge 可以利用index merge特性用到多个索引，提高查询效率。
ref_or_null 表连接类型是ref，但进行扫描的索引列中可能包含NULL值。
fulltext 全文检索。
ref 基于索引的等值查询，或者表间等值连接。
eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描，比ref略好。
const 基于主键或唯一索引唯一值查询，最多返回一条结果，比eq_ref略好。
system 查询对象表只有一行数据，这是最好的情况。

MySQL中的explain解析_杨 戬的博客-CSDN博客

索引失效的情况及解决(超详细)_zyy_demon的博客-CSDN博客

# mysql # 性能优化
Logo
2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

cover

软考中级-软件设计师 UML图详解（ 类图，对象图，用例图，序列图，通信图，状态图，活动图，构件图，部署图）

avatar 2048 AI社区
cover

机器学习 决策树-分类

avatar 2048 AI社区
cover

【SD教程】超详细AI绘画提示词语法讲解！

avatar 2048 AI社区