分类目录归档:数据库

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

第八章 为表连接设计索引

基本问题BQ:是否存在或者计划设计一个索引,使它包含所有被 where 子句引用的列。BQ 背后的原理是:保证只有知道 是必须被访问的表行时,才去访问该表。

基本连接问题 BJQ:是否有一个已经存在或计划添加的索引,包含了所有本地谓词对应的列?在表连接中指包含了涉及的所有表的本地谓词。

合并扫描连接和哈希连接

合并扫描连接执行过程如下:

  • 执行表或索引扫描以找出满足本地谓词的所有行。
  • 随后可能会进行排序,如果这些扫描未按所要求的顺序提供结果集。
  • 对前两步生成的结果集进行合并。

在以下情况中,合并扫描连接会比嵌套循环连接快:

  • 用于连接的字段上没有可用的索引。这种情况下,使用嵌套循环,内层表可能需要被扫描很多次。
  • 结果表很大。这种情况下使用嵌套循环会导致相同的页被不断重复访问。
  • 连接查询中不止一张表的本地谓词的过滤因子很低。嵌套循环可能导致对内层表(或者内层表索引)的大量随机访问。

继续阅读

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

第二章 表和索引结构

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>

继续阅读

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 。

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.

继续阅读

MySQL 高性能的索引策略

重新看了一遍做得记录。

独立的列

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

前缀索引和索引的选择性

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

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

多列索引

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

索引列顺序

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

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

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

继续阅读

MySQL datetime 与时区

问题

最近碰到这样的问题:jdbc 插入 timestamp 类型的数据到 MySQL 的 datetime 类型列时,小时会变成 12 小时制了。比如 2017-04-11 15:24:32 变成 2017-04-11 03:24:32

一开始以为是默认时区设置导致,后面发现不是,在 MySQL client 命令行里直接插入是不存在这样问题的。

如果是时区问题,跟本地时间相差不会是 12 小时的。

绕过方法:
1. 直接使用 MySQL 的 now() 函数;
2. 在 Java 里把 timestamp 转换为 yyyy-MM-dd HH:mm:ss 格式字符串,然后用 MySQL 的 convert 函数进行转换。

时区

CET, Central European Time,欧洲中部时间:比世界标准时早一个小时。冬季时间为 UTC+1,夏季欧洲夏令时为 UTC+2

UTC, Coordinated Universal Time, 世界标准时间或世界协调时间:

GMT, Greenwich Mean Time, 格林尼治标准时间。

CST, China Standard Time, 中国标准时间。

CST 同时可以代表如下 4 个不同的时区:
Central Standard Time (USA) UT-6:00
Central Standard Time (Australia) UT+9:30
China Standard Time UT+8:00
Cuba Standard Time UT-4:00

关系:
CET = UTC/GMT + 1小时
CST = UTC/GMT + 8小时
CST = CET + 9小时

show variables like '%time_zone%';
set time_zone='+8:00';

《高性能 MySQL》 — 第五章 创建高性能的索引

索引基础

索引的使用:现在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。

对于有多列的索引,MySQL 只能高效地使用索引的最左前缀列。因此列的顺序很重要。

MySQL 的唯一限制和主键限制都是通过索引实现。

索引的类型

MySQL 中,索引是在存储引擎层而不是服务器层实现的,所以没有统一的索引标准。

B-Tree 索引

B-Tree 通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相等,适合范围查询。

btree-structure

B-Tree 索引适用于全键值、键值范围或键前缀查找,其中键前缀查找只适用于根据最左前缀的查找。

索引还可以用于查询中的 order by 操作。

B-Tree 索引的限制:

  • 如果不是安装索引的最左列开始查找,则无法使用索引。
  • 不能跳过索引中的列。
  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

这些限制是 MySQL 优化器和存储引擎使用索引的方式导致的。

继续阅读

《高性能 MySQL》 — 第四章 Schema 与数据类型优化

选择优化的数据类型

选择数据类型的一般原则:

  • 更小的通常更好:尽量使用可以正确存储数据的最小数据类型。

  • 简单就好:简单数据类型的操作通常需要更少的 CPU 周期。例如整型比字符操作代价更低,以为字符集和校对规则(排序规则)使字符比较比整型更复杂。举例,应该使用 MySQL 内建的类型(date, time, datetime) 而不是字符串来存储日期和时间,应该使用整型来存储 IP 地址。

  • 尽量避免 NULL:如果查询中包含可为 NULL 的列,对 MySQL 来说更难优化,因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间,在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时,每个索引记录需要一个额外的字节,在 MyISAM 里甚至还可能导致固定大小的索引变成可变大小的索引。例外,对于稀疏数据(很多列的值为 NULL),InnoDB 使用单独的位(bit)存储 NULL 值,有很好的空间效率。

在为列选择数据类型时,第一步需要确定合适的大类型:数字、字符串、时间等。下一步是选择具体类型。

继续阅读