Mysql详解及原理_mysql实现原理和机制

存储引擎

InnoDB存储引擎

InnoDB越做越好从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB，主要特点有

• 容灾恢复性比较好
• 支持事务，默认事务隔离界别为可重复读
• 使用的锁粒度为行锁，可以支持更高的并发
• 支持外键
• 配合一些热备工具可以支持在线热备份
• 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度
• 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。根据主键进行排序，数据和索引放在一块，都位于B+数的叶子节点上

MyISAM存储引擎

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少，主要特点有

• 不支持事务
• 不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用
• 对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存
• 默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁
• 支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等
• 数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复

事务底层原理

原子性：undo log
一致性：数据库+应用层面保障
隔离性：锁、MVCC、undo log
持久性：redo log
1
2
3
4

原子性

事务中的所有操作要么全部提交成功，要么全部失败回滚
比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.

一致性

数据库总是从给一个一致性的状态转换到另一个一致性的状态
例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.不管数据怎么改变。一定是符合约束

隔离性

一个事务所做的修改在提交之前对其它事务是不可见的
两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.

持久性

一旦事务提交，其所做的修改便会永久保存在数据库中。

事务隔离级别

READ UNCOMMITTED(读未提交)

事务中的修改即使未提交也是对其它事务可见

READ COMMITTED(读提交)

事务提交后所做的修改才会被另一个事务看见，可能产生一个事务中两次查询的结果不同。

REPEATABLE READ(可重读)

只有当前事务提交才能看见另一个事务的修改结果。解决了一个事务中两次查询的结果不同的问题。

SERIALIZABLE(串行化)

只有一个事务提交之后才会执行另一个事务。

READ UNCOMMITTED (未提交读) ：隔离级别：0. 哪个问题都不能解决
原理：
事务A和事务B，事务B可以读取事务A未提交的记录。会出现脏读，因为事务A可能会回滚操作，导致数据发生变化。

READ COMMITTED (提交读) ：隔离级别：1. 可以解决脏读 。
原理：
事务中只能看到已提交的修改，提交读这种隔离级别保证了读到的任何数据都是提交的数据，避免了脏读，但是不保证事务重新读的时候能读到相同的数据，因为在每次数据读完之后其他事务可以修改刚才读到的数据。

REPEATABLE READ (可重复读) ：隔离级别：2. 可以解决脏读和不可重复读，实现不幻读，需要加锁
原理：
在InnoDB中是这样的：RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，因此不存在幻读现象。但是标准的RR只能保证在同一事务中多次读取同样记录的结果是一致的，而无法解决幻读问题。InnoDB的幻读解决是依靠MVCC的实现机制做到的。Mysql默认的隔离级别是RR。

InnoDB的幻读解决是依靠MVCC的实现机制： （增加系统版本号，每次事务操作，会比较系统版本号）

InnoDB为每行记录添加了一个版本号（系统版本号），每当修改数据时，版本号加一。在读取事务开始时，系统会给事务一个当前版本号，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。

例如：
此时books表中有5条数据，版本号为1
事务A，系统版本号2：select * from books；因为1<=2所以此时会读取5条数据。
事务B，系统版本号3：insert into books …，插入一条数据，新插入的数据版本号为3，而其他的数据的版本号仍然是2，插入完成之后commit，事务结束。
事务A，系统版本号2：再次select * from books；只能读取<=2的数据，事务B新插入的那条数据版本号为3，因此读不出来，解决了幻读的问题。

SERIALIZABLE （可串行化）：隔离级别：3.
原理：
该隔离级别会在读取的每一行数据上都加上锁，退化为基于锁的并发控制，即LBCC。可以解决脏读不可重复读和幻读—相当于锁表

需要注意的是，MVCC只在RC和RR两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容。

优化

为什么要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
数据是存放在磁盘上的，读写速度无法和内存相比
1
2
3

如何优化

设计数据库时：数据库表、字段的设计，存储引擎
利用好MySQL自身提供的功能，如索引等
横向扩展：MySQL集群、负载均衡、读写分离
SQL语句的优化（收效甚微）
1
2
3
4

数据类型
尽量使用可以正确存储数据的最小数据类型

更小的数据类型意味着更快，占用更少的磁盘，内存、缓存和处理时间
尽量使用整型表示字符串

因为字符集和校对规则，使处理字符比整型更复杂，比如：我们使用数据库内置的datetime类型存储时间而不是字符类型，我们使用整型存储ip而不是直接将ip字符串存到数据库中
尽可能使用not null

这个值是很烦人的，建字段时请尽量指定是否非空，NULL使得索引，统计，比较都变得更复杂，而且索引尽量不要创建到可以为null的字段上
字符串类型
VARCHAR是可变长字符串

比定长字符串（CHAR）更节省空间，仅使用必要的空间另外VARCHAR需要额外字节记录字符串长度（不同情况需要字节数不同）
CHAR类型是定长字符串

开发中基本很少用(一些公司甚至基本上不考虑这种类型了)，注意：字符串长度定义不是字节数，是字符数
日期和时间类型
datetime

使用8字节存储空间，保存从1001年到9999年的秒数。与时区无关，默认情况下,Mysql以一种可排序的格式显示它的值，例如：“2018-10-14 22：30：08”
timestamp

只使用4字节存储，保存1970年1月1日午夜以来的秒数，依赖于系统时区，和UNIX时间戳相同，转换函数分别为FROM_UNIXTIME()和UNIX_TIMESTAMP()，可以设置根据当前时间戳更新，比如我们熟悉的update_time字段
整数类型
UNSIGNED

属性表示不允许负值，可以使得正数的上限提高一倍，比如tinyint+unsigned可以使原本的-128_{127的范围变为0}255
tinyint

我们一般用它存储状态值而不要用int，如果是Boolean类型，那么tinyint(1)当值为1和0时，查询结果自动转为true和false，条件参数相应的也可以直接传入true和false即可
INT(11)

不会限制值的范围，只是规定了一些客户端工具用来显示的字符的个数，所以对于存储和计算来说INT(11)和INT(1)相同
IP地址

实际上是32位无符号整数，用INT存储，Mysql提供转换函数为INET_ATON()和INET_NTOA()
小数

decimal不会损失精度，存储空间会随数据的增大而增大。double占用固定空间，较大数的存储会损失精度，通常存金额用decimal(11,2),这表示整数部分和小数部分分别为9位和2位注意！，当然可以根据具体的金额大小选择长度，注意这时候对应的java中用BigDecimal类来处理运算时要仔细，因为加减法和比较跟平常不一样

1、查询尽量避免全表扫描，首先考虑在where、order by字段上添加索引

2、避免在where字段上使用NULL值，所以在设计表时尽量使用NOT NULL约束，有些数据会默认为NULL，可以设置默认值为0或者-1

3、避免在where子句中使用!=或<>操作符，Mysql只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE使用索引

4、避免在where中使用OR来连接条件，否则可能导致引擎放弃索引来执行全表扫描，可以使用UNION进行合并查询

  select id from t where num = 30 union select id from t where num = 40;
1

5、尽量避免在where子句中进行函数或者表达式操作

6、最好不要使用select * from t，用具体的字段列表代替"*"，不要返回用不到的任何字段

7、in 和 not in 也要慎用，否则会导致全表扫描，如

select id from t where num IN(1,2,3)如果是连续的值建议使用between and，select id from t where between 1 and 3；

8、select id from t where col like %a%;模糊查询左侧有%会导致全表检索，如果需要全文检索可以使用全文搜索引擎比如es，slor

9、limit offset rows关于分页查询，尽量保证不要出现大的offset，比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行，完全可以加一些条件过滤一下（完成筛选），而不应该使用limit跳过已查询到的数据。这是一个offset做无用功的问题。对应实际工程中，要避免出现大页码的情况，尽量引导用户做条件过滤

1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL， 例如’ 省份’、’ 性 别’ 最好适用 ENUM
2、使用连接(JOIN)来代替子查询
3、适用联合(UNION)来代替手动创建的临时表
4、事务处理
5、锁定表、优化事务处理
6、适用外键， 优化锁定表
7、建立索引
8、优化查询语句

原理

索引

索引：帮助数据库高效获取数据的排好序的数据结构

B-树 & B+树

B-树：有序数组+平衡多叉树

一个M阶的b树具有如下几个特征：

1.定义任意非叶子结点最多只有M个儿子，且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]，向上取整；
4.非叶子结点的关键字个数=儿子数-1；
5.所有叶子结点位于同一层；
6.k个关键字把节点拆成k+1段，分别指向k+1个儿子，同时满足查找树的大小关系。

B树的优点：

对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

B+树：有序数组链表+平衡多叉树

B+树的特征：

1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

B+树的优势：

1.单一节点存储更多的元素，使得查询的IO次数更少。
2.所有查询都要查找到叶子节点，查询性能稳定。
3.所有叶子节点形成有序链表，便于范围查询。

B+Tree相对于B-Tree有几点不同：

1.非叶子节点只存储键值信息。
2.所有叶子节点之间都有一个链指针。
3.数据记录都存放在叶子节点中

主键索引

联合索引

最左前缀原理

1、先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。
2、等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。

innodb page (默认大小为16KB)

1.页头
2.页目录
3.用户数据区

innodb内存和磁盘结构

buffer pool

free链表

flush链表

lru链表

redolog

binlog

undolog

log buffer

change buffer

doublewrite buffer

死锁

两个或多个事务在同一资源上相互占用并请求锁定对方占用的资源，从而导致恶性循环的现象。MySQL的部分存储引擎能够检测到死锁的循环依赖并产生相应的错误。InnoDB引擎解决死锁的方案是将持有最少排它锁的事务进行回滚。

Undo原理：（备份旧数据）

在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

Redo原理：（保存最新数据）

和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

MVCC的设计目的是什么

增加两个隐藏的列：行的创建时间，版本号

MVCC是一种多版本并发控制机制。锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。

人们一般把基于锁的并发控制机制称成为悲观机制，而把MVCC机制称为乐观机制。这是因为锁机制是一种预防性的，读会阻塞写，写也会阻塞读，当锁定粒度较大，时间较长时并发性能就不会太好；而MVCC是一种后验性的，读不阻塞写，写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，从而大大提升了并发性能。

相关知识点

数据库设计3范式

第一范式1NF：确保每列保持原子性
第二范式2NF：确保表中的每列都和主键相关
第三范式3NF：确保每列都和主键列直接相关,而不是间接相关

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库！

最左匹配原则

1、先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。
2、等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。

1.聚集索引

聚集索引是按每张表的主键构造的一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的节点成为数据页，这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序，所以每张表只能拥有一个聚集索引。查询优化器非常倾向于采用聚集索引，因为其直接存储行数据，所以主键的排序查询和范围查找速度非常快。
不是物理上的连续，而是逻辑上的，不过在刚开始时数据是顺序插入的所以是物理上的连续，随着数据增删，物理上不再连续。

2.辅助索引

辅助索引页级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行中还包含了一个书签，该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。其中存的就是聚集索引的键。
辅助索引的存在并不影响数据在聚集索引的结构组织。InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后通过主键索引找到一个完整的行记录。当然如果只是需要辅助索引的值和主键索引的值，那么只需要查找辅助索引就可以查询出索要的数据，就不用再去查主键索引了。

MySQL读写分离

问题

• 应用程序直连数据库，耦合性高
• 用户并发高，数据库压力大，无论读还是写没有明确划分
• 数据库单实例运行是肯定不行的
• 表中数据越来越多怎么维护等等

解决方案
​  通过上边问题相信大家可以看出本质，我们可以部署多台MySQL搭建集群，配置读和写操作不同的数据库，数据进行拆分，存储在多张表中，应用程序与数据库降低耦合，添加中间件，总结几点如下

• 添加数据库中间件降低耦合
• 读写分离，提高数据库响应速度
• 数据库分表分库，更好维护数据

Mysql主从复制原理
​  主数据库会将所有的写操作写入到二进制文件（binlog）中，从数据库读取binlog日志再将读取到的binlog写入到中继日志（Relaylog）中，再通过SQL线程执行语句，实现主从复制数据同步，与Redis一样主数据库只能有一台，从数据库可以有多台，不同之处在于Redis是全量复制，而MySQL是从确认主从关系后开始复制，之前的数据是不同步的，读取二进制文件和写入中继日志都会发生IO所以会存在延时

双主双从读写分离
​​  上边我们介绍了一主一从实现读写分离，但是如果主库或者从库宕机就无法正常提供写和读的服务。我们使用双主双从，master1和master2为主服务器，都有对应的slave，当Master1宕机时Master2负责写请求，M1和M2互为备机，提高MySQL可用性

数据库分库分表思路

数据切分
数据库分布式核心内容无非就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。

数据切分根据其切分类型，可以分为两种方式：垂直（纵向）切分和水平（横向）切分

1、垂直（纵向）切分
垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。
垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。
垂直切分的优点：
解决业务系统层面的耦合，业务清晰
与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

缺点：
部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
分布式事务处理复杂
依然存在单表数据量过大的问题（需要水平切分）

2、水平（横向）切分
当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。
库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。

水平切分的优点：
不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
应用端改造较小，不需要拆分业务模块

缺点：
跨分片的事务一致性难以保证
跨库的join关联查询性能较差
数据多次扩展难度和维护量极大

数据分片规则为：
1、根据数值范围
按照时间区间或ID区间来切分。<冷热数据分离>
优点在于：
单表大小可控
天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移
使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点：
热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询

2、根据数值取模
一般采用hash取模mod的切分方式

优点：
数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈

缺点：
后期分片集群扩容时，需要迁移旧的数据（使用一致性hash算法能较好的避免这个问题）
容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

分库分表带来的问题

1、事务一致性问题

分布式事务
最终一致性

2、跨节点关联查询 join 问题

1）全局表
2）字段冗余
3）数据组装
4）ER分片
关系型数据库中，如果可以先确定表之间的关联关系，并将那些存在关联关系的表记录存放在同一个分片上，那么就能较好的避免跨分片join问题。在1:1或1:n的情况下，通常按照主表的ID主键切分

3、跨节点分页、排序、函数问题

4、全局主键避重问题

主键生成策略
1）UUID
2）结合数据库维护主键ID表
存在单点问题，强依赖DB，当DB异常时，整个系统都不可用。配置主从可以增加可用性，但当主库挂了，主从切换时，数据一致性在特殊情况下难以保证。另外性能瓶颈限制在单台MySQL的读写性能。
3）Snowflake分布式自增ID算法

5、数据迁移、扩容问题