MySQL技术内幕：SQL编程

姜承尧

第1章 SQL编程

>> B是由MySQL创始人之一Monty分支的一个版本。在MySQL数据库被Oracle公司收购后，Monty担心MySQL数据库发展的未来，从而分支出一个版本。这个版本和其他分支有很大的不同，其默认使用崭新的Maria存储引擎，是原MyISAM存储引擎的升级版本

>> 在这里，我把SQL编程分为三个阶段，当然不是每个人都必须同意笔者的观点。

第一阶段是面向过程化的SQL编程阶段。

>> 在这一阶段，经常会有滥用各种工具（如游标、临时表、动态SQL语句等）的情况，而程序员自己通常意识不到他们正在引起破坏。

>> 第二阶段是面向集合的SQL编程阶段。

>> 从这一阶段开始，程序员开始相信那些说游标、临时表、动态SQL有害而永远不应该使用的“专家”。

>> 第三阶段是融合的SQL编程阶段

>> 在这一阶段，SQL程序员不再迷恋所谓的专家，他们可能意识到即使是游标，也并不是在所有情况下都是无用和有害的。

>> 对于表中数据的存储，InnoDB存储引擎采用了聚集（clustered）的方式，每张表都是按主键的顺序进行存储的，如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

第2章 数据类型

>> 如果在数据

>> UNSIGNED属性就是将数字类型无符号化，与C、C++这些程序语言中的unsigned含义相同。

>> 看起来这是一个不错的属性选项，特别是对于主键是自增长的类型，因为一般来说，用户都希望主键是非负数。然而在实际使用中，UNSIGNED可能会带来一些负面的影响，示例如下：

>> 和其他数据库一样，MySQL的确存在一些Bug，其实并不是MySQL数据库的Bug比较多，去看一下Oracle RAC的Bug，那可能就更多了，它可是Oracle的一款旗舰产品。

>> 笔者个人的看法是尽量不要使用UNSIGNED，因为可能会带来一些意想不到的效果。另外，对于INT类型可能存放不了的数据，INT UNSIGNED同样可能存放不了，与其如此，还不如在数据库设计

>> 阶段将INT类型提升为BIGINT类型。

>> 可以看到int（10），这代表什么意思呢？整型不就是4字节的吗？这10又代表什么呢？其实如果没有ZEROFILL这个属性，括号内的数字是毫无意义的

>> 就是ZEROFILL属性的作用，如果宽度小于设定的宽度（这里的宽度为4），则自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。

>> SQL_MODE可以设置的选项。

STRICT_TRANS_TABLES：

>> ALLOW_INVALID_DATES：该选项并不完全对日期的合法性进行检查，只检查月份是否在1～12之间，日期是否在1～31之间

>> ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它将被解释为识别符，

>> 然而从MySQL 5.6.4版本开始，MySQL增加了对秒的小数部分（fractional second）的支持，具体语法为：

>> 其中，type_name的类型可以是TIME、DATETIME和TIMESTAMP。fsp表示支持秒的小数部分的精度，最大为6，表示微秒（microseconds）；默认为0，表示没有小数部分，同时也是为了兼容之前版本中的TIME、DATETIME和TIMESTAMP类型。对于时间函数，如CURTIME（）、SYSDATE（）和UTC_TIMESTAMP（）也增加了对fsp的支持，例如：

>> TIMESTAMP占用4字节，显示的范围为“1970-01-0100:00:00”UTC到“2038-01-19 03:14:07”UTC。其实际存储的内容为“1970-01-0100:00:00”到当前时间的毫秒数。

>> UTC协调世界时，又称世界统一时间、世界标准时间和国际协调时间。它从英文Coordinated Universal Time和法文Temps Universel Cordonné而来。

>> CURRENT_TIMESTAMP是NOW的同义词，也就是说两者是相同的。

SYSDATE函数返回的是执行到当前函数时的时间，而NOW返回的是执行SQL语句时的时间。

>> 一般来说表中都会有一个对日期类型的索引，如果使用上述的语句，优化器绝对不会使用索引，也不可能通过索引来查询数据，因此上述查询的执行效率可能非常低。

>> 演示前，需要确认已经安装了MySQL官方的示例数据库employees

>> 一旦启用ZEROFILL属性，MySQL数据库为列自动添加UNSIGNED属性，

>> MySQL数据库支持两种浮点类型：单精度的FLOAT类型及双精度的DOUBLE PRECISION类型。这两种类型都是非精确的类型，经过一些操作后并不能保证运算的正确性，例如M*G/G不一定等于M，虽然数据库内部算法已经使其尽可能的正确，但是结果还会有偏差

>> 为了保证最大的可移植性，需要使用近似数值数据值存储的代码，使用FLOAT或DOUBLE PRECISION，并不规定精度或位数

>> DECIMAL和NUMERIC类型在MySQL中被视为相同的类型，用于保存必须为确切精度的值。

>> DECIMAL或NUMERIC的最大位数是65，但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。如果分配给此类列的值的小数点后位数超过指定的标度允许的范围，值将按该标度进行转换。（具体操作与操作系统有关，一般结果均被截取到允许的位数）。

>> 位类型，即BIT数据类型可用来保存位字段的值。BIT（M）类型表示允许存储M位数值，M范围为1到64，占用的空间为（M+7）/8字节。

>> 因为采用位的存储方式，所以不能直接查看

>> 数字辅助表是一个只包含从1到N的N个整数的简单表，N通常很大。因为数字辅助表是一个非常强大的工具，可能经常需要在解决方案中用到它，笔者建议创建一个持久的数字辅助表，并根据需要填充一定数据量的值。

>> 实际上如何填充数字辅助表无关紧要，因为只需要运行这个过程一次。不过还可以对填充语句进行优化。

>> utf8目前被视为utf8mb3，即最大占用3个字节空间，而utf8mb4可以视做utf8mb3的扩展。

>> 对BMP（Basic Multilingual Plane）字符的存储，utf8mb3和utf8mb4

>> 两者是完全一样的，区别只是utf8mb4对扩展字符的支持。

>> 对于Unicode编码的字符集，强烈建议将所有的CHAR字段设置为VARCHAR字段，因为对于CHAR字段，数据库会保存最大可能的字节数。例如，对于CHAR（30），数据库可能存储90字节的数据。

>> 要查看当前使用的字符集，可以使用STATUS命令：

>> MySQL数据库一个比较“强悍”的地方是，可以细化每个对象字符集的设置

>> 排序规则（Collation）是指对指定字符集下不同字符的比较规则。

>> 两个不同的字符集不能有相同的排序规则。

每个字符集有一个默认的排序规则。

有一些常用的命名规则。如_ci结尾表示大小写不敏感（case insensitive），_cs表示大小写敏感（case sensitive），_bin表示二进制的比较（binary）。

>> 另外，排序规则不仅影响大小写的比较问题，也影响着索引

>> 可以看到，不能在a列上创建一个唯一索引，报错中提示有重复数据。索引是B+树，同样需要对字符进行比较，因此在建立唯一索引时由于排序规则对大小写不敏感而导致了错误。

>> CHAR（N）和VARCHAR（N）中的N都代表字符长度，而非字节长度。

>> 对于CHAR类型的字符串，MySQL数据库会自动对存储列的右边进行填充（Right Padded）操作，直到字符串达到指定的长度N。而在读取该列时，MySQL数据库会自动将填充的字符删除

>> LENGTH函数返回的是字节长度，而不是字符长度。

>> VARCHAR类型存储变长字段的字符类型，与CHAR类型不同的是，其存储时需要在前缀长度列表加上实际存储的字符，该字符占用1～2字节的空间。当存储的字符串长度小于255字节时，其需要1字节的空间，当大于255字节时，需要2字节的空间。所以，对于单字节的latin1来说，CHAR（10）和VARCHAR（10）最大占用的存储空间是不同的， CHAR（10）占用10个字节这是毫无疑问的，

>> 而VARCHAR（10）的最大占用空间数是11字节，因为其需要1字节来存放字符长度。

>> BINARY和VARBINARY与前面介绍的CHAR和VARCHAR类型有点类似，不同的是BINARY和VARBINARY存储的是二进制的字符串，而非字符型字符串。也就是说，BINARY和VARBINARY没有字符集的概念，对其排序和比较都是按照二进制值进行对比。

>> BINARY和VARBINARY对比CHAR和VARCHAR，第一个不同之处就是BINARY （N）和VARBINARY（N）中的N值代表的是字节数，而非

>> 字符长度；第二个不同点是， CHAR和VARCHAR在进行字符比较时，比较的只是字符本身存储的字符，忽略字符后的填充字符，而对于BINARY和VARBINARY来说，由于是按照二进制值来进行比较的，因此结果会非常不同

>> 第三个不同的是，对于BINARY字符串，其填充字符是0x00，而CHAR的填充字符为0x20。可能是因为BINARY的比较需要，0x00显然是比较的最小字符

>> 而，BLOB和TEXT在以下几个方面又不同于VARBINARY和VARCHAR：[插图]在BLOB和TEXT类型的列上创建索引时，必须制定索引前缀的长度。而VARCHAR和VARBINARY的前缀长度是可选的。

>> [插图]BLOB和TEXT类型的列不能有默认值。[插图]在排序时只使用列的前max_sort_length个字节。

>> max_sort_length默认值为1024，该参数是动态参数，任何客户端都可以在MySQL数据库运行时更改该参数的值

>> 在数据库中，最小的存储单元是页（也可以称为块）。为了有效存储列类型为BLOB或TEXT的大数据类型，一般将列的值存放在行溢出页，而数据页存储的行数据只包含BLOB或TEXT类型数据列前一部分数据。

>> 在有些存储引擎内部，比如InnoDB存储引擎，会将大VARCHAR类型字符串（如VARCHAR（65530））自动转化为TEXT或BLOB类型

>> ENUM和SET类型都是集合类型，不同的是ENUM类型最多可枚举65536个元素，而SET类型最多枚举64个元素。

第3章 查询处理

>> 在大多数编程语言中，代码按编码顺序被处理。但在SQL语言中，第一个被处理的子句总是FROM子句

>> 每个操作都会产生一张虚拟表，该虚拟表作为一个处理的输入。

>> 这些虚拟表对用户是透明的，只有最后一步生成的虚拟表才会返回给用户。如果没有在查询中指定某一子句，则将跳过相应的步骤。

>> 对虚拟表VT1应用ON筛选，只有那些符合的行才被插入虚拟表VT2中。

>> JOIN：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。

>> 如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止。

>> 第一步需要做的是对FROM子句前后的两张表进行笛卡儿积操作，也称做交叉连接（Cross Join），生成虚拟表VT1。如果FROM子句前的表中包含a行数据，FROM子句后的表中包含b行数据，那么虚拟表VT1中将包含a*b行数据

>> SELECT查询一共有3个过滤过程，分别是ON、WHERE、HAVING。ON是最先执行的过滤过程。

>> 对于大多数的编程语言而言，逻辑表达式的值只有两种：TRUE和FALSE。但是在关系数据库中起逻辑表达式作用的并非只有两种，还有一种称为三值逻辑的表达式。这是因为在数据库中对NULL值的比较与大多数编程语言不同

>> 对于在ON过滤条件下的NULL值比较，此时的比较结果为UNKNOWN，却被视为FALSE来进行处理，即两个NULL并不相同。但是在下面两种情况下认为两个NULL值的比较是相等的：

GROUP BY子句把所有NULL值分到同一组。

ORDER BY子句中把所有NULL值排列在一起

>> 因此在产生虚拟表VT2时，会增加一个额外的列来表示ON过滤条件的返回值，返回值有TRUE、FALSE、UNKNOWN，如表3-5所示。

>> 取出比较值为TRUE的记录，产生虚拟表VT2，结果如表3-6所示。

>> 添加外部行的工作就是在VT2表的基础上添加保留表中被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值，最后生成虚拟表VT3

>> 在当前应用WHERE过滤器时，有两种过滤是不被允许的：

由于数据还没有分组，因此现在还不能在WHERE过滤器中使用where_condition=MIN(col)这类对统计的过滤。

由于没有进行列的选取操作，因此在SELECT中使用列的别名也是不被允许的，如SELECT city as c FROM t WHERE c='ShangHai'是不允许出现的。

>> 如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存中存放不下就放到磁盘上）。这张内存临时表的表结构和上一步产生的虚拟表一样，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来去除重复数据。

>> 对于使用了GROUP BY的查询，再使用DISTINCT是多余的，因为已经进行分组，不会移除任何行。

>> 关系数据库是在数学的基础上发展起来的，关系对应于数学中集合的概念。数据库中常见的查询操作其实对应的是集合的某些运算：选择、投影、连接、并、交、差、除。最终的结果虽然是以一张二维表的方式呈现在用户面前，但是从数据库内部来看是一系列的集合操作。因此，对于表中的记录，用户需要以集合的思想来理解。对于customers和orders表，更准确的描述应如图3-2所示。

>> 由此可见，即使采用的是InnoDB存储引擎表，对于没有使用ORDER BY子句的选择查询，其结果永远不会是按照主键顺序进行排列的。因为

>> 没有ORDER BY子句的查询只代表从集合中查询数据，而集合是没有顺序概念的。

>> 因此要牢记，不要为表中的行假定任何特定的顺序。就是说，在实际使用环境中，如果确实需要有序输出行记录，则必须使用ORDER BY子句

>> 在ORDER BY子句中，NULL值被认为是相同的值，会将其排序在一起。在MySQL数据库中，NULL值在升序过程中总是首先被选出，即NULL值在ORDER BY子句中被视为最小值。

>> 如果只是选取前5条记录，则非常轻松和容易；但是对100万条记录，选取从第80万行记录开始的5条记录，则还需要扫描记录到这个位置

>> 上一节介绍了逻辑查询处理，并且描述了执行查询应该得到什么样的结果。但是数据库也许并不会完全按照逻辑查询处理的方式来进行查询。图1-1显示了在MySQL数据库层有Parser和Optimizer两个组件。Parser的工作就是分析SQL语句，而Optimizer的工作就是对这个SQL语句进行优化，选择一条最优的路径来选取数据，但是必须保证物理查询处理的最终结果和逻辑查询处理是相等的。

第4章 子查询

>> 子查询可以按两种方式进行分类。若按照期望值的数量，可以将子查询分为标量子查询和多值子查询；若按查询对外部查询的依赖可分为独立子查询（self-contained subquery）和相关子查询（correlated subquery）

>> MySQL优化器对于IN语句的优化是“LAZY”的。对于IN子句，如果不是显式的列表定义，如IN ('a','b','c')，那么IN子句都会被转换为EXISTS的相关子查询

>> 如果子查询和外部查询分别返回M和N行，那么该子查询被扫描为O（N+M*N）而不是O（M+N）。

>> 用户通过EXPLAIN EXTENDED命令可以更为明确地得到优化器的执行方式

>> 有意思的是，翻阅官方的MySQL手册会发现，在子查询章节中有相关子查询的介绍，却没有独立子查询的介绍。这是因为在大多数情况下，MySQL数据库都将独立子查询转换为相关子查询。

>> 注意到慢的原因是独立子查询被转换成相关子查询，而这个相关子查询需要进行多次的分组操作。可以采取另一个方法，再嵌套一层子查询，避免多次的分组操作，

>> 相关子查询（Dependent Subquery或Correlated Subquery）是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次计算

>> 。但是在优化器内部，这是一个动态的过程，随情况的变化会有所不同，通过不止一种优化方式来处理相关子查询。

>> 这里再次提醒开发人员，对子查询的编写需要非常小心，尽可能地使用EXPLAIN来确认子查询的执行计划，并确认是否可以对其进行进一步优化。在测试机上执行一句SQL需要1秒的时间看似很短，但这通常是数据量较小的缘故；如果在大数据量的生产环境中，这可能会带来灾难性的后果。

>> EXIST

>> S是一个非常强大的谓词，它允许数据库高效地检查指定查询是否产生某些行。通常EXISTS的输入是一个子查询，并关联到外部查询，但这不是必须的

>> 。根据子查询是否返回行，该谓词返回TRUE或FALSE。与其他谓词和逻辑表达式不同的是，无论输入子查询是否返回行，EXISTS都不会返回UNKNOWN。如果子查询的过滤器为某行返回UNKNOWN，则表示该行不返回，因此，这个UNKNOWN被认为是FALSE。

>> 尽管通常不建议在SQL语句中使用*，因为可能会引起一些问题的产生，但是在EXIST子查询中*可以放心地使用。EXISTS只关心行是否存在，而不会去取各列的值。

>> EXISTS与IN的一个小区别体现在对三值逻辑的判断上。EXISTS总是返回TRUE或FALSE，而对于IN，除了TRUE、FALSE值外，还有可能对NULL值返回UNKNOWN。但是在过滤器中，UNKNOWN的处理方式与FALSE相同，因此使用IN与使用EXISTS一样， SQL优化器会选择相同的执行计划。

>> 但是输入列表中包含NULL值时，NOT EXISTS和NOT IN之间的差异就表现得非常明显了。

>> 对于包含NULL值的NOT IN来说，其总是返回FALSE和UNKNOWN

>> ，而对于NOT EXISTS，其总是返回TRUE和FALSE。这就是NOT EXISTS和NOT IN的最大区别。

>> 派生表又被称为表子查询，与其他表一样出现在FROM的子句中，但是是从子查询派生出的虚拟表中产生的

>> 目前派生表在使用上有以下使用规则：

列的名称必须是唯一的。

在某些情况下不支持LIMIT。

>> 派生表是完全的虚拟表，并没有也不可能被物理地具体化，因此优化器不清楚派生表的信息，这对于涉及查看派生表的EXPLAIN执行计划来说，速度可能非常慢，

>> 由于目前Oracle和MySQL都将SEMI JOIN转换为了EXISTS语句，因此在执行效率上显得非常低。从理论上来说，SEMI JOIN应该只需要关心外部表中与子查询匹配的部分即可

>> 。这就是MariaDB要对SEMI JOIN进行的优化，在MariaDB中子查询变得实际可用得多，效率也得到了极大的提升

>> Table Pullout的作用就是根据唯一索引将子查询重写为JOIN语句

>> 预热是指所要读取的表中的数据都已经在InnoDB存储引擎的缓冲池中，这时不涉及磁盘的读取。而无预热指的是数据库刚启动，缓冲池中没有数据，需要读取磁盘上的数据到缓冲池。

>> Duplicate Weedout优化是指外部查询条件的列是唯一的， MariaDB优化器会先将子查询查出的结果进行去重，这个步骤被称为Duplicate Weedout或者Duplicate Elimination。

>> 如果子查询是独立子查询，则优化器可以选择将独立子查询产生的结果填充到单独一张物化临时表（materialized temporary table）中

第5章 联接与集合操作

>> 联接查询是一种常见的数据库操作，即在两张表（或更多表）中进行行匹配的操作。一般称之为水平操作，这是因为对几张表进行联接操作所产生的结果集可以包含这几张表中所有的列。对应于联接的水平操作，一般将集合操作视为垂直操作。

MySQL数据库支持如下的联接查询：

CROSS JOIN（交叉联接）

INNER JOIN（内联接）

OUTER JOIN（外联接）

其他

>> 在进行联接操作时，请牢记第3章描述的逻辑查询处理阶段，尤其是关于联接所涉及的阶段。

>> 每个联接都只发生在两个表之间，即使FROM子句中包含多个表也是如此

>> 。每次联接操作也只进行逻辑操作的前三个步骤，每次产生一个虚拟表，这个虚拟表再依次与FROM子句的下一个表进行联接

>> 需要注意的是，不同联接类型执行的步骤不同。对于CROSS JOIN，只应用第一个阶段的笛卡儿积。INNER JOIN应用第一和第二个步骤，OUTER JOIN应用所有的前三个步骤。

>> 对于表5-1左侧的SQL联接查询语句，其由ANSI SQL 89标准引入，与新语法的区别是FROM子句中的表名之间用逗号分隔，没有JOIN关键字，也没有ON子句，其语法格式如下：

>> ANSI SQL 89只支持CROSS JOIN和INNTER JOIN，不支持OUTER JOIN。新语法是由ANSI SQL 92引入的，与旧语法的区别是引入了JOIN关键字和ON过滤子句，并去掉了表之间的逗号，其语法格式如下：

>> CROSS JOIN对两个表执行笛卡儿积，返回两个表中所有列的组合。若左表有m行数据，右表有n行数据，则CROSS JOIN将返回m*n行的表。

>> 对于交叉联接，笔者更喜欢使用ANSI SQL 89语法。这样代码会更短，语法更加易读。不必担心两者的性能，因为正如前面所说的，优化器将为两者生成相同的执行计划。

>> CROSS JOIN的一个用处是快速生成重复测试数

>> 据，因为通过它可以很快地构造m*n*o行的数据。

>> 虽然对两个N行表进行笛卡儿积会产生N2行的数据。但是如果是对一行表与N行表进行CROSS JOIN，笛卡尔儿积返回的还是N行数据

>> 如果使用的是ANSI 92语法，则选择在哪个子句中指定过滤条件，用户具有更多的灵活性。因为前面说了，从逻辑上讲，在哪里指定过滤条件都是一样的，通常不会有性能上的差异。唯一的准则就是可读性强。通过一种让DBA、开发人员感觉更自然的方式进行代码编写。例如，在表之间匹配记录的过滤器放在ON子句中，而只从一个表中过滤数据的条件放在WHERE子句中

>> 对于CROSS JOIN，笔者喜欢使用ANSI 89语法，而对于INNER JOIN正好相反，更倾向于使用ANSI 92语法。如果忘记指定联接条件，则使用ANSI 89语法可能有些危险，因为可能会得到很大的笛卡儿积返回集

>> 特别需要注意的是，在MySQL数据库中，如果INNER JOIN后不跟ON子句，也是可以通过语法解析器的，这时INNER JOIN等于CROSS JOIN，即产生笛卡儿积

>> 如果ON子句中的列具有相同的名称，可以使用USING子句来进行简化，得到的结果和上述两语法的语句结果是一样的：

>> 目前MySQL数据库不支持FULL OUTER JOIN。

>> OUTER JOIN只在ANSI SQL 92中得到支持，在其他一些数据库中可以使用（+）=、*=来表示LEFT JOIN，用=（+）、=*来扩展ANSI SQL 89语法使其支持OUTER JOIN。

>> 但是对MySQL数据库来说，只有一种OUTER JOIN的联接语法。

>> 需要注意的是，INNER JOIN中的过滤条件都可以写在ON子句中，而OUTER JOIN的过滤条件不可以这样处理，因为可能会得到不正确的结果

>> 与INNER JOIN不同的是，对于OUTER JOIN，必须制定ON子句，否则MySQL数据库会抛出异常，

>> 前面介绍的都是EQUAL JOIN（等值联接），即联接条件是基于“等于”运算符的联接操作。NONEQUI JOIN的联接条件包含“等于”运算符之外的运算符。

>> 对于INNER JOIN的多表联接查询，可以随意安排表的顺序，而不会影响查询的结果。这是因为优化器会自动根据成本评估出访问表的顺序。在该查询的执行计划中，可能会发现优化器访问表的顺序不同于在查询中指定的顺序。

>> 如果认为不按优化器所选择的顺序联接表会更加高效，可以通过前面介绍的STRAIGHT_JOIN来强制联接处理的顺序

>> 联接算法是MySQL数据库用于处理联接的物理策略。目前MySQL数据库仅支持Nested-Loops Join算法。而MySQL的分支版本MariaDB除了支持Nested-Loops Join算法外，还支持Classic Hash Join算法。

>> Simple Nested-Loops Join从第一张表中每次读取一条记录，然后将记录与嵌套表中的记录进行比较

>> 对于联接的列含有索引的情况，外部表的每条记录不再需要扫描整张内部表，只需要扫描内部表上的索引即可得到联接的判断结果。

>> 根据前面描述的Simple Nested-Loops Join算法，优化器在一般情况下总是选择将联接列含有索引的表作为内部表。如果两张表R和S在联接的列上都有索引，并且索引的高度相同，那么优化器会选择将记录数最少的表作为外部表，这是因为内部表的扫描次数总是索引的高度，与记录的数量无关。

>> Block Nested-Loops Join算法就是针对没有索引的联接情况设计的，其使用Join Buffer（联接缓冲）来减少内部循环读取表的次数。

>> Block Nested-Loops Join算法先把对Outer Loop表（外部表）每次读取的10行记录（准确地说是10行需要进行联接的列）放入Join Buffer中，然后在Inner Loop表（内部表）中直接匹配这10行数据。因此，对Inner Loop表的扫描减少了1/10

>> 每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer。

Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放。

Join Buffer只存储需要进行查询操作的相关列数据，而不是整行的记录。

>> Block Nested-Loops Join算法不支持OUTER JOIN

>> 可以看到这次执行计划的Extra列中并没有Using join buffer的提示，这也就意味着此时优化器没有使用Block Nested-Loops Join算法。从MySQL 5.6及MariaDB 5.3开始，Join Buffer的使用得到了进一步扩展，在OUTER JOIN中使用Join Buffer受到支持

>>  5.6（MariaDB 5.3）开始支持Batched Key Access Join算法（简称BKA），该算法的思想为结合索引和group这两种方法（Simple Nested-

>> Loops Join和Block Nested-Loops Join只能使用一种）来提高search-for-match的操作，以此加快联接的执行效率

>> 因为Batched Key Access Join算法的本质是通过Multi-Range Read接口将非主键索引对于记录的访问，转化为根据ROWID排序的较为有序的记录获取，所以要想通过Batched Key Access Join算法来提高性能，不但需要确保联接的列参与match的操作，还要有对非主键列的search操作。

>> Batched Key Access Join算法从本质上来说还是Simple Nested-Loops Join算法，其发生的条件为内部表上有索引，并且该索引为非主键的，并且联接需要访问内部表主键上的索引。

>> Classic Hash Join算法同样使用Join Buffer，先将外部表中数据放入Join Buffer中，然后根据键值产生一张散列表，这是第一个阶段，称为build阶段。随后读取内部表中的一条记录，对其应用散列函数，将其和散列表中的数据进行比较，这是第二个阶段，称为probe阶段。

>> Hash Join只能应用于等值的联接操作中，因为已通过散列函数生成新的联接值，不能将Hash Join用于非等值的联接操作中。

>> 倘若Join Buffer能够完全存放下外部表的数据，那么Classic Hash Join算法只需要扫描一次内部表。反之，Classic Hash Join需要扫描多次内部表

>> 正如前面所说，当Join Buffer不能存放下所有外部表中的数据时，Classic Hash Join需要扫描内部表多次。对于这种情况，其他数据库中使用的是Grace Hash Join算法，对内部表和外部表都只需扫描一次，有兴趣的读者可以查找相关资料。MariaDB有计划支持Grace Hash Join算法，相信不久的将来也能在MySQL数据库中看到Grace Hash Join算法。

>> MySQL数据库支持两种集合操作：UNION ALL和UNION DISTINCT

>> 集合操作的两个输入必须拥有相同的列数，若数据类型不同，MySQL数据库会自动将进行隐式转化。同时，结果列的名称由第一个输入决定。

>> UNION DISTINCT组合两个输入，并应用DISTINCT过滤重复项。一般省略DISTINCT关键字，直接用UNION

>> 由于向临时表添加了唯一索引，插入的速度显然会因此而受到影响。如果确认进行UNION操作的两个集合中没有重复的选项，最有效的办法应该是使用UNION ALL。

UNION ALL组合两个输入中所有项的结果集，并包含重复的选项

>> MySQL数据库并不原生支持EXCEPT的语法，不过我们仍然可以通过一些手段来得到EXCEPT的结果。EXCEPT集合操作允许用户找出位于第一个输入中但不位于第二个输入中的行数据。同UNION一样，EXCEPT可分为EXCEPT DISTINCT和EXCEPT ALL。

第6章 聚合和旋转操作

>> MySQL数据库支持聚合（aggregate）操作，按照分组对同一组内的数据聚合进行统计操作。

>> GROUP_CONCAT将分组后的非NULL数据

>> 通过连接符进行拼接，对NULL数据返回NULL值。

>> MySQL数据库仅支持流聚合，而其他的数据库可能会支持散列聚合。流聚合依赖于获得的存储在GROUP BY列中的数据。如果一个SQL查询中包含的GROUP BY语句多于一行，流聚合会先根据GROUP BY对行进行排序。

>> ing是一项可以把行旋转为列的技术。在执行Pivoting的过程中可能会使用到聚合。Pivoting技术应用得非常广泛

>> 因此，在频繁更改架构的情况下，可以在一个表中存储所有的数据，每行存储一个属性的值，多用VARCHAR来存储，因为其可容纳各种类型的数据

>> 在对通过开放架构设计的表进行添加、修改或删除表和列时，只需要通过INSERT、UPDATE、DELETE操作来完成逻辑架构的更改即可。当然使用这种方法可能导致关系数据库的其他特性无法使用，如完整性约束、SQL优化等，同时查询数据变得不如使用之前的SQL语句来得直接和直观。所以，对于利用开放架构设计的表，一般使用Pivoting技术来查询数据。

>> 这种旋转方式是非常高效的，因为它只对表进行一次扫描。另外，这是一种静态的Pivoting，用户必须事先知道一共有多少个属性，然而对于一般的开放架构表，用户都会定义一个最大的属性个数，这样可以比较容易地进行Pivoting。

第8章 事务编程

>> 对于Oracle数据库来说，其默认的事务隔离级别为READ COMMITTED，不满足I的要求，即隔离性的要求

>> 需要注意的是，持久性只能从事务本身的角度来保证结果的永久性，如事务提交后，所有的变化都是永久的，即使当数据库由于崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。但如果不是数据库本身发生故障，而是一些外部的原因，如RAID卡损坏、自然灾害等导致数据库发生问题，那么所有提交的数据可能会丢失。因此持久性保证的是事务系统的高可靠性（high reliability），而不是高可用性（high availability）。对于高可用性的实现，事务本身并不能保证，需要一些系统共同配合来完成。

>> 带有保存点的扁平事务，除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点（savepoint）用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。

>> 链事务可视为保存点模式的一个变种。带有保存点的扁平事务，当发生系统崩溃时，所有的保存点都将消失，因为其保存点是易失的（volatile），而非持久的（persistent）。这意味着当恢复保存点时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。

>> 链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的。

>> 链事务与带有保存点的扁平事务不同的是，带有保存点的扁平事务能回滚到任意正确的保存点。而链事务中的回滚仅限于当前事务，即只能恢复到最近一个保存点。对于锁的处理，两者也不相同。链事务在COMMIT后即释放了当前事务所持有的锁，而带有保存点的扁平事务不影响迄今为止所持有的锁。

>> MySQL命令行的默认设置下，事务都是自动提交（auto commit）的，即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一个事务须使用命令BEGIN和START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，以禁用当前会话的自动提交。

>> 注意 Microsoft SQL Server的数据库管理员或开发人员往往忽视对于DDL语句的隐式提交操作，因为在Microsoft SQL Server数据库中，即使是DDL也是可以回滚的，这和InnoDB存储引擎、Oracle这些数据库完全不同。

>> 令人惊讶的是，大部分数据库系统都没有提供真正的隔离性，最初或许是因为系统实现者并没有真正理解这些问题，如今这些问题已经弄清楚了，但是数据库实现者在正确性和性能之间做了妥协。

>> 虽然ISO和ANSI SQL标准制定了四种事务隔离级别的标准，但是很少有数据库厂商遵循这些标准。比如Oracle数据库就不支持READ UNCOMMITTED和REPEATABLE READ的事务隔离级别。

>> InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，因此避免了幻读的产生。这与其他数据库系统（如Microsoft SQL Server数据库）是不同的。所以说，InnoDB存储引擎在默认的REPEATABLE READ事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE隔离级别。

>> 在SERIALIZABLE的事务隔离级别，InnoDB存储引擎会对每个SELECT语句后自动加上LOCK IN SHARE MODE，即给每个读取操作加一个共享锁。在这个事务隔离级别下，读占用锁了，因此对于一致性的非锁定读不再予以支持。由于InnoDB存储引擎在REPEATABLE READ隔离级别下就可以达到3 °的隔离，因此一般不在本地事务中使用SERIALIZABLE隔离级别，SERIALIZABLE事务隔

>> 离级别主要用于InnoDB存储引擎的分布式事务。

>> XA事务允许不同数据库之间的分布式事务，如一台服务器是MySQL数据库的，另一台是Oracle数据库的，可能还有一台服务器是SQL Server数据库的，只要参与到全局事务中的每个节点都支持XA事务即可

>> 分布式事务使用两段式提交（two-phase commit）的方式。

>> 在单个节点上运行分布式事务没有太大的实际意义，但是要在MySQL数据库的命令下演示多个节点参与的分布式事务也是行不通的。通常来说，都是通过编程语言来完成分布式事务的操作的。当前Java的JTA（Java Transaction API）可以很好地支持MySQL的分布式事务，需要使用分布式事务应该认真参考其API。

>> 显然，第三种方法要快得多！这是因为每一次提交都要写一次重做日志，所以存储过程load1和load2实际写了10000次重做日志文件，而对于存储过程load3来说，实际只写了1次重做日志。

>> 大多数程序员会使用第一种或者第二种方法，有人可能不知道InnoDB存储引擎自动提交的情况，另外有些人可能持有以下两种观点：首先，在曾经使用过的数据库中，对于事务的要求总是尽快地进行释放，不能有长时间的事务；其次，可能担心存在Oracle数据库中由于没有足够UNDO空间产生的Snapshot Too Old的经典问题。MySQL InnoDB存储引擎都没有上述两个问题，因此程序员不论从何种角度出发，都不应该在一个循环中反复地进行提交操作，不论是显式的提交还是隐式的提交。

>> 喜欢使用自动回滚的人大多是以前使用Microsoft SQL Server数据库的开发人员。在Microsoft SQL Server数据库中，可以使用SET XABORT ON来自动回滚一个事务，因为Microsoft SQL Server数据库不仅会自动回滚当前的事务，还会抛出异常，开发人员可以捕获到这个异常。

>> 对于长事务的问题，有时可以通过将其转化为小批量（mini batch）的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次的已完成的事务继续进行。

第9章 索引

>> 顺序读取（sequntial read）是指顺序地读取磁盘上的页。随机读取（random read）是指访问的页不是连续的，需要磁盘的磁头不断移动。这里需要注意的是，这里的“顺序”指的是逻辑上的顺序，在物理上不可能保证所有的数据都是顺序的。

>> 在B+树索引中，B+树索引只能找到某条记录所在的页，需再根据二分查找法来进一步找到记录所在页的具体位置。

>> 在介绍B+树前，先要了解一下二叉查找树。B+树是通过二叉查找树，再由平衡二叉树、B树演化而来。

>> 若想最大性能地构造一个二叉查找树，需要这棵二叉查找树是平衡的，因此引入了新的定义—平衡二叉树，又称为AVL树。

>> 平衡二叉树的定义如下：首先符合二叉查找树的定义，其次必须满足任何节点的两棵子树的高度最大差为1

>> 平衡二叉树在查找方面的性能是比较高的，但不是最高的，只是接近最高性能。要达到最好的性能需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此一般只需建立一棵平衡二叉树即可。

平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价非常大，通常需要1次或多次左旋或右旋来得到经过插入或更新操作后二叉树的平衡性

>> 相信在任何一本数据结构书中都能找到B+树的定义，其定义十分复杂，这里列出B+树的定义只会让读者感到更加困惑

>> B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行链接

>> B+树的插入要求必须保证插入后叶子节点中的记录依然顺序排列，同时需要考虑插入到B+树的3种情况，每种情况都可能导致不同的插入算法，如表9-1所示。

>> 不管怎么变化，B+树总会保持平衡，但是为了保持平衡需要在插入新的键值后做大量的拆分页（split）操作，而B+树主要用于磁盘，因此页的拆分意味着磁盘的操作，应该在可能的情况下减少页的拆分。为此，B+树提供了旋转（rotation）的功能。

>> B+树索引的本质就是B+树在数据库中的实现，而B+树索引在数据库中的一个特点就是高扇出性。例如在InnoDB存储引擎中，每个页的大小为16KB。

>> 因此在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

>> 在MySQL数据库中，索引是在存储引擎层实现的，这意味着每个引擎的B+树索引的实现方式可能是不同的，取决于存储引擎实现的本身。

B+树索引可以分为聚集索引与辅助索引（非聚集索引），但是这两者本身都与之前讨论的B+树的数据结构一样，区别仅在于所存放数据的内容。

>> 聚集索引是根据主键创建的一棵B+树，聚集索引的叶子节点存放了表中的所有记录。辅助索引是根据索引键创建的一棵B+树，与聚集索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主键。

>> 按性别进行查询时，可取值的范围一般只有“M”和“F”，因此上述SQL语句得到的结果可能是该表50%的数据（我们假设男女比例1：1），这时添加B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即是高选择性的，那么此时使用B+树索引是最适合的

>> 怎样查看索引是否是高选择性的呢？可以通过SHOW INDEX语句中的Cardinality列来观察。Cardinality值非常关键，表示索引中唯一只记录数量的预估值。这里需要注意的是， Cardinality是一个预估值，而不是一个准确值，用户也不可能得到一个准确的值。在实际应用中，Cardinality/n_rows_in_table应尽可能接近1，如果非常小，那么需要考虑是否还要建这个索引。

>> 在OLAP中添加索引依据的是宏观的信息，而不是微观信息，这是因为最终要得到的结果是提供给决策者的。例如不需要在OLAP中对姓名字段进行索引，因为很少会对单个用户进行查询。但是对于OLAP中的复杂查询，需要涉及多张表之间的联接操作，这时索引的添加是有意义的

>> 之前的MySQL版本不支持ICP，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。在支持ICP后，MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，即将WHERE的部分过滤操作放在了存储引擎层。在某些查询中，ICP会大大减少上层SQL层对于记录的索取（fetch），从而提高数据库的整体性能。

>> ICP优化支持range、ref、eq_ref和ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。当优化器选择ICP优化时，可在执行计划的Extra列看到Using index condition提示。

>> 内存数据库中，一般使用T树（T-Tree）作为其索引的数据结构

>> T树的好处是节点不存放数据，只存放指针，这样能减少对内存的使用，这对内存数据库来说显得尤为重要。同时T树也是一棵平衡二叉树，以此保证查找的性能。T树中的T指的是T树中节点的形状。

>> 当前MySQL数据库中，Memory存储引擎支持哈希索引。InnoDB存储引擎支持自适应哈希索引，用户仅能开启该特性，不能对其进行人工干预

第10章 分区

>> 分区功能并不是在存储引擎层完成的，因此不只有InnoDB存储引擎支持分区，常见的存储引擎MyISAM、NDB等都支持分区

>> 分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

>> MySQL数据库支持的分区类型为水平分区，并不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放数据又存放索引。全局分区是指，数据存放各个分区中，但是所有数据的索引放在一个对象中。

>> 目前，MySQL数据库暂时不支持全局分区。

>> 不论创建何种类型的分区，如果表中存在主键

>> 或唯一索引时，分区列必须是唯一索引的一个组成部分，

>> 唯一索引可以是NULL值，并且只要求分区列是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列

>> 查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成，而是由建立分区时的各个分区ibd文件组成，比如下面的t#P#p0.ibd、t#P#p1.ibd。

>> 通过EXPLAIN PARTITION命令我们可以发现，在上述语句中，SQL优化器只需要搜索p2008这个分区，而不会搜索所有的分区—称为Partition Pruning（分区修剪），故查询的速度得到了大幅度提升。

>> 产生这个问题的主要原因是，对RANGE分区的查询，优化器只能对YEAR（）、TO_DAYS（）、TO_SECONDS（）和UNIX_TIMESTAMP（）这类函数进行优化选择

>> 在执行INSERT操作插入多个行数据的过程中如果遇到分区未定义的值， MyISAM和InnoDB存储引擎的处理会完全不同。MyISAM引擎会将之前的行数据都插入，但之后的数据不会被插入。而InnoDB存储引擎将其视为一个事务，没有任何数据被插入

>> MySQL数据库允许对NULL值进行分区，但是处理方法可能与其他数据库完全不同。MySQL数据库的分区总是把NULL值视为小于任何的一个非NULL值，这和MySQL数据库中处理NULL值的ORDER BY操作是一样的。

>> 因此对于不同的分区类型，MySQL数据库对NULL值的处理也各不相同。

>> 如果一百万行和一千万行的数据本身构成的B+树的层次是一样的，可能都是两层，那么上述主键分区的索引并不会带来性能的提高。假设一千万行数据的B+树的高度是3，一百万行数据的B+树的高度是2，这样上述主键分区的索引可以避免一次IO，从而提高查询的效率。

>> MySQL 5.6开始支持ALTER TABLE ... EXCHANGE PARTITION语法。该语句允许分区或子分区中的数据与另一个非分区的表中数据进行交换。如果非分区表的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表的数据为空，则相当于将外部表中的数据导入分区中。

>> 交换的表须与分区表有相同的表结构，但是表不能含有分区。