mysql sql优化

mysql有优化器，所以sql优化是一种概率优化，真是的执行情况要依靠explain查询的执行计划。
//查看执行计划
explain select * from table ;

 

 

id：编号
select_type：查询类型
table：查询的表
type：执行类型
possible_keys：预测使用的索引
key：实际使用的索引
key_len：实际使用的索引长度
ref：表之间的引用
extra：额外的信息

select_type的类型
1. system： 表中只有一条数据
2. const：查询结果只有一条数据
3. eq_ref：使用了唯一所以，查询的结果有且只有一条
4. ref：使用了非唯一所以，结果 0或者1 或者多
5. range：查询指定的范围，一般是范围查询，< > in
6. index:查询全部索引中的数据（扫描真个索引）
7. all：全表扫描，暴力扫描全表
8. ken_len：用到的索引字段长度，比如varchar（20）,utf8，一个字符三个字节，允许null需要一个字节表示，varchar可变长度需要两个字节描述，实际长度就是 63
9. rows：实际查询行数
10. extra：
　　useing filesort--表示sql消耗很大的性能，进行一次额外的排序，常见于order by语句，where是哪个字段，orderby就用哪个字段否则会有filesort
　　using temporary---也表示消耗了很大的性能，这个表示当前sql使用了临时表，一般出现在groupby，查询哪个字段就用哪个字段分组，否则会出现；
　　using index---表示覆盖索引，查询结果和条件都在索引树上。
　　using where---表示回表查询
　　impossible where 表示where永远为false，查询没有结果
　　using join buffer---表示mysql引擎使用了连接缓存，既mysql底层动了你的sql，你写的太烂了
　　优化路径 all->index->range->ref ，优化到这里就够了。

 

 

规则：
1. 复合索引必须满足最左前缀原则，索引的全部字段都在，即使不是顺序的，优化器会优化，还是可以走索引，索引中如果有范围查询，
　　范围查询（> < != in）必须是最后一个组合索引的字段，否则会失效，in 条件也会导致索引失效,将in条件呢放到where语句的最后，防止索引失效，
　　每次新建索引，最好废除以前的索引
2. 小表驱动大表原则
3. 不要再索引上进行任何操作（计算、函数、类型转换），否则索引会失效
4. 索引不能使用不等于（！= <>）或者 is null /is not null，否则自身以及右侧都会失效，符合索引有 > 则自身和右侧全部失效
5. 索引优化，只是一个概率优化，由于优化器存在，结论不是100%正确的，一般而言，范围查询 < > in 之后索引失效
6. sql优化是一种概率层面的优化，具体要通过explain进行推测
7. 新建了两个所以，实际只是用了一个，原因就是两个单独的索引，优化器认为只是用一个就够了，不需要都使用。
8. 索引覆盖，百分之百没有问题。
9. like不要以 % 开头，否则索引失效
10. 不要使用类型转换，否则索引失效
11. 不要使用 or ，否则索引失效，or 很猛，会让自身和左右两侧都失效。
12. 如果主查询的数据集大，则使用i关键字，效率高。如果子查询的数据集大，则使用exist关键字,效率高。