深入浅出mysql（二）：开发篇

我不是女神ヾ 2022-05-18 05:41 223阅读 0赞

# 存储引擎 #

**表类型（存储引擎的选择）**

和大多数数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

## 1.MySQL存储引擎概述 ##

插件式存储引擎是MySQL数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。

MySQL5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等；

其中，InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表；

创建新表的时候，如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB。如果需要修改默认的存储引擎，可以在参数文件中设置default-table-type。

查看当前的默认存储引擎，可以使用如下的命令：

mysql > show variables like ‘table\_type’;

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#002060;width:88.6pt;"> 特点 </td> 
 <td style="background-color:#002060;width:53.4pt;"> MyISAM </td> 
 <td style="background-color:#002060;width:71pt;"> InnoDB </td> 
 <td style="background-color:#002060;width:71pt;"> MEMORY </td> 
 <td style="background-color:#002060;width:68.7pt;"> MERGE </td> 
 <td style="background-color:#002060;width:73.4pt;"> NDB </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 存储限制 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 有 </td> 
 <td style="vertical-align:top;width:71pt;"> 64TB </td> 
 <td style="vertical-align:top;width:71pt;"> 有 </td> 
 <td style="vertical-align:top;width:68.7pt;"> 没有 </td> 
 <td style="vertical-align:top;width:73.4pt;"> 有 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 事务安全 </td> 
 <td style="vertical-align:top;width:53.4pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:68.7pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:73.4pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 锁机制 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 表锁 </td> 
 <td style="vertical-align:top;width:71pt;"> 行锁 </td> 
 <td style="vertical-align:top;width:71pt;"> 表锁 </td> 
 <td style="vertical-align:top;width:68.7pt;"> 表锁 </td> 
 <td style="vertical-align:top;width:73.4pt;"> 行锁 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> B树索引 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:68.7pt;"> 支持 </td> 
 <td style="vertical-align:top;width:73.4pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 哈希索引 </td> 
 <td style="vertical-align:top;width:53.4pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:68.7pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:73.4pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 全文索引 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:68.7pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:73.4pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 集群索引 </td> 
 <td style="vertical-align:top;width:53.4pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:68.7pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:73.4pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 数据缓存 </td> 
 <td style="vertical-align:top;width:53.4pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:68.7pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:73.4pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 索引缓存 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:68.7pt;"> 支持 </td> 
 <td style="vertical-align:top;width:73.4pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 数据可压缩 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:68.7pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:73.4pt;"> &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 空间使用 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 低 </td> 
 <td style="vertical-align:top;width:71pt;"> 高 </td> 
 <td style="vertical-align:top;width:71pt;"> N/A </td> 
 <td style="vertical-align:top;width:68.7pt;"> 低 </td> 
 <td style="vertical-align:top;width:73.4pt;"> 低 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 内存使用 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 低 </td> 
 <td style="vertical-align:top;width:71pt;"> 高 </td> 
 <td style="vertical-align:top;width:71pt;"> 中等 </td> 
 <td style="vertical-align:top;width:68.7pt;"> 低 </td> 
 <td style="vertical-align:top;width:73.4pt;"> 高 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 批量插入的速度 </td> 
 <td style="vertical-align:top;width:53.4pt;"> 高 </td> 
 <td style="vertical-align:top;width:71pt;"> 低 </td> 
 <td style="vertical-align:top;width:71pt;"> 高 </td> 
 <td style="vertical-align:top;width:68.7pt;"> 高 </td> 
 <td style="vertical-align:top;width:73.4pt;"> 高 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#92d050;width:88.6pt;"> 支持外键 </td> 
 <td style="vertical-align:top;width:53.4pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:71pt;"> 支持 </td> 
 <td style="vertical-align:top;width:71pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:68.7pt;"> &nbsp; </td> 
 <td style="vertical-align:top;width:73.4pt;"> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

## 2.各种存储引擎的特性 ##

### 1.MyISAM ###

MyISAM是MySQL 默认的存储引擎，MyISAM不支持事务，也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表；

数据文件和索引文件可以放置在不同的目录，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，也就是说不同的MyISAM表的索引文件和数据文件可以防止到不同的路径下。文件路径需要的是绝对路径，并且具有访问权限

MyISAM的表还支持3中不同的存储格式，分别是：

1.  静态（字段都是固定长度）表；
2.  动态表；
3.  压缩表；

### 2.InnoDB ###

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引；

【自动增长列】

对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列；

但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录之后，自动增长是按照组合索引的前面几列进行排序之后递增的；

【外键约束】

MySQL支持外键的存储索引只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引；

CONTRAINT ‘fk\_city\_county’ FOREIGN KEY (country\_id) REFERENCES country (country\_id) ON DELETE RESTRICT ON UPDATE CASCADE

在创建索引的时候，可以指定在删除、更新父表时，对子表进行相应的操作，包括RESTRICT、CASCADE 、SET NULL 和 NO ACTION。

其中RESTRICT和NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE 表示父表在更新或者删除的时候，更新或者删除子表对应的记录；

SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL。

选择后两种方式的时候需要谨慎，可能会因为错误的操作导致数据的丢失；

说明：当某个表被其他表创建了外键参照的时候，那么该表的对应索引或者主键禁止被删除；

在导入多个表的数据的时候，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同样，在执行LOAD DATA和ALTER TABLE操作的时候，可以通过暂时关闭外键约束来加快处理的速度，关闭的命令是：SET FOREIGN\_KEY\_CHECKS=0，执行完之后，通过执行SET FOREIGN\_KEY\_CHECKS=1语句改回原来的状态；

【存储方式】

1.  使用共享表空间存储；

使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb\_data\_home\_dir和innodb\_data\_file\_path定义的表空间中，可以是多个文件；

1.  使用多表空间存储；

使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中，如果是个分区表，则每个分区表对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，依次来将表的IO均匀分布在多个磁盘上；

注意：即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据字典和在线重做日志放在这个文件中；

### 3.MEMORY ###

MEMORY存储引擎使用存在于内存中的内容来创建表，每个MEMORY表只实际对应一个磁盘文件，格式为.frm。MEMORY类型的表访问非常的快，因为它的数据值放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉；

给MEMORY表创建索引的时候，可以指定使用HASH索引还是BTREE索引；

在启动MySQL服务的时候使用init-file选项，把INSERT INTO … SELECT或者LOAD DATA INFILE这样的语句放入到这个文件中，就可以在服务启动的时候从持久稳固的数据源装载表；

执行DELETE FROM或者TRUNCATE TABLE，或者整个地删除表（使用DROP TABLE操作）：用来释放内存；

可以指定MEMORY表的大小以及指定表的最大行数；

MEMORY表主要是用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对存储引擎为MEMORY的表进行更新操作需要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务之后如何获取这些修改后的数据所考虑；

### 4.MERGE ###

MEMORY存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MEMORY表本身并没有数据，对MEMORY类型的表可以进行查询、更新和删除操作，这些操作实际上是对内部的MyISAM表进行的；

说明：

MERGE表和分区表的区别：MERGE并不能智能的将记录写入到对应的表中，而分区表是可以的。通常我们使用MERGE表来透明的对多个表进行查询和更新操作，而对这种按照时间记录的操作日志则可以透明的进行插入操作；

### 5.TokuDB ###

ToKuDB是一个高性能、支持事务处理的MySQL和MariaDB的存储引擎，具有高扩展性、高压缩率、高效的写入性能，支持大多数在线DDL操作。

使用场景：

1.  日志数据，因为日志通常插入频繁并且存储量很大；
2.  历史数据，通常不会再有写操作，可以利用TokuDB的高压缩特性进行存储；
3.  在线DDL较频繁的场景，使用TokuDB可以大大增加系统的可用性；

## 3.如何选择合适的存储引擎 ##

**常用的存储引擎的适用环境**

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#002060;width:57.5pt;"> 存储引擎 </td> 
 <td style="background-color:#002060;width:368.6pt;"> 适用场景 </td> 
 </tr> 
 <tr> 
 <td style="background-color:#70ad47;width:57.5pt;"> &nbsp; &nbsp; MyISAM </td> 
 <td style="vertical-align:top;width:368.6pt;"> &nbsp; 默认MySQL插件式存储引擎，如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性，并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一； &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#70ad47;width:57.5pt;"> &nbsp; &nbsp; &nbsp; InnoDB </td> 
 <td style="vertical-align:top;width:368.6pt;"> &nbsp; 用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据库操作除了插入和查询之外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交（commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择； &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#70ad47;width:57.5pt;"> &nbsp; &nbsp; MEMORY </td> 
 <td style="vertical-align:top;width:368.6pt;"> &nbsp; 将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。MEMORY的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止之后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果； &nbsp; </td> 
 </tr> 
 <tr> 
 <td style="background-color:#70ad47;width:57.5pt;"> &nbsp; &nbsp; MERGE </td> 
 <td style="vertical-align:top;width:368.6pt;"> &nbsp; 用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE表的优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于诸如数据仓库等VLDB环境下十分适合； &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

--------------------

# 数据类型 #

## 1.CHAR和VARCHAR ##

在MySQL中，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，这里简单概括如下：

## 2.TEXT和BLOB ##

一般在保存少量字符串的时候，我们会使用CHAR和VARCHAR；而在保存较大的文本的时候，通常会选择使用TEXT或者是BLOB。二者之间的主要差别是BLOB能用来保存二进制数据，比如照片；而TEXT只能保存字符串数据，比如一篇文章或者日记；

而TEXT和BLOB中有分别包括TEXT、MEDIUMTEXT、LONGTEXT和BLOB、MEDIUMBLOB、LONGBLOB三种不同的类型。

它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足需求的最小存储类型。

**常见问题总结**

（1）BLOB和TEXT值会引起一些性能问题，特别是在执行了大量的删除操作的时候；

删除操作会在数据表中留下很大的“空间”，以后填入这些空洞的记录在插入的性能上会有些影响，为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题；

（2）可以使用合成的（Synthetic）索引来提高大文本字段（BLOB或者TEXT）的查询性能。

简单的讲，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，需要注意的是这种技术只能yo9ngyu精确匹配的查询（散列值对于类似“<”或者“>=”等范围搜索操作符是没有用的）。可以使用MD5()函数生成散列值，也可以使用SHA1()或者CRC32()，或者使用自己的程序逻辑生成散列值。提示：数值型的散列值可以很高效率的存储。同样，如果散列值算法生成的字符串带有尾部空格，就不要把它们存储在CHAR()或者VARCAHR()列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB或者TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多；

使用合成索引的用法，由于这种技术只能用于精确匹配，在一定的程度上减少了I/O，从而提高了查询的效率。如果需要对BLOB或者CLOB字段进行模糊查询，MySQL提供了前缀索引，也就是只为字段的前n列创建索引；

create index idx\_blob on t(context(100));

为BLOB类型的字段context的前100个字符进行模糊查询，就可以使用前缀索引；

模糊查询语句：

desc select \* from t where context like ‘beijing%’ \\G;

注意：这里的查询条件中，“%”不能放在最前面，否则索引将不会被使用；

（3）在不必要的时候避免检索大型的BLOB或者TEXT的值；

（4）把BLOB或者TEXT列分离到单独的表中；

在某些环境中，如果把数据列移动到第二张数据表中，可以把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这样会减少主表中的碎片，可以得到固定长度数据行的性能优势。它还可以使主数据表在运行SELECT \* 查询的时候不会通过网络传输大量的BLOB或者TEXT值；

## 3.浮点数和定点数 ##

浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型之后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值，然后插入，四舍五入的过程中不会报错。在MySQL中float、double（或者real）用来表示浮点数；

定点数不同于浮点数，定点数实际上是以字符串的形式存放的，所以定点数可以更加精确地保存数据。如果实际插入的数值精度大于实际定义的精度，则MySQL会进行警告（默认的SqlMode下），但是数据按照实际精度四舍五入之后插入；如果SQLMode是在TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入；

在MySQL中，decimal或者（numberic）用来表示定点数；

注意：

*  浮点数存在误差问题；
 *  对货币等精度敏感的数据，应该使用定点数表示或者存储；
 *  在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数的比较；
 *  要注意浮点数中一些特殊值的处理；

# 4.日期类型选择 #

根据需要选择能够满足应用的最小存储的日期类型；

TIMESTAMP表示的日期范围比DATETIME要短的多；

如果记录的日期需要让不同的时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际的时区对应；

--------------------

# 索引的设计和使用 #

**索引的设计和使用**

[http://www.cnblogs.com/shijianchuzhenzhi/p/6383117.html][http_www.cnblogs.com_shijianchuzhenzhi_p_6383117.html]

## 1.索引的概述 ##

所有的MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。

根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎对索引有着不同的规范与限制；

索引可以在创建表的时候同时创建，也可以随时增加新的索引：

CREATE \[UNIQUE|FULLTEXT|SPATIAL\] INDEX index\_name \[USING index\_type\] ON tb\_name (index\_col\_name,…)

index\_col\_name:

col\_name \[(length)\]\[ASC|DESC\]

或者使用ALTER TABLE的语法来增加索引，语法与CREATE INDEX类似，可以查询帮助获得详细的语法。

删除索引：  
DROP INDEX index\_name ON tbl\_name

## 2.设计索引的原则 ##

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量的考虑复合这些原则，便于提升索引的使用效率，更高效的使用索引；

1.  **搜索的索引列：**

索引类不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE 子句中的列，或者连接子句中指定的列，而不是出现在SELECT关键字之后的选择列表中的列；

1.  **使用唯一索引：**

考虑某列中值的分布。索引的列的基数越大，索引的效果就越好。例如，存放出生日期的列一般会具有不同的值，很容器区分各行。但是用来记录性别的列，只含有M和F，则对此列进行索引没有多大的用处，因为不管搜索哪个值，都会得出大约一半的行；

1.  **使用短索引：**

如果对字符串类型的列进行索引，应该指定一个前缀长度，只要有可能就应该这样做，例如，有一个CHAR(200)列，如果在前10或者20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或者20个字符进行索引能够节省大量索引空间，也可能会使得查询更快。较小的索引涉及的磁盘IO较少，较短的值比较起来更快，更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性；

1.  **利用最左前缀：**

在创建一个n列的索引的时候，实际上创建了MySQL可以利用的n个索引。多列索引可以其到几个索引的作用，因为可以利用索引中最左边的列集来匹配，这样的列集称为最左前缀；

1.  **不要多度索引：**

每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容的时候，索引必须进行更新，有时可能需要重构，因此，索引越多，所花费的时间就越长。如果有一个索引很少利用或者从不使用，那么会不必要的减缓表的修改速度。此外，MySQL在生成一个执行计划的时候，需要考虑各个索引，这也要花费时间，创建多余的索引给查询优化带来的更多的工作。

索引太多也可能会导致MySQL选择不到所要使用的最好索引。

只保持所需要的索引有利于查询的优化；

1.  对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存；

1.  如果有明确定义的主键，则按照主键顺序保存。
2.  如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。
3.  如果既没有主键有没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。

按照**主键**或者**内部列**进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为**访问条件**的列作为主键，提高查询的效率，另外，还需要注意的是，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效的减少索引的磁盘占用，提高索引的缓存效果；

## 3.BTREE索引和HASH索引 ##

MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引各有其不同的适用范围。HASH索引有一些重要的特征在使用的时候需要特别的注意：

【HASH索引】

只用于使用=或者<=>操作符的等式比较；

优化器不能使用HASH索引来加速ORDER BY操作；

MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY表，会影响一些查询的执行效率；

【BTREE索引】

而对于BTREE索引，当使用>、<、>=、<=或者LIKE ‘pattern’，其中“pattern”不以通配符开始，操作符的时候，都可以使用相关列上的索引；

注意：

了解了BTREE索引和HASH索引不同之后，当使用MEMORY表的时候，如果是默认出创建的HASH索引，就需要注意SQL语句的编写，确保可以使用上索引，**如果一定需要使用范围查询，那么在创建索引的时候，就应该创建成为****BTREE****索引；**

## 4.小结 ##

索引用于快速的找出在某个列中有一个特定值的行。如果不使用索引，MySQL必须从第一条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间就越多。如果表中查询的列有一个索引，MySQL能快速的达到一个位置去搜寻数据文件的中间，没有必要看所有的数据。

如果一个表有1000行，这比顺序读取至少要快100倍。注意如果需要访问大部分的行，顺序读取要快得多，因此此时应该避免磁盘搜索；

大多数MySQL索引（如PRIMARY KEY、UNIQUE、INDEX和FULLTEXT等）在BTREE中存储。只是空间列类型的索引使用RTREE，并且MEMORY表还支持HASH索引；

--------------------

# 视图 #

# 1.什么是视图 #

视图View是一种虚拟存在的表，对于使用视图的用户来讲基本上是透明的。视图并不在数据库中实际存在，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时候动态生成的；

视图相对于普通的表的优势：

1.  简单：

使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来讲已经是过滤好的复合条件的结果集；

1.  安全：

使用视图的用户只能访问它们被允许查询的结果集，对表的权限管理并不能限制到某个行的某个列，但是通过视图就可以实现；

1.  数据独立：

一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响；

## 2.视图操作 ##

### 1.创建或者修改视图 ###

创建视图需要有CREATE VIEW 的权限，并且对于查询涉及的列有SELECT权限。如果使用CREATE OR REPLACE 或者ALTER，那么还需要有该视图的DROP权限；

**创建视图语法：**

CREATE \[OR REPLACE\] \[ALGORITHM = \{UNDEFINED | MERGE | TEMPTABLE\}\] VIEW \[db\_name.\]view\_name \[(column\_list)\] AS select\_statement \[WITH \[CASCADED | LOCAL\] CHECK OPTION\]

说明：

通过该语句可以创建视图，若给定了\[OR REPLACE\]，则表示当已具有同名的视图时，将覆盖原视图。select\_statement是一个查询语句，这个查询语句可从表或其它的视图中查 询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。

表和数据库共享数据库中相同的名称空间，因此，数据库不能包含相同名称的表和视图，并且，视图的列名也不能重复。

**查看是否有相应的权限：**

SELECT SELECT\_priv,create\_view\_priv from mysql.user WHERE user='root'

说明：

MySQL视图的定义有一些限制，例如，在FORM关键字的后面不能包含子查询，这和其他数据库是不同的，如果视图是从其他的数据库迁移过来的，那么可能需要因此做一些改动，可以将一些子查询的内容先定义成为一个视图，然后对该视图再创建视图就可以实现类似的功能了。

### 2.修改视图 ###

【修改视图语法】

ALTER \[ALGORITHM = \{UNDEFINED | MERGE | TEMPTABLE\}\]

VIEW view\_name \[(column\_list)\]

AS select\_statement

\[WITH \[CASCADED|LOCAL\] CHECK OPTION\]

说明：

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可以更新的；

1.  包含以下关键字的SQL语句：聚合函数（SUM、MIN、MAX、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION 或者UNION ALL；
2.  常量视图
3.  JION
4.  FROM一个不能更新的视图；
5.  WHERE字句的子查询引用了FROM字句中的表；

\[WITH \[CASCADED|LOCAL\] CHECK OPTION\]决定了是否允许更新数据使记录不再满足视图的条件。这个选项与Oracle数据库中的选项是类似的，其中：

1.  LOCAL：主要满足视图的条件就可以更新；
2.  CASCADED：则必须满足所有针对该视图的所有视图的条件才可以更新；

### 3.删除视图 ###

用户可以一次性删除一个或者多个视图，前提是必须有该视图的DROP权限；

【语法】

DROP VIEW \[IF EXISTS\] view\_name \[,view\_name\]…\[RESTRICT | CASCADE\]

### 4.查看视图 ###

【show tables】

该命令不仅显示表的名字，同时也可以显示视图的名字；

【显示视图信息】

show tables status \[FROM bd\_name\] \[LIKE ‘pattern’\]

【查询某个视图的定义】

如果需要查询某个视图的定义，可以使用SHOW CREATE VIEW 命令进行查看：

## 3.小结 ##

注意：如果从不支持视图的旧版本升级到提供视图的新版本之后，要想使用视图，则需要升级授权表，使之包含与视图有关的权限。

--------------------

# 存储过程和函数 #

## 1.什么是存储过程和函数 ##

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的；

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型。如果有函数从其他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程；

## 2.存储过程和函数的相关操作 ##

创建存储过程或者函数需要有权限：CREATE ROUTINE权限

修改或者删除存储过程或者函数需要有：ALTER ROUTINE权限；

执行存储过程或者函数需要：EXECUTE权限

### 1.创建、修改存储过程或者函数 ###

【基本语法】

**CREATE PROCEDURE** sp\_name **(\[proc\_parameter\[,...\]\]) **

**　　\[characteristic ...\]** routine\_body** **

**说明：**

1.  **sp\_name:****存储过程的名称；**

默认在当前数据库中创建。这个名称应当尽量避免与MySQL的内置函数相同的名称

1.  **proc\_parameter:****存储过程的参数列表**

格式\[IN|OUT|INOUT\]param\_name type

Param\_name为参数名，type为参数的数据类型。多个参数彼此间用逗号分隔。输入参数、输出参数和输入/输出参数，分别用in/out/inout标识。参数的取名不要与数    据表的列名相同。

1.  **characteristic:****存储过程的某些特征设定，分别介绍**

1 COMMENT'string':用于对存储过程的描述，其中string为描述内容,comment为关键字。

2 LANGUAGE SQL:指明编写这个存储过程的语言为SQL语言。这个选项可以不指定。

3 DETERMINISTIC:表示存储过程对同样的输入参数产生相同的结果;NOT DETERMINISTIC，则表示会产生不确定的结果（默认）。

4 contains sql | no sql | reads sql data | modifies sql data Contains sql表示存储过程包含读或写数据的语句（默认）

No sql表示不包含sql语句

Reads sql data表示存储过程只包含读数据的语句

Modifies sql data 表示存储过程只包含写数据的语句

5 sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行，还是使用调用者(invoker)的许可来执行。默认是definer

SQL SECURITY \{DEFINER | INVOKER\}：

DEFINER：指定该使用创建该存储过程的用户的权限来执行该存储过程；

INVOKER：指定使用调用该存储过程的用户的权限来执行该存储过程；

例如如果调用者没有访问存储过程中某个表的权利，则此时就不能执行该存储过程；

Routine\_body:存储过程的主体部分，包含了在过程调用的时候必须执行的sql语句。以begin开始，以end结束。如果存储过程体中只有一条sql语句,可以省略begin-end标志。

### 2.删除存储过程或者函数 ###

一次只能删除一个存储过程或者函数，需要权限：ALTER ROUTINE

DROP \{PROCEDURE | FUNCTION\} \[IF EXISTS\] sp\_name

### 3.查看存储过程或者函数 ###

【1：查看存储过程或者函数的状态】

SHOW \{PROCEDURE | FUNCTION \}STATUS \[LIKE ‘pattern’\]

【2：查看存储过程或者函数的定义】

SHOW CREATE \{PROCEDURE | FUNCTION\} sp\_name

【3：通过查看information\_schema.Routines了解存储过程和函数的信息】

### 4.变量的使用 ###

【变量的定义】

DECLARE

【变量的赋值】

变量可以直接赋值SET，或者通过查询SELECT赋值；

SET var\_name = expr\[,var\_name=expr\]…

SET last\_month\_start = DATE\_SUB()

SELECT  col\_name INTO var\_name

### 5.定义条件和处理 ###

条件的定义和处理可以用来定义在处理过程中遇到问题的时候相应的处理步骤；

【1、条件的定义】

DECLARE condition\_name CONDITION FOR condition\_value

说明：

condition\_value：

SQLSTATE \[VALUE\] sqlstate\_value | myql\_error\_code

【2、条件的处理】

DECLARE handler\_type HANDLER FOR condition\_value \[,…\]sp\_statement

说明：

handler\_type:

CONTINUE | EXIT | UNDO

condition\_value：

SQLSTATE \[VALUE\] sqlstate\_value

| condition\_name

| SQLWARNING

| NOT FOUND

|SQLEXCEPTION

|mysql\_error\_code

### 6.光标的使用 ###

[http://wwty.iteye.com/blog/698353][http_wwty.iteye.com_blog_698353]

在存储过程和函数中，可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE，其语法分别如下：

### 7.流程控制 ###

[http://www.cnblogs.com/caoruiy/p/4485273.html][http_www.cnblogs.com_caoruiy_p_4485273.html]

可以使用IF、CASE、LOOP、LEAVE、ITERATE、REPEAT以及WHILE语句进行流程的控制；

注意：REPEAT和WHILE的区别

### 8.事件调度器 ###

事件调度器是MySQL 5.1之后新增的功能，可以将数据库按照自定义的时间周期触发某种操作，可以理解为时间触发器，类似Linux系统下的任务调度器crontab

【语法】

CREATE EVENT event\_name

ON SCHEDULE AT  …

DO  …

说明：

ON SCHEDULE 子句指定事件在何时执行以及执行的频次；

DO子句指定需要执行的具体操作或者事件；

--------------------

# 触发器 #

**触发器**

[http://www.cnblogs.com/xiaozl/archive/2012/10/30/2746189.html][http_www.cnblogs.com_xiaozl_archive_2012_10_30_2746189.html]

MySQL是与表有关的额数据库对象，在满足定义条件的时候触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

## 1.创建触发器 ##

【创建触发器语法】

CREATE TRIGGER trigger\_name trigger\_time trigger\_event

ON tbl\_name FOR EACH ROW trigger\_stmt

注意：触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table）创建触发器；

其中trigger\_name标识触发器名称，用户自行指定；

trigger\_time标识触发时机，用before和after替换；before的含义是指在检查约束前触发，而after是在检查约束后触发；

trigger\_event标识触发事件，用insert，update和delete替换；

tbl\_name标识建立触发器的表名，即在哪张表上建立触发器；

trigger\_stmt是触发器程序体；触发器程序可以使用begin和end作为开始和结束，中间包含多条语句；

对于INSERT INTO … ON DUPLICATE KEY UPDATE…语句来讲，触发器的顺序可能会造成疑惑；

[http://www.jb51.net/article/39255.htm][http_www.jb51.net_article_39255.htm]

总结：

对于有重复记录，需要进行UPDATE操作的INSERT，触发器触发的顺序是BEFORE INSERT 、BEFORE UPDATE、AFTER UPDATE；

对于没有重复记录的INSERT，就是简单的执行INSERT操作，触发器触发的顺序是BEFORE INSERT、AFTER INSERT。

对于那些实际执行UPDATE操作的记录，仍然会执行BEFORE INSERT触发器的内容，在设计触发器的时候一定需要考虑这种情况，避免错误的触发了触发器；

## 2.删除触发器 ##

一次可以删除一个触发程序，如果没有指定schema\_name，默认是当前的数据库，具体语法如下：

DROP TRIGGER \[schema\_name\].trigger\_name

## 3.查看触发器 ##

方式一：       可以通过执行SHOW TRIGGERS命令查看触发器的状态、语法等信息，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息；

方式二：

查询系统表的information\_schema.triggers表，这个方式可以查询指定的触发器指定信息，操作起来明显感觉方便很多；

mysql > desc triggers;

mysql > select \* from triggers where trigger\_name = ‘ins\_film\_bef’

## 4.触发器的使用 ##

触发器执行语句有以下两个限制：

1.  触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序。也就是说存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
2.  不能在触发器中使用以显示或者隐式方式开始或者结束事务的语句，如START TRANSACTION、COMMIT或者 ROLLBACK

MySQL触发器是按照BEFORE触发器、行操作、AFTER触发器执行的，其中任何一部操作发生错误都不会继续执行剩下的操作；如果是对事务表进行操作，那么会整个作为一个事务被回滚（Rollback），但是如果是对非事务表进行操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题；

## 5.小结 ##

需要注意的是，触发器是**行触发的，每次增加、修改或者删除记录都会触发进行处理**，编写对于复杂的触发器或者增加过多的触发器对记录的插入、更新、删除操作肯定会有比较重要的影响，因此在设计数据库的时候要有所考虑，不要将应用的处理逻辑过多的依赖于触发器来处理；

--------------------

# **事务控制和锁定语句** #

MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定，对BDB存储引擎的表进行页级别的锁定，对InnoDB的表进行行级锁定。

默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有些情况下，用户需要明确的进行锁表或者进行事务控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成；

## 1.LOCK TABLE和UNLOCK TABLE ##

LOCK TABLES：

可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，知道可以获取所有的锁定为止；

UNLOCK TABLE：

UNLOCK TABLE 可以锁定当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES 的时候，或当与服务器的连接被关闭的时候，所有当前线程锁定的表被隐含的解锁。

## 2.事务控制 ##

MySQL 通过SET AUTOCOMMIT 、START TRANSACTION 、COMMIT和ROLLBACK等语句支持本地事务。

1.  START TRANSACTION或者 BEGIN 语句可以开始一项新的事务；
2.  COMMIT 或者ROLLBACK用来提交或者回滚事务；
3.  CHAIN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即开启一个新的事务，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接；
4.  SET AUTOCOMMIT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚；

如果只是对某些语句需要进行事务控制，则使用START TRANSACTION 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改AUTOCOMMIT来控制事务比较方便，这样不用在每个事务开始的时候再执行START TRANSACTION语句；

如果在提交的时候使用COMMIT AND CHAIN，那么会在提交的时候立即开始一个新的事务；

如果在锁表期间，使用start transaction 命令开始一个新的事务，会造成一个隐含的unlock tables 被执行；

在事务中可以通过定义一个SAVEPOINT，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的SAVEPOINT，满足不同的条件的时候，回滚不同的SAVEPOINT。

## 3.分布式事务和使用 ##

MySQL从5.0.3开始支持分布式事务，当前分布式事务只支持InnoDB存储引擎，一个分布式事务会涉及到多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成或者一起被回滚；

### 1.分布式事务的原理 ###

在MySQL中，使用分布式事务的应用程序涉及一个或者多个资源管理器。

1.  资源管理器（RM）：

用于提供通向事务资源的途径。数据库服务器是一种资源管理器。该管理器必须可以提交或者回滚由RM管理的事务。

1.  事务管理器（TM）：

用于协调作为一个分布式事务一部分的事务。TM与管理每个事务的RMs进行通信。在一个分布式事务中，各个单位事务均是分布式事务的“分支事务”。分布式事务和分支事务通过一种命名方法进行标识；

MySQL在执行XA MySQL的时候，MySQL服务器相当于一个用于管理分布式事务中的XA事务的资源管理器。与MySQL服务器连接的客户端相当于事务管理器；

要执行一个分布式事务，必须知道这个分布式事务涉及到了哪些资源管理器，并且把每个资源管理器的事务执行到事务可以被提交或者回滚。根据每个资源管理器报告的有关执行情况的内容，这些分支事务必须作为一个原子性操作全部提交或者回滚。要管理一个分布式事务，必须要考虑任何组件或者连接网络可能会出现的故障；

用于执行分布式事务的过程使用两阶段提交，发生时间在由分布式事务的各个分支需要进行的行动已经被执行之后；

### 2.分布式事务的语法 ###

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

XA END xid\[SUSPEND\[FOR MIGRATE\]\]

XA PREPARE xid   是事务进入PREPARE状态，也就是两阶段提交的第一个提交阶段；

XA COMMIT xid \[ONE PHASE\]   提交分支事务

XA ROLLBACK xid   回滚分支事务；

XA RECOVER  返回当前数据库中处于PREPARE状态的分支事务的详细信息；

分布式事务的关键之处在于如何确保分布式事务的完整性，以及在某个分支出现问题的时候的故障解决。XA的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理，包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等；

### 3.存在的问题 ###

MySQL的分布式事务还存在比较严重的缺陷，在数据库或者应用异常的情况下，可能会导致分布式事务的不完整。如果应用对于数据的完整性要求不是很高，则可以考虑使用。如果应用对事务的完整性有比较高的要求，那么对于当前版本，则不推荐使用分布式事务；

## 4.小结 ##

事务控制和锁定是MySQL的重要特点之一；

--------------------

# SQL中的安全问题 #

## 1.SQL注入简介 ##

SQL注入（SQL Injection）就是利用某些数据库的外部接口将用户数据插入到数据库操作语言（SQL）当中，从而达到入侵数据库乃至操作系统的目的。它的产生主要是由于程序对用户输入的数据没有进行严格的过滤，导致非法数据库查询语句的执行；

SQL注入攻击具有很大的危害，攻击者可以利用它读取、修改或者删除数据库内的数据，获取数据中的用户名和密码等敏感信息，甚至可以获取数据库管理员的权限，而且，SQL注入也很难防范。网站管理员无法通过安装系统补丁或者进行简单的安全配置进行自我保护，一般的防火墙也无法拦截SQL注入攻击；

## 2.应用开发中可以采取的应对措施 ##

在SQL语句中“/\*”或者“\#”都可以将后面的语句注释掉；

### 1.PrepareStatement+Bind Variable ###

Java驱动中采用PrepareStatement语句来实现，这样在设置的参数中如果发现

“username ‘or 1=1’” ，则or 1=1会一同作为username的值出现，而不会作为SQL的一个单独条件被解析，避免了SQL注入的风险；

同样的，在使用绑定变量的情况下，企图通过注释“/\*”或者“\#”让后续天剑失效也是失败的；

### 2.使用应用程序提供的转换函数 ###

很多的应用程序接口都提供了对特殊字符进行转换的函数，恰当的使用这些函数，可以防止应用程序用户输入使应用程序生成不期望的语句；

1.  MySQL C API :

使用mysql\_real\_escape\_string() API调用

1.  MySQL ++：

使用escape和 quote修饰符

### 3.自己定义函数进行校验 ###

如果现有的函数仍然不能满足要求，则需要自己编写函数进行输入校验。输入验证是一个很复杂的问题。输入验证的途径可以分为以下几种：

（1）整理数据使之变得有效；

（2）拒绝已知的非法输入；

（3）只接受已知的合法输入；

因此，如果想要获得最好的安全状态，目前最好的解决办法就是，对用户提交或者可能改变的数据进行简单的分类，分别应用正则表达式来对用户提供的输入数据进行严格的检测和验证；

已知的非法符号有：“ ’ ”、“ ；”，“ （ ”、“）”、“/\*” 、 “\*/”、“%”、“+”、“”、“>”、“<”、“--”、“\[”、“\]”

其实只需要过滤非法的符号组合就可以阻止已知形式的攻击，并且如果发现更新的攻击符号组合，也可以将这些符号组合添加进来，继续防范新的攻击。

特别是空格符号和与其产生相同作用的分隔关键字的符号，例如“/\*\*/”，如果能成功过滤这种符号，那么有很多注入攻击将不能发生，并且同时也要过滤它们的十六进制表示“%XX”。

## 3.小结 ##

SQL注入问题是一个数据库应用普遍存在的安全问题；

--------------------

# **SQL Mode及相关问题** #

与其他数据库不同，MySQL可以运行在不同的SQL Mode(SQL 模式下)。SQL Mode定义了MySQL应该支持的SQL语法、数据校验等，这样可以更加容易的在不同环境中使用MySQL；

## 1.MySQL SQL Mode简介 ##

在MySQL中，SQL Mode常常用来解决下面几类问题：

通过设置SQL Mode，可以完成不同严格程度的数据校验，有效的保障数据准确定；

通过设置SQL Mode为ANSI模式，来保证大多数SQL符合标准的SQL语法，这样应用在不同数据库之间进行迁移的时候，则不需要对业务进行较大的修改；

在不同数据库之间进行数据的迁移的时候，通过设置SQL Mode，可以是MySQL上的数据

select @@sql\_mode;

show warings;

select \* from t;

set session sql\_mode =’STRICT\_TRANS\_TABLES’;

## 2.SQL Mode的常见功能 ##

*  校验日期数据的合法性，这是SQL Mode的一项常用功能；

在ANSI模式下，非法日期可以插入，但是插入值全部为0，并且系统给出warning提示；而在TRADITIONAL模式下，会直接提示日期非法，拒绝插入；

*  在INSERT 或者UPDATE 过程中，如果SQL Mode处于TRADITIONAL严格模式下，运行MOD(x,0)会报错，在非严格模式下，返回null。
 *  启用NO\_BACKSLASH\_ESCAPES模式，是反斜线称谓普通字符。在导入数据的时候，如果数据中含有反斜线字符，启用该模式保证数据的正确性；
 *  启用PIPES\_AS\_CONCAT模式，将“||”视为字符串连接操作符，在Oracle等数据库中，||被视为字符串的连接操作符；

## 3.常用的SQL Mode ##

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#002060;width:133pt;"> sql_mode的值 </td> 
 <td style="background-color:#002060;width:293.1pt;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:133pt;"> ANSI </td> 
 <td style="vertical-align:top;width:293.1pt;"> 该模式是语法和行为更符合标准的SQL </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:133pt;"> STRICT_TRANS_TABLES </td> 
 <td style="vertical-align:top;width:293.1pt;"> 适用于事务表和非事务表，它是严格模式，不允许非法日期，也不允许超过字段长度的值插入字段中，对于插入不正确的值给出错误而不是警告； </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:133pt;"> TRADITIIONAL </td> 
 <td style="vertical-align:top;width:293.1pt;"> 也是严格模式，对于插入不正确的值会给出错误而不是警告，可以应用在事务表和非事务表，用在事务表的时候，只要出现错误会立即回滚； </td> 
 </tr> 
 </tbody> 
</table>

## 4.SQL Mode在迁移中如何使用 ##

如果MySQL与其他异构数据库之间有数据迁移的需求，那么MySQL中提供的数据组合模式就会对数据迁移过程有所帮助；

MySQL提供了很多数据库的组合模式名称，例如：ORACLE、DB2等，这些模式组合是由很小的sql\_mode组合而成的，在异构数据库迁移数据的时候可以尝试使用这些模式来导出适合于目标数据库格式的数据，这样就使得导出数据更容易导入目标数据库；

## 5.小结 ##

1.  SQL Mode的严格模式，为MySQL提供了很好的数据校验功能，保证了数据的准确性，TRADITIONAL、STRICT\_TRANS\_TABLES是常用的两种严格模式；
2.  SQL Mode的多种模式可以灵活组合，组合之后的模式可以更好的满足应用程序的需求，尤其在数据迁移的时候，SQL Mode的使用很重要；

--------------------

# Mysql分区 #

分区是指根据一定的规则，数据库把一个表分解成为多个更小、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或者一个索引，但是实际上这个表可能由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分处理，分区对应用来讲是完全透明的；不影响应用的业务逻辑；

MySQL 分区的优点如下：

1.  和单个磁盘或者文件系统相比，可以存储更多的数据；
2.  优化查询。在where子句中包含分区条件的时候，可以只扫描必要的一个或者多个分区来提高查询的效率；同时在涉及SUM()和COUNT()这类聚合函数的查询时候，可以容易地在每个分区上并行处理，最终只需要汇总所有分区得到的结果；
3.  对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速的删除数据；
4.  跨多个磁盘来分散数据查询，以获得更大的查询吞吐量；

## 1.分区概述 ##

分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键（partition key）的概念，分区键用于根据某个区间值（或者范围值）、特定值列表或者HASH函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象变成一些小对象；

可以通过命令SHOW VARIABLES LIKE ‘%partition%’;来确定当前的MySQL是否支持分区；

MySQL支持使用大部分存储引擎（如MyISAM、InnoDB、Memory等存储引擎）创建分区；MySQL不支持使用MERGE或者CVS存储引擎来创建分区；

同一个分区表的所有分区必须使用同一个存储引擎；即同一个表上，不能对一个分区使用MyISAM引擎，对另一个分区使用InnoDB；但是，可以在同一个MySQL服务器上，甚至同一个数据库中，对于不同的分区表使用不同的存储引擎；

和任何分区表设置存储引擎一样，分区表设置存储引擎，只能使用\[STORAGE\]ENGINE子句。\[STORAGE\]ENGINE 子句必须列在CREATE TABLE语句中的其他任何分区选项之前。

举例：使用InnoDB引擎并有6个HASH分区的表：

CREATE TABLE emp(empid INT,salary DECIMAL(7,2) ,birth\_date DATE) ENGINE=INNODB

PARTITION BY HASH(MONTH(birth\_date))

PARTITIONS 6;

注意：MySQL分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区；反过来也一样的，不能只对索引分区而不对表分区，同时也不能只对表的一部分数据进行分区。MySQL的分区表上创建的索引一定是本地LOCAL索引；

## 2.分区类型 ##

<table> 
 <tbody> 
 <tr> 
 <td style="background-color:#7030a0;width:76.3pt;"> 分区 </td> 
 <td style="background-color:#7030a0;width:349.8pt;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:76.3pt;"> RANGE分区 </td> 
 <td style="vertical-align:top;width:349.8pt;"> 基于一个给定连续区间范围，把数据分配到不同的分区 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:76.3pt;"> LIST分区 </td> 
 <td style="vertical-align:top;width:349.8pt;"> 类似于RANGE分区，区别在于LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区； </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:76.3pt;"> HASH分区 </td> 
 <td style="vertical-align:top;width:349.8pt;"> 基于给定的分区个数，把数据分配到不同分区 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:76.3pt;"> KEY分区 </td> 
 <td style="vertical-align:top;width:349.8pt;"> 类似于HASH分区 </td> 
 </tr> 
 </tbody> 
</table>

注意：

数据库名和表名在Windows中是大小写不敏感的，而在大多数UNIX或者Linux系统中是大小写敏感的；但是分区的名字是不区分大小写的，如果创建分区的时候分区名仅仅是大小写的区别，则会报错；

## 1.Range分区 ##

按照RANGE分区的表是利用取值范围将数据进行分区，区间要连续并且不能互相重叠，使用VALUES LESS THAN操作符进行分区定义；

【在建表的时候就创建好分区】

mysql> CREATE TABLE part\_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam

PARTITION BY RANGE (year(c3)) (

PARTITION p0 VALUES LESS THAN (1995),

PARTITION p1 VALUES LESS THAN (1996) ,

PARTITION p2 VALUES LESS THAN (1997) ,

PARTITION p3 VALUES LESS THAN (1998) ,

PARTITION p4 VALUES LESS THAN (1999) ,

PARTITION p5 VALUES LESS THAN (2000) ,

PARTITION p6 VALUES LESS THAN (2001) ,

PARTITION p7 VALUES LESS THAN (2002) ,

PARTITION p8 VALUES LESS THAN (2003) ,

PARTITION p9 VALUES LESS THAN (2004) ,

PARTITION p10 VALUES LESS THAN (2010),

PARTITION p11 VALUES LESS THAN MAXVALUE );

注意:

1.  每个分区嗾使按顺序进行定义的，从最低到最高。这是PARTITION BY RANGE语法的要求；
2.  最后一行，考虑到可能的最大值
3.  MySQL 5.1支持整数列分区，要想在日期或者字符串列上进行分区，就得使用函数进行转换，但是查询如果不使用函数转换，那么就无法利用RANGE分区特性来提高查询性能；
4.  MySQL 5.5改进了RANGE分区功能，提供了RANGE COLUMNS分区支持非整数分区，这样创建日期分区就不需要通过函数进行转换了；

【增加分区语法】

ALTER TABLE table\_name ADD PARTITION (PARTITION p\_name VALUES LESS THAN (2050));

RANGE分区功能特别适用于以下两种情况：

1.  当需要删除过期的数据的时候，只需要简单的ALTER TABLE emp DROP PARTITION p\_name来删除p\_name分区中的数据。对于具有上百万条记录的表来讲，删除分区要比运行一个DELETE 语句有效的多；
2.  经常运行包含分区键的查询，MySQL可以很快的确定只有某一个或者某些分区需要扫描，因为其他分区不可能包含符合该WHERE子句的任何记录。例如：检索商店ID大于等于25的记录数，MySQL只需要扫描符合的分区即可；

### 2.List分区 ###

LIST分区是建立离散的值列表告诉数据库特定的值属于哪个分区，LIST分区和RANGE分区的区别是在LIST分区是从属于一个枚举列表的值的集合，RANGE分区是从属于一个连续区间值的集合；

与RANGE分区不同，LIST分区不必声明任何特定的顺序；

【MySQL 5.1中LIST分区只能匹配整数列表】

create table list(

a int(11),

b int(11)

)(partition by list (b)

partition p0 values in (1,3,5,7,9),

partition p1 values in (2,4,6,8,0)

);

【MySQL5.5中LIST分区支持非整数分区】

create table list(

a varchar(30),

b int(11)

)(partition by list  columns(a)

partition p0 values in (‘lodging’,’food’),

partition p1 values in (‘flights’,’ground’),

partition p1 values in (‘leisure’,’customer’,’fees’),

);

### 3.Columns分区 ###

Columns分区是在MySQL 5.5引入的分区，引入Columns分区解决了MySQL 5.5版本之前RANGE分区和LIST分区只支持整数分区，从而导致需要额外的函数计算得到整数或者通通过额外的转换表来转换为整数在分区的问题。Columns分区可以细分为RANGE Columns分区和LIST Columns分区，RANGE Columns分区和LIST Columns分区都支持整数、日期时间、字符串三大数据类型；

整数类型：tinyint、smallint、mediumint、int和bigint；其他数值类型都不支持，Decimal和Float不支持；

日期类型：date和datetime

字符类型：char、varchar、binary和varbinary；不支持text和blob类型作为分区键；

对比RANGE分区和LIST分区，Columns分区的亮点除了支持数据类型增加之外，另外的一个亮点就是Columns分区还支持**多列分区**。

其实，RANGE Columns分区键的比较（元组的比较）其实就是多列排序，先根据第一个字段排序然后根据第二个字段进行排序，根据排序结果来分区存放数据。和RANGE单字段分区排序实际上是一致的；

### 4.Hash分区 ###

HASH分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能的平均分布。对一个表执行HASH分区的时候，MySQL会对分区键应用一个散列函数，依次确定数据应当放在N个分区中的哪个分区中；

MySQL支持两种HASH分区，常规HASH分区和线性的HASH分区（LINEAR HASH分区）；常规HASH使用的是取模算法，线性HASH分区使用的是一个线性的2的幂的运算法则；

【常规HASH分区】

create table hash(

a int(11),

b datetime

)partition by hash (YEAR(b)

partitions num;

说明num是指定分区的个数；

使用公式MOD(b,num)可以计算出该条记录会被分配到哪个分区中；

常规分区的缺点：如果新增加分区，则需要对原有的分区数据重新计算重新分区，会造成很大的代价，所以常规分区不适合需要灵活变动分区的需求；

【线性HASH分区】

create table hash(

a int(11),

b datetime

)partition by **linear** hash (YEAR(b)

partitions num;

说明：

同样使用线性HASH的时候，指定记录保存在哪个分区是可以计算出来的；

线性HASH分区的优点是：在分区维护（包括增加、删除、合并、拆分分区）时候，MySQL能够处理的更加迅速；缺点是：对比常规HASH（取模）的时候，线性HASH各个分区之间数据的分布不太均衡；

### 5.Key分区 ###

按照Key进行分区非常类似于按照HASH进行分区，只不过HASH分区允许使用用户自定义的表达式，而Key分区不允许使用用户自定义的表达式，需要使用MySQL服务器提供的HASH函数；同时**HASH****分区只支持整数分区，而Key****分区支持使用除了BLOB** **或者 TEXT****类型外其他类型的列作为分区键；**

create table t\_key(

a int(11),

b datetime)

partition by key (b)

partitions 4;

说明：与HASH分区不同，创建Key分区表的时候，可以不指定分区键，

【默认会首先选择使用主键作为分区键】

create table t\_key(

id int(11) not null,

b datetime,

**primary key(id)**

)

partition by key ()

partitions 4;

【在没有主键的情况下，会选择使用非空唯一键作为分区键f】

create table t\_key(

id int(11) not null,

b datetime,

**unique key(id)**

)

partition by key ()

partitions 4;

注意：作为分区键的唯一键必须是非空的，如果不是非空的，依然会报错；

在没有主键、也没有唯一键的情况下，就不能不指定分区键了；

和HASH分区类似，在KEY分区中使用关键字LINEAR具有同样的作用，也就是LINEAR KEY分区的时候，分区的编号是通过2的幂算法得到的，而不是通过取模得到的；

KEY分区和HASH分区类似，在处理大量的数据的时候，能够有效的分散热点；

### 6.子分区 ###

到底还是开源软件，MySQL对复合分区的支持远远没有Oracle丰富。

在MySQL 5.6版本中，只支持RANGE和LIST的子分区，且子分区的类型只能为HASH和KEY。

【定义子分区】

CREATE TABLE ts (id INT, purchased DATE)

PARTITION BY RANGE( YEAR(purchased) )

**SUBPARTITION** **BY** **HASH**( TO\_DAYS(purchased) )

**SUBPARTITIONS** **2** (

PARTITION p0 VALUES LESS THAN (1990),

PARTITION p1 VALUES LESS THAN (2000),

PARTITION p2 VALUES LESS THAN MAXVALUE

);

说明：

表ts中有3个RANGE分区，这3个分区中的每个分区（p0、p1、p2）又进一步的分为2个子分区，实际上，整个表被分成为了3\*2=6个分区；

上述创建语句中，最外层是RANGE分区，分为3个区，里面是HASH子分区，分为2个区，这样，该表一共分了3\*2=6个分区。

【用SUBPARTITION语句来显示定义子分区】

CREATE TABLE ts (id INT, purchased DATE)

PARTITION BY RANGE( YEAR(purchased) )

**  SUBPARTITION BY HASH( TO\_DAYS(purchased) ) (**

PARTITION p0 VALUES LESS THAN (1990) (

**SUBPARTITION s0,**

**            SUBPARTITION s1**

PARTITION p1 VALUES LESS THAN (2000) (

**  SUBPARTITION s2,**

**            SUBPARTITION s3**

PARTITION p2 VALUES LESS THAN MAXVALUE (

** SUBPARTITION s4,**

**            SUBPARTITION s5**

)

);

注意：

1> 如果你在分区中使用了SUBPARTITION语句，则每个分区中都必须定义，且每个分区中子分区的数量必须保持一致。譬如以下两种用法就会报错：

复制代码

CREATE TABLE ts (id INT, purchased DATE)

PARTITION BY RANGE( YEAR(purchased) )

SUBPARTITION BY HASH( TO\_DAYS(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990) (

SUBPARTITION s0,

SUBPARTITION s1

PARTITION p1 VALUES LESS THAN (2000) (

SUBPARTITION s2

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s3,

SUBPARTITION s4

)

);

2> 在SUBPARTITION语句中，可指定该分区的物理位置。譬如：

CREATE TABLE ts (id INT, purchased DATE)

PARTITION BY RANGE(YEAR(purchased))

SUBPARTITION BY HASH( TO\_DAYS(purchased) ) (

PARTITION p0 VALUES LESS THAN (1990) (

SUBPARTITION s0a

DATA DIRECTORY = '/disk0'

INDEX DIRECTORY = '/disk1',

SUBPARTITION s0b

DATA DIRECTORY = '/disk2'

INDEX DIRECTORY = '/disk3'

PARTITION p1 VALUES LESS THAN (2000) (

SUBPARTITION s1a

DATA DIRECTORY = '/disk4/data'

INDEX DIRECTORY = '/disk4/idx',

SUBPARTITION s1b

DATA DIRECTORY = '/disk5/data'

INDEX DIRECTORY = '/disk5/idx'

PARTITION p2 VALUES LESS THAN MAXVALUE (

SUBPARTITION s2a,

SUBPARTITION s2b

)

);

说明：

以上这个创建语句，将不同的分区分布到不同的物理路径下，无疑会极大的分散IO，这一点还是蛮吸引人的。

复合分区适用于保存非常大量的数据记录；

### 7.MySQL分区处理null值的方式 ###

MySQL不禁止在分区键值上使用NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL的分区把NULL当做零值，或者一个最小值处理；

注意：

1.  RANGE分区中，NULL值会被当做最小值来处理；
2.  LIST分区中，NULL值出现在枚举列表中，否则不被接收（插入数据时候会报错！）
3.  HASH/KEY分区中，NULL值会被当做零值来处理；

### 8.MySQL分区键的规则 ###

**【MySQL****对分区键的要求】**

1.  无论是哪种MySQL分区类型，要么分区表上没有主键或者唯一键，此时就可以指定普通列作为对应的分区键（该列可以为null）；
2.  如果分区表上已经有了主键/唯一键，此时就不能再使用除了主键/唯一键字段之外的字段作为分区键了，否则会报错；

## 3.分区管理 ##

MySQL提供了添加、删除、重定义、合并、拆分分区的命令，这些操作都可以通过ALTER TABLE命令来进行实现；

### 1.RANGE&LIST分区管理 ###

在添加、删除、重新定义分区的处理上，RANGE分区和LIST分区非常的相似，所以合并一起来说。

【**从一个****RANGE****或者LIST****分区的表中：**删除一个分区】

**ALTER TABLE table\_name DROP PARTITION p\_name;**语句来实现

注意：

1.  删除分区的命令执行之后，并不显示实际从表中删除的行数，并不是真的没有记录被删除；
2.  删除LIST分区和删除RANGE分区使用的语句完全相同，只不过删除LIST分区之后，由于在LIST分区的定义中不再包含已经被删除了的分区的值列表，所以后续无法写入包含已经删除了的分区的值列表的数据；

【**从一个****RANGE****或者LIST****分区的表中：**增加一个分区】

**ALTER TABLE  ADD PARTITION** **语句来实现。**

注意：

（1）对于RANGE分区来讲，可以使用 ADD PARTITION方式添加新的分区到分区列表的最大一端。

只能从RANGE分区列表中最大端的方式增加分区，否则会出现错误；

（2）增加LIST分区的时候，不能添加一个包含分区值列表中的任意值分区，也就是对一个固定的分区键值，必须指定并且只能指定一个唯一的分区，否则会出现错误；

【合并或者拆分RANGE分区】

MySQL也提供了在不丢失数据的情况下，通过重新定义分区的语句**ALTER TABLE REORGANIZE PARTITION INTO**重新定义分区。

重新定义分区可以用来拆分一个RANGE分区，或者用来合并多个相邻RANGE分区为一个RANGE分区或者多个RANGE分区：

mysql > alter table emp\_date reoranize partition p1,p2,p3 into (

partition p1 values less than(2015)

);

注意：重新定义RANGE分区的时候，只能够重新定义相邻的分区，不能跳过某个RANGE分区进行重新定义，同时重新定义的分区区间必须和原分区区间覆盖相同的区间；也不能使用重新定义的分区来改变分区的类型，例如，不能把RANGE分区变为HASH分区，也不能把HASH分区变成RANGE分区；

【合并或者拆分LIST分区】

对于LIST分区，也可以使用ALTER TABLE REOGRANIZE PARTITION INTO语句重新定义分区；

可以通过增加分区和重定义分区来实现在一个原有的分区上增加分区；

第一步：首先需要增加不重复值列表的分区：

mysql > alter table expenses add partition (partition p6 values in (11));

第二步：通过REORGANIZE PARTITION 的方式重新定义分区‘

例如：合并p4和p6两个分区为新的p4分区：

mysql > alter table expenses reorganize partition p4,p5,p6 into(

partition p4 values in (6,11),

partition p5 values(7,8)

);

注意：类似于RANGE分区，重新定义LIST分区的时候，只能够重新定义相邻的分区，不能跳过LIST分区进行重新的定义，否则会报一下的信息；

### 2.HASH&KEY分区管理 ###

在改变分区的设置方面，HASH分区和KEY分区的表非常的类似，所以这两种分区的管理方式合并在一起讨论；

不能通过RANGE或者LIST分区表中删除分区的方式来从HASH或者KEY分区的表中删除分区，而可以通过ALTER TABLE COALEASCE语句来合并HASH分区或者KEY分区；

【**从一个****HASH****或者KEY****分区的表中：删除分区**】

ALTER TABLE COALEASCE

例如：需要减少HASH分区的数量，从4个分区变为2个分区：可以执行下面的ALTER TABLE命令：

mysql > ALTER TABLE emp COALESCE PARTITION 2;

COALESCE不能用来增加分区的数量，否则会出现错误；

【**从一个****HASH****或者KEY****分区的表中：增加分区**】

ALTER TABLE ADD PARTITION 语句可以实现为HASH或者KEY表增加分区；

举例：为emp表增加8个分区：

mysql > alter table emp add partition 8;

--------------------

[http_www.cnblogs.com_shijianchuzhenzhi_p_6383117.html]: http://www.cnblogs.com/shijianchuzhenzhi/p/6383117.html
[http_wwty.iteye.com_blog_698353]: http://wwty.iteye.com/blog/698353
[http_www.cnblogs.com_caoruiy_p_4485273.html]: http://www.cnblogs.com/caoruiy/p/4485273.html
[http_www.cnblogs.com_xiaozl_archive_2012_10_30_2746189.html]: http://www.cnblogs.com/xiaozl/archive/2012/10/30/2746189.html
[http_www.jb51.net_article_39255.htm]: http://www.jb51.net/article/39255.htm