MYSQL——SQL优化继上篇

9)优化分页limit进行分页的时候limit 1,20 21,20.......10001,20.当进行到后面的时候，比如limit 10001,10020就会非常慢。可以采用在查询第一次的时候把最后数据的id记录下来，下面的uuid0就是上一次的id,uuid1是一个预估范围。在下一次查询的时候就可以select * from tablename where id>uuid0 and where id<uuid1 order by id limit 10001,20

测试：表数据工5万多条

一个普通的limit写法：

EXPLAIN SELECT * FROM user_admin ORDER BY admin_id LIMIT 10000,20 

id select_type table type possible_keys key key_len ref rowsExtra 

1 SIMPLE user_admin index (NULL) PRIMARY 4 (NULL) 10020 (NULL) 

可以看到rows:10020扫描了10020条数据。如果表数据很大的话后面的分页性能会急剧下降。

好吧，优化

预估范围

EXPLAIN SELECT * FROM user_admin WHERE admin_id<10030 AND admin_id>10000 ORDER BY admin_id ASC LIMIT 20

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE user_admin range PRIMARY PRIMARY 4 (NULL) 28 Using where 

ok

当表数据很庞大的时候也可以使用延迟关联的方式提高效率

EXPLAIN SELECT * FROM user_admin INNER JOIN ( SELECT admin_id FROM user_admin ORDER BY admin_id ASC LIMIT 10000,20) aUSING(admin_id)

id select_type table type possible_keys key key_len ref rowsExtra 

1 PRIMARY<derived2> ALL (NULL) (NULL) (NULL) (NULL) 10020 (NULL) 

1 PRIMARY user_admin eq_ref PRIMARY PRIMARY 4 a.admin_id 1 (NULL) 

2 DERIVED user_admin index (NULL) PRIMARY 4 (NULL) 57252 Using index

可以看出预估扫描的行数是非常多的，笔者进行了各种交换顺序也未能有效解决扫描行数多的情况。所以这种情况一定要做测试。。一般不建议采用。。

是否使用此种方法还是要具体问题具体分析。

还有一种较为普遍的做法就是事先将一定的数据量做缓存，比如缓存1000条数据（常用的数据），这样在取前面10页或者几页的时候就直接在缓存中获取。应用程序可以根据结果集的大小采取不同的策略。如果少于1000就直接在页面上显示所有的分页链接。大于1000时再去数据库按上述方法取数据。

优化总则：

1）索引优化正确的使用索引

2）查询需要的数据ninnodb引擎的话where条件中有主键时可以直接使用select *,因为innodb的主键中存储了其他列信息，如果不含主键在查询时尽量只查询需要的字段。

3）切分查询切分查询是将一个查询分多次执行。比如一次较大的delete可以分几次来执行

4）分解关联查询分解关联查询就是将一个大的关联查询分解为多个小的查询，一次查询出一部分数据，可以有效的使用缓存。但是不一定是所有的查询都要进行分解，因为分解后的查询语句可读性不强，对于以后的维护会有一定的困难。

总结：优化查询语句需要从几方面入手考虑：

1、 减少数据访问（减少磁盘访问）

2、 返回更少数据（减少网络传输或磁盘访问）

3、 减少交互次数（减少网络传输）

4、 减少服务器CPU开销（减少CPU及内存开销）

5、 利用更多资源（增加资源）