orczhou.com

testing-04

MySQL

  • 《高性能MySQL》第三版

    ·

    orczhou

    本文是一篇写给《HPM 3rd 中文版》的软文，慎入。《HPM 3rd 中文版》已经开始正式发售了，不是预售：亚马逊 china-pub 当当网

    从去年5月开始，与宁海元、翟卫祥、彭立勋、刘辉一起利用业余时间，经历了翻译，校对，校对，再校对，交叉校对，再交叉校对，到前两天亚马逊上正式开售(不是预售了)，前前后后也历经了大概一年。

    在过去的两三年，MySQL的生态圈发生了很大的变化，出现了MariaDB，Percona/XtraDB等等分支，与官方的版本产生了一些竞争。目前为止这些竞争还是比较良性的，都大大推动了MySQL在各个方面的改进，包括MySQL的性能和新的功能，这期间在社区对于InnoDB的改进(例如XtraDB)，推进了MySQL/Oracle快速的推进了InnoDB Plugin的发展；MariaDB在优化器方面也做了很多工作，对应的MySQL/Oracle在5.6之后的版本也做了很多Server层(例如优化器、Group Commit等)的改进。

    虽然经历了收购的风波，但在竞争压力下，过去两三年仍然是MySQL/MariaDB快速发展的时期。08年High Performance MySQL(简称HPM)发布第二版，时隔四年发布了第三版，第三版中新增了分区、视图、存储过程方面的改进，高可用、集群和复制方面的改进，优化器全文索引等改进，SSD和多核CPU利用方面的改进，在线备份和恢复的工具等。是一本非常值得阅读的MySQL书籍。

    对于专注数据库领域的人来说，如果习惯阅读英文版本的，依旧推荐阅读英文版本，在亚马逊上可以买到HPM 3rd影印版的。如果不太习惯阅读英文版本的，我仍然强烈推荐阅读影印版，虽然这样可能会花更多的时间，但是可以大大锻炼一下自己的英文能力，相信你不会因为这个”浪费”时间而后悔的。

    如果，你喜欢阅读中文书籍，或者希望能够快速阅读，那么这次翻译的HPM 3rd会是很好的选择。这次译者都是专业/一线的MySQL DBA或者开发人员，并进行了多次(交叉)校对，是一次高要求、高质量的翻译，不会让你失望。 (more…)

  • 案例：MySQL优化器如何选择索引和JOIN顺序

    ·

    orczhou

    本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序。表结构和数据准备参考本文最后部分”测试环境”。这里主要介绍MySQL优化器的主要执行流程，而不是介绍一个优化器的各个组件(这是另一个话题)。

    我们知道，MySQL优化器只有两个自由度：顺序选择；单表访问方式；这里将详细剖析下面的SQL，看看MySQL优化器如何做出每一步的选择。

    explain 
select * 
from 
  employee as A,department as B 
where 
      A.LastName = 'zhou' 
  and B.DepartmentID = A.DepartmentID 
  and B.DepartmentName = 'TBX';

    1. 可能的选择

    这里看到JOIN的顺序可以是A|B或者B|A，单表访问方式也有多种，对于A表可以选择：全表扫描和索引`IND_L_D`(A.LastName = ‘zhou’)或者`IND_DID`(B.DepartmentID = A.DepartmentID)。对于B也有三个选择：全表扫描、索引IND_D、IND_DN。

    2. MySQL优化器如何做

    2.1 概述

    MySQL优化器主要工作包括以下几部分：Query Rewrite(包括Outer Join转换等)、const table detection、range analysis、JOIN optimization(顺序和访问方式选择)、plan refinement。这个案例从range analysis开始。

    2.2 range analysis

    这部分包括所有Range和index merge成本评估(参考1 参考2)。这里，等值表达式也是一个range，所以这里会评估其成本，计算出found records(表示对应的等值表达式，大概会选择出多少条记录)。

    本案例中，range analysis会针对A表的条件A.LastName = ‘zhou’和B表的B.DepartmentName = ‘TBX’分别做分析。其中： (more…)

  • Oracle如何根据SQL_TEXT生成SQL_ID

    ·

    orczhou

    本文纯属八卦，基本没有任何实用价值。Oracle总是都会通过SQL_ID来标志一个唯一的SQL。SQL_ID与SQL_TEXT一一对应。如果两个SQL文本有任何不同，包括空格等任何不可见字符，都会导致SQL_ID不同。本文八卦的内容是：Oracle如何根据SQL_TEXT内容散列成一个13位的字符串。为什么这个字符串会是13位？为什么这个字符经常以数字开头？

    本文参考TANEL PODER和Slavik的两篇介绍(1，2)，详细介绍转换原理，顺便给出PHP/Perl实现代码。

    0. 概述

    Oracle先计算SQL_TEXT的md5散列值；取散列值的低64位(bits)，每次取5位(最后一次4位)，使用Base32将其依次转换成可见字符，就是你最终看到的SQL_ID。原理就是这样。

    不过实际转换过程中有一些要注意的事项：

    (a) Oracle在计算md5散列时，会在SQL_TEXT末尾加一个不可见字符\0，AWR报表中经常有这样的SQL_TEXT

    (b) 注意little-endian的问题

    (c) Base32转码的可见字符为0123456789abcdfghjkmnpqrstuvwxyz

    (d) 编写程序的时候需要注意大数精度的问题，本文中Perl/PHP程序都使用了数学大数处理函数 (more…)

  • index merge的补充说明

    ·

    orczhou

    在除了前面介绍的常见index merge的案例(Index Merge Union Access Algorithm)之外，还有一类很少见也比较特殊的index merge，多个索引扫描后进行交集，即 Index Merge Intersection。这类执行计划比较少见(因为MySQL需要ROR的原因)，但是，在合适的场景使用，效率仍然会有很大的提示，本文将看看MySQL优化器如何评估和选择此类执行计划。MySQL手册对此只是三言两语简单介绍了一下，这里做个较为详细的说明。

    这类执行计划完整名称应该是：The Index Merge Intersection Access Algorithm，下文简称Intersection。

    1. 为什么需要考虑Intersection

    考虑如下查询:

    SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

    优化器可以考虑使用索引key1或者key2进行REF/Range访问，如果使用key1，那么key2=1则作为过滤条件。另外，优化器还会考虑使用Intersection，即同时使用索引key1和key2。这样做可能的好处是：

    (a) 如果两次索引扫描后做交集，如果最后ROWID很少，则回表次数大大减少

    (b) 如果扫描这两个索引能是覆盖扫描的话，则无需回表 (more…)

  • index merge的数据结构和成本评估

    ·

    orczhou

    前面以案例的形式介绍了什么是index merge，以及它的使用场景。本文将介绍index merge实现的主要数据结构以及MySQL如何评估index merge的成本。在开始本文之前，需要先理解Range访问相关的数据结构介绍：SEL_ARG结构，SEL_TREE结构。 (more…)

  • MySQL优化器：index merge介绍

    ·

    orczhou

    在MySQL官方手册上，关于index merge的介绍非常非常少。甚至还有不少误导的地方，这次把5.1版本关于此类优化处理的代码细看了一遍，以案例的方式介绍了各种实用index merge访问类型的SQL。后续的还会继续介绍index merge实现的主要数据结构，以及成本评估。

    1. 什么是index merge

    MySQL优化器如果发现可以使用多个索引查找后的交集/并集定位数据，那么MySQL优化器就会尝试index merge这类访问方式。index merge主要分为两大类，多个索引交集访问(intersections)，多个索引并集访问，当然这两类还可以组合出更为复杂的方式，例如多个交集后做并集。

    1.1 index merge的限制：range优先

    MySQL在5.6.7之前，使用index merge有一个重要的前提条件：没有range可以使用。这个限制降低了MySQL index merge可以使用的场景。理想状态是同时评估成本后然后做出选择。因为这个限制，就有了下面这个已知的bad case(参考)：

    SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

    优化器可以选择使用goodkey1和goodkey2做index merge，也可以使用badkey做range。因为上面的原则，无论goodkey1和goodkey2的选择度如何，MySQL都只会考虑range，而不会使用index merge的访问方式。这是一个悲剧…（5.6.7版本针对此有修复) (more…)