第四、优化limit查询：

limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用filesorts这样会造成大量的IO问题。

mysql> explain SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

|  1 | SIMPLE   | film  | ALL  | NULL      | NULL | NULL  | NULL |  914 | Using filesort|

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

分析：使用了ALL表扫描，查询了914行，使用了文件排序，所以效率比较低。

优化limit步骤1：使用有索引的列或主键进行Order by操作。

mysql> explain select film_id,description From sakila.film order by film_id limit 50,5;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

| id | select_type | table | type  | possible_keys | key  | key_len | ref  |rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

|  1 | SIMPLE  | film  | index | NULL  | PRIMARY | 2       | NULL |  55 |  |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

分析：使用了主键索引，查询行数55行，因为我们查询是从50行开始取5行，所以55行已经 是扫描的最少行数了。没有extra项，效率有所提高，但这样是不是优化就完成了没有 其他问题了呢？

mysql> explain select film_id,description From sakila.film order by film_id limit 500,5;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

| id | select_type | table | type  | possible_keys | key  | key_len | ref  |rows | Extra |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

|  1 | SIMPLE      | film  | index | NULL     | PRIMARY | 2       | NULL | 505 | |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

分析：随着limit开始值的增大，它的扫描行数也随着增到505行，可见优化还没有完善。

优化步骤2：记录上次返回的主键，在下次查询的时候使用主键过滤。

mysql> explain select film_id,description from sakila.film where film_id >55 and film_id<=60 order by film_id limit 1,5;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table | type  | possible_keys | key | key_len | ref  |rows | Extra  |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

|  1 | SIMPLE  | film  | range | PRIMARY | PRIMARY | 2 | NULL |   5 | Using where |

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

分析：range代表使用了索引的范围查找，排序用的主键，扫描行数为5行，所以效率进一步得 到提升。避免了数据量大的时候扫描过多的记录。但应该注意，这种优化的方法很精确 的锁定了要查询行的范围，所以在整个表中进来避免有不连续的主键或者空行，否则会 出现查询结果不足5行。如果真有不连续或者空行，我们可以再这个表中建一个附加列，

只要保证此列连续且有索引，也可以实现同样的效果。

第三章：索引优化

一、如何选择合适的列建立索引？

1.在where从句，group by从句，order by从句，on从句中出现的列。

2.索引字段越小越好（一页存储的数据越多，IO获取的数据量就越大，效率越高）

3.离散度大的列放到联合索引的前面

select * from from payment where staff_id=2 and customer_id = 584;

是index(sftaff_id,customer_id)好？还是index(customer_id,staff_id)好？

mysql> select count(distinct customer_id),count(distinct staff_id) from payment;

+-----------------------------+--------------------------+

| count(distinct customer_id) | count(distinct staff_id) |

+-----------------------------+--------------------------+

|              599 |           2     |

+-----------------------------+--------------------------+

分析：通过计算字段的唯一值的多少，唯一值越多的离散度越好越高。

因为customer_id的离散度更大，所以使用index(customer_id,staff_id)好。