MySQL语句优化10项基本原则

原则1、尽量避免在列上进行运算，这样会导致索引消失

1

SELECT * FROM table WHERE YEAR(d) > 2011;

优化为：

1

SELECT *FROMtableWHEREd>'2011-01-01';

原则2、使用JOIN联表时，应用最小结果集驱动最大结果集。同时吧复杂的JOIN查询拆分为多个Query。因为JOIN多张表时，可能会导致更多的锁表和堵塞。

1

2

3

4

SELECT   *  FROM  a  JOIN  b  ON  a.id  = b.id

LEFT JOIN  c  ON  c.time  =  d.date

LEFT JOIN  d  ON  c.pid  =  b.aid

LEFT JOIN  e  ON  e.cid  =  a.did;

原则3、注意LIKE模糊查询的使用，避免使用 %..% 。

例如原句为：

1

SELECT * FROMtable WHERE name LIKE %de%;

优化为：

1

SELECT  *  FROM  table  WHERE   name >=  'de'   AND  name  < 'df';//局限性很大[慎重]

原则4、仅列出需要查询的字段，这对速度不会有明显的影响，主要考虑节省内存。

例如原句为：

1

SELECT * FROM tabel;

(据说有公司见遇到一个 * 号 扣10块钱~~~   )

优化为：

1

SELECT  id,name,age   FROM  table;

(需要多少取多少，是 * 的话，如果无用字段数据过大则内存消耗比较大)

原则5、使用批量插入语句节省数据库交互。

例如原语句：

1

2

3

INSERT INTO table (id,name) VALUES(1,'sunphp');

INSERT INTO table (id,name) VALUES(2,'mysql');

INSERT INTO table (id,name) VALUES(3,'优化');

优化为：

1

2

INSERT INTO table (id,name) VALUES(1,'sunphp'),

VALUES(2,'mysql'),VALUES(3,'优化');

原则6、Limit的基数比较大时使用between。

例如原句为：

1

SELECT * FROM article ORDERBY id LIMIT 1000000,10;

优化为：

1

SELECT  * FROM  article  WHERE  id BETWEEN  1000000  AND  1000010  ORDER  BY  id;

Between 限定比Limit快，所以在海量数据访问时，建议用Between 或是 where  替换掉 limit。

但是between 也有缺陷，如果id中间有断行或是中间部分id不读取的情况，总读取量可能小于预计读取量(有损服务)。

在去比较靠后的数据时，通过 desc 方式吧数据反向查找，以减少对前面数据的扫描，让 limit 得基数越小越好。

原则7、不要使用 rand 函数获得随机多条记录。

1

SELECT * FROM table ORDERBY rand() LIMIT 20;

使用下面的语句代替：

1

2

3

4

5

SELECT *FROM table ASt1

JOIN (

SELECT ROUND( RAND() * ( SELECT MAX(id) FROMtable )-( SELECTMIN(id) FROMtable ) ) +(SELECT MIN(id)FROM table)

) AS id

) AS t2 WHERE t1.id >= t2.id ORDERBY t1.id LIMIT t1;

这是获得一条随机记录，这样即使执行20次，也比原来的语句效率高。或者先用PHP产生随机数，把这些字符传给MySQL，MySQL 里面使用 in 查询。

原则8、避免使用 null。

原则9、不要使用 count(id)，而应该使用 count(*)。

原则10、不要做无谓的排序操作，而应该尽可能在索引中完成排序。

http://wudi.in/archives/127.html