江苏龙网

  1. 主页 > 生活百科 > >

这么骚的SQL进阶技巧，不怕被揍么？( 四 )

SQL


⑧减少中间表
在 SQL 中 ， 子查询的结果会产生一张新表 ， 不过如果不加限制大量使用中间表的话 ， 会带来两个问题：一是展示数据需要消耗内存资源 ， 二是原始表中的索引不容易用到 ， 所以尽量减少中间表也可以提升性能 。
⑨灵活使用 HAVING 子句
这一点与上面第八条相呼应 ， 对聚合结果指定筛选条件时 ， 使用 HAVING 是基本的原则 ， 可能一些工程师会倾向于使用下面这样的写法：
SELECT *FROM (SELECT sale_date, MAX(quantity) AS max_qtyFROM SalesHistoryGROUP BY sale_date) TMPWHERE max_qty >= 10; 虽然上面这样的写法能达到目的 ， 但会生成 TMP 这张临时表 ， 所以应该使用下面这样的写法：
SELECT sale_date, MAX(quantity)FROM SalesHistoryGROUP BY sale_date HAVING MAX(quantity) >= 10; HAVING 子句和聚合操作是同时执行的 ， 所以比起生成中间表后再执行 HAVING 子句 ， 效率会更高 ， 代码也更简洁 。
⑩需要对多个字段使用 IN 谓词时 ， 将它们汇总到一处
一个表的多个字段可能都使用了 IN 谓词 ， 如下：
SELECT id, state, cityFROM Addresses1 A1WHERE state IN (SELECT stateFROM Addresses2 A2WHERE A1.id = A2.id)AND city IN (SELECT cityFROM Addresses2 A2WHERE A1.id = A2.id); 这段代码用到了两个子查询 ， 也就产生了两个中间表 ， 可以像下面这样写：
SELECT *FROM Addresses1 A1WHERE id || state || cityIN (SELECT id || state|| cityFROM Addresses2 A2); 这样子查询不用考虑关联性 ， 没有中间表产生 ， 而且只执行一次即可 。
?使用延迟查询优化 limit [offset] ， [rows]
经常出现类似以下的 SQL 语句：
SELECT * FROM film LIMIT 100000, 10 Offset 特别大!这是我司出现很多慢 SQL 的主要原因之一 ， 尤其是在跑任务需要分页执行时 ， 经常跑着跑着 Offset 就跑到几十万了 ， 导致任务越跑越慢 。
LIMIT 能很好地解决分页问题 ， 但如果 Offset 过大的话 ， 会造成严重的性能问题 。
原因主要是因为 MySQL 每次会把一整行都扫描出来 ， 扫描 Offset 遍 ， 找到 Offset 之后会抛弃 Offset 之前的数据 ， 再从 Offset 开始读取 10 条数据 ， 显然 ， 这样的读取方式问题 。
可以通过延迟查询的方式来优化 ， 假设有以下 SQL ， 有组合索引(sex ， rating)：
SELECT <cols> FROM profiles where sex='M' order by rating limit 100000, 10; 则上述写法可以改成如下写法：
SELECT <cols>FROM profilesinner join (SELECT id form FROM profiles where x.sex='M' order by rating limit 100000, 10) as x using(id); 这里利用了覆盖索引的特性 ， 先从覆盖索引中获取 100010 个 id ， 再丢充掉前 100000 条 id ， 保留最后 10 个 id 即可 ， 丢掉 100000 条 id 不是什么大的开销 ， 所以这样可以显著提升性能 。
?利用 LIMIT 1 取得唯一行
数据库引擎只要发现满足条件的一行数据则立即停止扫描 ，  ， 这种情况适用于只需查找一条满足条件的数据的情况 。
?注意组合索引 ， 要符合最左匹配原则才能生效
假设存在这样顺序的一个联合索引“col_1, col_2, col_3” 。这时 ， 指定条件的顺序就很重要 。
○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500; ○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ; × SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ; 前面两条会命中索引 ， 第三条由于没有先匹配 col_1 ， 导致无法命中索引 ，  另外如果无法保证查询条件里列的顺序与索引一致 ， 可以考虑将联合索引 拆分为多个索引 。
?使用 LIKE 谓词时 ， 只有前方一致的匹配才能用到索引(最左匹配原则)
× SELECT * FROM SomeTable WHERE col_1 LIKE '%a'; × SELECT * FROM SomeTable WHERE col_1 LIKE '%a%'; ○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%'; 上例中 ， 只有第三条会命中索引 ， 前面两条进行后方一致或中间一致的匹配无法命中索引 。
?简单字符串表达式
模型字符串可以使用 _ 时 ， 尽可能避免使用 % ， 假设某一列上为 char(5) 。


推荐阅读


上一篇：淘宝开店注意什么 为什么要开淘宝店

下一篇：淘宝如何提升自然搜索流量 分析淘宝店铺如何进行营销推广引流