mysql 索引优化

最左匹配原则

DROP TABLE IF EXISTS employees;
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='员工表';
)

设置name 、 age 、 position 为复合索引

INSERT INTO employees(name,age,position,hire_time) VALUES('张三',22,'Net',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('李四', 23,'c#',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('王五',23,'Python',NOW());

当前这三个字段按从左到右的方式查询会走索引，最左匹配原则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最 左前列开始并且不跳过索引中的列。

不在索引列上做任何操作

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

EXPLAIN SELECT * FROM employees WHERE left(NAME,1) = '张';

不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= '小明' AND age >
22 AND position ='Net';
我们可以计算下 key_len 长度，可以得知 索引只用到了 name 和 age , position 没用用到索引，所以在复合索引中使用了范围条件右边的列索 引会失效。

使用覆盖索引

EXPLAIN SELECT NAME, age, POSITION FROM employees WHERE NAME= '张三' AND age = 22 AND POSITION ='Net';

    --此时Extra是Using Index ，走索引，读内存 可以看到，从 NULL 变成了 Using index

使用不等于（！=或者<>）时索引失效

EXPLAIN SELECT * FROM employees WHERE NAME != '张三' 此时，Extra字段Using where 

IS NULL，IS NOT NULL 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name IS NOT NULL 此时，Extra字段是Using where

LIKE 以通配符开头索引会失效

 EXPLAIN SELECT * FROM employees WHERE name LIKE '%李'  此时，Extra字段是Using where
 通配符放结尾索引不会失效
EXPLAIN SELECT * FROM employees WHERE name LIKE '李%'   此时，Extra字段是Using index condition
 当覆盖索引指向的字段是 varchar(380) 及 380 以上的字段时，覆盖索引会失效！

字符串不加单引号索引失效

1 EXPLAIN SELECT * FROM employees WHERE NAME = 张三;  此时，Extra字段是Using where

少用or，用它连接很多情况下索引会失效

EXPLAIN SELECT * FROM employees WHERE name = '张三' OR NAME= '李四';