工作中遇到的sql优化相关问题总结

本文主要总结下工作以来，所遇到过的sql优化问题，至于一些通用sql优化常识，还有一些简单的场景加加索引index就可以这些情况本文就不描述了，本文主要挑选些工作中曾经遇到过的，个人认为(毕竟我不是专业的dba)还是有点价值的总结下，总体感觉下来，在优化sql的时候要多看sql的执行计划，便于你分析如何去优化sql。

1,mysql相关

在讲述问题前，我们先来准备下相关环境,如下:

数据表:

CREATE TABLE `COMMON_MESSAGE` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `CREATED_BY` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT 'SYS' COMMENT '创建人',
  `UPDATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `UPDATED_BY` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT 'SYS' COMMENT '修改人',
  `VERSION` int(11) NOT NULL COMMENT '版本号',
  `MESSAGE_TYPE` int(11) NOT NULL COMMENT '消息类型',
  `REF_ID` varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT '消息唯一键',
  `MESSAGE_BODY` varchar(4000) COLLATE utf8mb4_bin NOT NULL COMMENT '消息内容',
  `STATUS` int(11) NOT NULL COMMENT '状态（0－未处理，1－已处理）',
  `DEAL_COUNT` int(11) NOT NULL COMMENT '处理次数',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_COMMON_MSG_REFID_TYPE` (`REF_ID`,`MESSAGE_TYPE`),//唯一索引
  KEY `COMMON_MESSAGE_UPT` (`UPDATED_AT`)//普通索引
) ENGINE=InnoDB AUTO_INCREMENT=219376 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='消息通用落地表';

表中有 主键--id,UK--ref_id+message_type,index--updated_at等索引，目前量级在几十万，为了测试方便，我们构造了20w与线上有相同分布的数据

场景:

有如下sql，是一个补偿job执行的，需要捞出48小时内的为处理数据进行补偿(status=0):

select id, created_at, created_by, updated_at, updated_by, version, message_type, ref_id, message_body, status, deal_count from 
COMMON_MESSAGE where 1=1 and STATUS = 0 and updated_at >= '2018-11-29 14:23:00.241' and updated_at < '2018-11-30 14:22:00.241' and id > 0 and deal_count < 5 order by id asc limit 100;

问题：
因为是补偿job处理，满足条件的数据应该很少，sql执行应该很快返回，可实际上：

居然要花费1s多的时间！！！！再看下我们满足条件的总量是:

满足条件的就459条，居然要1s多的查询时间，太坑了吧！下面分析下，该怎么进行优化,首先我们看下这条sql的执行计划:

居然走的pk，不是updated_at上的索引，相当于是进行了全表扫描了(id>0).难怪，下面我们的思路在于如何能够上这条sql走到updated_at索引。

1,从上图中我们可以看到这条sql可能的索引也是有updated_at索引的，so，首先可以想到让sql强制走这个索引:

看到了吧，瞬间性能提升!

2,order by id asc代码改为order by id+0 asc,将order by 后的字段使用函数处理（默认字段+函数处理后无法使用索引）:

可以看到，会走updated_at索引.

3,order by id asc代码改为order by id,updated_at asc , 因为id是唯一，再加入updated_at排序不影响结果，但执行计划会改变，使用updated_at索引:

4,以上三条都是dba给的意见，其实针对这条sql，我们只需要在where条件中把id>0干掉，照样可以走到updated_at索引：

但是如果实际查询中有id>100这样的条件时，还是需要按照前三种办法解决!

2,oracle

oracle的优化本人接触到的主要是使用leading,use_nl对sql进行优化。我们的例子主要涉及到两张数据表(因为要自己线下造数据，将两张表字段进行了精简)，一张表transfer_requests，它是用来记录转让请求历史。其结构和表上的索引如下:

数据表：

索引:

另一张表transfer_request_data主要用于某次转让请求的相关数据，结构如下：

索引：

其中凡是PK打头的索引均表示主键索引，这两张表的数据关系大致是一对多的关系，举个例子说明：

TRANSFER_REQUESTS					TRANSFER_REQUEST_DATA
id	investment_id	status	created_at	code	id	investment_id	transfer_price	created_at	status	product_code
1	1	TRANSFERABLE	sysdate	null	1	1	1000.00	sysdate	TRANSFER_CANCELLED	1
2	1	TRANSFER_APPLIED	sysdate	1
3	1	NON_TRANSFERABLE	sysdate	null
4	1	TRANSFER_CANCELLED	sysdate	1
5	1	TRANSFER_APPLIED	sysdate	1+1	2	1	1000.00	sysdate	TRANSFER_SUCCESS	1+1
6	1	TRANSFER_SUCCESS	sysdate	1+1

表TRANSFER_REQUEST_DATA中，一条TRANSFER_SUCCESS或者TRANSFER_CANCELLED记录会至少对应2条及以上的TRANSFER_REQUESTS记录，这个情况讲明白后，我们在本地TRANSFER_REQUEST_DATA表造了大概一百多万条数据，TRANSFER_REQUESTS表大概四五百万条数据，然后进行我们的实验，精简原来sql，假设我们要执行的sql是:

select tr.id,tr.INVESTMENT_ID,tr.CREATED_AT,trd.TRANSFER_PRICE 
from transfer_requests tr,transfer_request_data trd 
where tr.investment_id=trd.investment_id and trd.status='TRANSFER_SUCCESS' and trd.investment_id=341324 order by tr.id desc;

 在本地机器上执行的实际大约0.7s如图:

我们来看下这条sql的执行计划:

 

其执行顺序为"INDEX RANGE SCAN"----"INDEX FULL SCAN DESCENDING"---"TABLE ACCESS BY INDEX ROWID"---"TABLE ACCESS BY INDEX ROWID"----"BUFFER SORT"----"MERGE JOIN CARTESIAN"----"SELECT STATEMENT,GOAL=ALL_ROWS"，这个执行计划的问题在于:

1,TRANSFER_REQUESTS表查询时应该走索引"IDX_TR_INVESTMENT_ID",而不是根据索引全扫描

2，笛卡尔join是一个费时费力的join,数据量较大时不推荐使用

接下来，看下我们优化sql如下：

select /*+ leading(trd,tr) use_nl(trd,tr) index(trd,IDX_TRANSFER_REQUEST_DATA_INV) index(tr,IDX_TR_INVESTMENT_ID)*/  tr.id,tr.INVESTMENT_ID,tr.CREATED_AT,trd.TRANSFER_PRICE 
from transfer_requests tr,transfer_request_data trd 
where tr.investment_id=trd.investment_id and trd.status='TRANSFER_SUCCESS' and trd.investment_id=341324 order by tr.id desc;

以TRANSFER_REQUEST_DATA作为驱动表(数据较另一个更少),分别指定两表所使用的index，其执行时间为:

其执行时间接近原来的1/9，我们看下其执行计划:

其执行计划很明了了，首先，在TRANSFER_REQUEST_DATA表走investment_id上的索引,然后根据rowid找出满足条件记录(status='TRANSFER_SUCCESS',只有一条)，再根据investment_id在表TRANSFER_REQUESTS找到对应的记录，连接，最后排序，返回所有满足条件的记录。

因为TRANSFER_REQUEST_DATA查出来满足条件的只有一条记录，这里我们可以使用use_hash代替use_nl,如果查出来的数据较多，可能会占用较多内存空间，如果机器内存有限，此时可能就不大适合了。

以后如果遇到新的有价值的问题了，再来补充本文!