(2022.04.22 Fri)
SQL语句的优化目的在于提高SQL语句的运行效率。注意SQL优化和数据库优化的区别。
SQL语句的优化有很大一部分是和SQL索引有关，善用索引避免全表扫描可以提升运行效率。
利用索引
- 避免全表扫描，在WHERE和ORDER BY涉及的列上建立索引
以下列出的各种查询方法都会避开索引而直接使用全表扫描，建议使用修改建议做查询。
- 使用IN/NOT IN的地方，可用
=代替，或EXIST，或>=,<=，或BETWEEN代替，
# 低效
SELECT name_field, score_field
FROM table_a
WHERE score_field IN (90, 91, 92);
############################
SELECT name_field, score_field FROM table_a
WHERE score_field >= 90 AND score_field <=92;
############################
SELECT name_field, score_field FROM table_a
WHERE score_field = 90
UNION ALL
SELECT name_field, score_field FROM table_a
WHERE score_field = 91
UNION ALL
...
############################
SELECT name_field, score_field FROM table_a
WHERE score_field BETWEEN 90 AND 92;
-
NULL值作为查询条件。建议在创建表示设置默认值非NULL，比如0
SELECT * FROM table_a
WHERE some_field IS NULL;
###########################
SELECT * FROM table_a
WHERE some_field = 0;
- 使用
OR作为条件从句，建议使用UNION ALL代替。
SELECT * FROM table_a
WHERE sid = 10 OR sid = 20;
##########################
SELECT * FROM table_a
WHERE sid = 10
UNION ALL
SELECT * FROM table_a
WHERE sid = 20;
-
'%abc%'的首字母是%的检索方式，在大数据量的情况建议换成'abc%'，或者在MySQL中用INSTR(field, substr)指令检索。如果是小数据量的数据，可使用'%abc%'指令查询
SELECT * FROM table_a
WHERE content LIKE '%abc%'
##########################
SELECT * FROM table_a
WHERE content LIKE 'abc%'
##########################
SELECT * FROM table_a
WHERE INSTR(content, 'abc');
- 在
WHERE语句中对字段进行表达式或函数操作，转换为其他；或WHERE子句中=左侧的运算应放在右侧
SELECT * FROM table_a
WHERE some_field/10 > 10;
#########################
SELECT * FROM table_a
WHERE some_field > 100;
SELECT * FROM table_a
WHERE SUBSTRING(some_field, 1, 3) = 'abc';
#########################
SELECT * FROM table_a
WHERE some_field LIKE 'abc%';
- 在
WHERE从句中使用的!=,<>，建议用其他方式和语法代替
SQL优化
-
SELECT语句中用
SELECT <col_names>代替SELECT *，避免找出不需要的字段，占用过多CPU资源。另，查全部变量不会使用覆盖索引(?)从而降低了查询效率 -
JOIN查询代替子查询。子查询往往涉及
IN操作，而IN操作不仅会使用全表扫描，还会生成临时表，等查询结束再销毁临时表，给系统造成负担。用JOIN操作代替
SELECT a.name, a.id, b.subjects, b.score
FROM stu_info a
INNER JOIN stu_score b
ON a.id = b.id;
-
能用
UNION的语句尽量换成UNION ALL语句。这两个指令都可进行结果的合并与整理，但UNION在执行过程中会对重复的选项做筛选，之后进行排序，在返回结果前会将重复的选项重新加入到返回结果中。UNION ALL对重复的数据不做操作，不对数据做排序处理。显然后者的效率更高。 -
使用表的别名代替表名，减少解析时间和友列歧义导致的语法错误
-
调整
WHERE子句中的筛选字段连接顺序。MySQL中是自左向右、自上而下的顺序对变量做筛选，应将过滤数据多的条件往前放，最快缩小数据集 -
小表驱动大表 - 多表关联查询(JOIN)时，在MySQL中执行WHERE语句中的表从左到右查询(Oracle相反)，第一张表会涉及全表扫描，所以把小表放在前面， 大表放在后面，提高效率。能用
INNER JOIN尽量不用LEFT JOIN -
考虑语句的执行顺序，在先执行的
WHERE子句中先筛选数据，为后面的操作提供更小的数据，用于取代HAVING子句做数据筛选 -
创建数据表时，字段的类型
varchar比char好，因前者更灵活，后者占用空间固定且检索效率低
网友评论