4-MySQL DBA笔记-开发进阶

第4章开发进阶
本章将介绍一些重中之重的数据库开发知识。
在数据库表设计中，范式设计是非常重要的基础理论，因此本章把它放在最前面进行讲解，而这其中又会涉及另一个重要的概念——反范式设计。
接下来会讲述MySQL的权限机制及如何固化安全。
然后介绍慢查询日志及性能管理的部分理念，并讲述数据库的逻辑设计、物理设计、导入导出数据、事务、锁等知识。
最后会提及 MySQL的一些非核心特性，并对于这些特性的使用给出一些建议。
4.1 范式和反范式
4.1.1 范式
什么是范式？
范式是数据库规范化的一个手段，是数据库设计中的一系列原理和技术，用于减少数据库中的数据冗余，并增进数据的一致性。
数据规范化通常是将大表分成较小的表，并且定义它们之间的关系。这样做的目的是为了避免冗余存放数据，并确保数据的一致性。
添加、删除和修改数据等操作可能需要修改多个表，但只需要修改一个地方即可保证所有表中相关数据的一致性（由于数据没有冗余存放，修改某部分数据一般只需要修改一个表即可）。
由于数据分布在多个表之间，因此检索信息可能需要根据表之间的关系联合查询多个表。
数据规范化的实质是简单写、复杂读。
写入操作比较简单，对于不同的信息，分别修改不同的表即可；而读取数据则相对复杂，检索数据的时候，可能需要编写复杂的SQL来联合查询多个表。
常用的范式有第一、第二、第三范式，通常来说，如果数据库表满足某一个层级的范式，那么它也满足前面所有层级的范式，比如，第三范式肯定满足第一、第二范式。
如果一个关系数据库表的设计满足第三范式，通常可认为它是“范式化”的。
那么，这三类范式又分别代表什么含义呢？以下将进行更进一步的阐释。
1.第一范式
第一范式是指数据库表的每一列（属性）都是不可分割的基本数据项，这就要求数据库的每一列都只能存放单一值，即实体中的某个属性不能有多个值或不能有重复的属性。
第一范式（1NF）是对关系模式的基本要求。
图4-1是不满足第一范式的一个例子：“credit_card_transactions（客户信用卡交易）”。
图4-1 credit_card_transactions
每个用户（Customer）对应多个交易（Transactions），但这些交易记录被封装在一个复杂的属性Transactions内，
这个属性包含多个时间的交易记录，其中Tr.ID列存储的是交易事务ID，Date列的是存储交易时间，Amount列存储的是交易金额，
如果要查询某用户某个时间的交易记录，还要解析这个结构，才能找到对应的信息。
这样的数据很难在关系型数据库内存储和检索。
下面来看下满足第一范式的等价例子，如表4-1所示。
表4-1 满足第一范式的credit_card_transactions表
现在，每一行数据代表一笔单独的信用卡交易记录，这样就可以很方便地进行检索和统计了。
再看表4-2所示的例子。Customer表存放了客户的信息，包括Customer ID（客户ID），First Name（名），Surname（姓），Telephone Number（电话号码）。
存储电话记录的列“Telephone Number”里包含了多个值，违反了第一范式。以下是符合第一范式的设计。
将Customer表分解为两个表：Customer Name（见表4-3）和Customer Telephone Number（见表4-4）。
表4-3 Customer Name（客户姓名）表4-4 Customer Telephone Number（客户电话号码）这样Telephone Number列就不存在多个值了。
当然，这样的设计在现实中很少用到。
也可以把多个电话存储为以逗号分隔的字符串，见表4-5。表4-5 Customer（客户表）
以上的设计，属于一种常用的反范式设计，使用分隔符存储多个值，一般来说，如果应用程序只需要存储和使用，而不需要对单独的项进行修改或检索的话，那就可以存储为以上的形式。
2.第二范式
一个数据表符合第二范式的前提是该数据表符合第一范式。
它的规则是要求数据表里的所有数据都要和该数据表的主键有完全相依的关系；如果有哪些数据只和主键的一部分有关的话，就得把它们独立出来变成另一个数据表。
如果一个数据表的主键只有单一一个字段的话，那么它就一定符合第二范式。
来看下面的例子，表4-6给出了Employees’Skills（雇员技能）信息。
表4-6 Employees’Skills（雇员技能表）
表4-6的主键是（Employee,Skill），由于当前工作地点（Current Work Location）只取决于主键的部分列（取决于Employee列），
显然，它不满足第二范式，Current Work Location列的数据存在重复，且更新数据的时候也可能会忘记更改所有Current Work Location列的信息。
要满足第二范式，需要把依赖Employee列的信息独立出来放到另外的表中，见表4-7和表4-8。
表4-7 Employees（雇员表）表4-8 Employees’Skills（雇员技能表）
3.第三范式
第三范式的所有非键属性都只和候选键有相关性，也就是说所有非键属性互相之间应该是无关的。
候选键指的是能够唯一标识一笔记录的属性的最小集合，一般我们所说的候选键指的就是主键。
下面是一个关于锦标赛冠军的例子，见表4-9。
表4-9 Tournament Winners（锦标赛冠军表）
上表Tournament列存储锦标赛名称，Year列存储举办日期，Winner列存储冠军姓名。
主键是（Tournament,Year），由于冠军的生日（Winner Date ofBirth）是固定的，Winner列可以决定Winner Date ofBirth列的信息，
Winner和Winner Date ofBirth这两个属性都是非键属性，显然违反了第三范式。
满足第三范式的表格形式应如表4-10和表4-11所示，这里是将表4-9分解为了两个表。
表4-10 Tournament Winners（锦标赛冠军表）表4-11 Player Dates ofBirth（冠军生日表）
一般数据库表的设计满足第三范式即可，一些其他的范式，如第四范式、第五范式、DK范式、第六范式，因为使用得很少，这里就不做介绍了。
范式的好处是：使编程相对简单，数据量更小，更适合放入内存，更新更快，只需更新更少的数据。
更少的冗余数据意味着更少地需要GROUP、DISTINCT之类的操作。
不利之处是查询会变得更加复杂，查询时需要更多连接（JOIN）操作，一些可以复合索引的列由于范式化的需要被分布到了不同的表中，导致索引策略不佳。

4.1.2 反范式
反范式是试图通过增加冗余数据或通过分组数据来优化数据库读取性能的过程。
在某些情况下，反范式是解决数据库性能和可伸缩性的极佳策略。
范式化的设计是在不同的有关系的表中存储不同的信息，如果需要查询信息往往需要连接多个表，如果连接的表很多，将会导致很多随机I/O，那么查询可能会非常慢。
一般有两种解决方案，一种做法是仍然保持范式化的表设计，但在数据库存储冗余信息来优化查询响应，由数据库来确保冗余副本数据的一致性。
例如Oracle的物化视图技术，SQL Server的Indexed View技术。
另一种做法是反范式的数据表设计。由于多了冗余数据，因此数据的一致性需要靠数据库约束或应用程序来保证。
传统商业数据库一般通过施加数据库约束来确保数据的一致性，而互联网数据库一般靠应用程序来确保数据的一致性。
反范式的好处是减少了连接，因此可以更好地利用索引进行筛选和排序，对于一些查询操作可以提高性能。
但也需要清楚一个事实，那就是冗余数据意味着更多的写入，如果冗余的数据量很大，还可能会碰到I/O瓶颈，这会导致性能变得更差，
所以需要事先衡量对各个表的更新量和查询量，评估对其他查询的影响，避免引发性能问题。
冗余数据也意味着可能要牺牲部分数据的一致性，我们有必要区分不同数据的一致性的优先级，对于重要的、用户比较敏感的数据一定要注意一致性的问题，以免影响用户的体验。
随着开发经验的日渐丰富，做研发的读者通常都会逐渐熟悉范式，创建一个合格的满足第三范式的数据库设计并不会太难；而对于反范式设计，由于不熟悉硬件性能和数据库机制，可能考虑就不是那么周全了。
反范式设计在统计分析、数据仓库等领域使用的比较多，通过冗余数据，增加各种统计表、中间表，数据可以更快地被加载和分析。
以下是一些反范式的例子，一般的方法是冗余或缓存某个表的一些列到另一个表或缓存中。
1）论坛的消息表forum_message包括如下字段：msg_id、from_uid、to_uid、subject、message、post_time。
由于表中只存储了会员id的信息，因此如果要显示发送给某个用户（以to_uid标识）的完整消息，还需要用from_uid去连接会员表，获取会员的姓名，
在高并发的情况下，这样做可能会带来性能问题，常用的解决方案是增加1个冗余字段from_uname以避免JOIN。
2）反范式可以更好地利用索引进行筛选和排序，如上一个例子1）中，如果需要按照uname对消息进行排序，则需要连接会员表，
然后按照uname进行排序，这样的代价比较高，增加冗余列uname，并在其上创建索引，就可以利用索引排序很快地返回结果。
对于多个列的筛选排序也可以采用类似的优化思路，
例如： select table_a.* from table_a join table_b on table_a.id=table_b.id order by table_a.col_1,table_b.col_2；
或者： select table_a.* from table_a join table_b on table_a.id=table_b.id where table_a.col_1=100 order table_b.col_2；
这样的SQL语句，由于排序的列不能利用到索引，因此需要创建临时表进行排序，成本比较高。
可以考虑在table_a表中冗余col_2列，并且建立复合索引（col_1，col_2）。这样不仅可以不用JOIN两张表，而且还可以利用索引进行排序。
3）一些程序，需要发送系统信息、推荐信息给用户。
一种解决方案是在后台维护一张信息表a_message，发布新消息的时候，给t_message表的每个用户插入新的消息id。
用户登录后检查消息表t_message是否有新的未读消息，为了节省空间，t_message只有信息id而没有内容，这在大量用户登录的情况下可能会导致性能问题，
因为每次查询都需要JOIN另一个表 a_message以获取内容。
为了以更快的速度加载数据，可以在发布信息的时候把信息内容一并插入到t_message表中。
4）冗余数据也可以放在缓存中，比如表a，如果用户姓名已经缓存在某个缓存产品中了，如memcached，那么就可以直接从缓存中获取，而不需要再去JOIN数据库获取用户姓名了。
同样的，对于表c，也可以考虑把消息内容放到缓存里。
5）一些统计操作，比如COUNT、SUM、MAX、MIN等操作，如果计算耗费的资源比较多，可以考虑增加冗余的统计信息，或者增加额外的字段，或者增加额外的表，比如论坛的发帖统计，用户在线人数等。
例如，对于发帖统计，需要统计最近24小时的发帖数量，我们可以每个小时插入一条统计数据到统计表，这样就可以统计最近24小时的数据了，虽然这样做不够准确，但用户一般不会介意这种数据的不准确性。
如果需要更精确的统计，可以在前23 个小时使用统计值，最近1个小时使用实际值即可。
6）如果某个用户表的字段比较多（如uid、uname、upass、email、address、qq、msn……），数据量很大，超过亿级别，那么为了方便扩展，我们将把数据分片到多个表中，
例如，我们对uid这个整型字段进行求模运算，把求模运算结果一样的uid存放到同一张分表中。
这时，用户需要以uname登录，查询uid，以便到分表中去查询数据。
因此可以增加一个冗余表，只存储uname 和uid的映射关系。
由于这个冗余表仅有两个列，因此虽然数据量很大，但完全可以放在一张表内，也方便加载到内存中进行访问。
提示：要求数据库中的所有表都满足范式是不太现实的，一般生产库中会混合使用范式和反范式。
很多应用程序的数据库设计，起初都是偏向范式化的，这样做编码会简单方便，但随着流量上涨，数据量增加，往往会碰到一些性能问题，此时就要考虑一些反范式设计。
当然，大致估测到以后的流量、数据量，并能够预先考虑反范式设计会更好。
建议开发人员首先创建一个完全规范化的设计，然后为了性能原因选择性地对一些表进行反范式化设计。
我们要牢记一个准则，设计的数据库应该按照用户可能的访问路径、访问习惯进行设计，而不是严格地按照数据范式来设计。

4.2 权限机制和安全
4.2.1 MySQL访问权限系统
1.概述
MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的各种权限，一般生产环境中的程序账号只需要SELECT、INSERT、UPDATE和DELETE权限即可。
MySQL根据访问控制列表（ACL）对所有连接、查询和用户尝试执行的其他操作进行安全管理。
MySQL将验证用户的3项信息：用户名、密码、主机来源。对通过验证的用户再确认其他的访问权限，以进行访问控制。
权限可以分为两类：系统权限和对象选项。
系统权限允许执行一些特定的功能，如关闭数据库、终止进程、显示数据库列表、查看当前执行的查询等。
对象权限是指对一些特殊的对象（表、列、视图、数据库）的访问权限，例如是否允许访问某张表，是否允许在某个库中创建表。
一般不允许直接更改MySQL的权限表，而是通过GRANT和REVOKE语句进行权限的赋予和收回，这也是更安全可靠的办法。
GRANT和REVOKE语句允许系统管理员创建MySQL用户账户、授予权限和撤销权限。
授予的权限可以分为多个级别：服务器级别（全局）、数据库级别、表级别、列级别、子程序级别。
撤销权限即回收已经存在的权限。
GRANT和REVOKE的基本语法如下所示：
GRANT [privileges] ON [objects] TO [user];
GRANT [privileges] ON [objects] TO [user] IDENTIFIED BY [password];
REVOKE [privileges] ON [objects] FROM [user];
MySQL为有SUPER权限的用户专门保留了一个额外的连接，因此即使是所有的普通连接都被占用，MySQL root用户仍可以登录并检查服务器的活动。
如果想要限制单个账户允许的连接数量，可以通过设置max_user_connections变量来完成。
MySQL允许对不存在的数据库目标授予权限。这个特性是特意设计的，目的是允许数据库管理员为将在此后被创建的数据库目标预留用户账户和权限。
当在GRANT语句中指定数据库名称时，允许使用“_”和“%”通配符。
这意味着，如果想要使用“_”字符作为一个数据库名称的一部分，则应该在GRANT语句中指定它为“\_”，例如，“GRANT…ON‘foo\_bar’.*TO…。”
注意：SHOW TABLES命令不会显示用户没有权限访问的表。
MySQL的存储过程、触发器、视图都可以提供某种程度的安全性，但不建议采用以上特性来实现安全性。
除了尽量给予最小的权限外，建议不要给予过细的权限，MySQL的权限精细控制并不完善，可能会导致维护上的成本增加。
注意：不要重复利用原来的用户名。
不要采取给相同的用户名（但来自于不同的主机）赋予不同权限的方式。这样很容易造成混淆，导致维护的困难，可以另外创建单独的账号。

2.权限更改何时生效
当mysqld启动时，所有授权表的内容将被读进内存并且从此时开始生效。
当服务器注意到授权表被改变了时，现存的客户端连接将会受到如下影响。
表和列的权限在客户端的下一次请求时生效。
数据库的权限改变在下一个USE db_name命令生效。
全局权限的改变和密码改变在下一次客户端连接时生效。
如果使用GRANT、REVOKE或SET PASSWORD命令对授权表进行修改，那么服务器会注意到更改并立即将授权表重新载入内存。
如果手动地修改授权表（使用INSERT、UPDATE或DELETE等），则应该执行mysqladmin flush-privileges或mysqladmin reload告诉服务器再重新装载授权表，否则手动的更改将不会生效，除非重启服务器。

3.常用的权限
SHOW PRIVILEGES命令可以显示MySQL所支持的权限，如下是一些常用的权限。
SELECT、INSERT、UPDATE和DELETE权限允许用户在一个数据库现有的表上实施读取、插入、更新和删除记录的操作。这也是一般程序账号所需要的权限。
SHOW VIEW权限允许用户查看已经创建了的视图。
ALTER权限允许用户使用ALTER TABLE命令来修改现有数据表的结构。
CREATE和DROP权限允许用户创建新的数据库和表，或者删除现存的数据库和表。生产环境中一般不赋予程序账号DROP 的权限。
GRANT权限允许用户把自己拥有的权限授予其他的用户。
FILE权限允许被授予该权限的用户都能读或写MySQL服务器能读写的任何文件。
SHUTDOWN权限允许用户使用SHUTDOWN命令关掉服务器。可以创建一个用户专门用来关闭服务器。
PROCESS权限允许用户使用PROCESSLIST命令显示在服务器内执行的进程的信息；使用KILL命令终止服务器进程。
用户总是能显示或终止自己的进程，但是，显示或终止其他用户启动的进程则需要PROCESS权限。一些监控工具需要PROCESS权限查看正在执行的命令。
4.示例
（1）查看和赋予权限
查看数据库的用户、密码、主机字符串的命令如下：mysql > SELECT user,host,password FROM user;
显示某个用户的权限的命令如下：SHOW GRANTS FOR username@'ip_range';
赋予某个用户对库db1进行SELECT、INSERT、UPDATE和DELETE的权限的命令如下：
GRANT SELECT,INSERT,UPDATE,DELETE ON db1.* TO username@'10.%' IDENTIFIED BY 'your_password';
（2）赋予备份用户权限
赋予备份整个实例权限的命令如下：GRANT LOCK TABLES,RELOAD,SUPER,SELECT,SHOW VIEW,TRIGGER,PROCESS ON *.* TO backup@localhost IDENTIFIED BY 'xxxxxx';
赋予远程备份各个库权限的命令如下：GRANT LOCK TABLES,SELECT,RELOAD,SHOW VIEW,trigger ON *.* TO backup@'10.10.10.10' IDENTIFIED BY 'xxxxxx';
还有一些常用的权限，如配置复制时，复制用户需要REPLICATION SLAVE权限，查看复制状态需要REPLICATION CLIENT权限。
大家可以阅读官方文档来进一步了解具体的权限。
（3）修改用户密码
可以使用SET PASSWORD命令更改密码：mysql>SET PASSWORD FOR user@'ip_range' = PASSWORD('some password');
或者使用GRANT命令重新赋予用户连接密码：mysql>GRANT USAGE ON *.* TO user@'ip_range' IDENTIFIED BY 'some password';
或者可以使用如下命令直接修改系统表：
shell> mysql -u root
mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('newpwd') WHERE user='root';
mysql> FLUSH PRIVILEGES;
（4）强化安全
安装完MySQL之后，一定要移除匿名账号和空密码账号。具体操作请参考2.2节。
注意：MySQL用户主机字符串通配符“%”不包括“localhost”。
“localhost”和IP地址“127.0.0.1”并不等同，如果使用“mysql-u root-h localhost”，则默认会去连接socket文件。
如果我们要连接TCP端口，正确的写法应该是“mysql -u root -h 127.0.0.1”。

4.2.2 强化安全
本节将描述一些常见的需要注意的安全问题，以及一些可以使MySQL安装更加安全的、防止黑客和误操作的措施。
强化安全的目的有如下三点：
保护好MySQL主机的安全，同时也需要关注其他能访问数据库的主机的安全。
确保MySQL自身的安全，包括生产库和备份，应使用强密码，尽可能分配最小的权限给用户。
确保网络、物理的安全，同时也需要关注信息内容的保密。
下面是一些安全的指导原则和注意事项。
加强安全意识。比如加密办公电脑、个人笔记本上的重要数据，不要将未加密的数据上传到各种公共云存储中。在不安全的网络环境下，比如一些公共Wi-Fi中，涉及账号的操作可能会泄露你的信息。
一般将所有数据库都部署于内网（仅监听内网IP），需要慎重对待跨IDC的数据库同步，MySQL自身并没有很好的方式加密数据传输。
开放外网访问的MySQL服务器，需要有相应的访问控制策略，例如通过部署防火墙来限制来源IP。
如果条件允许，应该增加网络安全团队进行安全检查和审计。
在不安全的网络环境中访问公司或远程维护机器，建议使用VPN。
不要让任何人（除了MySQL root账户）访问MySQL数据库中的mysql系统库！
用GRANT和REVOKE语句来控制对MySQL的访问。不要授予超过需求的权限。绝对不能为所有主机授权。
不要给程序账号授予SUPER权限。
生产库上不要留研发人员的账号。
隔离生产环境、开发环境和测试环境，不允许研发、测试人员有权限更改生产环境或知道生产环境的账号密码。
初始安装后应该移除匿名和空密码账号，可以尝试用“mysql -u root”，如果你能够成功连接服务器而没有要求/输入任何密码，则说明有问题。
不要将纯文本密码保存到数据库中，不要从字典中选择密码，如果你的程序是一个客户端，必须用可读的方式存储密码，那么建议使用可解码的加密办法来存储。
一些工具，如telnet、ftp，使用的是明文传输密码，建议不要使用，使用ssh、sftp 是更安全的方式。
使用更安全的算法加密密码，一些流行算法，如MD5已经被证明是弱加密，不适合用于加密密码。曾经比较流行的散列算法SHA-1也被证明不够安全。
推荐的方式是在将密码传入散列函数进行加密之前，将其和一个无意义的字符串拼接在一起，这样即使用户选择了一个在字典中存在的单词作为密码，攻击者也很难使用字典攻击的手段破解密码。
试试从Internet上使用工具扫描端口，或者使用shell命令shell>telnet server_host 3306，如果得到连接并得到一些垃圾字符，则端口是打开着的，这种情况应从防火墙或路由器上关闭端口，除非你有足够合理的理由让它开着。
避免SQL注入，不要信任应用程序的用户输入的任何数据。
有时候人们会认为如果数据库只包含供公共使用的数据，则不需要保护。这是不正确的。即使允许显示数据库中的任何记录，也仍然应该保护和防范、拒绝服务攻击。
不要向非管理用户授予FILE权限。拥有FILE权限的任何用户都能在拥有mysqld守护进程权限的文件系统里写入一个文件！
FILE权限也可以被用来读取任何作为运行服务器的Unix用户可读取或访问的文件。使用该权限，可以将任何文件读入数据库表。
这可能会被滥用，例如，通过使用LOAD DATA装载“/etc/passwd”进入一个数据库表，然后就能用SELECT显示它。
也可以考虑加密传输HTTPS和SSH tunnnel等方案，这些措施将会更安全，但成本比较高，实施起来往往会受制于其他因素。
相对来说，从应用层做一些安全措施、在硬件防火墙中设置规则及MySQL权限控制则是更经济、更标准化的做法，总之，需要在安全和方便上达到一个平衡。
研发人员、测试人员也有必要熟悉目前常用的一些攻击手段的原理和预防，如会话（session）劫持、中间人攻击、SQL注入、跨站脚本攻击等。
1.会话劫持
由于HTTP是无状态的，客户端到服务器端并不需要维持一个连接，因此需要有一种关联的手段，基于此，服务器会给新的会话一个标识信息：cookie。
在PHP环境中，cookie默认是存储在/tmp下的。
生成的用以标识客户信息的cookie一般被称为 session id，用户发出请求时，所发送的HTTP请求header内包含了session id的值，可用firebug查看这个值。
服务器使用session id来识别是哪个用户提交的请求。
session保存的是每个用户的个人数据，一般的Web应用程序会使用session来保存通过验证的用户账号和密码。
在转换不同的网页时，如果需要验证用户的身份，就要用session内所保存的账号和密码来比较。
攻击者通过一些手段来获取其他用户session id的攻击就叫session劫持。
一个典型的场景是在未加密的Wi-Fi网络中，由于session id在用户的请求内而且是不加密的（未使用HTTPS），通过嗅探工具可以获取到用户的session id，然后可以冒充用户进行各种操作。
除了嗅探外，还有一些其他的手段，如跨站脚本攻击、暴力破解、计算等。
如果使用了HTTPS加密传输，那么理论上可以防止嗅探，但实际上，HTTPS在世界范围内远未普及开来，许多网站登录的时候使用了HTTPS，登录成功后仍然返回了HTTP会话，
一些网站虽然支持HTTPS，但并不作为默认选项，目前已知的网站中gmail是默认全部使用了HTTPS会话的，但常用的各种社交网站、电商网站大多只是部分采用HTTPS。
因为HTTPS无法实现缓存、响应变得缓慢、运营成本高、虚拟主机无法在同一台物理服务器上为多个网站提供服务、和其他不支持HTTPS应用的交互，以上种种因素都制约着HTTPS的普及。
2.中间人攻击
中间人攻击是指攻击者在通信的两端分别创建独立的连接，并交换其所收到的数据，使通信的两端认为他们正在通过一个私密的连接与对方直接对话，
但事实上整个会话都被攻击者完全控制（例如，在一个未加密的Wi-Fi无线接入点的中间人攻击者，可以将自己作为一个中间人插入这个网络）。
中间人攻击能够成功的一个前提条件是攻击者能将自己伪装成每一个参与会话的终端，并且不被其他终端识破。
大多数的加密协议都专门加入了一些特殊的认证方法以阻止中间人攻击。
例如，SSL协议可以验证参与通信的一方或双方使用的证书是否由权威的受信任的数字证书认证机构颁发，并且能执行双向身份认证。
3.跨站脚本攻击
跨站脚本攻击（Cross Site Scripting）是指攻击者利用网站程序对用户输入过滤不足，输入可以显示在页面上对其他用户造成影响的HTML代码，
从而盗取用户资料、利用用户身份进行某种动作，或者对访问者进行病毒侵害的一种攻击方式。
针对这种攻击，主要应做好输入数据的验证，对输出数据进行适当的编码，以防止任何已成功注入的脚本在浏览器端运行。
以下将详细介绍SQL注入攻击的原理和预防，这也是DBA需要重点考虑的。

4.2.3 SQL注入
SQL注入（SQL Injection）攻击是发生在应用程序中的数据库层的安全漏洞。
简而言之，是在输入的字符串之中注入SQL语句，如果在设计不良的程序中忽略了检查，
那么这些注入进去的SQL语句就会被数据库服务器误认为是正常的SQL语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。
SQL注入已经成为互联网世界Web应用程序的最大风险。我们有必要从开发、测试、上线各个环节对其进行防范。
以下将介绍SQL注入的原理及如何预防SQL注入。
SQL注入的原理有如下4点：
1）拼接恶意查询。SQL命令可查询、插入、更新、删除数据，以分号字符分隔不同的命令。
例如：select * from users where user_id = $user_id
user_id是传入的参数，如果传入了“1234;delete from users”，
那么最终的查询语句会变为： select * from users where user_id = 1234; delete from users
如上语句如果执行，则会删除user表的数据。
2）利用注释执行非法命令。SQL命令中，可以插入注释。
例如： select count(*) as 'num' from game_score where game_id=24411 and platform_id=11 and version=$version and session_id='d7a157-0f-48b6-98-c35592'
如果version包含了恶意的字符串“'-1' OR 3 AND SLEEP(500)--”，那么最终查询语句会变成下面这个样子：
select count(*) as 'num' from game_score where game_id=24411 and platform_id=11 and version='-1' OR 3 AND SLEEP(500)-- and session_id='d7a157-0f-48b6-98-c35592'
以上恶意查询只是想耗尽系统资源，SLEEP(500)将导致SQL一直运行，如果添加了修改、删除数据的恶意指令，将会造成更大的破坏。
3）SQL命令对于传入的字符串参数是用单引号引起来的。如果字符串本身包含单引号而没有被处理，则可能会篡改原本的SQL语法的作用。
例如： select * from user_name where user_name = $user_name
如果user_name传入的是G'chen，那么最终的查询语句会变成这样： select * from user_name where user_name ='G'chen'
以上语句将会出错，这样的语句风险比较小，因为语法错误的SQL语句不会被执行。
但也可能恶意产生的SQL语句，没有任何语法错误，并且以一种你不期望的方式运行。
4）添加一些额外的条件为真值表达式，改变执行行为。
例如： update users set userpass=SHA2('$userpass') where user_id=$user_id;
如果user_id被传入恶意的字符串“1234 OR TRUE”，最终的SQL语句会变成下面这样：
update users set userpass=SHA2('123456') where user_id=1234 OR TRUE; 这将更改所有用户的密码。
下面是避免SQL注入的一些方法。
（1）过滤输入内容，校验字符串
应该在将数据提交到数据库之前，就把用户输入中的不合法字符剔除掉。
可以使用编程语言提供的处理函数，如PHP的 mysql_real_escape_string()来剔除，或者定义自己的处理函数进行过滤，还可以使用正则表达式匹配安全的字符串。
如果值属于特定的类型或有约定的格式，那么在拼接SQL语句之前就要进行校验，验证其的有效性。
比如对于某个传入的值，如果可以确定是整型，那么我们要判断下它是否为整型，不仅在浏览器端（客户端），而且在服务器端也需要进行验证。
（2）参数化查询
参数化查询目前已被视作是最有效的预防SQL注入攻击的方法。
不同于在SQL语句中插入动态内容，查询参数的做法是在准备查询语句的时候，就在对应参数的地方使用参数占位符。然后，在执行这个预先准备好的查询时提供一个参数。
在使用参数化查询的情况下，数据库服务器不会将参数的内容视为SQL指令的一部分来进行处理，
而是在数据库完成SQL指令的编译之后，才套用参数运行，因此就算参数中含有破坏性的指令，也不会被数据库所运行。
可以使用MySQLi扩展或pdo扩展来绑定参数实现参数化查询。
如下是一个使用MySQLi扩展绑定参数的示例。
<html>
<head>
<title> test parameter query </title>
</head>
<body>
<?php
$host="127.0.0.1";
$port=3306;
$socket="";
$user="garychen";
$password="garychen";
$dbname="employees";
$con = new mysqli($host, $user, $password, $dbname, $port, $socket) or die ('Could not connect to the database server' . mysqli_connect_error());
echo 'connect to employees database successfully';
echo "<br />";
echo "select departments table using parameter";
echo "<br />";
$query = "select * from departments where dept_name = ?";
if ($stmt = $con->prepare($query)) {
$stmt->bind_param("s",$depname);
$depname="Finance";
$stmt->execute();
$stmt->bind_result($field1, $field2);
while ($stmt->fetch()) {
printf("%s, %s\n", $field1, $field2);
echo "<br />";
}
$stmt->close();
}
$con->close();
?>
</body>
</html>
上例首先是预处理语句if($stmt=$con->prepare($query))，然后绑定参数使用bind_param()方法，该方法的语法格式如下所示。
bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )
其中，types指定绑定参数的类型，包含了一个或多个字符。I代表整型，D代表双精度，S代表字符串，B代表BLOB类型，本例中是S。
但是绑定参数也有如下一些限制：
不能让占位符“?”代替一组值，例如： SELECT * FROM departments WHERE userid IN ( ? );
不能让占位符“?”代替数据表名或列名，例如： SELECT * FROM departments ORDER BY ?;
不能让占位符“?”代替SQL关键字，例如： SELECT * FROM departments ORDER BY dept_no ?;
对于Java、JSP开发的应用，也可以使用预处理语句加绑定参数的方式来避免SQL注入。
（3）安全测试、安全审计
除了开发规范，还需要流程、机制和合适的工具来确保代码的安全。
我们应该在开发过程中对代码进行审查，在测试环节使用工具进行扫描，上线后定期扫描安全漏洞。
通过多个环节的检查，一般是可以避免SQL注入的。
有些人认为存储过程可以避免SQL注入，存储过程在传统行业里用得比较多，对于权限的控制是有一定用处的，但如果存储过程用到了动态查询，拼接SQL，那就一样会存在安全隐患。
一些编程框架对于写出更安全的代码也有一定的帮助，因为它提供了一些处理字符串的函数和使用查询参数的方法，但同样，你仍然可以编写出不安全的SQL语句。
所以归根到底，我们需要有良好的编码规范，并能充分利用参数化查询、字符串处理和参数校验等多种办法来实现安全。

4.3 慢查询日志
慢查询日志可以用来定位执行时间很长的查询，它是我们常用的性能分析工具，通过在开发、测试期间关注慢查询，我们可以尽量避免引入效率很差的查询。
以下将介绍慢查询日志的分析策略和常用的工具。
4.3.1 查看慢查询日志
1.优化策略
性能优化的一个很重要的步骤是识别导致问题的BAD SQL。
对于一般的数据库调优，调优人员往往会采用调优TOP 10的策略，如果我们把最“昂贵”的10个查询优化完（更高效地运行它们，例如添加一个索引），那么就会立即看到对整体MySQL的性能的提升。
然后就可以重复这一过程，并优化新的前10名的查询。
就笔者经验而言，一般一两轮迭代就足够了。以后随着业务发展、用户流量增加，可进行新的一轮调优。
2.慢查询日志的格式
不同数据库TOP 10基于的标准可能不太一样，商业数据库提供了更完善的成本分析方法，MySQL的慢查询日志比较粗略，主要是基于以下3项基本的信息。
Query_time：查询耗时。
Rows_examined：检查了多少条记录。
Rows_sent：返回了多少行记录（结果集）。
以上3个值可以大致衡量一条查询的成本。其他信息包括如下几点。
Time：执行SQL的开始时间。
Lock_time：等待table lock的时间，注意InnoDB的行锁等待是不会反应在这里的。
User@Host：执行查询的用户和客户端IP。
以下是一个慢查询日志的例子。
# Time:11062210:11:16
# User@Host: rss[rss] @ [12.12.12.12]
#
Query_time:1.637992 Lock_time:0.000038 Rows_sent:0 Rows_examined:101 SET timestamp=1308708676;
select * from rss_doc1 where feed_id=5850 order by doc_id desc limit190,10;
其中，Query_time、Rows_examined、Rows_sent这3个值可以大致衡量一条查询的成本。
如果检查了大量记录，而只返回很小的结果集，则往往意味着查询质量不佳。
慢查询日志可以用来找到执行时间很长的查询，可以用于优化。
但是，检查又长又慢的查询日志会很困难。
要想使其变得容易些，可以使用mysqldumpslow命令获得慢查询日志摘要来处理慢查询日志，或者使用更好的第三方工具pt-query-digest。
注意，慢查询日志里的慢查询不一定就是不良SQL，还可能是受其他的查询影响，或者受系统资源限制所导致的慢查询。
比如下面的例子，会话被阻塞了，实际上是一个行锁等待50s超时，然后记录到了慢查询日志里。
# Query_time: 50.665866 Lock_time: 0.000102 Rows_sent: 0 Rows_examined: 0 SET timestamp=1339728734;
update tbl_rankings set status=2 where ranking=1;
3.如何识别需要关注的SQL
以下是识别需要关注的SQL的步骤。
第一步，确认已经开启了慢查询日志，并记录了合理的阈值。
MySQL可以把慢查询日志记录到数据表内，但更普遍的做法是记录到日志里，然后使用工具来分析。
以下的命令将查看慢查询是否启用了，以及慢查询的日志路径。
mysql> show variables like'%query_log%';
slow_query_log ON
slow_query_log_file /path/to/log3304/slowquery.log
MySQL 5.1可以动态打开示例中提到的slow_query_log选项。
如果配置文件或启动参数没有给出file_name值，慢查询日志将默认命名为“主机名-slow.log”，如果给出了文件名，但不是绝对路径名，文件则写入数据目录。
语句执行完成并且所有锁释放后则记入慢查询日志。记录的顺序与执行顺序可以不相同。
我们可以在MySQL客户端下使用命令“SHOW VARIABLES LIKE'%query_time%'”查看全局变量long_query_time。
所有执行时间超过long_query_time秒的SQL语句都会被记录到慢查询日志里。
MySQL参数long_query_time默认的2s阈值太大，可能不适用，对于一般的OLTP应用，建议将阈值设置得更小，比如200~500ms。
有时手动调整了某变量的值，且需要永久变更，这时则要注意全局变量的值应和配置文件保持一致，配置文件的参数示例如下所示。
[mysqld]
slow_query_log=1
slow_query_log_file=/usr/local/mysql/log/slowquery.lo
long_query_time=0.5
其中，slow_query_log设置为1表示开启慢查询日志，设置为0则表示关闭慢查询日志。
long_query_time的单位为秒，MySQL 5.1.21后可以设置毫秒级的慢查询记录，如设置long_query_time=0.01。
另外有一个参数log-queries-not-using-indexes，用于指定如果没有使用到索引或虽然使用了索引但仍然遍历了所有记录，就将其记录下来。默认此选项是关闭的。
注意：对于持久连接（长连接）、连接池这类情况，由于不能重置session会话的变量，因此即使修改了long_query_time的值，也不能马上生效，这会给我们带来一些困扰，
不过，使用短连接或使用Percona版本的MySQL可以解决此问题。
但对于测试人员或开发人员来说，这点是很方便调整和验证的，重启应用或重连数据库即可解决此问题。

4.3.2 使用工具分析慢查询日志
从前面的内容可知，Query_time、Rows_examined、Rows_sent这3个信息让我们看到了查询需要优化什么。
查询时间最长的SQL往往是最需要优化的，如果检查了大量记录（Rows_examined），而只返回很小的结果集（Rows_sent），往往也意味着存在不良SQL。
但在一个高并发的数据库服务上，或者在做压力测试时，如果发现慢查询日志增长得非常快，很难筛选和查找里面的信息，那么在这种情况下，有如下两种选择。
调整阈值，先设置为较大的阈值，这样慢查询记录就很少了，等优化得差不多了，再减少阈值，不断进行优化。
使用命令/脚本、工具进行分析，如mysqldumpslow、pt-query-digest等。
第一种方法比较繁琐，建议大家使用第二种方法。
如果优化效果比较理想，希望更进一步调优，则可以减低阈值，然后记录更多的慢查询日志，然后继续使用脚本、工具进行分析。
1.使用操作系统命令分析
可以使用操作系统自带的命令进行一些简单的统计，如grep、awk、wc，但不容易实现更高级的筛选排序。
下面来看个示例，通过如下命令可以看到每秒的慢查询的统计，当检查到有突变时，往往会有异常发生，这时便可以更进一步到具体的慢查询日志里去查找可能的原因。
awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slowquery.log > /tmp/aaa.log
2.mysqldumpslow
mysqldumpslow命令是官方自带的，此命令可获得日志中的查询摘要。
以下是mysqldumpslow命令的使用示例。
访问时间最长的10个sql语句的命令如下。 mysqldumpslow -t10 /path/to/log3304/slowquery.log
访问次数最多的10个sql语句的命令如下。 mysqldumpslow -s c -t10 /path/to/log3304/slowquery.log
访问记录集最多的10个sql语句的命令如下。 mysqldumpslow -s r -t10 /path/to/log3304/slowquery.log
3.pt-query-digest
有一些第三方分析工具（如msyqlsla、pt-query-digest）比mysqldumpslow更强大，更友好。以下将重点介绍pt-query-digest工具。
pt-query-digest可以生成一份比官方mysqldumpslow可读性好得多的报告。
安装也很简单，命令如下：
wget www.percona.com/get/pt-query-digest
chmod u+x pt-query-digest
基本语法格式如下：pt-query-digest [OPTIONS] [FILES] [DSN]
详细的语法介绍，请参考16.2.2节，这里仅给出一些常用的示例。
直接分析慢查询的命令如下：pt-query-digest /path/of/slow.log > slow.rtf
分析半个小时内的慢查询的命令如下：pt-query-digest --since 1800s /path/of/slow.log > slow.rtf
分析一段时间范围内的慢查询的命令如下：pt-query-digest --since '2014-04-14 22:00:00' --until '2014-04-14 23:00:00' /path/of/slow.log > slow.rtf
显示所有分析的查询命令如下：pt-query-digest --limit 100% /path/of/slow.log > slow.rtf
其中，“--limit”参数默认是“95%:20”，表示显示95%的最差的查询，或者20个最差的查询。
此外，也可以用这个工具来分析二进志日志，以查看我们日常的修改语句是如何分布的，首先需要把二进志日志转换为文本格式。
mysqlbinlog mysql-bin.012639 > /tmp/012639.log
pt-query-digest --type binlog /tmp/012639.log
对于以上分析命令，同样可以加上参数筛选信息，如“--since”、“--until”。
那么，如何查看pt-query-digest报告呢？
以下是一个输出报告，为了节省篇幅，删除了部分信息。
# 140.9s user time, 1.4s system time, 57.93M rss, 154.03M vsz
# Current date: Sun Feb 16 09:16:39 2011
解释：执行pt-query-digest工具的时间。
# Hostname: db1000
# Files: /usr/lcoal/mysql/data/slowquery.log #
Overall: 304.88k total, 159 unique, 0.22 QPS, 0.15x concurrency
解释：慢查询次数一共是304.88k，唯一的查询159个。
# Time range: 2010-12-01 00:00:01 to 2010-12-17 09:05:17
解释：这里记录的是发现第一条慢查询的时间到最后一条慢查询的时间。
# Attribute total min max avg 95% stddev median
# Exec time 216112s 500ms 21s 709ms 1s 968ms 552ms
# Lock time 414s 21us 101ms 1ms 626us 7ms 84us
# Rows sent 169.69M 0 213.73k 583.60 97.36 10.75k 9.83
# Rows examine 60.26G 0 866.23k 207.25k 328.61k 70.68k 201.74k
# Query size 120.31M 35 21.07k 413.76 719.66 148.97 363.48
解释分别如下。Exectime：执行时间。Lock time：表锁的时间。Rows sent：返回的结果集记录数。Rowsexamine：实际扫描的记录数。Query size：应用和数据库交互的查询文本大小。
# Profile
# Rank Query ID Response time Calls R/Call Apdx V/M Item
# 1 0x5931CCE8168ECE59 92062.4390 42.6% 168672 0.5458 1.00 0.01 SELECT game_info game_stat
# 2 0x0E8691F18411F3DC 23404.4270 10.8% 18602 1.2582 0.60 0.04 SELECT game_info game_stat game_info_2 ... ...
解释分别如下。
Rank：所有查询日志分析完毕后，此查询的排序。
Query ID：查询的标识字符串。
Response time：总的响应时间，以及总占比。一般小于5%可以不用关注。
Calls：查询被调用执行的次数。
R/Call：每次执行的平均响应时间。
Apdx：应用程序的性能指数得分。（Apdex响应的时间越长，得分越低。）
V/M：响应时间的方差均值比（变异数对平均数比，变异系数）。可说明样本的分散程度，这个值越大，往往是越值得考虑优化的对象。
Item：查询的简单显示，包括查询的类型和所涉及的表。
以下将按默认的响应时间进行排序，并列出TOP n条查询。并且pt-query-digest输出了EXPLAIN的语句，以方便我们验证查询计划。
以上关于SELECT查询的具体文本此处省略。
从pt-query-digest工具中看到的信息里，对于响应时间，不仅需要关注平均值，还需要关注百分比响应，以及关注其的分布情况和离散程度。
对于响应时间的方差均值比，如果该均值比很大，则可能意味着有一些异常值。
慢查询日志里的慢查询不一定就是BAD SQL。可能是受到了其他查询的影响，或者是受系统资源限制所导致的。
有了分析报告，就可以用EXPLAIN工具确认慢查询的执行计划，从而进行调优。
通常，80%的问题是因为索引不佳而引起的，添加适当的索引即可。
EXPLAIN的使用请参考3.5.4节；SQL的调优请参考第6章。

4.4 应用程序性能管理
4.4.1 为什么需要性能管理
我们知道，一个用户如果要访问网站，往往需要经过许多软硬件设备，现在的大型应用程序架构越来越复杂，可能包含多层架构，拥有各种子系统，
如果系统突然变得很慢，而且代码不能告诉你哪里耗时最长，那么你怎样才能找出系统在何处变慢的呢？所以需要对整个项目进行性能管理。
性能管理其实应该在硬件选型和软件编写之前就开始，但是我们的开发工作往往并没有这么做，往往是等到出现性能问题之后才考虑要进行性能管理，这是不合理的。
我们应该确定性能目标，并在产品的各个过程中不断进行验证，及时发现软件架构和编码的问题。
如果等到项目已经基本完成的情况下才发现性能问题，往往就会难以调整，因为之前确定的软件架构让性能调优变得很难。
影响服务性能的主要因素，从大到小大致是：架构和设计、应用程序、硬件、Web服务器、数据库、操作系统。
数据库、 Web服务器、主机一般由SA、系统工程师、DBA管理，在长期的实践中，已经积累了很多成熟的工具，也有一些开源的监控软件，
但在应用程序领域，研发人员往往会忘记了或不知道如何去监控自己所写的程序的性能。
或者即使知道有一些性能收集的方式，一些性能框架，但对于生成何种数据，以及应该如何统计和展现没有足够的意识。
出现这种现象的原因主要是，研发人员往往侧重于功能实现，而忽视了应用程序的可测量性，为了赶进度，在一般的项目中，对于性能的管理，往往也不作为要求。
这就导致了很多业务，一旦上线碰到大流量，就会暴露出性能问题，但由于没有做好性能监控，很难进行针对性的调优。
其实，让自己写得程序运行得更快、更有效率，往往不是依赖于自己的经验，而是依赖于有一个好的性能分析工具。
通过性能分析工具，我们可以知道自己的程序主要耗时在哪里，从而进行专门的优化，一些性能不佳的操作也可以及早发现，而不会带到生产环境中去。
所以，建议在每个新项目中加入性能剖析代码。
如果项目已经开发完毕，再来添加性能日志代码，将会非常困难，但在新项目中包含性能记录代码则是很容易的。
以下将详细介绍性能管理的一些知识及一些记录性能日志的例子。
4.4.2 应用性能管理概述
以下定义来自维基百科。
（1）什么是应用性能管理
在信息技术和系统管理等领域，应用性能管理（APM），是软件应用程序性能和可用性的监控和管理。
APM致力于检测和诊断应用性能问题，从而能提供预期的服务水平。
（2）应用程序性能指标
有两组性能指标，第一组定义了应用程序终端用户的性能体验，一个很好的例子是高峰时刻的平均响应时间。
请注意这里有两个组成部分，负载和响应时间。
负载是应用程序处理的业务量，如每秒事务数、每秒请求数、每秒PV。
响应时间是指在给定的负载下，应用程序响应用户操作的时间。
如果没有一定的负载，绝大部分应用程序都运行得足够快，这就是为什么程序员不太可能在开发过程中捕捉到性能问题的原因。
第二组性能指标衡量了在一定负载下应用程序使用的计算资源是否有足够的容量来支持给定的负载，在哪里可能会有性能瓶颈。
这些指标的测量为应用建立了一个基于历史经验的性能基线。然后基线可以用来检测性能的变化。
性能的变化可以与外部事件相关联，并用于预测应用程序性能的未来变化。
使用APM最常见的领域是Web应用。除了测量用户的响应时间，应用程序组件的响应时间也可以被监控，以协助我们查明延迟的具体原因。
（3）当前难点
APM已经演变成跨越许多不同的计算平台上的管理应用程序性能的一个概念。它的实现有如下两个挑战。
1）很难通过仪表化的应用程序来监视应用程序性能，尤其是应用程序的内部组件。
2）应用程序可以被虚拟化，这就增加了测量的变化性。
分布式、虚拟和基于云的应用程序给应用性能的监控带来了一个独特的挑战，因为大部分关键的系统组件都不再位于同一台主机上。
每个功能现在都可能被设计成运行于多个虚拟系统上的一个因特网服务，应用程序本身也很可能会从一个系统迁移到另一个系统上，以实现服务水平目标或应对临时停电。
4.4.3 应用性能管理的关注点
应用程序本身正变得越来越难以管理，因为它们正在走向高度分散、多层次、多元素的构造，在很多情况下它们依赖于应用程序开发框架，如.NET或Java。
对于Web性能管理，我们重点要关注的是终端用户体验监控（主动和被动）、用户自定义事务处理剖析、报告和应用数据分析。
（1）终端用户体验监控（主动和被动）
测量用户的请求数据然后将响应返回给用户是捕获终端用户体验的一部分。
这种测量的结果被称为实时应用监控（又名自上而下的监控），其中有两个组成部分，被动和主动。
被动监控通常是无代理的，比如使用网络端口镜像实现监控。
这个解决方案需要考虑的一个关键功能是支持多协议的分析（如XML、SQL、PHP），因为大多数企业已经不仅仅只支持基于Web的应用程序。
主动监控，包含预定义的人工探针和网络机器人，用于报告系统的可用性和业务交易。
主动监控是被动监控的一个很好的补充。两种手段配合使用，可以提供可视化的应用健康状况。
（2）用户自定义事务处理剖析
专注于用户自定义的事务或对于商业团体具有某种意义的URL页面定义。
例如，对于一个给定应用程序，如果有200~300 个唯一页面，可以把它们分组为8~12个更高层次的类别。
这样就可以实现有意义的服务等级协议（SLA）报告，从业务的角度提供应用性能的趋势信息：先从大类开始，逐渐完善它。
（3）报告和应用数据分析
对于所有的应用程序，提供一套共同的指标来收集和报告信息是很重要的，然后就可以标准化呈现应用程序的性能数据的视图。
来自其他工具集收集的原始数据可提高报告的灵活性。这样就可以回答各种各样的性能问题，尽管每个应用程序可能运行在不同的平台上。
注意：过多的信息是难以查看的，这就是为什么报告保持简单是很重要的原因，否则它将不被使用。

4.4.4 具体应用
生产环境中常见的方式是记录应用程序的日志。
在应用程序中记录性能日志将会更全面，这可以让你跟踪用户从访问应用服务到回传的各个环节。
而数据库的性能记录往往只反应了后端数据库的性能记录。
DBA常用的诊断工具慢查询日志很粗糙，只记录了超过阈值的慢查询。
通过对日志的分析，可以方便用户了解应用的运行情况，有助于进行容量规划，分配资源，还可以分析得到该应用的健康状况，及时发现问题并快速定位和解决问题；
也可以分析用户的操作行为、喜好、地域分布、浏览器类型、操作系统或其他更多信息。
我们应该尽可能地记录更多的信息，也就是说，只要愿意，就有能力生成大量的跟踪信息。
在这里，我们仅关注下记录性能方面的日志。
不管是使用框架提供的一些功能，还是自己编写记录日志的代码，都要注意如下这些要点。
要使用方便，配置简单。
要可读性好，方便处理，最好是可以图形化展示，并且趋向实时。
不仅要监测整体响应，还要监测每个环节，特别是关键部分的响应时间。
比如对于一个普通的PHP页面，我们可以记录整体的响应时间，页面每个部分的处理时间，也可以记录访问缓存、访问数据库的响应时间，
如果有重要的业务逻辑，也要一并记录，通过这些翔实的记录，一旦碰到各种性能问题，我们就可以很方便地定位到出现异常的地方。
由于日志的刷新往往很快，因此我们要尽量保持日志紧凑，可以记录到本地，也可以通过网络的方式发送日志到日志服务器。
除了记录日志，日志的解读也很重要，如果可能，最好能够图形化地展示性能、吞吐的变化，这样，我们就可以很直观地通过曲线的变化知道应用的性能是否可能有问题了。
一旦出现性能问题，也可以很直观地从图形中得到需要优化的点。
一般记录性能日志不会有什么开销，由于日志是顺序写入的，对I/O的影响也很小。
如果真的记录起来成本很昂贵，那么也可以选择某一台应用服务器打开性能记录，或者仅记录一段时间，用来诊断性能问题。
也可以随机抽样，选择记录部分比例的访问的性能记录。例如：
<?php
$profiling_enabled = rand(0, 100) > 99;
?>
以上代码采样为1%。
这里要介绍一个国外的性能管理服务工具，New Relic公司的性能工具，虽然国内到国外的网络质量不佳，不便直接使用 New Relic的服务，但它的思想很值得借鉴。
NewRelic是一种提供给公司的SaaS（software-as-a-service）解决方案，可以提供性能监视和分析服务。
能够对部署在本地或在云中的Web应用程序进行监控、故障修复、诊断、线程分析及容量计划，它可以监测从浏览器到应用程序到数据库各个环节的性能记录。
它还可以从多个角度、实时监测移动设备App的性能，及时发现App的错误。
这样的一个应用性能管理工具，能极大地解决各种性能问题。有兴趣的同学可以试用下，默认的仪表板会显示终端用户和应用服务器的一些指标。
它的基本原理是将工具嵌入到你的应用程序里，剖析它，并发送数据到New Relic的服务器，通过基于Web的界面，让你看到应用程序响应时间的性能记录。
这种SaaS服务，使得在生产环境上时刻记录性能成为可能。而传统的一些性能工具，可能因为消耗的资源巨大，而不能轻易地在生产环境中打开并使用。
PHP也有一些优秀的工具，可以用来剖析的你程序。如Facebook开源出来的xhprof（链接地址：http://pecl.php.net/package/xhprof）。
《high performance MySQL》作者开发的一个工具IfP（链接地址：https://code.google.com/p/instrumentation-for-php/ ）。
xhprof对PHP有完善的监控，IfP相对于xhprof来说，对数据库有更详细的测量，它可以自动记录整个页面、数据库和Cache的响应。

4.5 数据库设计
广义的数据库设计包括项目的目标、数据的架构设计、数据库产品的选择、需求收集、数据库逻辑/物理设计、后期维护等多个过程。
本书仅阐述数据库设计中DBA关注的两个阶段：逻辑数据库设计阶段和物理数据库设计阶段。
以下将介绍设计数据库的一般步骤，我们设计的时候不一定要严格遵循这些步骤，它们比较繁琐，
但这些步骤阐明了设计数据库的一般思路，它的方法学值得大家借鉴，尤其是设计很复杂的数据库应用的时候。
4.5.1 逻辑设计
逻辑数据库设计指构建企业所使用的数据模型的过程，它标识了数据库中要描述的重要对象以及这些对象之间的关系。
逻辑数据库设计独立于特定的DBMS和其他的物理考虑事项。
数据库设计人员将根据需求文档，创建与数据库相关的那部分实体关系图（ERD）/类图。
这些图形和需求文档相结合，将有助于相关人员更好地理解业务逻辑和实际的表设计。
互联网的一些应用往往比较简单，所以经验丰富的研发人员直接设计数据表也是很常见的情况，但是对于复杂的项目，仍然推荐绘制E-R图，
如果我们有对逻辑设计的详细描述会更有利于以后程序的开发和维护，也方便DBA与研发设计人员更好地沟通。
逻辑数据库的设计大体可以分为以下这些步骤。
1）创建并检查ER模型。
此步骤主要是标识实体及实体之间的关系。
标识实体的一种方法就是研究用户需求说明里的名词或名词短语。
例如员工管理系统里的员工、部门。在线考试系统里的课程、试卷、学员。
从用户提供的需求说明中得到的一组实体可能不是唯一的。
然而，分析过程的不断迭代必定会引导你选择对完成系统需求来说足够用的实体。
标识关系也可以通过研究需求说明书来实现，需求说明书里的动词或动词短语往往表征了某种关系。
大多数情况下，关系都是二元的，例如，员工实体属于某个公司，试卷实体属于某个课程，学员（实体）解答某张试卷（实体）。
接下来就可以标识实体和关系中的属性、主键等信息。
比如学员实体包括的属性可能有学员号、姓名、性别、生日等信息。
在确定好实体后，我们再检查实体模型是否能够满足的我们的需求。
2）将ER模型映射为表
这个步骤的主要目的是为步骤1建立的ER模型产生表的描述。
这组表应该代表逻辑数据模型中的实体、关系、属性和约束。
产生表的描述后，需要检查表是否满足用户的需求和业务规则。

4.5.2 物理设计
物理数据库设计用于确定逻辑设计如何在目标关系数据库中物理地实现。
它描述了基本表、文件组织、用户高效访问数据的索引、相关的完整性约束及安全性限制。
这个阶段允许设计者决定如何实现数据库，因此，物理设计和特定的DBMS有关。
这部分的任务主要是设计表结构。
逻辑设计中的实体大部分可以转换成物理设计中的表，但是它们并不一定是一一对应的。
物理设计又可以分为如下几步。
把逻辑设计转换为物理表、分析事务、选择文件组织方式、选择索引（基于最重要的事务），以及适当地进行反范式设计（这么做是为了拥有更好的性能）、列出最终表的详细说明。
1.将逻辑设计转换为物理表
将逻辑设计转换为物理表即用特定的数据库语言来实现逻辑设计过程中产生的表的描述，可以输出的信息有表汇总，如表 4-12所示的就是表汇总的模板。
2.分析事务
分析事务指的是分析数据库需要满足的用户需求，只有了解了必须要支持的事务的细节，才能做出有意义的物理设计抉择。
分析预期的所有事务是极为耗时的，只需研究最重要的那部分事务即可。
最活跃的20%的事务往往占据了总的数据访问量的80%。当进行分析时，你会发现这个80/20规则是很有用的方针。
最重要的事务一般是指如下两种事务。
经常运行的事务和对性能产生重大影响的事务。
业务操作的关键事务。
需要关注的一些细节如下：
事务运行的频率？频率信息将标识需要仔细考虑的表。
事务的高峰时间？
访问记录数比较多的事务。
不用写出全部的SQL语句，但至少应该标识出与SQL语句相连的细节类型，也就是如下这些信息。
将要使用的所有查询条件。
连接表所需要的列（对查询事务来说）。
用于排序的列（对查询事务来说）。
用于分组的列（对查询事务来说）。
可能使用的内置函数（例如AVG，SUM）。
被该事务更新的列。
我们将利用这些信息来确定所需要的索引。
3.选择文件组织方式
选择文件组织方式是指选择表数据的存放方式。
物理设计数据库的目标之一就是以有效的方式存储数据。
如果目标DBMS允许，则可以为每个表选择一个最佳的文件组织方式。
一般有如下两种方法。
1）保持记录的无序性并且创建所需数目的二级索引。
2）通过指定主键或聚簇索引使表中记录为有序的。这种情况下，应该选择如下的列来排序或聚簇索引记录。
经常用于连接操作的列，因为这样会使连接更有效率。
在表中经常按某列的顺序访问记录的列。
我们一般使用InnoDB（InnoDB主键即聚簇索引），基于主键的唯一查找和小范围查找是最高效的。
例如，如果有很频繁的基于USERID的查找，或者对USERID的小范围遍历，那么USERID作为主键就是最高效的方式。因为数据是以USERID为顺序进行存储的。
而如果以自增ID为主键，实际的执行过程是需要先按索引列USERID找到索引记录，然后利用存储在索引中的主键值去查找主键，最终定位到记录，这样代价会更高。
如果是范围查找，那么虽然索引是有序的，但最终会按照主键值去检索数据，由于主键值并不是连续的，这将产生很多物理随机读。
以上例子仅用于说明问题，实际应用中，对于小范围的索引查找，性能一般不会成为问题。自增主键在一般情况下也会工作得很好。
4.选择索引
设计索引需要平衡性能的提升和维护的成本。
创建你认为是索引的候选列的“意愿表”，然后逐个考虑维护这样的索引的影响。
以下是创建索引的一些基本指导原则。
1）不必为小表创建索引。在内存中查询该表会比存储额外的索引结构更加有效。
2）为检索数据时大量使用的列增加二级索引。
3）为经常有如下情况的列添加二级索引：查询或连接条件，ORDERBY ，GROUP BY ，其他操作（如UNION或DISTINCT）。
4）考虑是否可以用覆盖索引（covering index）。
5）如果查询将检索表中的大部分记录（例如25%），即使表很大，也不创建索引。这时候，查询整表可能比用索引查询更有效。
6）避免为由长字符串组成的列创建索引。
5.反范式设计
提示：建议先进行规范化的设计，这样将有助于了解系统，但MySQL对于多表连接的支持比较差，也就是优化器比较简单，往往为了性能，我们需要考虑一些反规范化的设计。
反范式的一些方法包括但不限于如下几点：
合并表。
冗余列减少连接。
引入重复组，例如，某公司有5个电话号码，我们不必使用额外的电话表，而是增加5个列telNO1、telNO2、telNO3、 telNO4、telNO5（此种情况一般用于重复组的项的数量不多且不易变化）。 ·
创建统计表。
水平/垂直分区。
注意：反范式增加了维护数据一致性的成本，因此需要谨慎实施。
6.列出最终表的详细说明
只需要列出重要的表即可。
以下索引是否建立、数据量及数据增长的情况要根据具体的业务需求来确定。
记录数：记录数，可补充说明未来半年、1年或2年的记录数。
增长量：单位时间的数据增长量。如果量大可以按每天；如果量不大则可以按每月。
表字段的区别度：主要是考虑到将来在此字段上建立索引类型选择时作参考，当字段值唯一时可以不考虑；当字段值不唯一时，估算一个区别度，近似即可。
例如：如果一个表的NAME字段共有2000个值，其中有1999个不同的值，那么 1999/2000=0.99越接近1区别度则越高，反之区别度就越低。
表的并发：根据具体的业务需求预测表的并发访问，或者说明高峰期的并发程度。
最终表的模板如表4-13所示。
实际设计中，如果表很多，只需要列出最重要、最关键的表设计即可。

4.6 导入导出数据
研发人员往往需要从数据库中导出数据，或者将数据导入到数据库中。
一些客户端工具提供了简单方便的功能，让研发人员可以不用去熟悉命令行工具mysql、mysqldump即可进行操作，
但客户端工具对于数据的导出导入可能存在兼容性的问题，而原生的命令行工具往往具有更好的兼容性。
客户端工具也可能会受到环境的限制而不能使用，所以，研发人员有必要掌握一些常用的命令行操作数据的方式。
我们在日常升级操作中，往往也需要提供一些命令让DBA运行，从而把数据导出来给研发、测试人员做二次处理。
熟悉导出导入数据的命令也有助于研发、测试人员自己方便地获取数据而不需要通过DBA。
一些统计分析脚本也依赖于调用mysql命令行工具实现数据的操作。
MySQL提供了好几种导出导入数据的方法：LOADDATA、mysqlimport、SELECT…INTO OUTFILE、mysqldump、mysql。
其中，mysqldump和mysqlimport是相反的操作，SELECT…INTO OUTFILE和LOAD DATA INFILE是相反的操作。
注意：在使用LOAD DATA或SELECT…INTO OUTFILE命令的时候，要留意操作系统文件的权限。
你需要确保 MySQL实例进程的拥有者对操作系统文件拥有权限。
4.6.1 规则简介
1.文本文件里的特殊字符处理
LOAD DATA和SELECT…INTO OUTFILE、mysqlimport和mysqldump有一组专门的用来处理文本文件中特殊字符的选项，具体如下所示。
FIELDS TERMINATED BY 'fieldtermstring'：各列（字段）之间用什么字符分隔，默认是tab，一般设置为逗号“,”。
[OPTIONALLY]ENCLOSEDBY'char'：值被什么字符引起来，一般设置为引号'"'，如果指定了OPTIONALLY，则 ENCLOSEDBY 'char'只对字符串数据类型（比如CHAR、BINARY、TEXT或ENUM）生效。
ESCAPED BY 'escchar'：定义转义字符，默认是“\”。
LINES TERMINATED BY 'linetermstring'：定义行结束符，用于分隔行。在Windows下需要使用“\r\n”提供一次换行，而在Linux下只需要“\n”就可以了。
2.文本文件的数据格式
所有命令都要求有关的文本文件必须严格遵守一种数据格式，具体如下所示。
数值：可以用科学计数法。
字符串：字符串里的特殊字符必须加上反斜线字符作为识别标志，以区别于各种分隔符。日期按照2005-12-21格式的字符串来对待，时间值按照23:59:59格式的字符串来对待，时间戳按照20051231235959格式的整数来对待。
NULL值：假设“\”作为转义前导字符，“'”作为字符串的前后缀标记，那么在导出操作中，NULL值将被表示为\N；在没有指定转义前导字符的导出操作中，NULL值将被表示为由4个字符构成的字符串。
在指定了转移前导字符的操作中，MySQL将把 NULL、\N、'\N'都解释为NULL值，但'NULL'将被解释为一个字符串'NULL'。
4.6.2 使用mysqldump导出，使用mysql导入
虽然mysqldump速度较慢，但这种方式有最好的兼容性，这也是目前使用最为广泛的备份数据的方式。
使用mysqldump导出的一般是SQL文件，也称为转储文件或dump文件，我们可以使用客户端工具mysql执行这个文件，导入数据，示例如下。
1）导出指定的表。 mysqldump test --tables test1 test4 > test1_test4.sql
2）分别导出sql文件和数据文件（数据值以tab分隔）。 mysqldump --tab=/home/garychen/tmp test
3）分离导出sql文件和数据文件（定制数据格式，数据值以逗号分隔） mysqldump --tab=/home/garychen/tmp --fields-terminated-by=',' --fields-enclosed-by=''' test
4）导出某个库。 mysqldump --complete-insert --force --add-drop-database --insert-ignore --hex-blob --databases test > test_db.sql
代码说明如下：
--complete-insert：导出的dump文件里，每条INSERT语句都包括了列名。
--force：即使出现错误（如VIEW引用的表已经不存在了），也要继续执行导出操作（mysqldump会打印出错误，注释完 VIEW定义后继续后续的数据导出）。
--insert-ignore：生成的INSERT语句是INSERT IGNORE的形式，如果导入此文件，即使出错了也仍然可以继续导入数据（当作警告）。
例如，使用mysql执行SQL文件，插入与主键冲突的值，如果是INSERT，那么mysql会异常退出，并提示如下错误。 ERROR 1062 (23000) at line 28: Duplicate entry '1' for key 1
如果是INSERT IGNORE，那么mysql会忽略错误，继续插入后面的值。例如下面这些语句。
INSERT IGNORE INTO 't1' VALUES ('1'),('10'),('11'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
INSERT IGNORE INTO 't1' VALUES ('111'),('20'),('21'),('22'),('23'),('4'),('5'),('6'),('7'),('88'),('99');
两条INSERT语句，即使有重复键值，也仍然会插入后面的值，因此88、99仍然可以正常插入。
--databases：类似--tables，后面可以跟多个值。
--compatible=name：导出的文件和其他数据库更兼容（但不确保），name的值可以是ANSI、MYSQL323、MYSQL40、 POSTGRESQL、ORACLE、MSSQL、DB2、MAXDB、NO_KEY_OPTIONS、NO_TABLE_OPTIONS或NO_FIELD_OPTIONS。
5）导出所有的数据库。mysqldump --all-databases --add-drop-database > db.sql
6）导出xml格式的数据。 mysqldump -u root -p --xml mylibrary > /tmp/mylibrary.xml
如果有二进制数据，则要使用选项--hex-blob。
InnoDB若想获得一致性的数据库副本，则要启用选项--single-transaction。
mysqldump不能利用通配符导出多个表，表比较多的时候，可以先SELECT出要导出的表，如下语句即可查询到所有的表。
select group_concat(table_name SEPARATOR ' ') from information_schema.tables where table_schema ='db_name' and table_name like 'prefix%';
或者，可以采用如下方式将表名导出到一个文件。
mysql -N information_schema -e "select table_name from tables where table_name like 'prefix_%' " > tbs.txt
然后运行如下命令导出数据。 mysqldump db 'cat tbs.txt' > dump.sql
也可以忽略部分表，加上参数--ignore-table=db_name.tbl_name1、--ignore-table=db_name.tbl_name2。
mysqldump可以把警告和错误追加记录在文件中，加上参数--log-error=file_name即可。
如果使用mysqldump导出数据，可以考虑的优化的方式有如下5种。
选择I/O活动低的时候。
I/O分离（数据盘和备份盘I/O分离）。
输出到管道压缩（gzip）。
--quick跳过内存缓冲（--opt默认启用）。
从数据保留策略上想办法，把不需要修改的大量数据放到历史表中，而不是每次都备份。
mysqldump导出的SQL转储文件，可以用如下的形式将数据导入到数据库中。 mysql db_name < db_name.sql
转储文件（dump文件）里面一般指定了set names utf8，所以我们在导入的时候不再需要指定特殊的字符集。
例外的情况是，有一些特殊的场合，SQL文件是以其他的字符集导出的，这个时候导入要注意保持文件的字符集、客户端字符集和连接的字符集的一致性，
例如： mysql --default-character-set=charset_name database_name < import_table.sql
--default-character-set的意思是，客户端和连接都默认使用charset_name字符集。例如：
mysql --default-character-set=gbk < import_table.sql 这个文件的字符集是gbk。
如果mysql客户端输出的数据是乱码，那么请检查下客户端、连接的字符集配置。
例如，我们使用SSH工具securecrt登录主机，然后使用mysql命令行工具连接MySQL服务器，mysql连接的默认配置可能是latin1，那么此时显示utf8的数据将会是乱码。
这种情况下，可以在客户端运行set names utf8，并确认securecrt的字符编码是UTF-8，这样就可以正常显示utf8字符集的数据了。

4.6.3 使用SELECT INTO OUTFILE命令导出数据
如果想要进行SQL级别的表备份，可以使用SELECT INTO OUTFILE命令语句。对于SELECT INTO OUTFILE，输出的文件不能先于输出存在。
示例语句如下所示。
SELECT * INTO OUTFILE '/tmp/testfile.txt' FROM exporttable;
SELECT * INTO OUTFILE '/tmp/testfile.txt' FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '+' ESCAPED BY '!' FROM exporttable;
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;
一般来说，只要导出导入操作中使用的选项完全一致，用SELECT…INTO OUTFILE命令导出的文本文件就可以用LOAD DATA命令导入到数据表里去，不会发生任何变化。

4.6.4 使用LOAD DATA导入数据
SELECT...INTO OUTFILE可以筛选记录，导出表数据到一个文件中，而LOAD DATA INFILE则是相反的操作，是读取这个文件导入表中。
如果MySQL服务器和LOAD DATA命令不在同一台计算机上执行，当想导入本地文件系统的文件时，则需要使用语法变体 LOAD DATA...LOCAL INFILE...，
也就是说，如果指定LOCAL关键词，则表明从客户主机读文件。如果没指定LOCAL，那么文件必须位于服务器上。
可能是因为字符集设置而导致乱码的问题。LOAD DATA INFILE在某些MySQL的版本上不支持指定导入时的字符集。
这时，MySQL将假设导入文件的字符集是character_set_database，这个变量会根据当前数据库指定的字符集而变化；
如果没有指定当前数据库，那么它的值将由character_set_server决定。
因此如果LOAD DATA INFILE不支持指定字符集，那么在导入前需要确认当前数据库的字符集，如果与当前数据库的字符集不符，则使用SET character_set_database命令进行更改。
SET names命令也是可行的，或者直接在LOAD DATA INFILE命令里指定字符集，例如如下语句。
mysql> load data infile '/tmp/t0.txt' into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`description`) set update_time=current_timestamp;
其他示例如下。
示例1： LOAD DATA INFILE '/path/to/file' INTO TABLE table_name FIELDS TERMINATED BY '\t' ENCLOSED BY '\'' LINES TERMINATED BY '\n'
示例2： LOAD DATA INFILE '/path/to/file' REPLACE INTO TABLE table_name FIELDS TERMINATED BY '\t' ENCLOSED BY '\'' LINES TERMINATED BY '\n'
示例3：导入csv格式的文本文件。csv格式的文件，即逗号分隔的数据文件。
首先，生成如下csv文件。
mysql> select field_list from table_name into outfile '/home/garychen/tmp/table_name_2.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';
然后，截断表，清空数据，命令如下。
mysql> truncate table table_name;
最后，进行验证，可以看到，原来导出的文件，现在可以正常导入到数据表中，语句如下。
mysql> load data local infile '/home/garychen/tmp/table_name_2.csv' into table table_name fields terminated by ',' lines terminated by '\n'(field1,field2,field3);
LOAD DATA的优化
相较于普通的mysql命令，LOAD DATA执行SQL文件导入的方式要快得多，一般可以达到每秒几万条记录的插入速度。
有时对于大表，我们仍然期望获得更高的导入速度，以下将针对InnoDB和MyISAM表分别叙述如何进行优化。
对于InnoDB的优化，建议的方式如下。
将innodb_buffer_pool_size设置得更大些。
将innodb_log_file_size设置得更大些，如256MB。
设置忽略二级索引的唯一性约束，SETUNIQUE_CHECKS=0。
设置忽略外键约束，SET FOREIGN_KEY_CHECKS=0。
设置不记录二进制日志，SET sql_log_bin=0。
按主键顺序导入数据。由于InnoDB使用了聚集索引，如果是顺序自增ID的导入，那么导入将会更快，我们可以把要导入的文件按照主键顺序先排好序再导入。
对于InnoDB引擎的表，可以在导入前，先设置autocommit=0，例如如下语句。
truncate table_name; set autocommit = 0; load data infile /path/to/file into table table_name... commit;
可以将大的数据文件切割为更小的多个文件，例如使用操作系统命令split切割文件，然后再并行导入数据。
对于MyISAM的优化，建议的方式如下。
将bulk_insert_tree_size、myisam_sort_buffer_size、key_buffer_size设置得更大些。
先禁用key（ALTERTABLE…DISABLEKEYS），然后再导入数据，然后启用key（ALTERTABLE…ENABLEKEYS）。
重新启用key后，可以批量重新创建索引，批量创建索引的效率比在逐笔插入记录时创建索引要高效得多。
注意ALTER TABLE… DISABLE KEYS禁用的只是非唯一索引，唯一索引或主键是不能禁用的，除非你先手动移除它。
使用LOAD DATA INFILE，tab分隔的文件更容易解析，比其他方式更快。
由于唯一索引（约束）对于我们导入数据的影响比较大，尤其对于大表导入，我们需要留意这一点。
不要在大表上创建太多的唯一索引，主键、唯一索引不要包含太多列，否则导入数据将会很慢。
关于优化导入数据的方式，见仁见智，其实一次INSERT插入多条记录，控制每个表的大小（<15GB，确保B-tree索引在内存中），并发导入，批量事务等方式都有好处，但更多的时候也要考虑维护的简单方便。
如果有很多表，那么使用mysqldump会更简单。如果是导入个别大表，而且对于时间有很高的要求，那么LOADDATA未尝不可。
mysqldump默认的导出文件，其实已经包含了一些优化了，会有禁用key、启用key的操作，而且是一条INSERT语句包括多行记录的。

4.6.5 用mysqlimport工具导入
mysqlimport命令的语法格式如下。 mysqlimport databasename tablename.txt
示例如下。 mysqlimport --local test imptest.txt

4.6.6 用mysql程序的批处理模式导出
有时可以考虑使用mysql工具导出数据，特别是远程操作的时候，下面来看几个示例。
示例1：导出authors表。 mysql -u root --password=123456 --batch --default-character-set=utf8 -e "SELECT * FROM authors;" mylibrary > output.txt
示例2：查询结果的纵向显示。 mysql -u root --password=123456 --vertical '--execute=SELECT * FROM titles;' mylibrary > test.txt
示例3：生成html表格形式的输出。 mysql -u root -p=xxx --html '--execute=SELECT * FROM titles;' --default-character-set=latin1 mylibrary > test.html
示例4：用mysql程序生成xml格式的输出。 mysql -u root -p -xml -default-character-set=utf8 '-execute=SELECT * FROM titles;' mylibrary > C:\test.xml

4.6.7 用split切割文件，加速导入数据
split命令的作用是切割文件，语法格式如下所示。 split [OPTION] [INPUT [PREFIX]]
如果不加入任何参数，默认情况下是以1000行的大小来分割的。
下面来看个案例，使用split切割导出的数据文件，这些数据文件需要通过PHP脚本解析二次处理后，再插入MySQL数据库，示例如下。
split -l 5052000 subs.txt test_split_sub_
其中，-l参数指定按多少条记录切割文件。
这里将按照每5052000条记录进行切割，生成的文件名以test_split_sub_为前缀，生成的文件名类似如下。
test_split_sub_aa test_split_sub_ab test_split_sub_ac …
然后就可以并发执行多个PHP客户端程序来导入数据了。

4.7 事务和锁
4.7.1 概述
我们知道，数据库是一个多用户访问系统，因此需要一种机制来确保当多个用户同时读取和更新数据时，数据不会被破坏或失效，锁就是这样的一种并发控制技术。
当一个用户需要修改数据库中的记录时，首先要获取锁，只有这样该用户在锁的持有期间，其他用户就不能对这些记录进行修改了。
不同的数据库产品实现的锁机制各不相同，而锁定的程度也会受到事务隔离级别的影响。
不同的数据库产品实现锁的方式各不一样，即使是MySQL，不同版本之间也可能存在差异。
本节只是介绍锁的一般表现形式，对于具体的锁定细节，请读者自行参考相关资料并验证。
MySQL Server级别的锁大致有如下两种。
（1）Table locks（表锁）
mysql> LOCK TABLES table_name READ;
mysql> SELECT SLEEP(30) FROM table_name LIMIT 1;
（2）Global locks（全局锁）
mysql> FLUSH TABLES WITH READ LOCK;
Name locks mysql> RENAME TABLE table_name TO table_name2;
String locks mysql> SELECT GET_LOCK('my lock', 100);
下面将首先简单介绍MyISAM表的锁技术，生产环境中使用MyISAM的场景很少，所以这里只是介绍下基本原理和可能会碰到的问题。
然后再着重介绍下InnoDB事务及与事务相关的锁定技术。

4.7.2 MyISAM的表锁
MySQL支持对MyISAM和MEMORY表进行表级锁。下面来看看表锁定的原理。
对于WRITE，MySQL使用的表锁定方法原理如下：如果在表上没有锁，则在它上面放一个写锁；否则，把锁定请求放在写锁定队列中。
对于READ，MySQL使用的锁定方法原理如下：如果在表上没有写锁定，则把一个读锁定放在它上面；否则，把锁定请求放在读锁定队列中。
当一个锁定被释放时，锁定可先被写锁定队列中的线程得到，然后是读锁定队列中的线程。
这就意味着，如果你在一个表上有很多更新，那么SELECT语句将等待直到没有更多的更新操作为止。
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺。
对于MyISAM引擎的表，如果INSERT语句不会发生冲突，则可以在其他客户正在读取MyISAM表的时候插入行。
如果数据文件中不包含空闲块，则不会发生冲突，因为在这种情况下，记录总是插入在数据文件的尾部（从表的中部删除或更新的行可能会导致空洞）。
如果有空洞，那么当所有空洞都填入新的数据时，并行的插入就能够重新自动启用。
表锁定将会使很多线程同时从一个表中进行读取操作，但是如果某个线程想要对表进行写操作，那么它必须首先获得独占访问。
更新期间，所有其他想要访问该表的线程必须等待，直到更新完成。
如下是需要注意的特殊的表锁机制。
如果一个客户发出了长时间运行的查询（SELECT），而此时，另一个客户想要对同一个表进行更新（UPDATE），那么该客户必须等待直到SELECT完成。
如果此时还有一个客户对同一个表也发出了另一个SELECT语句，因为UPDATE比SELECT的优先级高，那么该SELECT语句将会等待直到UPDATE完成，并且它们都要等待第1个SELECT完成。
性能问题往往发生在这个步骤。
以上的机制，在很多基于MyISAM引擎表的程序中可能会导致严重的性能问题，比如一些论坛程序。
建议的解决方案是设置变量-low-priority-updates=1，即可以在系统级别进行设置，以避免SELECT查询线程大量累计。
一些公司采用了MyISAM作为统计库，为了加速，往往在批量更新数据的时候设置了并发，但由于并发更新时频繁的表锁竞争，更新数据的速度反而会下降。
可以使用LOCK TABLES来提高速度，因为在一个锁定中进行很多更新比没有锁定的更新要快得多。
将表中的内容切分为几个小表也可以有所帮助。
LOCK TABLES的一些表现如下，读者可以自行验证。
LOCK TABLES t1 READ; 表示其他会话可读，但不能更新。
LOCK TABLES t1 write; 表示其他会话不可读，不可写。
UNLOCK TABLES; 表示释放锁。
4.7.3 事务定义和隔离级别
事务是数据库管理系统执行过程中的一个逻辑单元，由有限的操作序列构成。
1.事务的ACID特性
并非任意的对数据库的操作序列都是数据库事务。数据库事务拥有以下4个特性，习惯上被称为ACID特性。
（1）原子性（Atomic）事务作为一个整体被执行，包含在事务中的对数据库的操作要么全部被执行，要么全部都不执行。
比如，InnoDB支持事务，在InnoDB事务内如果执行了一条插入多个值的INSERT语句“INSERT INTO t VALUES('b1'),('b2'), ('b3'),('b4'),('b5'),('b6');”只要其中一个值插入失败，那么整个事务就失败了。
而对于MyISAM引擎的表，它不支持事务，那么在出错之前的值是可以被正常插入到表中的。
（2）一致性（Consistency）事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足约束。
（3）隔离性（Isolation）多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
（4）持久性（Durability）已被提交的事务对数据库的修改应该被永久保存在数据库中。
2.事务的隔离级别
事务隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也会越大。
MySQL事务包含如下4个隔离级别，按隔离级别从低到高排列如下。
（1）read uncommitted（dirty read）
read uncommitted也称为读未提交，事务可以看到其他事务更改了但还没有提交的数据，即存在脏读的情况。
（2）read committed
read committed也称为读已提交，事务可以看到在它执行的时候，其他事务已经提交的数据，已被大部分数据库系统采用。
允许不可重复读，但不允许脏读，例如如下语句。
begin transaction;
select a from b where c=1;
... #其他事务更改了这条记录 ,并且 commit提交
select a from b where c=1; #可以看到新的数据，不可重复读
end
（3）repeatableread
repeatableread也称为可重复读。同一个事务内，同一个查询请求，若多次执行，则获得的记录集是相同的，但不能杜绝幻读，示例如下。
begin transaction
select a from b where c=1;
... #其他事务更改了这条记录 ,并且 commit
select a from b where c=1; #仍然看到旧的数据 ,可重复读 ,但不能杜绝幻读
end
发生幻读的场景有，某事务A按某个条件进行查询，此时尚未提交。然后另一个事务成功插入了数据。事务A再次查询时，可能会读取到新插入的数据。
MySQL InnoDB引擎默认使用的是repeatableread（可重复读）。
当事务A发出一个一致性读之时，即一个普通的SELECT语句，InnoDB将给事务A一个时间点。
如果另一个事务在该时间点被指定之后删除一行并提交，则事务A看不到该行已被删除。插入和更新的处理与此相似。
可以通过提交事务来前进时间点，然后进行另一个SELECT。
这被称为多版本并发控制（multi- versioned concurrency control）。
如果想要查看数据库的最新状态，应该用READCOMMITTED隔离级别或用一个锁定读“SELECT * FROM t LOCK IN SHAREMODE;”。
为了满足可重复读，事务开启后，对于要查询的数据，需要保留旧的行版本，以便重新查询，这在一些特殊的环境中可能会导致某些问题，
比如一些框架，对于任何操作，都要先进入AUTOCOMMIT=0的模式，直到有写入时才会进行COMMIT提交，这可能会导致事务数过多，
有时由于框架或编码的不完善，可能会出现长时间不提交的事务，导致UNDO保留的旧的数据记录迟迟不能被删除，还可能导致UNDO空间暴涨。
对于这些极端情况，首先应该考虑调整应用，实在没有办法的话，可以考虑将事务的隔离模式更改为read committed。
（4）serializable
serializable也称为序列化，最高级别的锁，它解决了幻读，它将锁施加在所有访问的数据上。
该锁将把普通的SELECT语句默认改成SELECT…LOCK IN SHAREMODE。即为查询语句涉及的数据加上共享琐，阻塞其他事务修改真实数据。
如下的命令语句可查询当前的事务隔离级别。 mysql> show variables like '%tx%';
或者 mysql> SELECT @@global.tx_isolation, @@session.tx_isolation;
设置事务隔离级别的语法格式如下。 SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
在配置文件内修改mysqld节的transaction-isolation参数的方式如下。
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
注意：如上配置文件transaction-isolation选项的级别名中有连字符，但SET TRANSACTION语句的级别名中则没有连字符。
不建议更改InnoDB的事务隔离级别。一些传统的商业数据库，如Oracle，使用了类似read-commited的隔离级别。
但由于绝大部分场景下，MySQL的用户都使用默认的隔离级别repeatable read，此隔离级别下的使用验证会比其他隔离级别完善得多，官方可能也不会对非默认隔离级别进行充分的验证，或者存在不完善支持的行为。

4.7.4 InnoDB的行锁
1.概述
一般来说，我们没有必要针对InnoDB引擎的表使用LOCK TABLES锁定记录。正常情况下，使用InnoDB支持的行锁技术就能够处理绝大部分场景。
行级锁定的优点如下：
当在很多线程中访问不同的行时只存在少量锁定冲突。
回滚时只有少量的更改。
可以长时间锁定单一的行。
数据库的锁定技术往往是基于索引来实现的，InnoDB也不例外。如果我们的SQL语句里面没有利用到索引，那么InnoDB将会执行一个全表扫描，锁定所有的行（不是表锁）。
锁过多的行，增加了锁的竞争，降低了并发率，所以建立索引是很重要的，InnoDB需要索引来过滤（在存储引擎层中）掉那些不需要访问的行。
这里举例说明如下。
mysql> SET AUTOCOMMIT=0;
mysql> BEGIN;
mysql> SELECT actor_id FROM sakila.actor WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;
mysql> EXPLAIN SELECT actor_id FROM sakila.actor WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;
如上案例所示，EXPLAIN输出的执行计划里type为range，即索引范围查找，MySQL会锁定1~4行，而不是2~4行，为什么呢？
因为InnoDB存储引擎的优化器会忽略最后一个范围查找之后的条件（即条件actor_id<>1），
所以对于查询“SELECT actor_id FROMsakila.actor WHERE actor_id<5 ANDactor_id<>1 FOR UPDATE;”还锁定了actor_id=1的行。
即MySQL执行的计划是InnoDB存储层先进行索引范围查找，扫描了1、2、3、4行的记录，然后才返回给MySQL Server层，
Server层再用WHERE条件去过滤掉行1的记录（注意EXPLAIN执行计划里的“Using where”），MySQL Server层并没有告诉InnoDB引擎需要过滤掉行1的记录。

2.几种行锁技术
InnoDB有几种不同类型的行锁技术，如记录锁（record lock）、间隙锁（gap lock），和next-key锁。
记录锁（index-rowlocking）：这是一个索引记录锁。它是建立在索引记录上的锁，很多时候，扫描一个表，由于无索引，往往会导致整个表被锁住，建立合适的索引可以防止扫描整个表。
间隙锁：这是施加于索引记录间隙上的锁。
next-key锁：记录锁加间隙锁的组合。也就是说next-key锁技术包含了记录锁和间隙锁。
有时在开发过程中我们会发现，在INSERT的时候会锁定相邻的键。其实这是一个next-key锁技术。MySQL使用这个技术来避免幻读。
当同一查询在不同时间产生不同的结果集时，在事务内发生所谓的幻读。例如，如果SELECT执行两次，但第二次返回第一次未返回的行，则该行为“幻影”行。
MySQL默认的是repeatable read，但更进一步，它使用next-key锁来防止发生幻读现象。
例如，对于语句“SELECT * FROM child WHERE id>100 FOR UPDATE;”，如果child表内有id=90、id=102，
那么gap就是90–102 了，锁住这个gap，才能防止在你的事务执行期间，其他用户插入id=101的记录，造成幻读。
当然，你所在的当前事务是允许插入id=101的记录的，这样其实变通实现了唯一性的检查。
如果需要禁用next-key锁，可以设置事务隔离级别为read committed级别，或者设置参数innodb_locks_unsafe_for_binlog=1。
在开发数据库程序的时候必须要清楚的一点，当我们执行数据操作的时候，很可能会导致间隙锁。由于间隙锁锁定的范围比较大，会导致可并发执行的事务数受到限制。
还有一点需要留意的是，next-key锁是为了防止发生幻读，而只有repeatable read及以上隔离级别才能防止幻读，所以在read committed隔离级别下面没有next-key锁这一说法。

3.等待行锁超时
有时我们在慢查询日志中会看到一些很耗时的查询，但单独执行却很快，此时有可能就是因为该查询因等待InnoDB行锁而超时。
如下是生产环境的一个示例。
mysql> DESC tbl_rankings;
mysql> SHOW CREATE TABLE tbl_rankings \G
mysql> select * from tbl_rankings limit 30;
表tbl_rankings的rid列上创建了唯一索引。
下面新建两个会话，分别执行如下的操作。
会话1执行命令“update tbl_rankings set rid=0 where ranking=1;”，此时会锁住ranking=1的记录里的rid值索引（原来的rid=551915）。
该会话不允许其他的会话设置rid=551915，同样的，也不允许其他会话设置rid=0。
会话2运行命令“update tbl_rankings set rid=551915 where ranking=2;”，此时会话2会被阻塞，并且一直等待。在50s后超时并在慢查询日志里记录超时信息。
可以看到索引上有锁。 show innodb status \G;

4.MVCC简要介绍
单纯靠行级别的锁，是不可能实现好的并发性的，MySQL InnoDB还需要配合MVCC（Multi version Concurrency Control）技术来提供高并发访问。
在很多情况下MVCC可以不需要使用锁，即可实现更新数据时的无阻塞读。
在常用的事务隔离级别read committed和repeatable read级，都应用了MVCC技术。
InnoDB官方建议的默认的事务隔离级别是可重复读（repeatabl eread），意思是在同一个事务内，对于同一个查询请求，多次执行，获得的记录集是相同的。
这样，事务内的查询会看到一致性的数据，而不管它执行了多久，这一般是通过保存数据的快照来实现的。
MVCC会保存某个时间点上的数据快照。这就意味着事务可以看到一个一致的数据视图，不管它们还需要运行多久。
这同时也意味着不同的事务在同一个时间点看到的同一个表的数据可能是不同的。
具体的更详细的介绍，请参考官方文档。

4.8 死锁
死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法进行下去。
我们可以用图4-3来说明死锁的形成。
图4-3中，进程P1、P2都需要申请额外的资源，P1持有资源R2，需要申请资源R1，P2持有资源R1，需要申请资源R2，此时就会形成一个闭环，两个进程都无法继续运行。
理论上，产生死锁有4个必要条件。
禁止抢占（no preemption）
持有和等待（hold and wait）
互斥（mutualexclusion）
循环等待（circular waiting）
预防死锁就是至少破坏这4个条件中的一项，即破坏“禁止抢占”、“持有等待”、“资源互斥”或“循环等待”。
实践中，处理死锁的方法大致分为两种。既可以检测死锁并进行修复，也可以对事务进行管理，使死锁永远都不可能形成。
当存在死锁时，对该状态进行修复以使所有涉及的事务都能继续执行通常是不可能的。
因此，至少其中的一个事务必须终止并重新开始。
InnoDB会自动检测死锁。
据官方文档可知，目前InnoDB处理死锁的机制是：发现有循环等待的现象，立即回退（rollback）开销更小的事务，也就是插入、修改、删除了更少记录的事务。
对于MySQL死锁的解决，通常有如下方法。
经常提交你的事务。小事务的锁冲突更少。
以固定的顺序访问你的表和行。这样事务就会形成定义良好的查询并且没有死锁。
将精心选定的索引添加到你的表中。这样你的查询就只需要扫描更少的索引记录，并且因此也可以设置更少的锁定。
不要把无关的操作放到事务里面。
在并发比较高的系统中，不要显式加锁，特别是在事务里显式加锁。如SELECT…FOR UPDATE语句，如果是在事务里（运行了START TRANSACTION或设置了autocommit等于0），那么就会锁定所查找到的记录。
尽量按照主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外的计算工作。比如有些读者会用到“SELECT…WHERE…ORDER BY RAND();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
优化SQL和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂SQL分解为多个简单的SQL。

4.9 其他特性
4.9.1 临时表
临时表指的是CREATE TEMPORARY TABLE命令创建的临时的表，临时表只对当前连接可见，对其他连接不可见，结束连接或中断，数据表（数据）将丢失。
也就是说，在短连接的情况下，断开连接后，这个表就自动删除了。如果是长连接的话，则需要自己先初始化下表。
我们常使用临时表来存储一些中间结果集，如果需要执行一个很耗资源的查询或需要多次操作大表，那么把中间结果或小的子集放到一个临时表里，可能会有助于加速查询。
创建了临时表之后，如果运行SHOW TABLES、SHOW OPEN TABLES、SHOW TABLE STATUS命令及在 INFORMATION_SCHEMA库中都将看不到临时表，这不是Bug，而是设计就是如此。
临时表支持多种存储引擎，如HEAP、MyISAM、InnoDB，当设置ENGINE=HEAP时，就会具有内存表的属性，即表的大小超过max_heap_table_size时就会报错。
我们需要注意的是，在已有的内存表上设置该变量是没有效果的，除非用CREATE TABLE、ALTERTABLE、TRUNCATE TABLE等语句重新创建表。当然，重启也是可以生效的。
MySQL临时表也有一些限制。
比如不能用RENAME来重命名一个临时表，可以用ALTER TABLE来代替。
比如，在同一个查询语句中，你只能查找一次临时表。
临时表的详细使用方法和相关限制请参考官方文档。

4.9.2 分区表
分区表是商业数据库的一项高级技术，MySQL从5.1版开始也支持分区表，分区表技术允许按照设置的规则，跨文件系统分配单个表的多个部分。
实际上，表的不同部分在不同的位置被存储为单独的表。
用户所选择的、实现数据分割的规则被称为分区函数，在MySQL中它可以是模数，或者是简单地匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或者是一个线性HASH函数。
以笔者使用分区表的经验来看，分区表一直不太成熟，据说在MySQL 5.6以后才趋向成熟稳定，所以，不要轻易将分区表应用于生产环境。
如下命令将确定MySQL是否支持分区。 mysql> SHOW VARIABLES LIKE '%partition%';
可使用EXPLAIN命令查看是否过滤掉了不需要查询的分区，如“mysql>EXPLAIN PARTITIONS SELECT*FROMtrb1\G”。
MySQL 5.1有如下的一些分区类型，RANGE分区、LIST分区、HASH分区、KEY分区和子分区。常用的存储引擎，如 InnoDB、MyISAM、MEMORY都支持分区表。
RANGE分区的表是通过如下这种方式进行分区的，基于一个连续区间的列值，把多行分配给分区，例如某个时间段的值属于某个分区，某个数值范围的值应该属于某个分区。
LIST分区中每个分区的定义和选择是基于值列表的，而RANGE分区是从属于一个连续区间值的集合的。每个分区都必须明确定义。
HASH分区是基于用户定义的表达式的返回值选择分区。它主要用来确保数据在预先确定了数目的分区中是平均分布的。
在RANGE分区和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL将自动完成这些工作，
你所要做的只是为将要被散列的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
按照KEY进行分区类似于按照HASH进行分区，除了HASH分区使用的是用户自定义的表达式，而KEY分区的散列函数是由MySQL服务器提供的。
子分区是分区表中每个分区的再次分割。
以下是一些MySQL 5.1分区表的操作示例。
创建一个RANGE分区表，语句如下。
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) )
( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005) );
RANGE分区和LIST分区的操作示例如下。
（1）删除分区（需要DROP权限） ALTER TABLE tr DROP PARTITION p2;
如果需要调整分区，但不想丢失数据，那么可以重整分区。 ALTER TABLE ... REORGANIZE PARTITION;
（2）增加分区
对于RANGE分区，只能从分区列表的最高端开始增加。
例如，对于如下的表使用ALTER TABLE…ADD PARTITION命令添加分区。
CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE )PARTITION BY RANGE( YEAR(dob) )
( PARTITION p0 VALUES LESS THAN (1970), PARTITION p1 VALUES LESS THAN (1980), PARTITION p2 VALUES LESS THAN (1990) );
#增加一个分区。 ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
如果要加入1960分区则会报错。 mysql> ALTER TABLE members ADD PARTITION ( PARTITION n VALUES LESS THAN (1960)); #将报错 .
可以增加多个分区，例如：
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, hired DATE NOT NULL )
PARTITION BY RANGE( YEAR(hired) )
( PARTITION p1 VALUES LESS THAN (1991), PARTITION p2 VALUES LESS THAN (1996), PARTITION p3 VALUES LESS THAN (2001), PARTITION p4 VALUES LESS THAN (2005) );
ALTER TABLE employees ADD PARTITION ( PARTITION p5 VALUES LESS THAN (2010), PARTITION p6 VALUES LESS THAN MAXVALUE );
（3）调整分区
如果想要调整分区，比如在分区列表中加入一个分区，或者忘记增加分区了，所有的数据都落入了最后一个分区，这时想重新定义最后的分区，那么你可以使用重整分区的功能。
ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION n0 VALUES LESS THAN (1960), PARTITION n1 VALUES LESS THAN (1970) );
（4）合并分区
还可以合并分区，注意，对于RANGE分区，合并的分区必须是相邻的分区。
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (PARTITION p0 VALUES LESS THAN (1970) );
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS THAN (1980), PARTITION m1 VALUES LESS THAN (2000) );
对于LIST分区，如果新加分区中的元素和旧的分区有冲突，那么可以先添加分区（只有没有冲突的元素），然后重整分区。
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) );
（5）重建分区（rebuilding partition）
相当于删除所有的数据，再INSERT所有的数据，整理碎片可用重建分区，语句如下。
ALTER TABLE t1 REBUILD PARTITION p0, p1;
（6）优化分区（optimizing partition）
如果某个分区中删除了大量数据，或者频繁修改了表（有可变字段），那么可以考虑优化该分区，语句如下。
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
（7）分析分区（analyzing partition）
如下这个命令将分析分区的key分布信息。 ALTER TABLE t1 ANALYZE PARTITION p3;
（8）检查分区（checking partition）
检查表，如果坏了，则用REPAIR命令修复，语句如下。 ALTER TABLE trb3 CHECK PARTITION p1;
（9）修复分区（repairing partition）
修复分区的语句如下。 ALTER TABLE t1 REPAIR PARTITION p0,p1;
如果需要对所有分区进行操作，那么可加入All关键字，语句如下。 mysql> ALTER TABLE hotspace_0 ANALYZE PARTITION ALL;
MySQL 5.1 RANGE分区有如下一些注意事项：
同一个分区表中的所有分区必须使用同一个存储引擎，并且存储引擎要和主表的存储引擎保持一致。
有MAXVALUE值之后，直接加分区是不可行的。
RANGE的分区方式在加分区的时候，只能从最大值的后面添加，而在最大值的前面不可以添加。
分区健必须包含在主键里面。
如上列了一些常用的分区表操作，主要是基于MySQL 5.1的版本，MySQL分区表的技术在不断发生改变，而且不同版本的变化也比较大，一些限制和弱点不断地在新的版本中取消或完善，如果大家要使用分区表，建议参考官方文档，采用合适的方法。
分区包括如下一些优点：
与单个磁盘或文件系统分区相比，可以存储更多的数据。表分区物理上被存储为单独的表，所以可以把分区存储到不同的磁盘或文件系统中。
在现实生产环境中，这样使用还是比较少见的。选择分区表更常见的是基于业务的需要，是否能够更高效地查询数据和维护数据。
对于那些已经失去了保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除掉那些数据。
一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用再查找剩余的其他分区了。
分区表也有如下一些不足之处：
MySQL的分区表不像Oracle那么灵活和成熟可靠，也不像Oracle那样可以有全局的索引，MySQL的索引对于每个表来说都是单独的。这样如果有跨越多个分区的查找，那么效率可能就会有问题。
一般来说，系统设计人员在碰到一些有“分区”特征的数据时，可能就会倾向于分区，比如一些按时间记录的流水账，这种想法本身并没有错，
但是需要明白的是，分区表不能跨越MySQL的实例，也就是说不能超过单机，扩展性仍然有限，而且由于分区表的不成熟，可能会给整个系统带来隐患。
这里有一些通用的建议：
1）只有大表才可能需要分区，几百万笔记录的表并不算大，对于一些高配置的数据库主机，几千万甚至上亿条数据的表也不算大。
2）分区数不能过多，很难想象大于500的分区数。
3）查询的时候，不要跨越多个分区，建议最多跨越1~2个分区。
4）索引的列应该是分区的列，或者有其他条件限制的分区，否则访问所有分区上面的索引进行查找，开销会比较大。
笔者个人不推荐在生产环境中使用分区表，基于的理由如下：
1）就目前的生产环境来说，分区表还只是一项不是很成熟的技术：据官方发布的Bug升级记录可知，5.1、5.5长期以来修复了很多Bug。
虽然Oracle公司也在不断完善分区表，官方宣称在MySQL 5.6已经成熟了很多，但如果要使用分区表，仍然建议事先经过充分的测试和验证。
2）目前已知的官方5.1版本的内存分配机制有一定的问题，有内存碎片，笔者曾经发现在生产环境里使用了分区表的实例，内存会不断上升。
3）MySQL分区表的管理性、可维护性还存在一些问题。如果数据不能单独分布在一两个有限的分区内，那么查询性能往往会更差。因为扫描多个分区将比扫描原来的一张表慢得多。
4）使用分区表往往需要更多的技术考虑，需要更多的经验，且不一定适合未来的业务需求。
5）一般从应用层分表是很成熟的技术，各种大型项目中更多的是从应用层分片数据。

4.9.3 存储过程、触发器、外键
1.存储过程/函数
MySQL在MySQL 5.0版之后支持存储过程。
存储程序和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。
（1）存储过程的使用
由于存储过程包含多个语句，因此需要在MySQL客户端使用另外的分隔符，语句如下。
DELIMITER //
DELIMITER $$
CREATE PROCEDURE p1 () SELECT * FROM t; //
声明的变量，如果没有DEFAULT子句，那么变量的值默认为NULL，如下例中a变量的默认值即为NULL。
CREATE PROCEDURE p10 ()
BEGIN
DECLARE a, b INT DEFAULT 5;
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; //
作用域BEGIN…END之内的声明离开作用域就失效了，例如如下的语句。
mysql> DELIMITER //
mysql> CREATE PROCEDURE p11 ()
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'outer';
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'inner';
SELECT x1;
END;
SELECT x1;
END; //
mysql> DELIMITER ;
mysql> call p11()；
显示的值将是outer。
存储过程的name不区分大小写，可以使用databasea_name.procedure_name来调用。
存储过程支持常见的控制体结构，比如IF语句、WHEN条件分支语句、WHILE…DO循环语句。
IF语句的示例如下。
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
CASE…WHEN语句的示例如下，满足条件值后执行相应的分支语句。
CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN INSERT INTO t VALUES (17);
WHEN 1 THEN INSERT INTO t VALUES (18);
ELSE INSERT INTO t VALUES (19);
END CASE;
END; //
WHILE…DO语句的示例如下，满足条件后执行相应的循环体。
CREATE PROCEDURE p14 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //
REPEAT…UNTIL语句的示例如下，首先执行循环体，再判断条件，即至少执行相应的一次。
CREATE PROCEDURE p15 ()
BEGIN
DECLARE v INT;
SET v = 0;
REPEAT
INSERT INTO t VALUES (v);
SET v = v + 1;
UNTIL v >= 5 #注意后面没有分号。
END REPEAT;
END; //
此外，还支持标号，示例如下。
CREATE PROCEDURE p16 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END; //
以下是综合上述控制体的一个示例。
CREATE PROCEDURE p21 (IN parameter_1 INT, OUT parameter_2 INT) LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER
BEGIN
DECLARE v INT;
start_label: LOOP
IF v = v THEN LEAVE start_label;
ELSE ITERATE start_label;
END IF;
END LOOP start_label;
REPEAT
WHILE 1 = 0 DO BEGIN END;
END WHILE;
UNTIL v = v END REPEAT;
END;//
SQL SECURITY特征可以用来指定子程序是用创建子程序者的许可权限来执行，还是使用调用者的许可权限来执行。默认值是DEFINER。
SQL SECURITY DEFINER：按创建存储过程的用户的许可权限来执行。
SQL SECURITY INVOKE：按调用者的许可权限来执行。
不能在存储过程中再执行一些更改存储过程的操作，比如CREATE PROCEDURE、ALTER PROCEDURE等。
如下是一个创建存储过程的例子。
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END //
mysql> delimiter ;
mysql> CALL simpleproc(@a);
mysql> SELECT @a;
再来看下面这个例子。
CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1); /* statement */
END /* end of block */
存储过程的实际定义是存放在系统表mysql.proc中的，所以查看或备份存储过程也可以针对这个表来进行。
创建存储过程的时候可以保存sql_mode。如下示例将演示ansi模式。
mysql> set sql_mode='ansi';
mysql> select 'a'||'b';
mysql> set sql_mode='';
mysql> select 'a'||'b';
mysql> show warnings;
ansi模式包含一些组合，比如REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、ANSI等。
下面将实际创建一个存储过程，并查看是否保存了sql_mode，命令如下。
mysql> set sql_mode='ansi' //
mysql> create procedure p3()select'a'||'b'// mysql> set sql_mode=''//
mysql> call p3()//
可以看到，在创建存储过程的时候，存储过程定义中保存了sql_mode的。所以虽然后来又设置了sql_mode，但是存储过程不会受到影响。
我们可以运行命令SHOW CREATE PROCEDURE procedure_name来查看创建存储过程的代码，里面有sql_mode的信息。
以上对于存储过程的介绍比较粗略，由于篇幅所限，且MySQL存储过程并非必须要掌握的知识，因此这里仅列举一些代码，未做详细说明，大家如果有兴趣深入学习和编写存储过程，请参考官方文档。
（2）对于复制的影响
CREATE PROCEDURE、CREATE FUNCTION、ALTER PROCEDURE、ALTE RFUNCTION、CALL、DROP PROCEDURE和DROP FUNCTION语句都将被写进二进制日志。
存储子程序（存储过程/函数）在复制中引发了很多问题，如果应用了存储过程，则复制可能就是不可靠的了。
笔者认为主要原因在于它不是核心的功能但又足够复杂。对于一项大多数人都不使用的特性，如果你要使用，那么使用的时候一定要慎重。
（3）DETERMINISTIC定义
生产中，如果要创建存储过程/函数，往往需要添加DETERMINISTIC定义，否则可能会报错，我们需要在BEGIN关键字之前添加DETERMINISTIC，例如如下语句。
CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
DETERMINISTIC
BEGIN
如果程序或线程总是对同样的输入参数产生同样的结果，则可认为它是“确定的”，否则就是“非确定”的。
如果既没有给定 DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC。
加上DETERMINISTIC关键字的目的是，确保我们的存储过程/函数不会导致复制不可靠。
如果一个存储函数在一个诸如 SELECT这样不修改数据的语句内被调用，即使函数本身更改了数据，函数的执行也不会被写进二进制日志里。这个记录日志的行为会潜在地导致问题。
假设函数myfunc()定义如下。
CREATE FUNCTION myfunc () RETURNS INT
BEGIN
INSERT INTO t (i) VALUES(1);
RETURN 0;
按照上面的定义，下面的语句将修改表t，因为myfunc()修改表t，但是语句不会被写进二进制日志，因为它是一个SELECT 语句。
SELECT myfunc();
默认地，要想让一个CREATE PROCEDURE或CREATE FUNCTION语句被接受，那么必须明白地指定DETERMINISTIC、 NO SQL或READS SQLDATA三者中的一个，否则会产生错误。
DETERMINISTIC：确定的。
NO SQL：没有SQl语句，当然也不会修改数据。
READS SQL DATA：只是读取数据，当然也不会修改数据。
注意，子程序本身的评估是基于创建者的“诚实度”的，MySQL不会检查被声明为确定性的子程序是否不包含产生非确定性结果的语句。
我们也可以设置全局变量“SET GLOBAL log_bin_trust_routine_creators=1;”，这样就可以不用添加DETERMINISTIC关键字了。
官方文档的解释是：若启用了二进制记录，则该变量适用。它控制是否可以信任程序的作者不会创建向二进制日志写入不安全事件的程序。
如果设置为0（默认情况下），则不允许用户创建或修改保存的程序，除非他们不仅拥有CREATE ROUTINE或 ALTER ROUTINE的权限还拥有SUPER的权限。
设置为0还强制限制程序必须用DETERMINISTIC、READS SQLD ATA或NO SQL 三者中的一个进行声明。
如果将变量设置为1，那么MySQL不会对保存程序的创建强加限制。
（4）游标功能
存储过程和函数内均支持游标（cursor），其语法格式如下。
DECLARE cursor-name CURSOR FOR SELECT ...;
OPEN cursor-name;
FETCH cursor-name INTO variable [, variable];
CLOSE cursor-name;
示例如下。
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
（5）错误异常处理
语法格式如下。 DECLARE { EXIT | CONTINUE } HANDLER FOR { error-number | { SQLSTATE error-string } | condition } SQL statement
这个语句指定了每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，则指定的语句将被执行。
对于一个 CONTINUE处理程序，当前子程序的执行将在执行处理程序的语句之后继续。
对于EXIT处理程序，当前的BEGIN…END复合语句的执行将被终止。
下面给出了一个示例。
mysql> CREATE TABLE test.t (s1 int,primary key (s1));
mysql> delimiter //
mysql> CREATE PROCEDURE handlerdemo ()
-> BEGIN
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
-> SET @x = 1;
-> INSERT INTO test.t VALUES (1);
-> SET @x = 2;
-> INSERT INTO test.t VALUES (1);
-> SET @x = 3;
-> END;
mysql> CALL handlerdemo()//
mysql> SELECT @x
其中，SQLSTATE'23000'是重复键的错误消息。
可以注意到，@x是3，这表明了MySQL被执行到了程序的末尾。
如果“DECLARE CONTINUE HANDLER FOR SQLSTATE'23000' SET@x2=1;”这一行不存在，那么第二个INSERT因PRIMARYKEY强制而失败之后，MySQL会采取默认（EXIT）路径，并且SELECT@x会返回2。
异常处理中需要注意的是，不一定是当前的语句/游标会触发错误，程序体的其他部分也可能触发异常处理，使程序以一种我们不期望的方式来运行。
例如对于SQLSTATE:02000(ER_SP_FETCH_NO_DATA)找不到数据。
我们知道对于“SELECT… FROM table_name WHERE…”语句，可能会触发这个条件，但是如果SELECT…INTO语句查找不到记录，其实也会触发SQLSTATE:02000。

2.触发器
对触发器的支持，使得InnoDB也具有了商业数据库的功能。
但就笔者个人的使用经验而言，InnoDB触发器离传统商业数据库的成熟度还比较遥远。
下面给出了一个简单的示例，在该示例中，针对INSERT语句，将触发程序和表关联了起来。其作用相当于累加器，能够将插入表中某一列的值累加起来。
在下面的语句中，创建了一个表，并为该表创建了一个触发程序。
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
CREATE TRIGGER语句创建了与账户表相关的、名为ins_sum的触发程序。
它还包括一些子句，这些子句指定了触发程序激活的时间、触发程序事件，以及激活触发程序时要做些什么。
关键字BEFORE指明了触发程序的动作时间。在本例中，将在将每一行插入表之前激活触发程序。如果需要在事件发生后激活触发程序，则需要指定关键字AFTER。
关键字INSERT指明了激活触发程序的事件。在本例中，INSERT语句将导致触发程序的激活。同样也可以为DELETE和UPDATE语句创建触发程序。
跟在FOR EACH ROW后面的语句定义了每次激活触发程序时将要执行的程序，对于受触发语句影响的每一行执行一次。
在本例中，触发的语句是简单的SET语句，负责将插入amount列的值累加起来。
该语句将列引用为NEW.amount，意思是“将要插入到新行的amount列的值”。
要想使用触发程序，将累加器变量设置为0，执行INSERT语句，然后查看变量的值，语句如下。
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
在本例中，执行了INSERT语句后，@sum的值是14.98+1937.50-100，或1852.48。
要想销毁触发程序，可使用DROP TRIGGER语句。 mysql> DROP TRIGGER test.ins_sum;

3.外键
InnoDB支持外键约束。InnoDB定义外键约束的语法格式如下所示。
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
例如，如下是一个通过单列外键联系起的父表和子表，语句如下。
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB;
InnoDB支持使用ALTER TABLE来移除外键。 ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
要使得重新导入有外键关系的表变得更容易操作，那么mysqldump会自动在dump输出文件中包含一个语句设置 FOREIGN_KEY_CHECKS为0。
这就避免了在dump文件被重新装载之时，因为约束而导入失败。
我们也可以手动设置这个变量，语句如下。
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
InnoDB不允许删除一个被FOREIGN KEY表约束引用的表，除非设置了SET FOREIGN_KEY_CHECKS=0。
外键约束使得程序员更不容易将不一致性引入数据库，而且设计合适的外键也有助于以文档方式记录表间的关系。
但请记住，这些好处是以数据库服务器为执行必要的检查而花费额外的开销为代价的。
服务器进行额外的检查会影响性能，对于某些应用程序而言，该特性并不受欢迎，应尽量避免（出于该原因，在一些主要的商业应用程序中，在应用程序级别上均实施了外键逻辑）。
查询外键信息的语句如下。
select CONSTRAINT_SCHEMA,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
from information_schema.KEY_COLUMN_USAGE where referenced_table_schema is not null ;
使用外键应注意如下一些要点：
不存在服务器端外键关联检查时，应用程序本身必须处理这类关联事宜。
从具有外键的表中删除记录时，在缺少ON DELETE的情况下，一种解决方式是为应用程序增加恰当的DELETE语句。实际上，它与使用外键同样快，而且移植性更好。

4.建议
传统的观点认为，如果有一个重复执行的任务，而且这个任务需要检查、循环重复执行多条语句，但实际上不需要交互，
那么我们使用存储过程会更高效，这样将不存在在客户端和服务器之间来回地传递信息。
存储过程/触发器往往还是已经编译好了的，所以也会更快。
在商业数据库实现比较完善的存储过程/触发器后，存储过程、外键及触发器这些特性，在传统行业也获得了大量的应用。
许多复杂的业务逻辑用存储过程来实现，还可以保证安全、进行权限控制、集中控制业务逻辑，客户端也可以大大简化。
如果业务逻辑发生变更，只需要修改下存储过程即可，而不需要繁琐地升级大量的客户端，而且数据库服务器往往更强劲，执行得也更快更有效率，网络通信来回往返传输的开销也可以节省。
所以，当数据量还没有大到RDBMS处理不了的时候，可以考虑使用存储过程，毕竟这是花了钱购买的，充分利用商业数据库的潜能往往可以获得比较好的收益。
但是，即使存储过程、外键、触发器有这么多的好处，在现实中却也存在许多问题，特别是互联网行业，使用的是开源免费的MySQL数据库，
在当前海量数据的环境下，关系型数据库本身都需要NoSQL的补充，存储过程的使用也就受到了约束。
随着业务规模的扩大，数据库会逐渐成为系统的瓶颈，而在客户端和数据库中间增加应用服务器（应用层）来实现业务逻辑，
用应用服务器（客户端）来确保数据的完整性和一致性，是伸缩性更好的方案。
如今的计算模式也已经和以前有了很大的不同，特别是在互联网环境中，相对廉价的PC服务器集群的大量应用，硬盘容量更大，价格更低，更倾向水平扩展，而没有必要把负荷都堆积到中心的数据库服务器之上。
所以对于互联网应用，存储过程、外键及触发器这些特性也已不再凸显其重要性，许多项目基本不用。
而且对于大多数的程序员来讲，他们更熟悉语言框架，数据库更多的只是作为一个存储数据的容器。这也影响到了存储过程的使用。
就现状而言，存储过程只能存在于较少的业务场景中。
下面就从不同的角度来分析下存储过程/触发器。
（1）安全
理论上来说，业务逻辑和各种约束越靠近数据库就会越安全，也能最大化地充分利用数据库。
但对于互联网行业的应用来说，一般没有那么高的数据安全性，也不需要很强的数据完整性和一致性，
如果确实有非常严苛的数据一致性的需求，那么可以专门实现一个“数据访问层”，其他的应用都将通过它来访问数据库。
（2）性能和扩展性
对于单线程而言，据官方资料表示，存储过程有20%的性能提升，但应用很少是单线程的，随着连接数的不断增加，存储过程对比直接的SQL并不见得有什么性能上的提升。
此时系统的性能提升已经让位于多线程并发管理，而且随着连接数的继续增加，存储过程的性能可能还会降低。
MySQL的触发器只支持行级别（foreach row）一种方式，对于大数据量表的处理，这种方式将会很低效。
触发器没有 WHEN条件，不能控制何时触发，可能会造成性能瓶颈，无谓地消耗资源。
外键对并发性能的影响比较大，因为每次修改数据都需要去另外一个表检查数据，需要获取额外的锁（以确保事务完成之前，父表的记录不会被删除），高并发的环境下很容易出现性能问题。
而级联更新删除之类的特性也比我们正常执行批量更新删除之类的操作要慢得多（级联删除、更新是one by one的）。
所以更好的办法是在应用层实现外键约束。
在应用层实现业务逻辑的网络通信的成本可能高了点，但这只是一个相对的概念，在距离很遥远的情况下，客户端和服务器端通信的成本比较大，这个时候存储过程更显优势，
但Web服务器和数据库服务器一般位于同一个集群的内网中，网络交互很快、很稳定，成本也很低。
很多真正能提高效率的终极办法是使用缓存而不是在数据库中进行运算，靠数据库预编译或减少网络流量那点优化就可以了，那也说明性能要求原本就不高。
数据库实现存储过程、触发器和外键，很大一个背景就是数据库服务器很强劲，
传统行业一般是昂贵小型机，有非常强劲的处理能力，配备的是Oracle等商业产品，业务需求相对稳定，需要充分利用数据库的能力而不仅仅将它当作一个数据的容器。
而互联网行业一般使用的是MySQL数据库，相对廉价的PC，业务增长的不确定性，甚至是爆炸式的增长，如果数据架构不足，数据库很可能会成为整个系统的瓶颈，
数据库的资源一般会比较紧张（服务器和人），扩展性不强，成本更昂贵；而Web服务器相对来说更便宜，更容易水平扩展，把业务逻辑放到Web服务器上去实现可以保证系统有更好的伸缩性。
（3）迁移
如果需要在不同的数据库产品之间进行迁移，虽然有一些文档和各种各样的迁移方案供我们选择，但存储过程、触发器的迁移却是一个难题。
往往需要投入巨大的精力进行开发和测试，所以如果有数据库解耦的需求，就不应该使用存储过程。
（4）升级、维护、诊断、调优
实际上，从设计的角度来看，逻辑封装很重要，不是存储过程那一点的封装，而是整个业务逻辑的封装。
如果把业务逻辑分散在程序代码和存储过程两部分中，那么它实际上是业务碎片化，不利于表述业务逻辑，会造成后期阅读和维护的困难。
如果使用存储过程，往往会降低上线、升级的效率，DBA和研发需要高度协调。
以前一般是分离的，或者升级代码，或者升级数据库结构，而现在则需要升级存储在数据库服务器上的代码，但DBA往往并不熟悉业务逻辑。
升级失败很难马上恢复，而且影响面太大。而升级Web服务器，可以逐台进行升级。一般情况下是可以做到逐台升级而不会导致异常的。
对于业务非常繁忙的系统，升级存储过程可能会导致系统出现异常，因为要升级的存储过程可能正被频繁访问，或者应用系统足够复杂，存储过程互相调用，因此升级单个存储过程需要特别小心，以免影响整个系统。
开发、测试环境和生产环境很可能会不一致，从而导致开发环境的存储过程、触发器需要经过修改，才能升级到生产环境，因为存储过程、视图和触发器附加了一些与生产环境不一致的信息。
存储过程、触发器备份恢复不方便。
MySQL不能临时禁用或启用触发器，因为这点在做数据迁移时，修复会比较麻烦，需要临时删除触发器，可能还会影响到生产环境。
由于存储过程或触发器不易测试，或者未做充分测试，一旦升级失败就可能会导致数据错误，因为已经事先删除了存储过程或触发器。
不易分析存储过程或触发器的性能，因为不能通过慢查询日志去分析存储过程或触发器的具体执行情况。慢查询日志里只记录了“call procedure_name();”这样简单的信息。
触发器可能会导致死锁。
以上只是列举一些问题，具体的使用过程中，MySQL的存储过程和触发器离商业产品的距离还比较远。
MySQL的很多Bug 都涉及了存储过程和触发器。存储过程对于复制的支持也不太好。
（5）开发
存储过程并不是一种结构化良好的语言，对于习惯于面向对象编程的人而言，存储过程更加难以理解。
代码的可读性和可维护性在工程上是很重要的，从这点来说，存储过程并不适合工程化的需要。
存储过程和触发器的调试都比较困难，也没有什么好的工具和方法。
一个表中同类型的触发器只能建立一个，这就可能会导致代码逻辑很复杂，不易阅读和维护，因为需要把很多不相关的逻辑都写在一个触发器代码内。
存储过程也有诸多限制，具体请参考官方文档http://dev.MySQL.com/doc/refman/5.1/en/stored-program-restrictions.html#stored- routines-trigger-restrictions
如果没有完善的、一致的文档，开发人员往往会不熟悉（遗漏）数据库上的存储过程。
存储过程比较简单，功能也很有限，而程序代码却可以实现更多的功能，实现更复杂的业务逻辑。
因此，笔者建议，一定要慎用存储过程，业务逻辑不要放在存储过程中。不要使用触发器。
在良好的业务系统中应该尽量抛弃存储过程和触发器之类的东西。
不要用外键，在高并发的情况下，外键会降低并发性，外键自身的维护性和管理性也欠佳。

4.9.4 视图
MySQL 5.0版以上的MySQL服务器提供了视图功能（包括可更新视图）。
如下命令将创建一个视图。 mysql> CREATE VIEW test.v AS SELECT * FROM t;
注意视图（VIEW）并不会保存任何数据，查询视图返回的结果都是来自于基表存储的数据。
视图一般不会用来提升性能，而是用来简化部分开发，进行权限限制。
表和视图将共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图。
视图必须具有唯一的列名，不得有重复，就像基表那样。
默认情况下，由SELECT语句检索的列名将用作视图列名。
可使用多种SELECT语句创建视图。视图能够引用基表或其他视图，还能使用联合、UNION和子查询。
视图可以简化一些操作，比如隐藏基表的复杂性，进行一些安全控制（基于列的权限控制），但如果使用不当，很可能会带来性能问题。
我们需要了解视图实现的机制。对于包含视图的SQL，优化器进行优化的机制有两种：MERGE和TEMPTABLE。
TEMPTABLE：创建一个临时表，把视图的结果集放到临时表中，然后SQL操作这个临时表。
MERGE：重写SQL，合并视图的SQL，这种方法更智能。
例如，新建一个视图test.v。 CREATE VIEW test.v AS SELECT * FROM t where a=1;
对于视图的查询语句： select a,b,c from test.v where b=2;
第一种临时表的方式类似如下语句。 CREATE TEMPORARY TABLE TMP_a AS SELECT * FROM t where a=1; select * from TMP_a where b=2;
这种方式必须先查出所有视图的数据，然后才能基于这个视图的数据进行查找。显然可能会有性能问题。同时，外部的 WHERE条件也不能传递到内部视图的限制中，临时表上没有索引。
而用第二种方式，优化后的SQL类似如下语句。 SELECT * FROM t where a=1 and b=2;
MySQL将尽量使用第二种合并SQL的方式，但在很多情况下，由于研发人员编写的查询采用临时表的方式，因而导致性能很差。
可以用EXPLAIN命令来确认，如果EXPLAIN的select_type输出显示DERIVED（查询结果来自一个衍生表），那么查询使用的是临时表的方式，示例如下。
root@localhost test>CREATE VIEW test_garychen_v AS SELECT * FROM test_garychen GROUP BY STAT_TIME;
root@localhost test>EXPLAIN SELECT * FROM test_garychen_v;
使用临时表的方式，性能可能会变得很差，视图也没有索引，外部的WHERE条件也不会传递到内部的视图（类似于 UNION ALL）中。
如果两个视图相连接，那将无法利用索引，可能会导致严重的性能问题。
所以需要小心编写查询，以免使用到临时表的机制。
也就是说，视图里应尽量避免使用GROUP BY、ORDER BY、DISTINCT、聚集函数、UNION和子查询。
一些复杂的视图，若使用EXPLAIN命令显示执行计划，将会执行得很慢，因为EXPLAIN会实际执行和物化派生表（derived table）。
视图里隐藏了很多细节，研发人员可能会觉得这个表很简单，但实际上底层是很复杂的查询。
如果认为这个视图很简单，那么可能将它当作成一个简单查询频繁调用而不自知，从而导致性能问题。
在生产环境中我们还发现，高并发的情况下，查询优化器将在planing和statistics阶段花费大量时间，甚至导致MySQL服务器停滞，
所以即使使用的是merge的算法，也仍然可能导致严重的性能问题。

小结
本章介绍了范式和反范式，反范式对于开发中的大型项目很重要，我们有必要在项目中不断积累这方面的经验。
关于慢查询日志，它不仅是DBA常用的工具，研发人员一样也需要熟练掌握。
由于现实开发项目中存在一个很大的问题，缺少性能管理，本章也介绍了性能管理的一些概念和方法。
总之，如果要开发高质量的项目，一定要深刻理解数据库，对于数据库的逻辑设计、物理设计、事务、锁等内容都需要深入理解。
本章最后介绍了一些非核心的MySQL特性，对于非核心的 MySQL特性的使用，一定要慎重对待。

巴特西

4-MySQL DBA笔记-开发进阶

最新文章

热门文章