2025年Mysql索引的优化原则

科技前沿 • 2025-01-12 14:24 • 阅读 52

大家好，我是讯享网，很高兴认识大家。

1、全值匹配和key_len长度计算

2、最左前缀匹配原则

3、避免在索引列上计算

4、存储引擎不能使用索引中范围条件右边的列

5、索引列尽量包含查询列

6、Mysql在使用不等于，not in，not exists 时无法使用索引

7、is null，is not null 一般情况下也无法使用索引

8、like 以通配符开头('%abc...')，索引失效会变成全表扫描

9、字段类型转换导致索引失效

10、少用or或in进行查询

11、索引使用总结

讯享网

案例前准备

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=4 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());

讯享网

1、全值匹配和key_len长度计算

讯享网--以下三条语句都遵循联合索引的定义顺序 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';

以下分别为三次执行结果

全值匹配：联合索引中的索引字段用得越多，筛选出来的结果越少，sql执行效率越高。

三次查询都使用到了索引，区别在于使用的索引长度不同，关于key_len长度的计算：

查询结果一：使用到`name` varchar(24)，那么key_len=24x3+2=74；
查询结果二：使用到`name`和`age` int(11)，那么key_len=74 + 4 =78；
查询结果三：使用到`name`和`age` 和 `position` varchar(20) ，那么key_len=78+(20x3+2)=140；

// int(11)中，11 代表的并不是长度，而是字符的显示宽度，int 固定占用4字节

key_len的计算规则

字符串类型：char(n) 和 varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节。

char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

字符串类型	长度	数值类型	长度	时间类型	长度
char(n)	存汉字长度为 3n 字节	tinyint	1字节	date	3字节
varchar(n)	存汉字长度为 3n + 2 字节	smallint	2字节	timestamp	4字节
		int	4字节	datetime	8字节
		bigint	8字节

注：如果字段允许为 NULL，则需要额外1字节记录是否为 NULL，索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

2025年Mysql索引的优化原则

1、全值匹配和key_len长度计算

相关推荐