背景:需要根据工号或英文名带出中文名,但数据量过大,导致响应时间过长
查询工号为 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 | 查询对象表只有一行数据,这是最好的情况。 |