Mysql SQL优化方法

1. 分析表

  analyze table payment; //主要目的是重新分析表，便于执行计划的选择。因为可能之前统计的数据会有偏差，如果发现mysql执行计划选择有偏差，可以执行这个sql。需要注意，这个sql会锁表

2. 优化表

optimize table payment //如果表删除了比较多的数据，建议运行这个sql语句。可以回收空间，回收空间之后，查询扫描的时候，就不用扫描已经被删除的块了。

3. 优化order by

尽量减少额外排序，通过索引来返回有序数据。where条件和order by 使用相同的索引，并且order by 的顺序和索引顺序相同，并且order by 都是降序或者升序。

如果用不到索引，那就会用到filesort. 需要考虑max_length_for_sort_data和sort_buffer_size字段。

4. 优化group by 

mysql默认会对group by的字段进行排序，如果不想要排序，需要指定order by null。如果这么做，可避免filesort操作，因为filesort比较耗时

5. 优化嵌套查询（子查询）

select  * from customer where customer_id not in(select customer_id from payment); //查找没有支付过的用户

优化后的查询：

select * from customer a left join payment b on a.customer_id = b.customer_id where b.customer_id is null;

因为mysql5.5及以后的版本，子查询效率不如join，因为mysql不需要创建临时表来保存子查询的结果。

6. 优化分页查询

由于mysql limit1000,20，此时会先排序出前1020条记录，然后仅返回最后20条。排序的前面1000条全部丢弃，大大影响效率。优化方法2种：

6.1  select a.film_id, a.description from film  a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_id; //limit的时候只查出主键，然后再去关联查询

6.2 select * from payment where rental_id <15640 order by rental_id desc limit 10; //每次查询的时候，上一次的查询结果最小id

7. 随机抽取数据

select * from category order by rand() limit 5; //随机取5条数据