MySQL 技术内幕：InnoDB存储引擎——读书笔记（五）

hfutwhy

1.数据库约束
    关系型DBMS本身能够保证存储数据的完整性，不需要应用程序的控制。三种形式：实体完整性(保证有一个主键)、域完整性(保证数据的值满足特定的条件)和参照完整性(保证两表之间的关系)。
    与索引相比，约束更是一个逻辑的概念，用来保证数据的完整性；而索引是一个数据结构，更像一种物理存储方式。INFORMATION_SCHEMA库中TABLE_CONSTRAINTS表及REFERENTIAL_CONSTRAINTS表可以查看数据库的约束信息。
    对于数据的约束，可通过选择合适的类型、外键、触发器、DEFAULT等进行。默认情况下，MySQL允许非法或不正确数据的插入或更新，或内部将其转为一个合法的值。若要限制非法输入，设置sql_mode="STRICT_TRANS_TABLES"。

    对于ENUM和SET，只能限于对离散数值的约束；对于ENUM，若插入非法值，将插入空字符串作为特殊错误值。
    InnoDB的外键建立时会自动在该列上加一个索引，且约束是即时检查的。(在数据导入时，可能会导致花费大量时间在约束检查上，解决办法：在导入时忽视外键检查。SET FOREIGN_KEY_CHECKS=0; LOAD DATA……; SET FOREIGN_KEY_CHECKS=1;)

2.物化视图
    根据基表实际存在的实表，可以用于预先计算并保存表连接或***等耗时较多的操作结果。Oracle下，创建方式包括BUILD IMMEDIATE和BUILD DEFERRED。物化视图刷新模式有两种：ON DEMAND和ON COMMIT，刷新的方法有四种：FAST(增量刷新)、COMPLETE(完全刷新)、FORCE(是否可以快速刷新)和NEVER。
    查询重写指对物化视图的基表进行查询时，Oracle会自动判断是否通过查询物化视图来得到结果；如果可以，则避免了***或连接操作。但MySQL不支持物化视图，不过可以通过基表和触发器构建类似的机制。

3.分区
    MySQL分区类型仅支持水平分区(同一表中不同行的记录分配到不同的物理文件)，而不支持垂直分区(将不同的列分配到不同文件)。
    四种类型的分区：

• RANGE分区：给定连续区间的列值。(VALUE LESS THAN；需指定MAXVALUE值的分区，主要用于日期列的分区；对于RANGE分区的查询，优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()和UNIX_TIMESTAMP()函数进行优化选择。)
• LIST分区：离散值。(VALUE IN；对于未定义的插入，MySQL会抛出异常；对于多条记录同时插入过程中存在未定义的值时，MyISAM分区会允许之前数据插入，而拒绝之后的插入，但是InnoDB视插入为一个事务，从而ROLLBACK整个插入)
• HASH分区：自定义的表达式的返回值，返回值不为负。(PARTITION BY HASH (expr)，将数据均匀分布；还可按LINEAR HASH分区，区别在于算法不同)
• KEY分区：根据MySQL中提供的哈希函数进行分区。

    不管是哪种类型的分区，若表中存在主键或唯一索引时，分区列必须是唯一索引的一部分。如果两者都没有，则可指定任何一列为分区列。并且分区的条件必须是整数。
    启用分区后，表不再由一个ibd文件组成，而是由建立分区时的各个分区ibd文件组成。可以通过INFORMATION_SCHEMA.PARTITIONS查看分区表情况。
    (MySQL 5.5开始支持COLUMNS分区，可直接使用非整数的数据进行分区，分区根据类型直接比较而得，不需要转化为整型，可视为RANGE和LIST分区的一个进化。同时，RANGE COLUMNS分区可对多个列的值进行分区。)
    MySQL允许在RANGE和LIST的分区上再进行HASH和KEY的子分区。(每个子分区的数量必须相同；子分区的名称必须唯一；子分区可以将数据和索引分配到不同磁盘，指定DATA DIRECTORY和INDEX DIRECTORY，但是InnoDB无效)
    对于NULL值的处理，不同的分区类型会进行不同的处理。RANGE将其放入最左边的分区(MySQL把NULL视为小于任何一个非NULL值，ORDER BY排序也是如此)、LIST分区需指明NULL的存放，而HASH和KEY分区会将NULL值变为0。

    分区和性能
    分区表的使用必须要了解分区的使用环境，合理使用，尤其是OLTP系统必须谨慎。
    对于大表，一般的B+树需要2~3次磁盘IO，所以B+树可以高效地完成查询工作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。
注：如若涉及版权或者其他问题，请联系本人。