赞
踩
新建一个student表,它有一个普通索引userId,结构如下:
- CREATE TABLE `student` (
- `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
- `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `score` decimal(10,2) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `student_name_IDX` (`name`) USING BTREE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
explain select * from student s where name ='liuliu'
explain select * from student s where name ='liuliu' or score =80
分析&结论:
对于or+score没有索引的这种情况,假设它走了name的索引,但是走到score查询条件时,它还得全表扫描,假如走索引也就是需要三步过程:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,就只需一步;mysql的查询优化器,处于效率与成本考虑,遇到这种情况,明显全表扫描更合理。
注意: 如果or条件的列都加了索引,索引有可能会走,也有可能不会走。
分析与结论:
- 类型不匹配,MySQL会做隐式的类型转换,索引失效
并不是用了like通配符,索引一定失效,而是like查询是以%开头(前缀模糊匹配),才会导致索引失效。
explain select * from student s where name like '%zhang%'
explain select * from student s where name like 'zhang%'
- CREATE TABLE `student` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT,
- `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `score` decimal(10,2) DEFAULT NULL,
- `subject` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
- `create_time` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建时间',
- PRIMARY KEY (`id`),
- KEY `student_name_IDX` (`name`) USING BTREE,
- KEY `student_subject_IDX` (`subject`,`score`) USING BTREE,
- KEY `student_create_time_IDX` (`create_time`) USING BTREE
- ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
表结构student有一联合索引: `student_subject_IDX` (`subject`,`score`)
explain select * from student s where score >80 and subject ='英语'
explain select * from student s where score >80
分析与结论:
当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。
联合索引不满足最左原则,索引一般会失效。
- CREATE TABLE `student` (
- `id` bigint(20) NOT NULL AUTO_INCREMENT,
- `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
- `score` decimal(10,2) DEFAULT NULL,
- `subject` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '科目',
- `create_time` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建时间',
- PRIMARY KEY (`id`),
- KEY `student_name_IDX` (`name`) USING BTREE,
- KEY `student_subject_IDX` (`subject`,`score`) USING BTREE,
- KEY `student_create_time_IDX` (`create_time`) USING BTREE
- ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
表结构student有一索引:`student_create_time_IDX` (`create_time`)
explain select * from student s where create_time='2023-02-02'
explain select * from student s where date(create_time)='2023-02-02'
注意:从 MySQL 8.0 开始,索引特性增加了函数索引,即可以针对函数计算后的值建立一个索引,也就是说该索引的值是函数计算后的值,所以就可以通过扫描索引来查询数据
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。