mysql之sql优化总结（一）

主键优化

1. 尽量的缩短主键长度，节省磁盘空间。
2. 避免使用无序的主键，如UUID、身份证等。
3. 避免对主键进行修改，修改主键会同时修改索引，消耗很大。

排序

1. 能不排序就不排序，毕竟会增效消耗。
2. 参与排序的字段尽量创建索引，如果有多个排序字段，根据各字段的排序规则建立联合索引。
3. 如果实际情况创建不了索引，可以适当增加排序缓冲区（sort_ buffer_ _size，默认256K） 

分组 

1. 参与分组的字段尽量创建索引，如果有多个排序字段可创建联合索引，分组在使用索引时也能满足最左前缀法则。
2. 默认情况下，mysql会对参与分组的字段进行排序，如果不需要排序，记得加ORDER BY NUL。

limit

1. 合理使用limit，比如：select id,name from product limit 100000, 20，查询的时候100020条数据，然后丢弃前面的100000条数据，返回剩下的20条数据。可以改成这样：select id,name from product where id> 99999 limit 20。
2. 查询数据是否存在时，尽量使用limit 1。

count

1. 使用count时尽量使用count(*)，效率：count(字段)<count(主键)<count(1)≈count(*)。 

union & union all

1. union会将合并的结果进行去重，会涉及到排序，所以会加大消耗，在明确知道结果不会有重复数据时，使用union all。

select * 

1. 禁止使用select *，百分九十会回表查询。
2. 使用select 字段1、字段2，如果select后面的字段都命中索引，那么就不会进行回表查询。
3. 能够提高group by的效率。

exist 

1. 外层表为驱动表，即先执行外层表的查询，按照小表驱动大表的原则，外层表应为小表。 

in 

1.  如果in条件里面是一个子查询，那么会先执行括号里面的子查询，按照小表驱动大表的原则，子查询的表应为小表。
2. 如果in条件里面是一个连续的数值，尽量替换成between。

join 

1. 遵守小表驱动大表的原则，left join左边为驱动表，right join右边为驱动表，inner join mysql会自动选择数据较少的表为驱动表。
2. 尽量用inner join。
3. 被驱动表的关联字段尽量建索引。
4. 尽可能的用where条件减小关联表的范围。
5. 适当的冗余字段减少join次数。
6. 如果被驱动表的join字段上无法建立索引，适当增加Join Buffer Size的大小。

索引 

1. 遵循最左侧前缀原则。
2. 不要在过滤条件中对索引字段使用函数、表达式，因为会使索引失效。
3. 不要在过滤条件中对索引字段进行隐式的数据类型转换，因为会使索引失效。
4. 存储引擎不能使用索引中范围条件右边的列，比如select * from student where name='aaa' and age>18 and phone='15111111111' ，其中name、age、phone都建立了索引，但是只有name、age用到了索引。
5. 使用覆盖索引，即查询的列都建立了索引，这个要根据实际情况考虑，没必要强求，比如要查询十几个字段，不可能给所有字段都加上索引。
6. 非条的查询会使索引失效（!=、<>、not in、not exists、not like），要做合理的转换。
7. is null, is not null 也无法使用索引，在实际中尽量不要使用null（避免在 where 子句中对字段进行 null 值判断） ，不过在mysql的高版本已经做了优化，允许使用索引。
8. like尽量使用后置模糊匹配。比如：'abc%。
9. 谨慎使用or，如果or前面是索引字段，后面不是索引字段，那么会使or前面的索引字段失效。
10. 在组合/联合索引中，将有区分度的索引放在前面。

11. 使用前缀索引（短索引），短索引不仅可以提高查询性能而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销，但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。比如有一个varchar(255)的列，如果该列在前10个或20个字符内，可以做到既使前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。为了减少key_len，可以考虑创建前缀索引，即指定一个前缀长度，可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

计算 

1. 尽量不在查询字段上使用函数、表达式计算等，把这写都放到业务层处理。