MySQL数据库

数据库的定义

• 数据库：物理操作文件系统或其他形式文件类型的集合
• 实例：MySQL数据库由后台线程以及一个共享内存区组成

我们无法直接操作数据库，而是通过数据库实例来操作数据库文件。
在Unix上，启动一个MySQL实例往往会产生两个进程，mysqld就是真正的数据库服务守护进程，而mysqld_safe是一个用于检查和设置mysqld启动的控制程序，它负责监控MySQL进程的执行，当mysqld发生错误时，mysqld_safa会对其状态进行检查并在合适的条件下重启

三大范式

第一范式（1NF）：字段具有原子性，不可再分。确保表的每一列都是不可分割的原子数据项，方便字段的维护、查询效率高，易于统计。所有关系型数据库都满足第一范式。
第二范式（2NF）：有唯一的属性列——主键。其他属性依赖于主键，保证每行数据都是按主键划分的独立数据。
第三范式（3NF）：要求一个数据库表中不包含在其他表中已包含的非主键信息。

三大范式只是一般设计数据库的基本理念，可以设计冗余较小，存储查询效率高的表结构，但不能一味的去追求数据库设计范式，应该多关注需求和性能。需求-性能-表结构

MySQL存储引擎

InnoDB

InnoDB：默认的数据库存储引擎。最大的特点在于支持事务（但是是以损失效率换取的）。

• 可以通过自动增长列，方法是auto_increment
• 支持事务。默认事务隔离级别为可重复读，通过MVCC（并发控制版本）来实现。
• 使用的锁粒度为行级锁，可以支持更高的并发。
• 支持外键约束，外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
• 配合一些热备工具可以支持在线热备份；
• 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
• 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

当然InnoDB的存储表和索引也有下面两种形式：

• 使用共享表空间存储：所有的表和索引存放在同一个表空间中。
• 使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，使用分区表的好处在于提升查询效率。

MyISAM

MyISAM：
使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

• frm文件：存储表的定义数据
• MYD文件：存放表具体记录的数据
• MYI文件：存储索引

支持全文索引、B树索引、数据压缩。

支持数据的类型：

• 静态固定长度表
  这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。
• 动态可变长表
  优点是节省空间，但是一旦出错恢复起来比较麻烦。
• 压缩表
  支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用check table工具来检查，而且还可以使用repair table工具来恢复。

有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎。

MyISAM和InnoDB的区别

• InnoDB 支持事务；MyISAM 不支持事务
• InnoDB 支持行级锁；MyISAM 支持表级锁
• InnoDB 支持 MVCC(多版本并发控制)；MyISAM 不支持
• InnoDB 支持外键，MyISAM 不支持
• MySQL 5.6 以前的版本，InnoDB 不支持全文索引，MyISAM 支持；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
• InnoDB 不保存表的总行数，执行 select count(*) from table 时需要全表扫描；MyISAM 用一个变量保存表的总行数，查总行数速度很快
• InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。辅助索引需要两次查询，先查询到主键，再通过主键查询到数据。主键太大，其他索引也会很大；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的

总结：

• InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引
• MyISAM 不支持事务、也不支持外键，优势是访问的速度快。对事务的完整性没有要求、以 SELECT 和 INSERT 为主的应用可以使用这个存储引擎

Memory

将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。

• 支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
• 支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
• 由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；
• 查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；
• 默认使用hash索引。
• 如果一个内部表很大，会转化为磁盘表。

B树和B+树

两者区别

• B树，每个结点都存储key和data，所有结点组成这棵树，叶子结点指针为null，不包含任何关键字信息
• B+树，所有的叶子结点中包含了全部关键字信息以及指向含有这些关键字记录的指针，叶子结点依关键字的大小自小而大的顺序链接（有序链表） 。所有的非终端结点可以看成是索引部分，结点中仅含有其子树根节点中最大（或最小）关键字。

（B树中数据存在所有结点中，B+树中数据只存在于叶子结点中，其他结点只是叶子结点中关键字的索引）

为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引

• B+树的磁盘读写代价更低：B+树的内部结点中没有指向关键字具体信息的指针，所以其内部结点比B树小，一次性能读入内存的关键字数量就越多。读写代价更低。
• B+树的查询效率更加稳定：内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的索引必须走一条从根结点到叶子结点的路。查询效率相当。
• B+树只要遍历叶子结点就可以实现整棵树的遍历，而且在数据库中基于范围的查询非常频繁，而B树只能中序遍历所有结点，效率太低

B树和B+树

索引

自己写的笔记

事务

四大特性和隔离级别
好文章！谈谈MySQL InnoDB存储引擎事务的ACID特性

四大特性(ACID)

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。也就是说一个事务执行之前和执行之后都必须处于一致性状态。

隔离性（Isolation）

指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

持久性（Durability）

指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

隔离性

• 脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
• 不可重复读：不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

• 幻读：幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

隔离级别

• 读未提交（Read Uncommitted）：是最低的事务隔离级别，它允许另外一个事务可以看到这个事务未提交的数据。会出现脏读，幻读，不可重复读，所有并发问题都可能遇到。
• 读已提交（Read Committed）：保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。不会出现脏读现象，但是会出现幻读，不可重复读。
• 可重复读（Repeatable Read）：这种事务隔离级别可以防止脏读，不可重复读，但是可能会出现幻象读。它除了保证一个事务不能被另外一个事务读取未提交的数据之外还避免了不可重复读。
• 序列化（Serializable）：这是花费最高代价但最可靠的事务隔离级别。事务被处理为顺序执行。防止脏读、不可重复读、幻象读。

设置数据库的隔离级别一定要是在开启事务之前

锁机制

MySQL中的锁机制
死锁

日志

Mysql的Binlog原理

Mysql中的MVCC