MySQL查询优化

1. COUNT与LIMIT

  COUNT(*) 计算满足条件的记录行数,COUNT(col)计算满足条件的且col非空的记录行数

  LIMTI n 在满足条件的记录中查询n条,发现n条后停止扫描

  需要判断记录是否存在时可以用LIMIT取代COUNT,LIMIT 1 找到记录就会停止

mysql> select count(id) from test where tkey='1pNEFf3mudsuVYVxK0AMZs9cqnpJkf5y' \G
*************************** 1. row ***************************
count(id): 1
1 row in set (1.92 sec)

mysql> select id from test where tkey='1pNEFf3mudsuVYVxK0AMZs9cqnpJkf5y' limit 1 \G
*************************** 1. row ***************************
id: 1500000
1 row in set (0.85 sec)

mysql> select exists(select id from test where tkey='1pNEFf3mudsuVYVxK0AMZs9cqnpJkf5y') \G
*************************** 1. row ***************************
exists(select id from test where tkey='1pNEFf3mudsuVYVxK0AMZs9cqnpJkf5y'): 1
1 row in set (0.85 sec)

2.ORDER BY RAND()

  随机取数据时使用ORDER BY RAND()效率极低,可以使用其它方式来实现,如取随机数交给程序或者函数去做,或者使用RAND()随机取一条再UNION也会比RAND()快很多

数据量大时该方法速度极其慢
mysql> select * from test where id < 2000000 order by rand() limit 1 \G
*************************** 1. row ***************************
id: 1145236
tname: bSCkE5j9DASrqENRN
tstat: 0
tkey: tDAPfzXUyRvK3T9SOfElE0YFe2icZaGN
tsort: 59.42
sid: 2
created_ts: 2011-12-15 11:59:52
1 row in set (1 min 37.10 sec)

拷贝网络上他人SQL 随机取一条记录, 速度挺快.
mysql> SELECT *
-> FROM `test` AS t1 JOIN ( SELECT ROUND ( RAND () * (( SELECT MAX( id ) FROM `test` ) - ( SELECT MIN( id ) FROM `test`))
-> + ( SELECT MIN( id ) FROM `test` )) AS id ) AS t2
-> WHERE t1 . id >= t2 . id
-> ORDER BY t1 . id LIMIT 1 \G
*************************** 1. row ***************************
id: 3248895
tname: ujVxM89euDxEtdyVO97YoRX5moHa
tstat: 2
tkey: ixEvojbRAd9XbQy3rV8J7fGxvIS8QFF8
tsort: 37.81
sid: 5
created_ts: 2011-12-15 15:31:47
id: 3248895
1 row in set (0.03 sec)




posted on 2011-12-15 17:20  BobbyPeng  阅读(1330)  评论(0编辑  收藏  举报

导航