菜单

有关mysql优化的准则

2016-03-01 - lnmp

前辈们总结的经验,学习、学习。

1.尽量避免在列上进行运算,这样会导致索引失效。

例如:SELECT * FROM t WHERE YEAR(d) >=2011;

优化为:SELECT * FROM t WHERE d >=’2011-01-01′;

2.使用JOIN时,应该用小结果集驱动大结果集。同时把复杂的JOIN查询拆分成多个QUERY。因为JOIN多个

表时,可能导致更多的锁定和堵塞。

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

LEFT JOIN c ON c.time=a.date

LEFT JOIN d ON c.pid=d.aid

LEFT JOIN e ON e.cid=a.did

3.LIKE模糊查询的使用,避免%%

例如:SELECT * FROM t WHERE name LIKE ‘%de%’;

优化为:SELECT * FROM t WHERE name >=’de’ AND name<‘df’;

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

5.使用批量插入语句节省交互

例如:

INTO t (id,name) VALUES (1,’a’);

INSERT INTO t (id,name) VALUES (2,’b’);

INSERT INTO t (id,name) VALUES (3,’c’);

优化:INSERT INTO t (id,name) VALUES (1,’a’),(2,’b’),(3,’c’);

6.limit的基数比较大时使用between

SELECT * FROM article AS article ORDER BY id LIMIT 100000,10;

优化:SELECT * FROM article AS article WHERE id BETWEEN 100000 AND 100010 ORDER BY id;

7.不要使用rand函数获取多条随机记录

SELECT * FROM table ORDER BY rand() LIMIT 20;

优化:

SELECT * FROM ‘table’ AS t1 JOIN (SELECT ROUND (RAND() * ((SELECT MAX(id) FROM ‘table’)-(SELECT MIN(id) FROM ‘table’ )) + (SELECT MIN(id) FROM ‘table’ )) AS id) AS t2 WHERE t1.id>=t2.id ORDER BY t1.id LIMIT 1;

8.避免使用NULL

9.不要使用count(id),而应该是count(*)

10.不要做无谓的排序,而应尽可能在索引中完成排序。

 

转载请注明: 转载自—艾瑞可erik

本文链接地址: http://erik.xyz/1179.html