独辟蹊径，向扁鹊学习治理SQL！

詹姆斯邦德0072024年07月09日约 6 分钟

作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验，
Oracle、PostgreSQL ACE
CSDN博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、
高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理微信:jem_db
QQ交流群:587159446
公众号：IT邦德

1.防患于未燃
2.统计信息

2.1 获取真实的执行计划
2.2 正确的收集统计信息

3.优化SQL

3.1 优化思路
3.2 表关联方式
3.3 索引创建原则

4.分区规划
5.表空间管理
6.SQL质量管控
7.总结

前言

经常面对各种应用卡顿、执行计划变、CPU及IO使用率飙升等问题实在是疲于奔命，对于SQL的治理必须马上提上议程。

1.防患于未燃

从扁鹊这里延伸，不难理解经典的中医学说：防病于未萌，治病于初起。总而言之就是风险越早发现越好。做到早发现，早规划，早治疗,通常越早发现也意味着越好排查和解决。这时“疾在腠理，汤熨之所及也，今在骨髓，臣是以无请也。“的词句映入脑海...

那么如何防患于未燃呢？记住只要心中有道，术会自生，术是没办法通过一篇文章穷尽的，接下来给大家分享一些治理SQL的经验。

2.统计信息

之前给大家分享过一个案例，1个SQL干崩核心系统长达12小时，SQL性能问题最常见的就是执行计划错误，它一般会导致数据库CPU负载高、IO负载高导致服务器卡住，最终导致导致应用卡顿。在这里统计信息至关重要，优化器会根据统计信息选择最的佳执行计划，以最小的资源消耗获取到想要的数据。

所以首选要学会拿到正确的执行计划，并学会正确分析它。

2.1 获取真实的执行计划

这里分享Oracle如何获取真实的执行计划的方法1.从awr性能视图里面获取
特点：如果有多个执行计划，可对比，是真实的计划
select * from 
table(dbms_xplan.display_awr('&sql_id'));
2.真正的资源消耗
特点：很快定位性能瓶颈
select /*+ gather_plan_statistics */ * 
from emp where empno = 1123;
select * from 
table(dbms_xplan.display_cursor('6pjwyvcd9p8uj',null,'allstats last'));

2.2 正确的收集统计信息

某些情况下（如数据分布不均）仅仅更新统计信息不一定能得到准确的执行计划。那么我们在实际的数据库表中，什么样的列需要收集直方图的信息呢？那就是数据分布不均匀的列，或者从优化器的角度说是cardinality小的列，下面是一个MySQL的案例！

--此表记录上学及放学的时间记录
create table t_trx_attendance (
id int not null primary key,
name varchar(80) not null,
attend_start_time datetime ,
attend_end_time datetime
);

for i in {1..1000} do mysql -uroot -proot -e "insert into jeamesdb.t_trx_attendance(id,name,attend_start_time,attend_end_time) values(i','2024-05-01 08:00:00','2024-05-01 18:00:00');" done

--由于一个小朋友上午请假，
1000条中数据出现了1个上学时间11点的数据
mysql> insert into jeamesdb.t_trx_attendance 
values (1001,'狗蛋儿','2025-01-01 11:00:00','2025-01-01 18:00:00');
mysql> explain select * from 
jeamesdb.t_trx_attendance 
where attend_start_time > '2024-05-01 07:00:00';

明显我们看到执行错误了filtered 表示返回结果的行数占需读取行数的百分比，filtered 的值应该越大越好，这里反而才占了33.33%。

进行直方图收集
analyze table jeamesdb.t_trx_attendance 
UPDATE HISTOGRAM ON attend_start_time WITH 8 BUCKETS;explain select * from 
jeamesdb.t_trx_attendance 
where attend_start_time > '2024-05-01 07:00:00';

此时我们发现filtered百分比到达100%，这时SQL的性能才是最好的！

3.优化SQL

3.1 优化思路

SQL优化的大致思路就是
1、找到执行计划瓶颈
2、调整执行计划
3、获取更合适的执行计划
4、调整或绑定执行计划

3.2 表关联方式

正确的表连接决定一个好的执行计划1.Nested Loops：
每读一条驱动表的数据，
就根据连接条件去被驱动表中查找对应的数据，
直到读完驱动表所有数据为止。
一般用于驱动表小，被驱动表较大，
且关联字段有索引的情况。
2.Hash Join：
先在内存根据连接条件生成一张hash表，
然后再去扫描被驱动表，
并将每行与hash表对比，
找到所有匹配的行。
一般用于两个大表关联、
查询小表大部分数据、相同数量级的表关联
3.Sort Merge Join：
将两个表的数据分别全部读取出来并排序，
然后再根据连接条件合并

3.3 索引创建原则

1.适合创建索引的列
索引覆盖、避免排序
复合索引尽量兼顾更多SQL
该列在表中的唯一性特别高或者有些状态列有倾斜值
等值谓词条件字段放在前面，非等值谓词条件字段放在后面
表关联使用Nested Loop 被驱动表的关联字段上建议创建索引
该SQL语句是主流的业务，具有高并发，where条件中出现的列2.不适合创建索引的列
DML频繁的表不适合创建索引，索引会带来额外的维护成本
Where条件中不会使用的列也不适合创建索引

4.分区规划

对于海量数据库，分区设计至关重要，数据库架构师一项关键的技能是，设计分区策略，使得查询性能和数据维护不会数据规模的影响。应该根据业务负载，评估多种分区策略，选取最优的方案

【分区功能】
1、增强性能
分区裁剪（Partition Pruning）：
可以访问具体分区的数据而避免访问整张表。
智能连接（Partition-Wise Joins）：
分区表和分区表关联查询，可以实现分区和分区之间的关联。
2、易于管理
提供了更小的管理单元，便于历史数据的管理。
3、提高可用性
不同分区可以存储在不同的表空间，
分区之间相互独立，单个分区不可用不影响其他分区，
可以对单独的分区进行备份恢复操作。

尤其像MySQL、postgreSQL这样的关系型数据库，表及索引都是以文件的形式存储的，海量数据场景下分区的设定尤为关键，这对于后期SQL性能的提升非常有效果，以下是MySQL、postgreSQL规划的分区。

5.表空间管理

对于表来说，表空间是对表操作影响很大的因素，
主要涉及到频繁操作的数据！
那么表空间的作用是什么呢？
1.磁盘布局控制：通过创建不同的表空间来优化磁盘布局，
以适应不同的存储需求和性能要求。
2.性能优化：表空间可以根据数据库对象的使用模式来优化性能。
3.数据隔离：控制数据库部分数据的可用性
总之：能合理利用磁盘性能和空间,
制定最优的物理存储方式来管理数据库表和索引

6.SQL质量管控

开发人员写SQL操作数据库想必一定是一类基础且常见的工作内容。如何避免 “问题”SQL流转到生产环境，保证数据质量？这值得被研发/DBA/运维所重视。

SQL全生命周期质量管控的目的是规避业务SQL不规范引起的生产事故，提高业务稳定性！总之就是建立规范、标准发布、前控后督！

7.总结

拒绝临时抱佛脚，SQL的治理必须马上提上议程！让我们共同提升上线效率，提高数据质量

前言