MySQL
文章平均质量分 93
程序员小潘
Java开发工程师,现居杭州,CSDN博客专家,热衷于分享计算机编程相关知识,欢迎关注~
展开
-
MySQL架构与历史
《高性能MySQL》读书笔记一MySQL逻辑架构第一层:连接处理,授权认证,安全校验等…第二层:查询分析,优化,缓存,内置函数实现。第三层:由存储引擎来负责对数据进行存储和查询。连接管理与安全每个客户端连接都会在MySQL服务端进程中拥有一个线程,每个连接的查询只会在自己单独的线程中执行,因此MySQL是支持并发的。为了保证数据的一致性,MySQL支持四种事务隔离级别和各种锁机制...原创 2019-11-17 17:38:50 · 3061 阅读 · 0 评论 -
Schema与数据类型优化
《高性能MySQL》读书笔记二选择合适的数据类型简单的几个原则:更小的更好:一般情况下,应该尽量使用可以存储数据的最小数据类型。更小的数据类型通常更快,占用更少的磁盘、内存和CPU缓存。schema设计之初,应该估算出字段的数据能用到的最大值,然后在满足要求的前提下使用最小数据类型。简单:简单数据类型的操作通常需要更少的CPU周期。例如:整型比字符型操作代价更低。使用整...原创 2019-11-17 17:41:03 · 3174 阅读 · 0 评论 -
MySQL事务和锁
MySQL中事务的实现机制和锁,以及不同隔离级别下的测试笔记原创 2019-10-16 19:06:54 · 4969 阅读 · 0 评论 -
创建高性能的索引
索引基础索引:存储引擎用于快速找到记录的一种数据结构。索引可以包含一个或多个列,如果包含多个列,列的顺序也十分重要,因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两列的索引,和创建两个只包含一列的索引是大不相同的。索引的类型B-Tree索引可以使用B-Tree索引的查询类型:全值匹配匹配最左前缀匹配列前缀匹配范围值精确匹配某一列并范围匹配另外一列只访问索引的...原创 2019-11-17 17:41:46 · 3112 阅读 · 0 评论 -
MySQL的一些高级特性(读书笔记)
分区表对用户来说,分区表是一个独立的逻辑表,但是底层是由多个物理子表组成。MySQL实现分区表的方式:对底层表的封装。意味着索引也是按照分区的子表定义的,没有全局索引。这和Oracle不同,Oracle可以更加灵活的定义索引和表是否分区。MySQL在创建表时使用 PARTITION BY 子句定义每个分区存放的数据。执行查询时,优化器会根据分区定义过滤掉不需要查询的分区,这样就无需扫描...原创 2019-11-17 17:43:33 · 3350 阅读 · 0 评论 -
MySql的备份和恢复
平常备份数据都是用navicat的转储sql文件,速度慢不说,线上维护也不方便。MySql自带的备份命令非常好用,速度极快。备份命令 mysqldump单实例语法mysqldump -u -p > *.sql多实例语法mysqldump -u -p -S > *.sql参数解析-A --all-databases:导出全部数据库-Y --al...原创 2019-10-12 09:17:14 · 4689 阅读 · 0 评论 -
MySQL的锁机制
MySQL服务端是允许多个客户端连接的,服务端允许客户端并发的对数据进行CRUD操作,以提升数据库整体的并发访问性能。但是,为了保证并发访问数据的一致性和完整性,MySQL服务端内部有它特有的锁机制。MySQL支持插件式的存储引擎,不同的存储引擎内部锁实现也大不相同。例如:MyISAM只支持表锁,而InnoDB则支持更细粒度的行锁和间隙锁。1、InnoDB的锁机制InnoDB相较于其他存...原创 2020-04-21 12:58:39 · 3293 阅读 · 1 评论 -
为什么索引可以提升查询速度?
官方定义:索引是帮助存储引擎高效获取数据的数据结构。提取句子主干:索引是数据结构。基于快速查找的数据结构有很多,MySQL采用的是 B+Tree。为什么采用B+Tree而不是其他的呢?B+Tree相比其他数据结构有什么优点呢?其他数据结构了解B+Tree之前,先了解一下其他的数据结构,看看它们有什么问题,再看B+Tree是如何解决这些问题的。二叉树二叉树的特点:任何一个节点,左边的...原创 2019-10-12 08:18:41 · 5809 阅读 · 0 评论 -
MySQL悲观锁与乐观锁方案
悲观锁和乐观锁是用来解决并发问题的两种思想,在不同的平台有着各自的实现。例如在Java中,synchronized就可以认为是悲观锁的实现(不严谨,有锁升级的过程,升级到重量级锁才算),Atomic***原子类可以认为是乐观锁的实现。悲观锁具有强烈的独占和排他特性,在整个处理过程中将数据处于锁定状态,一般是通过系统的互斥量来实现。当其他线程想要获取锁时会被阻塞,直到持有锁的线程释放锁。乐观锁对数据的修改和访问持乐观态度,假设不会发生冲突,只有当数据提交更新时才会对数据冲突与否进行检测,如果没有冲突则原创 2020-11-08 12:37:10 · 217 阅读 · 0 评论 -
MySQL服务器配置优化(读书笔记)
MySQL配置的工作原理配置文件:Windows:my.ini,Linux:my.cnf。对于Linux,配置文件一般是在/etc/my.cnf或/etc/mysql/my.cnf。可以通过命令查看:mysql --verbose --help | grep -A 1 'Default options'Default options are read from the following...原创 2019-11-17 17:44:21 · 3199 阅读 · 0 评论 -
MySQL查询性能优化(读书笔记)
设计优秀的库表结构及索引对于高性能来说必不可少,但这还不够。糟糕的查询往往不能发挥索引的功效,也就达不到所谓的高性能。要想实现高性能,库表结构优化,索引优化,查询优化 需要齐头并进。为什么查询速度会很慢?查询的生命周期大致可以按照顺序来看:从客户端,到服务器,然后在服务器进行解析,生成执行计划,执行,并返回结果给客户端。其中“执行”可以认为是整个周期里最重要的一个阶段,其中包含了大量为...原创 2019-11-17 17:42:47 · 3275 阅读 · 0 评论 -
MySql将查询结果导出文件
线上维护数据库时,有时需要将一些数据导出到文件。例如:客户要求将指定条件的数据查询出来,制成Excel表格发给他们。如果网络方面没有限制,Navicat能直连数据库,直接利用工具导出即可。如果Navicat不能直连,只能通过命令行界面操作数据库,该如何操作呢?MySql将查询结果导出到文件的两种方式方式一into outfile需要进入Mysql终端并选择数据库,然后执行如下命令:...原创 2019-10-13 11:17:22 · 5424 阅读 · 0 评论 -
InnoDB之Undo log写入和恢复
为了实现事务的回滚和MVCC,InnoDB设计了Undo log模块,简单来说就是在修改记录前先记下日志,以便之后能将记录恢复成修改前的样子。针对insert、delete、update这三种不同的操作,InnoDB设计了不同类型的undo log,每个类型的undo log都有自己的格式,里面记录了撤销记录修改所必须的数据,InnoDB可以根据undo log将记录进行恢复。和redo log一样,undo log也是要持久化到磁盘的,来看看InnoDB是如何存储undo log的吧。原创 2022-11-19 15:07:36 · 1179 阅读 · 0 评论 -
InnoDB之Undo log格式
InnoDB有两大日志模块,分别是redo log和undo log。为了避免磁盘随机写,InnoDB设计了redo log,数据写入时只写缓冲页和redo log,脏页由后台线程异步刷盘,哪怕系统崩溃也能根据redo log恢复数据。但是我们漏了一种情况没有考虑,如果事务执行到一半系统崩溃了,redo log没刷盘还好,相当于本次事务的修改全部停留在内存里,重启后相当于什么也没做。原创 2022-11-19 15:05:56 · 1099 阅读 · 0 评论 -
InnoDB之redo log写入和恢复
InnoDB使用Buffer Pool来加速数据读写,提升性能的同时也带来了一些问题,为了避免页面频繁刷盘和磁盘随机写,InnoDB引入了WAL机制,先顺序写少量的redo log,再由后台线程去异步刷脏页,尽可能提升SQL的执行效率。写redo log的好处是:相较于一个完整的页,redo log占用的空间极小,而且它是顺序写的,比随机写的效率更高。根据不同的修改场景,InnoDB设计了几十种不同类型的redo log。这里面包含极其简单的物理日志,只是单纯记录了要把哪个页面的哪些数据做修改;原创 2022-10-29 16:29:48 · 2391 阅读 · 0 评论 -
InnoDB之redo log格式
MySQL有一个组件叫Buffer Pool,它通过内存来缓存磁盘里的数据页,来提升数据读写性能。所有数据的读写首先经过Buffer Pool,并不直接和磁盘打交道。加速读不会有问题,但是加速写可能会存在数据不一致的问题。试想这样一个场景:如果只在Buffer Pool中修改了页面,事务已经提交了,但是Buffer Pool里的脏页还没来得及刷新到磁盘,此时系统崩溃,内存里的数据失效,就会导致刚刚提交的事务数据丢失,这一点是不可接受的,它违背了事务的持久性。原创 2022-10-10 19:50:58 · 843 阅读 · 3 评论 -
InnoDB缓存之Buffer Pool
1. 前言我们已经知道,对于InnoDB存储引擎而言,页是磁盘和内存交互的基本单位。哪怕你要读取一条记录,InnoDB也会将整个索引页加载到内存。哪怕你只改了1个字节的数据,该索引页就是脏页了,整个索引页都要刷新到磁盘。InnoDB是基于磁盘的存储引擎,如果每次操作都去读写磁盘,那么性能将会受到很大的影响。而且绝大多数时候,程序读写的数据在磁盘上并不是连续的,这意味着需要执行大量的随机IO读写,磁盘随机IO读写效率是非常低的,尤其是传统的机械硬盘。在解决这个问题之前,大家可以先想一想,为什么我们只想读取原创 2022-03-20 17:02:27 · 2743 阅读 · 0 评论 -
MySQL执行成本是如何计算的?
1. 前言我们知道,全表扫描适用于任何查询,这是最简单也是最笨拙的一种查询方式,它的缺点是当表中数据量较大时性能就会非常差,因为需要扫描整棵聚簇索引B+树的叶子节点,涉及到大量的磁盘IO和CPU计算。为了解决全表扫描的性能问题,我们可以给条件列加上索引,这样就可以形成一个较小的扫描区间,过滤掉绝大部分的记录,从而提高查询效率。如果过滤条件十分复杂,涉及到多个列,我们还可以给多个列都加上索引,MySQL会判断可能会使用到哪些索引,以及使用这些索引对应的执行成本是多少,最终选择成本最低的索引方案,这就是所谓的原创 2022-03-13 21:23:54 · 2503 阅读 · 0 评论 -
MySQL表连接算法
1. 前言MySQL属于关系型数据库,我们建的表大多也都存在业务上的关联关系,同时我们又不可能将所有的数据都冗余一份,这不符合数据库的设计范式。因此,当我们需要把多张表的数据融合在一起的时候,就需要使用到「多表连接查询」。多表连接查询虽然用的很爽,但是常常会带来性能问题。大家可以回忆一下自己遇到的慢SQL,大多数都是多表联查导致的。有的DBA甚至会要求严格限制连接查询中表的数量,理论上来说,连接表的数量越多,效率越低。表连接最坏的情况,就是「笛卡尔积」,它没有任何限制条件,结果集中包含一张表中所有的记录原创 2022-03-12 12:17:17 · 1710 阅读 · 0 评论 -
MySQL优化之Index Merge
1. 前言先问大家一个问题,在不考虑多表联查这种复杂的查询场景下,一个简单的单表查询,MySQL可以同时利用几个索引?当初我学习MySQL的时候,天真的以为只要把WHERE条件涉及到的列全部加上索引,就可以提升查询速度,这个想法其实大错特错。因为一般情况下,单表查询MySQL只能利用一个索引,比如下面这个查询,假设id是主键,a和b分别创建了索引,别天真的以为idx_a和idx_b都能发挥作用,其实不是的。SELECT id,a,b FROM T WHERE a>100 AND b>200原创 2022-03-06 14:14:13 · 2797 阅读 · 2 评论 -
InnoDB表空间之段的概念
1. 前言通过前面的文章,我们已经知道了索引页的结构可以使得它们不用在物理上连续也能正常工作,只是不连续会导致大量的随机IO性能较差,为了解决这个问题InnoDB引入了区的概念,物理上连续的64个页就是一个区,这样InnoDB就可以以“区”为单位来分配空间了。同时,为了避免少量数据也占用2个区的问题,InnoDB采取先零散页后完整区的分配策略,当某个段使用的零散页数量超过32时,开始以区为单位进行分配。介绍区时我们稍微提到了“段”的概念,但没有细讲,今天的文章我们重点分析。先提几个问题:为什么一棵B原创 2022-03-05 13:04:05 · 576 阅读 · 0 评论 -
InnoDB表空间之区的概念
1. 前言目前为止我们已经知道,「行格式」决定了记录在磁盘中的存储格式,记录通过头信息里的指针串联成单向链表。为了更好的管理记录,InnoDB使用「页」为基本单位来存储记录,页与页之间串联成双向链表。同时,为了提高记录的检索效率,InnoDB借鉴了页中Page Directory的设计,给所有叶子节点页建立目录项记录,存储在内节点中,以此来构建一棵树,也就是我们熟悉的B+树索引。这一切看起来好像都没有问题,InnoDB可以正常工作,但是在性能上会存在一些问题。我们往表中插入记录,本质上是往聚簇索引B+树原创 2022-03-04 22:35:02 · 606 阅读 · 0 评论 -
InnoDB表数据的组织形式:B+树
1. 前言通过「行格式」我们知道了记录在磁盘里的存储格式,除了存储记录的真实数据外,每条记录还会有额外的头信息、变长字段长度列表、NULL值列表等信息。为了更好的管理记录,InnoDB以「页」为基本单位,将一条条记录存储在一个个单独的页中,页内的记录按照主键排序并形成单向链表,页与页之间通过在File Header里记录上一个和下一个页的页号来形成双向链表。一张表的记录往往是很多的,可能上千万甚至上亿条记录。可即便如此,哪怕上亿条记录的表,我们通过主键或索引检索数据时,速度依然很快,InnoDB是怎原创 2022-03-01 20:10:38 · 2011 阅读 · 0 评论 -
MySQL索引页结构
1. 前言「页」是InnoDB管理存储空间的基本单位,也是内存和磁盘交互的基本单位。也就是说,哪怕你需要1字节的数据,InnoDB也会读取整个页的数据,下次读取的数据如果恰巧也在这个页里,就能命中缓存了。写也是一样的,写数据前要先把页加载到内存,然后在内存中修改,该页被记为「脏页」,脏页淘汰之前必须刷盘。InnoDB有很多类型的页,它们的用处也各不相同。比如:有存放undo日志的页、有存放INODE信息的页、有存放Change Buffer信息的页、存放用户记录数据的页等等。今天我们要聊的,就是最基原创 2022-02-26 15:29:58 · 4155 阅读 · 3 评论 -
MySQL行格式
1. 前言MySQL架构分为Server层和存储引擎层,Server层负责接收处理客户端指令,一旦涉及到数据的读取和写入操作,最终是需要调用存储引擎提供的接口来完成的。在MySQL的整个生态里,除Memory外,绝大多数存储引擎都是将数据存储在磁盘上的,例如常用的InnoDB、MyISAM。大家有没有思考过这样一个问题,我们提交Insert语句,MySQL会帮我们把数据保存下来,当我们查询的时候它又能把数据再返回给我们,那它底层是按照什么格式存储数据的呢?我们以一条条记录为单位向表中插入数据,My原创 2022-02-22 22:07:42 · 3385 阅读 · 3 评论