mysql优化

Limit子句

1. 介绍

Limit子句可以被用于强制 SELECT 语句返回指定的记录数。Limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

    //初始记录行的偏移量是 0(而不是 1)：
　　mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15

　　//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
　　mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last

　　//如果只给定一个参数，它表示返回最大的记录行数目。换句话说，LIMIT n 等价于 LIMIT 0,n：
　　mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行

参数：OFFSET

为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
　　经常用到在数据库中查询中间几条数据的需求
　　比如下面的sql语句：
　　　　① selete * from testtable limit 2,1;
　　　　② selete * from testtable limit 2 offset 1;
　　注意：
　　　　1.数据库数据计算是从0开始的
　　　　2.offset X是跳过X个数据，limit Y是选取Y个数据
　　　　3.limit X,Y 中X表示跳过X个数据，读取Y个数据
　　这两个都是能完成需要，但是他们之间是有区别的：
　　　　①是从数据库中第三条开始查询，取一条数据，即第三条数据读取，一二条跳过
　　　　②是从数据库中的第二条数据开始查询两条数据，即第二条和第三条。

2. 优化

高效率的原理是：避免全表扫描，提高查询效率

Limit的问题在于：受偏移量的影响很大，当偏移量大到一定后性能开始大幅下降

优化：
1）使用where等查询语句，配合limit使用

语句1：
    select * from table limit 150000,1000;
语句2:
    select * from table while id>=150000 limit 1000;
　　
    语句1为0.2077秒；语句2为0.0063秒
    两条语句的时间比是：语句1/语句2＝32.968

2）如果这张表非常大，列特别多。尽可能的使用覆盖索引扫描，而不是查询所有的列。即，根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。

优化前：
    SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

优化后：
    SELECT film.film_id,film.description FROM film INNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50,5) AS tmp USING(film_id);

注：
    USING(film_id)相当于on film.film_id = tmp.film_id

ps：在sql语句中，limt关键字是最后才用到的。以下条件的出现顺序一般是：where->group by->having-order by->limit