《MySQL 实战45讲》–笔记–锁

根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁是对整个数据库实例加锁。MySQL 提供的一个加全局锁的命令: Flush tables with read lock (FTWRL),让整个库处于只读状态。

全局锁的典型使用场景是做全库逻辑备份。

逻辑备份工具 mysqldump 使用参数 -single-transaction 的时候在导数据之前就会启动一个事务,来确保拿到一致性视图。

一致性读的前提是引擎要支持这个隔离级别的事务。

set global readonly=true 也可以让全库进入只读状态。但存在两个风险: readonly 的值可能被用来做其他逻辑,比如判等一个库是主库还是备库;设置 readonly 之后,如果客户端发生异常,则数据库会一直保持 readonly 状态。

在 slave 上,如果用户有超级权限的话,全库只读 readonly=true 是失效的。

FTWRL 执行完后如果客户端异常断开,MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。

继续阅读

《MySQL 实战45讲》–笔记–order by 实现

用 explain 命令查看执行计划的时候,Extra 列的值含有 “using filesort” 表示需要排序,可能是在内存里排序、也可能是磁盘文件排序。MySQL 给每个线程分配一块内存用于排序,称为 sort_buffer ,可以通过参数 sort_buffer_size 调整这个内存的大小。

如果要排序的数据都能放进 sort_buffer 则直接在内存里排序,否则需要借助磁盘临时文件进行辅助排序。

由于 InnoDB 是索引组织表,聚簇索引就是主键索引,下面的描述就用 rowid 替代主键值。

走索引全字段排序

  1. 初始化 sort_buffer,确定要放入 sort_buffer 的目标字段;
  2. 根据选择的索引查找满足条件的 rowid;
  3. 通过rowid到聚簇索引树获取整行数据,取目标字段放入sort_buffer;
  4. 从选择的索引取下一个记录的rowid;
  5. 重复步骤 3、4直到不满足条件;
  6. 对 sort_buffer 中的数据按照排序列进行排序。
  7. 遍历排序结果,把目标数据返回给客户端。

继续阅读

InnoDB Sorted Index Build

Sorted Index Build

InnoDB 在创建或重建索引时采用 批量加载而不是一次插入一个索引记录的方式。这种索引创建方法也称为 Sorted Index Build ,该方式不支持空间索引。

索引创建有三个阶段:

  1. 第一阶段,扫描聚簇索引,生成索引条目并添加到排序缓存(sort buffer)。当排序缓存满了后,条目被排序、写入临时的中间文件。这个过程也称为 run 。

  2. 第二阶段,一个或多个 run 写入临时中间文件,对文件里的所有条目执行合并排序。

  3. 在第三阶段,排序后的条目被插入到 B+tree 上。

Sorted Index build 采用自底向上方式来构建索引,采用这种方式,在 B+Tree 的每一层,持有对最右叶子页的引用。位于B+Tree需要的深度的最右叶子页被分配,条目按它们排序后的顺序插入。一旦叶子页满了,一个节点指针追加到父页上,同层叶子页被分配用于接下来的插入。这个过程持续至所有条目插入完成,这可能导致插入一直到顶层。

白话解读:最底层的最右叶子页插入索引记录,如果当前最右叶子页满了,生成一个新的最右叶子页作为接下来的插入位置,在新旧最右叶子页之间建立指针链接,然后把指向旧最右叶子页的指针追加到上一层的最右叶子页,触发上一层的最右叶子页插入,逻辑跟前面是一样的,这是个递归的过程,直至根页。这是一个自底向上、递归构建的过程

最终生成的索引结构如下图(图片来自MySQL内核解析:Innodb页面存储结构-1):
InnoDB索引结构

为后续插入保留空间

可以使用 innodb_fill_factor 配置选项来指定在 B+Tree 页空间保留的空闲百分比,用于后续在这些位置插入索引条目。

这个设置应用于索引树的叶子和非叶子页。不适用于外部页如用于 TEXT/CLOB 条目的。

这个配置是一个提示而非强制约束。

MySQL InnoDB 二级索引的排序

排序问题

最近看了极客时间上 《MySQL实战45讲》,纠正了一直以来对 InnoDB 二级索引的一个理解不到位,正好把相关内容总结下。PS:本文的所有测试基于 MySQL 8.0.13 。

先把问题抛出来,下面的 SQL 所创建的表,有两个查询语句,哪个索引是非必须的?

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

作者给的答案是索引 c 和 ca 的数据模型是一样的,因此 ca 是多余的。为啥??

我们知道,二级索引里存放的不是行的位置,而是主键的值,也知道索引是有序的。

如果 c 与 ca 的数据模型一样,那么就要求二级索引的叶子节点不仅是按索引列排序、而且还按关联的主键值进行排序

我以前的理解是 二级索引只按索引列进行排序,主键值是不排序的。

问了专栏作者,得到的答复是:索引 c 就是按照 cab 这样排序,(二级索引))有保证主键算进去、还是有序的。(PS:非原话,前后问了三次得到)。

本着 先问是不是,再问为什么 的思路,进行一番探究。

继续阅读

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

第八章 为表连接设计索引

基本问题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 。


欢迎关注我的微信公众号: 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.

继续阅读