本文主要介绍了数据库-MySQL常用查询优化技巧的分析与总结，通过具体的内容展现，希望对大家数据库MySQL的学习有所帮助。

数据准备

CREATE TABLE staffs(

id INT PRIMARY KEY AUTO_INCREMENT,

`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',

`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',

`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',

`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'

)CHARSET utf8 COMMENT'员工记录表';

insert into staffs(NAME,age,pos,add_time) values('z3',22,'manager',NOW());

insert into staffs(NAME,age,pos,add_time) values('July',23,'dev',NOW());

insert into staffs(NAME,age,pos,add_time) values('2000',23,'dev',NOW());

create index idx_staffs_nameAgePos on staffs(name,age,pos)

1. 复合索引遵循最佳左前缀法则

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

另外对于复合索引，MySQL查询不能使用索引中范围条件右边的列，即范围后的索引列都失效。

示例：复合索引在遵循最佳左前缀法则的情况下，使用了范围条件。

EXPLAIN SELECT * FROM staffs where name = 'Alice' AND age > 21 AND pos = 'HR';

key_len=198，说明只有 name 和 age 两个索引生效，而后面的 pos 没有用上索引。

如果不使用范围查询，复合查询的执行计划如下：

查询级别达到了ref，使用到了 name ， age和pos索引列。

2. 不在索引列上做任何操作

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

但对要查询的值使用函数操作可以正常使用索引，如使用CONCAT函数拼接字符串。

3. 尽量做到索引覆盖

覆盖索引的概念：又叫索引覆盖，select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。

4. 尽量不用不等于号(!=或<>)

MySQL在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描。所以应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE。

5. 尽量避免使用is null和is not null

is null和is not null 的使用会导致索引不能使用，应该尽量避免在 WHERE 子句中对字段进行 NULL 值判断，创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。

6. 注意like模糊匹配

like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

但是总会有的业务场景必须以通配符开头模糊查找，对应的解决办法是使用索引覆盖，即查询的字段可以是主键和其他建立索引的字段，从而避免全表扫描。

简单示例：

7. 尽量不使用or

应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用 UNION 合并查询，如：

select id from t where num=10 union all select id from t where num=20

使用联合查询：

8. 注意数据类型问题

需要注意数据类型问题，如果条件列是字符串，则条件值应该与条件列属性相同，避免隐式转换而导致的索引失效。

–注意：

如果是条件列是字符串类型，条件值为数字的话索引会失效。

但是如果条件列是整型，条件值为字符串的话，索引则不会用影响。

age是整型，但是查询条件列使用字符串，依然会使用到索引。

9. order by关键字优化

9.1 基本原则

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。尽可能在索引列上完成排序操作，并且对于复合索引应该遵照索引建的最佳左前缀法则

9.2 优化策略

1.增大sort_buffer_size参数的设置

2.增大max_length_for_sort_data参数的设置

9.3 order by使用总结

MySQL有两种排序方式：文件排序或扫描有序索引排序。MySQL能为排序与查询使用相同的索引。

10. group by关键字优化

groupby实质是先排序后进行分组，所以注意点和order by大致相同，当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。where高于having,能写在where限定的条件就不要去having限定了。

总结

最后以复合索引为列，具体查询条件是否使用到索引的案例如下:

帮助记忆的口诀：

我是小职，记得找我

✅ 解锁高薪工作

✅ 免费获取基础课程·答疑解惑·职业测评