数据库分为关系型数据库和非关系型数据库，常见的关系型数据库为Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL；而NoSQL的重要的四种非关系型数据库有HBase，Redis，MongodDB，Neo4j。
由于前两篇已经介绍过非关系型数据库了，这篇就主要介绍关系型数据库的---MySQL。
目录
MySQL常用的引擎
（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，主要提供插入和压缩功能。
为什么要采用B tree和B+ tree？
一般的查找算法有顺序查找、折半查找、快速查找等，但是每种查找算法都只能应用于特定的数据结构之上，例如顺序查找依赖于顺序结构，折半查找通过二叉查找树或红黑树实现二分搜索。这样的索引数据结构还是会对数据库的数据结构有要求，而且对磁盘IO的操作依旧很频繁。因此采用了B树和B+ 树.
| 功能 | MyISAM | Memonry | InnoDB | Archive |
| 存储限制 | 256TB | RAM | 64TB | NONE |
| 支持事务 | NO | NO | YES | NO |
| 支持全文索引 | YES | YES | YES | NO |
| 支持数索引 | YES | YES | YES | NO |
| 支持哈希索引 | NO | YES | NO | NO |
| 支持数据缓存 | NO | N/A | YES | NO |
| 支持外键 | NO | NO | YES | NO |
MySQL主要的索引
1.索引的概念
索引是一种数据结构（树形结构）。索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
索引分为聚集索引和非聚集索引：
- 聚集索引：表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，会不断地调整数据的物理地址、分页，如果主键是自增id，只需要一页一页地写，索引结构相对紧凑 磁盘碎片少，效率也高。因此采用 InnoDB存储引擎的数据库，表一定要设置自增列作为主键，这样才能提高数据查询以及插入的效率。
- 非聚集索引：表中的数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。
建立索引的原因：为了查找的优化，特别是当数据很庞大的时候，采用特殊的查找算法，可以实现数据的高效快速查询。
2.MyISAM和InnoDB的索引处理方式
1）MyISAM索引底层是用B+树实现的；它是将索引和数据分离开存储；
主索引：不允许key重复
辅助索引：允许key重复；叶子节点放的是数据的地址；查询1次；
2）InnoDB索引底层也是B+树实现；它是把索引当成数据的一部分存储；
主索引：叶子节点放的是真实的数据；
辅助索引：叶子节点放的是主索引的索引值；查询2次。
3.索引的类型
MySQL目前主要有以下几种索引类型：普通索引、唯一索引、主键索引、组合索引和全文索引。
1）普通索引
这是最基本的索引，它没有任何限制， MyIASM中默认的B tree类型的索引，也是大多数情况下用到的索引。
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索引的表中，然后创建索引，其速度比把数据输入现有全文索引的速度更为快，对于大容量的数据表，生成全文索引是一个非常消耗时间和硬盘空间的做法。
4.索引的优化
虽然索引大大提高了查询速度，但同时却会降低更新表的速度，如对表进行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.事务的ACDI特征
- A(atomicity)原子性:原子性指的是事务里边的操作一旦开始，要么全部成功执行；要么全部失败回滚，不可以只执行其中的一部分。
- C(consistency)一致性：一个事务的执行不应该破坏数据库的完整性约束。
- I(isolation)隔离性：事务之间的行为不应该相互影响；
- D(durability)持久性：事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。可以说是日志先行；
2.没有隔离性会出现的问题
1） 脏读
脏读就是指事务A正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外事务B也访问这个数据，然后使用了这个数据，这个数据即为脏数据，事务B做了一次脏读。
2）不可重复读
不可重复读是指在事务A多次读同一数据，在事务A操作还没有结束时，另外一个事务B也访问该同一数据，那么在事务A中的两次读数据之间，由于事务B的修改，事务A两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，称为是不可重复读。
3）幻读
幻读是指当事务不是独立执行时发生的一种现象，例如事务A对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行，同时，事务B也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作事务A的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
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的并发控制
MVCC是一种多版本并发控制机制。锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。
人们一般把基于锁的并发控制机制称成为悲观机制，而把MVCC机制称为乐观机制。这是因为锁机制是一种预防性的，读会阻塞写，写也会阻塞读，当锁定粒度较大，时间较长时并发性能就不会太好；而MVCC是一种后验性的，读不阻塞写，写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，从而大大提升了并发性能。
MVCC的一种简单实现是基于CAS（Compare-and-swap）思想的有条件更新（Conditional Update）。普通的update参数只包含了一个keyValueSet’，Conditional Update在此基础上加上了一组更新条件conditionSet { … data[keyx]=valuex, … }，即只有在D满足更新条件的情况下才将数据更新为keyValueSet’；否则，返回错误信息。
日志
Redo log：记录将要执行的操作；
事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是“日志先行”。当事务提交后，在buff pool中映射的数据文件才会慢慢地被刷新到磁盘中。此时如果出现数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，那数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。
Undo log：记录执行前的数据状态；
undo log主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。
锁机制
1.MyISAM的锁机制：支持表锁
select：读锁
insert、delete、update：写锁
读锁：共享读锁 加锁后，其他链接可以查看但是不能修改； 兼容读锁
写锁：独占写锁 加锁后，其他链接不可以做任何操作； 不兼容读写锁
2.innoDB的锁机制：支持行锁
select：不加锁
insert、deete、update：写锁
读锁：共享锁 共享读锁
写锁：排他锁（排斥一切其他的锁）；
使用索引时，行锁；当提交后，结束事务，锁释放。
3.锁的分类
按锁的粒度划分：可分为表锁、行锁、页锁；
按锁级别划分：可分为共享锁、排他锁（排斥一切其他的锁）；
按加锁方式划分：可分为自动锁、显式锁
按操作划分：可分为DDL锁和DML锁
按使用方式划分：可分为悲观锁（先加锁控制再执行）和乐观锁（先执行，当遇到问题时再进行加锁控制）；
意向锁：防止死锁
触发器
触发器是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（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类型的触发器。
扫一扫
812
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
