数据库DBA专家：教你如何做好SQL优化性能优化最佳实践

欢迎关注我的头条号：Wooola，10年Java软件开发及架构设计经验，专注于Java、Golang、微服务架构，致力于每天分享原创文章、快乐编码和开源技术。

作者 | 陈虹君 DBA专家 授权头条原创首发

一、SQL优化整体思路

性能优化层次

SQL响应时间分析

T=响应时间，C=开销，V=吞吐量

性能优化的目标：

1. 缩短响应时间
2. 减少资源开销
3. 提高系统吞吐量

Sql 优化基本原则

1. 减少磁盘I/O：减少数据库访问
2. 减少网路传输：减少交互次数
3. 减少复杂计算：减少CPU开销
4. 增加资源：利用多线程并发处理

SQL调优思路

SQL语句执行过程

绑定变量

二、表设计在SQL优化中的应用

命名规范

表相关对象的命名建议项目组统一用业务名称命名，做到见名知意

1. ORACLE不区分大小写

2. MYSQL在LINUX环境下区分大小写,在Windows环境下不分区大小写

最大长度限制 表名：30 其它：18不要使用数据库保留关键字，不要使用空格以及特殊符号，特别注意不要使用数据库保留关键字，不要使用空格以及特殊符号，特别注意编写脚本时，不要带双引号

合理进行范式化设计

TIPS：并不是范式化程度越高的设计就越好，适当反范式化设计，可以达到以空间换时间的目的。

反范式化设计：增加字段-冗余列，派生列

不同表类型特点

表设计规范

表设计你应该考虑：预期的数据量，数据保留周期，数据清理策略，字段查询频率

1. 选择合适的表类型
2. 必须设计主键，可以不用设计外键（除非是关系非常紧密---特别是MySql）
3. 字段必须设置合理的约束（例如：业务上非空的必须设置NOT NULL ，业务上唯一的必须设置UNIQUE）
4. 选择合适的字段类型
5. 尽量避免宽表（控制少而精，提升IO效率，缓存更多有效数据）
6. 大表考虑分表，分区（针对MYSQL 甚至分库）
7. 慎用触发器（尽量使用程序来实现）

正确匹配数据类型

1. integer,char,varchar …… 请暂时忘了它,常用的记住上面几种就行
2. oracle的隐式转换功能 …… 请忘掉它（当然也包括MySQL）

请别再让字符、数字、时间混用了

1. 不允许用字符类型存放时间或日期类数据。
2. 不允许用字符类型存放数值类型的数据。
3. 不允许表中字段数值类型直接使用INT型，应明确写明字段的取值范围，如number(8)。int 型在数据库中表示为number(38),造成存储空间浪费。
4. 不允许表中长度不固定的字段字符类型直接使用CHAR、VARCHAR，应用VARCHAR2定义；
5. 如无特别需要，避免使用大字段(blob，clob，long，text，image等)，假设要使用可以使用SecureFiles 新一代LOB。
6. 同一个字段名在一个数据库中只能代表一个意思，不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。

主外键与约束设计

一般有一个设计原则，两个表有主外建关系时，外键的列上要创建索引，这样有两大好处，1.关联查询更高效

2.外键所在的表更新不容易产生死锁

Sharding 之 分库分表分区

表分区技术基本原理

分区技术的效益和目标

分区类型选择

三、索引设计加速数据检索

索引与数据检索

创建索引以进行有效率的数据查询

Ø 数据量少时，即便没有索引，全表搜索也不成问题

Ø 数据量大时，搜索性能问题（特别是全表搜索）才会产生

B+树索引结构

索引设计规范

1. 合理选择单列索引与组合索引
2. 应用模糊查询时严禁左模糊匹配与全模糊匹配
3. 多表关联查询时确保被关联字段存在索引，且确保需要JOIN的字段数据类型一致
4. 利用索引覆盖进行查询避免回表
5. 分区表尽量使用本地（LOCAL）索引，有利于数据维护，是否使用（ Global ）索引请遵循分区索引创建基本原则
6. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度即可【该规范只适用于MYSQL】
7. 位图索引与函数索引应该尽量规避
8. 外键必须创建索引

单列索引

创建单列索引的要素：

• 频繁出现在where条件中的列
• 用来与其它表进行连接的列
• 有高的选择性和过滤性的列（高选择性的字段：如果很少的字段拥有相同值,即有很多唯一值,则选择性很好）
• Oracle与MySql在主键字段和UNIQUE字段上会自动建立唯一索引

一般在查询数据量占总表数据量比例较小时才考虑使用索引定位数据，否则大量数据扫描还不如全表

复合索引

创建复合索引的要素：最左前缀原则、覆盖索引原则

• 适用于单独查询返回记录较多，而组合查询后返回记录较少的情况

例如where 学历=本科 以上会返回不少的记录，where 工作=厨师 同样会返回不少的记录，那么这2个条件任意一个查询做索引，都会不合适，但学历既是本科且工作又是厨师的，返回的记录就少之又少了，这时候创建复合索引就非常合适

• 组合查询的组合顺序，要考虑单独的前缀查询情况（否则单独前缀查询的索引不能生效或者只能用到跳跃索引），我们应该让选择性好的做为前导列

例如我们创建object_id,object_type的联合索引时，要看考虑是单独where object_id=xxx查询的多，还是单独where object_type=xxx查询的多

• 仅等值无范围查询时，复合索引顺序不影响查询性能（比如where col1=xxx and col2=xxx，无论COL1+COL2组合还是COL2+COL1组合 都一样的效果）
• 复合索引最佳顺序一般是将列等值查询的列置前

分区索引

• 可以将索引存储在不同的分区中
• 与分区有关的索引有三种类型：

1. 局部分区索引 － 在分区表上创建的索引，在每个表分区上创建独立的索引，索引的分区范围与表一致
2. 全局分区索引 － 在分区表或非分区表上创建的索引，索引单独指定分区的范围，与表的分区范围或是否分区无关
3. 全局非分区索引 － 在分区表上创建的全局普通索引，索引没有被分区

▲需注意Oracle与MySQL的异同

索引失效情况分析

• 物理失效

索引真正意义上的失效，DML操作列时会报错，比如人为设置了unusable操作，或者分区表相关操作导致的索引失效，以及其它操作如 move表等

• 逻辑失效

索引本身并没有真正失效，而只是因为SQL的写法等一些原因导致索引利用不上那么哪些情况会导致索引无法使用？

索引去哪儿了

1. 字段类型不匹配，发生了隐式或显示转换
2. Like 与 %前置模糊查询
3. 反向键索引不支持范围查询
4. 索引列进行了函数转换或者表达式运算
5. 不等于<>, != 操作
6. 查询谓词条件是 is null
7. 复合索引前置列设置错误

四、经典表连接方式应用

经典的3种表连接方式解读

表连接方式特点与优化建议

五、SQL审核与编码规范

SQL审核防范于未然

我们应该建立长效的机制，为应用的健康稳定运行保驾护航

1. 通过规范与培训提高开发人员能力
2. 通过SQL审核进行SQL质量控制
3. 通过SQL捕获优化解决现有程序问题

书写规则

SQL开发审核规范

六、项目案例解析

SQL拆解分析，整个SQL其实就由3部分组成

纠结的写法

目前BPM系统存在的问题基本全在SQL开发审核规范覆盖范围之内

1. 大量查询使用*号
2. 没有使用绑定变量
3. 大量查询使用全模糊搜索查询”%”
4. 大量全表扫描（未创建索引or索引设计不合理）
5. 分页查询框架使用错误
6. 大表没有数据清理策略（采取分表OR分区OR历史数据归档）
7. 部分存在主外键关系约束的表，外键没有创建索引
8. 字段类型设计不合理

案例1：表设计问题 - - - 问题描述

存在问题：

1. 表没有设计主键
2. sendresult字段类型设计不合理
3. Sendtime 字段类型设计不合理
4. 表索引设计不合理
5. sendresult字段类型设计不合理
6. Sendtime 字段类型设计不合理
7. 表索引设计不合理

如何改进：

1. wf_orunid 设计主键
2. sendresult字段类型应该设计为整形
3. Sendtime 字段类型应该设计为时间类型
4. wf_orunid 应该设计为唯一索引
5. 分表或者按时间字段分区
6. 业务上非空的字段应该显示设计not null 非空约束
7. 注意CLOB字段

案例2：没有使用绑定变量 - - - 问题讨论

案例3：分页框架使用错误 - - - 问题讨论

解决方案

案例3：分页框架使用拓展 - - - 解决方案 续1

分页SQL语句的优化思路：避免排序，减少扫描

1. 在分页的需求中存在排序的时候，如何避免，我们可以利用索引已经排序的特性，将order by 的列包含在索引中
2. .利用rownnum 的COUNT STOPKEY 特性，减少扫描直至N行停止

拓展：多表关联分页如何优化

优化思路：多表关联分页语句如果存在排序，只能对其中一个表进行排序，且让参与排序的表作为嵌套循环的驱动表，并且驱动表返回数据的顺序要与排序的顺序一致，表的连接列必须创建好索引，如果有外连接，排序的字段只能是主表的字段，SQL语句中不要有distinct,group by ,union,union all,avg,max,min 操作

案例3：分页框架使用拓展 - - - 解决方案 续2

拓展：MySQL分页框架

开发常用框架：

select * from tab where tab_id=:B1 limit M,N

这是通常普通的分页写法,越往后翻（M越大）性能越差

建议分页框架：

select t1.* from tab t1,

(select priv_id from tab where tab_id=:B1 limit M,N) t2

where t1. priv_id =t2. priv_id

上一篇：批量将制定文件夹下的全部Excel文件导入微软SQL数据库

下一篇：SQL Server 2008另类提权思路