SQL----存储引擎、索引、事务、日志、触发器

    一、存储引擎

    （1）MyISAM
        MyISAM的存储文件有3个，后缀名分别为.frm（表的定义文件）、 .MYD（数据文件）、 .MYI（索引文件），  MyISAM只支持表锁，不支持事务，MyISAM采用B+Tree作为引擎结构，由于 MyISAM 中的索引和数据分别存放在不同的文件，所以在B+ 树的叶子节点中存的数据是该索引对应的数据记录的地址，由于数据与索引不在一起，所以 MyISAM是非聚簇索引，MyISAM 有单独的索引文件，在读取数据方面的性能很高 。
    （2）InnoDB
        InnoDB 的存储文件有两个，后缀名分别是 .frm（表的定义文件）、.idb（数据文件），InnoDB 支持表锁和行锁，支持事务，主要是面向在线事务处理方面的应用，InnoDB也采用B+Tree作为引擎结构，和MyISAM不同的是InnoDB将索引和数据存放在一起，在B+ 树的叶子节点中存的数据是该索引对应的数据值，InnoDB采用聚集索引的方式。InnoDB如果有主键，建立主键索引，如果没有主键但是由有唯一键，建立唯一索引，如果没有主键也没有唯一键，系统会为每一行添加一个6字节的行id，作为主键，隐藏autoincreamen是可自增长的。
    （3）Memory
        将数据放在内存中，如果数据库重启或者宕机，表数据就会丢失，非常适合存储一些临时表，默认的是哈希索引，不是B+树索引，varchar()默认是按照char()存储的，浪费内存，不支持text和BLOB类型。如果数据中有text和BLOB类型，数据库会把这些数字转换到磁盘上。
    （4）Archive
        只支持INSERT和SELECT操作，使用压缩算法将数据进行压缩后存储，压缩比例一般是1:10，主要提供插入和压缩功能。
        下面对这四种存储引擎从存储限制、事务、全文索引、数索引、哈希索引、数据缓存和外键这七个方面进行了总结，如下所示：

     二、索引

        索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针，更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 
    （1）索引的分类
        索引分为聚集索引和非聚集索引：
        聚集索引：表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。
        非聚集索引：表中的数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。
        聚簇索引的数据的物理存放顺序与索引顺序是一致的，即只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，会不断地调整数据的物理地址、分页，如果主键是自增id，只需要一页一页地写，索引结构相对紧凑 磁盘碎片少，效率也高。因此采用 InnoDB存储引擎的数据库，表一定要设置自增列作为主键，这样才能提高数据查询以及插入的效率。
        下表列出了聚集索引和非聚集索引的使用场景：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	使用	使用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	不使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	不使用	使用

    （2）索引的数据结构
         Mysql数据库索引采用的数据结构是B tree和B+ tree，一般的查找算法有顺序查找、折半查找、快速查找等，但是每种查找算法都只能应用于特定的数据结构之上，例如顺序查找依赖于顺序结构，折半查找通过二叉查找树或红黑树实现二分搜索，这样的索引数据结构还是会对数据库的数据结构有要求，而且对磁盘IO的操作依旧很频繁，因此采用了B树和B+树。
    （3）MyISAM和InnoDB索引的实现方式
        1.MyISAM
        主索引：MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。
        辅助索引：在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
        2.InnoDB
        主索引：InnoDB引擎也是使用B+树作为索引结果，但是实现方式却完全不同，InnoDB表数据文件本身就是一个索引结构，树的叶节点data域保存了完整的数据记录。InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列，则mysql自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
        辅助索引： InnoDB的所有辅助索引都引用主键作为data域，聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
    （4）索引的类型
        MySQL目前主要有以下几种索引类型：普通索引、唯一索引、主键索引、组合索引和全文索引。
        1.  普通索引
        这是最基本的索引，它没有任何限制， MyIASM中默认的BTREE类型的索引，也是大多数情况下用到的索引。

        1> 直接创建索引
        CREATE INDEX index_name ON table(column(length));
        2> 修改表结构的方式添加索引
        ALTER TABLE table_name ADD INDEX index_name ON (column(length));
        3>创建表的时候同时创建索引
        CREATE TABLE `table` (表中的属性......INDEX index_name (title(length));
        4> 删除索引
        DROP INDEX index_name ON table

         2. 唯一索引
        唯一索引就是用唯一键建立的索引，也就是说索引列的值必须唯一，但允许有空值，这和主键不同。

        1> 直接创建索引
        CREATE UNIQUE INDEX index_name ON table(column(length));
        2> 修改表结构的方式添加索引
        ALTER TABLE table_name ADD UNIQUE index_name ON (column(length));
        3> 创建表的时候同时创建索引
        CREATE TABLE `table` (表中的属性......UNIQUE index_name (title(length));
        4> 删除索引
        DROP UNIQUE index_name ON table

        3. 主键索引
        主键索引就是用主键建立的索引，也就是说索引列的值必须唯一且非空。
        4. 组合索引
        组合索引就是用多个列共同建立的索引，组合索引要符合“最左前缀”原则，即按照从左到右的顺序使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a，b，c)，其可以支持（a），（a，b），（a，b，c）3种组合进行查找，但不支持 b，c进行查找，当最左侧字段是常量引用时，索引就十分有效，因此在创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。
        5. 全文索引
        全文索引仅可用于 MyISAM 表，它们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将数据输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把数据输入现有全文索引的速度更为快，对于大容量的数据表，生成全文索引是一个非常消耗时间和硬盘空间的做法。
    （5）索引的优化
        虽然索引大大提高了查询速度，但同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件，一般情况这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些优化的方法：
        1. 使用短索引
        对串列进行索引，如果可能应该指定一个前缀长度。例如：如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
        2. 索引列排序
        MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的，因此在数据库默认的排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列创建组合索引。
        3. like语句操作
        一般情况下不鼓励使用like操作，像like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引，应尽量避免通配符在左面。

     三、事务    

        事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行，因此事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。 
    （1）事务的ACID特性
        1. A(atomicity) 原子性
      一个事务的执行被视为一个不可分割的最小单元。事务里面的操作，要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。
       2. C(consistency) 一致性 
     一个事务的执行不应该破坏数据库的完整性和业务逻辑上的一致性。例如A向B转账300元，事务完成之后，整个结果必须是A减少300元，B增加300元，这样才是整个业务处于一致的状态。
        3. I(isolation) 隔离性   
       在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自完整的数据空间，由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态时要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。通常来说，事务之间的行为不应该互相影响，然而实际情况中，事务相互影响的程度受到隔离级别的影响。
        4. D(durability) 持久性
        事务成功结束后，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。
    （3）没有隔离性会出现的问题
        1. 脏读
        脏读就是指事务A正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外事务B也访问这个数据，然后使用了这个数据，这个数据即为脏数据，事务B做了一次藏脏读。

例如：
　　张三的工资为5000，事务A中把他的工资改为8000，但事务A尚未提交。
        与此同时，
        事务B正在读取张三的工资，读取到张三的工资为8000。
        随后，
        事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
        最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

        2. 不可重复读
        不可重复读是指在事务A内，多次读同一数据，在事务A还没有结束时，另外一个事务B也访问该同一数据，那么在事务A中的两次读数据之间，由于事务B的修改，事务A两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，称为是不可重复读。

例如：
　　在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B把张三的工资改为8000，并提交了事务。
　　随后，
　　在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不一致，导致了不可重复读。

        3. 幻读
        幻读是指当事务不是独立执行时发生的一种现象，例如事务A对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行，同时，事务B也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作事务A的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

例如：
　　目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
　　此时，
　　事务B插入一条工资也为5000的记录。
　　这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。  

        4. 第一类丢失更新
        第一类丢失更新就是指在没有事务隔离的情况下，两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。

例如：
        张三的工资为5000，事务A中获取工资为5000，事务B获取工资为5000，汇入100，并提交数据库，工资变为5100，
        随后
        事务A发生异常，回滚了，恢复张三的工资为5000，这样就导致事务B的更新丢失了。

        5. 第二类丢失更新
        第二类丢失更新是不可重复读的特例，有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交，这就会造成第一次写操作失效。

例如：
        在事务A中，读取到张三的存款为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B，存储1000，把张三的存款改为6000，并提交了事务。
　　随后，
　　在事务A中，存储500，把张三的存款改为5500，并提交了事务，这样事务A的更新覆盖了事务B的更新。

    （3）隔离级别
        1. READ UNCOMMITTED（读未提交）
        在RU的隔离级别下，事务A对数据做的修改，即使没有提交，对于事务B来说也是可见的，这种问题叫脏读，这是隔离程度较低的一种隔离级别，在实际运用中会引起很多问题，因此一般不常用。
        2. READ COMMITTED（读已提交）
        在RC的隔离级别下，不会出现脏读的问题。事务A对数据做的修改，提交之后会对事务B可见，比如，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，会读到最新的数据2。在RC的隔离级别下，会出现不可重复读的问题。这个隔离级别是许多数据库的默认隔离级别。
        3. REPEATABLE READ（可重复读）
        在RR的隔离级别下，不会出现不可重复读的问题。事务A对数据做的修改，提交之后，对于先于事务A开启的事务是不可见的。比如，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，仍然只能读到1。在RR的隔离级别下，会出现幻读的问题。幻读的意思是，当某个事务在读取某个范围内的值的时候，另外一个事务在这个范围内插入了新记录，那么之前的事务再次读取这个范围的值，会读取到新插入的数据。Mysql默认的隔离级别是RR，然而mysql的innoDB引擎间隙锁成功解决了幻读的问题。
        4. SERIALIZABLE（可串行化）
        可串行化是最高的隔离级别。这种隔离级别强制要求所有事物串行执行，在这种隔离级别下，读取的每行数据都加锁，会导致大量的锁征用问题，性能最差。
        下表列出了上面这四种隔离级别出现脏读、不可重复读和幻读的情况：

隔离级别	脏读	不可重复读	幻读
读未提交	会	会	会
读已提交	不会	会	会
可重复读	不会	不会	会
可串行化	不会	不会	不会

        事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。mysql中支持事务的存储引擎有innoDB和NDB。innoDB是mysql默认的存储引擎，默认的隔离级别是RR，并且在RR的隔离级别下更进一步，通过多版本并发控制（MVC C，Multiversion Concurrency Control ）解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此innoDB的 RR隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。

     四、日志     

        事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。事务日志有两种，重做（redo）日志和撤销（undo）日志。
    （1）redo log
        在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA（数据库管理员）口中常说的“日志先行”(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态，未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。
        在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序I/O来改善性能，所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。如下一个简单示例：

        记录1：<trx1, insert...>
        记录2：<trx2, delete...>
        记录3：<trx3, update...>
        记录4：<trx1, update...>
        记录5：<trx3, insert...>

    （2）undo log
        undo log主要为事务的回滚服务，在事务执行的过程中，除了记录redo log，还会记录一定量的undo log，undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。
        以下是undo+redo事务的简化过程：

       假设有2个数值，分别为A和B，值为1，2
        1. start transaction;
        2. 记录 A=1 到undo log;
        3. update A = 3；
        4. 记录 A=3 到redo log；
        5. 记录 B=2 到undo log；
        6. update B = 4；
        7. 记录B = 4 到redo log；
        8. 将redo log刷新到磁盘
        9. commit

        在1-8的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化，若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘。所以，redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性。  

     五、触发器

        触发器是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由个事件来触发，比如当对一个表进行操作（INSERT、UPDATE、DELETE）时就会激活它执行，触发器分为事前触发和事后触发，结合INSERT、UPDATE、DELETE这三种操作，所以共有六种触发方式 。       
        MySQL除了对 INSERT、UPDATE、DELETE基本操作进行定义外，还定义了 LOAD DATA 和 LOAD 语句，这两种语句也能引起上述六种类型的触发器触发。LOAD DATA语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。REPLACE语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE语句有时候等价于一条INSERT语句，有时候等价于一条 DELETE语句加上一条 INSERT 语句。
        INSERT：将新行插入表时激活触发程序，通过INSERT、LOAD DATA和REPLACE语句。
        UPDATE：更改某一行时激活触发程序，通过UPDATE语句。
        DELETE：从表中删除某一行时激活触发程序，通过DELETE和REPLACE语句。
    （1）创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

        触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象，触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表，不能将触发程序与临时表或视图关联起来。
        trigger_time是触发程序的动作时间，它可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。               trigger_event指明了激活触发程序的语句的类型，trigger_event可以是INSERT、UPDATE、DELETE事件。
    （2）查看触发器
        查看数据库中已存在的触发器的定义、状态、语法信息等，可以使用SHOW TRIGGERS和在TRIGGERS表中查看触发器信息。
    （3）删除触发器

    DROP TRIGGER [schema_name.]trigger_name

        其中schema_name是可选的，如果省略了schema（方案），将从当前方案中舍弃触发程序。
        对于相同的表，相同的事件只能创建一个触发器，比如对表account创建了BEFORE INSERT触发器，如果对表account再次创建一个BEFORE INSERT触发器，MYSQL就会报错，此时，只可以在表account上创建AFTER INSERT或者BEFORE UPDATE类型的触发器。