MySQL中SQL语句和索引的优化

最左法则

如果索引了多列，要遵守最左前缀法则。

最左前缀法则指的是查询从索引的最左前列开始并且不跳过索引中的列。
过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

有如下三个索引，顺序为 age、 deptId、 name

测试查询结构如下：

函数

使用模糊匹配like 时：前缀匹配可以触发索引，后缀匹配不能触发索引

新增name索引进行测试

测试结果

where 后面加函数查询：索引完全失效（select 后面可以加）

LEFT(emp.name,3) 代表使用LEFT函数返回最左边的三个字符串，也就是名字前三位为 abc

范围条件

范围查询右边的列，不能使用索引
注意: 应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置 where 语句的最后面

运算操作

对索引列运算，运算包括（ + - * / ! != <> % or in exist 等），导致索引失效。

注意：不要在索引列上进行运算操作，索引将失效

is not null 与 is null

#IS NULL:可以触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;
#IS NOT NULL:无法触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;

类型转换

字符串不加单引号，造成索引失效

总结

假设有一个组合索引：index(a,b,c)，即有有 a，ab，abc 三种组合

Where 语句	索引是否被使用
where a = 3	使用到 a
where a = 3 and b = 5	使用到 ab
where a = 3 and b = 5 and c = 4	使用到 abc
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	不能使用索引，不符合最左法则
where a = 3 and c = 5	使用到 a，但是 c 不可以，b 中间断了
where a = 3 and b > 4 and c = 5	使用到 a 和 b， c 不能用在范围之后，b 断了
where a is null and b is not null	is null 支持索引但是 is not null 不支持所以 a 可以使用索引，但是 b 不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引
where a = 3 and b like ‘kk%’ and c = 4	使用到 abc
where a = 3 and b like ‘%kk’ and c = 4	只用到 a
where a = 3 and b like ‘%kk%’ and c = 4	只用到 a
where a = 3 and b like ‘k%kk%’ and c = 4	使用到 abc