首页 > 代码库 > SQL基本语句的优化10个原则
SQL基本语句的优化10个原则
原则一:尽量避免在列上进行运算,这样会导致索引失效。
例如: SELECT * FROM table WHERE YEAR(d) >= 2011;
优化: SELECT * FROM table WHERE d >= ‘2011-01-01‘;
原则二:使用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 = b.aid
LEFT JOIN e ON e.cid = a.did
原则三:注意LIKE模糊查询的使用,避免%%。
例如:
SELECT * FROM table WHERE name LIKE ‘%de%‘;
优化: SELECT * FROM table WHERE name >= ‘de‘ AND name < ‘df‘;
原则四:仅列出需要查询的字段,这对速度不会有明显的影响,主要考虑节省内存。
例如:
SELECT * FROM table;
优化:
SELECT id,name,pwd FROM table;
原则五:使用批量插入语句节省交互。
例如:
INSERT 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‘);
原则六:LIMIT 的基数比较大时使用 BETWEEN。
例如:
SELECT * FROM article ORDER BY id LIMIT 100000,10
优化:
SELECT * FROM article WHERE id BETWEEN 100000 AND 100010 ORDER BY id;
BETWEEN 限定比 LIMIT 快,所以在海量数据访问时,建议用 BETWEEN 或是 WHERE 替换掉 LIMIT 。但是 BETWEEN 也有缺陷,如果 id 中间有断行或是部分 id 不读取的情况,总读取的数量会少于预计数量! 在读取比较后面的数据时,通过 DESC 方式把数据反向查找,以减少对前端数据的扫描,让 LIMIT 的基数越少越好!
原则七:不要使用 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;
这是获取一条随机记录,这样即使执行 20 次,也比原来的语句高效。或者先用PHP产生随机数,把这个字符串传给MySQL,MySQL里用IN查询。
原则八:避免使用NULL。
原则九:不要使用 COUNT(id) ,而应该是 COUNT(*)。
原则十:不要做无谓的排序操作,而应尽可能在索引中完成排序。
SQL基本语句的优化10个原则