文章目录
- 1、测试表
- 2、最佳实战
-
- 2.1、全值匹配
- 2.2、最左前缀法则
- 2.3、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 2.4、存储引擎不能使用索引中范围条件右边的列
- 2.5、尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句
- 2.6、mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
- 2.7、is null,is not null 一般情况下也无法使用索引
- 2.8、like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作
- 2.9、字符串不加单引号索引失效
- 2.10、少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引,详见范围查询优化
- 2.11、范围查询优化
- 3、总结
1、测试表
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('zhz',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
2、最佳实战
2.1、全值匹配
EXPLAIN SELECT
*
FROM
employees
WHERE
NAME = "zhz";
- 1
- 2
- 3
- 4
- 5
- 6
EXPLAIN SELECT
*
FROM
employees
WHERE
NAME = "zhz"
AND
age = 22;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
EXPLAIN SELECT
*
FROM
employees
WHERE
NAME = "zhz"
AND
age = 22
AND
position = "manager";
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
2.2、最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
1、EXPLAIN SELECT
*
FROM
employees
WHERE
NAME = 'Bill'
AND age = 31;
2、EXPLAIN SELECT
*
FROM
employees
WHERE
age = 30
AND position = 'dev';
3、EXPLAIN SELECT
*
FROM
employees
WHERE
position = 'manager';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
我们可以发现,只有第一个可以打到索引上
2.3、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
EXPLAIN SELECT
*
FROM
employees
WHERE
NAME = 'zhz';
- 1
- 2
- 3
- 4
- 5
- 6
给hire_time增加一个普通索引:
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2021-01-15';
- 1
- 2
转化为日期范围查询,有可能会走索引:
EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <
='2018‐09‐30 23:59:59';
- 1
- 2
还原最初索引状态
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
- 1
2.4、存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM employees WHERE name= 'zhz' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'zhz' AND age > 22 AND position ='manager';
- 1
- 2
2.5、尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句
EXPLAIN SELECT name,age FROM employees WHERE name= 'zhz' AND age = 23 AND position ='manager';
- 1
EXPLAIN SELECT * FROM employees WHERE name= 'zhz' AND age = 23 AND position ='manager';
- 1
2.6、mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
EXPLAIN SELECT * FROM employees WHERE name != 'zhz';
- 1
2.7、is null,is not null 一般情况下也无法使用索引
EXPLAIN SELECT * FROM employees WHERE name is null
- 1
2.8、like以通配符开头(’$abc…’)mysql索引失效会变成全表扫描操作
EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
- 1
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'
- 1
问题:解决like’%字符串%'索引不被使用的方法?
a)使用覆盖索引,查询字段必须是建立覆盖索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
- 1
b)如果不能使用覆盖索引则可能需要借助搜索引擎
2.9、字符串不加单引号索引失效
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
- 1
- 2
2.10、少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引,详见范围查询优化
EXPLAIN SELECT * FROM employees WHERE name = 'zhz' or name = 'HanMeimei';
- 1
2.11、范围查询优化
给年龄添加单值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;
- 1
- 2
没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子,可能是由于单次数据量查询过大导致优化器最终选择不走索引
优化方法:可以将大的范围拆分成多个小范围
explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
- 1
- 2
还原最初索引状态
ALTER TABLE `employees` DROP INDEX `idx_age`;
- 1
3、总结
like KK%相当于=常量,%KK和%KK% 相当于范围
‐‐ mysql5.7关闭ONLY_FULL_GROUP_BY报错
select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));