MySQL memo优化_MySQL之SQL优化一

一、随机查询优化：

4.6G -rw-rw---- 1 mysql mysql 4.6G Oct 7 14:13 task_log.ibd

select 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 t2

WHERE t1.id >= t2.id

ORDER 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: 202439383

update_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:66

1 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.ibd

mysql> 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_log

where 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_log

where 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)