MySQL SQL optimization 1

I. Random query optimization: 4.6G-rw ---- 1 mysql 4.6G Oct 7 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; + ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + hour + | 1 | SIMPLE | task_log | ALL | NULL | 8822652 | Using temporary; using filesort | + ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + ------------------------------- + 1. consider the week-wise initial method (if min (id) is not added, the query always returns some values whose id is near the maximum) 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. the improved method 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; + ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ---------- + ------ + --------------- + ------ + --------- + hour + | 1 | PRIMARY | task_log | ALL | NULL | 8825321 | Using where | 5 | SUBQUERY | NULL | Select tables optimized away | 4 | SUBQUERY | NULL | NULL | Select tables optimized away | 3 | SUBQUERY | 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: Beijing Ancient Capital account: (undefined) gid: task_type: xiux task_name: action: jiangl ip: (undefined) line: first line of Beijing Ancient Capital El: 0 tao: 0 gold_coin: 0 insider: 0 para1: Demon para2: 5226584700060AE34F76: para3: 1 memo: martial: 661 row in set (0.08 sec) ii. Paging limit optimization: disadvantage: the data must be continuous. It can be said that there is no where condition, and the where condition filters data, resulting in data loss of continuity. The key to optimization: When the offset Value of the limit is large, the efficiency of the limit is reduced, and the threshold value needs to be identified. 145 M-rw ---- 1 mysql 144 M Oct 7 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)