数据库逻辑框架

MySQL服务器逻辑架构图

整体可分为三部分：

• 连接和认证：
  每一个客户端请求原则上对应一个线程，5.5版本之后，可以建立连接池来对线程进行管理，使用少量的线程对付服务大量的连接；认证主要包括认证用户名和密码，如果使用过可SSL，则要进行证书认证；再有就是权限认证。
• 优化和执行：
  该部分会对查询进行解析和优化。

• 存储引擎：Mysql提供了8中存储引擎可供选择：

  
  
  存储引擎特征对比
  
  

  使用比较多的两种引擎是MyISAM和InnoDB，两者的区别是：

• MyISAM使用表级锁，适用于选择密集型和插入密集型的表。
• InnoDB使用行级锁，支持事务，聚簇索引，适用于更新密集型的表，容灾型更好，支持快速修复。

数据库事务：

在mysql中，每一次数据库操作其实都是放在一个事务里的，如果不显式的调用START TRANSACTION和COMMIT，则每一条SQL语句即是一个事务，并自动提交，可以通过SHOW VARIABLES LIKE "AUTOCOMMIT";来查看自动提交的开关与否。

可以使用SET AUTOCOMMIT = 0来关闭自动提交，关闭自动提交主要用于非事务性表的操作，该更改会在下一个事务执行时有效。

事务的四大基本特性（ACID）：

• 原子性（atomicity）：一个事务被视为一个不可分割的最小单元，整个事务要么全部提交成功，要么全部失败回滚。
• 一致性（consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态，比如之前的500元再经过转换后加和仍是五百元。
• 隔离性（isolation）：通常来说，一个事务所作的修改再提交前，对其他事务是不可见的，“通常来说”，体现在不同的隔离级别中。
• 持久性（durability）：事务一旦提交，则其所作的修改将永久的保存到数据库中。

事务的隔离级别：

• READ UNCOMMITED(未提交读)：事务中的修改，即使没有提交，对其他事务也是可见的。
• READ COMMITED(提交读)：一个事务开始时，只能看见已提交的事务所作的修改。
• REPEATABLE READ(可重复度)：同一事务中，多次读取同样记录的结果是相同的。
• SERIALIZABLE(可串行化)：最高级别，强制事务串行执行，对读取的每一行数据都加锁，可能导致大量的超时和锁争用的情况，只有在非常需要确保数据的一致性且可以接收没有并发的情况下，才会使用这一隔离级别。
  
  隔离级别
  
  mysql默认的隔离级别是可重复读。

MVCC：
即多版本并发控制，可以用来防止“幻读”的发生，从而保证同一事务中两次相同查询所得到的结果是一样的，是一种用于解决读-写冲突的无锁并发机制。

其实现原理即是，对表中的每一行数据给定一个版本号，每一次更改操作会使得版本号递增，这样一个事务开启时，拿到的所有数据的版本号都是小于或者等于当前版本号的，即一个快照，同一事务中的查询操作对该同一记录集进行操作，从而解决读-写冲突。

OCC：
即乐观并发机制，又叫乐观锁，主要解决写-写冲突的问题。

其实现原理为CAS，即默认是不会发生冲突，但是在事务准备提交的时候，检查以下有没有新的提交出现，如果没有，则直接提交，如果有，则放弃并重试。适用于低数据争用，写冲突比较少的情景。

索引

索引是什么：索引是为了加快查找速度的一种数据结构。

索引的作用：高效查找数据。

索引的结构：MyISAM和InnoDB都是采用B+树作为数据结构。

B+树：
以下是B+树的一个建树过程：（转载）

下面是往B+树中依次插入
6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4

B+树建树过程

索引实现：
虽说MyISAM与InnoDB的数据结构都是B+树，但是其实现方式缺截然不同：

• MyISAM
  索引文件与数据文件分开，叶子节点存储的是数据行的内存地址，示意图如下：
  
  MyISAM索引示意图
  
  如需要建立辅助索引，以Col2列为例，则辅助索引的叶节点存储的也是数据行的内存地址，示意图如下：
  
  辅助索引示意图
  
  MyISAM这种索引的方式也叫非聚簇索引。
• InnoDB
  InnoDB的索引文件就是数据文件，叶子节点存储的就是真实数据，示意图如下：
  
  InnoDB索引示意图
  
  若想建立辅助索引，同样以Col2为例，叶子节点存储的是就是主键，这里以英文字符的ASCII码作为比较准则：
  
  InnoDB辅助索引示意图
  
  InnoDB的这种索引又叫聚簇索引，所以不建议使用过长的字段作为主键，这样会使得辅助索引变得过大，此外，非单调的字段也不宜作为主键，因为非单调的字段会使得在建树阶段频繁的分裂，十分低效，所以宜使用自增字段作为主键。

什么情况适合建立索引：

• 表的主键和外键需要建立索引；
• 数据量超过300的需要建立索引；
• 经常出现在where字句中的字段；
• 经常用于连接两张表的列；
• 经常需要范围查找的列，因为索引已经排序，所以指定的范围是连续的；
• 经常需要排列的列，理由同上；

索引的缺点：

• 更新数据时需要对索引进行更新；
• 索引需要占用一定的空间。