MySQL之SQL最佳化一

一、隨機查詢最佳化：4.6G -rw-rw---- 1 mysql mysql 4.6G Oct  7 14:13 task_log.ibdselect sql_no_cache * from task_log order by rand() limit 1;mysql> explain select sql_no_cache * from task_log order by rand() limit 1 ;+----+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |+----+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+|  1 | SIMPLE      | task_log | ALL  | NULL          | NULL | NULL    | NULL | 8822652 | Using temporary; Using filesort |+----+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+1.考慮不周的初始方法(沒有加入min(id) 查詢總是返回id靠近最大的一些值)SELECT *FROM task_log AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM task_log)) AS id) AS t2WHERE t1.id >= t2.idORDER BY t1.id ASC LIMIT 1;2.完善後的方法select * from task_log  where id>=(select floor(RAND()*((SELECT MAX(id) FROM task_log) - (SELECT MIN(id) FROM task_log)) + (SELECT MIN(id) FROM task_log))) limit 1;+----+-------------+----------+------+---------------+------+---------+------+---------+------------------------------+| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra                        |+----+-------------+----------+------+---------------+------+---------+------+---------+------------------------------+|  1 | PRIMARY     | task_log | ALL  | NULL          | NULL | NULL    | NULL | 8825321 | Using where                  ||  5 | SUBQUERY    | NULL     | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away ||  4 | SUBQUERY    | NULL     | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away ||  3 | SUBQUERY    | NULL     | NULL | NULL          | NULL | NULL    | NULL |    NULL | Select tables optimized away |+----+-------------+----------+------+---------------+------+---------+------+---------+------------------------------+

mysql> select sql_no_cache * from task_log  where id>=( select floor(RAND()*((SELECT MAX(id) FROM task_log) - (SELECT MIN(id) FROM task_log)) + (SELECT MIN(id) FROM task_log)) )  limit 1\G*************************** 1. row ***************************         id: 202439383update_time: 20130907000344       dist: 北京古都    account: (undefined)        gid:  task_type: xiux  task_name:     action: jiangl         ip: (undefined)       line: 北京古都十線      level: 0        tao: 0  gold_coin: 0    insider: 0      para1: 花妖      para2: :5226584700060AE34F76:      para3: 1       memo: martial:661 row in set (0.08 sec)二、分頁limit最佳化：缺點：資料必須是連續的，可以說不能有where條件，where條件會篩選資料，導致資料失去連續性。最佳化的關鍵：當limit的offset 值到很大時，limit的效率就下降，要求找准這個閥值。145M -rw-rw---- 1 mysql mysql 144M Oct  7 15:08 block_log.ibdmysql> select count(*) from block_log ; +----------+| count(*) |+----------+|   411923 |select sql_no_cache * from block_log limit 401922,5;5 rows in set (0.98 sec)select * from block_logwhere id between         (select sql_no_cache id from block_log order by id asc limit 401923,1)         and         (select sql_no_cache id from block_log order by id asc limit 401927,1);5 rows in set (0.34 sec)select * from block_logwhere id >= ( select sql_no_cache id from block_log order by id asc limit 401923,1)limit 5;5 rows in set (0.17 sec)