数据库技术作为现代信息系统最重要的组成部分,其查询速度往往直接影响到系统的响应速度。合理规范正确的书写SQL查询语句往往能大大提高系统的访问速度。现在就总结9条常见的能极大提升SQL查询效率的SQL写法。
)使用正则regexp_like代替LIKE
如下例所示,当我们要进行模糊匹配的时候(尤其是匹配项很多的时候),我们使用regexp_like代替LIKE可以提高效率。
SELECT * FROM phones WHERE lower(name) LIKE '%samsing&' OR lower(name) LIKE '%apple&' OR lower(name) LIKE '%htc&' OR高效代码
SELECT * FROM phones WHERE REGEXP_LIKE(lower(name),'samsung|apple|htc')2)使用regexp_extract代替 Case-when Like
SELECT * CASE WHEN concat(' ', name, ' ') LIKE '%acer%' then 'Acer' WHEN concat(' ', name, ' ') LIKE '%samsung%' then 'Samsung' WHEN concat(' ', name, ' ') LIKE '%dell%' then 'Dell' AS brand FROM laptops高效代码
SELECT regexp_extract(name,'(acer|samsung|dell)') AS brand FROM laptops3)IN子句转换为临时表
SELECT * FROM table1 as t1 WHERE itemid in (3363134, 5343, 5555555)高效代码
SELECT * FROM table 1 as t1 JOIN ( SELECT itemid FROM ( SELECT split('3363134, 5343, 5555555') as bar ) CROSS JOIN UNNEST(bar) AS t(itemid) ) AS table2 as t2 ON t1.itemid = t2.itemid4)将 JOIN 的表从大到小排序
SELECT * FROM small_table JOIN large_table ON small_table.id = large_table.id高效代码
SELECT * FROM large_table JOIN small_table ON small_table.id = large_table.id5)使用简单的表关联条件
SELECT * FROM table1 a JOIN table2 b ON a.date = CONCAT(b.year, '-', b.month, '-', b.day)高效代码
SELECT * FROM table1 a JOIN ( SELECT name, CONCAT(b.year, '-', b.month, '-', b.day) as date FROM table2 b ) new ON a.date = new.date6)分组的字段按照类别取值种类数排序
SELECT main_category, sub_category, itemid sum(price) FROM table1 GROUP BY main_category, sub_category, itemid高效代码
SELECT main_category, sub_category, itemid sum(price) FROM table1 GROUP BY itemid, sub_category, main_category7)避免 WHERE 子句中的子查询
SELECT sum(price) FROM table1 WHERE itemid in ( SELECT itemid FROM table2 )好代码
WITH t2 AS (SELECT itemid FROM table2) SELECT Sum(price) FROM table1 AS t1 JOIN t2 ON t1.itemid = t2.itemid8)取最大直接用Max而非Rank后取第1
SELECt * FROM ( SELECT userid, rank() over (order by prdate desc) as rank FROM table 1 ) WHERE ranking = 1高效代码
SELECT userid, max(prdate) FROM table1 GROUP BY 19)其他优化点