序言
本文主要对日常使用的SQL优化进行总结及测试
1. 测试环境
测试用户 | 测试模式 | 测试表 | 字段 | 数据量 | 索引 |
LYT | LYT | TB1 | ID INT NAME VARCHAR | 10W | IDX_ID IDX_NAME |
相关字段
数据统计
索引统计
数据插入存储过程
2. SQL优化实测
2.1 >= <= !
应用场景:系统作不等于判断时。
!=
<>
>= <=
通过执行计划可以看出>= <=执行代价远小于!、<>。
批注:由执行计划操作符可知,!及<>走聚集索引扫描,从结果集看应是全表扫描。而>=、<=走二级索引数据定位、定位查找。
2.2 IS NULL IS NOT NULL NVL()
应用场景:系统作空值判定及非空判定
IS NULL
IS NOT NULL
NVL()
通过执行计划可以看出IS NULL的执行代价和结果集远小于IS NOT NULL、NVL()。
批注:有别于ORACLE等数据库,从执行计划可以看出,达梦数据库对IS NULL的执行进行过优化,其sql执行使用了索引查找。
2.2 单表 IN OR BETWEEN
应用场景:系统作范围判定(查询值可能连续可能不连续)
IN
OR
BETWEEN
通过执行计划可以看出IN 和OR的执行代价和结果集完全一致,BETWEEN的执行代码和结果集小于IN/OR。
批注:达梦数据库在进行连续值查询时,建议使用BETWEEN代替IN/OR。在对不连续值进行查询时,IN/OR的执行在达梦数据库中完全一致,考虑是执行默认进行了优化。
2.3 多表 IN(OR) EXISTS
在此考虑新建一张参展表TB2,新建一个存储过程。
TB2
测试用户 | 测试模式 | 测试表 | 字段 | 数据量 | 索引 |
LYT | LYT | TB2 | TB1_ID INT ID INT NAME VARCHAR(128) | 10W | IDX_TB2_ID IDX_TB2_NAME |
CREATE TABLE LYT.TB2(
ID INT,
TB1_ID INT,
NAME VARCHAR(129)
);
MAKEDATA2()
CREATE OR REPLACE PROCEDURE MAKEDATA2
AS
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO LYT.TB2(TB1_ID,ID,NAME) VALUES(CAST(CEILING(RAND()*2000000) AS INT),I, 'TKS');
END LOOP;
END;
IN
EXISTS
通过执行计划可以看出IN和EXISTS的执行代价有着较小的差距,EXISTS略优于IN。笔者在做测试时,尝试删除ID>50000此判定然后观察
IN
EXISTS
批注:在只有联表判定时,两者执行计划中代价和结果集完全一致,在此情况下IN效率等同于EXISTS。
2.4 LIKE REGEXP INSTR()
应用场景:系统进行字符串模糊查找时
LIKE1:
LIKE2:
LIKE3:
INSTR()4:
通过执行计划可以看出1-5执行计划代价由低到高依次为:
2>>1=3=4
批注:当使用LIKE+通配符时,'%VALUE%'及'%VALUE'都不会走索引,INSTR()函数相同,只有'VALUE%'走索引查找。
通过正则表达式进行字符串查找
批注:当使用正则表达式时,同样进行的全表扫描。
更多达梦相关技术文档可访问