使用索引最佳技巧（简单易懂）

郭吱吱

已于 2022-09-19 19:44:54 修改

阅读量800

点赞数

分类专栏： MySQL 文章标签： java 数据库 sql

于 2022-09-19 19:37:13 首次发布

本文链接：https://blog.csdn.net/PingFanXianSheng/article/details/126938817

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

2.3 不在索引列上做任何操作(计算、函数（自动or手动）类型转换)，会导致索引失效转而全表扫描

2.4 存储引擎不能使用索引中范围条件右边的列(字段)

2.5 尽量使用覆盖索引

2.6 mysql在使用不等于(!= 或者<>)的时候无法使用索引

2.7 is null、is not null、not in、not exists也无法使用索引

2.8 like以通配符开头('%abc') 索引失效

2.9 字符串不加单引号索引失效(隐形类型转换)

2.10 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

2.11 范围查询优化

3、索引使用总结

在读这篇文章前，需要先知道sql的执行计划的含义，可以跳到 Explain工具介绍这里查看。

1、实例SQL

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=0 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',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());

说明：这个表目前只有两个索引

一个主键索引

还有一个联合索引 idx_name_age_position (name、age、position)

2、索引最佳实践

2.1 全值匹配

--走索引
explain select * from employees WHERE name = 'LiLei';
explain select * from employees WHERE name = 'LiLei' and age = 22;
explain select * from employees WHERE name = 'LiLei' and age = 22 and position = 'manager';
key_len分析：

        idx_name_age_position 这个索引一共有三个字段，分别是`name` varchar(24)、`age` int(11)、`position` varchar(20)这三个字段；根据上一篇文章（Explain工具介绍）提到计算ken_len长度方法。

        varchar的字符集为utf-8的情况，长度计算方式为3n+2。所以如果使用到了name字段则长度为74，如果使用到了position的长度为62。

        int类型为4个字节。

第一个SQL只使用了索引中的name字段，长度为74；

第二个SQL使用到了索引中的name 和 age (74 + 4) = 78；

第三个SQL使用了name，age，position（74+4+62）= 140

2.2 最左前缀法则

这里根据图来理解：需要按顺序一步一步的去找

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

1、以下SQL不会走索引
explain select * from employees WHERE age = 22 and position = 'manager';
explain select * from employees WHERE position = 'manager';
因为不是从最左(第一个)索引字段开始的，所以无法走索引树。

2、以下SQL只会走索引的第一个字段
explain select * from employees WHERE name = 'LiLei';
explain select * from employees WHERE name = 'LiLei' and position = 'manager';
因为索引走到第一个之后，无法找到第二个索引，所以只能查询索引的第一个字段列。

2.3 不在索引列上做任何操作(计算、函数（自动or手动）类型转换)，会导致索引失效转而全表扫描

实例1：

explain select * from employees WHERE left(name,3) = 'LiL';

实例二：给hire_time增加一个普通索引

-- 添加一个普通索引
alter table employees add index idx_hire_time (hire_time);

-- 会走索引
EXPLAIN select * from employees where hire_time ='2022-09-19';
EXPLAIN select * from employees where hire_time < '2022-09-19';

-- 没有走索引
EXPLAIN select * from employees where DATE_FORMAT(hire_time,"%Y-%m-%d") ='2022-09-19';
-- 删除索引
DROP INDEX idx_hire_time ON employees;