翼度科技»论坛 编程开发 mysql 查看内容

MySQL索引失效十种场景与优化方案

4

主题

4

帖子

12

积分

新手上路

Rank: 1

积分
12
1 数据准备


1.1 新建数据表
  1. CREATE TABLE `player` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  3.   `player_id` varchar(256) NOT NULL COMMENT '运动员编号',
  4.   `player_name` varchar(256) NOT NULL COMMENT '运动员名称',
  5.   `height` int(11) NOT NULL COMMENT '身高',
  6.   `weight` int(11) NOT NULL COMMENT '体重',
  7.   `type` varchar(256) DEFAULT '0' COMMENT '球员类型',
  8.   `game_performance` text COMMENT '最近一场比赛表现',
  9.   PRIMARY KEY (`id`),
  10.   KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),
  11.   KEY `idx_type` (`type`),
  12.   KEY `idx_height` (`height`)
  13. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
复制代码
以上数据表声明三个索引:

  • 联合索引:idx_name_height_weight
  • 普通索引:idx_type
  • 普通索引:idx_height

1.2 新增100万条数据
  1. @SpringBootTest(classes = TestApplication.class)
  2. @RunWith(SpringJUnit4ClassRunner.class)
  3. public class PlayerServiceTest {

  4.     @Resource
  5.     private PlayerRepository playerRepository;

  6.     @Test
  7.     public void initBigData() {
  8.         for (int i = 0; i < 1000000; i++) {
  9.             PlayerEntity entity = new PlayerEntity();
  10.             entity.setPlayerId(UUID.randomUUID().toString());
  11.             entity.setPlayerName("球员_" + System.currentTimeMillis());
  12.             entity.setType("0");
  13.             entity.setWeight(150);
  14.             entity.setHeight(188);
  15.             entity.setGamePerformance("{"runDistance":8900.0,"passSuccess":80.12,"scoreNum":3}");
  16.             playerRepository.insert(entity);
  17.         }
  18.     }
  19. }
复制代码
2 基础知识


2.1 explain type

执行计划中访问类型是重要分析指标:


2.2 explain Extra

Extra表示执行计划扩展信息:


3 索引失效场景

本章节介绍索引失效十种场景:

  • 查询类型错误
  • 索引列参与运算
  • 错误使用通配符
  • 未用到覆盖索引
  • OR连接无索引字段
  • MySQL放弃使用索引
  • 联合索引失效

    • 索引不完整
    • 索引中断
    • 非等值匹配
    • 最左索引缺失


3.1 查询类型错误


3.1.1 失效场景
  1. explain select * from player where type = 0
复制代码


3.1.2 解决方案

数据表定义
  1. type
复制代码
字段为
  1. varchar
复制代码
类型,查询必须使用相同类型:


3.2 索引列参与运算


3.2.1 失效场景
  1. explain select * from player where height + 1 > 189
复制代码


3.2.2 解决方案
  1. explain select * from player where height > 188
复制代码


3.3 MySQL放弃使用索引


3.3.1 失效场景

MySQL发现如果使用索引性能低于全表扫描则放弃使用索引。例如在表中100万条数据
  1. height
复制代码
字段值全部是
  1. 188
复制代码
,所以执行如下语句时放弃使用索引:
  1. explain select * from player where height > 187
复制代码


3.3.2 解决方案一

调整查询条件值:
  1. explain select * from player where height > 188
复制代码


3.3.3 解决方案二

强制指定索引,这种方法不一定可以提升性能:


3.4 错误使用通配符


3.4.1 数据准备

避免出现3.3章节失效问题此处修改一条数据:
  1. update player set player_name = '测试球员' where id = 1
复制代码
3.4.2 失效场景一
  1. explain select * from player where player_name like '%测试'
复制代码


3.4.3 失效场景二
  1. explain select * from player where player_name like '%测试%'
复制代码


3.4.4 解决方案
  1. explain select * from player where player_name like '测试%'
复制代码


3.5 OR连接无索引字段


3.5.1 失效场景
  1. type
复制代码
有索引,
  1. weight
复制代码
无索引:
  1. explain select * from player where type = '0' or weight = 150
复制代码


3.5.2 解决方案
  1. weight
复制代码
新增索引,
  1. union
复制代码
拼装查询数据
  1. explain
  2. select * from player where type = '0'
  3. union
  4. select * from player where weight = 150
复制代码


3.6 未用到覆盖索引


3.6.1 失效场景
  1. Using index condition
复制代码
表示使用索引,但是需要回表查询
  1. explain select * from player where player_name like '测试%'
复制代码


3.6.2 解决方案

覆盖索引含义是查询时索引列完全包含查询列,查询过程无须回表(需要在同一棵索引树)性能得到提升。
  1. Using Index; Using where
复制代码
表示使用覆盖索引并且用
  1. where
复制代码
过滤查询结果:
  1. explain select id,player_name,height,weight from player where player_name like '测试%'
复制代码


3.7 联合索引失效


3.7.1 完整使用

联合索引
  1. idx_name_height_weight
复制代码
完整使用
  1. key_len
复制代码
=778:
  1. explain select * from player where player_name = '球员_1682577684751' and height = 188 and weight = 150
复制代码


3.7.2 失效场景一:索引不完整
  1. weight
复制代码
不在查询条件,所以只用到
  1. idx_name_height
复制代码
,所以
  1. key_len
复制代码
= 774:
  1. explain select * from player where player_name = '球员_1682577684751' and height = 188
复制代码


3.7.3 失效场景二:索引中断
  1. height
复制代码
不在查询条件,所以只用到
  1. idx_name
复制代码
,所以
  1. key_len
复制代码
= 770:
  1. explain select * from player where player_name = '球员_1682577684751' and weight = 150
复制代码


3.7.4 失效场景三:非等值匹配
  1. height
复制代码
非等值匹配,所以只用到
  1. idx_name_height
复制代码
,所以
  1. key_length
复制代码
=774:
  1. explain select * from player where player_name='球员_1682577684751' and height > 188 and weight = 150
复制代码


3.7.5 失效场景四:最左索引缺失
  1. player_name
复制代码
最左索引不在查询条件,全表扫描
  1. explain select * from player where weight = 150
复制代码


4 文章总结

本文第一进行测试数据准备,第二介绍执行计划相关知识,第三介绍索引失效10种场景:查询类型错误,索引列参与运算,错误使用通配符,未用到覆盖索引,OR连接无索引字段,MySQL放弃使用索引,联合索引中索引不完整,索引中断,非等值匹配,最左索引缺失。
以上就是MySQL索引失效十种场景与优化方案的详细内容,更多关于MySQL索引失效的资料请关注脚本之家其它相关文章!

来源:https://www.jb51.net/article/283474.htm
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具