MySQL笔记第一章

MySQL架构与历史

MySQL的架构图

• MySQL三层架构，最上面那一层负责客户端和MySQL服务器端的连接处理、授权认证、安全等方面。可以理解负责管理登录到MySQL这个服务器的过程。
• 第二层-大多数MySQL的核心都在这里，包括查询解析，分析，优化，缓存和所有的内置函数，储存过程、触发器、视图等
• 第三层-存储引擎。存储引擎负责对数据的存储和提取，就像Linux下的各种文件系统一样有优劣势。不过存储引起不会解析SQL，这是上层应该做的操作

MySQL第一层架构

主要负责连接管理和安全性，每个客户端连接都会在服务器进程中拥有一个线程，服务器会负责管理线程，类似于线程池一样，不一定需要为每个新建的连接创建或销毁线程。

MySQL第二层架构

优化与执行

你可以把这里看作是服务器主要实现逻辑的部分，负责解析SQL等操作。这里会解析查询，然后创建内部数据结构。用户可以通过特殊的关键字提示优化器，影响它的决策过程。

并发控制

锁和事务

多线程的环境下就一定要谈得是数据的安全，即如何让数据不相互冲突。

那么就需要锁来进行数据的锁定，这里主要使用两种类型的锁：共享锁和排他锁（读锁和写锁）后者锁的东西比前者更多一些。主要区别在于，当处于读锁时，其他事务只能对其施加读锁，只有拿到读锁的线程才能访问，而且不能给加排他锁，也就是数据只能读取不能修改。写锁的话，其他事务不能加任何锁，获得读锁的线程既能读数据，也能写数据。

大家都知道关系型数据库中存在表结构，那么在锁的层面就是锁粒度（我应该锁哪些数据）加锁其实是一件影响性能的事情，那么我们应该尽量锁更少的东西。数据库在锁粒度上分为表锁和行级锁，而MySQL的每种存储引擎都可以实现自己的策略和锁粒度。

然后还有一个非常重要的概念—事务。事务我之前写过类似的，这里就不多赘述了。数据库事务

MVCC多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁，基于性能考虑，一般都实现了多版本并发控制。MVCC可以认为是行级锁的一个变种，但是在很多情况下避免了加锁操作，因此性能更高一些。

MVCC的实现：保存数据在某个时间点的快照，当事务开始时，每个事务都可能看到不同的数据。因为在某个事务里面可能已经对该数据进行了修改。但是对其他事务来说，这个数据并没有改变。

InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建实际，一个保存行的过期时间（删除时间），这里并不是真实的时间，而是系统版本号。下面看一下具体如何操作的

• INSERT（插入语句）为新插入的每一行保存当前系统版本号作为创建时间。
• DELETE（删除语句）为删除的每一行保存当前系统版本号作为删除时间。
• UPDATE（更新语句）InnoDB插入一行新记录，保存当前系统版本号作为创建时间，同时修改原来行的删除标识为当前系统版本号。

SELECT会怎么处理呢？

1. InnoDB只查找时间早于当前事务的数据行，也就是保证数据是在事务之前读取的行，要么是自身事务

2. 只查找删除版本大于当前事务版本号，或者就是未定义。保证事务读取的行，在事务开始之前未被删除。未定义就表示该数据没有被删除，当一条数据在事务之前被删除，那么该数据的删除时间就会小于事务的时间，那么它就不应该被查出来，如果大于这个事务时间，则表示在进行事务之后这个数据被删除了，但是在事务执行时还没有被删除，所以应该能查出来。

MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个 隔离 级别 下 工作。因为其他方式处理数据都很极端

MySQL的存储引擎

SHOW TABLE STATUS LIKE 'user'

上面的语句可以查出user表的一些状态信息，返回的结果如下图所示（注意我是使用Navicat的查询来展示的结果，假如使用cmd的话，展示的样式可能不同，但是数据是一样的）

字段名	解释
Name	表名
Engine	表的存储引起，旧版本中叫做type
Row_format	行的格式。Dynamic的行长度是可变的(VARCHAR,BLOB)。Fixed长度是固定的(CHAR,INTEGER)
Rows	表的行数。MySIAM和其他的一些存储引擎，该值是 准确的，对于InnoDB是估计值
Avg_rowlength	平均每行包含的字节数
Data_length	表的数据的大小（字节）
Max_data_length	表数据的最大容量，该值和存储引擎有关
Index_length	索引的大小(字节)
Data_free	MyISAM表中表示已分配但目前没有使用的空间，也就是能被INSERT利用的
Auto_increment	下一个自增的值
Create_time	创建时间
Update_time	更新时间
Check_time	使用CHECK TABLE命令或者myisamchk工具最后一次检查表的时间
Collation	表的默认字符集和字符排序规则
Checksum	如果启动，保存的是整个表的实时校验和
Create_options	创建表时指定的其他选项
Comment	包含一些其他的额外信息。对于MyISAM表，保存的是表在创建时带的注释。对于InnoDB，保存是InnoDB表空间的剩余空间信息。如果是视图，则包含VIEW文本

InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，也是最重要、使用最广泛的存储引擎

• InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。

• InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别，通过间隙锁策略防止幻读的出现（间隙锁不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入）

• InnoDB表是基于聚簇索引建立的

• InnoDB内部做了很多优化，从磁盘读取数据时采用可预测性预读，自动在内存中创建Hash索引以加速读操作的自适应哈希索引。

• 通过一些机制和工具支持真正的热备份。MySQL其他存储引擎不支持热备份。

MyISAM存储引擎

MySQL5.1之前是默认的存储引擎。对于只读的数据，或者表毕竟小、可以容忍修复操作，则依然可以继续使用MyISAM。

• 存储：MyISAM将表存储在两个文件中：数据文件和索引文件。
• MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则加入排他锁。
• MyISAM表的修复可能导致一些数据丢失，而且修复操作是非常慢的
• 对于MyISAM表，即使是BLOB和TEXT等长字段，也可以基于前500个字符创建索引，也支持复杂的查询。
• 延迟更新索引键：创建MyISAM表时，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会先写入内存。这样可以极大提升写入性能，但是在数据库崩溃时会造成索引损坏。
• MyISAM压缩表：如果在导入数据以后不会进行修改操作，那么可以使用压缩表，压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘IO，从而提升查询性能。不过压缩表是不能进行修改的。修改之前可以解压缩。
• MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下性能很好，最典型的性能问题还是因为表锁的关系，如果你发现所有查询都长期处于Locked状态，则就是因为表锁

选择合适的引擎

大部分情况下，InnoDB都是正确的选择，除非需要用到某些InnoDB不具备的特性，并且没有办法替代，否则都应该使用InnoDB。如果考虑用到全文索引，建议优先考虑InnoDB加上Sphinx的组合而不是使用MyISAM。而且除非万不得已，不要混合使用多种存储引起，否则可能带来一系列复杂的问题。

如果应用需要不同的存储引擎，请先考虑以下几个因素

• 事务
• 备份
• 崩溃恢复
• 特有的特性