10大sql书写规范实战技巧sql书写优化建议.docx
《10大sql书写规范实战技巧sql书写优化建议.docx》由会员分享,可在线阅读,更多相关《10大sql书写规范实战技巧sql书写优化建议.docx(10页珍藏版)》请在文库网上搜索。
1、SQL调优 | SQL 书写规范及优化技巧10 个sql书写规范及优化技巧:一、 使用延迟查询优化 limit offset, rows经常出现类似以下的 SQL 语句:SELECT * FROM film LIMIT 100000, 10offset 特别大!这是我司出现很多慢 SQL 的主要原因之一,尤其是在跑任务需要分页执行时,经常跑着跑着 offset 就跑到几十万了,导致任务越跑越慢。LIMIT 能很好地解决分页问题,但如果 offset 过大的话,会造成严重的性能问题,原因主要是因为 MySQL 每次会把一整行都扫描出来,扫描 offset 遍,找到 offset 之后会抛弃 of
2、fset 之前的数据,再从 offset 开始读取 10 条数据,显然,这样的读取方式问题。可以通过延迟查询的方式来优化假设有以下 SQL,有组合索引(sex, rating)SELECT FROM profiles where sex=M order by rating limit 100000, 10;则上述写法可以改成如下写法这里利用了覆盖索引的特性,先从覆盖索引中获取 100010 个 id,再丢充掉前 100000 条 id,保留最后 10 个 id 即可,丢掉 100000 条 id 不是什么大的开销,所以这样可以显著提升性能二、 利用 LIMIT 1 取得唯一行数据库引擎只要发现
3、满足条件的一行数据则立即停止扫描,这种情况适用于只需查找一条满足条件的数据的情况三、 注意组合索引,要符合最左匹配原则才能生效假设存在这样顺序的一个联合索引“col_1, col_2, col_3”。这时,指定条件的顺序就很重要。前面两条会命中索引,第三条由于没有先匹配 col_1,导致无法命中索引, 另外如果无法保证查询条件里列的顺序与索引一致,可以考虑将联合索引 拆分为多个索引。四、使用 LIKE 谓词时,只有前方一致的匹配才能用到索引(最左匹配原则)上例中,只有第三条会命中索引,前面两条进行后方一致或中间一致的匹配无法命中索引五、 简单字符串表达式模型字符串可以使用 _ 时, 尽可能避免
4、使用 %, 假设某一列上为 char(5)不推荐推荐六、尽量使用自增 id 作为主键比如现在有一个用户表,有人说身份证是唯一的,也可以用作主键,理论上确实可以,不过用身份证作主键的话,一是占用空间相对于自增主键大了很多,二是很容易引起频繁的页分裂,造成性能问题(什么是页分裂,请参考这篇文章)主键选择的几个原则:自增,尽量小,不要对主键进行修改七、如何优化 count(*)使用以下 sql 会导致慢查询原因是会造成全表扫描,有人说COUNT(*)不是会利用主键索引去查找吗,怎么还会慢,这就要谈到 MySQL 中的聚簇索引和非聚簇索引了,聚簇索引叶子节点上存有主键值+整行数据,非聚簇索叶子节点上则
5、存有辅助索引的列值 + 主键值,如下所以就算对 COUNT(*) 使用主键查找,由于每次取出主键索引的叶子节点时,取的是一整行的数据,效率必然不高,但是非聚簇索引叶子节点只存储了列值 + 主键值,这也启发我们可以用非聚簇索引来优化,假设表有一列叫 status, 为其加上索引后,可以用以下语句优化:SELECT COUNT(status) FROM SomeTable有人曾经测过(见文末参考链接),假设有 100 万行数据,使用聚簇索引来查找行数的,比使用 COUNT(*) 查找速度快 10 几倍。不过需要注意的是通过这种方式无法计算出 status 值为 null 的那些行如果主键是连续的,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 10 sql 书写 规范 实战 技巧 优化 建议