一次结合业务、技术综合进行的 SQL 优化过程

一、问题 sql

最近有个查询页面特别慢,这个页面有 12 个输入框,但都是可选的,默认是没有输入值的。

下面是这个搜索的原始 SQL 语句,写在 MyBatis 的 XML 文件里的,通过 if 语句在相应的搜索参数不为空时拼接对应的过滤条件。

最悲观输入的情况下,SQL 等价于下面的简化版:

SELECT *
  FROM (SELECT A.*, ROWNUM RN
          FROM (select C.*
                  from (select distinct AI.SerialNo as aiSerialNo,
                                        AI.inputDate,
                                        FD.phaseName,
                                        getItemName('BusinessStatus',
                                                    AI.ApplyStatus) as applyStatus,
                                        getBusinessName(AI.BusinessType) as businessType,
                                        getItemName('LType', AI.LOANTYPE) as lType,
                                        getSellerName(AI.OperateUserID) as opUname,
                                        getusername(AI.Interviewerid) as iName,
                                        getorgname(AI.OperateOrgID) as opOrgName,
                                        bi.Serialno,
                                        substr(ai.properties, 9, 1) as a2
                          FROM t_ai AI
                          LEFT JOIN t_ci CI
                            ON AI.SERIALNO = CI.APPLYSERIALNO
                           and CI.PUTOUTTYPE in ('010', '030')
                          LEFT JOIN t_bi BI
                            ON BI.CONTRACTSERIALNO = CI.SERIALNO
                          LEFT JOIN t_tj tj
                            on tj.loanno = BI.serialno, t_fd FD, t_pc PC, t_pw pw
                         WHERE AI.SERIALNO = FD.OBJECTNO
                           AND AI.SERIALNO = PC.OBJECTNO
                           and AI.SERIALNO = PW.OBJECTNO
                           and (AI.applystatus in ('103', '104') and
                               FD.endtime is not null and
                               fd.serialno =
                               (select max(t.serialno)
                                   from t_fd t
                                  where t.objectno = ai.serialno) or
                               (AI.applystatus not in ('103', '104') and
                               FD.endtime is null))
                           and AI.OPERATEORGID in
                               (SELECT t_b_org.BELONGORGID
                                  FROM t_b_org t_b_org
                                 WHERE t_b_org.ORGID IN
                                       (SELECT ORGID
                                          FROM t_org
                                         WHERE SALESUPER = :3))
                        ) C
                 order by C.inputDate, C.aiSerialNo) A
         WHERE ROWNUM < :1)
 WHERE RN >= :2

这个 SQL 的执行计划总的 cost 值为 13902 。大表都是全表扫描。

二、优化过程

1. 检查表的关联

把 sql 语句拷贝到 notepad++,双击表的别名,会用绿色高亮显示。

  • 发现 tj 这个表既没有用于 where 子句进行过滤,也不在 select 子句里。 结合 select distinct, 可以确定 left join 这个表对最终结果集是没有任何影响的。可以安全地移除对这个表的关联。

  • 发现 pc、pw 这两个表都与 ai 表关联,且与 ai 是一对一的关系。但它们并没有出现在 select 子句,在 where 子句也只是在相应的参数出现时才拼接过滤语句。也就是:如果过滤条件出现,那么关联这个表是能过滤数据的,如果过滤条件不出现,关联不关联这个表的结果都是一样的。既然这样,就可以利用 MyBatis 的 if 条件性地关联这个表。如下:

<if test="mobilePhone != null and mobilePhone != ''.toString() or familyTel != null and familyTel != ''.toString()">
    join t_pc PC on AI.SERIALNO = PC.OBJECTNO
    <if test="mobilePhone != null and mobilePhone != ''.toString()">
      and (PC.MOBILETELEPHONE = #{mobilePhone , jdbcType=VARCHAR} or PC.MOBILETELEPHONE2 = #{mobilePhone , jdbcType=VARCHAR})
    </if>
    <if test="familyTel != null and familyTel != ''.toString()">
      and PC.FAMILYTEL = #{familyTel , jdbcType=VARCHAR}
    </if>
</if>

继续阅读

Spring 事务管理的一个 trick

问题

最近有同事碰到这个异常信息: Transaction rolled back because it has been marked as rollback-only ,异常栈被吃了,没打印出来。

调用代码大概如下:

@Component
public class InnerService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional(rollbackFor = Throwable.class)
    public void innerTx(boolean ex) {
        jdbcTemplate.execute("insert into t_user(uname, age) values('liuwhb', 31)");
        if (ex) {
            throw new NullPointerException();
        }
    }

}

@Component
public class OutterService {
    @Autowired
    private InnerService innerService;

    @Transactional(rollbackFor = Throwable.class)
    public void outTx(boolean ex) {
        try {
            innerService.innerTx(ex);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

outterService.outTx(true);

他期望的是 innerService.innerTx(ex); 调用即使失败了也不会影响 OutterService.outTx 方法上的事务,只回滚了 innerTx 的操作。

结果没有得到他想要的,调用 OutterService.outTx 的外围方法捕获到了异常,异常信息是 Transaction rolled back because it has been marked as rollback-onlyoutTx 的其他操作也没有提交事务。

分析

上述方法的事务传播机制的默认的,也就是 Propagation.REQUIRED,如果当前已有事务就加入当前事务,没有就新建一个事务。

事务性的方法 outTx 调用了另一个事务性的方法 innerTx 。调用方对被调用的事务方法进行异常捕获,目的是希望被调用方的异常不会影响调用方的事务。

但还是会影响调用方的行为的。Spring 捕获到被调用事务方法的异常后,会把事务标记为 read-only,然后调用方提交事务的时候发现事务是只读的,就会抛出上面的异常。

继续阅读

Feign

简介

Feign 是一个 Java 到 HTTP 客户端的粘合剂。Feign 的目标是以最少的开销和代码把 你的代码连接到 http api 上。通过定制的编解码器和错误处理,你可以请求任何基于文本的 http api 。

原理

通过处理注解信息来生成模板化请求。在发出请求前,参数直接应用到这些模板上。这限制了 Feign 只支持基于文本的 api,这显著简化了系统的一些方面如重放请求。

为什么选择 Feign

  • 依赖问题。目前项目组用的是 Hessian 做远程调用,由于 Hessian 存在对 jar 包的依赖,特别是一些项目升级到 JDK 1.8,采用 Maven 构建;而老的一些任然采用 1.6 ,是个简单的 Eclipse 工程,导致打包、部署非常麻烦。

  • 依赖于接口,使用简洁。对于客户端,只依赖于接口类,通过 Spring 注入实现,迁移基本不需要很大的改动。

  • 与 Spring Cloud 集成。Feign 本身是 Spring Cloud 的一个组件,可以通过 Ribbon 做路由,可以进一步提升服务化。

  • 系统对性能的要求并不是那种很严苛的,基于文本的调用也方便调试。

继续阅读

流水账式开发 VS. 有重点的开发

流水账日记

小时候写日记很可能出现这样的:

今天早上我7点钟起床,起床后刷牙、洗脸,然后吃早餐,吃了早餐去上学。去到学校,第一节是语文课,语文课下课后跟小明一起玩,然后上数学课,数学课下课后也是跟小明一起玩,然后上体育课,上完体育课我们吃午餐、午睡。。。。(中间省略一千字)下午4点半下课后我回家,回到家我先吃了个雪糕,然后开始写语文作业,写完语文作业写数学作业。。。(再次省略一千字)。。。今天我度过了快乐、充实的一天。

这样的日记如白开水般平淡无味地描述了一天的经过,读完之后让人一脸茫然,不知重点是什么、要关注什么。

一个开发任务

现在有个开发任务:从数据库的 t_smsinfo 表取从未发送或发送失败3次以下的短信进行发送。如果发送成功了就标记为成功不再再次发送;如果发送失败了就记录失败的原因、增加失败次数,失败次数达到3次的就不再重试。

t_smsinfo 表有下列字段:

  • id:唯一标识符;
  • mobile:目标手机号;
  • text:短信内容;
  • send_by_comp:发送短信的公司,目标是支持以多家公司的名义发送;
  • msg_type:消息类型,因为有不同的业务场景,希望做区分;
  • status 表示发送状态,它的取值为: ‘W’ 表示未发送,’F’ 表示发送失败,’G’ 表示发送中。
  • sendout_time:最近一次发送时间。
  • fail_reason:最近一次发送失败的原因,希望分析失败原因;
  • fail_times 表示失败的次数,默认是 0 。

继续阅读

MySQL 5.7 重置 root 密码

以安全模式启动,这样登录不需要密码:

mysqld_safe --skip-grant-tables &

用 root 用户登录

mysql -u root

更新密码、刷新权限:

UPDATE mysql.user
    SET authentication_string = PASSWORD('SonarQB-0609.'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

退出安全模式,重新启动 MySQL 。

java.util.Collections.singleton*

今天在抄 Motan 的代码时才发现 java.util.Collections 有三个以 singleton 开头的方法:

  • public static <T> List<T> singletonList(T o):返回一个内部类 SingletonList 的实例。

  • public static <T> Set<T> singleton(T o):返回一个内部类 SingletonSet 的实例。

  • public static <K,V> Map<K,V> singletonMap(K key, V value):返回一个内部类 SingletonMap 的实例。

这三个内部类都是非常高效的:
* SingletonListSingletonSet 都用一个属性来表示拥有的元素,而不是用数组、列表来表示;SingletonMap 分别用两个属性表示 key/value;内存使用上更高效;
* 在方法的实现上也更高效,减少了循环。比如 size 方法都是直接返回 1 ;List.contains 方法是把参数与属性元素直接对比。

真是一种追求性能极限的精神!我们要充分利用好这些特性。

Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle

引言

Oracle 使用两种引擎来处理 PL/SQL 代码。所有存储过程的代码由 PL/SQL 引擎处理,所有的 SQL 由 SQL 语句执行器/SQL 引擎处理。

在两个引擎直间的上下文切换会带来开销。如果 PL/SQL 代码在一个集合上循环,为集合里的每个元素执行同样的 DML 操作,那么可以通过一次 bulk 绑定整个集合到 DML 语句上以减少上下文切换。

BULK COLLECT

Bulk 绑定可以在从查询里加载数据集时提升性能。BULK COLLECT INTO 把查询的数据结构化绑定到集合上。

CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name,
       object_id
FROM   all_objects;


--  使用
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();
  l_start  NUMBER;
BEGIN

  SELECT *
  BULK COLLECT INTO l_tab
  FROM   bulk_collect_test;

END;

集合是维护在内存里的,因此从一个大查询里做 bulk collect 会对性能有显著的影响。事实上,你不应该以这样的方式直接使用 bulk collect 。应当使用 LIMIT 子句限制返回的行数,这让你得到 bulk 方式的好处,又不会占用大量的服务器内存。

继续阅读

append hint, direct-path insert

一份笔记。

1. append hint

直接加载插入(direct load insert, direct-path insert)是运行 insert 语句的一种快速方法。对于加载大量数据行特别有用。

1.1 append hint 如何影响性能

  • 数据被追加到表的末尾,而不是尝试使用表里已存在的空闲空间。
  • 数据被直接写到数据文件,避开了 (写,buffer)缓冲、(读,cache)缓存。
  • 引用完整性约束将不会考虑。
  • 触发器的处理将不会执行。

后面两点可能导致数据逻辑损化,因此,如果表上允许引用完整性约束和触发器,Oracle 忽略 append hint 并以传统的 insert 方式加载数据。

1.1.1 append hint 对表大小的影响(高水位线, high water mark)

由于直接路径插入把数据追加到表的末尾,它们不断地增加表的高水位线,即使表里还有很多空闲空间。append hint 可能导致很大的表里包含了很多稀疏填充的块。这可以通过下面的收缩操作来管理:

  • 导出数据、 truncate 表然后导入数据。
  • 使用 create table ... as select(CTAS) 操作来构建新的表,让数据压缩,删除原始表,重命名新表来替代原始的。
  • 使用 online table redefinition 操作来重新创建表。
  • 使用 online segment shrink 操作压缩数据。

1.1.2 How the APPEND Hint Affects Redo Generation

If the database is running on NOARCHIVELOG mode, using just the APPEND hint will reduce redo generation. In reality, you will rarely run OLTP databases in NOARCHIVELOG mode, so what happens in ARCHIVELOG mode? In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation unless the table is set to NOLOGGING.

继续阅读

HttpURLConnection 自动 重复 提交 POST

一、问题

测试环境偶尔反馈有个服务 HTTP 请求重复提交。这个 HTTP 请求头里有个 UUID 作为唯一标识,会存入日志表里作为主键的,因为主键冲突,服务端是有打印异常信息的,但客户端完全正常,没有任何错误信息,业务逻辑也正常完成了。

通过查看客户端、服务端两边的日志,怀疑是 HttpURLConnection 自动进行了请求重试。在网上搜索一番,发现已经有人提了 bug JDK-6427251 ,按照这个的操作步骤是可以复现的,包括 JDK 8 的版本。

HttpURLConnection 采用 Sun 私有的一个 HTTP 协议实现类: HttpClient.java

关键是下面这段发送请求、解析响应头的方法:

  569       /** Parse the first line of the HTTP request.  It usually looks
  570           something like: "HTTP/1.0 <number> comment\r\n". */
  571   
  572       public boolean parseHTTP(MessageHeader responses, ProgressSource pi, HttpURLConnection httpuc)
  573       throws IOException {
  574           /* If "HTTP/*" is found in the beginning, return true.  Let
  575            * HttpURLConnection parse the mime header itself.
  576            *
  577            * If this isn't valid HTTP, then we don't try to parse a header
  578            * out of the beginning of the response into the responses,
  579            * and instead just queue up the output stream to it's very beginning.
  580            * This seems most reasonable, and is what the NN browser does.
  581            */
  582   
  583           try {
  584               serverInput = serverSocket.getInputStream();
  585               if (capture != null) {
  586                   serverInput = new HttpCaptureInputStream(serverInput, capture);
  587               }
  588               serverInput = new BufferedInputStream(serverInput);
  589               return (parseHTTPHeader(responses, pi, httpuc));
  590           } catch (SocketTimeoutException stex) {
  591               // We don't want to retry the request when the app. sets a timeout
  592               // but don't close the server if timeout while waiting for 100-continue
  593               if (ignoreContinue) {
  594                   closeServer();
  595               }
  596               throw stex;
  597           } catch (IOException e) {
  598               closeServer();
  599               cachedHttpClient = false;
  600               if (!failedOnce && requests != null) {
  601                   failedOnce = true;
  602                   if (httpuc.getRequestMethod().equals("POST") && (!retryPostProp || streaming)) {
  603                       // do not retry the request
  604                   }  else {
  605                       // try once more
  606                       openServer();
  607                       if (needsTunneling()) {
  608                           httpuc.doTunneling();
  609                       }
  610                       afterConnect();
  611                       writeRequests(requests, poster);
  612                       return parseHTTP(responses, pi, httpuc);
  613                   }
  614               }
  615               throw e;
  616           }
  617   
  618       }

在第 600 – 614 行的代码里:

  • failedOnce 默认是 false,表示是否已经失败过一次了。这也就限制了最多发送 2 次请求。
  • httpuc 是请求相关的信息。
  • retryPostProp 默认是 true,可以通过命令行参数(-Dsun.net.http.retryPost=false)来指定值。
  • streaming:默认 falsetrue if we are in streaming mode (fixed length or chunked) 。

通过 Linux 的命令 socat tcp4-listen:8080,fork,reuseaddr system:"sleep 1"\!\!stdout 建立一个只接收请求、不返回响应的 HTTP 服务器。
对于 POST 请求,第一次请求发送出去后解析响应会碰到流提前结束,这是个 SocketException: Unexpected end of file from serverparseHTTP 捕获后发现满足上面的条件就会进行重试。服务端就会收到第二个请求。

继续阅读

MySQL 高性能的索引策略

重新看了一遍做得记录。

独立的列

索引列不能是表达式的一部分,也不能是函数的参数。在 where 语句里,始终将索引列单独放在比较符号的一侧。

前缀索引和索引的选择性

选择性是指不重复的索引值和数据表的记录总数的比值。
选择性越高查询效率越高,唯一索引的选择性是 1.

对于很长的索引列,判等它的前缀是否有足够的选择性。
MySQL 无法用前缀索引做 order bygroup by ,也无法用前缀索引做覆盖扫描。

多列索引

MySQL 5.0 引入索引合并策略。索引合并使用的范围: OR 条件的联合,AND 条件的相交,组合前两种条件的组合和相交。
索引合并暗示着不良的设计,可以考虑组合索引。

索引列顺序

正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要。

将选择性搞的列放在最前列,通常不如避免随机 I/O 和排序重要。
不需要考虑排序、分组时,应将选择性最高的列放在最前面。

性能不只是依赖于所有索引的列的选择性,也和查询条件的具体值有关,也就是和值的分布有关。

继续阅读