最左前缀原则
最佳左前缀原则——如果索引了多列,要遵守最左前缀原则。是查询要从加了索引的最左边的列开始,而且不跳过加了索引 的列(表示按照加了索引的列的顺序执行)
两条原则: 加索引的列的第一个列不能丢
加索引的列的中间列不能丢会造成后面的加索引列失效。
表中必须加了复合索引
简单创建了一个user表,字段有id, name, age,gender, address
//添加复合索引 顺序(name,age,gender)
alter table user add INDEX `sindex` (`name`,`age`,`gender`)
正确的:
//EXPLAIN 表示执行计划
EXPLAIN SELECT * FROM user WHERE name = '张三';
EXPLAIN SELECT * FROM user WHERE name = '张三' AND age = '12';
剖析:该查询有name和age条件,根据最佳左前缀原则索引能够被使用到,但是是部分使用。
部分索引失效:
最左前缀第2个原则:加了索引的多个列,不能跳过列(加索引的顺序不能跳过,如果跳过中间的任意一列,后面加索引的列会 失效),会造成部分失效
可以与正确部分中的第二张图中的ref作比较
EXPLAIN SELECT * FROM user WHERE name = '张三' AND gender= '男';
错误的:
EXPLAIN SELECT * FROM user WHERE age = '12' AND gender = '男';
剖析:查询缺少name,查询的条件复合索引最左侧name缺少,不符合最佳左前缀原则,导致索引失效,type属性为ALL,ALL表示全表扫描。
EXPLAIN SELECT * FROM user WHERE gender = '男';
剖析:查询缺少name和age,查询的条件复合索引最左侧name和age缺少,不符合最佳左前缀原则,导致索引失效,type属性为ALL,ALL表示全表扫描。
索引列上做任何计算操作(计算,函数,(自动or手动)类型转换)会导致索引失效
错误的:
EXPLAIN SELECT * FROM user WHERE left(name,2) = '张三';
mysql 中like查询是以通配符%开头,会索引失效
错误的:
EXPLAIN SELECT * FROM user WHERE name LIKE '%张';
剖析:对name列做了普通索引,以%开头进行查询,结果索引失效
正确的:
EXPLAIN SELECT * FROM user WHERE name LIKE '张%';
Mysql中使用is not null 和is null无法使用索引,导致全表扫描
网上说的是is not null 和 is null 会索引失效,但是本人自己测试并不是就会造成索引失效。
当我表中数据为2条时,is not null 索引就不会失效。
EXPLAIN SELECT * FROM user WHERE name is not null;
当表中数据大于2条时,is not null 索引就会失效。
EXPLAIN SELECT * FROM user WHERE name is not null;
当表中数据为2条时,is null 就不会索引失效。
EXPLAIN SELECT * FROM user WHERE name is null;
当表中数据大于2条时,is null 就索引失效。
EXPLAIN SELECT * FROM user WHERE name is null;
Mysql中使用or连接条件,会导致索引失效,全表扫描。
EXPLAIN SELECT * FROM user WHERE age = '15' or age = '23';
Mysql在使用不等于(!=或者<>),会无法使用索引,全表扫描。
EXPLAIN SELECT * FROM user WHERE age != '23';
EXPLAIN SELECT * FROM user WHERE age <> '23';
Mysql中in会索引失效
失效的原因:
1. 字段类型
如果字段类型为字符串,需要给in查询中的数值与字符串值都需要添加引号,索引才能起作用。
如果字段类型为int,则in查询中的值不需要添加引号,索引也会起叙用。
2. 数据量
数据量小的的情况下,mysql会认为扫描全表比使用索引快,自然不会使用索引.
3. 条件
如果in后面的条件过多的话,会导致索引失效,mysql会认为全表扫描要比索引快
数据量大一些的情况下,它会使用索引
EXPLAIN SELECT * FROM user WHERE address IN ('北京','重庆');
条件过多的情况下索引失效
EXPLAIN SELECT * FROM user WHERE address IN ('北京','重庆','上海','天津');
数据量小的情况下,索引失效
EXPLAIN SELECT * FROM user WHERE address IN ('北京','重庆');