mysql 是什么？

转自浪漫先生

mysql 是关系型数据库的一种实现，它是一个数据库管理的应用软件；

mysql和其他数据库的最大区别在于它的插件式存储引擎设计，你可以根据自己的场景使用不同的存储引擎达到不同的效果。

记住这张图，它对于理解MySQL体系和原理学习至关重要。

mysql 存储引擎比较

Innodb：最大的亮点在于支持事务，非常适用于 OLTP 类型的应用。采用不同的技术和方案实现事务的ACID特性。

其中，事务隔离级别与MVCC并发版本控制技术在保证良好的并发性能的同时实现了事务的隔离性；redo、undo日志实现事务的原子性、一致性和持久性。在性能优化方面，提供了缓冲池、预读、自适应Hash索引等技术方案进一步提升并发性能。

MyISAM：是官方提供的存储引擎，不支持事务。适用于OLAP类型的应用。如果业务量不大，可以作为一个简单的搜索引擎。

NDB：是一个集群索引，采用share nothing集群架构，与Oracle的RAC集群相比，有着更高的高可用性。但是在join等查询情况下，网络开销非常大。

Memory：是一款内存存储引擎，因此查询速度极快。可以作为临时数据存放。它有一些缺点，不支持行锁，并发性能较差；不支持Text和BLOB类型。

Archive存储引擎：顾名思义，是一个归档存储引擎，采用zlib压缩算法压缩比可以达到1:10，非常适合存储归档数据。

表

在 innodb 中，所有的数据都被逻辑的存储在表空间当中，表空间内部由段（segment）、区（extent）、页（page）组成，如下图所示：

段分为索引段、数据段，在 innodb 存储引擎中，索引即数据，数据即索引。因此数据段就是 B+树的叶子节点，索引段就是 B+树的非叶子节点。默认情况下，一个段承载的数据量为 256 MB，即 256 个区大小。

区：由连续的 64 个页组成每个页的大小为 16KB，因此每个区的大小为 1MB，对于大的数据段，innodb 存储引擎一次最多申请 4 个区。

页：页是 Innodb 磁盘管理的最小单位，每一页大小为 16KB。页的分类有数据页、undo 页、磁盘缓冲位图页等。

在B+树索引中，并不能找到具体的某一行记录，而是找到对应的页。然后把页读取到内存中，然后通过 Page Dictory 进行二分查找找到对应的行记录。只是在内存中进行二分查找的速度极快，所以这部分耗时忽略不计。

索引

InnoDB 存储引擎支持 B+tree 索引和自适应 Hash 索引。 B+ 树索引是一种特殊的 AVL 树。索引即数据、数据即索引。所有的数据都存储在叶子节点上。它叶子节点本质上还是一个双向链表，因此天然支持排序，对单点查找、范围查找非常友好。由于 B+ 树具备 AVL 的特性，在增删的过程中会动态的平衡树的高度，因此在正常情况下，索引的查询性能比较高且比较稳定。

聚簇索引（主键索引）和非聚集索引（辅助索引）

在 Innodb 存储引擎下，一张表一定会有一个主键，主键的选择遵循：优先使用用户自定义主键，如果没有指定的话，则使用不允许 Null 值的唯一索引作为主键索引，如果还没有，那么就启用行记录中的隐藏字段 row_id 作为主键索引。

除此之外非主键列构建的索引即为非聚集索引，非聚集索引的叶子节点存储的是索引列的值以及数据行的指针（索引列的值和主键 id）。

在非聚集索引下的查询过程如下：首先会根据非聚集索引查询到所在的列值从而定位到主键 id，然后根据主键 id 回表查询（即回到主键索引上查询）找到对应行所在的页。

添加/删除索引的一个弊端

目前在 Innodb 存储引擎下，如果创建或者删除一个聚集索引，mysql 会先创建一个临时表然后把数据导入到临时表中，再把原表删除，最后把临时表更名为正式表。对于一张大表而言，创建或者删除索引需要花费更多的时间，因此最好在创建表的同时就把索引创建好。

顺序读和随机读

在数据库中，顺序读指的是：根据叶子节点数据的顺序就能读取到所需要的行数据，但这个顺序读只是逻辑的顺序读，在实际读取磁盘时还可能是随机读（因为要读取两行数据可能并不位于同一个页中，同时也可能不再同一个缓存行中）。

随机读：指的是通过非聚集索引找到对应的主键值，间接的读取实际的行数据。由于非聚集索引和主键所在的段（数据段和索引段）不同，因此访问是以随机的方式进行的。

预读

为了进一步提升性能，InnoDB存储引擎引入了预读取功能。预读取分为随机预读取和显性预读取。

随机预读取：所谓随机预读取就是如果读取的区中有13个页在缓冲池中并且处于LRU缓存的前端，则会将该区剩下的页全部读取到缓冲池中；

线性预读取：如果读取的页连续20页有序，则会将下个区的页全部读取到缓冲池中。

这里的区指的是InnoDB存储引擎中逻辑划分的区，不是指的磁盘的扇区！ MySQL中，经过实际的性能测试，随机预读取的性能表现不好，线性预读取的性能up!因此MySQL中采用的是线性预读取。

锁

什么是锁？

数据库为了能够在保障事务的同时尽最大可能得提升并发访问性能，引入了锁机制。

在 mysql 中锁的标准规范分为共享锁和排它锁。

锁的标准分类

共享锁和排它锁

共享锁（share lock）：允许事务读一行数据

排它锁（exclusive lock）：允许事务删除或者更新一行数据

粒度：行级别

当一个事务获取到共享锁后，其他事务可以继续获取共享锁，但不能获取到排它锁；

当一个事务获取到排它锁时，其他事物既不可以获取到共享锁也不可以获取到排它锁，只能等待前一个排它锁释放后再尝试获取。

提供并发能力：减少所冲突的意向锁

innodb 存储引擎为了支持不同粒度的锁，提供了意向锁类型。

意向锁最主要的目的是为了尽可能的减少锁冲突，从而提升并发访问性能

意向锁分为

意向共享锁：事务想要获取某几行的共享锁；
意向排它锁：事务想要获取某几行的排它锁；

当事务获取到意向锁时，并不会阻塞其他事务获取相同表中其他未上锁行数据的意向锁，但是会阻塞获取相同行数据的锁的事务。（很明显相比于之前的排他锁，意向锁提供了更细粒度的锁，从而减少了锁冲突，提高了并发性能）

提供并发能力：一致性的非锁定读

所谓的非锁定读指的是事务在对某一行数据进行 UPDATE、DELETE 时，另一个事务读取这一行数据时不需要等待当前事务释放 X（排他）锁，而是通过读取当前数据的快照，这种方式称为非锁定读取。

MVCC 机制

MVCC 机制简单理解就是一行数据有多个版本，根据事务的隔离级别确定当前事务读取哪个版本（可见性判断），从而进一步提升系统并发访问能力，这种能力就被称为多版本并发访问机制。

多版本机制 Undo log 日志文件实现的，因此没有额外的存储开销和性能开销。

不同事务的隔离级别读取到不同的版本，在 innodb 存储引擎中，如果事务隔离级别为 read commited，那么读取的总是最新的已提交的事务版本；如果事务隔离级别是 repeatable，那么读取的是当前事务开启的时候的版本。

锁定读

在某些情况下我们需要对读取进行加锁。这种方式称为锁定读。

锁定读的类型有2种。

加 X 锁：select ... for update; （加排它锁）

加 S 锁：select ... lock in share more;（加共享锁，默认可以省略）

并发能力由你决定：悲观锁和乐观锁

悲观和乐观是针对锁冲突的场景，悲观锁和乐观锁本质上是对于锁冲突几率的不同而采取的处理手段。

悲观锁的思想是认为每次操作都可能出现锁冲突，因此在实际操作之前，需要采取一种机制防止其他操作的干扰，因此采用严格的排它锁（加 X 锁）

start transaction;
select * from user where id = 1 for update;
update user set name = 'zhangsan' where id = 1;
commit;

乐观锁是认为锁冲突几率比较小，因此采用 CAS 的方式进行更新，一旦真的出现了锁冲突，就放弃当前事务的更新。这样做的好处是性能较高。

select * from user where id = 1; // version = 1
update user set name = 'zhangsan', version = 2 where version = 1;

锁的算法

数据库中，锁的算法有三种：

行锁：单行记录上加锁
间隙锁：锁定一个范围，但不包含数据本身
Next-Key 锁：锁定一个范围，同时页包含数据本身

Innodb 存储引擎采用的是 Next-Key Lock 算法，这样可以保证实现复杂度不会太高并且保证事务的正常执行。

事务

什么是事务

事务是数据库的一个重要的能力和机制，它可以保证数据从一个一致性状态转移到另一个一致性状态；换句话说：它可以保证事务中的操作要么全部执行，要么全部不执行，不存在任何中间情况，这就是事务的强大之处。

事务提供了四大特性：ACID

A：原子性。一个事务中，所有相关的数据变化要么一起成功，要么一起失败。

C：一致性。一个事务完成后，所有数据从一个一致性变为另一个一致性。事务开始前和事务完成后，数据的状态虽然变了，但是依然保持完整的一致性。

I：隔离性。事务之间互不干扰。（这是通过锁机制实现的）

D：持久性。事务一旦提交，其结果是永久性的，即使服务宕机也不影响。

事务的实现

在 innodb 存储引擎中，事务的原子性、一致性、持久性都是通过 redo log 和 undo log 实现的。

redo：重做日志，记录了事务的执行过程，可以支持任何场景下的重做，由于它是 WAL 日志，因此即使一个写操作导致的数据更改还未持久化到磁盘之前机器宕机丢失了缓存中（脏页）的数据，我们也可以从已经持久化到磁盘的 redolog 日志中重新加载之前的操作，从而再次恢复到一致性状态。

当事务提交之时，通过异步刷新磁盘的方式持久化到磁盘中，如果事务提交之后服务宕机了，可以通过 redo 日志进行恢复。

undo：undo 日志和 redo 日志的作用完全不同，在事务执行过程中，不仅会产生 redo log 也会产生 undo log。当事务失败了，会通过 undo 日志进行回滚，使得数据状态恢复原样（准确的说：undo log 提供了事务的回滚功能，他记录事务操作的反操作，当事务执行失败需要回滚时，执行 undolog 中记录的反向就可以将数据库状态回滚到事务开始前的状态，最后就好像这个事务从而没有执行过一样）。

请记住：事务的回滚不是物理性的将磁盘上的数据恢复原样，而是逻辑性的执行 undo 日志使得数据的状态恢复原样；回滚后磁盘的数据排列、表空间大小可能都会改变（比如事务执行过程中出现了分页）

事务的隔离级别

未提交度
读已提交
可重复读
串行化

从上到下：隔离级别越来越高，系统的并发能力越来越差

事务隔离级别本质上是为了提供一种事务严格程度和数据库并发能力的一种平衡选择方式。

越宽松的事务隔离级别对隔离性的支持越差，但并发能力更强。

不同事务隔离级别下的事务问题

脏读

所谓的脏读就是一个事务读取到了另一个事务未提交的数据。对应的事务隔离级别为read uncommited;

不可重复读（幻读）

为了避免脏读现象，数据库的隔离级别可以设置为read commited。这种事务隔离级别下会带来另一个问题：不可重复读。所谓不可重复读就是一个事务在反复读取同一个数据时，前后数据状态不一致。其本质原因是数据库提供了一种MVCC机制，在MVCC机制下，不同的事务隔离级别读取到的数据版本有区别。在read commited级别下，事务总是读取当前最新已提交事务的版本数据。

解决方案：为了解决不可重复读的现象，我们可以将事务隔离级别设置为read repeatable.在read repeatable级别下，当前事务总是读取当前事务开始时的版本数据。

性能调优思想指导

OLTP or OLAP

对于 OLTP 应用它的特征有如下几点：

用户操作的并发量大
事务处理的时间较短；
一般查询都是命中索引；
复杂的查询比较少。

基于以上 OLTP 应用的特征不难发现：OLTP 应用更加注重事务，更偏向于磁盘 IO 性应用；

而 OLAP 应用的特征刚好与 OLTP 恰恰相反，OLAP 往往会有很多复杂的查询、计算，不直接面向用户，因此更偏向于 cpu 密集型应用。

所以我们首先要做的是区分我们的应用属于 OLTP 还是 OLAP 应用，然后决定物理层面的硬件配置。

合理选择存储引擎

MySQL最大的优势之一就是其插件式存储引擎，我们只需要评估我们的业务场景然后选择合理的存储引擎。

连接优化

本质上 mysql 还是以 TCP 长连接方式进行的网络通信，因此根据业务规模设置合理的连接池大小和连接参数很有必要，我们要尽可能避免长事务、长时连接、死锁等情况，目的就是为了避免连接池耗尽和连接阻塞问题。

sql 优化

一切SQL优化都是为了达到高质量使用索引的目的。

索引

索引对于 innodb 存储引擎的性能而言是非常重要的，我们在实际的工作中，要充分评估业务场景；能走聚集索引就走聚集索引，不能走才选择非聚集索引。

必要情况异构索引（联合索引）

索引本身的质量也非常重要，否则即使命中索引，性能也可能很差（比如选择区分度很小的字段作为索引字段，性别字段就是一个例子，因为它只能将数据分为两份，即使根据索引查询来一半数据，还是要花费 O(n/2) 时间遍历）。

索引最主要的目的是为了能够快速检索到某一行数据，如果某一列存在大量重复数据，那么索引本身的质量肯定不高，此时我们可以考虑异构索引，通过增加一次查询次数（组合成联合索引）来提高索引命中率和质量。（比如联合索引（a, c），即使数据表中有大量 a 字段值相等，联合索引也能在基于 a 大小排序后，对 a 字段值相同的数据行再按照 c 的大小再次进行筛选，这样就可以过滤掉更多的不合法结果从而达到加速访问的效果）

EXPLAIN 执行计划

在实际操作中，我们可以借助 EXPLAIN 执行计划初步分析一条 SQL 的执行质量，进行合理的优化。

慢日志

慢日志是 mysql 底层文件系统中的一种日志文件，主要是用于记录执行时间超过设置阈值的 sql（比如 10s），这些 sql 语句会被存放在慢日志中，当服务出现阻塞变慢时，可以通过排查慢日志定位导致查询变慢的长时执行语句，然后对主要瓶颈进行优化可以快速提升程序执行速度。

架构优化

分库分表

对于大库大表一旦突破了最佳性能的极限数据量阈值，mysql 性能就会急剧下降，why？

在 innodb 存储引擎中，其特有的 B+Tree 索引数据结构的最本质作用就是为了尽可能保证树高度低（一般不超过 4）；因为 h 越高，代表着磁盘 io 次数就越多，对于关系型数据库而言最关注的性能指标莫过于磁盘 io 次数了（因为它比从内存读取满了上百倍甚至上万倍（随机读取））；因此一旦突破最佳性能下的 h 高度，性能就会急剧下降。

对于大库大表，最直接且有效的方案就是分库分表，具体怎么分需要结合业务场景，但最终目的都是为了保证单表的最佳查询性能。

读写分离

在高并发场景下，一台数据库服务器同时接收读写请求，如果只是简单使用一个粗粒度互斥锁，那么并发性能就太差了，此时我们可以采用读写分离的技术方案提高性能吞吐量和并发能力。

在读写分离的模式下，不再是一个数据库了，而是存在从库；主库负责所有的写操作而从库负责读操作，各司其职。

Mysql Innodb 存储引擎