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)