单表查询优化

注意

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

建议

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

示例

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);

关联查询优化

建议

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

子查询优化

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

排序优化

ORDER BY子句，尽量使用Index方式（索引）排序,避免使用FileSort方式（手工）排序。

技巧：无过滤，不索引；顺序错，必手工排序；方向反，必手工排序；

要想Order BY使用到索引，必须要添加过滤条件（where子句对索引中的字段进行过滤，而且必须按照顺序），Limit分页也行。
在SQL语句中的顺序一定要和定义索引中的字段顺序完全一致！
要么全升序、要么全降序。有升有降无法使用索引

案例

SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

可以看到，上面where条件中有范围查询，那么后面的索引会失效。

那么我们可以创建两个索引，一个是idx_age_empno（避免不了Using filesort），另一个是idx_age_name（不能让where条件充分用到索引），当这两个索引同时存在的时候，MySQL会选择谁作为最优索引呢？会选择让where子句舒服的索引，即idx_age_empno。

Using filesort有两种排序算法

一种是单路排序，一种的双路排序。

单路排序更快，因为使用到了内存。

分组优化

Group By 使用索引的原则几乎跟Order By一致，唯一区别是Group By即使没有过滤条件用到索引，也可以直接使用索引。

MySQL索引优化