MySQL 查询语句优化的十个原则
 2016-07-17  |   431  |   MySQL  |   MySQL优化

总结了一些 SQL 查询语句优化的基本原则,可以作为平时编写 SQL 语句的参考规范。

总结了一些 SQL 查询语句优化的基本原则,可以作为平时编写 SQL 语句的参考规范。

SQL 语句的优化是数据库优化的一个重要方式,对于 MySQL 而言,优化 SELECT 查询语句,在多数的情况下可以对 MySQL 的负载能力产生较为明显的提升。

原则1:避免直接再数据列上进行运算操作,这样可能会导致索引失效

例如 SQL 语句:

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

优化为:

SELECT * FROM table WHERE d >= '2011-01-01';

原则2:使用 JOIN 语句时,应该使用“小结果集”驱动“大结果集”,同时可以把复杂的 JOIN 语句拆分成多个查询来执行,因为使用多个 JOIN 时候,可能会导致更多的数据表锁定和堵塞

例如:

SELECT * FROM a</p><p>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;

原则3:使用 LIKE 子句时候,应当避免使用“%%”这样的匹配模式

例如:

SELECT * FROM table WHERE name LIKE '%de%';

优化为:

SELECT * FROM table WHERE name >= 'de' AND name < 'df';

原则4:只列出需要的列而不是*,虽然对查询速度没有太大优化,但可以节省内存,例如:

SELECT * FROM table;

优化为:

SELECT id, name, passwd FROM table;

原则5:多次使用 INSERT 插入数据可以优化成一次批量插入多条数据,可以节省底层交互时候的开销**

例如:

INSERT INTO table (id, name) VALUES(1, 'a');
INSERT INTO table (id, name) VALUES(2, 'b');
INSERT INTO table (id, name) VALUES(3, 'c');
INSERT INTO table (id, name) VALUES(4, 'd');

优化为:

INSERT INTO table (id, name) VALUES(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

原则6:LIMIT 子句基数比较大的时候,改为使用 BETWEEN

例如:

SELECT * FROM table ORDER BY id LIMIT 10 000 000, 10;

优化为:

SELECT * FROM table WHERE id BETWEEN 10 000 000 AND 10 000 010 ORDER BY id;

这样优化的原因在于,再海量的数据访问时候,BETWEEN 定位数据的速度要比 LIMIT 快许多,但是如果需要限制的数据列中从在断行或者跳行的时候,BETWEEN 语句读取的数量会小于预计的数量。

原则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;

虽然看起增加了复杂度,但相信这都是值得的。

原则8:避免使用 NULL

原则9:不使用 COUNT(id),改为用 COUNT(*)

原则10:尽量不做无谓的排序操作,尽可能在索引列中完成排序。

 分享: