MySQL技术内幕:SQL编程 笔记

MySQL技术内幕:SQL编程

第一章 SQL编程
第二章 数据类型
2.1 类型属性
2.1.1 UNSIGNED
UNSIGNED属性就是将数字类型无符号化。INT类型取整范围-2147483648~-2147483647,32位,一个符号位。如果是无符号INT,取值就是0~4294967295。

MySQL中,对于UNSIGNED数的操作,其返回值都是UNSIGNED的。尽量不用UNSIGNED,如果超过INT的表示范围,可以使用BIGINT(64位,包含一符号位)。

2.1.2 ZEROFILL
在创建数字字段时,后面会紧跟一个数字,比如int(5),如果没有ZEROFILL时,这个括号中的数字没有意义。对列添加了ZEROFILL属性后,表示如果长度不够,将使用0进行填充(仅对显示有影响)。比如123,显示为00123。(注意,通过工具为字段设置ZEROFILL后,同时添加了UNSIGNED,说明这个填充行为针对无符号数)

2.2 SQL_MODE设置
SQL_MODE默认为空。生成环境中建议是严格模式。可以避免NULL插入NOT NULL的字段中,非法日期等。SQL_MODE可以在my.cnf和客户端工具中进行。

mysql> select @@global.sql_mode \G
*************************** 1. row ***************************
@@global.sql_mode: NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql> select @@session.sql_mode \G
*************************** 1. row ***************************
@@session.sql_mode: NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

2.3 日期和时间类型
日期时间应该使用YYYY-MM-DD HH:MM:SS格式输入。MySQL5.5之前(包含),MySQL不能输入微秒级别(被截断)。MySQL提供了MICROSECOND来提供日期中的微秒值:

SELECT MICROSECOND('2015-09-15 00:01:10.123456')\G;

MySQL 5.6.4开始,MySQL增加了对秒的小数部分(fractional second)的支持,语法:type_name(fsp),type_name的类型可以是TIME、DATETIME和TIMESTAMP,fsp表示支持的小数部分的精度,最大为6,表示微秒,默认为0,表示没有小数部分。

TIMESTAMP和DATETIME显示的结果是一样的,都是固定的“YYYY-MM-DD HH:MM:SS”的形式。不同的是,TIMESTAMP占用4个字节,显示范围为“1970-01-01 00:00:00到2038-01-19 03:14:07”UTC。实际存储的内容为1970-01-01 00:00:00到当前时间的毫秒数。

区别:建表时,列为TIMESTAMP的日期类型可以设置一个默认值,而DATETIME不行;更新表时,可以设置TIMESTAMP类型的列自动更新时间为当前时间。

<?php
date_default_timezone_set("UTC"); 	 	//转换为UTC
echo PHP_INT_SIZE."\n";			 	//4,4个字节
echo PHP_INT_MAX."\n";			 	//2147483647,最大整数,31位
echo date("Y-m-d H:i:s",2147483647)."\n";	//2038-01-19 03:14:07,最大整数变成时间
echo strtotime("1970-01-01 00:00:00")."\n";	//0,时间戳开始为0
echo strtotime("2038-01-19 03:14:07");		//2147483647,最大时间,为最大整数

如果对来自MySQL的时间戳进行取整操作,不会产生截断问题,因为整数的大小跟PHP中的一致。

日期和时间相关函数
NOW CURRENT_TIMESTAMP和SYSDATE

DATE_ADD 和 DATE_SUB

DATE_FORMAT

2.4 关于日期的经典SQL编程问题
2.5 数字类型
2.5.1 整型
INT 4字节
SMALLINT 2字节
TINYINT 1字节
MEDIUMINT 3字节
BIGINT 8字节

ZEROFILL可以格式化显示整型,一旦启用ZEROFILL属性,MySQL数据库为列自动添加UNSIGNED属性。

2.5.2 浮点型
2.5.3 高精度类型
DECIMAL和NUMERIC类型在MySQL中被视为相同的类型,用于保存必须为确切精度的值。在标准SQL中,语法DECIMAL(M)等价于DECIMAL(M,0)。在MySQL 5.5中,M的默认值为10。

DECIMAL和NUMERIC的最大位数是65,但具体的DECIMAL和NUMERIC列的实际范围受具体列的精度和标度约束。

2.5.4 位类型
位类型,即BIT数据类型可用来保存字段的值。BIT(M)类型表示运行存储M位数值,M范围为1到64,占用的空间为(M+7)/8字节。如果为BIT(M)列分配的值的长度小于M位,在值的左边用0填充。

要指定位值,可以使用b’value’:

INSERT INTO t SELECT b'1000';

直接使用SELECT进程查看会出现空白情况,需要转化:

SELECT HEX(a) FROM t;

2.6 关于数字的经典SQL编程问题
2.7 字符类型
2.7.1 字符集
通过命令SHOW CHARSET可以查看MySQL数据库支持的字符集。MySQL 5.5版本支持39个字符集。

MySQL 5.5数据库共支持ucs2、utf8(utf8mb3)、utf8mb4、utf16以及utf32五种Unicode编码,而5.5之前的版本只支持ucs2和utf8两种Unicode字符集。utf8目前被视为utf8mb3,即最大占用3个字节空间,而utf8mb4可以视为utf8mb3的扩展。对BMP字符的存储,utf8mb3和utf8mb4两者是完全一样的,区别只是utf8mb4对扩展字符的支持。

对于Unicode编码的字符集,建议将CHAR字段设置为VARCHAR字段,因为对于CHAR字段,数据库会报错最大可能的字节数(采用最大可能字节累加)。

MySQL5.5版本开始移除了参数default_character_set,取而代之的是参数character_set_server。

character_set_server=utf8

查看使用的字符集,可以使用STATUS命令。

命令SET NAMES可以用来更改当前会话连接的字符集、当前会话的客户端的字符集,以及当前会话返回结果集的字符集。

MySQL数据库可以细化每个对象字符集的设置。

2.7.2 排序规则
两个不同的字符集不能有相同的排序规则。每个字符集有一个默认的排序规则。有一些常用的命名规则。如果_ci结尾表示大小写不敏感(case insensitive),_cs表示大小写敏感(case sensitive),_bin表示二进制的比较(binary)。在MySQL中,可以通过命令SHOW COLLATION来查看支持的各种排序规则。字符是否大小写敏感,影响到字符比较(建索引时,返回结果,表字段)。

2.7.3 CHAR和VARCHAR
CHAR类型,N的范围为0~255,对于VARCHAR类型,N的范围为0~65535。CHAR(N)和VARCHAR(N)中的N都代表长度,而非字节长度。(这个根int后制定的数字完全两码事)

对于CHAR类型的字符串,MySQL数据库会自动对存储列的右边进行填充操作(取出时做相反操作)

VARCHAR类型存储变长字段的字符类型,与CHAR类型不同的是,其存储时需要在前缀列表加上实际存储的字符数,该字符占用1~2字节的空间。

2.7.4 BINARY和VARBINARY
2.7.5 BLOB和TEXT
BLOB(Binary Large Object)用来存储二进制大数据类型。
TINYBLOB BLOB MEDIUMBLOB LONGBLOB

TEXT类型:
TINYTEXT TEXT MEDIUMTEXT LONGTEXT

在大多数情况下,可以将BLOB类型的列视为足够大的VARBINARY类型的列。同样,也可以将TEXT类型的列视为足够大的VARCHAR类型的列。区别:
BLOB和TEXT类型的列上创建索引时,必须制定索引前缀的长度。
BLOB和TEXT类型的列不能有默认值。
在排序时只使用列的钱max_sort_length个字节(默认1024)。

在数据库中,最小的存储单元是页(也可以称为块)。为了有效存储列类型为BLOB或TEXT的大数据类型,一般将列的值存放在行溢出页,而数据页存储的行数据只包含BLOB或TEXT类型数据列前一部分数据(大体上,就是页存放尽可能多的行,对BLOB或TEXT放入到所谓的溢出页,当然,中间需要有指针链接起来)

2.7.6 ENUM和SET类型
ENUM和SET类型,不同的是ENUM类型最多可枚举65536个元素,而SET类型最多枚举64个元素。由于MySQL不支持传统的CHECK约束,因此通过ENUM和SET类型并结合SQL_MODE可以解决一部分问题。

查询处理
3.1 逻辑查询处理
3.1.1 执行笛卡尔积
3.1.2 应用ON过滤器
下面两种情况下认为两个NUL值的比较是相等的:
GROUP BY子句把所有NULL值分到同一组
ORDER BY子句中把所有NULL值排列在一起
3.1.3 添加外部行
3.1.4 应用WHERE过滤器
3.1.5 分组
3.1.6 应用ROLLUP或CUBE
3.1.7 应用HAVING过滤器
3.1.8 处理SELECT列表
3.1.9 应用DISTINCT子句
3.1.10应用ORDER BY子句
3.1.11LIMIT子句
3.2 物理查询处理

子查询
4.1 子查询概述
4.2 独立子查询
4.3 相关子查询
4.4 EXISTS谓词
4.5 派生表
4.6 子查询可以解决的经典问题
4.7 MariaDB对SEMI JOIN的优化

第五章
联接与集合操作
5.1 联接查询
MySQL数据库支持如下的联接查询:
CROSS JOIN 交叉联接
INNER JOIN 内联接
OUTER JOIN 外链接
其它

5.1.1 新旧查询语法

SELECT * FROM A,B WHERE A.ID = B.ID
SELECT * FROM A INNER JOIN B ON A.ID = B.ID

5.1.2 CROSS JOIN
CROSS JOIN对两个表执行笛卡尔积,返回两个表中所有列的组合。
5.1.3 INNER JOIN
INNERR关键字可省略。INNER JOIN中的WHERE的过滤条件可以写在ON子句中。

注:在MySQL中,如果INNER JOIN后不跟ON子句(不出错),等同于CROSS JOIN,即产生笛卡尔积。

如果ON子句中的列具有相同的名称,可以使用USING子句来进行简化:

SELECT * FROM A INNER JOIN B ON A.ID = B.ID
//等同
SELECT * FROM A INNER JOIN B USING(ID)

就是ON A.ID = B.ID可以用USING(ID)来代替。

5.1.4 OUTER JOIN
MySQL数据库支持LEFT OUTER JOIN 和 RIGHT OUTER JOIN(OUTER可省略)。
通过OUTER JOIN和IS NULL,可以返回没有用户订单的客户:

SELECT c.customer_id FROM customers AS c LEFT JOIN order AS o 
ON c.customer_id = o.customer_id WHERE o.order_id IS NULL

注:INNER JOIN中的过滤条件都可以写在ON子句中,而OUTER JOIN的过滤条件不可以。OUTER JOIN必须给出ON子句,否则抛异常。
5.1.5 NATURAL JOIN
NATURAL JOIN等同于INNER JOIN与USING的组合,它隐含的作用是将两个表中具有相同名称的列进行匹配。同样,NATURAL LEFT(RIGHT) JOIN等同于LEFT(RIGHT) JOIN于USING的组合。(自动寻找相同列,这个用法可xx)
5.1.6 STRAIGHT_JOIN
STRAIGHT_JOIN不是新的链接类型,而是用户对SQL优化器的控制,其等同于JOIN。通过STRAIGHT_JOIN,MySQL数据库会强制险读取左边的表。

5.2 其它联接分类
5.2.1 SELF JOIN

CREATE TABLE emp (
	emp_no int PRIMARY KEY,
	mgr_no int,
	emp_name varchar(30)
);

SELECT a.emp_name AS employee, b.emp_name AS manager
FROM emp a LEFT JOIN emp b ON a.mgr_no = b.emp_no

对于层次结构问题,通常可以通过SELF JOIN来解决。
5.2.2 NONEQUI JOIN
NONEQUI JOIN的联接包含“等于”运算符之外的运算符。
5.2.3 SEMI JOIN和ANTI SEMI JOIN
5.3 多表联接
5.4 滑动订单问题
5.5 联接算法
5.5.1 Simple Nested-Loops Join算法
5.5.2 Block Nested-Loops Join算法
5.5.3 Batched key Access Join算法
5.5.4 Classic Hash Join算法
5.6 集合操作
5.6.1 集合操作的概述
MySQL数据库支持两种集合操作:UNION ALL和UNION DISTINCT。与联接操作一样,集合操作也是对两个输入进行操作,并生成一个虚拟表。在联接操作中,一般把输入表称为左输入和右输入。集合操作的两个输入必须有相同的列数,若数据类型不同,MySQL数据库会自动将进行隐式转化。同时,结果列的名称由第一个输入决定。

注:在集合操作中,INTO OUTFILE只能存在于最后一个SELECT语句中,否则MySQL数据库会提示语法错误。

5.6.2 UNION DISTINCT和UNION ALL
UNION DISTINCT组合两个输入,并应用DISTINCT过滤重复项。一般省略DISTINCT关键字,直接用UNION。如果确认进行UNION操作的两个集合中没有重复的选项,最有效的办法是使用UNION ALL。UNION ALL组合两个输入中所有项的结果集,并包含重复的选项。

5.6.3 EXCEPT
5.6.4 INTERSECT

聚合和旋转操作
6.1 聚合
6.1.1 聚合函数
MySQL数据库支持聚合(aggregate)操作,按照分组对同一组内的数据聚合进行统计操作。目前MySQL数据库支持的聚合函数有:
avg()
big_and()
bit_or()
bit_xor()
count(distinct)
count()
group_concat()
max()
min()
std()
stddev_pop()
stddev_samp()
stddev()
sub()
var_pop()
var_samp()
variance()

6.1.2 聚合的算法
MySQL数据库仅支持流聚合,而其它数据库可能支持散列聚合。流聚合依赖于获得的存储在GROUP BY列中的数据。如果一个SQL查询中包含的GROUP BY语句多于一行,流聚合会先根据GROUP BY对行进行排序。(若要避免第一次的排序操作,可以在分组的列上添加索引,就是GROUP BY后的列)

6.2 附加属性聚合
6.3 联系聚合

游标
7.1 面向集合与面向过程的开发
7.2 游标的使用
7.3 游标的开销
7.5 使用游标解决问题
7.4.1 游标的性能分析
7.4.2 连续聚合
7.4.3 最大会话数

事务编程
8.1 事务概述
事务的ACID…
8.2 事务的分类
从理论角度:
扁平事务(flat transactions)
带有保存点的扁平事务(flat transactions with savepoints)
链事务(chained transactions)
嵌套事务(nested transactions)
分布式事务(distributed transactions)

扁平事务是最简单的一种,所有操作都处于同一层次,其由BEGIN WORK开始,由COMMIT WORK或ROLLBACK WORK接收。扁平事务的主要显示是不能提交或回滚事务的某一部分,或分几个步骤提交。

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

对扁平事务来说,其隐式地设置了一个保存点,但是在整个事务中,只有这一个保存点,回滚只能回滚到事务开始时的状态。保存点用SAVE WORK函数来建立,通知系统记录当前的处理状态。当出现问题时,保存点能用做内部的重启动点,根据应用逻辑,决定是回到最近一个保存点还是其他更早的保存点。

链事务可视为保存点模式的一个变种。在提供一个事务时,释放不需要的数据对象,将必要的处理上下文隐式地传给下一个要开始的事务。链事务中的回滚仅限于当前事务,即只能恢复到最近一个保存点。

嵌套事务是一个层次结构框架。

分布式事务通常是一个在分布式环境下运行的扁平事务,因此需要根据数据所在位置访问网络中的不同节点。
对于MySQL数据库(InnoDB存储引擎)来说,其支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。

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

可用的事务控制语句:
>START TRANSACTIN|BEGIN:显式地开启一个事务。
>COMMIT:COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的。
>ROLLBACK:回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
>SAVEPOINT identifier:SAVEPOINT运行在事务中创建一个保存点,一个事务中可以有多个SAVEPOINT。
>RELEASE SAVEPOINT identifier:删除一个事务的保存点,当没有一个保存点执行这语句时,会抛出一个异常。
>ROLLBACK TO [SAVEPOINT] identifier:把事务回滚到标记点,而不回滚在此标记点之前的任何工作。
>SET TRANSACTION:这个语句用来设置事务的隔离级别。

START TRANSACTIN和BEGIN语句都可以在MySQL命令行下显式地开启一个事务。但是在存储过程中,MySQL数据库的分析器会自动将BEGIN识别为BEGIN…END,因此在存储过程中只能使用START TRANSACTIN语句来开启一个事务。

COMMIT和COMMIT WORK语句基本上是一致的,都用来提交事务。不同之处在于COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE的。如果是CHAIN方式,那么事务就变成了链事务。可以通过参数completion_type来进行控制,默认该参数为0,表示没有任何操作。在这种设置下,COMMIT和COMMIT WORK是完全等价的。当completion_type为1时,COMMIT WORK等同于COMMIT AND CHAIN,表示马上自动开启一个相同隔离级别的事务。

8.4 隐式提交的SQL语句
8.5 事务的隔离级别
ANSI SQL标准定义了十个隔离级别:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

隔离级别越低,事务请求的锁越少或保护锁的事件就越短。这也是为何大多数数据库系统默认的事务隔离级别是READ COMMITTED的原因。

在InnoDB存储引擎中,可以使用以下命令来设置当前会话或全局的事务隔离级别:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
	READ UNCOMMITTED
	| READ COMMITTED
	| REPEATABLE READ
	| SERIALIZABLE	
}

如果要在MySQL启动时就设置事务的默认隔离级别,那就需要修改配置,在[mysqld]中添加如下行:

transaction-isolation = READ COMMITTED

查看当前会话或全局的事务隔离级别:

SELECT @@tx_isolation
SELECT @@global.tx_isolation

8.6 分布式事务编程
在使用分布式事务时,InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。

8.7 不好的事务编程习惯
8.8 长事务

索引
9.1 缓冲池、顺序读取与随机读取
基于磁盘的数据库系统一般都有缓冲池,即一块内存区域,其作用是将从磁盘上读取的指定大小数据(页或块),放入缓冲池。当再次读取是,数据库首先判断该页是否在缓冲池中,如果在则直接读取缓冲池中的页,如果不在则读取磁盘上的页。对于写操作,数据库将页读入缓冲池,然后在缓冲池中对页进行修改,修改完成后的页一般被异步地写入磁盘上。对于缓冲池的维护一般采用最近最少使用算法。由此可见,缓冲池的大小决定了数据库的性能。若数据库中的数据可以完全放入缓冲池中,则可以认为这时数据库的性能是最优的。除了同步/异步的写磁盘操作外,所有其它操作都可以在内存中完成。

对于MySQL数据库系统,由于其有着各种不同的存储引擎,因此其缓冲池是基于存储引擎的,也就是说每个存储引擎都有自己的缓冲池。对于MyISAM存储引擎来说,变量key_buffer_size决定了缓冲池的大小。对于InnoDB存储引擎来说,变量innodb_buffer_pool_size决定了缓冲池的大小。

9.2 数据结构与算法
9.2.1 二分查找法
9.2.2 二叉查找树和平衡二叉树
二叉查找树中,左子树的键值总是小于根的键值,右子树的键值总是大于根的键值,可以通过中序遍历得到键值的排序输出。

若想最大性能地构造一个二叉查找树,需要这个二叉查找树是平衡的,因此引入了平衡二叉树,又称为AVL树。平衡二叉树的定义:首先符合二叉查找树的定义,其次必须满足任何节点的两颗子树的高度最大差为1.
9.3 B+树
B+树和二叉树、平衡二叉树都是经典的数据结构。B+树是为磁盘或其它直接存储辅助设备设置的一种平衡查找树,在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点,各叶子节点通过指针进行链接。
9.3.1 B+树的插入操作
9.3.2 B+树的删除操作

9.4 B+树索引
9.4.1 InnoDB B+树索引
9.4.2 MyISAM B+树索引

9.5 Cardinality
9.5.1 什么是Cardinality
并不是所有在查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引,一般的经验是,在访问表中的很少一部分行时使用B+树索引才有意义。如果某个字段的取值范围很广,几乎没有重复,那么此时使用B+数索引是最适合的。

可以通过SHOW INDEX语句中的Cardinality列来观察索引是否是高选择性。
9.5.2 InnoDB存储引擎怎样统计Cardinality

9.6 B+树索引的使用
9.6.1 不同应用中B+树索引的使用
9.6.2 联合索引
9.6.3 覆盖索引
9.6.4 优化器选择不使用索引的情况
9.6.5 INDEX HINT
9.7 Multi-Range Read
MySQL数据库5.6版本开始支持Multi-Range Read(MRR)优化。MRR优化的目的就是减少磁盘的随机访问,并且将随机访问转化为较为顺序的数据访问。
9.8 Index Condition Pushdown
9.9 T树索引
9.10哈希索引

分区
10.1 分区概述
分区功能并不是在存储引擎层完成的,因此不只有InnoDB存储引擎支持分区,常见的存储引擎MyISAM、NDB等都支持分区。分区的过程是将一个表或索引分解为多个更小、更可管理的部分。从逻辑上,只有一个表或一个索引,但是物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。

MySQL数据库支持的分区类型为水平分区(行分散),并不支持垂直分区(列分散)。MySQL数据库的分区是局部分区索引,一个分区中既存放数据又存放索引。全局分区是指,数据存放各个分区中,但是所有数据的索引放在一个对象中(索引放一起,局部分区索引,索引放入各分区,和数据放一起)。

当前MySQL数据库支持以下几种类型的分区:
>RANGE分区:行数据基于属于一个给定连续区间的列值放入分区。MySQL5.5开始支持RANGE COLUMNS的分区。
LIST分区:和RANGE分区类型一样,只是LIST分区面向的是离散的值。>MySQL5.5开始支持LIST COLUMNS的分区。
>HASH分区:根据用户自定表达式的返回值来进行分区,返回值不能为负数
>KEY分区:根据MySQL数据库提供的离散函数来进行分区。
不论创建何种类型的分区,如果表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分。(*****)

10.2 分区类型
10.2.1 RANGE分区

CREATE TABLE t (
	id INT
)ENGINE=INNODB
PARTITION BY RANGE (id)(
	PARTITION p0 VALUES LESS THAN (10),
	PARTITION p1 VALUES LESS THAN (20)
);

小于10,插入p0分区,小于20,插入p1分区。查看表在磁盘上的物理文件,启用分区之后,表不再由一个ibd文件组成,而是由建立分区是的各个分区ibd文件组成。

可以通过查询information_schema架构下的PARTITIONS表来查看每个分区的具体信息:

SELECT * FROM information_schema.PARTITIONS
WHERE table_schema=database() AND table_name='t'

返回结果中的TABLE_ROWS列反应了每个分区中记录的数量。PARTITION_METHOD表示分区的类型。定义了分区的表,应该严格遵守分区的定义,当插入一个不在分区中定义的值时,MySQL数据库会抛出异常。对于这个问题,可以对分区添加一个MAXVALUE值的分区。MAXVALUE可理解为正无穷:

ALTER TABLE T
ADD PARTITION(
	partitin p2 values less than maxvalue
);

RANGE分区主要用于日期列的分区。例子:

CREATE TABLE sales(
	money INT UNSIGNED NOT NULL,
	date DATETIMES
)ENGINE=INNODB
PARTITION by RANGE (YEAR(date)) (
	PARTITION p2008 VALUE LESS THEN (2009),
	PARTITION p2008 VALUE LESS THEN (2010),
	PARTITION p2008 VALUE LESS THEN (2011),
)

这样创建的好处是便于对sales表的管理,如果要删除2008年的数据,只需要删除2008年数据所在的分区即可:

alter table sales drop partition p2008;

另一个好处是加快某些查询操作,例如值需要查询2008年整年的销售额:

EXPLAIN PARTITIONS 
SELECT * FROM sales WHERE date>='2008-01-01' AND date<='2008-12-31';

EXPLAIN PARTITIONS命令发现,SQL优化器值需要搜索p2008这个分区,而不会搜索所有的分区(称为Partition Pruning分区修剪),故查询的速度得到了大幅提升。

对RANGE分区的查询,优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()这类函数进行优化选择。

10.2.2 LIST分区
LIST分区和RANGE分区非常相似,只是分区列的值是离散的,而非连续的:

CREATE TABLE T (
	a INT,
	b INT
)ENGINE=INNODB
PARTITION BY LIST(b) (
	PARTITION p0 VALUES IN (1,3,5,7,9),
	PARTITION P1 VALUES IN (0,2,4,6,8)
)

不同于RANGE分区中定义的VALUES LESS THAN语句,LIST分区使用VALUES IN。因为每个分区的值是离散,因此只能定义值。

10.2.3 HASH分区
HASH分区的目的是将数据均匀地分布到预先定义的各个分区中,保证各分区的数据量大致是一样的。在HASH分区中,用户所要做的是基于将要被散列的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。

要使用HASH分区分割一个表,要在CREATE TABLE语句上添加一个PARTITION BY HASH(expr)子句,其中expr是返回一个整数的表达式。expr可以仅仅是字段类型为MySQL整型的列名。此外,用户需要在后面添加一个PARTITONS num子句,其中num是一个非负的整数,它表示表将要被分割成分区的数量。如果没有包含一个PARTITION子句,那么分区的数量将默认为1。

CREATE TABLE t_hash (
	a INT,
	b DATETIME
)ENGINE=INNODB
PARTITION BY HASH(YEAR(b))
PARTITION 4;

MySQL数据库还支持一种称为LINEAR HASH的分区,它使用一个更加复杂的算法来确定插入到已经分区的表中的位置。它的语法和HASH分区的语法相似,只是将关键字HASH改为LINEAR HASH。

10.2.4 KEY分区
KEY分区和HASH分区类似,不同在于HASH分区通过用户自定义的函数进行分区,KEY分区使用MySQL数据库提供的函数进行分区。NDB Cluster引擎使用MD5函数来分区,对于其他存储引擎,MySQL数据库使用其内部的散列函数来分区,这些函数基于与PASSWORD()一样的运算法则。

10.2.5 COLUMNS分区
RANGE、LIST、HASH和KEY这四种分区中,分区的条件必须是整型,如果不是,那么需要通过函数将其转化为整型,如YEAR()、TO_DAYS()、MONTH()等函数。MySQL5.5版本开始支持COLUMNS分区,可视为对RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区,分区根据类型直接比较而得到,不需要转化为整型。此外,RANGE COLUMNS分区可以对多个列的值进行分区。

MySQL 5.5开始支持COLUMNS分区,对于之前的RANGE和LIST分区,用户可以用RANGE COLUMNS和LIST COLUMNS分区来很好地代替。

10.3 子分区
子分区(subpartiton)是在分区的基础上在进行分区,有时也称这种分区为符合分区(composite partitioning)。MySQL数据库允许在RANGE和LIST的分区上在进行HASH或KEY的子分区。
10.4 分区中的NULL值
MySQL数据库允许对NULL值进行分区,但是处理方法可能与其它数据库完全不同。MySQL数据库的分区总是把NULL值视为小于任何一个非NULL值,这和MySQL数据库中处理NULL值的ORDER BY操作是一样的。
10.5 分区和性能
分区中存放了数据和索引,对于查询的条件,如果是分区的依据,那么可以确定定位到某个分区,否则就要便利全部分区,如果有索引,进入分区后比较索引。这个原理告诉我们,启用了分区,不见得查询会更快,只有查询条件是分区的依据时,可以明显提高速度。

10.6 在表和分区间交换数据