MySQL之索引查询优化

转载希望指明原处：https://blog.csdn.net/qq_34161458
更多知识，请移步我的小破站：http://hellofriend.top

1. 单表查询优化

1.1 注意

• 使用复合索引的效果会大于使用单个字段索引（但是要注意顺序）。
• 查询条件时要按照索引中的定义顺序进行匹配。如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
• 存储引擎不能使用索引中范围条件右边的列，范围查询的列在定义索引的时候，应该放在最后面。
• mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。
• is not null 也无法使用索引,但是is null是可以使用索引的。
• like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作。
• 字符串不加单引号索引失效（类型转换导致索引失效）。
  

1.2 建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引。
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。 书写sql语句时，尽量避免造成索引失效的情况。

1.3 示例

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30;
# 给emp表的age字段创建索引
CREATE INDEX idx_age ON emp(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30 AND deptId = 1;
# 给emp表的age字段和deptid创建索引
CREATE INDEX idx_age_deptId ON emp(age,deptId);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30 AND deptId = 1 AND NAME = 'abcd';
# 给emp表的age字段和deptid和name创建索引
CREATE INDEX idx_age_deptId_name ON emp(age,deptId,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME  LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(NAME,3) = 'abc';
# 给emp表的name字段创建索引,如果where条件中使用到了函数，可能会造成索引失效！
CREATE INDEX idx_name ON emp(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30 AND deptId > 20 AND NAME = 'abc';
# 当索引中有字段是范围查询时，这个字段后面的字段索引失效，所以在创建索引时，范围查询的字段放在最后
CREATE INDEX idx_age_deptId_name ON emp(age,deptId,NAME);
CREATE INDEX idx_age_name_deptId ON emp(age,NAME,deptId);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME <> 'abc';
# <>会让索引失效
CREATE INDEX idx_name ON emp(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME IS NOT NULL;
# IS NOT 也会导致索引失效
CREATE INDEX idx_name ON emp(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME LIKE '%abc%';
# LIKE 匹配值的开头是%，索引失效
CREATE INDEX idx_name ON emp(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE NAME = 123;
# NAME类型为字符类型，123会被mysql做类型转换，索引失效
CREATE INDEX idx_name ON emp(NAME);

2. 关联查询优化

2.1 建议

1. 保证被驱动表（从表）的join字段已经被索引。
2. left join 时，选择小表作为驱动表（主表），大表作为被驱动表（从表）。
3. inner join 时，mysql会自己帮你把小结果集的表选为驱动表（主表）。
4. 子查询尽量不要放在被驱动表（从表），有可能使用不到索引。
5. 能够直接多表关联的尽量直接关联，不用子查询。

3. 子查询优化

尽量不要使用 not in 或者 not exists，因为两者会导致索引失效。用left outer join on xxx is null 替代 not in 或者 not exists。两者都可以取到一个表独有的数据。连接查询可以使用到索引。

4. 总结

本文总结了索引查询优化中的知识点，下篇将继续讲解排序分组的索引优化的相关知识。