MYSQL底层内容丰硕多彩，本文细致介绍相干内容。

mysql视频教程栏目介绍底层道理。

内部模块

连贯器(JDBC、ODBC等) =>

[MYSQL 内部

[Connection Pool] (授权、线程复用、连贯限定、内存检测等)
=>

[SQL Interface] (DML、DDL、Views等) [Parser] (Query Translation、Object privilege) [Optimizer] (Access Paths、 统计剖析) [Caches & Buffers]
=>

[Pluggable Storage Engines] 

]

=> [File]

内存构造

这里有个关键点，当我们去查询数据时候会先 拿着我们目前查询的 page 去 buffer pool 中查询 目前page可否在缓冲池中。要是在，则直接猎取。

而要是是update操纵时，则会直接修改 Buffer中的值。这个时候，buffer pool中的数据就和我们磁盘中现实存储的数据纷歧致了，称为脏页。每隔一段工夫，Innodb存储引擎就会把脏页数据刷入磁盘。个别来说当更新一条数据，我们需要将数据给读取到buffer中修改，然后写回磁盘，完成一次 降盘IO 操纵。

为了提高update的操纵机能，Mysql在内存中做了优化，可以看到，在架构图的缓冲池中有一块区域叫做：change buffer。望文生义，给change后的数据，做buffer的，当更新一个没有 unique index 的数据时，直接将修改的数据放到 change buffer，然后通过 merge 操纵完成更新，从而减少了那一次 降盘的IO 操纵。

• 我们上面说的有个前提：没有独一索引的数据更新时，为何必需要没有独一索引的数据更新时才干直接放入change buffer呢？要是是有独一束缚的字段，我们在更新数据后，可能更新的数据和已经存在的数据有反复，所以只能从磁盘中把所有数据读出来比对才干肯定独一性。
• 所以当我们的数据是 写多读少 的时候，就可以通过 添加 innodb_change_buffer_max_size 来调整 change buffer在buffer pool 中所占的比例，默许25（即：25%）

题目又来了，merge是怎样运作的

有四种状况：

1. 有其他拜访，拜访到了目前页的数据，就会合并到磁盘
2. 后台线程按时merge
3. 系统正常shut down以前，merge一次
4. redo log写满的时候，merge到磁盘

一、redo log有哪些

谈到redo，就要谈到innodb的 crash safe，运用 WAL 的方式实现（write Ahead Logging，在写以前先记载日志）

这样就可以在，当数据库解体的后，直接从 redo log中恢复数据，保障数据的准确性

redo log 默许存储在两个文件中 ib_logfile0 ib_logfile1，这两个文件都是牢固大小的。为何需要牢固大小？

这是由于redo log的 次序读取 的特性造成的，必需是陆续的存储空间

二、随机读写与次序读写

看一张图

所以有时候，我们被请求主键为何如果有序的缘由就是，要是我们在一个有序的字段上，创立索引，然后插入数据。 在存储的时候，innodb就会按着次序一个个存储到 页 上，存满一个页再去申请新的页，然后接着存。

但要是我们的字段是无序的，存储的位置就会在不一样的页上。当我们的数据存储到一个已经被 存满的页上时，就会造成页破裂，从而构成碎片。

几种不一样的索引组织情势

1. 聚簇索引，如上面B+树图所示，子节点上存储行数据，而且索引的罗列的次序和索引键值次序一致的话就是 聚簇索引。主键索引就是聚簇索引，除了主键索引，其他所以都是辅助索引
2. 辅助索引，要是我们新建了一个辅助索引，它的叶子节点上只存储本人的值和主键索引的值。这就意味着，要是我们通过辅助索引查询所有数据，就会先去查寻辅助索引中的主键键值，然后再去主键索引里面，查到相干数据。这个历程称为回表
3. rowid 要是没有主键索引怎么办呢？
   1. 没有主键，但是有一个 Unique key 并且都不是 null的，则会依据这个 key来新建聚簇索引。
   2. 那上面两种都没有呢，别担忧，innodb本人保护了一个叫 rowid 的东西，依据这个id来新建 聚簇索引

索引怎样起作用

搞分明什么是索引，构造有哪些之后。 我们来看看，什么时候我们要用到索引，了解了这些能更好的帮忙我们新建准确高效的索引

1. 离散度低不建索引，也就是数据之间相差不大的就没须要创立索引。（由于创立索引，在查询的时候，innodb大多数据都是雷同的，我走索引 和全表没什么差别就会直接全表查询）。比方 性别字段。这样反而浪费了批量的存储空间。

2. 结合字段索引，比方 idx(name, class_name)

   1. 当施行 select * from stu where class_name = xx and name = lzw 查询时，也能走 idx 这个索引的，由于优化器将SQL优化为了 name = lzw and class_name = xx
   2. 当需要有 select ··· where name = lzw 的时候，不需要新建一个独自的 name索引，会直接走 idx这个索引
   3. 遮盖索引。要是我们此次查询的所有数据全都包括在索引里面了，就不需要再 回表去查询了。比方：select class_name from stu where name =lzw

3. 索引前提下推(index_condition_pushdown)

   1. 有这样一条SQL，select * from stu where name = lzw and class_name like '%xx'
   2. 要是没有索引前提下推，由于背面是 like '%xx'的查询前提，所以这里第一依据 name 走 idx结合索引 查询到几条数据后，再回表查询到全量row数据，然后在server层进行 like 过滤寻到数据
   3. 要是有，则直接在引擎层对like也进行过滤了，相当于把server层这个过滤操纵下推到引擎层了。如图所示：

锁篇

四大特性

先回忆一下我们耳熟能详的几个根本概念：

1. 原子性（通过Undo log实现）
2. 一致性
3. 隔离性
4. 耐久性（解体恢复，Redo log + double write 实现）

读一致性题目应当由数据库的事务隔离级别来解决 （SQL92 规范）

条件，在一个事务中：

1. 脏读（读到了他人尚无commit的数据，然后他人又回滚掉了）
2. 不成反复读（首先次读取到了数据，然后他人修改commit了，再次去读取就读到了他人已经commit的数据）
3. 幻读（在范畴查询的时候，读到他人新增加的数据）

SQL92 规范规定： (并发度从左到右，顺次落低)

• RC的MVCC实现是对 统一个事务的多个读 新建一个版本 而 RR 是 统一个事务任何一条都新建一个版本

通过MVCC与LBCC的联合，InnoDB能解决关于不加锁前提下的 幻读的状况。而无须像 Serializable 同样，必需让事务串行进行，无任何并发。

下面我们来深入研究一下InnoDB锁是怎样实现 RR 事务隔离级另外

锁深入 MVCC在Innodb的实现

一、Innodb 的锁

1. Shared and Exclusive Locks 同享和排它锁 =>（S、X）
2. Intention Locks 意向锁 => 这里指的是两把锁，其实就是表级别的 同享和排它锁 => (IS、IX)

上面这四把锁是最根本锁的类型

3. Record Locks 记载锁
4. Gap Locks 间隙锁
5. Next-key Locks 临锁

这三把锁，了解成关于上面四把锁实现的三种算法方式，我们这里暂且把它们称为：高阶锁

6. Insert Intention Locks 插入锁
7. AUTO-INC Locks 自增键锁
8. Predicate Locks for Spatial Indexes 专用于给Spatial Indexes用的

上面三把是额外扩展的锁

二、读写锁深入解释

1. 要运用同享锁，在语句背面加上lock in share mode 。排它锁默许 Insert、Update、Delete会运用。显示运用在语句后加for update。
2. 意向锁都是由数据库本人保护的。（主要作用是给表打一个标志，记载这个表可否被锁住了） => 要是没有这个锁，另外事务想锁住这张表的时候，就要去全表扫描可否有锁，效率太低。所以才会成心向锁的存在。

增补：Mysql中锁，到底锁的有哪些

锁的是索引，那么这个时候可能有人要问了：那要是我不新建索引呢？

索引的存在，我们上面讲过了，这里再回忆一下，有下面几种状况

1. 你建了一个 Primary key， 就是汇集索引 （存储的是 完备的数据）
2. 没有主键，但是有一个 Unique key 而是都不是 null的，则会依据这个 key来新建 聚簇索引
3. 那上面两种都没有呢，别担忧，innodb本人保护了一个叫 rowid 的东西，依据这个id来新建 聚簇索引

所以一个表里面，必定会存在一个索引，所以锁固然总有索引拿来锁住了。

当要给一张你没有显示新建索引的表，进行加锁查询时，数据库其实是不晓得到底要查哪些数据的，整张表可能都会用到。所以干脆就锁整张表。

• 要是是给辅助索引加写锁，比方select * from where name = ’xxx‘ for update 最后要回表查主键上的信息，所以这个时候除了锁辅助索引还要锁主键索引

三、高阶锁深入解释

第一上三个概念，有这么一组数据：主键是 1，3，6，9 在存储时候有如下：x 1 x 3 x x 6 x x x 9 x···

记载锁，锁的是每个记载，也就是 1，3，6，9 间隙锁，锁的是记载间隙，每个 x，(-∞,1), (1,3), (3,6), (6,9), (9,+∞) 临锁，锁的是 (-∞,1], (1,3], (3,6], (6,9], (9,+∞] 左开右闭的区间

第一这三种锁都是 排它锁， 而且 临键锁 = 记载锁 + 间隙锁

1. 当 select * from xxx where id = 3 for update 时，发生记载锁
2. 当 select * from xxx where id = 5 for update 时，发生间隙锁 => 锁住了(3,6)，这里要分外注意一点：间隙锁之间是不冲突的。
3. 当 select * from xxx where id = 5 for update 时，发生临键锁 => 锁住了(3,6], mysql默许运用临键锁，要是谴责脚 1 ，2 状况 则他的行锁的都是临键锁

• 回到开端的题目，在这里 Record Lock 行锁防止另外事务修改或删除，Gap Lock 间隙锁防止另外事务新增，Gap Lock 和 Record Lock联合构成的Next-Key锁共同解决RR级别在写数据时的幻读题目。

说到了锁那么必定逃不外要说一下死锁

产生死锁后的检查

1. show status like 'innodb_row_lock_%'
   1. Innodb_row_lock_current_waits 目前正在有多少期待锁
   2. Innodb_row_lock_time 一共期待了多少工夫
   3. Innodb_row_lock_time_avg 均匀等多少工夫
   4. Innodb_row_lock_time_max 最大等多久
   5. Innodb_row_lock_waits 一共涌现过多少次期待
2. select * from information_schema.INNODB_TRX 能查看到目前正在运转和被锁住的事务
3. show full processlist = select * from information_schema.processlist 能查询出是 哪个会员 在哪台机器host的哪个端口上 连贯哪个数据库 施行什么指令 的 状态与工夫

死锁预防

1. 保障拜访数据的次序
2. 以免where的时候不消索引（这样会锁表，不仅死锁更容易发生，并且机能更加低下）
3. 一个非常大的事务，拆成多个小的事务
4. 尽量运用等值查询（就算用范畴查询也要限制一个区间，而不要只开不闭，比方 id > 1 就锁住背面所有）

优化篇

分库分表

动态选中数据源

编码层 -- 实现 AbstracRoutingDataSource => 框架层 -- 实现 Mybatis Plugin => 驱动层 -- Sharding-JDBC(配置多个数据源，依据自定义实现的战略对数据进行分库分表存储)中心流程,SQL解析=>施行优化=>SQL数据库路由=>SQL转变(比方分表，改表名)=>SQL施行=>效果归并） => 代理层 -- Mycat（将所有与数据库的连贯独立出来。全部由Mycat连贯，其他办事拜访Mycat猎取数据） => 办事层 -- 特别的SQL版本

MYSQL怎样做优化

说到底我们学习这么多见识都是为了能更好运用MYSQL，那就让我们来实操一下，创立一个完备的优化体系

要想获得更好的查询机能，可以从这张查询施行历程入手

一、客户端连贯池

增加连贯池，以免每次都创建、烧毁连贯那我们的连贯池是不是多多益善呢？ 有乐趣的盆友可以看看这篇文章：About Pool Sizing

我大约总结一下：

1. 我们并发的施行SQL，并不会由于连贯数目增多而变快。为何呢？要是我有10000连贯同时并发施行，难道不比你10个连贯施行快得多吗? 答案可否定的，不仅烦懑反而越来越慢。
   1. 在盘算机中，我们都晓得只要CPU才干真正去施行线程。而操纵系统由于用工夫分片的技术，让我们认为一个CPU内核施行了多个线程。
   2. 但其实上一个CPU在某个工夫段只能施行一个线程，所以不管我们怎么添加并发，CPU还是只能在这个工夫段里处置这么多数据。
   3. 那就算CPU处置不了这么多数据，又怎么会变慢？由于工夫分片，当多个线程看起来在"同时施行"，其实他们之间的高低文切换十分耗时
   4. 所以，一旦线程的数目超过了CPU中心的数目，再添加线程数系统就只会更慢，而不是更快。
2. 固然，这只是其中最中心的缘由，磁盘一样也会对速度有影响，同时也对我们连贯数配置有影响。
   1. 比方我们用的机械硬盘，我们要通过扭转，找址到某个位置，再进行I/O操纵，这个时候，CPU就可以把工夫，分片给其他线程，以提拔处置效率和速度
   2. 所以，要是你用的是机械硬盘，我们平常可以多增加一些连贯数，维持高并发
   3. 但要是你用的是 SSD 呢，由于I/O期待工夫非常短，所以我们就不克不及增加过多连贯数
3. 通过来说你需要遵循这么一个公式：线程数 = ((中心数 * 2) + 有效磁盘数)。比方一台 i7 4core 1hard disk的机器，就是 4 * 2 + 1 = 9
4. 看到这个公式不晓得大家是不是很眼熟，这不仅适用于数据库连贯，也适用于任何许多CPU盘算和I/O的场景 比方：设定最大线程数等

二、数据库整体设计方案

第三方缓存

要是并发非常大，就不克不及让他们全打到数据库上，在客户端连贯数据库查询时，增加如Redis这种三方缓存

集群方式部署数据库

既然我们一个数据库接受不了庞大的并发，那为何未几增加几台机器呢？ 主从复制道理图

从图中我们不难看出、Mysql主从复制 读写别离 异步复制的特性。

• tips: 在把Binary Log写入relay log之后，slave都会把最新读取到的Binary Log Position记载到master info上，下一次就直接从这个位置去取。

不一样方式的主从复制

上面这种异步的主从复制，很显明的一个题目就是，更新不及时的题目。当写入一个数据后，即将实用户读取，读取的还是以前的数据，也就是存在着延时。 要解决延时的题目，就需要引入 事务

1. 全同步复制，事务方式施行，主节点先写入，然后让所有slave写，必需要所有 从节点 把数据写完，才返回写成功，这样的话会大大影响写入的机能
2. 半同步复制，只有有一个salve写入数据，就算成功。（要是需要半同步复制，主从节点都需要安装semisync_mater.so和 semisync_slave.so插件）
3. GTID（global transaction identities）复制，主库并行复制的时候，从库也并行复制，解决主从同步复制推迟，实现主动的failover行动，即主节点挂掉，推举从节点后，能迅速主动以免数据遗失。

集群高可用方案

1. 主从 HAPrxoy + keeplive
2. NDB
3. Glaera Cluster for MySQL
4. MHA（Master-Mater replication manager for MySQL），MMM（MySQL Master High Available）
5. MGR（MySQL Group Replication） => MySQL Cluster

分表

对数据进行分类划分，分成不一样表，减少对单一表造成过多锁操纵影响机能

表构造

1. 设计合理字段类型
2. 设计合理字段长度

三、优化器与施行引擎

慢日志

开启show_query_log，施行工夫超过变量long_query_time的SQL会被记载下来。 可以运用mysqldumpslow /var/lib/mysql/mysql-slow.log，还有许多插件可以供给比这个更优雅的剖析，这里就不细致讲了。

explain剖析SQL

任何SQL在写完之后都应当explain一下

1. 驱动表 - 比方滥用left/right join致使机能低下

1. 运用left/right join会直接指定驱动表，在MYSQL中，默许运用Nest loop join进行表关联（即通过驱动表的效果集作为轮回根基数据，然后通过此汇合中的每一条数据筛选下一个关联表的数据，最后合并效果，得出我们常说的暂时表）。
2. 要是驱动表的数据是 百万千万级另外，不言而喻这联表查询得有多慢。但是反过来，要是以小表作为驱动表，借助千万级表的索引查询就能变得很快。
3. 要是你不肯定到底该用谁来作为驱动表，那么请交给优化器来决议，比方：select xxx from table1, table2, table3 where ···，优化器会将查询记载行数少的表作为驱动表。
4. 要是你就是想本人指定驱动表，那么请拿好Explain兵器，在Explain的效果中，首先个就是根基驱动表
5. 排序。一样的，对不一样表排序也是有很大的机能悬殊，我们尽量对驱动表进行排序，而不要对暂时表，也就是合并后的效果集进行排序。即施行规划中涌现了 using temporary，就需要进行优化。

2. 施行规划各参数含义

1. select_type（查询的类型）：普通查询和复杂查询（结合查询、子查询等）
   1. SIMPLE，查询不包括子查询或者UNION
   2. PRIMARY，要是查询包括复杂查询的子构造，那么就需要用到主键查询
   3. SUBQUERY，在select或者where中包括 子查询
   4. DERIVED，在from中包括子查询
   5. UNION RESULT，从union表查询子查询
2. table 运用到的表名
3. type（拜访类型），寻到所需行的方式，从上往下，查询速度越来越快
   1. const或者system 常量级另外扫描，查询表最快的一种，system是const的一种特别状况（表中只要一条数据）
   2. eq_ref 独一性索引扫描
   3. ref 非独一性索引扫描
   4. range 索引的范畴扫描，比方 between、<、>等范畴查询
   5. index （index full）扫描全部索引树
   6. ALL 扫描全表
   7. NULL，不需要拜访表或者索引
4. possible_keys，给出运用哪个索引能寻到表中的记载。这里被列出的索引纷歧定运用
5. key：到底哪一个索引被真正运用到了。要是没有则为NULL
6. key_len：运用的索引所占用的字节数
7. ref：哪个字段或者常数和索引（key）一起被运用
8. rows：一共扫描了多少行
9. filtered（百分比）：有多少数据在server层还进行了过滤
10. Extra：额外信息
    1. only index 信息只需要从索引中查出，可能用到了遮盖索引，查询非常快
    2. using where 要是查询没有运用索引，这里会在server层过滤再运用 where来过滤效果集
    3. impossible where 啥也没查出来
    4. using filesort ，只有没有通过索引来排序，而是运用了其他排序的方式就是 filesort
    5. using temporary（需要通过暂时表来对效果集进行临时存储，然后再进行盘算。）个别来说这种状况都是进行了DISTINCT、排序、分组
    6. using index condition 索引下推，上文讲过，就是把server层这个过滤操纵下推到引擎层

四、存储引擎

1. 当仅仅是插入与查询比拼多的时候，可以运用MyISAM存储引擎
2. 当只是运用暂时数据，可以运用memory
3. 当插入、更新、查询等并发数许多时，可以运用InnoDB

总结

从五个条理答复MYSQL优化，由上至下

1. SQL与索引
2. 存储引擎与表构造
3. 数据库架构
4. MySQL配置
5. 硬件与操纵系统

除此以外，查数据慢，要不仅仅扣留于一味的 "优化" 数据库，而是要从业务利用层面去剖析。比方对数据进行缓存，对要求进行限流等。

我们下篇文章见

相干免费学习举荐：mysql视频教程

以上就是一篇文章让你搞懂MYSQL底层道理的细致内容，更多请关注 百分百源码网 其它相干文章！