热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

一篇文章让你搞懂MYSQL底层原理

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]

内存结构

这里有个关键点,当我们去查询数据时候会先 拿着我们当前查询的 pagebuffer 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 bufferbuffer 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'的查询条件,所以这里首先根据 nameidx联合索引 查询到几条数据后,再回表查询到全量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 是 同一个事务任何一条都创建一个版本

通过MVCCLBCC的结合,InnoDB能解决对于不加锁条件下的 幻读的情况。而不必像 Serializable 一样,必须让事务串行进行,无任何并发

下面我们来深入研究一下InnoDB锁是如何实现 RR 事务隔离级别的

锁深入 MVCC在Innodb的实现

一、Innodb 的锁

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

上面这四把锁最基本锁的类型

  1. Record Locks 记录锁
  2. Gap Locks 间隙锁
  3. Next-key Locks 临锁

这三把锁,理解成对于上面四把锁实现的三种算法方式,我们这里暂且把它们称为:高阶锁

  1. Insert Intention Locks 插入锁
  2. AUTO-INC Locks 自增键锁
  3. 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底层原理的详细内容,更多请关注 第一PHP社区 其它相关文章!


推荐阅读
author-avatar
技术潜行者
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有