目录1 数据准备1.1 新建数据表1.2 新增100万条数据2 基础知识2.1 explain type2.2 explain Extra3 索引失效场景3.1 查询类型错误3.1.
CREATE TABLE `player` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`player_id` varchar(256) NOT NULL COMMENT '运动员编号',
`player_name` varchar(256) NOT NULL COMMENT '运动员名称',
`height` int(11) NOT NULL COMMENT '身高',
`weight` int(11) NOT NULL COMMENT '体重',
`type` varchar(256) DEFAULT '0' COMMENT '球员类型',
`game_perfORMance` text COMMENT '最近一场比赛表现',
PRIMARY KEY (`id`),
KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),
KEY `idx_type` (`type`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
以上数据表声明三个索引:
@SpringBootTest(classes = TestApplication.class)
@RunWith(springJUnit4ClassRunner.class)
public class PlayerServiceTest {
@Resource
private PlayerRepository playerRepository;
@Test
public void initBigData() {
for (int i = 0; i < 1000000; i++) {
PlayerEntity entity = new PlayerEntity();
entity.setPlayerId(UUID.randomUUID().toString());
entity.setPlayerName("球员_" + System.currentTimeMillis());
entity.setType("0");
entity.setWeight(150);
entity.setHeight(188);
entity.setGamePerformance("{\"runDistance\":8900.0,\"passSuccess\":80.12,\"scoreNum\":3}");
playerRepository.insert(entity);
}
}
}
执行计划中访问类型是重要分析指标:
Extra表示执行计划扩展信息:
本章节介绍索引失效十种场景:
explain select * from player where type = 0
数据表定义type
字段为varchar
类型,查询必须使用相同类型:
explain select * from player where height + 1 > 189
explain select * from player where height > 188
MySQL发现如果使用索引性能低于全表扫描则放弃使用索引。例如在表中100万条数据height
字段值全部是188
,所以执行如下语句时放弃使用索引:
explain select * from player where height > 187
调整查询条件值:
explain select * from player where height > 188
强制指定索引,这种方法不一定可以提升性能:
避免出现3.3章节失效问题此处修改一条数据:
update player set player_name = '测试球员' where id = 1
explain select * from player where player_name like '%测试'
explain select * from player where player_name like '%测试%'
explain select * from player where player_name like '测试%'
type
有索引,weight
无索引:
explain select * from player where type = '0' or weight = 150
weight
新增索引,uNIOn
拼装查询数据
explain
select * from player where type = '0'
union
select * from player where weight = 150
Using index condition
表示使用索引,但是需要回表查询
explain select * from player where player_name like '测试%'
覆盖索引含义是查询时索引列完全包含查询列,查询过程无须回表(需要在同一棵索引树)性能得到提升。Using Index; Using where
表示使用覆盖索引并且用where
过滤查询结果:
explain select id,player_name,height,weight from player where player_name like '测试%'
联合索引idx_name_height_weight
完整使用key_len
=778:
explain select * from player where player_name = '球员_1682577684751' and height = 188 and weight = 150
weight
不在查询条件,所以只用到idx_name_height
,所以key_len
= 774:
explain select * from player where player_name = '球员_1682577684751' and height = 188
height
不在查询条件,所以只用到idx_name
,所以key_len
= 770:
explain select * from player where player_name = '球员_1682577684751' and weight = 150
height
非等值匹配,所以只用到idx_name_height
,所以key_length
=774:
explain select * from player where player_name='球员_1682577684751' and height > 188 and weight = 150
player_name
最左索引不在查询条件,全表扫描
explain select * from player where weight = 150
本文第一进行测试数据准备,第二介绍执行计划相关知识,第三介绍索引失效10种场景:查询类型错误,索引列参与运算,错误使用通配符,未用到覆盖索引,OR连接无索引字段,MySQL放弃使用索引,联合索引中索引不完整,索引中断,非等值匹配,最左索引缺失。
--结束END--
本文标题: MySQL索引失效十种场景与优化方案
本文链接: https://www.lsjlt.com/news/212589.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0