mysql整理
2025-09-07 12:41:15 0 举报AI智能生成
MySQL是一款流行的开源关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，后被甲骨文公司（Oracle Corporation）收购。它使用结构化查询语言（SQL）进行数据库管理和数据访问，是最常用的数据库之一。MySQL支持大型数据库，并以其性能、可靠性、易用性以及对多用户和多线程编程的支持而闻名。它适用于多种操作系统，包括Linux、Windows、OS X等。MySQL广泛应用于互联网相关应用，例如内容管理系统（CMS）、仓库管理、客户关系管理（CRM）系统以及其他各种Web应用程序中。该系统的核心内容包括存储引擎、索引、事务处理和数据库备份功能，这些都是其作为一种强大数据库工具的核心优势。MySQL强调灵活性和速度，因此被众多开发者选中，用以构建稳定且扩展性强的数据库解决方案。
作者其他创作
大纲/内容
分支主题
事务
事务的属性
原子性（Atomicity）
事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行
一致性（Consistent）
在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规
则都必须应用于事务的修改,以保持数据的完整性
在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规
则都必须应用于事务的修改,以保持数据的完整性
隔离性（(Isolation)）
各个事务之间互相隔离，互不干扰
各个事务之间互相隔离，互不干扰
持久性（Durable）
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持
并发访问带来的问题
更新丢失(Lost Update)或脏写
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存
在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存
在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。
脏读（Dirty Reads）
读取到其他事务未提交的数据
读取到其他事务未提交的数据
不可重读（Non-Repeatable Reads）
多次读取，相同查询语句在不同时刻读出的结果不一致，读取到其他事务对数据的修改
多次读取，相同查询语句在不同时刻读出的结果不一致，读取到其他事务对数据的修改
幻读（Phantom Reads）
读取到其他事务新增的数据
读取到其他事务新增的数据
隔离级别
repeatable read：出现幻读的情况
当前事务可能会更新到其他事务新增的数据，会使得相关更新数据从快照读变为当前读，从而出现幻读。
read uncommit (读未提交)
read commit (读已提交)
mvcc(事务中每次对表的查询都会创建一个新的readview，当前读)
repeatable read (可重复读)
mvcc(事务中同一表多次查询共用一个readview，快照读) + 间歇锁
serializable (串行)
串行化（serializable）底层原理
mysql会在执行查询时，底层默认为查询语句添加共享读锁。
select xxxx from xxx lock in share mode
mysql会在执行查询时，底层默认为查询语句添加共享读锁。
select xxxx from xxx lock in share mode
repeatable read：出现幻读的情况
当前事务可能会更新到其他事务新增的数据，会使得相关更新数据从快照读变为当前读，从而出现幻读。
隔离级别的设置
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别。
RC与RR怎么选择
RR:同一时间维度，性能相对较低，多个查询操作需要放到一个事务当中
RC:能够体现数据的及时变化，并发性能更高，没有事务要求。
如果对性能要求较高，可以采用RC级别，从业务和实现上保证不可重复读带来的影响。
RC:能够体现数据的及时变化，并发性能更高，没有事务要求。
如果对性能要求较高，可以采用RC级别，从业务和实现上保证不可重复读带来的影响。
大事务的问题
- 大事务的影响并发情况下，数据库连接池容易被撑爆
- 锁定太多的数据，造成大量的阻塞和锁超时
- 执行时间长，容易造成主从
- 延迟回滚所需要的时间比较长
- undo log膨胀
- 容易导致死锁
- 锁定太多的数据，造成大量的阻塞和锁超时
- 执行时间长，容易造成主从
- 延迟回滚所需要的时间比较长
- undo log膨胀
- 容易导致死锁
事务怎么优化
- 将查询等数据准备操作放到事务外
- 事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
- 事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
- 更新等涉及加锁的操作尽可能放在事务靠后的位置（放太前加长锁阻塞的时间）
- 能异步处理的尽量异步处理
- 应用侧(业务代码)保证数据一致性，非事务执行
- 事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
- 事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
- 更新等涉及加锁的操作尽可能放在事务靠后的位置（放太前加长锁阻塞的时间）
- 能异步处理的尽量异步处理
- 应用侧(业务代码)保证数据一致性，非事务执行
mysql中事务开启的时机
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作或加排它锁操作(比如select...for update)的语句，事务才真正启动，才会向mysql申请真正的事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。
锁机制
锁分类
操作类型
读锁（共享锁，S锁(Shared)）
针对同一份数据，多个读操作可以同时进行而不会互相影响
select * from T where id=1 lock in share mode
select * from T where id=1 lock in share mode
写锁（排它锁，X锁(eXclusive)）
当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都会加写锁，查询也可以通过for update加写锁。
select * from T where id=1 for update
select * from T where id=1 for update
意向锁（Intention Lock）：又称I锁，针对表锁
主要是为了提高加表锁的效率，当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。
意向锁主要分为：
意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。
意向锁主要分为：
意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。
粒度
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。
页锁
只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中
可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的
数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的
数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。
该索引不能失效，否则RR级别会从行锁升级为表锁，这里的表锁并非真正的表锁，而是对所有扫描的数据加锁。
PS：关于RR级别行锁升级为表锁的原因分析
因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过
的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所
以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表
锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录
InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。
该索引不能失效，否则RR级别会从行锁升级为表锁，这里的表锁并非真正的表锁，而是对所有扫描的数据加锁。
PS：关于RR级别行锁升级为表锁的原因分析
因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过
的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所
以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表
锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录
间隙锁
间隙锁，锁的就是两个值之间的空隙（a,b）开区间，间隙锁是在可重复读隔离级别下才会生效
临键锁
Next-Key Locks是行锁与间隙锁的组合（a,b] 左开右闭，锁定间隙和 = a的所有记录
性能
乐观锁
乐观锁适合读操作较多的场景，如果在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能
悲观锁
悲观锁适合写操作较多的场景
锁的退化机制
innodb 默认为临键锁
唯一索引，主键索引
假设有id 1 3 4 5 10 组成间隙（-无穷,1),(1,3),(5,10),(10,+无穷)，连续的记录值也可以当成间隙来处理如:(3,4],(4,5]
等值查询：id = 3 记录存在，记录锁；id = 2 不存在，间隙锁(1,3)
范围查询：含等值且记录存在，记录锁 + 临键锁，不存在，间隙锁 + 临键锁；<=,记录存在，临键锁，不存在，间隙锁 + 临键锁。
等值查询：id = 3 记录存在，记录锁；id = 2 不存在，间隙锁(1,3)
范围查询：含等值且记录存在，记录锁 + 临键锁，不存在，间隙锁 + 临键锁；<=,记录存在，临键锁，不存在，间隙锁 + 临键锁。
一般索引
等值查询，记录存在，记录锁 + 前后间隙锁，不存在，间隙锁
范围查询，范围查询，不管记录存不存在，边界都是临键锁，会锁定，前后 （包括）+ 中间的所有区间
比如：a取值：5 10 15 20
select * from lock_test where a>=10 and a<=15 FOR UPDATE ; 锁定范围：[5,10],(10,15],(15,20]
范围查询，范围查询，不管记录存不存在，边界都是临键锁，会锁定，前后 （包括）+ 中间的所有区间
比如：a取值：5 10 15 20
select * from lock_test where a>=10 and a<=15 FOR UPDATE ; 锁定范围：[5,10],(10,15],(15,20]
联合索引
只有唯一联合索引，查询包含所有列且数据存在才会退化成记录锁。
跳跃查询：会升级成表锁
其他情况：按照最左匹配原则，尽可能的利用最长前缀减少锁定范围。规则可以将最长可利用前缀看成单一非唯一索引进行处理。
跳跃查询：会升级成表锁
其他情况：按照最左匹配原则，尽可能的利用最长前缀减少锁定范围。规则可以将最长可利用前缀看成单一非唯一索引进行处理。
锁等待分析
锁统计信息
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划
系统库锁相关表
查看INFORMATION_SCHEMA系统库锁相关数据表
死锁分析
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉
InnoDB与MyISAM区别
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会
自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
mvcc机制
Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日
志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链(见下图，需参考视频里的
例子理解)。
在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前永
远都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成read-view)，这个视图由执行查询时所有未提交
事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版
本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
版本链比对规则：
1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；
2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的
trx_id 就是当前自己的事务是可见的）；
3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的
事务是可见的)；
b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。
对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的
trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，
在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日
志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链(见下图，需参考视频里的
例子理解)。
在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前永
远都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成read-view)，这个视图由执行查询时所有未提交
事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版
本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
版本链比对规则：
1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；
2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的
trx_id 就是当前自己的事务是可见的）；
3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的
事务是可见的)；
b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。
对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的
trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，
在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。
索引结构
mysql中的索引结构类型
1. B+树
2. Hash
1. B+树
2. Hash
B+树
innodb
- 联合索引数据结构
- 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接，提高区间访问的性能
- 叶子节点包含所有索引字段
- 叶子节点用指针连接，提高区间访问的性能
MyISAM
0x07、0x56等是数据的储存地址，注意图示中的箭头应该是双向的
hash表
- 对索引的key进行一次hash计算就可以定位出数据存储的位置
- 很多时候Hash索引要比B+ 树索引更高效
- 仅能满足 “=”，“IN”，不支持范围查询
- hash冲突问题
- 很多时候Hash索引要比B+ 树索引更高效
- 仅能满足 “=”，“IN”，不支持范围查询
- hash冲突问题
各种引擎的支持情况
- MyISAM引擎：仅支持B-Tree索引结构
- innodb : 支持B-Tree 和 HASH，但时HASH通过自适应哈希索引(Adaptive Hash Index)机制实现，无法手动创建或控制。
- Memory引擎：默认使用哈希索引
- MyISAM引擎：仅支持B-Tree索引结构
- innodb : 支持B-Tree 和 HASH，但时HASH通过自适应哈希索引(Adaptive Hash Index)机制实现，无法手动创建或控制。
- Memory引擎：默认使用哈希索引
mysql的索引为什么选择B+树
二叉树
深度过深，插入数据有序存在斜树的可能，查询效率链表话
B树
非叶子节点存储数据，叶子节点不存在双向指针，范围查询效率低，相同数据量，深度更深，查询需要更多的磁盘io
红黑树
存储相同量数据，深度更深，且深度无法预测
B+树
1.高度可控
按照主键bingint(8 byte)，子节点指针（6 byte），一页16 k计算，一页大概可以存储1170个索引，叶子节点数据每条按照1k计算，一页大概可以存储16条记录。
一个三层的B+树可以存储：1170 x 1170 x 16 = 2190w
2.非叶子节点只存储索引，可以放更多的索引，减少磁盘io
3.叶子节点用指针连接，提高区间访问的性能
按照主键bingint(8 byte)，子节点指针（6 byte），一页16 k计算，一页大概可以存储1170个索引，叶子节点数据每条按照1k计算，一页大概可以存储16条记录。
一个三层的B+树可以存储：1170 x 1170 x 16 = 2190w
2.非叶子节点只存储索引，可以放更多的索引，减少磁盘io
3.叶子节点用指针连接，提高区间访问的性能
数据库优化
server配置优化
server
- max_connections=3000
连接的创建和销毁都需要系统资源，比如内存、文件句柄，sort buffer、join buffer等，一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。
- max_user_connections=2980
允许普通用户连接的最大数量，保留20个连接（3000-2980）专供DBA管理操作，（拥有super权限的账号）
- back_log=300
当普通用户连接数达到2980时，新连接请求会被拒绝或进入等待队列（取决于back_log设置），如果等待连接的数量超过back_log，将被拒
绝
- wait_timeout=300
指的是客户端应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
- interactive_timeout=300
指的是mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
区别：
- interactive_timeout：仅对交互式连接生效（如MySQL命令行客户端、GUI工具等）12
- wait_timeout：仅对非交互式连接生效（如JDBC、PHP PDO等程序化连接）
- sort_buffer_size=4M
sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer
的时候，一次性分配设置的内存
- join_buffer_size=4M
用于表关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
- table_open_cache = 4096
作用于：表实例（TABLE），已打开表的文件描述符和结构信息，占用文件描述符（受 open_files_limit 限制）
- table_definition_cache = 4096
作用于：表定义，.frm 文件解析后的元数据（字段、索引等）
连接的创建和销毁都需要系统资源，比如内存、文件句柄，sort buffer、join buffer等，一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。
- max_user_connections=2980
允许普通用户连接的最大数量，保留20个连接（3000-2980）专供DBA管理操作，（拥有super权限的账号）
- back_log=300
当普通用户连接数达到2980时，新连接请求会被拒绝或进入等待队列（取决于back_log设置），如果等待连接的数量超过back_log，将被拒
绝
- wait_timeout=300
指的是客户端应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
- interactive_timeout=300
指的是mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
区别：
- interactive_timeout：仅对交互式连接生效（如MySQL命令行客户端、GUI工具等）12
- wait_timeout：仅对非交互式连接生效（如JDBC、PHP PDO等程序化连接）
- sort_buffer_size=4M
sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer
的时候，一次性分配设置的内存
- join_buffer_size=4M
用于表关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
- table_open_cache = 4096
作用于：表实例（TABLE），已打开表的文件描述符和结构信息，占用文件描述符（受 open_files_limit 限制）
- table_definition_cache = 4096
作用于：表定义，.frm 文件解析后的元数据（字段、索引等）
innodb
- innodb_thread_concurrency=64
限制进入InnoDB层的线程数，建议设置为CPU核心数的2倍
- innodb_buffer_pool_size=40G
innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。内存大小直接反应数据库的性能
- innodb_buffer_pool_instances=1
将缓冲池分区以减少锁争用，每1GB缓冲池对应1个实例，最大64个
- innodb_buffer_pool_chunk_size=128
动态调整时的增量大小，默认为128MB
- innodb_lock_wait_timeout=10
行锁锁定时间，默认50s，根据公司业务定，没有标准值
- innodb_deadlock_detect=on
死锁检测，高并发时可考虑禁用
- innodb_flush_log_at_trx_commit=1
刷盘策略：0|1|2
- innodb_log_buffer_size
事务日志缓冲区，建议16MB-64MB(大事务可适当增加)
- innodb_log_file_size
单个redo日志文件大小，建议1GB-4GB，总日志大小应能容纳。
- innodb_file_per_table
启用独立表空间，每个InnoDB表使用单独的.ibd文件
优势：
- 便于单表维护和备份
- 可回收删除表后的磁盘空间
- 避免系统表空间膨胀
- 建议：生产环境强烈建议开启
限制进入InnoDB层的线程数，建议设置为CPU核心数的2倍
- innodb_buffer_pool_size=40G
innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。内存大小直接反应数据库的性能
- innodb_buffer_pool_instances=1
将缓冲池分区以减少锁争用，每1GB缓冲池对应1个实例，最大64个
- innodb_buffer_pool_chunk_size=128
动态调整时的增量大小，默认为128MB
- innodb_lock_wait_timeout=10
行锁锁定时间，默认50s，根据公司业务定，没有标准值
- innodb_deadlock_detect=on
死锁检测，高并发时可考虑禁用
- innodb_flush_log_at_trx_commit=1
刷盘策略：0|1|2
- innodb_log_buffer_size
事务日志缓冲区，建议16MB-64MB(大事务可适当增加)
- innodb_log_file_size
单个redo日志文件大小，建议1GB-4GB，总日志大小应能容纳。
- innodb_file_per_table
启用独立表空间，每个InnoDB表使用单独的.ibd文件
优势：
- 便于单表维护和备份
- 可回收删除表后的磁盘空间
- 避免系统表空间膨胀
- 建议：生产环境强烈建议开启
sql优化
索引优化
mysql8新特性
with tmp as( sql ) 语句
8.0以下不支持
8.0以下不支持
窗口函数
<窗口函数>(参数) OVER(
[PARTITION BY 分组列]
[ORDER BY 排序列]
[ROWS/RANGE 窗口范围]
)
select name,channel,balance,row_number() over(order by balance) as row_number1 from account_channel
select name,channel,balance,first_value(balance) over(order by balance) as
first1 from account_channel
<窗口函数>(参数) OVER(
[PARTITION BY 分组列]
[ORDER BY 排序列]
[ROWS/RANGE 窗口范围]
)
select name,channel,balance,row_number() over(order by balance) as row_number1 from account_channel
select name,channel,balance,first_value(balance) over(order by balance) as
first1 from account_channel
隐藏索引
索引可以在invisible和visible之间转换，invisible的索引mysql在查询中不会使用，但是依然保持对其的维护。
使用场景：线上验证、测试
ALTER table t2 ALTER index idx_c1 invisible;
ALTER table t2 ALTER index idx_c1 visible;
索引可以在invisible和visible之间转换，invisible的索引mysql在查询中不会使用，但是依然保持对其的维护。
使用场景：线上验证、测试
ALTER table t2 ALTER index idx_c1 invisible;
ALTER table t2 ALTER index idx_c1 visible;
函数索引
create index func_idx on t3((UPPER(c2)));
select * from t3 where UPPER(c2) = 'LCY'
create index func_idx on t3((UPPER(c2)));
select * from t3 where UPPER(c2) = 'LCY'
降序索引
默认索引是按照升序形成的B+树，联合索引查询时，多个子索引遍历B+树的方向一致才能使用索引。
默认索引是按照升序形成的B+树，联合索引查询时，多个子索引遍历B+树的方向一致才能使用索引。
group by 不再隐式排序
mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句
mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句
索引跳跃
0 条评论
下一页
