③. 不在索引列上做任何操作
- ①. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- ②. SQL语句与截图
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
④. 存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manage r'; EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manage r';
⑤. 尽量使用覆盖索引、减少select *
- ①. 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句
EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
②. 使用select *
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manage r';
⑥. mysql在使用不等于(！=或者<>)
- ①. mysql在使用不等于(！=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描
- ②. < 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
