MySQL技术内幕第二章（3)

MySQL技术内幕第二章（3）

十二、事务处理

事务指的是一组SQL语句，它们是一个执行单位，且在必要时还可以取消。事务处理是通过使用提交和回滚功能实现的。
事务系统通常具有“ACID”的四种特性，即

（1）原子性。构成事务的所有语句应该是一个独立的逻辑单元。
（2）一致性。数据库在事务的执行前后都必须是一致的。
（3）独立性。事务之间不应该相互影响。
（4）持久性。当事务执行完成时，其影响将永久性地记录到数据库里。

默认情况下，MySQL的运行模式是自动提交，即每条语句所做的更改会立刻提交到数据库，并永久保存下来。事实上，这相当于每条语句都被隐式地当作了一个事务来执行。
如果想要显式地执行事务，那么需要禁用自动提交模式，并主动告知MySQL何时提交或何时回滚。
常用的方法：

（1）调用START TRANSACTION语句以及后续的COMMIT和ROLLBACK语句。
（2）利用SET语句直接操作自动提交模式的状态。
SET autocommit=0; 禁用自动提交模式。
SET autocommit=1; 启动自动提交模式。

使用事务保存点：使用SAVEPOINT语句，可以使ROLLBACK回滚到这个保存点。
事务隔离（解决并发问题）：
多个事务同时运行可能出现的各类问题：

（1）脏读。 （2）不可重复读。 （3）幻影行。

事务隔离级别：

（1）READ UNCOMMITTED。 它允许某个事务看到其他事务尚未提交的行修改。
（2）READ COMMITTED。它只允许某个事务看到其他事务已经提交的行修改。
（3）REPEATABLE READ。 如果某个事务两次执行同一条SELECT语句，其结果是可重复的。
（4）SERIALIZABLE。 隔离级别最高，对于某个事务正在读取某些行，只有等到该事务完成 才能被其他事务所修改。

InnoDB存储引擎默认的隔离级别是REPEATABLE READ。
更改隔离级别的方式：

（1）在服务器启动时使用–transaction–isolation选项。
（2）在服务器运行时，使用SET TRANSACTION语句。

SET GLOBAL TRANSACTION ISOLATION LEVEL level;   --修改全局隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL level;   --修改当前会话隔离级别
SET TRANSACTION ISOLATION LEVEL level;   --修改下一个事务的隔离级别。

十三、外键和引用完整性

利用外键关系，你可以在一个表里声明与另一个表里的某个索引相关联的索引。在插入、删除、更新等操作中，外键可以帮我们维护数据一致性。
InnoDB存储引擎会通过这些规则来保证：在外键关系里不会出现不匹配的情况。这就是所谓的参照完整性。
语法：

[CONSTRAINT constraint_name]
FOREIGN KEY [fk_name] (index_columns)
REFERENCES tbl_name (index_columns)
[ON DELETE action]
[ON UPDATE action]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

1.CONSTRAINT子句。用来提供外键约束名。
2.FOREIGN KEY子句。指定子表的的索引列和提供外键ID。
3.REFERENCES子句。指定父表和其索引列的名字。
4.ON DELETE子句。可以用它来指定在删除父表的行时，子表应该做些什么。如果没有指定ON DELETE子句，那么默认行为是“拒绝从父表里删除仍被子表的行所引用的那么些行”。

（1）ON DELETE NO ACTION 子句和ON DELETE RESTRICT子句。它们的含义与省略ON DELETE子句一样。
（2）ON DELETE CASCADE子句。即在删除父表的行时，子表里与之相关联的行也会被删除掉。
（3）ON DELETE SET NULL子句。即在删除父表的行时，子表里与之相关联的行会被设置为NULL。
（4）ON DELETE SET DEFAULT子句。

5.ON UPDATE子句。用来指定当更新父表里的行时，子表应该做些什么。如果没有指定ON UPDATE子句，那么默认行为是“在子表里，拒绝执行那些会导致其外键值在父表索引里无任何匹配的插入和更新操作；并且会阻止更新那些仍然被子表所引用的父表索引值。”可供选择的action值与ON DELETE相同，并且具有相同效果。

外键关系要遵从的指导：

（1）子父表的外键列必须都建立索引。
（2）子父表的外键列必须类型兼容。
（3）不能对外键关系里的字符串列的前缀进行索引。

十四、使用FULLTEXT搜索。

全文搜索有三种类型：

1.自然语言搜索（默认类型）。搜索出包含这些字符串的那些行。
2.布尔模式搜索，在自然语言搜索的基础上，可以包含修饰符。
3.查询扩展搜索，这个搜索分两个阶段进行，第一阶段是自然语言搜索，第二阶段是将第一阶段匹配到的行和搜索的字符串一起用来搜索，这样搜索的范围就更广泛了。（即搜索一开始指定的字符串和一开始搜索匹配到的行加起来进行搜索。）

全文搜索特点：

1.全文搜索基于FULLTEXT索引。FULLTEXT索引只支持包含CHAR、VARCHAR和TEXT这几种类型的列。
2.有些内建的单词会被忽略，如“the”，“after”和“other”等。
3.太短的单词也会被忽略，不过这个可以通过设置变量修改。
4.全文搜索对“单词”的定义是，它们是由字母、数字、撇号和下划线构 成的字符序列。
5.可以在一个或多个列上创建FULLTEXT索引。

创建FULLTEXT索引语法：

1.CREATE TABLE tbl_name(....,FULLTEXT(index_columns));
2.ALTER TABLE tbl_name ADD FULLTEXT(index_columns);

1.自然语言FULLTEXT搜索：

SELECT .... FROM tbl_name WHERE MATCH(col_name) AGAINST(‘values’);

2.布尔模式的全文搜索：

SELECT .... FROM tbl_name WHERE MATCH(col_name) AGAINST(‘values’ IN BOOLEAN MODE);

布尔模式可以搜索短语，短语需要用双引号引起来。还可以为搜索字符串里的单词加上一些修饰符，在单词前面加上加号，则表示该单词必须出现在匹配行里；而加上减号，则表示该单词不能出现在匹配行里；在单词后面加上*号则表示，只要行包含的单词是以搜索单词开头的，那么这些行就使匹配的。
3.查询扩展全文搜索：

SELECT .... FROM tbl_name 
WHERE MACTH(col_name)  AGAINST(‘values’ WITH QUERY EXPANSION);

4.配置全文搜索引擎:
变量ft_min_word_len和ft_max_word_len用于确定FULLTEXT索引里的那些单词所具有的最小长度和最大长度。