SQL规范和优化（持续更新）

发表于 更新于 分类于
本文字数： 38k 阅读时长 ≈ 35 分钟
简单整理SQL规范和优化相关内容，包括：SQL规范，SQL常见优化项，优化案例，Oracle的STA优化。

SQL规范和优化

第一节 SQL规范

1.1 命名规范

表和视图的命名，长度不超过 30 个字符 。

表名及字段名设置要合理，不能使用MySQL的关键字，如desc, order, status, group等。

设置lower_case_table_names = 1表名不区分大小写。

对象前缀命名原则：

对象类型 对象前缀 说明
表（table） t t_module_name
临时表（temporary table） tmp tmp_module_name
视图（view） v v_表名
非唯一索引（ index） idx idx_表名 _每列首字母
唯一索引（unique index） uk uk_表名 _每列首字母
主键（primary key） pk pk_表名
过程（procedure） p
函数（function） f

1.2 格式规范

略。

1.3 设计规范

通用：

  • 尽量不要用 char 类型，用 varchar 类型代替（可变）。
  • 字段建议定义为 not null，并且指定默认值，如果列值存储了大量的NULL，会影响索引的稳定性。。
  • 禁止使用外键，外键约束应该在应用层处理。建议在 ER 图里保留外键。
  • 字段的备注要能明确该字段的作用，尤其是某些表示状态的字段，要显式的写出该字段所有可能的状态数值以及该数值的含义。
  • 不建议使用Text数据类型，一方面由于传输大量的数据包可能会超过max_allowed_packet设置导致程序报错，另一方面表上的DML操作都会变的很慢，建议采用es或者对象存储OSS来存储和检索。

MySQL：

  • 使用 InnoDB存储引擎，可以通过参数default_storage_engine控制。
  • 字符集统一使用 UTF8MB4 。
  • 所有表都必须有主键。
  • 不涉及分库分表的表选用 auto_increment 列做主键，主键类型使用无符号整型；涉及到要做分库分表的表用有序uuid做主键。
  • 主键之外，应增加代表业务规则的唯一索引，但不建议直接将其作为主键。
  • 禁止使用 enum/set/bool 类型， 尽量不使用 text/blobs 等大字段类型 。
  • 禁止使用浮点类型 FLOAT 和 DOUBLE。
  • 对较长的字符型字段建立索引时，应使用前缀索引。

Oracle：

  • 建议表和索引的数据分不同表空间存储。
  • 原则上每个表都应该有主键。
  • 优先使用业务主键，在业务主键超过3个字段时考虑使用逻辑主键，把业务主键建成唯一约束。
  • 不建议在分区表上创建全局索引，原则上一律使用 local索引。

建表的时候主键id带有AUTO_INCREMENT属性，而且AUTO_INCREMENT=1，在InnoDB内部是通过一个系统全局变量dict_sys.row_id来计数，row_id是一个8字节的bigint unsigned，InnoDB在设计时只给row_id保留了6个字节的长度，这样row_id取值范围就是 0 到 2^48^ - 1，如果id的值达到了最大值，下一个值就从0开始继续循环递增，在代码中禁止指定主键id值插入。

1
2
3
4
5
--新插入的id值会从10001开始，这是不对的，应该从1开始。
create table booking( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',......) engine = InnoDB auto_increment = 10000;

--指定了id值插入，后续自增就会从该值开始+1，索引禁止指定id值插入。
insert into booking(id, book_sn) values(1234551121, 'N12121');

1.4 语法规范

（1）select 检查

  • 禁止 select * 这样的代码 ，必须将字段名一一列出。

  • 尽量避免在 select 后使用自定义函数 ：SQL返回多少行，那么UDF函数就会被调用多少次，非常影响性能。

    1
    2
    --getOrderNo是用户自定义一个函数用户来根据order_sn来获取订单编号
    select id, payment_id, order_sn, getOrderNo(order_sn) from payment_transaction where status = 1 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';

  • 避免查询 text 类型字段 ：如果select出现text类型的字段，就会消耗大量的网络和IO带宽，由于返回的内容过大超过max_allowed_packet设置会导致程序报错，需要评估谨慎使用。

    1
    2
    --表request_log的中content是text类型。
    select user_id, content, status, url, type from request_log where user_id = 32121;

  • 谨慎使用 group_concat ：gorup_concat是一个字符串聚合函数，会影响SQL的响应时间，如果返回的值过大超过了max_allowed_packet设置会导致程序报错。

    1
    select batch_id, group_concat(name) from buffer_batch where status = 0 and create_time between '2020-10-01 10:00:00' and '2020-10-02 10:00:00';

  • 内联子查询 ：在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

    1
    select id,(select rule_name from member_rule limit 1) as rule_name, member_id, member_type, member_name, status  from member_info m where status = 1 and create_time between '2020-09-02 10:00:00' and '2020-10-01 10:00:00';

  • ……

（2）from 检查

  • 当 SQL涉及到多个表时， 必须为每个字段指定表名前缀。

  • 定义表和子查询的别名时不能重名。

  • 尽量避免表关联 ：在MySQL中不建议使用Left Join，即使ON过滤条件列索引，一些情况也不会走索引，导致大量的数据行被扫描，SQL性能变得很差，同时要清楚ON和Where的区别。应避免带 in 的子查询，尽量改写成 join？没必要的时候不要做外连接，内连接效率比外连接高 。外连接一律用 left join，禁止使用 right join。

    1
    2
    SELECT a.member_id,a.create_time,b.active_time FROM operation_log a LEFT JOIN member_info b ON a.member_id = b.member_id where  b.`status` = 1
    and a.create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' limit 100, 0;

  • 避免子查询 ：由于MySQL的基于成本的优化器CBO对子查询的处理能力比较弱，不建议使用子查询，可以改写成 Inner Join

    1
    2
    select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1
    and create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00') as b on a.member_id = b.member_id;

  • ……

（3）where 检查

  • where条件列上禁止使用函数和表达式 ，也要避免数据类型的隐式转换。

  • 使用 like进行模糊查询时， %不要放在首位 ，因为会限制对索引的使用 。
    例如 like 'abc%' 可以走索引，而 like '%abc' 或者 '%abc%' 都不会走索引。

  • 索引列被运算 ：当一个字段被索引，同时出现where条件后面，是不能进行任何运算，会导致索引失效

    1
    2
    3
    4
    --device_no列上有索引，由于使用了ltrim函数导致索引失效
    select id, name , phone, address, device_no from users where ltrim(device_no) = 'Hfs1212121';
    --balance列有索引,由于做了运算导致索引失效
    select account_no, balance from accounts where balance + 100 = 10000 and status = 1;

  • 类型转换 ：

    1
    2
    3
    4
    --user_id是bigint类型，传入varchar值发生了隐式类型转换，可以走索引。
    select id, name , phone, address, device_no from users where user_id = '23126';
    --card_no是varchar(20)，传入int值是无法走索引
    select id, name , phone, address, device_no from users where card_no = 2312612121;

  • 列字符集 ：从MySQL 5.6开始建议所有对象字符集应该使用用utf8mb4，包括MySQL实例字符集，数据库字符集，表字符集，列字符集。避免在关联查询Join时字段字符集不匹配导致索引失效，同时目前只有utf8mb4支持emoji表情存储。

    1
    2
    3
    4
    character_set_server  =  utf8mb4    #数据库实例字符集
    character_set_connection = utf8mb4  #连接字符集
    character_set_database = utf8mb4    #数据库字符集
    character_set_results = utf8mb4     #结果集字符集

  • ……

（4）group by 检查

  • 前缀索引：group by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。

    1
    2
    3
    4
    --device_no字段类型varchar(200)，创建了前缀索引。
    mysql> alter table users add index idx_device_no(device_no(64));

    mysql> select device_no, count(*) from users where create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00' group by device_no;

  • 函数运算：假设需要统计某月每天的新增用户量，参考如下SQL语句，虽然可以走create_time的索引，但是不能消除排序，可以考虑冗余一个字段stats_date date类型来解决这种问题。

    1
    select DATE_FORMAT(create_time, '%Y-%m-%d'), count(*) from users where create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59' group by DATE_FORMAT(create_time, '%Y-%m-%d');

  • ……

（5）order by 检查

  • 避免不必要的排序 。order by、 group by、 distinct、 union等操作都有可能排序。
    MySQL中 使用 group by 时， 默认会进行排序， 如果不需要排序 ，可以使用 order by null。
  • 前缀索引：order by后面的列有索引，索引可以消除排序带来的CPU开销，如果是前缀索引，是不能消除排序的。
  • 字段顺序：排序字段顺序，asc/desc升降要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销。
  • ……

（6）limit 检查

  • limit m,n要慎重 ：对于limit m, n分页查询，越往后面翻页即m越大的情况下SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。
  • ……

（7）Union 检查

  • 合并多个表的数据时，如果不需要去除重复数据，应使用 union all 而不是 union 。union需要做排序和去重操作，效率不如 union all。

（8）兼容性检查

同时支持MySQL和Oracle：

  • 对于单行注释应使用 -- 方式 。
  • 对于字符串应该使用单引号括起来。MySQL还支持双引号的方式，但 Oracle不支持。
  • 尽量避免使用 full join。MySQL还不支持 full join。
  • 避免在 group by和 having子句中使用列的别名。Oracle不支持这种语法。
  • from子句中的子查询要定义别名。MySQL中这个别名是必须要有的。

1.5 索引规范

1.5.1 索引属性

索引基数指的是被索引的列唯一值的个数，唯一值越多接近表的count(*)说明索引的选择率越高，通过索引扫描的行数就越少，性能就越高，例如主键id的选择率是100%，在MySQL中尽量所有的update都使用主键id去更新，因为id是聚集索引存储着整行数据，不需要回表，性能是最高的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select count(*) from member_info;
+----------+
| count(*) |
+----------+
|   148416 |
+----------+
1 row in set (0.35 sec)

mysql> show index from member_base_info;
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name                   | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| member_info |          0 | PRIMARY                    |            1 | id                | A         |      131088 | NULL     | NULL   |      | BTREE      |         |               |
| member_info |          0 | uk_member_id               |            1 | member_id         | A         |      131824 | NULL     | NULL   |      | BTREE      |         |               |
| member_info |          1 | idx_create_time            |            1 | create_time       | A         |        6770 | NULL     | NULL   |      | BTREE      |         |               |
+------------------+------------+----------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
--Table：表名
--Non_unique ：是否为unique index，0-是，1-否。
--Key_name：索引名称
--Seq_in_index：索引中的顺序号，单列索引-都是1；复合索引-根据索引列的顺序从1开始递增。
--Column_name：索引的列名
--Collation：排序顺序，如果没有指定asc/desc，默认都是升序ASC。
--Cardinality：索引基数-索引列唯一值的个数。
--sub_part：前缀索引的长度；例如index (member_name(10)，长度就是10。
--Packed：索引的组织方式，默认是NULL。
--Null：YES:索引列包含Null值；'':索引不包含Null值。
--Index_type：默认是BTREE，其他的值FULLTEXT，HASH，RTREE。
--Comment：在索引列中没有被描述的信息，例如索引被禁用。
--Index_comment：创建索引时的备注。

1.5.1 前缀索引

对于变长字符串类型varchar(m)，为了减少key_len，可以考虑创建前缀索引，但是前缀索引不能消除group by， order by带来排序开销。如果字段的实际最大值比m小很多，建议缩小字段长度。

1
alter table member_info add index idx_member_name_part(member_name(10));

1.5.3 复合索引顺序

有很多人喜欢在创建复合索引的时候，总以为前导列一定是唯一值多的列，例如索引 index idx_create_time_status(create_time, status)，这个索引往往是无法命中，因为扫描的IO次数太多，总体的cost的比全表扫描还大，CBO最终的选择是走 full table scan。

MySQL遵循的是索引最左匹配原则，对于复合索引，从左到右依次扫描索引列，到遇到第一个范围查询（>=, >,<, <=, between ….. and ….）就停止扫描，索引正确的索引顺序应该是 index idx_status_create_time(status, create_time) 。

1
select account_no, balance from accounts where status = 1 and create_time between '2020-09-01 00:00:00' and '2020-09-30 23:59:59';

1.5.4 时间列索引

对于默认字段 created_at(create_time)、updated_at(update_time) 这种默认就应该创建索引，这一般来说是默认的规则。

第二节 SQL优化

SQL优化方向：

  • 减少数据访问次数：设置合理的字段类型，使用压缩，通过索引访问减少磁盘IO。
  • 返回更少的数据：只返回需要的字段，分页处理，减少磁盘IO和网络IO。
  • 减少交互次数：批量DML操作、函数存储等减少数据连接次数。
  • 减少CPU开销：尽量减少数据库排序操作以及全表查询，减少CPU内存占用。
  • 利用更多资源：使用表分区，增加并行操作，更大限度利用CPU资源。

SQL执行顺序：

1
2
3
4
5
6
7
8
9
10
SELECT 
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
  1. FROM：选取表，将多个表数据通过笛卡尔积变成一个表
  2. ON：对笛卡尔积的虚表进行筛选
  3. JOIN：指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
  4. WHERE：对上述虚表进行筛选
  5. GROUP BY：分组聚合
  6. HAVING：对分组后的结果进行聚合筛选
  7. SELECT：返回的单列必须在group by子句中，聚合函数除外
  8. DISTINCT：数据除重
  9. ORDER BY： 排序
  10. LIMIT：行数限制

2.1 常见优化项

  1. 对常用于where和order等地方的属性列创建索引，避免全表扫描，提高查询效率。

    以下写法可能会导致查询不走索引，而进行全表扫描，下列总结不一定准确涵盖，有时因为如索引字段重复数据太多等原因也会导致SQL不走索引，具体情况需要具体分析，且不要提前做优化，开发首先的优先级应该保证进度按时完成，尽量不要先把重心提前放到这些地方。

用法 说明 代替用法
select * 无法使用索引 标明具体返回属性
where xx != 或 <> 无法使用索引 使用 > 或用 union all 来代替
where xx is (not) null 无法使用索引 where xx = 0(不一定都支持)
where xx or … 无法使用索引 用 union all 来代替
where xx in/not in … 无法使用索引 between，exists或join替换in，用not exists替代not in
where xx like … 模糊查询无法使用索引 避免字段前%，可以使用字段后%，考虑使用全文检索
where 使用局部变量 无法使用索引 如：select id from t where num=@num 改为强制索引：select id from t with(index(索引名)) where num=@num
where 表达式运算 无法使用索引 如：select id from t where num/2=100 应改为: select id from t where num=100*2
where 函数操作 无法使用索引 如：select id from t where substring(name,1,3)=’abc’ 和 select id from t where datediff(day,createdate,’2005-11-30’)=0–’2005-11-30’生成的id 应改为: select id from t where name like ‘abc%’ 和 select id from t where createdate>=’2005-11-30’ and createdate<’2005-12-1’
若索引字段是组合索引 只能使用索引中的第一个字段作为条件，否则不会使用索引，尽量让字段顺序和索引顺序保持一致 select col1 from table where key_part2=1 and key_part3=2
隐式类型转换 索引对应字段类型为varchar，但给定值为数值 select col1 from table where col_varchar=123;

  1. 避免使用in或not in

    • 如果是连续数值，可以用between代替：

      1
      2
      SELECT * FROM t WHERE id IN (2,3);
      SELECT * FROM t WHERE id BETWEEN 2 AND 3;

    • 如果是子查询，可以使用exists代替in：

      1
      2
      3
      select num from a where num in(select num from b);
      # 替换为： 
      select num from a where exists(select 1 from b where num=a.num);

    • 使用EXISTS时，如果连接列(id)上建立了索引，那么查询Class_B时不用查询实际的表，只需查索引就可以了。只要查到一行数据满足条件就会终止查询，不用像使用IN时一样扫描全表。在这一点上NOT EXISTS也一样。

  1. 存储数字值时尽量使用数字类型的字段：若用字符类型来存储数字值，相比数字类型会降低查询和连接的性能，字符比较相比数字比较要慢，并一定程度上增加存储开销。
  1. 尽可能的使用 varchar/nvarchar 代替 char/nchar：变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  1. 尽量使用表变量来代替临时表：如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

  1. 避免频繁创建和删除临时表，以减少系统表资源的消耗

    适当地使用临时表，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

    使用临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

    如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

  2. 尽量避免使用游标，因为游标的效率较差。

    使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

    与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

  1. 尽量避免使用游标，因为游标的效率较差：使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

  2. 减少访问数据库的次数：数据库在每次执行前可能会做一些默认工作，如果不影响开发进度，尽量对数据的一次访问就取出所要数据。

  3. 字段Column前加表名：减少因为防止歧义而进行解析所消耗的时间。

  4. 用EXISTS替换DISTINCT：减少因为防止歧义而进行解析所消耗的时间。

    1
    2
    (低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO 
    (高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

  5. Oracle中尽量用全大写：Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

  6. 用>=替代>

    1
    2
    高效：SELECT * FROM EMP WHERE DEPTNO >=4 
    低效: SELECT * FROM EMP WHERE DEPTNO >3

    两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

  7. 模糊查询

    • 尽量只在字段后使用 abc% ，避免在字段前使用 %abc% 。
    • 使用MySQL内置函数 INSTR(str,substr) 来匹配，作用类似于java中的 indexOf() ，查询字符串出现的角标位置。
    • 使用FullText全文索引，用 match against 检索。
    • 数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级。
    • 表数据量较少，直接用 like '%xx%' 。

  8. 优化GROUP BY

    提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。

    1
    2
    低效: SELECT JOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 
    高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB

  9. 优化ORDER BY

    order by 条件要与where中条件一致，否则order by不会利用索引进行排序

    1
    2
    3
    4
    5
    -- 不走age索引
    SELECT * FROM t order by age;
     
    -- 走age索引
    SELECT * FROM t where age > 0 order by age;

    数据库执行顺序：

    • 根据where条件和统计信息生成执行计划，得到数据。
    • 将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
    • 返回排序后的数据。

    当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

    这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

  10. 避免使用耗费资源的操作：带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。通常，带有 UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。如果你的数据库的SORT_AREA_SIZE调配得好。使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强。

  11. 两表关联小表在前，大表在后

  12. 子查询优化

    子查询可以分为关联子查询（Correlated Subquery）和非关联子查询（Non-correlated Subquery）。

    • 非关联子查询： 非关联即先执行内层查询，再执行外层查询，如下所示。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      SELECT c_count, count(*) AS custdist
      FROM (
           SELECT c_custkey, count(o_orderkey) AS c_count
           FROM CUSTOMER
           LEFT OUTER JOIN ORDERS ON c_custkey = o_custkey
           AND o_comment NOT LIKE '%pending%deposits%'
           GROUP BY c_custkey
           ) c_orders
      GROUP BY c_count
      ORDER BY custdist DESC, c_count DESC;

    • 标量子查询：输出只有一个结果值的子查询，可以作为条件或属性出现在WHERE子句或SELECT子句

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      SELECT c_custkey
      FROM CUSTOMER
      WHERE 1000000 < (
          SELECT SUM(o_totalprice)
          FROM ORDERS
          WHERE o_custkey = c_custkey  -- Correlated!
      )

      SELECT o_orderkey, (
          SELECT c_name
          FROM CUSTOMER
          WHERE c_custkey = o_custkey  -- Correlated!
      ) AS c_name FROM ORDERS

    • 存在性检测子查询：特指 EXISTS 的子查询，返回一个布尔值。如果出现在 WHERE 中，这就是我们熟悉的 Semi-Join。当然，它可能出现在任何可以放布尔值的地方。

      1
      2
      3
      4
      5
      6
      7
      --Semi-Join
      SELECT c_custkey
      FROM CUSTOMER
      WHERE c_nationkey = 86 AND EXISTS(
          SELECT * FROM ORDERS
          WHERE o_custkey = c_custkey  -- Correlated!
      )

    • 集合比较子查询：特指 IN、SOME、ANY 的查询，返回一个布尔值，常用的形式有：x = SOME(Q) （等价于 x IN Q）或 X <> ALL(Q)（等价于 x NOT IN Q）。同上，它可能出现在任何可以放布尔值的地方。

      1
      2
      3
      SELECT c_name
      FROM CUSTOMER
      WHERE c_nationkey <> ALL (SELECT s_nationkey FROM SUPPLIER)

去关联化，测试执行标量子查询示例1，可以发现子查询是挂在filter下的，实际执行时就会变成查询执行器调用表达式执行器，表达式执行器因为子查询再调用查询执行器，当数据比较多时很明显会对效率造成很大的影响。

2.2 优化案例

（1）慢查询优化案例

通过对慢查询的监控告警，经常会发现一些SQL语句where过滤字段都有索引，但是由于SQL写法的问题导致索引失效，下面二个案例解释如何通过SQL改写来查询。可以通过以下SQL来捞取最近5分钟的慢查询进行告警。

1
select CONCAT( '# Time: ', DATE_FORMAT(start_time, '%y%m%d %H%i%s'), '\n', '# User@Host: ', user_host, '\n', '# Query_time: ', TIME_TO_SEC(query_time),  '  Lock_time: ', TIME_TO_SEC(lock_time), '  Rows_sent: ', rows_sent, '  Rows_examined: ', rows_examined, '\n', sql_text, ';' ) FROM mysql.slow_log where start_time between current_timestamp and date_add(CURRENT_TIMESTAMP,INTERVAL -5 MINUTE);
1.慢查询SQL
1
| 2020-10-02 19:17:23 | w_mini_user[w_mini_user] @ [10.200.20.11] | 00:00:02   | 00:00:00  |         9 |        443117 | mini_user |              0 |         0 | 168387936 | select id,club_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or applicant_id=12395) order by created_time desc limit 0,10; | 1219921665 |

从慢查询slow_log可以看到，执行时间2s，扫描了443117行，只返回了9行，这是不合理的。

2.SQL分析
1
2
3
4
5
6
7
8
9
10
--原始SQL，频繁访问的接口，目前执行时间2s。
select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and app_id is not null) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;

--执行计划
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys                   | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | t_user_msg | index | invite_id,app_id,team_id | created_time | 5       | NULL |   10 | Using where |
+----+-------------+--------------+-------+---------------------------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

从执行计划可以看到，表上有单列索引 invite_id,app_id,team_id,created_time，走的是create_time的索引，而且type=index索引全扫描，因为create_time没有出现在where条件后，只出现在order by后面，只能是type=index，这也预示着表数据量越大该SQL越慢，我们期望是走三个单列索引 invite_id，app_id，team_id，然后type=index_merge操作。

按照常规思路，对于OR条件拆分两部分，分别进行分析。

1
select id, ……. from t_user_msg where 1 and (team_id in (3212) and app_id is not null) order by created_time desc limit 0,10;

从执行计划看走的是team_id的索引，没有问题。

1
2
3
| id | select_type | table        | type | possible_keys        | key     | key_len | ref   | rows | Extra                       |
+----+-------------+--------------+------+----------------------+---------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | t_user_msg | ref  | app_id,team_id | team_id | 8       | const |   30 | Using where; Using filesort |

再看另外一个sql语句：

1
select id, ……. from t_user_msg where 1 and  **(invite_id=12395 or app_id=12395)** order by created_time desc limit 0,10;

从执行计划上看，分别走的是invite_id,app_id的单列索引，同时做了index_merge合并操作，也没有问题。

1
2
3
| id | select_type | table        | type        | possible_keys           | key                     | key_len | ref  | rows | Extra                                                             |
+----+-------------+--------------+-------------+-------------------------+-------------------------+---------+------+------+-------------------------------------------------------------------+
|  1 | SIMPLE      | t_user_msg | index_merge | invite_id,app_id | invite_id,app_id | 9,9     | NULL |    2 | Using union(invite_id,app_id); Using where; Using filesort |

通过上面的分析，第一部分SQL走的执行计划走team_id索引没问题，第二部分SQL分别走invite_id,app_id索引并且index_merge也没问题，为什么两部分SQL进行OR关联之后走create_time的单列索引呢，不应该是三个单列索引的index_merge吗？

index_merge默认是在优化器选项是开启的，主要是将多个范围扫描的结果集合并成一个，可以通过变量查看。

1
2
mysql >select @@optimizer_switch;
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,

其他三个字段都传入的是具体的值，而且都走了相应的索引，只能怀疑app_id is not null这个条件影响了CBO对最终执行计划的选择，去掉这个条件来看执行计划，竟然走了三个单列索引且type=index_merge，那下面只要搞定app_id is not null这个条件就OK了吧。

1
2
3
| id | select_type | table        | type        | possible_keys                   | key                             | key_len | ref  | rows | Extra                                                                     |
+----+-------------+--------------+-------------+---------------------------------+---------------------------------+---------+------+------+---------------------------------------------------------------------------+
|  1 | SIMPLE      | t_user_msg | index_merge | invite_id,app_id,teadm_id | team_id,invite_id,app_id | 8,9,9   | NULL |   32 | Using union(team_id,invite_id,app_id); Using where; Using filesort |
3.SQL改写

通过上面分析得知，条件 app_id is not null 影响了CBO的选择，下面进行改造。

改写优化1

根据SQL开发规范改写，将OR改写成Union All方式即可，最终的SQL如下：

1
2
3
4
5
select id, ……. from (
select id, ……. from t_user_msg where **1 and (club_id in (5821) and applicant_id is not null)**
        **union all** select id, ……. from t_user_msg where **1 and invitee_id='146737'**
        **union all** select id, ……. from  t_user_msg where **1 and app_id='146737'**
       ) as a order by created_time desc limit 0,10;

一般情况下，Java代码和SQL是分开的，SQL是配置在xml文件中，根据业务需求，除了team_id是必填，其他两个都是可选的，所以这种改写虽然能提高SQL执行效率，但不适合这种业务场景。

改写优化2

app_id is not null 改写为**IFNULL(app_id, 0) >0)**，最终的SQL为：

1
select id,team_id,reason,status,type,created_time,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **IFNULL(app_id, 0) >0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;
改写优化3

将字段app_id bigint(20) DEFAULT NULL，变更为app_id bigint(20) NOT NULL DEFAULT 0，同时更新将app_id is null的时候全部更新成0，就可以将条件app_id is not null 转换为app_id > 0，最终的SQL为：

1
select id,team_id,reason,status,type,created_at,invite_id,falg_admin,file_id from t_user_msg where 1 and (team_id in (3212) and **app_id > 0)**) or (invite_id=12395 or app_id=12395) order by created_time desc limit 0,10;

从执行计划看，两种改写优化方式都走三个单列索引，执行时间从2s降低至10ms，线上采用的是优化1的方式，如果一开始能遵循MySQL开发规范就就会避免问题的发生。

上面介绍了SQL规范性检查，表结构检查，索引检查以及通过SQL改写来优化查询，在编写代码的过程，如果能提前做这些规范性检查，评估出自己认为理想的执行计划，然后通过explain解析出MySQL CBO的执行计划，两者做对比分析差异，弄清楚自己的选择和CBO的不同，不但能够编写高质量的SQL，同时也能清楚CBO的工作原理。

（2）生产环境RPC服务调用超时

1.排查过程

问题描述：生产环境反映进行大批量数据转换操作时，流程一直没结束。

排查过程：

  1. 获取日志后进行分析，发现MQ消息发送频率异常，消息产生速率远远大于处理速率。
  2. 程序一直在报RPC调用超时（超时时间5分钟）对应服务是持久层的一个查询SQL。
  3. 确认了SQL本身非慢查询，以防万一用Oracle的STA分析了下，没有优化建议。
  4. 继续观察日志，发现环境数据操作频繁，怀疑是服务器负载过高，导致某些流程未正常结束，以及数据库是否有较长的等待事件。
  5. 查看了下Oracle的会话状态，发现活跃会话数有近400个，平时在30左右。
  6. 查到一些会话有等待事件且等待时间很长，查出阻塞的SQL相关信息，确认等待原因为 cursor: pin S wait on X（有300多个）和 PGA memory operation （2个）。
  7. 发现所有会话都在等待一个SQL，正好是超时的服务接口对应的一个查询。所有等待事件的来源是 PGA memory operation 。
  8. 查阅该事件相关文档。
  9. 导出对应时间段的AWR报告，分析得到数据库服务器的负载过高，大部分信息和上文排查内容一致，无法定位到具体问题，转交由DBA处理。
2.使用到的SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
# 查询等待事件
select inst_id,event,count(*) from gv$session_wait
where wait_class not like 'Idle'
group by inst_id, event order by 3 desc;

# 活跃的会话信息
SELECT b.inst_id,b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       sql_fulltext,
       b.machine 计算机名,
       b.EVENT,
       'alter system kill session '''||b.sid||','||b.serial#||''';'
FROM gv$process a, gv$session b, gv$sql c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
   and a.inst_id=1
   and b.inst_id=1
   and c.inst_id=1
   and b.status='ACTIVE';
   
# 对应等待事件的会话信息
SELECT b.inst_id,b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       sql_fulltext,
       b.machine 计算机名,
       b.EVENT,
       c.SQL_ID,
       c.CHILD_NUMBER
FROM gv$process a, gv$session b, gv$sql c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
	 order by b.sid
and event like '%s wait for x%'
   and a.inst_id=1
   and b.inst_id=1
   and c.inst_id=1;

select * from dba_hist_active_sess_history where event ='cursor: pin S wait on X';

# 根据时间统计会话数
select to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME,count(*) cnt
from dba_hist_active_sess_history ash
where ash.instance_number=1
 and ash.wait_class <> 'Idle'  /* 非空闲回话 **/
 and ash.sample_time between sysdate -1/2 and sysdate
 group by SAMPLE_TIME
 having count(*) >= 1 
 order by SAMPLE_TIME;

# 查看某个时间节点SQL执行情况
select
   to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME
   ,ash.sql_id
   ,ash.event
   ,count(*) CNT /* SQL的数量 **/
   ,TRUNC(SUM(TIME_WAITED) / 1000000,2) SECONDS_IN_WAIT /* SQL的等待时间 **/
   ,SUM(to_number(CAST(ash.sample_time AS DATE)-ash.sql_exec_start) *24*60*60 ) SECONDS_IN_EXECUTE /* SQL的执行时间 **/
from dba_hist_active_sess_history ash
where ash.instance_number=1
 and ash.wait_class <> 'Idle'
 and to_char(ash.sample_time,'YYYY-MM-DD HH24:MI:SS') = '2021-04-21 15:11:16'
group by ash.sample_time,ash.sql_id,ash.event
having count(*) >= 1
order by CNT desc,SAMPLE_TIME,4;


select
  to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE_TIME
 ,ash.event
 ,ash.sql_id 
 ,ash.blocking_inst_id
 ,ash.blocking_session
 ,ash.blocking_session_serial#
 ,count(*) cnt
from dba_hist_active_sess_history ash 
where ash.instance_number=1
 and ash.wait_class <> 'idle'
 and ash.sql_id = '4qn8f6ujgjy9f'
 and to_char(ash.sample_time,'YYYY-MM-DD HH24:MI:SS') = '2021-04-21 15:11:16'
group by sample_time,ash.event,ash.sql_id,ash.blocking_inst_id,ash.blocking_session,ash.blocking_session_serial#
order by sample_time,7;

# 分析下4448，58047会话的历史执行情况
select
 to_char(ash.sample_time, 'YYYY-MM-DD HH24:MI:SS') sample_time
,to_char(ash.sql_exec_start,'YYYY-MM-DD HH24:MI:SS') SQL_START_TIME
,ash.instance_number inst_id
,ash.session_id sid
,ash.session_serial# serial 
,ash.blocking_inst_id b_inst_id
,ash.blocking_session b_sid
,ash.blocking_session_serial# b_serial
,ash.sql_id
,ash.event
,to_number(CAST(ash.sample_time AS DATE)-ash.sql_exec_start) *24*60*60 SECONDS_IN_EXECUTE
,ash.xid
from dba_hist_active_sess_history ash
where
 and ash.instance_number=1
 and ash.session_id = 4448
 and ash.session_serial# = 58047
order by sample_time;

# 创建STA分析任务
declare
my_task_name varchar2(30);
my_sqltext CLOB;
begin
my_sqltext := '
            select XXX
			from XXX';
# 删除 STA
--dbms_sqltune.drop_tuning_task(task_name => 'smalltab_inner_bigtab_sql');
# 创建 STA
my_task_name := dbms_sqltune.create_tuning_task(
sql_text => my_sqltext,
user_name => upper('XXX'),
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'test_sql_account',
description => '优化smalltab和bigtab连接到的例子'
);
# 执行 STA
dbms_sqltune.execute_tuning_task(task_name => 'test_sql_account');

dbms_output.put_line('my_task_name='||my_task_name);
end;

SELECT OWNER,TASK_ID,TASK_NAME,STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='test_sql_account';

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test_sql_account' );
END;

# 查看SQL TUNING状态
SELECT status FROM USER_ADVISOR_TASKS WHERE  task_name = 'test_sql_account';

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_account') FROM DUAL;

select count(*) from v$session;
select 
'call sys.dbms_shared_pool.purge('''||t.ADDRESS||','||t.hash_value||''',''c'')'
SQL_TEXT,sql_id, address, hash_value, executions, loads, parse_calls, invalidations  
from v$sql t where t.SQL_ID = '1uw84jcq6802a';

call sys.dbms_shared_pool.purge('000000009C88A8F0,1227751471','c')
call sys.dbms_shared_pool.purge('0000000068220548,150339650','c')
call sys.dbms_shared_pool.purge('000000009A7D2700,744751178','c')


# 查看等待事件cursor: pin S wait on X的会话信息
select s.inst_id as inst,
       s.sid as blocked_sid,  --被阻塞进程
       s.username as blocked_user,  --被阻塞用户
       sa.sql_id as blocked_sql_id,  --被阻塞sql_id
       trunc(s.p2/4294967296) as blocking_sid, --阻塞进程
       b.username as blocking_user,  --阻塞用户
       b.sql_id as blocking_sql_id  --阻塞sql_id
from gv$session s
join gv$sqlarea sa
  on sa.hash_value = s.p1
join gv$session b
  on trunc(s.p2/4294967296)=b.sid
 and s.inst_id=b.inst_id
join gv$sqlarea sa2
  on b.sql_id=sa2.sql_id
where s.event='cursor: pin S wait on X';

select * from gv$session;
# 阻塞的SQL版本、执行和解析数
select sql_id,loaded_versions,executions,loads,invalidations,parse_calls
from gv$sql 
where sql_id='cn7m7t6y5h77g';
3.相关文档
文档1

从12.1迁移到12.2后SQL语句执行缓慢等待在”PGA memory operation”和”Acknowledge over PGA limit”(Doc ID 2403920.1)

（内容省略）……

原因：

“PGA memory operation” 只是一个新的等待事件，用来衡量之前版本并未测量的一个等待。该事件较大可能是因为底层硬件出现了CPU或内存的短缺。在12.2.0.1环境中，可用物理内存大小与TABLE ACCESS FULL和MERGE JOIN操作的块大小相比非常低。

“PGA memory operation” is just an new wait event to expose a prior unmeasured wait in earlier versions of the database. High wait times for that event likely mean the underlying OS/hardware is becoming CPU or memory starved. In customer’s 12.2.0.1 environment, the Available Physical Memory size was very low compared to the blocks size of TABLE ACCESS FULL and MERGE JOIN operation. Therefore the overhead of memory resource operation in OS side spent a lot of CPU time.

因此，OS端内存资源操作的开销花费了大量的CPU时间。

解决方案：

有必要增加物理内存大小，或将SQL执行调整到主机服务器有更多可用物理内存的另一时间段。超大内存大小的工作区也可能会提高SQL性能，从而增加会话工作区大小并降低对临时空间的访问。

例如：

1
2
3
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1500M;
alter system set "_pga_max_size" = 1G;
alter system set "_smm_max_size" = 1048576;
文档2

以下内容来自：Oracle从 12.1 迁移到 12.2 后 SQL 语句执行缓慢等待在”PGA memory operation”和”Acknowledge over PGA limit” (Doc ID 2403920.1).pdf

文档3

Oracle文档：

内容：

  • APPLIES TO：Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
    Information in this document applies to any platform. Checked for relevance 17-Aug-2014

  • SYMPTOMS：In alert log there are reported messages like the following: Memory Notification: Library Cache Object loaded into SGA, Heap size <heap size K> exceeds notification threshold (51200K)

  • CAUSE：

    These are warning messages that are not causing process failure.

    They appear as a result of event messaging mechanism and memory manager introduced starting with 10gR2 database release.

    As large objects in the shared pool can potentially cause problems this warning threshold was implemented.

    Items/SQLs which allocate more space than this warning threshold, outputs a warning to the alert log.

    This warning is only to inform that a given heap object exceeds the defined threshold size and a trace file is generated so that a DBA can check potentially expensive - from shared memory point of view - objects.

    These are purely warning messages and have no impact on the database functionality, although they are designed to indicate possible tuning opportunities in customers’ applications.

    The messages do not imply that an ORA-4031 is about to happen immediately unless the size of shared pool is very small.

  • SOLUTION：

    A hidden parameter - _kgl_large_heap_warning_threshold - that sets the KGL heap size warning threshold was introduced starting with 10gR2. Warnings are written if heap size in shared pool exceeds this threshold: 

    1
    _kgl_large_heap_warning_threshold => maximum heap size before KGL writes warnings to the alert log

    Besides reducing the heap size from the application code (recommended) one can set _kgl_large_heap_warning_threshold to a reasonable high value or zero to prevent these warning messages. The value needs to be set in bytes. For example:

    If using a SPFILE:

    =============

    (logged in as “/ as sysdba”)

    1
    2
    3
    4
    5
    SQL> alter system set "_kgl_large_heap_warning_threshold"=83886080 scope=spfile ; 

    SQL> shutdown immediate

    SQL> startup

    If using a PFILE:

    ============
    Edit the PFILE and add:

    1
    _kgl_large_heap_warning_threshold=8388608

    The default threshold in 10.2.0.1 is only 2M. Starting with 10.2.0.2 the threshold was increased to 50MB after regression tests, so this should be a reasonable and recommended value in most cases.

    In 12.1.0.2 database release (that includes the fix Bug 15898589 - enhancement to restrict the size of SGA base library cache heaps) an enhancement to restrict the size of SGA base library cache heaps was introduced in order to avoid running out of space inside shared pool and hence ORA-4031 errors. With this fix, a new hidden parameter - _kgl_large_heap_assert_threshold - was also introduced. 

    1
    _kgl_large_heap_assert_threshold => maximum heap size before KGL raises an internal error

    Its value represents the maximum heap size before raising the ORA-600 internal error like:

    1
    ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded], [0x7FF91F844240], [6], [532279608], [], [], [], [], [], [], [], []

    To check current value of the parameters, one can run the following query:

    1
    2
    3
    4
    5
    6
    7
    8
    select
    nam.ksppinm NAME,
    nam.ksppdesc DESCRIPTION,
    val.KSPPSTVL
    from
    x$ksppi nam,
    x$ksppsv val
    where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

    For example in 12.1.0.2 the default values for the two parameters are:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    SQL> select nam.ksppinm NAME,
          nam.ksppdesc DESCRIPTION,
          val.KSPPSTVL
    from x$ksppi nam,  x$ksppsv val
    where nam.indx = val.indx and nam.ksppinm like'%kgl_large_heap_%_threshold%';

    NAME
    \--------------------------------------------------------------------------------
    DESCRIPTION
    \--------------------------------------------------------------------------------
    KSPPSTVL
    \--------------------------------------------------------------------------------
    _kgl_large_heap_warning_threshold
    maximum heap size before KGL writes warnings to the alert log
    52428800

    _kgl_large_heap_assert_threshold
    maximum heap size before KGL raises an internal error
    524288000

    Please be aware that by setting _kgl_large_heap_warning_threshold to 0 in 12.1.0.2 it is caused the problem described in:

  • MORE：

    Bug 22330282 - “Heap size 0K exceeds notification threshold” alert messages when “_kgl_large_heap_warning_threshold” is set to 0 (Document: 22330282.8)

    To fix this problem either:

    ==> Apply Patch 22330282 or open a backport request with Oracle Support if the patch is not available for your database release and/or platform.

    or

    ==> Either of following options may workaround the issue:
    a. Set _kgl_large_heap_warning_threshold to a very large value.
    b. Set both _kgl_large_heap_warning_threshold and _kgl_large_heap_assert_threshold to zero.

    NOTE: In 11.2.0.4 or 12.1.0.1 in order to have the _kgl_large_heap_assert_threshold parameter available and avoid the Bug 22330282, apply [Patch Bug 22330282](https://support.oracle.com/epmos/faces/ui/patch/PatchDetail.jspx?parent=DOCUMENT&sourceId=330239.1&patchId=Bug 22330282) or open a backport request with Oracle Support if the patch is not available for your database release and/or platform.

参考内容：

🔗 cursor: pin S wait on X

🔗 记一次cursor pin s wait on X的处理

🔗 详解Oracle数据库硬解析、软解析、软软解析联系与区别

🔗 CURSOR: PIN S WAIT ON X IN THE TOP 5 WAIT EVENTS

🔗 ORA-00600 , Arguments: [KGL-heap-size-exceeded]

🔗 PGA Memory Operation Events

🔗 Concurrency Wait Event

🔗 oracle性能优化之awr分析

（3）大表SQL查询执行超时

问题描述：生产环境大批量数据转换，数据未能转换成功，MQ队列堆积了大量消息。

排查过程：

  1. 数据转换通过MQ消息触发，所以首先排查消息队列，发现堆积了很多消息，三天前的任务还未被处理掉。
  2. 排查日志发现有多个SQL一直在执行超时，大大降低了消息的消费速度，导致数据一直不能被成功处理。
  3. 现场测试环境未能复现相同问题，Oracle版本分别为19c和12c。
  4. 排查Oracle等待事件定位到latch: cache buffers chains，等待的SQL与超时的SQL刚好匹配。产生该事件的原因主要是低效的SQL。
  5. 生产环境排查确认有命中索引，PLSQL中单独执行能很快返回。
  6. 获取相应时段的AWR报告，按1小时或半小时，获取超时SQL的执行计划。
  7. DBA协助优化SQL，用hint语法优化not exists和in改为表关联。现场直接实验发现问题解决。

2.3 Oracle STA优化SQL

SQL Tuning Advisor，可以从四个角度给出SQL的优化建议：

  • 为统计信息丢失或失效的对象收集统计信息；
  • 考虑优化器的任何数据偏差、复杂谓词或失效的统计信息；
  • 重新构建 SQL 以优化性能；
  • 提出新索引建议。

简单使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- 声明优化任务，单引号中的单引号要转义，''a''双单引号代替
DECLARE
my_task_name VARCHAR2(30);
my_sqltext  CLOB;
BEGIN
my_sqltext := 'select xxx from xxx';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text    => my_sqltext,
user_name   => 'xxx',  --表示语句由哪个数据库用户执行
scope       => 'COMPREHENSIVE',
time_limit  => 30,
task_name   => 'tuning_sql_test',
description => 'tuning');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test');
END;

-- 上面是传入文本的方式，还可以根据sql_id来创建

-- 获取sql_id
select sql_id,sql_text from v$sqlarea where sql_text like '%bigtab%';

-- 声明优化任务
DECLARE
my_task_name VARCHAR2(30);
sql_id VARCHAR2(30);
BEGIN
sql_id := '&sqlid';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id  => sql_id,
scope => 'comprehensive',
time_limit=>60,
task_name=>'tuning_sql_test',
description => 'Tuning Task');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tuning_sql_test');
END;
/

-- 执行创建的优化任务
exec dbms_sqltune.execute_tuning_task('tuning_sql_test');

-- 查看任务状态
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='tuning_sql_test';

-- 查看优化建议
select DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test') from dual;

-- 删除优化任务
exec dbms_sqltune.drop_tuning_task(task_name => 'tuning_sql_test');

分析结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tuning_sql_test
Tuning Task Owner  : XXX
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_38176
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status  : COMPLETED
Started at         : XXX
Completed at       : XXX

-------------------------------------------------------------------------------
Schema Name: XXX
SQL ID     : 8jzqw0sc2w7dx
SQL Text   : select XXX from XXX

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  通过创建一个或多个索引可以改进此语句的执行计划。

  Recommendation (estimated benefit: 99.71%)
  ------------------------------------------
  - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
    create index XXX.IDX$$_8F240001 on
    XXX.XXX("column_a","column_b","column_c");

  Rationale
  ---------
    创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
    可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  在执行计划的行 ID 2 处发现开销很大的笛卡尔积操作。

  Recommendation
  --------------
  - 考虑从此语句中移去断开连接的表或视图, 或者添加引用它的联接条件。

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 87783638

-----------------------------------------------------------------------------------------
|Id| Operation                         | Name    | Rows |Bytes|Cost (%CPU)|   Time   |
-----------------------------------------------------------------------------------------
| 0|SELECT STATEMENT                   |         |   1  | 127 | 1769 (1)  | 00:00:01 |
| 1|SORT ORDER BY                      |         |   1  | 127 | 1769 (1)  | 00:00:01 |
| 2|MERGE JOIN OUTER                   |         |   1  | 127 | 1768 (1)  | 00:00:01 |
|*3|TABLE ACCESS BY INDEX ROWID BATCHED| XXX     |   1  | 113 | 1767 (1)  | 00:00:01 |
|*4|INDEX RANGE SCAN                   | IDX_XXX | 20627|     | 106  (0)  | 00:00:01 |
| 5|BUFFER SORT                        |         |   1  | 14  | 2   (50)  | 00:00:01 |
| 6|TABLE ACCESS BY INDEX ROWID        | XXX     |   1  | 14  | 1    (0)  | 00:00:01 |
|*7|INDEX UNIQUE SCAN                  | IDX_XXX |   1  |     | 0    (0)  | 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("A"."column_c">=20210720.1054 AND "A"."column_b"=1 AND 
              "A"."column_d"<=20210720.155735)
   4 - access("A"."column_a"=' ' AND "A"."column_e">=0)
   7 - access("B"."column_a"(+)=' ')

2- Using New Indices
--------------------
Plan hash value: 1238368121

-----------------------------------------------------------------------------------------
|Id| Operation                         | Name         | Rows |Bytes|Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                  |              |    1 | 127 |   5 (20)  |00:00:01|
| 1| SORT ORDER BY                     |              |    1 | 127 |   5 (20)  |00:00:01|
| 2| MERGE JOIN OUTER                  |              |    1 | 127 |   4 (0)   |00:00:01|
|*3|TABLE ACCESS BY INDEX ROWID BATCHED| XXX          |    1 | 113 |   3 (0)   |00:00:01|
|*4| INDEX RANGE SCAN                  |IDX$$_8F240001|    1 |     |   2 (0)   |00:00:01|
| 5| BUFFER SORT                       |              |    1 | 14  |   2 (50)  |00:00:01|
| 6| TABLE ACCESS BY INDEX ROWID       | XXX          |    1 | 14  |   1 (0)   |00:00:01|
|*7| INDEX UNIQUE SCAN                 | IDX_XXX      |    1 |     |   0 (0)   |00:00:01|
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("A"."column_e">=0 AND "A"."column_d"<=20210720.155735)
   4 - access("A"."column_a"=' ' AND "A"."column_b"=1 AND "A"."column_c">=20210720.1054)
   7 - access("B"."column_a"(+)=' ')

-------------------------------------------------------------------------------

2.4 临时表优化

参考：MySql临时表

2.5 Explain

参考：执行计划

三. 问题排查

3.1 死锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查看死锁是否存在
select username,lockwait,status,machine,program,sid from v$session where sid in
(select session_id from v$locked_object);

# 查看死锁的语句
select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));
 
# 查找死锁的进程：
sqlplus "/as sysdba" (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS 
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

3.2 等待事件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 查询等待的事件
select inst_id,event,count(*) from gv$session_wait
where wait_class not like 'Idle'
group by inst_id, event order by 3 desc;

# 查询等待事件在执行的SQL
SELECT b.inst_id,b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       sql_fulltext,
       b.machine 计算机名,
       b.EVENT,
       c.SQL_ID,
       c.CHILD_NUMBER
FROM gv$process a, gv$session b, gv$sql c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
and event like '%pin S wait on X%'
   and a.inst_id=1
   and b.inst_id=1
   and c.inst_id=1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# 查询阻塞与被阻塞的SQL
select s.inst_id as inst,
       s.sid as blocked_sid,
       s.username as blocked_user,
       sa.sql_id as blocked_sql_id,
       trunc(s.p2/4294967296) as blocking_sid,
       b.username as blocking_user,
       b.sql_id as blocking_sql_id
from gv$session s
join gv$sqlarea sa
  on sa.hash_value = s.p1
join gv$session b
  on trunc(s.p2/4294967296)=b.sid
 and s.inst_id=b.inst_id
join gv$sqlarea sa2
  on b.sql_id=sa2.sql_id
where s.event is not null or s.event <> '';

# 根据SQL_ID查看SQL内容
select sql_text from v$sql where sql_id='f5qdnpakv26q8';

# 查看会话信息
select * from v$session WHERE OSUSER = 'xxx';
select * from v$session WHERE prev_sql_id = 'f5qdnpakv26q8';
select * from v$session WHERE sql_id = 'f5qdnpakv26q8';

# 查看VERSION_COUNT不正常的SQL
select inst_id,con_id,sql_id,version_count from gv$sqlarea where version_count > 100 order by 3;

# 查看数据库模式
select sys_context('USERENV','CON_NAME') from dual;

参考：

🔗 工作以来总结的大厂SQL调优姿势

🔗 where子句的优化

🔗 SQL子查询的优化

🔗 要提高SQL查询效率where语句条件的先后次序应如何写

🔗 5分钟快速掌握阿里内部MySQL性能优化的核心技术

相关文章