MySQL

参考文献 千金良方: MySQL性能优化金字塔法则 InnoDB后台线程 123456789101112131415161718192021222324252627282930313233343536373839404142434445mysql> select name,type,thread_id,processlist_id from performance_schema.threads;+---------------------------------------------+------------+-----------+----------------+| name | type | thread_id | processlist_id |+---------------------------------------------+------------+-----------+----------------+| thread/sql/main ...

参考文献 MySQL技术内幕 InnoDB存储引擎 InnoDB存储引擎的关键特性 插入缓冲(Insert Buffer) 两次写(Double Write) 自适应哈希索引(Adaptive Hash Index) 异步IO(Async IO) 刷新邻接页(Flush Neighbor Page) Insert Buffer 虽然InnoDB缓冲池中也有Insert Buffer 信息,但是Insert Buffer 和数据页一样,也是物理页的一个组成部分. 在InnoDB存储引擎中,主键是行唯一的标识符.通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的.因此,插入聚集索引(Primary Key)一般是顺序的,不需要磁盘的随机读取. TIPS: 并不是所有的主键插入都是顺序的,若主键类是UUID这样的,那么插入和辅助索引一样,同样是随机的.即使主键是自增类型,但是插入的是指定的值,而不是NULL值,那么同样可能导致插入并非连续的情况. B+树的特性决定了非聚集索引插入的离散性 解释: B+树的叶节点是有序的。当它用于聚集索引的时候，叶 ...

参考文献 MySQL技术内幕 InnoDB存储引擎 Checkpoint技术 缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟.因此页的操作首先都是在缓冲池中完成的.如果一条DML语句,如UPDATE或DELETE改变了页中的记录,那么此时页是脏的,即缓冲池中页的版本要比磁盘的新.数据库需要将新版本的页从缓冲池刷新到磁盘. 若每次一个页发生变化,就将新页的版本刷新到磁盘,那么这个开销是非常大的.若热点数据集中在某几个页中,那么数据库的性能将变得非常差.同时,如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机,那么数据就不能恢复了. 为了避免发送数据丢失的问题,当前事务数据库系统都普遍采用了Write Ahead Log策略,即当事务提交时,先写重做日志,再修改页.当由于发生宕机而导致数据丢失时,通过重做日志来完成数据的恢复. Checkpoint(检查点)技术的目的是解决以下几个问题: 缩短数据库的恢复时间; 缓冲池不够用时,将脏页刷新到磁盘; 重做日志不可用时,刷新脏页; 当数据库发生宕机时,数据库不需要重做所有日志,因为Checkpoint之前的页都已经 ...

参考文献 MySQL技术内幕 InnoDB存储引擎 https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html 数据库和数据库实例 从概率上说,数据库是文件的集合,是依照某种数据模型组织起来并存放与二级存储其中的数据集合; 数据库实例是程序,是位于用户与操作系统之间的一层数据管理软件,用户对数据库数据的任何操作,包括数据库定义,数据查询,数据维护,数据库运行控制等都是在数据库实例下进行的,应用程序只有通过数据库实例才能和数据库打交道. MySQL版本与InnoDB版本对照表 MySQL版本 InnoDB版本 版本开始时间 说明 1.0.x 1996 3.11.1 1996.10 MySQL没有2.x版本 4.0.x 2003 5.1.x 1.0.x版本(官方称为InnoDB Plugin) 5.5.x 1.1.x版本 2010 使用InnoDB作为默认引擎 5.6.x 1.2.x版本 8.0.x 8.0.x 2016 InnoDB体系架构 上 ...

参考文献 手把手教你解数据库死锁 死锁日志 前置知识 supremum记录 supremum这条记录是啥？ 我们可以简单理解为是数据页中的一条“伪记录”.mysql的数据页中,不管有多少自己的记录,始终会存在两条虚拟的记录,也就是伪记录,分别是“Infimum”（最小记录）和“Supremum”（最大记录）.要知道,一个数据页中多条记录存储类似于链表,Infimum->1->2->3->…->Supremum,这种. 通过SHOW ENGINE INNODB STATUS查看 日志解析 日志来自于MySQL(二十二)-死锁分析的唯一键死锁 (Delete + Insert)案例 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950------------------------LATEST DETECTED DEADLOCK------------------------2022-04-05 16:11: ...

参考文献 解决死锁之路 - 常见 SQL 语句的加锁分析 何登成–管中窥豹——MySQL(InnoDB)死锁分析之道 MySQL · 引擎特性 · InnoDB隐式锁功能解析 Locks Set by Different SQL Statements in InnoDB MySQL技术内幕 InnoDB存储引擎 27.12.13.1 The data_locks Table MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁 一张图彻底搞懂 MySQL 的锁机制 mysql事务和锁 SELECT FOR UPDATE MySQL 45 讲 MySQL 加锁规则！ 加锁的目的 数据库中的锁: 确保并发更新场景下的数据正确性. ACID中的I(Isolation) 锁要作的就是达到事务隔离的目的,即: 两个并发执行的事务T1和T2,如果T1正在修改某些行,那么,T2要并发 读取/修改/插入 满足T1查询条件的行时,T2就必须被阻塞,这是锁存在的根本原因. 加什么样的锁的判断因素 当前事务的隔离级别 SQL是一致性非锁定读(consistent nonlocking r ...

参考文献 何登成–管中窥豹——MySQL(InnoDB)死锁分析之道 手把手教你解数据库死锁 Mysql并发时经典常见的死锁原因,Mysql死锁问题分析及解决方法 并发insert死锁验证 死锁和死锁检测 当并发系统在不同现场出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待状态,称为死锁. 死锁出现的条件 多个并发事务(两个或者两个以上的事务); 每个事务都持有锁(或者是已经在等待锁); 每个事务都需要再继续持有锁(为了完成事务逻辑,还必须更新更多的行); 事务之间产生加锁的循环等待,形成死锁. 若A事务需要B的资源,B事务需要A的资源,这就是典型的AB-BA死锁. 死锁的危害 死锁,即表明有多个事务之间需要互相争夺资源而互相等待。 如果没有死锁检测,那么就会互相卡死,一直耗死 如果有死锁检测机制,那么数据库会自动根据代价来评估出哪些事务可以被回滚掉,用来打破这个僵局 所以说:死锁并没有啥坏处,反而可以保护数据库和应用 那么出现死锁,而且非常频繁,我们应该调整业务逻辑,让其避免产生死锁方为上策 MySQL出现死 ...

参考文献 MySQL锁系列(一)之锁的种类和概念 15.7.1 InnoDB Locking MySQL · 引擎特性 · InnoDB隐式锁功能解析 MySQL 中锁的各种模式与类型 MySQL技术内幕 InnoDB存储引擎 15.6.1.6 AUTO_INCREMENT Handling in InnoDB 锁的种类 在数据库中,通常使用锁机制来协调多个线程并发访问某一资源.MySQL的锁类型分为表锁和行锁,表示对整张表加锁,主要用在DDL场景中,也可以由用户指定,主要由server层负责管理； 而行锁指的是锁定某一行或几行,或者是行与行之间的间隙,行锁由存储引擎管理,例如最常使用的InnoDB. 表锁占用系统资源小,实现简单,但锁定粒度大,发生锁冲突概率高,并发度比较低.行锁占用系统资源大,锁定粒度小,发生锁冲突概率低,并发度比较高. InnoDB将锁分为**锁模式(lock_mode)和锁类型(lock_type)**两类.锁模式通常和锁类型结合使用. 锁类型描述了锁的粒度,也就是把锁具体加到什么地方. 锁类型包括表锁和行锁,而行锁还细分为记录锁、间隙锁、插 ...

参考文献 高性能MySQL(第三版) MySQL是怎样运行的 字符集和比较规则 字符集是指一种二进制编码到某类字符符号的映射. 比较规则是指一组用于某个字符集的排序规则. MySQL有很多的选项用于控制字符集.这些选项和字符集很容易混淆,一定要记住:只有基于字符的值才真正的"有"字符集的概念.对于其他类型的值,字符集只是一个设置,指定用哪一种字符集来做比较或者其他操作.基于字符的值能存放在某列中,查询的字符串中,表达式的计算结果中或者某个用户变量中,等等. MySQL的设置可以分为两类: 创建对象时的默认值; 在服务器和客户端通信时的设置; 一些重要的字符集 ASCII字符集:共收录128个字符集,包括空格,标点符号,数字,大小写字母和一些不可见字符. ISO 8859-1字符集: 共收录256个字符,它在ASCII字符集的基础上又扩充了128个西欧常用字符.ISO 8859-1字符集也可以使用一个字节来进行编码(这个字符集也有一个别名Latin1) GB2312字符集: 这个字符集同时又兼容ASCII字符集,所以在编码方式上显得有些奇怪: 如果该字 ...

参考文献 高性能MySQL(第三版) 分区 MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询就无需扫描所有分区,只需要查找包含需要数据的分区就可以了. 在下面的场景中,分区可以起到非常大的作用: 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据. 分区表的数据更容易维护.例如,想批量删除大量数据可以使用清除整个分区的方式,另外,还可以对一个独立分区进行优化,检查,修复等操作. 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备. 可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3文件系统的inode锁竞争等. 如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好. 分区表本身也有一些限制 一个表最多只能有1024分区. 在MySQL5.1中,分区表达式必须是整数,或者是返回整数的表达式.在MySQL5.5中,某些场景中可以直接是用列来进行分区. 如果分区字段中有主 ...