《数据库索引设计与优化》笔记一

第二章 表和索引结构

B-Tree-index

索引页和表页

目前页的大小一般是 32K64K 的。页的大小决定了一个页可以存储多少个索引行、表行,以及一共需要多少个页来存储表或者索引。每个页都会预留一定比例的空闲空间,以满足向其添加新的行。缓冲池和 I/O 活动都是基于页的

索引行

对于唯一索引,一个索引行等同于叶子页中的一个索引条目。字段值没表中复制到索引上,并加上一个指向表中记录的指针。

对于非唯一索引,一个索引值后带着多个指针,指针指向下一层非叶子页或叶子页,叶子页的指针指向表中的记录。

B 树索引结构

非叶子页包含着一个键值,以及一个指向下一层级页的指针,该键值是下一层级页中的最大键值。多个索引层级按照这一方式逐层建立,直到根页。

(对于非唯一索引)通过这种方式来查找任何一条索引记录都需要访问相同数量的非叶子页。

第三章 SQL 处理过程

谓词是索引设计的主要入手点。

第四章 为 select 语句创建理想的索引

三星索引评定:

  • 第一颗星:与查询相关的索引行是相邻的,或者至少相距足够靠近。最小化了必须扫描的索引片的宽度。
  • 第二颗星:索引行的顺序与查询语句的需求一致。排除了排序操作。
  • 第三颗星:索引行包含查询语句中的所有列。避免了回表。

宽索引:款索引是指一个至少满足了第三颗星的索引。该索引包含了 select 语句所涉及的所有列,因此能够使得查询只需访问索引而无须回表。

继续阅读

一次结合业务、技术综合进行的 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 。


欢迎关注我的微信公众号: coderbee笔记,可以更及时回复你的讨论。

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 方法是把参数与属性元素直接对比。

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


欢迎关注我的微信公众号: coderbee笔记,可以更及时回复你的讨论。

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.

继续阅读