连接器(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%)有四种情况:
redo log
写满的时候,merge到磁盘谈到redo,就要谈到innodb的 crash safe
,使用 WAL 的方式实现(write Ahead Logging,在写之前先记录日志)
这样就可以在,当数据库崩溃的后,直接从 redo log
中恢复数据,保证数据的正确性
redo log 默认存储在两个文件中 ib_logfile0
ib_logfile1
,这两个文件都是固定大小的
。为什么需要固定大小?
这是因为redo log
的 顺序读取
的特性造成的,必须是连续的存储空间
看一张图
所以有时候,我们被要求主键为什么要是有序的原因就是,如果我们在一个有序的字段上,建立索引,然后插入数据。
在存储的时候,innodb就会按着顺序一个个存储到 页
上,存满一个页再去申请新的页,然后接着存。
但如果我们的字段是无序的,存储的位置就会在不同的页上。当我们的数据存储到一个已经被 存满
的页
上时,就会造成页分裂
,从而形成碎片
。
B+树
图所示,子节点上存储行数据
,并且索引的排列的顺序
和索引键值顺序
一致的话就是 聚簇索引
。主键索引就是聚簇索引,除了主键索引,其他所以都是辅助索引
辅助索引
,它的叶子节点上只存储自己的值
和主键索引的值
。这就意味着,如果我们通过辅助索引查询所有数据,就会先去查找辅助索引
中的主键键值
,然后再去主键索引
里面,查到相关数据
。这个过程称为回表
rowid
如果没有主键索引
怎么办呢?聚簇索引
。rowid
的东西,根据这个id来创建 聚簇索引
搞清楚什么是索引,结构是什么之后。 我们来看看,什么时候我们要用到索引,理解了这些能更好的帮助我们创建正确高效的索引
离散度低不建索引,也就是数据之间相差不大的就没必要建立索引。(因为建立索引,在查询的时候,innodb大多数据都是相同的,我走索引 和全表没什么差别就会直接全表查询
)。比如 性别字段。这样反而浪费了大量的存储空间。
联合字段索引,比如 idx(name, class_name)
select * from stu where class_name = xx and name = lzw
查询时,也能走 idx
这个索引的,因为优化器将SQL优化为了 name = lzw and class_name = xx
select ··· where name = lzw
的时候,不需要创建一个单独的 name
索引,会直接走 idx
这个索引覆盖索引
。如果我们此次查询的所有数据
全都包含在索引里面了,就不需要再 回表
去查询了。比如:select class_name from stu where name =lzw
索引条件下推(index_condition_pushdown)
select * from stu where name = lzw and class_name like '%xx'
索引条件下推
,因为后面是 like '%xx'
的查询条件,所以这里首先根据 name
走 idx联合索引
查询到几条数据后,再回表
查询到全量row数据
,然后在server层
进行 like 过滤找到数据引擎层
对like也进行过滤了,相当于把server层
这个过滤操作下推到引擎层
了。如图所示:先回顾一下我们耳熟能详的几个基本概念:
前提,在一个事务中:
SQL92 标准规定: (并发度从左到右,依次降低)
RC的MVCC实现是对 同一个事务的多个读 创建一个版本
而 RR 是 同一个事务任何一条都创建一个版本
通过MVCC
与LBCC
的结合,InnoDB能解决对于不加锁
条件下的 幻读的情况。而不必像 Serializable
一样,必须让事务串行
进行,无任何并发
。
下面我们来深入研究一下InnoDB锁
是如何实现 RR
事务隔离级别的
表级别
的 共享和排它锁 => (IS、IX)上面这四把锁
是最基本锁的类型
这三把锁,理解成对于上面四把锁
实现的三种算法方式,我们这里暂且把它们称为:高阶锁
上面三把是额外扩展的锁
lock in share mode
。排它锁默认 Insert、Update、Delete
会使用。显示使用在语句后加for update
。打一个标记
,记录这个表是否被锁住了) => 如果没有这个锁,别的事务想锁住这张表的时候,就要去全表扫描是否有锁,效率太低。所以才会有意向锁的存在。锁的是索引,那么这个时候可能有人要问了:那如果我不创建索引呢?
索引的存在,我们上面讲过了,这里再回顾一下,有下面几种情况
完整的数据
)聚簇索引
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,+∞] 左开右闭的区间
首先这三种锁都是 排它锁
, 并且 临键锁 = 记录锁 + 间隙锁
select * from xxx where id = 3 for update
时,产生记录锁select * from xxx where id = 5 for update
时,产生间隙锁 => 锁住了(3,6),这里要格外注意一点:间隙锁之间是不冲突的。select * from xxx where id = 5 for update
时,产生临键锁 => 锁住了(3,6], mysql默认使用临键锁,如果不满足 1 ,2 情况 则他的行锁的都是临键锁Record Lock 行锁
防止别的事务修改或删除,Gap Lock 间隙锁
防止别的事务新增,Gap Lock 和 Record Lock
结合形成的Next-Key锁
共同解决RR级别
在写数据时的幻读问题。show status like 'innodb_row_lock_%'
select * from information_schema.INNODB_TRX
能查看到当前正在运行和被锁住的事务show full processlist
= select * from information_schema.processlist
能查询出是 哪个用户
在哪台机器host的哪个端口上
连接哪个数据库
执行什么指令
的 状态与时间
编码层 -- 实现 AbstracRoutingDataSource => 框架层 -- 实现 Mybatis Plugin => 驱动层 -- Sharding-JDBC(配置多个数据源,根据自定义实现的策略对数据进行分库分表存储)核心流程,SQL解析=>执行优化=>SQL数据库路由=>SQL改变(比如分表,改表名)=>SQL执行=>结果归并) => 代理层 -- Mycat(将所有与数据库的连接独立出来。全部由Mycat连接,其他服务访问Mycat获取数据) => 服务层 -- 特殊的SQL版本
说到底我们学习这么多知识都是为了能更好使用MYSQL,那就让我们来实操一下,建立一个完整的优化体系
要想获得更好的查询性能,可以从这张查询执行过程
入手
添加连接池,避免每次都新建、销毁连接
那我们的连接池是不是越多越好呢?
有兴趣的盆友可以看看这篇文章:About Pool Sizing
我大概总结一下:
CPU
才能真正去执行线程
。而操作系统因为用时间分片
的技术,让我们以为一个CPU内核
执行了多个线程
。CPU
在某个时间段
只能执行一个线程
,所以无论我们怎么增加并发,CPU
还是只能在这个时间段里处理这么多数据。CPU
处理不了这么多数据,又怎么会变慢?因为时间分片
,当多个线程看起来在"同时执行"
,其实他们之间的上下文切换
十分耗时I/O
操作,这个时候,CPU
就可以把时间,分片给其他线程
,以提升处理效率和速度I/O
等待时间非常短,所以我们就不能添加过多连接数线程数 = ((核心数 * 2) + 有效磁盘数)
。比如一台 i7 4core 1hard disk
的机器,就是 4 * 2 + 1 = 9很多CPU计算和I/O的场景
比如:设置最大线程数等如果并发非常大,就不能让他们全打到数据库上,在客户端连接数据库查询时,添加如Redis
这种三方缓存
既然我们一个数据库承受不了巨大的并发,那为什么不多添加几台机器呢? 主从复制原理图
从图中我们不难看出、Mysql主从复制 读写分离
异步复制
的特性。
Binary Log
写入relay log
之后,slave
都会把最新读取到的Binary Log Position
记录到master info
上,下一次就直接从这个位置去取。上面这种异步
的主从复制,很明显的一个问题就是,更新不及时的问题。当写入一个数据后,马上有用户读取,读取的还是之前的数据,也就是存在着延时。
要解决延时的问题,就需要引入 事务
failover
动作,即主节点挂掉,选举从节点后,能快速自动避免数据丢失。对数据进行分类划分,分成不同表,减少对单一表造成过多锁操作
影响性能
开启show_query_log
,执行时间超过变量long_query_time
的SQL会被记录下来。
可以使用mysqldumpslow /var/lib/mysql/mysql-slow.log
,还有很多插件可以提供比这个更优雅的分析,这里就不详细讲了。
任何SQL在写完之后都应该explain
一下
left/right join
导致性能低下left/right join
会直接指定驱动表,在MYSQL中,默认使用Nest loop join
进行表关联(即通过驱动表
的结果集作为循环基础数据,然后通过此集合中的每一条数据筛选下一个关联表的数据,最后合并结果,得出我们常说的临时表
)。驱动表
的数据是 百万千万
级别的,可想而知这联表查询得有多慢。但是反过来,如果以小表
作为驱动表
,借助千万级表
的索引
查询就能变得很快。驱动表
,那么请交给优化器来决定,比如:select xxx from table1, table2, table3 where ···
,优化器会将查询记录行数少的表作为驱动表。驱动表
,那么请拿好Explain
武器,在Explain
的结果中,第一个就是基础驱动表
表
排序也是有很大的性能差异,我们尽量对驱动表
进行排序,而不要对临时表,也就是合并后的结果集
进行排序。即执行计划中出现了 using temporary
,就需要进行优化。普通查询
和复杂查询
(联合查询、子查询等)SIMPLE
,查询不包含子查询或者UNIONPRIMARY
,如果查询包含复杂查询
的子结构,那么就需要用到主键查询SUBQUERY
,在select
或者where
中包含 子查询
DERIVED
,在from
中包含子查询UNION RESULT
,从union
表查询子查询越来越快
const或者system
常量级别的扫描,查询表最快的一种,system是const的一种特殊情况(表中只有一条数据)eq_ref
唯一性索引扫描ref
非唯一性索引扫描range
索引的范围扫描,比如 between、<、>等范围查询index
(index full)扫描全部索引树ALL
扫描全表NULL
,不需要访问表或者索引不一定使用
哪一个索引被真正使用
到了。如果没有则为NULL索引(key)
一起被使用only index
信息只需要从索引中查出,可能用到了覆盖索引,查询非常快using where
如果查询没有使用索引,这里会在server
层过滤再使用 where
来过滤结果集impossible where
啥也没查出来using filesort
,只要没有通过索引来排序,而是使用了其他排序的方式就是 filesortusing temporary
(需要通过临时表来对结果集进行暂时存储,然后再进行计算。)一般来说这种情况都是进行了DISTINCT、排序、分组
using index condition
索引下推,上文讲过,就是把server层
这个过滤操作下推到引擎层
插入与查询
比较多的时候,可以使用MyISAM
存储引擎memory
插入、更新、查询
等并发数很多时,可以使用InnoDB
从五个层次回答MYSQL优化,由上至下
除此之外,查数据慢,要不仅仅拘留于一味的 "优化" 数据库,而是要从业务应用层面去分析。比如对数据进行缓存,对请求进行限流等。
我们下篇文章见
相关免费学习推荐:mysql视频教程
以上就是一篇文章让你搞懂MYSQL底层原理的详细内容,更多请关注 第一PHP社区 其它相关文章!