MySQL索引失效十种场景与优化方案
作者:JAVA前线 发布时间:2024-01-26 06:07:33
1 数据准备
1.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
以上数据表声明三个索引:
联合索引:idx_name_height_weight
普通索引:idx_type
普通索引:idx_height
1.2 新增100万条数据
@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);
}
}
}
2 基础知识
2.1 explain type
执行计划中访问类型是重要分析指标:
2.2 explain Extra
Extra表示执行计划扩展信息:
3 索引失效场景
本章节介绍索引失效十种场景:
查询类型错误
索引列参与运算
错误使用通配符
未用到覆盖索引
OR连接无索引字段
MySQL放弃使用索引
联合索引失效
索引不完整
索引中断
非等值匹配
最左索引缺失
3.1 查询类型错误
3.1.1 失效场景
explain select * from player where type = 0
3.1.2 解决方案
数据表定义type
字段为varchar
类型,查询必须使用相同类型:
3.2 索引列参与运算
3.2.1 失效场景
explain select * from player where height + 1 > 189
3.2.2 解决方案
explain select * from player where height > 188
3.3 MySQL放弃使用索引
3.3.1 失效场景
MySQL发现如果使用索引性能低于全表扫描则放弃使用索引。例如在表中100万条数据height
字段值全部是188
,所以执行如下语句时放弃使用索引:
explain select * from player where height > 187
3.3.2 解决方案一
调整查询条件值:
explain select * from player where height > 188
3.3.3 解决方案二
强制指定索引,这种方法不一定可以提升性能:
3.4 错误使用通配符
3.4.1 数据准备
避免出现3.3章节失效问题此处修改一条数据:
update player set player_name = '测试球员' where id = 1
3.4.2 失效场景一
explain select * from player where player_name like '%测试'
3.4.3 失效场景二
explain select * from player where player_name like '%测试%'
3.4.4 解决方案
explain select * from player where player_name like '测试%'
3.5 OR连接无索引字段
3.5.1 失效场景
type
有索引,weight
无索引:
explain select * from player where type = '0' or weight = 150
3.5.2 解决方案
weight
新增索引,union
拼装查询数据
explain
select * from player where type = '0'
union
select * from player where weight = 150
3.6 未用到覆盖索引
3.6.1 失效场景
Using index condition
表示使用索引,但是需要回表查询
explain select * from player where player_name like '测试%'
3.6.2 解决方案
覆盖索引含义是查询时索引列完全包含查询列,查询过程无须回表(需要在同一棵索引树)性能得到提升。Using Index; Using where
表示使用覆盖索引并且用where
过滤查询结果:
explain select id,player_name,height,weight from player where player_name like '测试%'
3.7 联合索引失效
3.7.1 完整使用
联合索引idx_name_height_weight
完整使用key_len
=778:
explain select * from player where player_name = '球员_1682577684751' and height = 188 and weight = 150
3.7.2 失效场景一:索引不完整
weight
不在查询条件,所以只用到idx_name_height
,所以key_len
= 774:
explain select * from player where player_name = '球员_1682577684751' and height = 188
3.7.3 失效场景二:索引中断
height
不在查询条件,所以只用到idx_name
,所以key_len
= 770:
explain select * from player where player_name = '球员_1682577684751' and weight = 150
3.7.4 失效场景三:非等值匹配
height
非等值匹配,所以只用到idx_name_height
,所以key_length
=774:
explain select * from player where player_name='球员_1682577684751' and height > 188 and weight = 150
3.7.5 失效场景四:最左索引缺失
player_name
最左索引不在查询条件,全表扫描
explain select * from player where weight = 150
4 文章总结
本文第一进行测试数据准备,第二介绍执行计划相关知识,第三介绍索引失效10种场景:查询类型错误,索引列参与运算,错误使用通配符,未用到覆盖索引,OR连接无索引字段,MySQL放弃使用索引,联合索引中索引不完整,索引中断,非等值匹配,最左索引缺失。
来源:https://juejin.cn/post/7230120223000870971
猜你喜欢
- replace()方法返回当前old换成new,可选择的替代限制到最大数量的字符串的副本。语法以下是replace()方法的语法
- 一、背景分析对想要在视觉化环境下制作复杂网页的专业网页制作者来说,Dreamweaver已经渐渐在网页编辑工具中展露头角,成为专业人士编写网
- Python读取配置文件-ConfigParser二次封装直接上上代码test.conf[database]connect = mysqls
- 大致介绍好久没有写博客了,正好今天有时间把前几天写的利用python定时发送QQ邮件记录一下1、首先利用request库去请求数据,天气预报
- 来源:在工作过程中,需要统计一些trace信息,也就是一些打点信息,而打点是通过关键字进行的,因此对一个很大的文件进行分析时,想把两个打点之
- 长期以来我就有对几年来交互设计的心得进行总结整理的想法。回到中国来亲身体会到不少同行,主要是交互设计师和视觉设计师对于交互设计的困惑,以及其
- 实例如下:/** * 数字格式转换成千分位 *@param{Object}num */function commafy(num){ &nbs
- Pyperclip模块详解一. Pyperclip模块安装Pyperclip模块兼容python2和python3,能跨平台使用。安装指令如
- 今天来说说鄙人对input输入框在处理上的细节处理和心得,其实制作一个符合CSS标准、FF/IE7/IE6等主流浏览器全兼容、符合用户体验的
- 这是写给web设计者和前端开发者的教程,我们将演示如何使用Photoshop创建按钮的sprite图,然后是如何使用jQurey打造动态渐变
- 一、使用xlrd对excel进行数据读取excel表格示例:安装xlrd库pip install xlrd导入xlrd库import xlr
- python随机数种子seed()栗子1import numpy as npimport randomrandom.seed(0)np.ra
- python2.7wget http://www.python.org/ftp/python/2.7.6/Python-2.7.6.tar.
- 总结调试网站获取cookies时请查看,r.header和r.request.header这两个属性,因为cookie说不准出现在他们俩谁里
- 以下的文章主要介绍的是MySQL 查询缓存的实际应用代码以及查看MySQL 查询缓存的大小 ,碎片整理,清除缓存以及监视MySQL 查询缓存
- 图像显示和打印面临的一个问题是:图像的亮度和对比度能否充分突出关键部分。这里所指的“关键部分”在 CT 里的例子有软组织、骨头、脑组织、肺、
- 查询一天:select * from table where to_days(column_time) = to_days(now());s
- 最近有Win10系统用户反映,由于自己的电脑安装有两个python软件,所以想要卸载掉其中一个,不过在卸载的时候却发现无法卸载,并且出现提示
- 首先得有一个Scrapy项目,我在Desktop上新建一个Scrapy的项目叫test,在Desktop目录打开命令行,键入命令:scrap
- JavaScript图片水平翻转后垂直翻反转的特效一:<!--把下列代码加到body区域内--><SCRIPT langu