MYSQL篇——性能调优专题_mysql性能优化 毕业设计

MYSQL是业界最常使用的数据库，本文以5.7为主，从数据结构到性能调优阐述，深入理解mysql，但是本文只是九牛一毛，若对MYSQL感兴趣建议阅读源码、官方文档。

---

---

数据存在数据库中时，是离散的存储，数据表中相邻的字段存储位置并不相邻，因此在顺序查找字段时需要一个一个查找，这是很大的io消耗。因此需要降低io引入数据结构，二叉树，红黑树，b树，哈希表等。

---

MYSQL调优篇

深入理解Mysql索引底层数据结构与算法

• 不选择二叉树的原因：若1,2,3顺序存储会变成单链表，并不会降低io次数。
• 不选择红黑树的原因：红黑树是二叉平衡树，但在数据量巨大时，树的高度是不可控的（受限于2叉树的特点）
• B树非叶子节点有数据，B+树只有叶子节点有数据。一个节点数据页大小（innodb_page_size）16k,故B+树一个节点可以存16kb/(bigint8b+地址6b) 约=1170个点。3层最多存1170 * 1170 * 16=2000万个数据。而同类的B树只有16 * 16 * 16= 4096个数据。

索引是排好序的数据结构

B+树与B树：

• B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
• B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。(标准的b+树是单箭头，mysql优化的b+树是双箭头)
• B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

INNODB和MYISAM的区别

• 事务：
  innodb支持回滚、支持事务、外键、行锁，多版本事务管理mvcc。
  myisam不支持事务、不支持外键、表级锁。
• 存储结构上：
  innodb 2个文件.ifm（表结构） 、.ibd(数据和索引)
  myisam 3个文件 .ifm（表结构） 、.myd(数据文件)、.myi（索引文件)
• 存储空间上：
  innodb 占用空间大，需要更多的内存和存储，他需要有缓存池用于高速缓存数据和索引。
  myisam 占用空间小，可被压缩，数据以文件的方式存储。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
• 索引
  myisam支持全文索引fulltext，索引保存的是行地址。
  innodb可以使用sphinx插件支持全文索引，主键索引保存的是数据，其他索引保存的是主键索引地址。
• 缓存：
  myisam仅仅缓存索引，不缓存数据（myisam数据文件和索引文件分开）
  innodb缓存索引和真实数据，所以，对内存有更高的要求。
• 性能：
  select：myisam性能高
  update、insert：innodb性能高
  清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

• 为什么MyISAM会比Innodb 的查询速度快？
  INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多； 1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少； 2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快 3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护
• 如何选择？
  如果执行大量的SELECT，MyISAM是更好的选择。
  如果你的数据执行大量的INSERT或UPDATE ，出于性能方面的考虑，应该使用InnoDB表。

• 非聚集索引：索引文件和数据文件分离
• 稀疏索引也是一种非聚集索引
• Hash索引：可能io较低，但是不支持范围查询（此时全表查询）

InnoDB一般建议设立主键且推荐为自增主键（整型占空间小且易于比大小+==== ），若无逐渐，则筛选各不相同的列维护，若无，则加一隐藏列维护。

Explain详解与索引最佳实践

ID:数字越大执行的优先级越高
SELECT_TYPE:查询类型

• PRIMARY(复杂查询) DERIVED(衍生查询，from里的) SUBQUERY(子查询)

TYPE:访问类型，mysql掘洞如何在表中查找行，查找行的大致范围

• system(查询表只有一行记录)>const(常量查询)>eq_ref(主键关联查询)>ref(最好,普通索引或唯一索引的部分前缀)>range(至少优化到,范围查找)>index(二级索引)>ALL(全表扫描，扫描聚簇索引的全部叶子节点)

EXTRA:

• Using index(覆盖索引不是索引是查找方式)：不需要回表，查询数据在联合索引里存在
• Using where：使用where语句查询结果，并且查询列未被索引覆盖
• Using index condition（索引排序）：查询的列不完全被索引覆盖，where条件中是前导列的范围
  -Using filesort（文件排序）
  不要在索引上使用任何操作（计算，函数，类型转换），会导致索引失效而转为全表扫描

Mysql索引优化实战一

综合例子：
1.联合索引第一个字段用范围不走索引（mysql内部认为可能太多回表不如全表扫描）
2.强制走索引（使用force index(索引)强制走）

• 扫描行数减少但效率不一定提高了，只是决定速度的因素之一

3.使用in或or时，不一定走索引，mysql内部优化器会根据检索比例、表大小等因素自动优化
4.但是对于like，mysql不管表大表小都会走索引

• 索引下推：最左前缀匹配完成后，再向后推断字段，减少回表数据量。（like ‘zhangsan%’）

开启trace，查看mysql如何选择索引

常用的sql优化
1.Order by与Group by优化

in在数据量过大的时候可以走索引，但是order by永远不可能走索引。
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、order by满足两种情况会使用Using index。

1. order by语句使用索引最左前列。
2. 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立(索引创建的顺序）时的最左前缀法则。4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解
filesort文件排序方式
MYSQL通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断用哪种排序方法。小于单路，大于双路。

• 单路排序:是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序;用trace工具可以看到sort_mode信息里显sort_key, additional_fields >或者<sort_key, packed_additional_fields >
• 双路排序(又叫回表排序模式)︰是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段;用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

索引设计原则

1、代码先行，索引后上
不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立
索引。
2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原
则。
3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
4、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。
此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来
完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
5、where与order by冲突时优先where
在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
6、基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
关于慢sql查询不清楚的可以参考这篇文章：

• 链接：http://note.youdao.com/noteshare?id=c71f1e66b7f91dab989a9d3a7c8ceb8e&sub=0B91DF863FB846AA9A1CDDF431402C7B

• 思考业务场景再建立索引

Mysql索引优化实战二

select * from employees limit 90000,5
——走全表查询，为了避免回表操作，走主键索引全表查询
select * from employees where id>90000 limit 5

后者比前者有更高的效率，没必要查出来全部的数据
限制条件：自增主键，且连续不中断

join优化：
NLJ(nested-loop join)算法 循环嵌套连接算法
BNL(Block Nested-loop join) 基于块的循环嵌套连接算法
join-buffer默认为256k，如果超出，则会分段放。
驱动表：数据量相对较少的表，优化器优先执行驱动表
被驱动表：数据量相对大的表（小表驱动大表+大表关键字段加索引）
straight_join:类似于join，但是能让左边的表来驱动右边的表，能改表对于联合查询的执行顺序。（只适用于inner join）
遵从设计规范：最多关联3张表
大表小表的区分以实际过滤后join的表数据量大小为主

IN和EXSITS优化：（原则：小表驱动大表）
in：当b表数据集小于a表时，in优于exists ——先做子查询
exists：当a表数据集小于b表时，exists优于in ——先做外部查询
exists有时又也可以使用join代替

select count(1) from employee
select count(id) from employee
select count(name) from employee
select count(* ) from employee
实际sql执行查询效率差不多。
字段有索引：count()=count(1)>count(字段)>count(主键id)
count(字段)走二级缩影，二级索引存储数据比主键索引还少，所以count(字段)>count(主键id)
字段无索引：count()=count(1)>count(主键id)>count(字段)
count（ * ）是例外，mysql并不会将所有字段都取出来，而是专门做了优化，不取值，按行累加，效率最高，所以不需要用count(name)*

常用优化方法：
1.查询mysql自己维护的总行数
2.show table status （可能不准确）
3.将总数维护到redis里面去——不能100%绝对一致，双写一致消耗过大
4.增加数据库计数表(数据准确)在同一个事务内

阿里巴巴mysql数据库规范：
单表行数超过500万行或者单表容量超过2G，此时推荐分库分表。
业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
页面搜索严禁使用%xx左模糊查询，如果需要可以使用es来实现
禁止使用存储过程
in操作能避免则避免，太少太多都不一定走索引，最好在1000个以内。

• TINYINT(2)里面的2指的是显示宽度，比如查询结果是5，那末输出就是05，要是TINYINT(5)的话现实就是00005，其实存储的值还是5.
• DECIMAL比float等浮点精度更好使用
• 大公司使用DATETIME，小公司使用TIMESTAMP,因为timestamp占用空间小，而且只能表示1970-2038年的时间按数据。
• varchar(x)中x表示最大列长度，超出就会报错，不足时不会补充空格，char会补充空格。

Mysql事务原理与优化最佳实践

A（atomicity原子性——undlog）
C（consistency一致性——AID+代码逻辑正确）
I（isolation隔离性——MYSQL锁+MVCC）
D（durability持久性——redolog）

• read uncommit（读未提交）：脏读-读到了其他事务修改但未提交的修改
• read commit （读已提交）ORACLE：不可重复读-读取的是实时的表内数据(当前事务被其他事务影响，在当前事务中读到数值被其他提交了的事务修改了)修改不提交会有行锁
• repeated read（可重复读）MYSQL：
  幻读-事务A读取到了事务B新增的数据 ，对于其他事务新增的数据，查询时是查不到的，但当对未知数据做修改后，查询时可以查到此数据，并加行锁；
  （所以可重复读并未完全解决幻读问题） ，需再加间隙锁
  读取的是开启事务时刻的表内容-快照读；一旦对于数据做了update修改，修改了的数据查询就会使用当前读，但此时读取未修改的数据时，还是快照读（java做运算修改就会脏写，需要使用数据库更新update b=b+x）且此时修改里的数据就会加行锁，不能被其他事务所修改，保证了数据的安全
• serializable（串行）：安全，事务A读写了数据时，事务B查询就会死锁；如何解决幻读——串行读写（读锁-共享锁，写锁-排他锁）
• 脏写：在可重复读中使用行锁（悲观锁）来解决；在读已提交中使用了版本号(乐观锁)CAS解决
  MVCC: 可以做到读写不阻塞，且避免了类似脏读这样的问题，主要通过undo日志链实现
  select——快照读 insert.update.delete是当前读
  提问：可重复读快照的生成是开启事务的时刻还是第一次查询的时刻？？？（已解决：begin/start transacion命令并不是一个事务的起点）
  。。。在RR级别时，select需要加事务
  磁盘顺序写：先更新redolog再更新到ibd磁盘文件中（性能原因），redolog是提前在磁盘中开辟出一片区域进行顺序写操作，但是ibd可能十张表落在磁盘上不同的区域。（固态银盘随机写与顺序写的性能差异并不大）

大事务的影响：
1.并发情况下，数据库连接池容易被撑爆锁定太多的数据（一个事务占一个连接）
2.造成大量的阻塞和锁超时（行锁等）
3.执行时间长，容易造成主从延迟
4.回滚所需要的时间比较长
5.undo log膨胀
6.容易导致死锁

事务优化实践原则：
1.将查询等数据准备操作放到事务外
2.事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久
3.事务中避免一次性处理太多数据，可以拆分成多个事务分次处理
4.更新等涉及加锁的操作尽可能放在事务靠后的位置（先insert后update）
5.能异步处理的尽量异步处理
6.应用侧(业务代码)保证数据一致性，非事务执行

Mysql锁机制与优化实践以及MVCC底层原理剖析

锁分类：
1.从性能上分为乐观锁(用版本对比或CAS机制)和悲观锁，乐观锁适合读操作较多的场景，悲观锁适合写操作较多的场景，如果在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能
2.从对数据操作的粒度分，分为表锁(开销小，加锁快，并发低)、页锁(BDB支持，不常见)、行锁(开销大，加锁慢，并发高)
3. 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)，还有意向锁（加行锁时对表做标记，避免逐个判断行锁与表锁冲突；分为IS和IX锁）

INNODB和MYISAM最大的不同点(MYSQL默认InnoDB)
INNODB支持事务；INNODB支持行锁
注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁)
PS:关于RR级别行锁升级为表锁的原因分析
因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题)或间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上。

其他锁：
间隙锁（锁的是(x,y) x和y之间的开区间，只在R时使用）
临键锁（锁的时(x,y] x和y之间的闭区间默认是一个左开右闭的模式，只在R时使用）记录锁+间隙锁
当存在值a,b,c时，select b for update时，锁b时，间隙锁会锁[a,c]之间的全部。与无穷之间亦然

• 当 SQL 执行按照非唯一索引进行数据的检索时，会给匹配到行上加上临键锁——非唯一索引
• 建议尽可能让所有的查询都使用主键或者唯一索引，这样可以避免临键锁，预防对记录大面积的锁

意向锁：（以下是我认为特别直观的例子，一目了然）
打个比方，就像有个游乐场，很多小朋友进去玩，看门大爷如果要下班锁游乐场的门(加表锁)，他必须确保每个角落都要去检查一遍，确保每个小朋友都离开了(释放行锁)，才可以锁门。
假设锁门是件频繁发生的事情，大爷就会非常崩溃。那大爷想了一个办法，每个小朋友进入，就把自己的名字写在本子上，小朋友离开，就把自己的名字划掉，那大爷就能方便掌握有没有小朋友在游乐场里，不必每个角落都去寻找一遍。例子中的“小本子”，就是意向锁，他记录的信息并不精细，他只是提醒大爷，有人在屋里。
链接：https://www.jianshu.com/p/478bc84a7721

总结:

• MYISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁（因为不支持事务）
• lnnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
  另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

锁等待分析：
通过检查InnoDB_row_lock状态变量来分析系统的行锁争夺情况

show status like “innodb_row_lock%”;
对各个状态量的说明如下:
Innodb_row_lock_current_waits:当前正在等待锁定的数量Innodb_row_lock_time:从系统启动到现在锁定总时间长度Innodb_row_lock_time_avg:每次等待所花平均时间
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是:
Innodb_row_lock_time_avg（等待平均时长)Innodb_row_lock_waits（等待总次数)Innodb_row_lock_time（等待总时长)

–查看事务
select* from INEORMATION_SCHEMA.INNODB_TRX;（常用）
–查看锁,8.0之后需要换版这张表data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
–查看锁等待,8.0之后需要换成这张表data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
show engine innodb status;

mysql一般可以自动检测死锁并回滚产生死锁的事务，在日志里查看到对于事务线程id，手动kill解决。

锁优化实践

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围,避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能用低的事务隔离级别

MvCC多版本并发控制机制

注意:
begin/start transacion命令并不是一个事务的起点。在执行到它们之后的第一个修改操作或加排它锁操作(比如iselec …frupdale)的语句，事务才真正启动，才会向mysc申请真正的事务id,mysql内部是严格按照事务的启动顺序来分配事务id的。
总结:
MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务it数组（数组里最小的d为min_id)和已创建的最大事务id (max_id)组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
版本链比对规则:
1.如果row的 trx_id落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的;
2.如果row的 tx_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 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。
提问：（背景条件默认事务id顺序创建）若readview为[100，200] 300时，在[100，200]这两个数内时，说明生成readview时未提交，词条数据不可见，但不在这两个内的，比如280也已经提交，那说明生成事务时已提交，可见

Innodb底层原理与Mysql日志机制深入剖析

连接器
我们知道由于MySQL是开源的，他有非常多种类的客户端：navicat,mysql front,jdbc,SQLyog等非常丰富的客
户端，包括各种编程语言实现的客户端连接程序，这些客户端要向mysql发起通信都必须先跟Server端建立通信连
接，而建立连接的工作就是有连接器完成的。

查询缓存（鸡肋）：一旦表更新，缓存全部清空；需要sql语句完全一样
mysql 8.0已经移除了查询缓存功能

my . cnf
#query_cache_type有3个值e代表关闭查询缓存OFF，1代表开启ON，2(DENAND）代表当sql语句中有SQL_CACHE关键词时才缓存query_cache_type=2

分析器
如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解
析。
分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符
串分别是什么，代表什么。
MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把
字符串“ID”识别成“列 ID”。
做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个
SQL 语句是否满足 MySQL 语法。

SQL语句经过分析器分析之后，会生成一个这样的语法树

优化器
经过了分析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引;或者在一个语句有多表关联(join）的时候，决定各个表的连接顺序;以及一些mysql自己内部的优化机制。
执行器
开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示(在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证)。
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

究极无敌之看懂你就毕业之mysql底层理解一体图

ps：
1.默认ibd的page一页大小为16kb
2.redolog记录的不是具体做了什么修改而是其物理修改（在哪个页做了什么修改）
3.binlog记录的是逻辑修改（具体的sql语句），完成后再在redolog加commit标记，保持binlog和redolog数据一致。
4.binlog是Server层的，MYISAM也有，但redolog和undolog是InnoDB独有的。
5.先写redolog再将缓存池内的修改数据刷到ibd表文件里，叫WAL（write-ahead logging）机制。

• redolog重要参数：
  innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。
  innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
  innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1… iblogfileN。默认2
  个，最大100个。
  innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo
  log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。
  innodb_flush_log_at_trx_commit：这个参数控制 redolog 的写入策略，它有三种可能取值：
  设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。(效率最高)
  设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。（安全性最高）
  设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数
  据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。
  

redo log 写入磁盘过程分析：
redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如
下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里。
write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上
checkpoint，表示redo log写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一
下。

• binlog二进制归档日志
  binlog只保存了所有执行过的修改操作语句，不保存查询操作。
  启动binlog记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。（5.7默认关闭，8.0默认打开）

 # log‐bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在
data数据目录下
log‐bin=mysql‐binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环
境中每台mysql服务器的id不能一样，不加启动会报错
server‐id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB
1
2
3
4
5
6
7
8
9
10

ps：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。
binlog_format：可以设置binlog日志的记录格式

• STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。
• ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
• MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

binlog写入磁盘机制主要通过 sync_binlog参数控制，默认值是 0。

• 为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
• 也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
• 还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

# 查看bin‐log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐bi
nlog.000007

# 查看bin‐log二进制文件（带查询条件）
mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000007 start‐datetime="2023‐01‐21 00:00:00" stop‐datetime="2023‐02‐01 00:00:00" start‐position="5000" stop‐position="20000"
1
2
3
4
5
6

BINLOG恢复数据

mysqlbinlog ‐‐no‐defaults ‐‐start‐position=219 ‐‐stop‐position=701 ‐‐database=test D:/dev/mysql‐
5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v test

# 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
mysqlbinlog ‐‐no‐defaults ‐‐start‐datetime="2023‐1‐27 23:32:24" ‐‐stop‐datetime="2023‐1‐27 23:34:23" ‐‐database=test D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v
test
1
2
3
4
5
6

为了避免某些特种兵程序员删库跑路：
可以每天凌晨做数据库的全量备份，然后可以用它将数据库恢复到0点的状态，再用binlog恢复到当前状态，非常完美。
（备份数据库一般可以用mysqldump 命令工具）

• 为什么会有redolog和binlog两份日志呢？
  因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有crash-safe 能力的，所以InnoDB 使用另外一套日志系统——也就是 redolog 来实现 crash-safe 能力。
  有了 redolog，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

undo log回滚日志
InnoDB对undo log文件的管理采用段的方式，也就是回滚段（rollback segment） 。每个回滚段记录了 1024 个undo log segment ，每个事务只会使用一个undo log segment。
在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大 128个回滚段，故其支持同时在线的事务限制提高到了 128*1024 。

• undo log日志什么时候删除
  新增类型的，在事务提交之后就可以清除掉了。
  修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。
• 为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
  因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。
  因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
  Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。
  更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。
  正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

错误日志
Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相
关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭

# 查看错误日志存放位置
show variables like '%log_error%';
1
2

通用查询日志
通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。
通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。
general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启

MySQL全局优化与Mysql 8.0新增特性详解

假设服务器配置为：
CPU：32核
内存：64G
DISK：2T SSD
下面参数都是服务端参数，默认在配置文件的 [mysqld] 标签下

max_connections=3000
1

连接的创建和销毁都需要系统资源，比如内存、文件句柄，业务说的支持多少并发，指的是每秒请求数，也就是QPS。
一个连接最少占用内存是256K，最大是64M，如果一个连接的请求数据超过64MB（比如排序），就会申请临时空间，放到硬盘上。
如果3000个用户同时连上mysql，最小需要内存3000 * 256KB=750M，最大需要内存3000 * 64MB=192G。
如果innodb_buffer_pool_size是40GB(一般设置为机器内存的60-70%)，给操作系统分配4G，给连接使用的最大内存不到20G，如果连接过多，使用的内存超过20G，将会产生磁盘SWAP，此时将会影响性能。连接数过高，不一定带来吞吐量的提高，而且可能占用更多的系统资源。

• max_user_connections=2980
  允许用户连接的最大数量，剩余连接数用作DBA管理。
• back_log=300
  MySQL能够暂存的连接数量。如果MySQL的连接数达到max_connections时，新的请求将会被存在堆栈中，等待某一连接释放
  资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将被拒绝。
• wait_timeout=300
  指的是app应用通过jdbc连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
• interactive_timeout=300
  指的是mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800，单位秒，即8个小时。
• innodb_thread_concurrency=64
  此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍，如果超过配置并发数，则需要排队，这个值不宜太大，不然可能会导致线程之间锁争用严重，影响性能。
• innodb_buffer_pool_size=40G
  innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%。
• innodb_lock_wait_timeout=10
  行锁锁定时间，默认50s，根据公司业务定，没有标准值。
• innodb_flush_log_at_trx_commit=1
  redolog刷盘策略
• sync_binlog=1
  undolog刷盘策略
• sort_buffer_size=4M
  每个需要排序的线程分配该大小的一个缓冲区。增加该值可以加速ORDER BY 或 GROUP BY操作。
  sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
  sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(4M)=2G。
• join_buffer_size=4M
  用于表关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

Mysql 8.0新特性详解
建议使用8.0.17及之后的版本，更新的内容比较多。

• 1、新增降序索引
  //创建表，含降序索引（5.7中desc无效，8.0有效）
  create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc));
  // Using index：扫描索引
  explain select * from t1 order by c1,c2 desc
  // Backward index scan; Using index ： 反向扫描索引
  explain select * from t1 order by c1 desc,c2
  //Using index; Using filesort ：没有走索引
  explain select * from t1 order by c1 desc,c2 desc
  explain select * from t1 order by c1,c2
• 2、group by 不再隐式排序
  mysql 8.0 对于group by 字段不再隐式排序，如需要排序，必须显式加上order by 子句。
  //对其他数据库的兼容增强了
• 3、增加隐藏索引
  使用 invisible 关键字在创建表或者进行表变更中设置索引为隐藏索引。索引隐藏只是不可见，但是数据库后台还是会维护隐藏索引的，在查询时优化器不使用该索引，即使用force index，优化器也不会使用该索引，同时优化器也不会报索引不存在的错误，因为索引仍然真实存在，必要时，也可以把隐藏索引快速恢复成可见。注意，主键不能设置为 invisible。
  软删除就可以使用隐藏索引，比如我们觉得某个索引没用了，删除后发现这个索引在某些时候还是有用的，于是又得把这个索引加回来，如果表数据量很大的话，这种操作耗费时间是很多的，成本很高，这时，我们可以将索引先设置为隐藏索引，等到真的确认索引没用了再删除。

> //查询索引状态
> show index from 表名\G
> //在会话级别设置查询优化器可以看到隐藏索引
> set session optimizer_switch="use_invisible_indexes=on";
> //查询是否使用隐藏索引
> select @@optimizer_switch\G
1
2
3
4
5
6

• 4、新增函数索引
  函数索引基于虚拟列功能实现，在MySQL中相当于新增了一个列，这个列会根据你的函数来进行计算结果，然后使用函数索引的时候就会用这个计算后的列作为索引。
  //将函数排序后的主键建索引

//创建普通索引
create index idx_c1 on t3(c1); 
//创建一个大写的函数索引
create index func_idx on t3((UPPER(c2)));
1
2
3
4

• 5、innodb存储引擎select for update跳过锁等待
  在5.7及之前的版本，select…for update，如果获取不到锁，会一直等待，直到innodb_lock_wait_timeout超时。
  在8.0版本，通过添加nowait，skip locked语法，能够立即返回。如果查询的行已经加锁，那么nowait会立即报错返回，而skip locked也会立即返回，只是返回的结果中不包含被锁定的行。

应用场景比如查询余票记录，如果某些记录已经被锁定，用skip locked可以跳过被锁定的记录，只返回没有锁定的记录，提高系统性能。

//为查询的所有行记录加上排他锁（mysql5.7）
select for update
//新增加查询共享锁的语法（mysql8.0）
select ... for share
1
2
3
4

• 6、新增innodb_dedicated_server自适应参数
  能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size，innodb_log_file_size等参数，会尽可能多的占用系统可占用资源提升性能。解决非专业人员安装数据库后默认初始化数据库参数默认值偏低的问题，前提是服务器是专用来给MySQL数据库的，如果还有其他软件或者资源或者多实例MySQL使用，不建议开启该参数，不然会影响其它程序

//默认是OFF关闭，修改为ON打开
show variables like '%innodb_dedicated_server%'; 
1
2

• 7、死锁检查控制
  MySQL 8.0 （MySQL 5.7.15）增加了一个新的动态变量 innodb_deadlock_detect，用于控制系统是否执行 InnoDB 死锁检查，默认是打开的。死锁检测会耗费数据库性能的，对于高并发的系统，我们可以关闭死锁检测功能，提高系统性能。但是我们要确保系统极少情况会发生死锁，同时要将锁等待超时参数调小一点，以防出现死锁等待过久的情况。

 show variables like '%innodb_deadlock_detect%'; ‐‐默认是打开的
1

• 8、undo文件不再使用系统表空间
  //原本5.7中undolog默认在ibdata(系统表空间)中存放
  默认创建2个UNDO表空间，不再使用系统表空间

• 9、 binlog日志过期时间精确到秒
  之前是天，并且参数名称发生变化. 在8.0版本之前，binlog日志过期时间设置都是设置expire_logs_days参数，而在8.0版本中，MySQL默认使用binlog_expire_logs_seconds参数。

• 12、默认字符集由latin1变为utf8mb4
  在8.0版本之前，默认字符集为latin1，utf8指向的是utf8mb3，8.0版本默认字符集为utf8mb4，utf8默认指向的也是utf8mb4

• 13、MyISAM系统表全部换成InnoDB表
  将系统表(mysql)和数据字典表全部改为InnoDB存储引擎，默认的MySQL实例将不包含MyISAM表，除非手动创建MyISAM表。

• 14、元数据存储变动
  MySQL 8.0删除了之前版本的元数据文件，例如表结构.frm等文件，全部集中放入mysql.ibd文件里。可以看见下图test库文件夹里已经没有了frm文件。

• 15、自增变量持久化（重要）
  在8.0之前的版本，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置
  AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199)，一直到8.0才被解决，8.0版本将会对AUTO_INCREMENT值进行持久化，MySQL重启后，该值将不会改变。

• 16、DDL原子化
  InnoDB表的DDL支持事务完整性，要么成功要么回滚。
  MySQL 8.0 开始支持原子 DDL 操作，其中与表相关的原子 DDL 只支持 InnoDB 存储引擎。一个原子 DDL 操作内容包括：更新数据字典，存储引擎层的操作，在 binlog 中记录 DDL 操作。支持与表相关的 DDL：数据库、表空间、表、索引的 CREATE、ALTER、DROP 以及 TRUNCATE TABLE。支持的其它 DDL ：存储程序、触发器、视图、UDF 的 CREATE、DROP 以及ALTER语句。支持账户管理相关的 DDL：用户和角色的 CREATE、ALTER、DROP 以及适用的 RENAME等等。

• 17、参数修改持久化
  MySQL 8.0版本支持在线修改全局参数并持久化，通过加上PERSIST关键字，可以将修改的参数持久化到新的配置文件（mysqld-auto.cnf）中，重启MySQL时，可以从该配置文件获取到最新的配置参数。set global 设置的变量参数在mysql重启后会失效。

set persist innodb_lock_wait_timeout=25;
系统会在数据目录下生成一个包含json格式的mysqld‐auto.cnf 的文件，格式化后如下所示，当my.cnf 和mysqld‐auto.cnf 同时存在时，后者具有更高优先级。

• 10、窗口函数(Window Functions)：也称分析函数
  从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种分组聚合函数类似，在聚合函数后面加上over()就变成窗口函数了，在括号里可以加上partition by等分组关键字指定如何分组，窗口函数即便分组也不会将多行查询结果合并为一行，而是将结果放回多行当中，即窗口函数不需要再使用 GROUP BY。