mysql 即学a又学b_MySql数据库学习笔记

MySql分层结构：

MySql分层结构.png

1.MySQL中InnoDB与MyISAM的区别：

a.InnoDB引擎是MySql的默认引擎(从5.5.8版本开始)，支持事务，采用行级锁，支持外键，默认非锁定读，其设计主要作为OLTP(Online Transaction Processing，即在线事务处理)的应用。

b.MyISAM引擎不支持事务和表锁设计，但支持全文索引，主要面向OLAP(Online Analytical Processing，即在线分析处理)。

2.InnoDB的体系结构

InnoDB引擎体系结构.jpg

2.1.后台线程

主要负责刷新内存池中的数据，保存缓存池中的内存缓存的是最近的数据，还负责将修改的数据文件刷新的磁盘文件，同时保证数据库发生异常的情况下mysql能够恢复到正常运行状态。

2.1.1 Master Thread

作为核心后台线程，Master Thread 负责缓冲池中的数据异步刷新到磁盘，保证数据的一致性。

Master Thread的演进：

1.InnoDB 1.0.*之前

算法结构

public void masterThread() {

while(true) {

for(int i=0;i<10;i++) {

/**

1.将重做日志缓存刷到磁盘里的重做日志文件，即使事务还没有提交;

2.合并插入缓存(以当前1秒内IO次数是否小于5为界限，小于5在执行);

3.刷最多100个脏页到磁盘(脏页比例是否超过设置的阀值)；

4.如果当前没有用户活动，则切换到background循环中。

*/

do thing per second;

Thread.sleep(1000*1);

}

/**

1.刷100个脏页到磁盘(在10秒内IO次数小于200次的情况下);

2.合并最多5个插入缓存;

3.将日志缓冲刷到磁盘；

4.删除无用的undo页；

5.刷100个或10个脏页到磁盘(当脏页比例大于70%时刷100个脏页，否则刷10个脏页)

*/

do thing per 10 seconds;

}

}

background循环：

1.删除无用的undo页；

2.合并20个插入缓冲；

3.调回到主循环

2.InnoDB 1.2.*之前

通过分析InnoDB 1.0.*版本，我们知道InnoDB采用硬编码的方式实现，InnoDB 1.2.*版本支持用户来自定义IO吞吐量，通过innodb_io_capacity参数来设置，这样就适应了高性能和低性能的磁盘，另外还有innodb_max_dirty_pages_pct来设置脏页在缓冲池的百分比。另外还可以配置innodb_adaptive_flushing和innodb_purge_batch_size可以设置。

3.InnoDB 1.2.*：

算法结构：

if(innodb is idle) {

do 10秒间隔的操作

} else {

do 1秒间隔的操作

}

另外，对于刷脏页的操作，innoDB从Master Thread 分离出了单独线程Page Cleaner Thread，减轻了Master Thread的工作。

2.1.2 IO Thread

InnoDB大量使用了Async IO 来处理IO请求，极大的提高了数据库性能，IO Thread主要负责这些IO请求的回调(call back)。

2.1.3 Purge Thread

当事务被提交之后，其所使用的undolog就不再需要了，Purge Thread主要负责回收这些undo数据所占用的空间。

2.1.4 Page Cleaner Thread

主要负责脏页(即缓冲池中的页和磁盘中的页数据不一致)的刷新操作，减轻Master Thread的负担，提高引擎性能。

2.2.内存

2.2.1 缓冲池

缓存为了解决CPU和磁盘之间速度的鸿沟，来提高基于磁盘的数据库系统的整体性能。针对数据库页的修改，InnoDB则先修改缓冲池中的页，然后再以一定的频率刷新到磁盘上，而不是每次页有修改就刷新到磁盘上。

2.2.2 重做日志缓冲

InnoDB首先将重做日志信息放入到缓冲区，然后按照一定的频率将其刷新到外部磁盘的重做日志文件。一般情况下1秒刷新就会刷新一次，因此重做日志缓冲缓冲的大小不需要很大，8MB基本都能够满足。在下列三种情况下会刷新重做日志缓冲到外部磁盘的重做日志文件：

a.Master Thread 每秒将重做日志缓冲刷新到重做日志文件；

b.每次事务提交时会将重做日志缓冲刷新到重做日志文件；

c.当重做日志缓冲剩余空间小于1/2时，将重做日志缓冲刷新到重做日志文件。

2.2.3 额外内存池

在InnoDB引擎中，对数据进行内存分配时，首先充额外的内存池中进行申请，当该区域的内存不够时，才会从缓冲池中申请。

2.3 CheckPoint(检查点)技术

为了协调CPU和磁盘速度的鸿沟，因此页的操作都是在缓冲池中进行的。若每一次的页变化都将其刷新到磁盘中，那么开销巨大。InnoDB在事务提交时，采用先写重做日志，再修改页，这样系统宕机时，可以通过重做日志来恢复，而不会导致数据丢失。CheckPoint实现了在数据库宕机时，不需要重做所有日志，因为在CheckPoint之前的数据都已经刷到了磁盘里，故数据库只需要对CheckPoint之后的重做日志进行恢复即可，这样极大地缩短了恢复时间，同样也减少了重做日志所占的磁盘空间(只需要保存CheckPoint之后的重做日志)。

CheckPoint刷数据的两种方式：

a.Sharp CheckPoint 每次发生数据库关闭时，将所有脏页刷到磁盘，对数据库可用性产生很大影响。

b.Fuzzy CheckPoint 每次发生数据库关闭时，将部分脏页刷到磁盘，而不是所有脏页。

2.4 InnoDB特性

2.4.1 插入缓存(Insert Buffer)

针对非聚集索引的更新和插入操作(要求是非聚集索引，且索引不是unique的，因为数据库不去查找索引页来判断插入记录的唯一性)，不是每次都是直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入；否则先放到一个Insert Buffer对象中，然后再以一定的频率和情况将Insert Buffer对象和非聚集索引叶子节点的进行合并(即多步合成一步)，大大提高了非聚集索引的插入性能。缺点：遇到段时间内大量写的情况，Insert Buffer会占用使用过多的缓冲池空间，影响性能，如果此时宕机，会导致大量的Insert Buffer对象中的数据，没有合并到非聚集索引中，影响系统恢复效率。

Insert Buffer也是由一个B+树构成，MySql 4.1版本之后，每个数据库实例只有一个Insert Buffer对象。

2.4.2 两次写(Double Write)

在对系统重做日志前，用户需要一个页的副本，当写入失败时，先通过副业来还原对应的页，再执行重做日志。补充了重做日志只对页进行操作的粗粒度的弊端。

2.4.3 自适应Hash索引(Adaptive Hash Index)

Hash算法在不冲突的情况下，时间复杂度为O(1)，效率明显高于B+树。InnoDB通过监控表上的索引页的查询，如果监控到通过建立Hash索引可以带来速度提升，则InnoDB会通过缓冲池的B+树页来构造，创建Hash索引，不需要人为控制调整，故称为自适应Hash索引。

2.4.4 异步IO(Asynchronous IO)

InnoDB中，read ahead方式的读取都是通过AIO实现的，另外脏页的刷新(写入到磁盘)也是由AIO完成。

3.数据存储

MySql InnoDB的逻辑存储结构.jpg

说明：

namespace表空间：所有数据都保存在namespace中，如果innodb_file_per_table停用(默认停用)，所有的数据保存在共享表空间ibdata1中；innodb_file_per_table启用，每张表的表空间存放的有数据、索引和插入缓存Bitmap页数据，其他如回滚undo信息，插入缓存索引页，系统事务信息，二次写缓冲等还是保存在共享表空间ibdata1中。

segment段：namespace由segment构成，常见的有数据段(B+树的叶子节点)，索引段(B+树的非叶子节点)，回滚段等。segment由Innodb自动管理，DBA无法管理。

extent区：segment由extent构成，extent由连续的page组成的空间，大小都是1MB，不可设置。

page页：page是InnoDB磁盘管理的最小单位，默认大小为16KB，可设置。

row行： MySQL数据按行存放，即数据库中的一条数据记录。

4.事务(Transaction)

4.1 事务的特性：

原子性(Atomicity)：由redo log和undo log实现；

一致性(Consistency)：由undo log实现；

隔离性(Isolation)：由锁实现；

持久性(Durability)：由redo log实现。

4.2 事务隔离级别：

1.RU(Read UnCommitted) 读未提交;

2.RC(Read Commited) 不可重复读；

3.RR(Repeatable Read) 可重复读；

4.Serializable 串行化。

4.3 分布式事务：

分布式事务指允许多个独立的事务资源(Transactional Resources，如一个事务涉及到一个是MySQL，一个是Oracle，还有一个SQLServer)参与到一个全局事务中。

MySQL通过XA事务来支持分布式事务，XA事务允许不同数据库之间的分布式事务。在分布式事务时，InnoDB的事务隔离级别必须是最高级别，即Serializable 串行化。

分布式事务.jpg

分布式事务采用两段式提交(two-phase commit)方式:

第一阶段：所有参与全局事务的节点都开始准备，然后告诉事务管理器他们准备好提交了。

第二阶段：事务管理器告诉资源管理器执行commit或者rollback，如果任何一个节点显示不能commit，则所有节点都被告知需要rollback。

备注：应避免循环commit，自动commit和自动rollback。

5.索引

InnoDB支持的索引有:

5.1 B+(Balance+)树索引

B+树是平衡二叉树的扩展，B+树索引不能够找到一个给定键值的具体行，B+树索引找到的知识查找数据行所在的页(即InnoDB进行磁盘管理的最小单位，默认为16K)，让后数据库把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

在数据库中，B+树的一般高度都在2~4层，太高了会影响查询性能，毕竟搜索到每一层都需要IO。一般千万级别的数据量3层就足够了，如果数据量超过千万级别，那么最好的方式还是分库分表。

5.1.1 聚集索引

聚集索引(clustered index)按照每张表的主键构造一颗B+树，同时叶子节点(又称数据页)存放的即为整张表的行记录数据(注意：聚集索引的存储是逻辑上连续的，而不是物理上连续的)，即叶子节点上保存了完整的每行的记录，而在非叶子节点中存放的只有键值和指向子节点的偏移量，每个聚集索引的叶子节点都通过一个双向链表连接。 因为叶子节点只能按照一颗B+树进行排序，因此每张表只能够创建一个聚集索引。

聚集索引和主键的关系与区别：

关系：

a.如果一个表定义了主键，那么这个主键就是聚集索引；

b.如果表没有定义主键，那么该表的第一个唯一非空索引作为聚集索引；

c.如果没有主键和唯一非空索引，那么innodb内部会生成一个隐藏的主键作为聚集索引(隐藏主键是一个6字节的自增列)。

区别：

属性

主键

聚集索引

个数

一个表可以创建多个主键

一个表只能创建一个聚集索引

是否允许为NULL

不可以

可以

是否要求唯一

要求

不要求，可以通过单独设置为否限制唯一

自增主键的优缺点：

优点：速度快，数字型空间小，数据库可以自己控制主键自动增长；

缺点：插入指定ID的数据时，很难保证主键冲突；分库和分表情况下，自增主键将是一个很大的挑战。

5.1.2 非聚集索引

非聚集索引的叶子节点并不包含行记录的所有数据，叶子节点包含了键值和书签(书签告诉InnoDB哪里可以找到与索引相应的行数据，即相应行数据的聚集索引键)，因此效率和聚集索引没法比，几乎耗时是聚集索引的两倍，不过一张表上可以创建多个非聚集索引。

5.1.3 Cardinality值

Cardinality值表示索引中不重复记录的预估值，不是一个准确值，因为为了不影响数据库性能，Cardinality值是通过采样获得的。当Cardinality/Table_total_count值越大(最大值为1)，B+树索引创建的价值越大；当值很小时，没有必要创建B+索引。

5.2 全文检索

为解决SQL的like '%XXX%' 不走索引的问题，MySQL使用了倒排索引(和Elastic Search一样)，从MySQL 5.7.6开始已经支持中文了，但只能在CHAR、VARCHAR或者TEXT的字段上创建全文索引，而且要使用相同的字符集和排列规则，且每张表只能有一个全文索引。

正式由于这些问题，所以搜索很难达到Elastic Search的性能，故很少使用MySql的全文检索。

6.锁

锁用来管理对共享资源的并发访问，从而提供数据的完整性和一致性。

锁的类型：

共享锁：允许事务读一行数据；

排它锁：允许事务删除或更新一行数据。

行锁的算法：

1.Record Lock 单个行记录上的锁，总是锁住索引记录，如果没有索引，则锁定隐式主键；

2.Gap Lock 间隙锁 锁定一个范围，但不包含记录本身；

举例来说，假如student表中只有1001条记录，其student_id的值分别是1,2,...,1000,1001，下面的SQL：

SELECT * FROM student WHERE student_id > 1000 FOR UPDATE

是一个范围条件的检索，InnoDB不仅会对符合条件的student_id值为1001的记录加锁，也会对student_id大于101的“间隙”加锁，虽然这些记录并不存在。

3.Next-Key Lock 即Record Lock + Gap Lock 锁定一个范围，并包含记录本身。

6.1 脏读

脏读是指在不同的事务环境下，当前事务可以读到其他事务未提交的数据。

脏读.jpg

6.2 不可重复读

不可重复读是指在一个事务内，多次读取同一个数据集合，得到的结果集不一样(其他事务同时在做修改这个结果集包含的内容，并且已经commit)

不可重复读.jpg

6.3 幻读

幻读并不是值两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。(转自：https://segmentfault.com/a/1190000016566788)

事务-幻读.jpg

MySQL InnoDB通过版本号MVCC(Multi-Version Concurrency Control)解决事务的幻读问题，MVCC实现方式中，每行数据记录含有隐藏的两列数据

DATA_TRX_ID：事务的id，长度为6Byte

DATA_ROLL_PTR：表示回滚指针，指向该行回滚段 (rollback segment)的指针，长度为7 Byte，记录改行数据的所有未提交的旧版本数据，在undo中都通过链表的形式存储。

具体规则如下：

a.select情况：

InnoDB只会查找版本小于或者等于当前事务版本的数据行(即行的系统版本小于或等于事务的系统版本号)，这样确保了事务读取的行是当前事务开始之前已经存在的行，或者是事务自己自身插入或者修改的行。

b.insert情况：

InnoDB为新插入的每一行记录保存事务的id作为DATA_TRX_ID的值。

c.delete情况：

InnoDB为删除的每一行记录保存事务的id作为DATA_TRX_ID的值，回滚指针指向旧的版本数据。

d.update情况：

InnoDB为新插入的每一行记录保存当前系统的版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标示。

6.4 丢失更新

丢失更新.jpg

6.5 死锁

死锁是指两个或两个以上事务在执行过程中，因为相互争夺共享资源而造成的一种互相等待的现象。

解决死锁的方式：

1.超时阀值设置；

2.通过等待图来主动死锁检测，如果等待图中存在回路，就代表存在死锁。；

7 主从复制

MySQL主从复制的步骤：

+a.Master把数据修改记录保存到二进制日志binary log中；

+b.Slave把Master的binary log日志copy到自己的中继日志replay log中;

+c.Slave重做中继日志，将更改作用到自己的数据库上，以达到主从数据的最终一致性。

MySQL_copy_snapshot.jpg

7.1复制的方式

a.基于SQL语句的复制

Master记录那些更改数据的SQL到日志文件，Slave获取该SQL日志文件，然后再执行一遍Master执行过的SQL。

优点：

实现简单，主从数据传输量很小，占用带宽很少。如果出现问题可以很好的根据SQL语句去定位。

缺点：

更新必须是串行的，另外，Master和Slave执行SQL的时间和环境不同，会出现Master执行成功，Slave执行失败的问题，而且错误具有累积性。如果在使用触发器或者存储过程，最好不要使用基于SQL的复制模式。

b.基于行的复制

Master将实际数据记录保存到Binary log中，Slave获取该Binary log文件，然后更改对应的数据行。

优点：

使用场景广，可以正确的复制每一行，错误不具有累加性。

缺点：

针对一次修改数据量很大的情况，该方法会保存大量的Binary log数据，给Master和复制造成很大的负载，影响数据库性能。由于没有记录SQL语句，出现问题后，无法判断是那些SQL导致的问题。

c.折中

MySQL能够在这两种复制模式间动态切换，默认情况下使用基于SQL语句的复制，如果发现SQL语句无法正确的复制，就切换到基于行的复制模式。

7.2 复制的常见问题

Master过大的包，过大的复制延迟，受限的宽带，磁盘空间，主从系统配置不一样，加锁引起的锁争用，数据损坏，以及不确定性更改(update ... limit 5)等问题。

8 MySql缓存

MySql缓存.png

缓存的问题：

1.开启缓存之后，每次select开始之前先检查是否命中缓存，带来性能问题，特别是缓存命中率不高的情况时；

2.select结果被缓存时，也带来性能开销；

3.某个表被写入时，改表的所有缓存都会失效；

4.join操作会导致缓存失效；

5.事务也影响mysql缓存，在事务commit之后，数据才能够缓存。

9 MySql实践问题

9.1 varchar(n)保存数据长度问题

MySql 4.0 之前的版本

n指的是n个字节，如果存放utf-8格式只能保存 (n / 3)个汉字，即如果varchar(20) 那么只能保存6个汉字；

MySql 5.0 之后的版本

n指的是n个字符，如果存放utf-8格式，那么无论是数字，字母还是汉字，都可存放n个，即如果varchar(20) 那么可以保存20个汉字(自己亲测可以);

gbk类型：

varchar(n)中每个字符最多占2个字节，最大长度不能超过(65535 / 2);

utf-8类型：

varchar(n)中每个字符最多占3个字节，最大长度不能超过(65535 / 3);