MySQL详解（四）：MySQL的高级特性

MySQL的高级特性

1. MySQL查询缓存

MySQL服务器有一个重要的特征是查询缓存。缓存机制简单地说就是缓存SQL语句和查询的结果，如果运行相同的SQL语句，服务器会直接从缓存中取出结果，而不需要再次去执行SQL语句。当数据被修改之后，在查询缓存中的任何数据均被清除。对于频繁更新的表，查询缓存是不适合的；而对于一些不经常改变数据并且有大量相同SQL查询的表，查询缓存会提高很大的性能；

1.认识查询缓存

MySQL数据库设置了查询缓存之后，当服务器接收到一个和之前同样的查询的时候，会从查询缓存中检索查询的结果，而不是直接分析并检索查询；

 

在MySQL中使用查询缓存功能的具体步骤：

步骤：

1、设置query_cache_type为ON（禁用的话设置为OFF）

mysql > set session query_cache_type=ON;

 

2、查看查询缓存功能是否开启

select @@query_cache_type;

 

3、查看系统变量have_query_cache是否为‘YES’，该参数表示MySQL的查询缓存功能是否可用

show variables like ‘have_query_cache’;

 

4、查询系统变量query_cache_size的大小，该参数表示数据库分配给查询缓存的内存大小，如果该参数的值设置为0，那么查询缓存功能将不起任何的作用；

mysql>select @@global.query_cache_size;

 

5、设置系统变量query_cache_size的大小，命令：

set @@global.query_cache_size=1000000;

6、如果需要将该参数永久的修改，需要修改/etc/my.cnf配置文件，添加该参数的选项：

[mysqld]

port=3306

query_cache_size=1000000

7、如果查询结果很大，也可能缓存不了，需要设置query_cache_limit参数的值，该参数用来设置查询缓存的最大值，该值默认是1MB

mysql>select @@grobal.query_cache_limit;

 

8、设置query_cache_limit参数值的大小；

mysql> set @@global.query_cache_limit =2000000;

如果需要永久的修改则需要修改my.cnf文件

添加参数：

[mysqld]

port=3306

query_cache_size=1000000

query_cache_limit=2000000

9、使用SHOW VARIABLES 命令查询缓存的相关参数；

mysql> show variables like '%query_cache%';

+------------------------------+---------+

| Variable_name                | Value   |

+------------------------------+---------+

| have_query_cache             | YES     |

| query_cache_limit            | 1048576 |

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 0       |

| query_cache_type             | ON      |

| query_cache_wlock_invalidate | OFF     |

+------------------------------+---------+

 

查询缓存功能相关参数的含义：

have_query_cache：

用来设置是否支持查询缓存区，“YES”表示支持查询缓存区

query_cache_limit：      

         设置MySQL可以缓存的最大结果集，

query_cache_min_res_unit：

         用来设置内存块的最小体积。每次给查询缓存结果分配内存的大小，默认分配4096个字节。如果此值较小，那么会节省内存，但是这样会使系统频繁分配内存；

query_cache_size： 

         用来设置查询缓存使用的总内存字节数，必须是1024字节的倍数；

query_cache_type：         

         用来设置是否启用查询缓存。如果设置为OFF，表示不进行缓存；如果设置为ON，表示除了SQL_NO_CACHE的查询之外，缓存所有的结果；如果设置为DEMAND，表示仅缓存SQL_CACHE的查询；

query_cache_wlock_invalidate：

         用来设置是否允许在其他连接处于lock状态的时候，使用缓存结果，默认是OFF，不会影响大部分应用。在默认情况下，一个查询中使用的表即使被LOCK TABLES命令锁住了，查询也能被缓存下来。可以通过设置该参数来关闭这个功能；

可以用以下命令查看具体使用情况：
mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 195024544 |
| Qcache_hits             | 0         |
| Qcache_inserts          | 3         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 121       |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 4         |
+-------------------------+-----------+
8 rows in set (0.00 sec)

相关参数解释如下：
Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。flush query cache会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory：缓存中的空闲内存。
Qcache_hits：多少次命中，通过这个参数可以查看query cache的基本命中效果。
Qcache_inserts：每次插入一个查询时就增大，命中次数除以插入次数就是非命中率。
Qcache_lowmem_prunes：多少条query因为内存不足而被清理出query cache，通过Qcache_lowmem_prunes和Qcache_free_memory参数相互结合，能够
更清楚地了解到系统中query cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有query被换出的情况。
Qcache_not_cached：不适合进行缓存的查询数量，通常是由于这些查询不是SELECT语句或用了now()之类的函数。
Qcache_queries_in_cache：当前缓存的查询和响应数量。
Qcache_total_blocks：缓存中块的数量。

2.监控和维护查询缓存

经常使用的命令监控和维护查询缓存：

1、flush query cache：

                   整理查询缓存，以便更好的利用查询缓存的内存，这个命令不会从缓存中移除任何查询结果。

2、reset query cache：

         该命令用于移除查询缓存中所有的查询结果。

3、监视查询缓存的使用状况，可以计算出缓存命中率：

mysql> show global status like 'qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 195024544 |
| Qcache_hits             | 0         |
| Qcache_inserts          | 3         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 121       |
| Qcache_queries_in_cache | 1         |
| Qcache_total_blocks     | 4         |
+-------------------------+-----------+

相关参数解释如下：

变量	含义
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_lowmem_prunes的值将会增加，如果此值增加过快，可能是由于以下原因产生的：

1：如果存在大量的空闲块，则是因为碎片的存在而引起的；

2：空闲内存块较少，可以适当的增加缓存大小；

3.如何检查缓存命中率

为了提高缓存命中率：

1、数据库客户端的字符集跟服务器的字符集需要保持一致。在实际工作是，由于字符集没有保持一致，即使此时客户端没有出现乱码的情况，查询数据就是因为字符集不同的原因而没有被缓存起来；

2、客户端和服务器端采用一样的SQL语句；因为对于缓存来讲，大小写不同的SQL语句会被当做是不同的查询缓存；

3、查询缓存不会存储不确定结果的查询，任何一个包含不确定函数的查询不会被缓存。

实际上，查询缓存不会缓存引用了用户自定义函数、存储函数、用户自定义变量、临时表的查询；

4、查询缓存只是发生在服务器第一次接收到SQL查询语句，然后把查询结果缓存起来，对于查询中的子查询、视图查询和存储过程的查询都不能缓存结果，对于预存储语句同样也不能使用缓存；

可以通过分区表提高缓存的命中率；

 

 

4.优化查询缓存

优化查询缓存通常需要注意以下几点：

（1）在数据库设计的时候，尽量不要使用一张比较大的表，可以使用很多小的表，这样可以提高数据查询缓存的效率；

（2）在对数据库进行操作的时候，尽量一次性写入。因为逐个写入操作，每次写操作都会让数据库缓存功能失效或者清理缓存数据，此时服务器可能会挂起相当长的时间。

（3）尽量不要再数据库或者表的基础上控制查询缓存，可以采用SQL_CACHE和SQL_NO_CACHE来决定是否使用缓存查询；

（4）可以基于某个连接来运行或者禁止缓存，可以通过用适当的值设定query_cache_size来开启或者关闭对某个连接的缓存；

（5）对于包含很多写入任务的应用程序，关闭查询缓存功能可以改进服务器性能。

（6）禁用查询缓存功能的时候可以将query_cache_size参数设置为0，这样就会不消耗任何内存；

（7）如果想少数查询使用缓存，而多数查询都不使用查询缓存，此时可以将全局变量query_cache_type设置为DEMAND，然后可以在想使用缓存功能的语句的后面加上SQL_CACHE，不想使用缓存查询的语句后面可以加上SQL_NO_CACHE，这样可以通过语句来控制查询缓存，提高缓存的使用效率；

2.合并表和分区表

分区表是MySQL 5.1的新特性，而合并表已经有很长的历史了，合并表和分区表的概念比较相似，合并表是将许多个MyISAM表合并成一个续表，类似于使用UNION语句将多个表合并，合并表不是真的创造一张表，它就像是一个用户放置相似表的容器。而分区表则通过一些特殊的语句，创建独立的空间，事实上创建分区表的每个分区都是有索引的独立表。分区看上去像一个单独的表，MySQL在对分区表和合并表的实现上有很多的共同之处；

1.合并表

步骤1：创建数据引擎是MyISAM类型的表mtable1和mtable2；

mysql > create table mtable1(

                            data int not null primary key

)engine=myisam;

mysql > create table mtable2(

                   data int not null primary key

)engine=myisam;

mysql > insert into mtable1 values(1),(2),(3);

mysql > insert into mtable2 values(2),(3),(4);

使用UNION语句创建表mtable1和mtable2的合并表mergtable,命令如下：

mysql > create table mergtable(

                   data int not null primary key

)engine=merge union=(mtable1,mtable2) insert_method=last;

insert_method=last的含义是，如果向表mergtable中插入一条记录，那么就将这条记录插入到合并表的最后一个表里面；

Linux环境下：如果使用drop语句将mtable1和mtable2表删除，再查询mergtable，会发生错误；

Windows环境下：MySQL数据库做同样的操作，没有发生错误。

 

【注意事项】

MySQL合并表的实现对性能有一定的影响，

合并表看上去是一张表，事实上是逐个打开各个子表，这样的情况下，可能会因为缓存过多而导致超过MySQL缓存的最大设置；

创建合并表的CREATE语句不会检查子表是否兼容，如果创建了一个有效的合并表之后对某个表进行了修改，那么合并表也会发生错误；

2.分区表

从MySQL 5.1 版本开始支持数据表分区，通俗来讲，表分区就是将一张大表，根据条件分割成若干的小表。

数据库分区是一种物理数据库设计技术，分区的主要目的是为了让某些特定的查询操作减少响应时间，同时对于应用来讲分区是完全透明的。MySQL的分区主要有两种形式：水平分区（Horizontal Partitioning）和垂直分区（Vertical Partitioning）；

【水平分区】

根据表的行进行分隔，这种分区方式一定是通过表的某个属性作为分隔的条件；

【垂直分区】

是通过对表的垂直划分来减少目标表的宽度，是某些特定的列被划分到特定的分区；

 

查看是否支持分区的命令：

mysql > show variables like ‘%partition%’;

MySQL各种分区常用的操作案例

【1、RANGE分区】

RANGE 分区使用values less than操作符来进行定义，把连续并且不相互重叠的字段分配给分区；

mysql> create table emp(

    -> empno varchar(20) not null,

    -> empname varchar(20),

    -> deptno int,

    -> birthdate date,

    -> salary int

    -> )

    -> partition by range(salary)

    -> (

    -> partition p1 values less than(1000),

    -> partition p2 values less than(2000),

    -> partition p3 values less than(3000)

-> );

 

mysql > insert into emp values(1000,’kobe’,12,’1888-08-08’,1500);

 

partition p3 values less than maxvalue

maxvalue表示最大的可能的整数值。值得注意的是values less than 子句中也可以使用一个表达式，不过表达式的结果不能为null；

例如：

-> partition by range(year(birthdate))

    -> (

    -> partition p0 values less than(1980),

    -> partition p2 values less than(1990),

-> partition p3 values less than(2000),

-> partition p4 values less than maxvalue

-> );

 

RANGE分区很有用，常常使用在以下集中情况：

（1）如果想要删除某个时间段的数据，只需要删除分区即可。例如，想要删除1980年以前出生员工的所有信息，此时会执行“alter table emp drop partition p0”的效率要比执行“delete from emp where year(birthdate) <= 1980”高效的多；

（2）如果使用包含日期或者时间的列可以考虑用到RANGE分区；

（3）经常运行直接依赖于分隔表的列的查询。比如，当执行某个查询，如“select count (*) from emp where year(birthdate) = 1999 group by empno”，此时MySQL数据库可以很快速的确定只有分区p2需要扫描，这是因为查询条件对于其他分区不符合；

 

 

【2、LIST分区】

LIST分区类似RANGE分区，它们的区别主要在于每个分区的定义和选择是基于某列的值从属于一个集合，而RANGE分区是从属于一个连续区间值的集合。创建LIST分区的命令如下：
mysql > create table employees(

                   empname varchar(20),

                   deptno int ,

                   birthdate date not null,

                   salary int

)

partition by list(deptno)

(

         partition p1 values in(10,20),

         partition p2 valuse in(30),

         partition p3 valuse in(40)

);

以上代码表示，10号部分和20号部门的员工信息存储在p1分区，30号部门员工存储在p2分区，同GANGE分区一样，如果插入数据的部门编号不在分区值列表中的时候，那么insert插入操作将失败并报错；

 

【3、HASH分区】

         HASH分区是基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数的任何表达式；

         HASH分区主要是用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分隔成的分区数量；

 

mysql > create table htable(

         id int,

         name varchar(20),

         birthdate date not null,

         salary int

)

partition by hash(year(birthdate))

partitions 4;

说明：当使用了PARTITION BY HASH 的时候，MySQL将基于用户函数结果的模数来确定使用哪个编号的分区。将要保存记录的分区编号为N = MOD(表达式，num)。如果表htable中插入一条birthdate为“2010-09-23”的记录，可以通过

mod(year(2010-09-23),4)

 = mode(2010,4)

 = 2

此时，该条记录的数据将会存储在分区编号为2的分区空间；

【4、线性HASH分区】

http://www.bug315.com/article/19.htm

http://www.cnblogs.com/chenmh/p/5644496.html

mysql水平分表和垂直分表和数据库分区:

http://blog.csdn.net/java_mr_zheng/article/details/49886965

mysql的水平拆分和垂直拆分:

http://www.cnblogs.com/sns007/p/5790838.html

 

线性HASH分区和HASH分区的区别在于，线性哈希功能使用的一个线性的2的幂运算，而HASH分区使用的是哈希函数的模数；

mysql> create table lhtable(

         id int not null,

         name varchar(20),

         hired date not null default ‘1999-09-09’,

         deptno int

)

partition by linear hash(year(hired))

partitions 4;

说明：如果lhtable中插入一条hireddate为“2010-09-23”的记录，记录将要保存的分区是num个分区中的分区N，可以通过如下的方法计算N:：

步骤1：

找到下一个大于num的2的幂，把这个值称作V，可以通过下面的公式得到。V=POWR(2,CEILING(LOG(2,num)))，假设，num的值是13，那么LOG(2,13)就是3.700043，CEILING(3.70043)就是4，则V=POWER(2,4)，即等于16；

步骤2：计算N=F(column_list)&(V-1)此时当N>=num的时候，V=CEIL(V/2)，此时N=N&(V-1)

按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000GB）数据的表，它的缺点在于，与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡；

【5、KEY分区】

类似于HASH分区，区别在于KEY分区只支持计算一列或者多列，且MySQL服务器提供其自身的哈希函数，这些函数是基于PASSWORD()一样的运算法则；

mysql > create table keytable(

         id int ,

         name varchar(20) not null,

         deptno int,

         birthdate date not null,

         salary int

)

partition by key(birthdate)

partitions 4;

说明：在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂算法得到的，而不是通过模数算法；

【6、复合分区】

复合分区是分区表中每个分区的再次分割，子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为子分区；

复合分区需要注意以下的问题：

（1）如果一个分区中创建了复合分区，其他分区也要有复合分区；

（2）如果创建了复合分区，每个分区中的复合分区数必须相同；

（3）同一个分区内的复合分区，名字不相同，不同分区内的复合分区名字可以相同；

 

【创建RANGE-HASH】复合分区的命令如下：

mysql > create table rhtable(

         empno varchar(20) not null,

         empname varchar(20),

         depno int,

         birthdate date not null,

         salary int

)
partition by range(salary)

subpartition by hash(year(birthdate))

subpartitions 3

(

         partition p1 values less than(2000),

         partition p2 values less than maxvalue

);

【RANGE-KEY分区】

mysql > create table rktable(

         no varchar(20) not null,

         name varchar(20),

         depno int,

         birth date not null,

         salary int

)
partition by range(salary)

subpartition by key(year(birth))

subpartitions 3

(

         partition p1 values less than(2000),

         partition p2 values less than maxvalue

);

【LIST-HASH复合分区的命令】

mysql > create table lhtable(

         no varchar(20) not null,

         name varchar(20),

         depno int,

         birth date not null,

         salary int

)
partition by list(depno)

subpartition by hash(year(birth))

subpartitions 3

(

         partition p1 values in(10),

         partition p2 values in (20)

);

x

【LIST-KEY复合分区命令】

mysql > create table lktable(

         no varchar(20) not null,

         name varchar(20),

         depno int ,

         birthdate date not null,

         salary int

)

partition by list(depno)

subpartition by key(birthdate)

subpartitions 3

(

         partition p1 values in(10),

         partition p2 values in (20)

);

3. 事务控制

MySQL通过

SET AUTOCOMMIT ：用于设置是否自动提交事务

START TRANSACTION：设置开启事务；

COMMIT：提交事务；

ROLLBACK：回滚事务；

等语句控制本地事务；

默认情况下，MySQL事务是自动提交的，如果需要通过明确的COMMIT和ROLLBACK再提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理有明显不同的地方。如果应用从Oracle数据库迁移到MySQL数据库，则需要确保应用中是否对事务进行了明确的管理；

MySQL 的AUTOCOMMIT（自动提交）默认是开启，对MySQL的性能有一定的影响；

【关闭自动提交功能】

mysql>set @@autocommit=0;

【打开自动提交功能】

mysql>set @@autocommit=1;

【查看自动提交功能是否被关闭】

mysql > show variables like “autocommit”;

注意：如果在表的锁定期间，如果使用START TRANSACTION命令开启一个新的事务，会造成一个隐含的unlock tables 被执行，该操作存在一定的隐患。

4. MySQL分布式事务

在MySQL中，使用分布式事务的应用程序涉及一个或者多个资源管理器和一个事务管理器，分布式事务的事务参与者、资源管理器、事务管理器等位于不同的节点上，这些不同的节点相互协作共同完成一个具有逻辑完整性的事务。分布式事务的主要作用在于确保事务的一致性和完整性；

1.了解分布式事务的原理

MySQL 5.7 完美的分布式事务支持：

http://www.tuicool.com/articles/B3UZri2

Mysql数据库分布式事务XA详解:

http://blog.csdn.net/luckyjiuyi/article/details/46955337

资源管理器（Resource Manager，简称RM）用于向事务提供资源，同时还具有管理事务提交或者回滚的能力。数据库就是一种资源管理器；

事务管理器（Transaction Manager，简称TM）用于和每个资源管理器通信，协调并完成事务的处理。一个分布式事务中各个事务均是分布式事务的“分支事务”。分支事务和各个分支通过一种命名方法进行标识；

MySQL执行分布式事务，首先需要考虑到网络中涉及到的多个事务管理器，MySQL分布式事务管理，简单的讲就是同时管理若干管理器事务的一个过程，每个资源管理器的事务当执行到被提交或者被回滚的时候，根据每个资源管理器报告的有关情况决定是否将这些事务作为一个原子性的操作执行全部提交或者全部回滚。因为MySQL分布式事务同时涉及多台MySQL服务器，所以在管理分布式事务的时候，必须要考虑网络可能存在的故障；

用于执行分布式事务的过程使用两个阶段：

（1）第一阶段：所有的分支被预备。它们被事务管理器告知要准备提交，每个分支资源管理器记录分支的行动并指示任务的可行性；

（2）第二阶段：事务管理器告知资源管理器是否要提交或者回滚。

如果预备分支时，有的分支指示它们将能够提交，那么所有的分支被告知提交。如果有一个分支出错，那么就全部都要回滚。特殊情况下，只要一个分支的时候，第二阶段则被省略；

分布式事物的主要作用是在于确保事务的一致性和完整性。它利用分布式的计算机环境，将多个事务性的活动合并成一个事务单元，这些事务组合在一起构成原子操作，这些事务的活动要么一起执行并提交事务，要么回滚所有的操作，从而保证了多个活动之间的一致性和完整性；

2.分布式事务的语法

MySQL5.7对分布式事务的改进操作：

http://www.tuicool.com/articles/B3UZri2

在MySQL中，执行分布式事务的语法格式如下：

XA {START|BEGIN} xid [JOIN|RESUME]

XA START xid表示用于启动一个事务标识为xid的事务。xid分布式事务表示的值既可以由客户端提供也可以由MySQL服务器生成；

结束分布式事务的语法格式如下：

XA END xid [SUSPEND [FOR MIGRATE]]

其中xid包括：gtrid[,bqual[,formatID]]

gtrid是一个分布式事务标识符；

bqual表示一个分支限定符，默认值为空字符。对于一个分布式事务中的每个分支事务，bqual值必须是唯一的；

formatID：是一个数字，用于标识由gtrid和bqual值使用的格式，默认值为1；

【该命令使事务进入PREPARE状态，也就是两个阶段提交的第一个阶段】

XA PREPARE xid

【该命令用来提交具体的分支事务】

XA COMMIT xid [ONE PHASE]

【回滚具体的分支事务，也就是两阶段提交的第二个阶段】

【用于返回数据库中处于PREPARE状态的分支事务的详细信息】

XA PECOVER

分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题的时候如何解决故障。

分布式事务的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等；

MySQL分布式事务分为两类：内部分布式事务和外部分布式事务。内部分布式事务用于统一实例下跨多个数据引擎的事务，由二进制日志作为协调者；而外部分布式事务用于跨多个MySQL实例的分布式事务，需要应用层介入作为协调者，全局提交还是回滚，都是由应用层决定的，对应用层的要求比较高；

MySQL分布式事务在某些特殊的情况下会存在一定的漏洞，当一个事务分支在PREPARE状态的时候失去了连接，在服务器重启之后，可以继续对分支事务进行提交或者回滚操作，没有写入二进制日志，这将导致事务部分丢失或者主从数据库不一致；