数据库之——索引、触发器、事务（存储引擎）

一. 数据库

创新互联建站长期为上1000家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为建湖企业提供专业的网站制作、网站建设，建湖网站改版等技术服务。拥有十多年丰富建站经验和众多成功案例,为您定制开发。

    数据库（DataBase）是按照数据结构来组织、存储和管理数据的仓库。其主要特点有如下几个方面：

1. 实现数据共享

   数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享；

2. 减少数据的冗余度

   同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性；

3. 数据的独立性

   数据的独立性包括逻辑独立性（数据库中数据库的逻辑结构和应用程序相互独立）和物理独立性（数据物理结构的变化不影响数据的逻辑结构）；

4. 数据实现集中控制

   文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系；

5. 数据一致性和可维护性，以确保数据的安全性和可靠性

   主要包括：①安全性控制：以防止数据丢失、错误更新和越权使用；②完整性控制：保证数据的正确性、有效性和相容性；③并发控制：使在同一时间周期内，允许对数据实现多路存取，又能防止用户之间的不正常交互作用；

6. 故障恢复

   由数据库管理系统提供一套方法，可及时发现故障和修复故障，从而防止数据被破坏。数据库系统能尽快恢复数据库系统运行时出现的故障，可能是物理上或是逻辑上的错误。比如对系统的误操作造成的数据错误等；

二. 索引

    索引是对数据库表中一个或多个列的值进行排序的数据结构，是用于提高在数据库表中访问数据的速度的数据库对象。其实索引就相当于一本书的目录，如果没有索引，要想在数据库中查找某一特定的值就需要遍历整个数据库表，但是有了索引之后就可以在索引当中查找，有助于更快地获取信息；

    索引可分为聚集索引和非聚集索引；对于聚集索引：是按照数据存放的物理位置为顺序的；而非聚集索引中，表数据存储顺序与索引顺序无关；一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种；如果一张表没有聚集索引，那么它被称为“堆集”，这样的表中的数据行没有特定的顺序，所有的新行将被添加到表的末尾位置。

    一条索引记录中包含的基本信息有：键值（定义索引时指定的所有字段的值）+逻辑指针（指向数据页或另一索引页）；根据数据库的功能，可以在数据库设计器中创建三种索引：

• 唯一索引 

唯一索引是不允许其中任何两行具有相同索引值的索引；

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓；

使用语句为：

CREATE UNIQUE INDEX 索引名称
ON 表名称 (列名称，如果为多个列用逗号隔开)

对于一个简单索引的创建，只需将唯一索引中的UNIQUE去掉就可以了；

• 主键索引

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键；

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问；

• 聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引；如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

    虽然说建立索引的目的是加快对表中记录的查找或排序，但是为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录 ；其各有优缺点：

优点：创建索引可以大大提高系统的性能；

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因；

3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；

4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

5. 第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：增加索引也有许多不利的方面；

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；

2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大；

3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

    因此，对于索引的使用和建立，应该视情况而定，比如对于那些查询次数很少或者数据值也比较少的列就不必要建立索引，因为不仅不能提高多少查询速度，反而会耗费一定的空间和降低系统的维护程度。

三. 事务

    事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。

    对于事务有如下相关属性：

• 原子性（Atomic）（Atomicity)

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的；如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标，原子性消除了系统处理操作子集的可能性；

• 一致性（Consistent）(Consistency)

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的；

• 隔离性（Insulation）(Isolation)

由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量；

• 持久性（Duration）(Durability）

事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持；

事务的三种模型：

1. 隐式事务是指每一条数据操作语句都自动地成为一个事务，事务的开始是隐式的，事务的结束有明确的标记；

2. 显式事务是指有显式的开始和结束标记的事务，每个事务都有显式的开始和结束标记；

3. 自动事务是系统自动默认的，开始和结束不用标记；

使用事务的语句：

1. 开始事物：BEGIN  TRANSACTION

2. 提交事物：COMMIT  TRANSACTION

3. 回滚事务：ROLLBACK  TRANSACTION

事务的保存点：

1. SAVE  TRANSACTION  保存点名称 ——自定义保存点的名称和位置

2. ROLLBACK  TRANSACTION  保存点名称 ——回滚到自定义的保存点

存储引擎：

        MySQL中的数据用各种不同的技术存储在文件（或者内存）中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力，通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)；

        MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及需要数据结合什么性能和功能的时候能提供最大的灵活性。

下面主要介绍几种常用的存储引擎：

MyISAM

特性
不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用
表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据

适用场景
不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少（阻塞问题）
以读为主
数据一致性要求不是非常高

最佳实践
尽量索引（缓存机制）
调整读写优先级，根据实际需求确保重要操作更优先
启用延迟插入改善大批量写入性能
尽量顺序操作让insert数据都写入到尾部，减少阻塞
分解大的操作，降低单个操作的阻塞时间
降低并发数，某些高并发场景通过应用来进行排队机制
对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

InnoDB

特性
具有较好的事务支持：支持4个事务隔离级别，支持多版本读
行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
读写阻塞与事务隔离级别相关
具有非常高效的缓存特性：能缓存索引，也能缓存数据
整个表和主键以Cluster方式存储，组成一颗平衡树
所有Secondary Index都会保存主键信息

适用场景
需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

最佳实践
主键尽可能小，避免给Secondary index带来过大的空间负担
避免全表扫描，因为会使用表锁
尽可能缓存所有的索引和数据，提高响应速度
在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
避免主键更新，因为这会带来大量的数据移动

NDBCluster

特性
分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分
支持事务：和Innodb一样，支持事务
可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互
内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

适用场景
具有非常高的并发需求
对单个请求的响应并不是非常的critical
查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding

最佳实践
尽可能让查询简单，避免数据的跨节点传输
尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点
在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时

注：以上三个存储引擎是目前相对主流的存储引擎，还有其他类似如：Memory，Merge，CSV，Archive等存储引擎的使用场景都相对较少，这里就不一一分析了。

另外，可以使用SHOW TABLE STATUS FROMDBname来查看当前数据库中各表的引擎；

创建一个新表时，可以通过在CREATE语句中ENGINE或TYPE选项来告诉MySQL要创建什么类型的表：

CREATE TABLE t (i INT) ENGINE = INNODB;

CREATE TABLE t (i INT) TYPE = MEMORY;
如果省略掉ENGINE或TYPE选项，默认的存储引擎被使用。当MySQL被用MySQL配置向导安装在Windows平台上，InnoDB存储引擎替代MyISAM存储引擎作为默认。当不可用的类型被指定时，自动用InnoDB表来替代。

还可以把表从一个类型转到另一个类型，使用ALTERTABLE语句：

ALTER TABLE t ENGINE = MYISAM;

ALTER TABLE t TYPE = BDB;

四. 触发器

    触发器是一种特殊类型的存储过程，它在指定的表中的数据进行变化的时候自动生效；触发器是一个特殊的事务单元，可以引用其他表中的列执行特殊的业务规则或数据逻辑关系。当出现错误时，可以执行rollback transaction操作将整个触发器以及触发它的T-SQL语句一并回滚（不需显示声明begin transaction）；唤醒调用触发器以响应INSERT、UPDATE 或 DELETE 语句。触发器可以查询其它表，并可以包含复杂的Transact-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚，即撤销。

触发器类型分为两种：

AFTER触发器：这种触发器将在数据变动（insert、update、delete动作）完成以后才触发。对变动的数         据进行检查，如果发现错误，则拒绝或回滚变动的数据；

INSTEAD OF触发器：这种触发器将在数据变动以前被触发，并取代变动数据的操作（insert、update、            delete操作），转而去执行触发器定义的操作；

在建立触发器时，还必须指定触发操作：insert、update、delete操作，至少指定一种，也可指定多种；

创建触发器：

CREATE TRIGGER trigger_name//触发器名称
ON { table | view }//在其上执行的表或视图
[ WITH ENCRYPTION ]//可防止触发器作为SQL Server的一部分发布
{
    { 
        { FOR | AFTER | INSTEAD OF } //触发器类别，决定是after还是instead of
        { [ INSERT ][,] [ DELETE ][,] [ UPDATE ] }//指定激发触发器的关键字
            
            [ WITH APPEND ]
            [ NOT FOR REPLICATION ]//表示当复制进程更改触发器所涉及的表时，不应执行该触发器
            AS//触发器要执行的操作
            [ { IF UPDATE ( column )//测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。
            [ { AND | OR } UPDATE ( column ) ]
            [ ...n ]
            | IF ( COLUMNS_UPDATED(){bitwise_operator//位运算符} updated_bitmask )//测试是否插入或更新了提及的列，仅用于UPDATE和INSERT触发器中
            {comparison_operator//比较运算符}column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]//SQL Server不支持在触发器中包含所有的create语句、DROP语句等
    }
}

《完》