Day7——单表优化

一. 回顾

前面搭建好了Day6——准备sql优化的环境——批量插入数据，今天开始学习单表优化

二. 单表优化练习

注意代码中的注释，如下：

explain SELECT SQL_NO_CACHE * from emp where emp.age = 30;

#建立索引
create index idx_age on emp(age);

#清掉索引
call proc_drop_index("mydb", "emp");
------------------------------------------------------------------------------------

EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and deptid = 4;#优化前0.16，优化后0.033

create index idx_age_deptid on emp(age, deptId);

#清掉索引
call proc_drop_index("mydb", "emp");

------------------------------------------------------------------------------------

#全值匹配我最爱，where后面的字段都建索引（复合索引性能更佳）
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and deptid = 4 and emp.name = 'abcd';#优化前0.16,优化后0.036


create index idx_age_deptid_name on emp(age, deptId, name);

#清掉索引
call proc_drop_index("mydb", "emp");
----------------------------------------------------------

#调整where后面的条件的顺序，也能用上索引。因为mysql有优化器，能调整顺序。
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and emp.name = 'abcd' and deptid = 4 ;

----------------------------------------------------------

#最佳左前缀匹配，优化器会调整sql顺序，从索引的最左边开始匹配，若中间断开则后面的索引全都失效。

#删掉了'and deptid = 4 ',只能用上一个索引age，可以从explain的key_len看出。
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age = 30 and emp.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name = 'abcd' and emp.age = 30;

#删掉了'emp.age = 30',一个索引都用不上，explain的type为all，查全表
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.deptId = 4 and emp.name = 'abcd';

#清掉索引
call proc_drop_index("mydb", "emp");

----------------------------------------------------------

#哪条sql的性能更好
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name like 'abc%'; #优化前0.17 优化后0.030

EXPLAIN SELECT SQL_NO_CACHE * from emp where left(emp.name, 3) = 'abc'; #优化前0.17 索引失效，type=all

create index idx_name on emp (name);

#总结：在索引列上做任何操作（计算、函数、类型转换等），都会导致索引失效

#清掉索引
call proc_drop_index("mydb", "emp");

----------------------------------------------------------

EXPLAIN SELECT SQL_NO_CACHE * from emp 
where emp.age = 30 and emp.deptId > 20 and emp.name = 'abc'; #优化前0.036 优化后0.030 优化不明显

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 emp.age = 30 and emp.deptId > 20 and emp.name = 'abc';#优化后3个索引都用上了

#总结：时间的字段放在索引的最后


#清掉索引
call proc_drop_index("mydb", "emp");

----------------------------------------------------------

EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name <> 'abc%';

create index idx_name on emp (name);

#总结：出现不等于'<>'，索引失效

#清掉索引
call proc_drop_index("mydb", "emp");

----------------------------------------------------------

EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age is null;#能用上索引

EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.age is not null;#不能用上索引

create index idx_age on emp (age);

#总结：所有负面操作，比如is not, not in, !=, <>, is not null,都用不上索引

#清掉索引
call proc_drop_index("mydb", "emp");

----------------------------------------------------------

EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name like 'abc%';#能用上索引
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name like '%abc%';#不能用上索引
EXPLAIN SELECT SQL_NO_CACHE * from emp where emp.name = 123;#不能用上索引，因为name=123做了类型转换，name是varchar类型
create index idx_name on emp (name);

#总结：'%'开头用不上索引，字串类型的字段索引按首字母开头构成一个tree，通配符开头则只能全树匹配
#总结：字串类型的字段不加单引号索引失效

三. 总结

1. 全值匹配。where后面有哪些字段，就给哪些字段建立索引。
2. 最佳左前缀法则。如果索引了多列，查询从索引的最左前列开始并且不跳过索引中的列。
3. 不在索引列上做任何操作（函数、计算、类型转换等），因为这样会导致索引失效从而转向全表扫描。
4. 存储引擎不能使用索引中范围条件右边的列，但范围条件的索引列仍有效
5. 一切负面操作导致索引失效从而转向全表扫描。比如!=，<>，is not null，not in等等。但是is null能使用索引。
6. like以通配符%开头导致索引失效。
7. 字符串类型的字段不加单引号导致索引失效。比如where name = 123，name为字串类型时，给name建索引会失效。

四. 一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引。
• 对于组合索引，当前query中过滤性最好的字段在索引顺序中越靠前越好；尽量包含where子句中的更多字段；出现范围查询，尽量把该字段放在索引次序的末尾；书写sql时，尽量避免造成索引失效的情况。