网志栈编辑推荐
将*实用的MySQL技术融入到每个案例中,教你快速成为MySQL数据库*高手 ；
内容简介
本书针对 MySQL中高级用户，详细讲解 MySQL高级使用技术。书中详解了每一个知识点以及数据库操作的方法和技巧。本书注重实战操作，帮助读者循序渐进地掌握 MySQL中的各项高级技术。 ；
作者简介
暂无
目录
目 ； 录
第1章 ；MySQL架构介绍........... 1
1.1 ； MySQL架构... 1
1.1.1
； MySQL物理文件的组成........... 2
1.1.2
； MySQL各逻辑块简介................ 4
1.1.3
； MySQL各逻辑块协调工作....... 6
1.2 ； MySQL存储引擎概述....... 7
1.3 ； MySQL各种存储引擎的特性.............. 10
1.3.1
； MyISAM............... 10
1.3.2
； InnoDB................... 12
1.3.3
； MEMORY.............. 15
1.3.4
； MERGE................... 18
1.3.5
； BerkeleyDB存储引擎............... 20
1.4 ； MySQL工具. 21
1.4.1
； MySQL命令行实用程序......... 21
1.4.2
； MySQL Workbench................... 33
1.5 ； 本章小结....... 34
第2章 ；MySQL权限与安全..... 35
2.1 ； 权 限 表....... 35
2.1.1
； user表.................... 35
2.1.2
； db表和host表.... 37
2.1.3
； tables_priv表和columns_priv表................ 39
2.1.4
； p
rocs_priv表........ 40
2.2 ； 账户管理....... 41
2.2.1
； 登录和退出MySQL服务器.... 41
2.2.2
； 新建普通用户....... 43
2.2.3
； 删除普通用户....... 47
2.2.4
； root用户修改自己的密码....... 48
2.2.5
； root用户修改普通用户密码... 50
2.2.6
； 普通用户修改密码.................... 51
2.2.7
； root用户密码丢失的解决办法..................... 51
2.3 ； 权限管理....... 53
2.3.1
； MySQL的各种权限.................. 53
2.3.2
； 授权.. 55
2.3.3
； 收回权限............... 57
2.3.4
； 查看权限............... 58
2.4 ； 访问控制....... 59
2.4.1
； 连接核实阶段....... 59
2.4.2
； 请求核实阶段....... 60
2.5 ； MySQL的安全问题.......... 61
2.5.1
； 操作系统相关的安全问题....... 61
2.5.2
； 数据库相关的安全问题........... 62
2.6 ； 使用SSL安全连接........... 71
2.7 ； 综合管理用户权限............ 77
2.8 ； 小结................ 80
第3章 ；数据备份与还原.......... 81
3.1 ； 数据备份....... 81
3.1.1
； 使用mysqldump命令备份...... 81
3.1.2
； 直接复制整个数据库目录....... 88
3.1.3
； 使用mysqlhotcopy工具快速备份.............. 88
3.2 ； 数据还原....... 89
3.2.1
； 使用MySQL命令还原............. 89
3.2.2
； 直接复制到数据库目录........... 90
3.2.3
； mysqlhotcopy快速恢复........... 90
3.3 ； 数据库迁移... 90
3.3.1
； 相同版本的MySQL数据库之间的迁移.... 91
3.3.2
； 不同版本的MySQL数据库之间的迁移.... 91
3.3.3
； 不同数据库之间的迁移........... 92
3.4 ； 表的导出和导入................ 92
3.4.1
； 使用SELECT…INTO OUTFILE导出文本文件............... 92
3.4.2
； 用mysqldump命令导出文本文件............... 95
3.4.3
； 用MySQL命令导出文本文件 ；98
3.4.4
； 使用LOAD DATA INFILE方式导入文本文件............. 101
3.4.5
； 使用mysqlimport命令导入文本文件....... 103
3.5 ； 综合实例——数据的备份与恢复...... 105
3.6 ； 小结.............. 109
第4章 ；MySQL的高级特性... 110
4.1 ； MySQL 查询缓存........... 110
4.1.1
； 认识查询缓存..... 110
4.1.2
； 监控和维护查询缓存.............. 115
4.1.3
； 如何检查缓存命中率.............. 117
4.1.4
； 优化查询缓存..... 118
4.2 ； 合并表和分区表.............. 119
4.2.1
； 合并表.................. 119
4.2.2
； 分区表.................. 121
4.3 ； 事务控制..... 131
4.4 ； MySQL分布式事务........ 135
4.4.1
； 了解分布式事务的原理......... 135
4.4.2
； 分布式事务的语法.................. 136
4.5 ； 小结.............. 137
第5章 ；MySQL锁定机制....... 138
5.1 ； MySQL锁定机制概述... 138
5.2 ； MyISAM表级锁............. 143
5.2.1
； MyISAM表级锁的锁模式.... 143
5.2.2
； 获取MyISAM表级锁的争用情况............ 145
5.2.3
； MyISAM表级锁加锁方法.... 146
5.2.4
； MyISAM Concurrent Insert的特性........... 148
5.2.5
； MyISAM表锁优化建议........ 150
5.3 ； InnoDB行级锁................ 150
5.3.1
； InnoDB行级锁模式................ 150
5.3.2
； 获取InnoDB行级锁的争用情况............... 155
5.3.3
； InnoDB行级锁的实现方法... 157
5.3.4
； 间隙锁（Net-Key锁）........... 162
5.3.5
； InnoDB 在不同隔离级别下加锁的差异.. 163
5.3.6
； InnoDB 存储引擎中的死锁.. 164
5.3.7
； InnoDB行级锁优化建议....... 166
5.4 小结.............. 167
第6章 使用MySQL Workbench 管理数据库.................... 168
6.1 MySQL Workbench简介..................... 168
6.1.1
MySQL Workbench 的概述.. 168
6.1.2
MySQL Workbench 的优势.. 169
6.1.3
MySQL Workbench 的安装.. 169
6.2 SQL Development的基本操作........... 171
6.2.1
创建数据库连接 171
6.2.2
创建新的数据库 173
6.2.3
创建和删除新的数据表......... 174
6.2.4
添加、修改表记录.................. 177
6.2.5
查询表记录......... 178
6.2.6
修改表结构......... 178
6.3 Data Modeling的基本操作................. 179
6.3.1
建立ER模型...... 179
6.3.2
导入ER模型...... 184
6.4 Server Administration的基本操作..... 185
6.4.1
管理MySQL用户................... 186
6.4.2
备份MySQL数据库............... 188
6.4.3
还原MySQL数据库............... 191
6.5 小结.............. 192
第7章 SQL性能优化........... 193
7.1 优化简介..... 193
7.2 MySQL Query Optimizer概述............. 194
7.3 SQL 语句优化的基本思路.................. 194
7.4 利用EXPLAIN分析查询语句............ 196
7.4.1
EXPLAIN语句的基本语法.... 196
7.4.2
EXPLAIN语句分析实例........ 208
7.5 利用Profiling分析查询语句............... 212
7.6 合理地使用索引.............. 216
7.6.1
索引对查询速度的影响......... 216
7.6.2
如何使用索引查询.................. 217
7.7 不同类型SQL语句优化方法............. 220
7.7.1
优化INSERT语句................... 220
7.7.2
优化ORDER BY语句............. 221
7.7.3
优化GROUP BY语句............. 222
7.7.4
优化嵌套查询..... 223
7.7.5
优化OR条件...... 224
7.7.6
优化插入记录的速度.............. 226
7.8 优化数据库结构.............. 228
7.8.1
将字段很多的表分解成多个表.................. 228
7.8.2
增加中间表......... 230
7.8.3
增加冗余字段..... 231
7.9 分析表、检查表和优化表................... 232
7.9.1
分析表.................. 232
7.9.2
检查表.................. 233
7.9.3
优化表.................. 233
7.10 小结............ 234
第8章 MySQL服务器性能优化.................. 235
8.1 MySQL源码安装的性能优化............ 235
8.2 MySQL服务器配置优化..................... 238
8.2.1
查看性能参数的方法.............. 238
8.2.2
key_buffer_size的设置.......... 243
8.2.3
table_cache的设置................. 246
8.2.4
内存参数的设置 248
8.2.5
日志和事务参数的设置......... 252
8.2.6
存储和I/O相关参数的设置.. 253
8.2.7
其他重要参数的设置.............. 254
8.3 MySQL日志设置优化... 256
8.4 MySQL I/O设置优化..... 257
8.5 MySQL并发设置优化... 259
8.6 线程、Table Cache和临时表的优化 261
8.6.1
线程的优化......... 261
8.6.2
关于table_cache相关的优化..................... 262
8.6.3
关于临时表的优化.................. 263
8.7 小结.............. 264
第9章 MySQL 性能监控...... 265
9.1 基本监控系统方法.......... 265
9.1.1 p
s命令................. 265
9.1.2
top命令............... 266
9.1.3
vmstat命令......... 268
9.1.4
mytop命令.......... 269
9.1.5
sysstat工具........ 272
9.2 开源监控利器Nagios实战................. 277
9.2.1
安装Nagios之前的准备工作..................... 277
9.2.2
安装Nagios主程序................ 279
9.2.3
整合Nagios到Apache服务 280
9.2.4
安装Nagios插件包................ 284
9.2.5
监控服务器的CPU、负载、磁盘I/O使用情况........... 286
9.2.6
配置Nagios监控MySQL服务器.............. 291
9.3 MySQL监控利器Cacti实战.............. 293
9.3.1
Cacti工具的安装..................... 294
9.3.2
Cacti监控MySQL服务器..... 299
9.4 小结.............. 304
第10章 MySQL Replication. 305
10.1 MySQL Replication概述.................... 305
10.2 Windows环境下的MySQL主从复制................. 306
10.2.1
复制前的准备工作................ 306
10.2.2
Windows环境下实现主从复制............... 306
10.2.3
Windows环境下主从复制测试............... 314
10.3 Linux环境下的MySQL复制............ 315
10.3.1
下载并安装MySQL 5.6........ 315
10.3.2
单机主从复制前的准备工作..................... 316
10.3.3
mysqld_multi实现单机主从复制............. 320
10.3.4
不同服务器之间实现主从复制................ 328
10.3.5
MySQL 主要复制启动选项 329
10.3.6
指定复制的数据库或者表... 330
10.4 查看Slave的复制进度 338
10.5 日常管理和维护............ 339
10.5.1
了解服务器的状态................ 339
10.5.2
服务器复制出错的原因....... 340
10.6 切换主从服务器............ 343
10.7 小结............ 347
第11章 MySQL Cluster实战 348
11.1 MySQL Cluster概述..... 348
11.1.1
MySQL Cluster基本概念..... 348
11.1.2
理解MySQL Cluster节点.... 349
11.2 Linux环境下MySQL Cluster安装和配置........... 350
11.2.1
安装MySQL Cluster 7.2.8软件................. 352
11.2.2
管理节点配置步骤................ 357
11.2.3
配置SQL节点和数据节点.. 358
11.3 管理MySQL Cluster..... 358
11.3.1
Cluster的启动.. 358
11.3.2
Cluster的测试.. 360
11.3.3
Cluster的关闭.. 363
11.4 维护MySQL Cluster..... 363
11.4.1
Cluster的日志的管理........... 366
11.4.2
Cluster的联机备份............... 367
11.4.3
Cluster的数据恢复............... 368
11.5 Windows操作系统中配置Cluster.. 369
11.6 小结............ 374
第12章 企业中MySQL的高可用架构........ 375
12.1 MySQL高可用的简单介绍............... 375
12.2 MySQL主从复制.......... 375
12.2.1
MySQL主从架构设计.......... 376
12.2.2
配置环境........... 376
12.2.3
服务器的安装配置................ 376
12.2.4
LVS的安装配置..................... 379
12.3 MySQL DRBD HA...... 381
12.3.1
什么是DRBD... 381
12.3.2
MySQL DRBD HA架构设计.................. 382
12.3.3
配置环境........... 382
12.3.4
安装配置Heartbeat............... 383
12.3.5
安装配置DRBD..................... 385
12.4 Lvs Keepalived MySQL 单点写入主主同步方案.................. 388
12.4.1
配置环境........... 388
12.4.2
Lvs Keepalived的安装........ 393
12.4.3
Lvs Keepalived的配置........ 394
12.4.4
Master和Backup的启动.... 397
12.5 MMM高可用MySQL方案.............. 397
12.5.1
MMM的架构... 398
12.5.2
配置环境........... 398
12.5.3
MMM的安装... 402
12.5.4
Monitor服务器的配置......... 402
12.5.5
各个数据库服务器的配置... 404
12.5.6
MMM的管理... 404
12.6 小结 405媒体评论
评论
前沿
前 言
本书是面向MySQL数据库管理系统读者的一本高质量的书籍。目前国内MySQL需求旺盛，各大知名企业高薪招聘技术能力强的MySQL开发人员和管理人员。本书根据这样的需求，针对已经有MySQL基础的读者，注重内容实战，通过实例的操作与分析，引领读者快速学习和掌握MySQL开发和管理的高级技术。
本书内容
第1章主要介绍MySQL架构、各种MySQL存储引擎的特性。
第2章介绍MySQL权限与安全。包括权限表、账户管理、权限管理、访问控制、MySQL的安全问题和使用SSL安全连接。
第3章介绍数据库的备份还原。包括各种备份方法、各种还原方法、数据库迁移的方法、表的导入和导出。
第4章介绍MySQL的高级特性。包括MySQL查询缓存、合并表和分区表、事务控制和MySQL分布式事务。
第5章介绍MySQL锁定机制。包括MySQL锁定机制的概述、MyISAM表级锁和InnoDB行级锁。
第6章介绍使用MySQL Workbench管理数据库。包括MySQL Workbench简介、SQLDevelopment的基本操作、Data Modeling的基本操作、ServerAdministration的基本操作。
第7章介绍SQL性能优化。包括优化简介、MySQLQuery Optimizer概述、SQL语句优化的基本思路、利用EXPLAIN分析查询语句、利用Profiling分析查询语句、合理地使用索引、不同类型SQL语句优化方法、优化数据库结构、分析表、检查表和优化表。
第8章介绍MySQL服务器性能优化。包括MySQL源码安装的性能优化、MySQL服务器配置优化、MySQL日志设置优化、MySQL I/O设置优化、MySQL并发设置优化、线程、Table Cache和临时表的优化。
第9章介绍MySQL 性能监控。包括基本监控系统方法、开源监控利器Nagios实战、MySQL监控利器Cacti实战。
第10章介绍MySQL Replication。包括MySQL Replication概述、Windows环境下的MySQL主从复制、Linux环境下的MySQL复制、查看Slave的复制进度、日常管理和维护、切换主从服务器。
第11章介绍MySQL Cluster实战。包括MySQL Cluster概述、Linux环境下MySQLCluster安装和配置、管理MySQL Cluster、维护MySQL Cluster、Windows操作系统中配置Cluster。
第12章介绍企业中MySQL的高可用架构。包括MySQL高可用的简单介绍、MySQL主从复制、MySQL DRBD HA、Lvs Keepalived MySQL单点写入主主同步方案、MMM高可用MySQL方案。
本书特色
内容全面，案例丰富：知识点由浅入深，涵盖了所有MySQL的实用知识点，由浅入深地掌握MySQL数据库管理技术。把知识点融汇于系统的案例实训当中，并且结合综合案例进行讲解和拓展。进而达到“知其然，并知其所以然”的效果。
图文并茂，易学易用：注重操作，图文并茂，在介绍案例的过程中，每一个操作均有对应步骤和过程说明。这种图文结合的方式使读者在学习过程中能够直观、清晰地看到操作的过程以及效果，便于读者更快地理解和掌握。
提示技巧，源码下载：本书对读者在学习过程中可能会遇到的疑难问题以“提示”和“技巧”的形式进行了说明，以免读者在学习的过程中走弯路。为了方便读者学习，本书源码提供下载。
读者对象
本书是一本全面介绍MySQL数据库高级技术的教程，内容丰富、条理清晰、实用性强，适合以下读者学习使用：
有一定基础的MySQL数据库学习者。
希望全面、深入掌握MySQL的开发人员。
MySQL数据库管理员。
高等院校和培训学校相关专业的师生。
致谢
参与本书创作的作者除了封面署名人员以外，还有刘玉萍、刘增杰、胡同夫、王英英、肖品、孙若淞、王攀登、王维维、梁云亮、刘海松、陈伟光、包惠利等人参与了创作。虽然倾注了作者的努力，但由于水平有限、时间仓促，书中难免有错漏之处，请读者谅解，如果遇到问题或有意见和意见，敬请与我们联系，我们将全力提供帮助。
本书技术支持QQ群：221376441。
源码下载
本书使用的源码下载地址如下：
http://pan.baidu.com/s/1ntu60IH
如果下载有问题，请联系电子邮箱
booksaga@163.com
著者
2015年11月
前 言
本书是面向MySQL数据库管理系统读者的一本高质量的书籍。目前国内MySQL需求旺盛，各大知名企业高薪招聘技术能力强的MySQL开发人员和管理人员。本书根据这样的需求，针对已经有MySQL基础的读者，注重内容实战，通过实例的操作与分析，引领读者快速学习和掌握MySQL开发和管理的高级技术。
本书内容
第1章主要介绍MySQL架构、各种MySQL存储引擎的特性。
第2章介绍MySQL权限与安全。包括权限表、账户管理、权限管理、访问控制、MySQL的安全问题和使用SSL安全连接。
第3章介绍数据库的备份还原。包括各种备份方法、各种还原方法、数据库迁移的方法、表的导入和导出。
第4章介绍MySQL的高级特性。包括MySQL查询缓存、合并表和分区表、事务控制和MySQL分布式事务。
第5章介绍MySQL锁定机制。包括MySQL锁定机制的概述、MyISAM表级锁和InnoDB行级锁。
第6章介绍使用MySQL Workbench管理数据库。包括MySQL Workbench简介、SQLDevelopment的基本操作、Data Modeling的基本操作、ServerAdministration的基本操作。
第7章介绍SQL性能优化。包括优化简介、MySQLQuery Optimizer概述、SQL语句优化的基本思路、利用EXPLAIN分析查询语句、利用Profiling分析查询语句、合理地使用索引、不同类型SQL语句优化方法、优化数据库结构、分析表、检查表和优化表。
第8章介绍MySQL服务器性能优化。包括MySQL源码安装的性能优化、MySQL服务器配置优化、MySQL日志设置优化、MySQL I/O设置优化、MySQL并发设置优化、线程、Table Cache和临时表的优化。
第9章介绍MySQL 性能监控。包括基本监控系统方法、开源监控利器Nagios实战、MySQL监控利器Cacti实战。
第10章介绍MySQL Replication。包括MySQL Replication概述、Windows环境下的MySQL主从复制、Linux环境下的MySQL复制、查看Slave的复制进度、日常管理和维护、切换主从服务器。
第11章介绍MySQL Cluster实战。包括MySQL Cluster概述、Linux环境下MySQLCluster安装和配置、管理MySQL Cluster、维护MySQL Cluster、Windows操作系统中配置Cluster。
第12章介绍企业中MySQL的高可用架构。包括MySQL高可用的简单介绍、MySQL主从复制、MySQL DRBD HA、Lvs Keepalived MySQL单点写入主主同步方案、MMM高可用MySQL方案。
本书特色
内容全面，案例丰富：知识点由浅入深，涵盖了所有MySQL的实用知识点，由浅入深地掌握MySQL数据库管理技术。把知识点融汇于系统的案例实训当中，并且结合综合案例进行讲解和拓展。进而达到“知其然，并知其所以然”的效果。
图文并茂，易学易用：注重操作，图文并茂，在介绍案例的过程中，每一个操作均有对应步骤和过程说明。这种图文结合的方式使读者在学习过程中能够直观、清晰地看到操作的过程以及效果，便于读者更快地理解和掌握。
提示技巧，源码下载：本书对读者在学习过程中可能会遇到的疑难问题以“提示”和“技巧”的形式进行了说明，以免读者在学习的过程中走弯路。为了方便读者学习，本书源码提供下载。
读者对象
本书是一本全面介绍MySQL数据库高级技术的教程，内容丰富、条理清晰、实用性强，适合以下读者学习使用：
有一定基础的MySQL数据库学习者。
希望全面、深入掌握MySQL的开发人员。
MySQL数据库管理员。
高等院校和培训学校相关专业的师生。
致谢
参与本书创作的作者除了封面署名人员以外，还有刘玉萍、刘增杰、胡同夫、王英英、肖品、孙若淞、王攀登、王维维、梁云亮、刘海松、陈伟光、包惠利等人参与了创作。虽然倾注了作者的努力，但由于水平有限、时间仓促，书中难免有错漏之处，请读者谅解，如果遇到问题或有意见和意见，敬请与我们联系，我们将全力提供帮助。
本书技术支持QQ群：221376441。
源码下载
本书使用的源码下载地址如下：
http://pan.baidu.com/s/1ntu60IH
如果下载有问题，请联系电子邮箱
booksaga@163.com
著者
2015年11月
免费在线读
第 4 章 MySQL的高级特性
本章主要讲解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)
如果需要将该参数永久修改，需要修改/etc/my.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)
如果需要将该参数永久修改，需要修改/etc/my.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 values(2);
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 querycache;
Query OK, 0 rowsaffected (0.00 sec)
（2）reset query cache：该命令用于移除查询缓存中所有的查询结果。命令运行如下：
mysql> resetquery 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 |
查询缓存中的块的总数目 |
如果空闲内存块是总内存块的一半左右，则表示存在严重的内存碎片。通常使用flushquery cache命令整理碎片，然后采用reset query cache命令清理查询缓存。
如果碎片很少，但是缓存命中率很低，则说明设置的缓存内存空间过小，服务器频繁删除旧的查询缓存，腾出空间，以保存新的查询缓存，此时，参数Qcache_lowmeme_preunes状态值将会增加，如果此值增加过快，可能是有以下原因造成：
l 如果存在大量空闲块，则是因为碎片的存在而引起的。
l 空闲内存块较少，可以适当地增加缓存大小。
4.1.3
MySQL检查缓存命中率的方式十分简单快捷。缓存就是一个查找表（LookupTable）。查找的键就是查询文本﹑当前数据库﹑客户端协议的版本，以及其他少数会影响实际查询结果的因素的哈希值。
下面主要学习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所示。
图4-1 MySQL优化查询缓存的大致步骤
优化查询缓存通常需要注意如下几点。
（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，这样可以通过语句来控制查询缓存，提高缓存的使用率。
4.2 合并表和分区表
分区表是MySQL 5.1的新特性，而合并表已经有很长的历史了，合并表和分区表的概念比较相似，合并表是将许多个MyISAM表合并成一个续表，类似于使用UNION语句将多个表合并，合并表不是真的创造一个真正的表，它就像一个用于放置相似表的容器。而分区表则通过一些特殊的语句，创建独立的空间，事实上创建分区表的每个分区都是有索引的独立表。分区看上去像一个单独的表，MySQL在对分区表和合并表的实现上有很多共通之处。
4.2.1
MySQL的合并表可以把多个结果相同的表合并成为一个续表，事实上是容纳真正表的容器，可以使用UNION语句来创建表，下面是一个合并表的例子。
创建数据存储引擎是MyISAM类型的表mtable1和mtable2，命令如下：
mysql> create tablemtable1(
-> data int not null primary key
-> )engine=myisam;
Query OK, 0 rowsaffected (0.03 sec)
mysql> create tablemtable2(
-> data int not null primary key
-> )engine=myisam;
Query OK, 0 rowsaffected (0.02 sec)
向表mtable1和mtable2中插入数据，命令如下：
mysql> insert intomtable1 values(1),(2),(3);
Query OK, 3 rowsaffected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert intomtable2 values(2),(3),(4);
Query OK, 3 rowsaffected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
使用UNION语句创建表mtable1和mtable2的合并表mergtable，命令如下：
mysql> create tablemergtable(
-> data int not null primary key
-> )engine=merge union=(mtable1,mtable2)insert_method=last;
Query OK, 0 rowsaffected (0.03 sec)
insert_method=last的含义是，如果向表mergtable中插入一条记录，那么就将这条记录插入到合并表所合并的最后一个表里面，以上例子就是将记录插入到mtable2表中。
查询合并表mergtable的信息，命令如下：
mysql> select *frommergtable;
------
| data |
------
| 1 |
| 2 |
| 2 |
| 3 |
| 3 |
| 4 |
------
6 rows in set (0.00sec)
值得注意的是合并表所包含的表列的数量和类型跟所合并的表的列的数量和类型都是一样的。同时也可以看到每个表的列有主键，这会导致合并表有重复的行，这是合并表的一个局限。
直接插入数据到mergtable中，命令如下：
mysql> insert intomergtable values(5);
Query OK, 1 rowaffected (0.00 sec)
查询mtable1表中的数据是否发生变化，命令如下：
mysql> select *from mtable1;
------
| data |
------
| 1 |
| 2 |
| 3 |
------
3 rows in set (0.00sec)
从结果可以看出，数据没有发生变化。
查询mtable2表中的数据是否发生变化，命令如下：
mysql> select *from mtable2;
------
| data |
------
| 2 |
| 3 |
| 4 |
| 5 |
------
4 rows in set (0.00sec)
从结果可以看出，插入到合并表mergtable的一条数据记录已经插入到mtable2表中了。
删除表mtable1和mtabl2，命令如下：
mysql> drop tablemtable1,mtable2;
Query OK, 0 rowsaffected (0.00 sec)
在linux环境查询mergtable，发生错误，命令如下：
mysql> select * frommergtable;
ERROR 1146(42502):Table‘test.mtable
1’
值得注意的是，在Windows环境下的MySQL数据库做同样的操作，没有发生错误。
MySQL合并表的实现对性能有一定的影响，下面是一些需要注意的事项：
l 合并表看上去是一个表，事实上是逐个打开各个子表，这样的情况下，可能会因为缓存过多的表而导致超过MySQL缓存的最大设置。
l 创建合并表的CREATE语句不会检查子表是否兼容，如果创建了一个有效的合并表后对某个表进行了修改，那么合并表也会发生错误。
4.2.2
从MySQL 5.1版本开始支持数据表分区，通俗地讲，表分区是将一张大表，根据条件分割成若干小表。例如，某用户表的记录超过了600万条，那么就可以根据入库日期或者所在地将表分区。
笔者的数据库版本是5.0.89，此时需要安装MySQL 5.1以上版本，这里以安装mysql-
5.6.10
将mysql-
5.6.10
[root@localhost tools]# gunzip mysql-
5.6.10
[root@localhost tools]# tar -xvf mysql-
5.6.10
开始安装MySQL程序。
[root@localhost mysql-
5.6.10
修改/etc/mysql.cnf中数据库配置选项，具体设置如下：
###########################################################################
# The MySQL server
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
#user = root
#password = root
[mysqld1]
port = 3306
socket = /tmp/mysql.sock
pid-file = /usr/local/var/mysql1/mysql1.pid
datadir = /usr/local/var/mysql1
skip-locking
key_buffer =
16M
max_allowed_packet =
1M
query_cache_type = on
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
log-bin =/usr/local/var/mysql1/mysql-bin.log
server-id = 1
[mysqld2]
port = 3307
socket = /tmp/mysql2.sock
pid-file = /usr/local/var/mysql2/mysql2.pid
datadir = /usr/local/var/mysql2
skip-locking
key_buffer =
16M
max_allowed_packet =
1M
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 = 2
#replicate-do-table=test.rep_t1
#replicate-ignore-table=test.rep_t2
#replicate-do-db=test
#replicate-do-db=cc
#replicate-ignore-db=tt
log-bin =/usr/local/var/mysql2/mysql-bin.log
[mysqld3]
port = 3308
socket = /tmp/mysql3.sock
pid-file = /usr/local/var/mysql3/mysql3.pid
datadir = /usr/local/var/mysql3
skip-locking
key_buffer =
16M
max_allowed_packet =
1M
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 = 3
#ssl
#ssl-ca=/etc/myql/newcerts/ca-cert.pem
#ssl-cert=/etc/mysql/newcerts/server-cert.pem
#ssl-key=/etc/mysql/newcerts/server-key.pem
###########################################################################
[mysqld]
port = 3309
socket = /tmp/mysql.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 MySQLmonitor. Commands end with ; or g.
Your MySQL connectionid is 1
Server version:
5.6.10
Type help; or \hfor help. Type \c to clear the current input statement.
mysql>
数据库分区是一种物理数据库设计技术，分区的主要目的是为了让某些特定的查询操作减少响应时间，同时对于应用来讲分区完全是透明的。MySQL的分区主要有两种形式：水平分区（Horizontal Partitioning）和垂直分区（Vertical Partitioning）。
水平分区（Horizontal Partitioning）是根据表的行进行分割，这种形式的分区一定是通过表的某个属性作为分割的条件，例如，某张表里面数据日期为2011年的数据和日期为2012年的数据分割开，就可以采用这种分区形式。
垂直分区（Vertical Partitioning）是通过对表的垂直划分来减少目标表的宽度，是某些特定的列被划分到特定的分区。
通常可以通过下面命令查看是否支持分区，命令如下：
mysql> showvariables like \%partition%;
------------------- -------
| Variable_name | Value |
------------------- -------
| have_partitioning |YES |
------------------- -------
1 row in set (0.01sec)
下面介绍MySQL各种分区表常用的操作案例。
1. RANGE分区
RANGE分区使用valuesless than 操作符来进行定义，把连续且不相互重叠的字段分配给分区，命令如下。
mysql> create tableemp(
-> 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)
-> );
Query OK, 0 rowsaffected (0.01 sec)
mysql> insert intoemp values(1000,
kobe
1888-08-08
Query OK, 1 rowaffected (0.01 sec)
mysql> insert intoemp values(1000,
kobe
1888-08-08
ERROR 1526 (HY000):Table has no partition for value 3500
此时，按照工资级别（字段salary）进行表分区，partitionby range 的语法类似于“switch…case”的语法，如果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 tableemp(
-> 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),
-> partition p4 values less thanmaxvalue
-> );
Query OK, 0 rowsaffected (0.01 sec)
mysql> insert into empvalues(1000,
kobe
1888-08-08
Query OK, 1 rowaffected (0.00 sec)
mysql> insert intoemp values(1000,durant,12,
1888-08-08
Query OK, 1 rowaffected (0.00 sec)
maxvalue 表示最大的可能的整数值。值得注意的是values less than子句中也可以使用一个表达式，不过表达式结果不能为NULL，下面按照日期进行分区，命令如下。
mysql> create tableemp(
-> empno varchar(20) not null,
-> empname varchar(20),
-> deptno int,
-> birthdate date,
-> salary int
-> )
-> partition by range(year(birthdate))(
-> partition p0 values less than(1980),
-> partition p1 values less than(1990),
-> partition p2 values less than(2000),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rowsaffected (0.01 sec)
该方案中，生日1980年以前的员工信息存储在p0分区中，生日1990年以前的员工信息存储在p1分区中，生日2000年以前的员工信息存储在p2分区中，2000年以后出生的员工信息存储在p3分区中。
RANGE分区很有用，常常使用在以下几种情况。
（1）如果要删除某个时间段的数据时，只需要删除分区即可。例如，要删除1980年以前出生员工的所有信息，此时会执行“alter table emp drop partition p
0”
1980”
（2）如果使用包含日期或者时间的列可以考虑用到RANGE分区。
（3）经常运行直接依赖于分割表的列的查询。比如，当执行某个查询，如“select count（*） from empwhere year(birthdate) = 1999 group by empno”，此时MySQL数据库可以很迅速地确定只有分区p2需要扫描，这是因为查询条件对于其他分区不符合。
2. LIST分区
LIST分区类似RANGE分区，他们的区别主要在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个集合，而RANGE分区是从属于一个连续区间值的集合。创建LIST分区命令如下：
mysql> create table employees(
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition bylist(deptno)
->(
-> partition p1 values in (10,20),
-> partition p2 values in (30),
-> partition p3 values in (40)
-> );
Query OK, 0 rowsaffected (0.01 sec)
以上示例以部门编号划分分区，10号部门和20号部门的员工信息存储在p1分区，30号部门的员工信息存储在p2分区，40号部门的员工信息存储在p3分区，同RANG分区一样，如果插入数据的部门编号不在分区值列表中时，那么“insert”插入操作将失败并报错。
3. HASH分区
HASH分区是基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL自动完成这些工作，用户所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
先看下面的例子：
mysql> create tablehtable(
-> id int,
-> name varchar(20),
-> birthdate date not null,
-> salary int
-> )
-> partition by hash(year(birthdate))
-> partitions 4;
Query OK, 0 rowsaffected (0.00 sec)
当使用了“PARTITION BY HASH”时，MySQL将基于用户函数结果的模数来确定使用哪个编号的分区。将要保存记录的分区编号为N = MOD(表达式, num)。如果表htable中插入一条birthdate为“
2010-09-23
mod(year(’
2010-09-23
=mode(2010,4)
=2
此时，该条记录的数据将会存储在分区编号为2的分区空间。
4. 线性HASH分区
线性HASH分区和HASH分区的区别在于，线性哈希功能使用的一个线性的2的幂运算法则，而HASH分区使用的是哈希函数的模数。
先看下面的例子：
mysql> create tablelhtable(
-> 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;
Query OK, 0 rowsaffected (0.03 sec)
如果表lhtable中插入一条hireddate为“
2010-09-23
找到下一个大于num的2的幂，把这个值称作V，可以通过下面的公式得到。V =POWR(2,CEILING(LOG(2,num)))，假设，num的值是13，那么LOG(2,13)就是3.70043。CEILING(3.70043)就是4，则V= POWER(2,4)，即等于16。
计算 N= F(column_list) & (V – 1) 此时当N>=num时，V = CEIL(V/2), 此时N= N & (V-1)。
下面使用一个示例来说明通过线性哈希分区算法计算分区N的值，线性哈希分区表t1是通过下面的语句创建。
mysql> create tablet1(
-> col1 int,
-> col2 char(5),
-> col3 date
-> )
-> partition by linear hash( year(col3))
-> partitions 6;
Query OK, 0 rowsaffected (0.59 sec)
现在假设要插入两条记录到表t1中，其中一条记录col3列的值为“
2003-04-14
1998-10-19
记录将要保存到的分区num分区中的分区N，假设num是7个分区，假设表t1使用线性HASH分区且有4个分区。
V = POWR(2,CEILING(LOG(2,num)))
V = POWR(2,CEILING(LOG(2,7))) = 8
N = YEAR(
2003-04-14
= 2003& 7
= 3
N的值是3，很显然 3>=4 不成立，所以附件条件不执行，所以第一条记录的信息将存储在3号分区中。
第二条记录将要保存到的分区序号计算如下：
V =POWR(2,CEILING(LOG(2,num)))
V =POWR(2,CEILING(LOG(2,7))) = 8
= YEAR(
1998-10-19
= 1998 & 7
= 6
N的值是6，很显然 6>=4成立，所以附件条件会执行。
V = CEIL(6/2) = 3
N = N & (V-1)
= 6 & 2
= 2
此时发现2 >= 4不成立，记录将被保存到#2分区中。按照线性哈希分区的优点在于增加﹑删除﹑合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000GB）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡。
5. KEY分区
类似于HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。
先看下面的例子：
mysql> create tablekeytable(
-> id int,
-> name varchar(20) not null,
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by key(birthdate)
-> partitions 4;
Query OK, 0 rowsaffected (0.11 sec)
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂算法得到，而不是通过模数算法。
6. 复合分区
复合分区是分区表中每个分区的再次分割，子分区既可以使用HASH分区，也可以使用KEY分区。这也被称为子分区。
复合分区需要注意以下问题：
l 如果一个分区中创建了复合分区，其他分区也要有复合分区。
l 如果创建了复合分区，每个分区中的复合分区数必有相同。
l 同一分区内的复合分区，名字不相同，不同分区内的复合分区名字可以相同。
下面通过案例讲述不同的复合分区的创建方法。
创建RANGE - HASH复合分区的命令如下：
mysql> create table rhtable
-> (
-> empno varchar(20) not null,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by range(salary)
-> subpartition by hash( year(birthdate))
-> subpartition 3
-> (
-> partition p1 values less than (2000),
-> partition p2 values less than maxvalue
-> );
Query OK, 0 rowsaffected (0.23 sec)
创建RANGE - KEY复合分区的命令如下：
mysql> create tablerktable(
-> no varchar(20) not null,
-> name varchar(20),
-> deptno int,
-> birth date not null,
-> salary int
-> )
-> partition by range(salary)
-> subpartition by key(birth)
-> subpartitions 3
-> (
-> partition p1 values less than (2000),
-> partition p2 values less than maxvalue
-> );
Query OK, 0 rowsaffected (0.07 sec)
创建LIST - HASH复合分区的命令如下：
mysql> create tablelhtable(
-> no varchar(20) not null,
-> name varchar(20),
-> deptno int,
-> birth date not null,
-> salary int
-> )
-> partition by list(deptno)
-> subpartition by hash( year(birth) )
-> subpartitions 3
-> (
-> partition p1 values in (10),
-> partition p2 values in (20)
-> );
Query OK, 0 rowsaffected (0.08 sec)
创建LIST - KEY复合分区的命令如下：
mysql> create tablelktable(
-> no varchar(20) not null,
-> name varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by list(deptno)
-> subpartition by key(birthdate)
-> subpartitions 3
-> (
-> partition p1 values in (10),
-> partition p2 values in (20)
-> );
Query OK, 0 rowsaffected (0.09 sec)
4.3 事务控制
MySQL通过SETAUTOCOMMIT﹑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表示回滚事务、SETAUTOCOMMIT用于设置是否自动提交事务。
默认情况下，MySQL事务是自动提交的，如果需要通过明确的COMMIT和ROLLBACK再提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理有明显不同的地方。如果应用从Oracle数据库迁移到MySQL数据库，则需要确保应用中是否对事务进行了明确的管理。
MySQL的AUTOCOMMIT（自动提交）默认是开启，对MySQL的性能有一定影响，举个例子来说，如果用户插入了1000条数据，MySQL会提交事务1000次。这时可以把自动提交关闭掉，通过程序来控制，只要一次提交事务就可以了。
可以通过如下方式关掉自动提交功能，命令如下：
mysql> set@@autocommit=0;
Query OK, 0 rowsaffected (0.00 sec)
查看自动提交功能是否被关闭，命令如下：
mysql> showvariables like "autocommit";
--------------- -------
| Variable_name |Value |
--------------- -------
| autocommit | ON |
--------------- -------
1 row in set (0.02sec)
下面通过两个Session（Session1和Session2）来理解事务控制的过程，具体操作步骤如下：
在Session1中，打开自动提交事务功能，然后创建表ctable并插入两条记录。命令如下：
mysql> set@@autocommit=1;
Query OK, 0 rowsaffected (0.00 sec)
CREATE TABLE ctable (
data INT(4),
);
mysql> insert intoctable values(1);
Query OK, 1 rowaffected (0.02 sec)
mysql> insert intoctable values(2);
Query OK, 1 rowaffected (0.00 sec)
在Session2中，打开自动提交事务功能，然后查询表ctable，命令如下：
mysql> set@@autocommit=1;
Query OK, 0 rowsaffected (0.00 sec)
mysql> select *from ctable;
------
| data |
------
| 1 |
| 2 |
------
2 rows in set (0.00sec)
在Session1中，关闭自动提交事务功能，然后向表ctable中插入两条记录，命令如下：
mysql> set@@autocommit=0;
Query OK, 0 rowsaffected (0.00 sec)
mysql> insert intoctable values(3);
Query OK, 1 rowaffected (0.00 sec)
mysql> insert intoctable values(4);
Query OK, 1 rowaffected (0.00 sec)
在Session2中，查询表ctable，命令如下：
mysql> select *from ctable;
------
| data |
------
| 1 |
| 2 |
------
2 rows in set (0.00sec)
从结果可以看出，在session1中新插入的两条记录没有查询出来。
在Session1中，提交事务，命令如下：
mysql> commit;
Query OK, 0 rowsaffected (0.01 sec)
在Session2中，查询表ctable，命令如下：
mysql> select *from ctable;
------
| data |
------
| 1 |
| 2 |
| 3 |
| 4 |
------
4 rows in set (0.00sec)
如果在表的锁定期间，如果使用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 tablenbaplayer write;
Query OK, 0 rowsaffected (0.00 sec)
在Session2中，向表nbaplayer中增加一条记录，命令如下：
mysql> insert intonbaplayer values
(1,
kobe
在Session1中，插入一条记录，命令如下：
mysql> insert intonbaplayer values
(2,durant,40000);
Query OK, 1 rowaffected (0.02 sec)
在Session1中，回滚刚才插入的记录，命令如下：
mysql> rollback;
Query OK, 0 rowsaffected (0.00 sec)
在Session1中，开启一个新的事务，命令如下：
mysql> starttransaction;
Query OK, 0 rowsaffected (0.00 sec)
在Session2中，表锁被释放，此时成功增加该条记录，命令如下：
mysql> insert intonbaplayer values
(1,
kobe
Query OK, 1 rowaffected (2 min 32.99 sec)
在Session2中，查询nbaplayer，命令如下：
mysql> select *from nbaplayer;
------ -------- --------
| id | name | salary |
------ -------- --------
| 2 | durant | 40000 |
| 1 |
kobe
------ -------- --------
2 rows in set (0.00sec)
从结果可以看出，此时发现Session1的回滚操作并没有执行成功。
MySQL提供的LOCK IN SHARE MODE锁可以保证会停止任何对它要读的数据行的更新或者删除操作。下面通过一个例子来理解。
在Session1中，开启一个新的事务，然后查询数据表nbaplayer的salary列的最大值，命令如下：
mysql> begin;
Query OK, 0 rowsaffected (0.00 sec)
mysql> selectmax(salary) from nbaplayer lock in share mode;
-----------------
| max(salary) |
-----------------
| 40000|
-----------------
1 row in set (0.00sec)
在Session2中，尝试做更新操作，命令如下：
mysql> updatenbaplayer set salary = 90000 where id = 1;
等待
在Session1中，提交事务，命令如下：
mysql> commit;
Query OK, 0 rowsaffected (0.00 sec)
在Session2中，等Session1的事务提交后，此时更新操作成功执行，结果如下：
mysql> updatenbaplayer set salary = 90000 where id = 1;
Query OK, 1 rowaffected (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]
XASTART 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 [ONEPHASE]
该命令用来提交具体的分支事务。
XA ROLLBACK xid
该命令用来回滚具体的分支事务。也就是两阶段提交的第二个提交阶段，分支事务被实际地提交或者回滚。
XA RECOVER
该命令用于返回数据库中处于PREPARE状态的分支事务的详细信息。
分布式的关键在于如何确保分布式事务的完整性，以及在某个分支出现问题时如何解决故障。
分布式事务的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等。
MySQL 分布式事务分为两类：内部分布式事务和外部分布式事务。内部分布式事务用于同一实例下跨多个数据引擎的事务，由二进制日志作为协调者；而外部分布式事务用于跨多个MySQL实例的分布式事务，需要应用层介入作为协调者，全局提交还是回滚，都是由应用层决定的，对应用层的要求比较高。
MySQL分布式事务在某些特殊的情况下会存在一定的漏洞，当一个事务分支在PREPARE状态的时候失去了连接，在服务器重启以后，可以继续对分支事务进行提交或者回滚操作，没有写入二进制日志，这将导致事务部分丢失或者主从数据库不一致。
4.5 小结
本章主要讲解MySQL的一些高级特性，其中包括MySQL查询缓存，优化查询缓存来提高缓存命中率，并且详细介绍了MySQL合并表和分区，MySQL提供的事务控制和锁定语法，并对MySQL事务管理和分布式事务进行了简单的介绍。值得注意的是MySQL分布式事务存在一定的漏洞，MySQL分布式事务在特殊情况下是无法保证事务的完整性。
MySQL技术精粹---架构、高级特性、性能优化与集群实战 pdf下载声明
本pdf资料下载仅供个人学习和研究使用，不能用于商业用途，请在下载后24小时内删除。如果喜欢，请购买正版
