本章主要讲解MySQL的一些高级特性，其中包括MySQL查询缓存，查询缓存会存储一个SELECT查询的文本与被传送到客户端的相应结果。如果执行相同的一个SQL语句，MySQL数据库会将数据缓存起来以供下次直接使用，MySQL数据库以此优化查询缓存来提高缓存命中率。
MySQL 5.1及高版本支持分区表（partitioned table），分区表的使用大大增加了MySQL执行效率。另外本章还讲解到MySQL数据库事务，其中包括分布式事务的原理和语法。MySQL分布式事务涉及多个事务性的活动，本章介绍分布式事务使用的同时也涉及MySQL分布式事务技术存在的一些漏洞。
4.1 MySQL 查询缓存
MySQL服务器有一个重要特征是查询缓存。缓存机制简单地说就是缓存SQL语句和查询的结果，如果运行相同的SQL语句，服务器会直接从缓存中取到结果，而不需要再去解析和执行SQL语句。查询缓存会存储最新数据，而不会返回过期数据。当数据被修改后，在查询缓存中的任何相关数据均被清除。对于频繁更新的表，查询缓存是不适合的；而对于一些不经常改变数据且有大量相同SQL查询的表，查询缓存会提高很大的性能。
4.1.1 认识查询缓存
MySQL数据库设置了查询缓存后，当服务器接收到一个和之前同样的查询时，会从查询缓存中检索查询结果，而不是直接分析并检索查询。
在MySQL数据库中，使用查询缓存功能的具体操作步骤如下。
设置query_cache_type为ON，命令如下：
mysql set session query_cache_type=ON;
Query OK, 0 rows affected 0.01 sec
查看查询缓存功能是否被开启，命令如下：
mysql select @@query_cache_type;
--------------------
| @@query_cache_type |
--------------------
| ON
|
--------------------
1 row in set 0.00 sec
从结果可以看出，查询缓存功能已经被开启。
如果要禁用查询缓存功能，直接执行命令如下：
mysql
set session query_cache_type=OFF；
查看系统变量have_query_cache是否为YES，该参数表示MySQL的查询缓存是否可用，查看命令如下。
mysql show variables like ''have_query_cache'';
------------------ -------
| Variable_name
| Value |
------------------ -------
| have_query_cache | YES |
------------------ -------
1 row in set 0.00 sec
查询系统变量query_cache_size的大小，该参数表示数据库分配给查询缓存的内存大小，如果该参数的值设置为0，那么查询缓存功能将不起任何作用。
mysql select @@global.query_cache_size;
---------------------------
| @@global.query_cache_size |
---------------------------
| 0 |
---------------------------
1 row in set 0.00 sec
从结果可知，系统默认的query_cache_size参数值是0。
设置系统变量query_cache_size的大小，命令如下：
mysql set @@global.query_cache_size=1000000;
Query OK, 0 rows affected 0.00 sec
查询系统变量query_cache_size设置后的大小，命令如下：
mysql select @@global.query_cache_size;
---------------------------
| @@global.query_cache_size |
---------------------------
| 1000000 |
---------------------------
1 row in set 0.00 sec
如果需要将该参数永久修改，需要修改etcmy.cnf配置文件，添加该参数的选项，添加如下：
[mysqld]
port = 3306
query_cache_size = 1000000
如果查询结果很大，也可能缓存不了，需要设置query_cache_limit参数的值，该参数用来设置查询缓存的最大值，该值默认是1MB。
查询该参数的值的命令如下：
mysql select @@global.query_cache_limit;
----------------------------
| @@global.query_cache_limit |
----------------------------
|
1000000 |
----------------------------
1 row in set 0.00 sec
设置query_cache_limit参数值的大小，命令如下：
mysql set @@global.query_cache_limit=2000000;
Query OK, 0 rows affected 0.00 sec
如果需要将该参数永久修改，需要修改etcmy.cnf配置文件，添加该参数的选项，添加如下：
[mysqld]
port = 3306
query_cache_size=1000000
query_cache_limit=2000000
通过以上步骤的设置，MySQL数据库已经成功地开启查询缓存功能。在实际工作中，需要关注查询缓存的使用效率和性能，可以使用SHOW VARIABLES命令查询缓存的相关参数，命令如下。
mysql show variables like ''%query_cache%'';
------------------------------ ---------
| Variable_name | Value |
------------------------------ ---------
| have_query_cache
| YES |
| query_cache_limit | 1000000 |
| query_cache_min_res_unit | 4096
|
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
------------------------------ ---------
6 rows in set 0.00 sec
下面具体介绍查询缓存功能相关参数的含义。
l have_query_cache用来设置是否支持查询缓存区，YES表示支持查询缓存区。
l query_cache_limit 用来设置MySQL可以缓存的最大结果集，大于此值的结果集不会被缓存，该参数默认值是1MB。
l query_cache_min_res_unit用来设置分配内存块的最小体积。每次给查询缓存结果分配内存的大小，默认分配4096个字节。如果此值较小，那么会节省内存，但是这样会使系统频繁分配内存块。
l query_cache_size 用来设置查询缓存使用的总内存字节数，必须是1024字节的倍数。
l query_cache_type用来设置是否启用查询缓存。如果设置为OFF，表示不进行缓存；如果设置为ON，表示除了SQL_NO_CACHE的查询以外，缓存所有的结果；如果设置为DEMAND，表示仅缓存SQL_CACHE的查询。
l query_cache_wlock_invalidate 用来设置是否允许在其他连接处于lock状态时，使用缓存结果，默认是OFF，不会影响大部分应用。在默认情况下，一个查询中使用的表即使被LOCK TABLES命令锁住了，查询也能被缓存下来。可以通过设置该参数来关闭这个功能。
下面通过一个简单的例子来了解查询缓存的过程。
设置缓存内存大小为10240字节，开启查询缓存功能，命令如下。
mysql set global query_cache_size=10240;
Query OK, 0 rows affected 0.00 sec
mysql set session query_cache_type=ON;
Query OK, 0 rows affected 0.01 sec
查询t表中总共记录的条数，命令如下。
mysql use test;
Database changed
mysql show tables;
----------------
| Tables_in_test |
----------------
| rep_t1
|
| rep_t2
|
| t
|
----------------
3 rows in set 0.00 sec
mysql use test;
Database changed
mysql select count* from t;
----------
| count* |
----------
| 64 |
----------
1 row in set 0.05 sec
查询在缓存中命中的累计次数，命令如下：
mysql show status like ''Qcache_hits'';
--------------- -------
| Variable_name | Value |
--------------- -------
| Qcache_hits
| 0 |
--------------- -------
1 row in set 0.00 sec
从结果可知，第一次查询发现查询缓存累计命中的数是0。
再次查询t表的总的记录数据，然后查询缓存累计命中数，命令如下：
mysql select count* from t;
----------
| count* |
----------
| 64 |
----------
1 row in set 0.00 sec
mysql show status like ''Qcache_hits'';
--------------- -------
| Variable_name | Value |
--------------- -------
| Qcache_hits
| 1 |
--------------- -------
1 row in set 0.00 sec
从结果可知，第二次查询后发现该缓存累计命中数已经发生了变化，此时查询出参数Qcache_hists的值是1，表示查询直接从缓存中获取结果，不需要再去解析SQL语句。
连续两次查询t表的总的记录数据，然后再查询缓存累计命中数，此时会发现缓存累计命中数已经变成了3，本次查询也是直接从缓存中取到结果，执行命令如下。
mysql select count* from t;
----------
| count* |
----------
| 64 |
----------
1 row in set 0.00 sec
mysql select count* from t;
----------
| count* |
----------
| 64 |
----------
1 row in set 0.00 sec
mysql show status like ''Qcache_hits'';
--------------- -------
| Variable_name | Value |
--------------- -------
| Qcache_hits
| 3 |
--------------- -------
1 row in set 0.00 sec
增加了一条记录到t表，插入数据后，跟该表所有相关的查询缓存就会被清空掉，然后重新查询t表的总的记录，此时发现缓存累计命中数没有发生变化，说明本次查询没有直接从缓存中取到数据，执行命令如下。
mysql insert into t values2;
Query OK, 1 row affected 0.00 sec
mysql select count* from t;
----------
| count* |
----------
| 65 |
----------
1 row in set 0.00 sec
mysql show status like ''Qcache_hits'';
--------------- -------
| Variable_name | Value |
--------------- -------
| Qcache_hits
| 3 |
--------------- -------
1 row in set 0.00 sec
重新查询t表的总的记录，此时发现缓存累计命中数发生了变化，说明缓存继续起作用了，执行命令如下。
mysql select count* from t;
----------
| count* |
----------
| 65 |
----------
1 row in set 0.00 sec
mysql show status like ''Qcache_hits'';
--------------- -------
| Variable_name | Value |
--------------- -------
| Qcache_hits
| 4 |
--------------- -------
1 row in set 0.00 sec
通过上面的例子不难发现，查询缓存适合更新不频繁的表，当表更改后，查询缓存值的相关条目被清空。
4.1.2 监控和维护查询缓存
在日常工作中，经常使用以下命令监控和维护查询缓存。
（1）flush query cache：该命令用于整理查询缓存，以便更好地利用查询缓存的内存，这个命令不会从缓存中移除任何查询结果。命令运行如下：
mysql flush query
cache;
Query OK, 0 rows
affected 0.00 sec
（2）reset query cache：该命令用于移除查询缓存中所有的查询结果。命令运行如下：
mysql reset
query cache;
Query OK, 0 rows affected 0.00 sec
（3）show status like ''Qcache%''：该命令可以监视查询缓存的使用状况，可以计算出缓存命中率。命令运行如下：
mysql show status like ''Qcache%'';
------------------------- ---------
| Variable_name | Value |
------------------------- ---------
| Qcache_free_blocks | 4984
|
| Qcache_free_memory | 30097400|
| Qcache_hits | 24 |
| Qcache_inserts
| 4342 |
| Qcache_lowmem_prunes | 41224
|
| Qcache_not_cached | 2654
|
| Qcache_queries_in_cache | 20527 |
| Qcache_total_blocks | 46362
|
------------------------- ---------
8 rows in set 0.00 sec
结果中的性能监控参数含义如表4-1所示。
表4-1 查询缓存的性能监控参数含义
变量
含义
Qcache_queries_in_cache
在缓存中已注册的查询数目
Qcache_inserts
被加入到缓存中的查询数目
Qcache_hits
缓存采样数的数目
Qcache_lowmem_prunes
因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached
没有被缓存的查询数目
Qcache_free_memory
查询缓存的空闲内存总数
Qcache_free_blocks
查询缓存中的空闲内存块的数目
Qcache_total_blocks
查询缓存中的块的总数目
如果空闲内存块是总内存块的一半左右，则表示存在严重的内存碎片。通常使用flush
query cache命令整理碎片，然后采用reset query cache命令清理查询缓存。
如果碎片很少，但是缓存命中率很低，则说明设置的缓存内存空间过小，服务器频繁删除旧的查询缓存，腾出空间，以保存新的查询缓存，此时，参数Qcache_lowmeme_preunes状态值将会增加，如果此值增加过快，可能是有以下原因造成：
l 如果存在大量空闲块，则是因为碎片的存在而引起的。
l 空闲内存块较少，可以适当地增加缓存大小。
4.1.3 如何检查缓存命中率
MySQL检查缓存命中率的方式十分简单快捷。缓存就是一个查找表（Lookup
Table）。查找的键就是查询文本﹑当前数据库﹑客户端协议的版本，以及其他少数会影响实际查询结果的因素的哈希值。
下面主要学习MySQL数据库中缓存的管理技巧，以及如何合理配置MySQL数据库缓存，提高缓存命中率。
首先，在配置数据库客户端或者是第三方工具与服务器连接时，应该保证数据库客户端的字符集跟服务器的字符集保持一致。在实际工作中，经常发现客户端配置的字符集和服务器字符集兼容没有完全一致，即使此时客户端没有出现乱码情况，查询数据可能就因为字符集不同的原因而没有被数据库缓存起来。
其次，为了提高数据库缓存的命中率，应该在客户端和服务器端采用一样的SQL语句。从数据库缓存的角度考虑，数据库查询SQL的语句是不区分大小写的，比如第一个查询语句采用大写语句，第二个查询语句采用小写语句，但对于缓存来讲，大小写不同的SQL语句会被当作不同的查询语句。
查询缓存不会存储不确定结果的查询，任何一个包含不确定函数比如NOW或CURRENT_DATE的查询不会被缓存。同样地，CURRENT_USER或CONNECTION_ID这些由不同用户执行，将会产生不同的结果的查询也不会被缓存。实际上，查询缓存不会缓存引用了用户自定义函数﹑存储函数﹑用户自定义变量﹑临时表的查询。
查询缓存只是发生在服务器第一次接收到SQL查询语句，然后把查询结果缓存起来，对于查询中的子查询、视图查询和存储过程查询都不能缓存结果，对于预存储语句同样也不能使用缓存。
使用查询缓存有利也有弊。一方面，查询缓存可以使查询变得更加高效，改善了MySQL服务器的性能；另一方面，查询缓存本身也需要消耗系统IO资源，所以说，查询缓存也增加了服务器额外的开销，主要体现以下几个方面。
l MySQL服务器在进行查询之前首先会检测查询缓存是否存在相同的查询条目。
l MySQL服务器在进行查询操作时，如果缓存中没有相同的查询条目，会将查询的结果缓存到查询缓存，这个过程也需要开销系统资源。
l 如果数据库表发生增加操作，MySQL服务器查询缓存中相对应的查询结果将会无效，这时同样需要消耗系统资源。
除了注意以上问题可以提高查询缓存的命中率外，还可以通过分区表提高缓存的命中率。通常我们会遇到这样的问题，对于某张表某个时间段内的数据更新比较频繁，其他时间段查询和更新比较多，一旦数据表数据执行更新操作，那么查询缓存中的信息将会清空，查询缓存的命中率不会很高。此时，可以考虑采用分区表，把某个时间段的数据存放在一个单独的分区表中，这样可以提高服务器的查询缓存的命中率。
4.1.4 优化查询缓存
MySQL查询缓存优化方案的大致步骤如图4-1所示。
#ssl
#ssl-ca=etcmyqlnewcertsca-cert.pem
#ssl-cert=etcmysqlnewcertsserver-cert.pem
#ssl-key=etcmysqlnewcertsserver-key.pem
###########################################################################
[mysqld]
port = 3309
socket = tmpmysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
query_cache_size =
999424
table_cache = 64
sort_buffer_size =
512K
net_buffer_length = 8K
read_buffer_size =
256K
read_rnd_buffer_size =
512K
myisam_sort_buffer_size
= 8M
server-id = 1
启动MySQL，如下所示。
[root@localhost ~]#
mysql -u root
Welcome to the MySQL
monitor. Commands end with ; or \g.
Your MySQL connection
id is 1
Server version: 5.6.10-log
MySQL Community Server GPL
Type ''help;'' or ''\h''
for help. Type ''\c'' to clear the current input statement.
mysql
数据库分区是一种物理数据库设计技术，分区的主要目的是为了让某些特定的查询操作减少响应时间，同时对于应用来讲分区完全是透明的。MySQL的分区主要有两种形式：水平分区（Horizontal Partitioning）和垂直分区（Vertical Partitioning）。
水平分区（Horizontal Partitioning）是根据表的行进行分割，这种形式的分区一定是通过表的某个属性作为分割的条件，例如，某张表里面数据日期为2011年的数据和日期为2012年的数据分割开，就可以采用这种分区形式。
垂直分区（Vertical Partitioning）是通过对表的垂直划分来减少目标表的宽度，是某些特定的列被划分到特定的分区。
通常可以通过下面命令查看是否支持分区，命令如下：
mysql show
variables like ''%partition%'';
------------------- -------
| Variable_name | Value |
------------------- -------
| have_partitioning |
YES |
------------------- -------
1 row in set 0.01
sec
下面介绍MySQL各种分区表常用的操作案例。
1. RANGE分区
RANGE分区使用values
less than 操作符来进行定义，把连续且不相互重叠的字段分配给分区，命令如下。
mysql create table
emp
- empno varchar20 not null,
- empname varchar20,
-
deptno int,
- birthdate date,
- salary int
-
- partition by rangesalary
-
- partition p1 values less than1000,
- partition p2 values less than2000,
- partition p3 values less than3000
- ;
Query OK, 0 rows
affected 0.01 sec
mysql insert into
emp values1000,''kobe'',12,''1888-08-08'',1500;
Query OK, 1 row
affected 0.01 sec
mysql insert into
emp values1000,''kobe'',12,''1888-08-08'',3500;
ERROR 1526 HY000:
Table has no partition for value 3500
此时，按照工资级别（字段salary）进行表分区，partition
by range 的语法类似于switchcase的语法，如果salary小于1000，数据存储在p1分区；如果salary小于2000，数据存储在p2分区；如果salary小于3000，数据存储在p3分区。
上面插入的第二条数据工资级别（字段salary）为3500，此时没有分区用来存储该范围的数据，所以发生了错误。为了解决这种问题，加入PARTITION p4 VALUES LESS THAN MAXVALUE语句即可，命令如下。
mysql drop table emp;
Query OK, 0 rows affected 0.00 sec
mysql create table
emp
- empno varchar20 not null,
- empname varchar20,
- deptno int,
- birthdate date,
- salary int
-
- partition by rangesalary
-
- partition p1 values less than1000,
- partition p2 values less than2000,
- partition p3 values less than3000,
- partition p4 values less than
maxvalue
- ;
Query OK, 0 rows
affected 0.01 sec
mysql insert into emp
values1000,''kobe'',12,''1888-08-08'',1000;
Query OK, 1 row
affected 0.00 sec
mysql insert into
emp values1000,''durant'',12,''1888-08-08'',3500;
Query OK, 1 row
affected 0.00 sec
maxvalue 表示最大的可能的整数值。值得注意的是values less than子句中也可以使用一个表达式，不过表达式结果不能为NULL，下面按照日期进行分区，命令如下。
mysql create table
emp
- empno varchar20 not null,
- empname varchar20,
- deptno int,
- birthdate date,
- salary int
-
- partition by rangeyearbirthdate
-
partition p0 values less than1980,
-
partition p1 values less than1990,
-
partition p2 values less than2000,
-
partition p3 values less than maxvalue
- ;
Query OK, 0 rows
affected 0.01 sec
该方案中，生日1980年以前的员工信息存储在p0分区中，生日1990年以前的员工信息存储在p1分区中，生日2000年以前的员工信息存储在p2分区中，2000年以后出生的员工信息存储在p3分区中。
RANGE分区很有用，常常使用在以下几种情况。
（1）如果要删除某个时间段的数据时，只需要删除分区即可。例如，要删除1980年以前出生员工的所有信息，此时会执行alter table emp drop partition p0的效率要比执行delete from emp where
yearbirthdate=1980高效得多。
（2）如果使用包含日期或者时间的列可以考虑用到RANGE分区。
（3）经常运行直接依赖于分割表的列的查询。比如，当执行某个查询，如select count（*） from emp
where yearbirthdate = 1999 group by empno，此时MySQL数据库可以很迅速地确定只有分区p2需要扫描，这是因为查询条件对于其他分区不符合。
2. LIST分区
LIST分区类似RANGE分区，他们的区别主要在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个集合，而RANGE分区是从属于一个连续区间值的集合。创建LIST分区命令如下：
mysql create table employees
- empname varchar20,
- deptno int,
- birthdate date not null,
- salary int
-
- partition by
listdeptno
-
-
partition p1 values in 10,20,
-
partition p2 values in 30,
-
partition p3 values in 40
- ;
Query OK, 0 rows
affected 0.01 sec
以上示例以部门编号划分分区，10号部门和20号部门的员工信息存储在p1分区，30号部门的员工信息存储在p2分区，40号部门的员工信息存储在p3分区，同RANG分区一样，如果插入数据的部门编号不在分区值列表中时，那么insert插入操作将失败并报错。
3. HASH分区
HASH分区是基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
先看下面的例子：
mysql create table
htable
-
id int,
-
name varchar20,
-
birthdate date not null,
-
salary int
-
- partition by hashyearbirthdate
- partitions 4;
Query OK, 0 rows
affected 0.00 sec
当使用了PARTITION BY HASH时，MySQL将基于用户函数结果的模数来确定使用哪个编号的分区。将要保存记录的分区编号为N = MOD表达式, num。如果表htable中插入一条birthdate为2010-09-23的记录，可以通过如下方法计算该记录的分区。
modyear2010-09-23,4
=mode2010,4
=2
此时，该条记录的数据将会存储在分区编号为2的分区空间。
4. 线性HASH分区
线性HASH分区和HASH分区的区别在于，线性哈希功能使用的一个线性的2的幂运算法则，而HASH分区使用的是哈希函数的模数。
先看下面的例子：
mysql create table
lhtable
-
id int not null,
-
name varchar20,
-
hired date not null default ''1999-09-09'',
-
deptno int
-
-
partition by linear hashyearhired
-
partitions 4;
Query OK, 0 rows
affected 0.03 sec
如果表lhtable中插入一条hireddate为2010-09-23的记录，记录将要保存的分区是num个分区中的分区N，可以通过如下方法计算N。
找到下一个大于num的2的幂，把这个值称作V，可以通过下面的公式得到。V =
POWR2,CEILINGLOG2,num，假设，num的值是13，那么LOG2,13就是3.70043。CEILING3.70043就是4，则V
= POWER2,4，即等于16。
计算 N
= Fcolumn_list V 1 此时当N=num时，V = CEILV2, 此时N
= N V-1。
下面使用一个示例来说明通过线性哈希分区算法计算分区N的值，线性哈希分区表t1是通过下面的语句创建。
mysql create table
t1
-
col1 int,
-
col2 char5,
-
col3 date
-
- partition by linear hash yearcol3
- partitions 6;
Query OK, 0 rows
affected 0.59 sec
现在假设要插入两条记录到表t1中，其中一条记录col3列的值为2003-04-14，另一条记录cols列值为1998-10-19。第一条记录要保存到的分区计算过程如下：
记录将要保存到的分区num分区中的分区N，假设num是7个分区，假设表t1使用线性HASH分区且有4个分区。
V = POWR2,CEILINGLOG2,num
V = POWR2,CEILINGLOG2,7 = 8
N = YEAR''2003-04-14''
8 - 1
= 2003
7
= 3
N的值是3，很显然 3=4 不成立，所以附件条件不执行，所以第一条记录的信息将存储在3号分区中。
第二条记录将要保存到的分区序号计算如下：
V =
POWR2,CEILINGLOG2,num
V =
POWR2,CEILINGLOG2,7 = 8
= YEAR''1998-10-19''
8 - 1
= 1998 7
= 6
N的值是6，很显然 6=4成立，所以附件条件会执行。
V = CEIL62 = 3
N = N V-1
= 6 2
= 2
此时发现2 = 4不成立，记录将被保存到#2分区中。按照线性哈希分区的优点在于增加﹑删除﹑合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000GB）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。
5. KEY分区
类似于HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数，这些函数是基于与PASSWORD一样的运算法则。
先看下面的例子：
mysql create table
keytable
-
id int,
-
name varchar20 not null,
-
deptno int,
-
birthdate date not null,
-
salary int
-
- partition by keybirthdate
- partitions 4;
Query OK, 0 rows
affected 0.11 sec
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂算法得到，而不是通过模数算法。
6. 复合分区
复合分区是分区表中每个分区的再次分割，子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为子分区。
复合分区需要注意以下问题：
l 如果一个分区中创建了复合分区，其他分区也要有复合分区。
l 如果创建了复合分区，每个分区中的复合分区数必有相同。
l 同一分区内的复合分区，名字不相同，不同分区内的复合分区名字可以相同。
下面通过案例讲述不同的复合分区的创建方法。
创建RANGE - HASH复合分区的命令如下：
mysql create table rhtable
-
-
empno varchar20 not null,
-
empname varchar20,
-
deptno int,
-
birthdate date not null,
-
salary int
-
- partition by rangesalary
- subpartition by hash yearbirthdate
- subpartition 3
-
-
partition p1 values less than 2000,
-
partition p2 values less than maxvalue
- ;
Query OK, 0 rows
affected 0.23 sec
创建RANGE - KEY复合分区的命令如下：
mysql create table
rktable
- no varchar20 not null,
- name varchar20,
- deptno int,
- birth date not null,
- salary int
-
- partition by rangesalary
- subpartition by keybirth
- subpartitions 3
-
-
partition p1 values less than 2000,
-
partition p2 values less than maxvalue
- ;
Query OK, 0 rows
affected 0.07 sec
创建LIST - HASH复合分区的命令如下：
mysql create table
lhtable
-
no varchar20 not null,
-
name varchar20,
-
deptno int,
-
birth date not null,
-
salary int
-
- partition by listdeptno
- subpartition by hash yearbirth
- subpartitions 3
-
-
partition p1 values in 10,
- partition p2 values in 20
- ;
Query OK, 0 rows
affected 0.08 sec
创建LIST - KEY复合分区的命令如下：
mysql create table
lktable
-
no varchar20 not null,
-
name varchar20,
-
deptno int,
-
birthdate date not null,
-
salary int
-
- partition by listdeptno
- subpartition by keybirthdate
- subpartitions 3
-
-
partition p1 values in 10,
-
partition p2 values in 20
- ;
Query OK, 0 rows
affected 0.09 sec
4.3 事务控制
MySQL通过SET
AUTOCOMMIT﹑START TRANSACTION﹑COMMIT和ROLLBACK等语句控制本地事务，具体语法如下。
START TRANSACTION |
BEGIN [WORK];
COMMIT [WORK] [AND
[NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND
[NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0|1}
其中START TRANSACTION表示开启事务、COMMIT表示提交事务、ROLLBACK表示回滚事务、SET
AUTOCOMMIT用于设置是否自动提交事务。
默认情况下，MySQL事务是自动提交的，如果需要通过明确的COMMIT和ROLLBACK再提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理有明显不同的地方。如果应用从Oracle数据库迁移到MySQL数据库，则需要确保应用中是否对事务进行了明确的管理。
MySQL的AUTOCOMMIT（自动提交）默认是开启，对MySQL的性能有一定影响，举个例子来说，如果用户插入了1000条数据，MySQL会提交事务1000次。这时可以把自动提交关闭掉，通过程序来控制，只要一次提交事务就可以了。
可以通过如下方式关掉自动提交功能，命令如下：
mysql set
@@autocommit=0;
Query OK, 0 rows
affected 0.00 sec
查看自动提交功能是否被关闭，命令如下：
mysql show
variables like "autocommit";
--------------- -------
| Variable_name |
Value |
--------------- -------
| autocommit | ON
|
--------------- -------
1 row in set 0.02
sec
下面通过两个Session（Session1和Session2）来理解事务控制的过程，具体操作步骤如下：
在Session1中，打开自动提交事务功能，然后创建表ctable并插入两条记录。命令如下：
mysql set
@@autocommit=1;
Query OK, 0 rows
affected 0.00 sec
CREATE TABLE ctable
data INT4,
;
mysql insert into
ctable values1;
Query OK, 1 row
affected 0.02 sec
mysql insert into
ctable values2;
Query OK, 1 row
affected 0.00 sec
在Session2中，打开自动提交事务功能，然后查询表ctable，命令如下：
mysql set
@@autocommit=1;
Query OK, 0 rows
affected 0.00 sec
mysql select *
from ctable;
------
| data |
------
| 1 |
| 2 |
------
2 rows in set 0.00
sec
在Session1中，关闭自动提交事务功能，然后向表ctable中插入两条记录，命令如下：
mysql set
@@autocommit=0;
Query OK, 0 rows
affected 0.00 sec
mysql insert into
ctable values3;
Query OK, 1 row
affected 0.00 sec
mysql insert into
ctable values4;
Query OK, 1 row
affected 0.00 sec
在Session2中，查询表ctable，命令如下：
mysql select *
from ctable;
------
| data |
------
| 1 |
| 2 |
------
2 rows in set 0.00
sec
从结果可以看出，在session1中新插入的两条记录没有查询出来。
在Session1中，提交事务，命令如下：
mysql commit;
Query OK, 0 rows
affected 0.01 sec
在Session2中，查询表ctable，命令如下：
mysql select *
from ctable;
------
| data |
------
| 1 |
| 2 |
| 3 |
| 4 |
------
4 rows in set 0.00
sec
如果在表的锁定期间，如果使用START TRANSACTION 命令开启一个新的事务，会造成一个隐含的unlock tables被执行，该操作存在一定的隐患。下面通过一个案例来理解。
在Session1中，查询nbaplayer表，结果为空，命令如下：
mysql select *
from nbaplayer;
Empty set 0.00 sec
在Session2中，查询nbaplayer表，结果为空，命令如下：
mysql select *
from nbaplayer;
Empty set 0.00 sec
在Session1中，对表nbaplayer加写锁，命令如下：
mysql lock table
nbaplayer write;
Query OK, 0 rows
affected 0.00 sec
在Session2中，向表nbaplayer中增加一条记录，命令如下：
mysql insert into
nbaplayer values
1,''kobe'',10000;
在Session1中，插入一条记录，命令如下：
mysql insert into
nbaplayer values
2,''durant'',40000;
Query OK, 1 row
affected 0.02 sec
在Session1中，回滚刚才插入的记录，命令如下：
mysql rollback;
Query OK, 0 rows
affected 0.00 sec
在Session1中，开启一个新的事务，命令如下：
mysql start
transaction;
Query OK, 0 rows
affected 0.00 sec
在Session2中，表锁被释放，此时成功增加该条记录，命令如下：
mysql insert into
nbaplayer values
1,''kobe'',10000;
Query OK, 1 row
affected 2 min 32.99 sec
在Session2中，查询nbaplayer，命令如下：
mysql select *
from nbaplayer;
------ -------- --------
| id | name
| salary |
------ -------- --------
| 2 | durant | 40000 |
| 1 | kobe |
10000 |
------ -------- --------
2 rows in set 0.00
sec
从结果可以看出，此时发现Session1的回滚操作并没有执行成功。
MySQL提供的LOCK IN SHARE MODE锁可以保证会停止任何对它要读的数据行的更新或者删除操作。下面通过一个例子来理解。
在Session1中，开启一个新的事务，然后查询数据表nbaplayer的salary列的最大值，命令如下：
mysql begin;
Query OK, 0 rows
affected 0.00 sec
mysql select
maxsalary from nbaplayer lock in share mode;
-----------------
| maxsalary |
-----------------
| 40000
|
-----------------
1 row in set 0.00
sec
在Session2中，尝试做更新操作，命令如下：
mysql update
nbaplayer set salary = 90000 where id = 1;
等待
在Session1中，提交事务，命令如下：
mysql commit;
Query OK, 0 rows
affected 0.00 sec
在Session2中，等Session1的事务提交后，此时更新操作成功执行，结果如下：
mysql update
nbaplayer set salary = 90000 where id = 1;
Query OK, 1 row
affected 16.25 sec
Rows matched: 1 Changed: 1
Warnings: 0
4.4 MySQL分布式事务
在MySQL中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器，分布式事务的事务参与者、资源管理器、事务管理器等位于不同的节点上，这些不同的节点相互协作共同完成一个具有逻辑完整性的事务。分布式事务的主要作用在于确保事务的一致性和完整性。
4.4.1 了解分布式事务的原理
资源管理器（Resource Manager，简称RM）用于向事务提供资源，同时还具有管理事务提交或回滚的能力。数据库就是一种资源管理器。
事务管理器（Transaction Manager，简称TM）用于和每个资源管理器通信，协调并完成事务的处理。一个分布式事务中各个事务均是分布式事务的分支事务。分布式事务和各分支通过一种命名方法进行标识。
MySQL执行分布式事务，首先要考虑网络中涉及多少个事务管理器，MySQL分布式事务管理，简单地讲就是同时管理若干管理器事务的一个过程，每个资源管理器的事务当执行到被提交或者被回滚的时候，根据每个资源管理器报告的有关情况决定是否将这些事务作为一个原子性的操作执行全部提交或者全部回滚。因为MySQL分布式事务同时涉及多台MySQL服务器，所以在管理分布式事务的时候，必须要考虑网络可能存在的故障。
用于执行分布式事务的过程使用两个阶段。
（1）第一阶段：所有的分支被预备。它们被事务管理器告知要准备提交，每个分支资源管理器记录分支的行动并指示任务的可行性。
（2）第二阶段：事务管理器告知资源管理器是否要提交或者回滚。如果预备分支时，所有的分支指示它们将能够提交，那么所有的分支被告知提交。如果有一个分支出错，那么就全部都要回滚。特殊情况下，只有一个分支的时候，第二阶段则被省略。
分布式事务的主要作用在于确保事务的一致性和完整性。它利用分布式的计算机环境，将多个事务性的活动合并成一个事务单元，这些事务组合在一起构成原子操作，这些事务的活动要么一起执行并提交事务，要么回滚所有的操作，从而保证了多个活动之间的一致性和完整性。
4.4.2 分布式事务的语法
在MySQL中，执行分布式事务的语法格式如下：
XA {START|BEGIN} xid
[JOIN|RESUME]
XA
START xid 表示用于启动一个事务标识为xid的事务。xid分布式事务表示的值既可以由客户端提供，也可以由MySQL服务器生成。
结束分布式事务的语法格式如下：
XA END xid [SUSPEND
[FOR MIGRATE]]
其中xid包括：gtrid [, bqual [, formatID ]]，含义如下：
l gtrid是一个分布式事务标识符。
l bqual表示一个分支限定符，默认值是空字符串。对于一个分布式事务中的每个分支事务，bqual值必须是唯一的。
l
formatID是一个数字，用于标识由gtrid和bqual值使用的格式，默认值是1。
XA PREPARE xid
该命令使事务进入PREPARE状态，也就是两个阶段提交的第一个阶段。
XA COMMIT xid [ONE
PHASE]
该命令用来提交具体的分支事务。
XA ROLLBACK xid
该命令用来回滚具体的分支事务。也就是两阶段提交的第二个提交阶段，分支事务被实际地提交或者回滚。
XA RECOVER
该命令用于返回数据库中处于PREPARE状态的分支事务的详细信息。
分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时如何解决故障。
分布式事务的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等。
MySQL 分布式事务分为两类：内部分布式事务和外部分布式事务。内部分布式事务用于同一实例下跨多个数据引擎的事务，由二进制日志作为协调者；而外部分布式事务用于跨多个MySQL实例的分布式事务，需要应用层介入作为协调者，全局提交还是回滚，都是由应用层决定的，对应用层的要求比较高。
MySQL分布式事务在某些特殊的情况下会存在一定的漏洞，当一个事务分支在PREPARE状态的时候失去了连接，在服务器重启以后，可以继续对分支事务进行提交或者回滚操作，没有写入二进制日志，这将导致事务部分丢失或者主从数据库不一致。
4.5 小结
本章主要讲解MySQL的一些高级特性，其中包括MySQL查询缓存，优化查询缓存来提高缓存命中率，并且详细介绍了MySQL合并表和分区，MySQL提供的事务控制和锁定语法，并对MySQL事务管理和分布式事务进行了简单的介绍。值得注意的是MySQL分布式事务存在一定的漏洞，MySQL分布式事务在特殊情况下是无法保证事务的完整性。
購物車/收銀台(0) | 在線留言板
| 付款方式
| 運費計算
| 聯絡我們
| 幫助中心 |
加入書簽
HOME
新書上架
