Mysql数据库高级、sql优化

https://note.youdao.com/ynoteshare/index.html?id=53cd90b2a1d930e1f5c7558a3f595696&type=notebook&_time=1655133645036

一、设计思想(分层、扩展)

实际上我们的理解的mysql就是存储数据的，我们可以通过可视化工具方便的对mysql进行数据的操作，但是mysql也是一个服务，对于开发mysql的人员来说以及我们后续开发服务时可以学习到的设计思想是分层，以及扩展性，mysql的扩展性主要体现在多种存储引擎的任意使用（可插拔的方式）
查询时小表驱动大表，因为每次表之间的关联操作实际上是IO操作，因此减少IO处理。

二、服务结构

分层图

mysql server的分层：连接层、服务层(分析器、优化器)、存储引擎(事务实现是在该层)、文件存储

其中服务层的Optimizer会对sql做一些优化

sql执行过程

查看缓存

• MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key 是查询的语句, value 是查询的结果。如果你的查询能够直接在这个缓存中找到key,那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后， 执行结果会被存入查询缓存中。所以,如果查询命中缓存，MySQL 不需要执行后面的复杂操作,就可以直接返回结果，这个效率会很高。
• 此外，既然是缓存,那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、 DELETE、 TRUNCATE TABLE TABLE或DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除!对于更新.压力大的数据库来说，查询缓存的命中率会非常低。
  总之，因为查询缓存往往弊大于利，查询缓存的失效非常频繁。
  mysql5.8中查询缓存已经删除了

服务层分析/解析器（词法分析和语法分析）

如果没有命中查询缓存，就要开始真正执行语句了。MySQL 需要知道你要做什么,因此需要对SQL语句做解析。
SQL语句的分析分 为词法分析与语法分析。

• 分析器先做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。
• 接着，要做“语法分析”。根据词法分析的结果，语法分析器(比如: Bison) 会根据语法规则，判断你输入的这个SQL语句是否满足MySQL 语法。
  如果你的语句不对，就会收到“You have an error in your SQL syntax’的错误提醒，比如下面这个语句from写成了“rom”
  如果SQL语句正确会生成语法树：
  
  语法树在后面被回滚的时候会用到

服务层优化器

• 一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。比如:优化器是在表里面有多个索引的时候，决定使用哪个索引;或者在一个语句有多表关联(join) 的时候,决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。
• 在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。
• 逻辑查询优化就是通过改变SQL语句的内容来使得SQL查询更高效,同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化,对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
• 物理查询优化是基于关系代数进行的查询重写，而关系代数的每-步都对应着物理计算, 这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

执行器

• 执行器这里主要对生成的执行计划进行实施，这里是由存储引擎层去执行数据的存储和检索。先记录undo.log日志，然后执行sql（要是在8之前有缓存的情况，再将查询的结果放在缓存中一份），再记录redolog日志，最后记录binlog日志，完成事务提交

三、存储结构

存储引擎（MyISAM、INNODB）

存储引擎的设置实际上是基于表的，不同的表可设置不同的存储引擎

为什么选择B+树

mysql的存储引擎都是采用B树的结构，并且使用的都是B+树（B树的改进），其特点就是只有在叶子节点存储数据
那么mysql为什么会选择采用B+树存储？而不使用二叉树、红黑树、hash表

• 1、使用二叉树带来的问题，要是使用二叉树存储聚簇索引（主键索引）的话，并且要是递增的情况下，那么二叉树就成了链表的形式了，查询效率就非常慢了。
• 2、红黑树：虽然是自平衡的二叉树，但是由于每一个节点都是存储的一个数据，那么对于要是存储大数据量时，I/O操作的次数也是会非常多的
• 3、hash表：存在hash冲突，更重要的是hash表存储的话不支持范围查找，因为存储在hash桶中的数据就是按照hash值存储的，没有任何的规则，所以要是想查询年龄>5的数据，有可能在不同的桶中都有。
• 4、B+树：采用页存储的方式，一个节点或者说是一次I/O操作会查询出多个数据

B-Tree和B+树的区别

相同点: 每个节点都会使用磁盘页的方式，可以存储多个数据
不同点:

• 1、B树不光是在叶子节点存储数据，也可以在非叶子节点存储数据，而B+树首先只在叶子节点存储数据，使得每个磁盘页存储的页指针更多（因为少存了数据），因为mysql的存储结构有一个原则就是减少I/O操作。同时对于非叶子节点不存储数据的情况下，mysql会首先将非叶子节点的数据拿到内存。
• 2、B+树相邻的叶子节点间也会存储指向下一相邻叶子节点的指针；这样的话方便了范围查找，由于B+树是顺序的，所以在查找时，要是叶子节点有指针，可以继续往后找了，否则对于不在一个磁盘页的数据还需要从根节点重新遍历找。
• 3、B+树中页存储数据有重复值（冗余），但是B树没有重复值
• 4、对于千万级的数据，B+树的高度可控制在3层内，并且每个叶子节点的高度是相同的

MyISAM和INNODB的B+Tree的对比

其中存储引擎有很多种，比较常用的是MyISAM、INNODB(5.5之后默认的)

myISAM

INNODB

两个存储引擎B+树存储数据的区别：

对比项	MyISAM	INNODB
维护的表数量	frm文件：存储的是建表的语句，myI ：存储的是索引文件，myd：存储的是真实的文件。索引文件和数据文件是分离的	frm还是建表语句，idb:索引和数据
叶子节点存储内容	主键索引/普通索引叶子节点都是存储的磁盘地址	主键索引存储了一整行数据；普通索引存储了索引值同时存储了主键值

解释：存储的磁盘地址，主要依赖于数据的存储位置，要是存储位置变了，磁盘地址就需要改变，但是存储的是主键值时，不管数据的实际存储位置如何改变，都不会影响索引的调整。
MyISAM 的B+树只存储磁盘地址，这样不管查询的内容是否是索引内容，都需要再根据指针查询真正的数据，而INNODB要是在查询的字段或者排序的字段是索引列时，只需要在索引文件中查询就可以得到数据，只有查询索引列以外的字段，才需要根据主键索引去数据文件中找。

常见问题

• 什么是回表
  在非聚簇索引下通过主键到聚簇索引中查询数据的过程

• 为什么INNODB表建议都要创建一个主键索引？
  因为INNDB底层存储索引的结构就是使用B+树，因为在构建B+树的时候终归是要找到一个基点，要是不创建主键索引的话，mysql会自己找到一列不重复的作为唯一索引处理，要是所有列都没有完全不重复的数据，那么mysql就会自动的创建一列，自增

• 为什么建议使用整型自增？
  在构建B+树时，总会有数据的比较，因为B+树是要构建成有序的，字符串的比较还要转换为ASCII码，显然整数的比较要比字符串快；自增的主要是为了防止页分裂

页存储

磁盘与内存交互基本单位:页

InnoDB将数据划分为若千个页，InnoDB中页的大小默认为16KB。
以页作为磁盘和内存之间交互的基本单位

• 也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
• 也就是说，在数据库中,不论读一行,还是读多行，都是将这些行所在的页进行加载。
• 也就是说，数据库管理存储空间的基本单位是页(Page) ，数据库I/O操作的最小单位是页。一个页中可以存储多个行记录。
  数据库中不光是有页，还有区、段、表空间的概念。数据库是由多个表空间组成的，每个表空间又是由多个段组成的，每个段是由多个区组成的，每个区是由连续的64个页组成的，每个页大小默认为16KB。
  

页的结构

纠正一点：上图中的大小，单位是字节，不是kb

四、数据库缓冲池-Buffer Pool

重要性及作用(减少IO)

InnoDB存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面、写页面、创建新页面等操作)。而磁盘I/O需要消耗的时间很多，而在内存中进行操作,效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool 之后才可以访问。
这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行I/O 的时间。要知道，这种策略对提升SQL语句的查询性能来说至关重要。如果操作的数据在缓冲池里,那么访问的成本就会降低很多。

原则（减少IO提高效率）

• 位置决定效率,提供缓冲池就是为了在内存中可以直接访问数据
• 频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有200G,但是内存只有16G,缓冲池大小只有1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会优先对使用频次高的热数据进行加载。

刷盘机制（checkpoint）

实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后会以一定的频率刷新到磁盘上。注意并不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上,这样做的好处就是提升了数据库的整体性能。这里所说的刷盘是将redo log buffer中的内容写到redo log file文件中（磁盘），注意，这里也只是操作的redo log文件，并不是真正数据库对应的表文件（redo log正式WAL的很好体现，后续再讲WAL）。
当缓冲池不够用时，需要释放掉一些不常用的页，此时就可以强行采用checkpoint的方式，将不常用的脏页回写到磁盘上,然后再从缓冲池中将这些页释放掉。这里脏页(dirty page)指的是缓冲池中被修改过的页，与磁盘上的数据页不一致。

#查看缓存池大小 默认大小128M
show variables like 'innodb_buffer_pool_size';
#修改缓存大小
set global innodb_buffer_pool_size = number;
#或者修改配置文件，重启mysql
[server]
innodb_buffer_pool_size = number;

多实例

Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间，当缓存池的大小>1G时，建议将实例调大，默认为1，因为数据量变大或者并发访问量高时，要是只有一个实例，会影响访问效率的，但是也不是越多越好，适当即可

#查看实例个数，默认是1个，建议在大小>1G时，增加实例
show variables like 'innodb_buffer_pool_instances';
#或者修改配置文件，重启mysql
[server]
innodb_buffer_pool_instances = 2;

五、数据类型

六、索引

索引分类

聚簇索引、非聚簇索引、唯一索引、联合索引、单值索引、覆盖索引、前缀索引
测试要是在单值索引和联合索引一起使用时，优先使用联合索引

sql性能分析（explain）

正常的sql select * from a where XX前添加explain关键字就可以对该条sql进行性能分，执行结果如下字段：
explain 关键字-mysql的解释执行，可以用此来分析sql的效率，其结果为：
id、select_type、table 、type 、possiable_keys、key、key_len、ref、rows、extra
id参数主要是用来判断一条多表查询sql，执行的先后顺序
id 相同时，表的执行顺序是从上到下
id不同时，id值越大的，越先执行，优先级越大

select_type 指明select的类型：simple、primary、subquery、derived(衍生 from结构的子查询)、union、union result

type 指明索引的级别：级别从大到小
system>const>eq_ref>ref>range>index>all(全表扫描)，还会有Index_merge（同一张表中对多个索引做的优化处理）
在分析sql时，至少要达到ref、range
ref ：使用索引并查询出多条数据，eq_ref：使用索引只查询出一条数据，一般都是唯一索引
between > < in like‘a%’等级别是range
index虽然也是全表扫描，但是其走的是索引，all走的是硬盘

extra 其他比较重要的属性
Using filesort（不好）：一般在使用了order by /group by 时，会出现该属性，该属性表明sql在执行的时候进行内部排序了，一般情况就是在使用复合索引的时候，最好是按照顺序和个数同时使用，比如复合索引共有3个字段，where 1= order by 3(执行时有Using filesort),但是where 1= order 2,3（无Using filesort）
说明：order可以结合where 条件共同使用复合索引，group by和order by 基本是一样的，group by在分组时首先做了排序，然后又进行的分组
Using temporary（不好）：一般在使用group by 的时候，要是没有完全按照复合索引的顺序，不但会引来Using filesort，也会引来Using temporary，都是比较影响性能的。
Using index(好事):使用到覆盖索引的就会有该参数，什么是覆盖索引，就是select d的字段是复合索引（或者是复合索引的部分，顺序可不一致，索引type=index），但是要求是复合索引的字段数量一定要>=select的字段数量才叫覆盖索引，因此这也是select 时候尽量不要使用*的其中一个原因；另外使用到索引下推的时候也会有此标识，索引下推一般出现在联合索引使用时。
特殊说明下：在对于多表关联查询时，比如使用的join语句，那么关联条件需要建索引的时候，在从表建索引会生效，否则索引不生效。
union 不光有合并的作用，同时还有去重的作用

索引失效场景

执行了explain的语句，一般情况下通过type,key,extra等字段就可以看sql是否走索引，同时key_len可以判断索引使用字段的个数，一般情况下，使用的查询条件越多，key_len的值越大
索引失效的原因：

1）like：查询条件中有like的，要是like的索引生效，如：like ‘ab%’，则type类型是range,但是和>不同的是，>也是range ,但是>后面的查询条件不走索引了，但是like ‘ab%’ 自身和like后面的都会走索引的。要是使用like ‘%ab’，那个like自身的条件也不走索引了，并且后面的也不走了，sql最终是否走索引需要看like 之前的条件是否使索引。
2）where 条件后的查询条件顺序可以不和索引顺序完全一致，mysql会自动将顺序排好，mysql在执行的时候会有优化器将sql进行优化，同时不会引来Using filesort。
3）一条sql在执行的时候，可能会使用到多个索引，在对于同一张表的多个索引时，会出现索引合并的情况，不同表中是不会合并的，要是出现索引合并的情况type属性写的是Index_merge,key也会列举出所有使用到的索引。
4）in 是可以正常走索引的，type = range，但是not in不走索引了，所以在使用not in时可以使用not exists替换
5）关于使用in还是exists的区别：
“外层查询表小于子查询表，则用exists，外层查询表大于子查询表，则用in，如果外层和子查询表差不多，则爱用哪个用哪个。”
6）使用is null 或者是is not null 会导致索引失效，所以我们在创建表结构的时候尽量要定义为非null，并且要设置默认值，同时对于库表中可为null的字段，在存储的时候也会额外占用空间，使用bit位进行存储。
7）使用or关键字的话，就不会走到索引了，type = index，虽然也是全表扫描，但是比all好一些。
8）索引的重要性：不光是为了查询的效率，因为命中了索引的sql查询效率肯定是快的，另一方面，合理的索引以及正确的使用索引可以避免行锁升级为表锁。

• 一个是在更新的时候where条件中有非索引列，不管是否使用><或者like，都会锁住全表，即使走了索引
• 另一个是即使全部where字段都是索引列，但是索引列上使用了<>或者like等，有可能使索引失效，那么也会锁住全表。
• 单纯的写select 是不会锁表的，即使索引失效也不会锁全表

如何正确的创建索引

1）索引是排序好的快速查询的数据结构，不光是应用于查询，还用于排序
2）在更新操作的时候有索引会影响效率，因为mysql不光要维护数据，还要维护索引，要是做更新操作，索引文件就会重新生成

索引下推（ICP)

介绍

介绍
Index Condition Pushdown（ICP）是MySQL 5.6中新特性,是一种在存储引擎层使用索引过滤数据的优化方式。索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。索引下推的目的是为了减少回表操作

使用场景

Join详细用法

join分为inner join、left join、right join、full join，在进行join时首先遵从2个原则，一个是用小结果集的表作为驱动表，另外一个就是尽量在被驱动表关联字段上添加索引。
join连接多个表有不同的方式，但本质都是各个表之间数据的循环匹配。MySQL5.5 版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会非常长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

inner join

mysql的优化器在进行inner join时，首先会优化使用哪张表作为驱动表，哪张作为被驱动表，原则：小表作为驱动表

left join/right join

前面的表作为驱动表

嵌套循环（Nested-Loop）

嵌套循环主要做的操作就是用驱动表，匹配被驱动表

简单嵌套循环

索引嵌套循环

要求：为了减少内表数据的匹配次数，在被驱动表中的关联条件上建立索引

块嵌套循环

小结

七、数据库事务

数据库事务四大特性（ACID）

说明：一致性是事务的最终目标，而隔离性是事务要达到目标的手段，原子性是对一个事务的简单说明

ACID是如何实现的

AICD主要是通过InnoDb存储引擎的不同日志保证的，同时mysql的binlog日志也有一定的作用
A(原子性)：undo log,要是事务需要回滚，执行undo log的数据即可，这里当前事务执行前一版数据
C(一致性)：最终目标，由其他三个特性决定
I(隔离性)：MVCC+锁决定
D(持久性)：内存+redo log日志(commit标志)+binlog

事务的隔离级别

mysql默认使用的是rr，oracle默认是rc
更正下，幻读是两次读取结果集的行数发生了改变，多了或者少了

幻读的解决办法（快照读-MVCC，当前读-Next-Key Lock）

幻读的解决办法：https://www.cnblogs.com/xuwc/p/13873293.html

sql读取分为当前读和快照读，而mvcc是在快照读的情况下解决了幻读，但是在当前读的情况下需要通过间隙锁+行锁解决幻读
1）a事务两次select,b事务insert,由于readview的原因可以使得两次select结果相同。
2）再就可以利用当前读也可以做到，select update select ,另外一个事务insert,要是update的操作可以产生间隙锁，并且insert的恰好是间隙锁间数据，会导致a事务提交前，insert被阻塞
出现幻读的情况：
1、select (insert) update(由于是当前读，获取的是最新数据，并且在修改后将那条数据的事务ID修改成当前事务) select
当前读：
select * for update
select * lock in share mode
insert delete update
对于范围条件筛选的，可以通过间隙锁方法阻止insert等操作
对于单条件的查询要是唯一索引查询，那么使用的行锁，要是非唯一索引则是使用的间隙锁。
mvcc是多版本并发控制，主要是将版本链+readview（是一组活跃事务的id），mysql在事务开启的时候，认为就会产生readview，并且在当前事务没有结束时始终使用的都是同一个readview。而快照读依赖的就是这个readview,当我们要去访问一条数据时，首先会判断该条数据记录的最大的事务ID，然后拿该值和readview中的事务ID进行比较，要是在readview的左边说明是当前事务开始前已经完成的事务，可以对该条数据进行访问。要是在readview中或者是在这之后说明在当前事务开始之后，有别的事务对该条数据进行过修改，所以就要访问这条数据上一个版本的信息。至于多个版本的生成，实际上个人理解应该是该条数据的主键是不变的，不然无法定位到这条数据。

事务日志文件(redo.log，undo.log，bin.log)

redo log file

redo log叫做重做日志，是保证事务持久性的重要机制。当mysql服务器意外崩溃或者宕机后，保证已经提交的事务，确定持久化到磁盘中的一种措施。redo log file是一个存储在磁盘中的文件。默认是在mysql服务的var/lib/mysql目录下。在主从的结构中，主服务器的恢复主要通过redo log完成的。

redo log 组成部分

redo log file有两个组成部分，一个是redo log buffer ,其是在内存中的一块空间。另一个才是磁盘中的redo log file。
redo log buffer的大小默认是16M，最大4096M，最小1M。一个redo log buffer又分为很多个redo log block 一个block的大小为512字节。
可以通过设置 innodb_log_buffer_size设置大小

redo log 工作流程

• 1、当我们在一个事务中执行sql语句时，是先修改的buffer pool中的内容（内存），同时也会向redo log buffer中写入内容（也是内存中的空间，写入的叫redo log，每条redo log由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成。主要也是记录修改之后的数据值）
• 2、当事务执行完毕，commit的时候，会将redo log buffer中的内容同步到磁盘中的redo log 文件，我们所说的刷盘就是指的这一步骤
• 3、当然刷盘是有策略的，可以通过innodb_flush_log_at_trx_commit 指定，默认为1

0：当commit的时候，什么都不做，由存储引擎后台的线程定时执行。定时任务是每1s执行一次，注意：这里由于刷盘的动作由定时任务处理了，所以刷到磁盘中的内容有可能是未提交事务。后面还得由undolog回滚。
（缺点：有可能定时处理时没有commit，所以有可能将未提交的事务也刷盘了）
1：commit时，将redo log buffer中的数据同步到pach cache(操作系统层面)，刷盘到redo log文件（磁盘）
2：commit时，将redo log buffer中的数据同步到pach cache(操作系统层面)，由系统决定啥时候刷盘。相对来说，也是比较保险的，因为mysql宕机的话还是可以持久的，只有操作系统宕机了，才会丢数据。

1也是最耗时的，其次是2，最快的是0。但是1是能确保持久化的，其他两个都不能确保持久化

redo log 参数设置

# 查看redo log buffer的大小,默认16M
show variables like '%innodb_log_buffer_size%';
# 查看刷盘策略
show variables like '%innodb_flush_log_at_trx_commit%';

redo log 日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容（文件的大小和个数均可通过配置调整）
它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示（类似log4j）

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。
每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。
write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录
如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

undo log file

• 事务回滚，确保事务的原子性和一致性
• MVCC多版本并发控制，依赖undo log找到上一版本数据

binlog

binlog记录内容

binlog是mysql的服务层维护的日志，记录形式顾名思义就是二进制，记录的内容是DDL和DML等数据库更新事件的语句，event事件。查询的sql不会被记录，原因：1、通用查询日志中记录的是最全的日志，包括查询了。2、binlog日志的作用是进行数据的同步和恢复，查询语句没有意义。建议binlog日志不要和其他日志放在一起，避免全军覆没

binlog格式

binlog参数设置

# 是否启用binlog日志
show variables like 'log_bin';

# 日志保存时长
show variables like '%expire_logs_days%';

# 查看当前服务器使用的binlog文件及大小
show binary logs;

# 查看 binlog 内容
show binlog events in 'binlog文件名';

# 查看binlog的目录
show global variables like "%log_bin%";

每当服务器重启的时候，都会新生成一个binlog文件

binlog日志查看

要是查看时间戳使用mysqlbinlog

• mysqlbinlog -v /data/xxxbinlog
  -v是说对查看的日志以伪sql的情况展示，并且后面跟着的是binlog的全路径
  

要是查看position使用events

• show binlog events in ‘xxxbinlog’;
  后面的info内容可以看到伪sql的执行语句
  

binlog日志实现数据恢复

• 使用show binlog events in ‘xxxbinlog’;先查看下日志的起止位置
• mysqlbinlog进行数据恢复
  mysqlbinlog --start-position=1001 --stop-position=1287 --database=tx_trace_db /data/mysql/mysqlbinlog.000001 | /usr/software/mysql-5.7.24/bin/mysql -uroot -pYhz19961214 -v tx_trace_db

binlog日志删除

purger master log to ‘文件名’
purger master log befor ‘时间戳’
reset master

binlog日志写入机制

write和fsync的时机，可以由参数sync_binlog控制，默认是0,其工作流程类似于redolog.

0:表示每次提交事务都只write,由系统自行判断什么时候执行fsync
1:表示每次提交事务都会执行fsync
2:可以设置为N(N>1), 表示每次提交事务都write,但累积N个事务后才fsync

binlog和redolog的区别

• redo log它是物理日志，是在事务执行过程中一直会记录的，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层产生的。
• 而binlog是逻辑日志，只有在事务提交时才会写入binlog，记录内容是语句的原始逻辑，类似于“给ID=2这一行的c字段加1”, 属于MySQL Server层。
• 虽然它们都属于持久化的保证，但是则重点不同。
  redo log让InnoDB存储引擎拥有了崩溃恢复能力。
  binlog 保证了MySQL集群架构的数据一致性。主要是做主从复制的
  

如何确保系统恢复时数据不一致问题

这里说的主要是针对主从结构中，主机和从机恢复时数据不一致问题，也就是redolog和binlog不一致问题。

• 主服务器的恢复主要依赖redo log,从服务器的恢复主要依赖binlog

mysql采用两阶段提交的方式，预防服务恢复时数据不一致的问题

这里就相当于预分单中或者是派单开始时，都要设置一个初始状态，表明该环节已经开始，至于执行结果，要再次设置值。也就是只要是记录redolog日志了，就算做是准备阶段，只有最终完成binlog的写入，才算是最终的提交，要设置commit标识

中继日志

八、数据库锁

读锁和写锁（表级别锁）

对于当前事务来说，读锁和写锁都是从权限上有限定的。
1、两种锁对锁表的事务有不同的权限，读锁只能读锁定表，写锁可以读写锁定表
2、不管是读锁还是写锁对于当前在使用锁的事务,都不可以对其他表做任何操作

从别的事务的操作来看，只对上锁的表会出现阻塞的情况，其他没有上锁的表是可以正常访问的。读锁是共享锁，可以读不可写，写锁是排他锁，读写都不可以

总结：读锁会阻塞写，写锁会阻塞读写，myISAM在进行查询操作前都会加上读锁，在进行更新操作时都会记上写锁
show open tables;–查看哪些表被锁，0-未锁，1-锁

行锁

MyISAM支持表锁，INNODB支持行锁，一般也都是给更新语句加行锁，要是给select语句加行锁的话，需要手动的添加for update，注意：对于行锁使用不当的情况下，会升级为表锁

间隙锁

间隙锁作用对象

一个区间中数据库中不存在的数据

间隙锁产生的场景

• 查询表中不存在的数据时，并且加上了for update或者lock in share mode，就会产生间隙锁
• 我们在进行范围查找时，都是开区间，并且加上了for update或者lock in share mode，就会产生间隙锁，要是左开右闭得话，就是临键锁了。临键锁就是行锁+间隙锁的结合（n,m]

间隙锁使用

• 产生的间隙锁，不管是使用的for update还是使用lock in share mode都是相同的效果，区别于查询表中已有数据来说，已有数据要是加了读锁，那么另一个事务也只能加读锁，要是加了写锁，那么另一个事务读写锁都不能加了。
• 间隙锁是可以重复加的，就是说不同的事务之间都可以对同一区间段进行加锁（间隙锁）
• 间隙锁主要是限制的insert操作的。要是对于加了间隙锁的区间来说，我们去更新数据时不会产生阻塞。
• 间隙锁是一个开区间，既不包括左边，也不包括右面

例：主键数据有1 3 4，更新时条件为>1 and ❤️，此时虽然没有2，但是也会产生间隙锁，要是insert 2时就会有阻塞了。或者select * from table where id = 2 for update；此时会产生间隙锁，insert 2时也会阻塞。

死锁（一般在行级锁上会出现死锁现象）

九、主从复制（同步）

主从复制的过程

对于mysql来说，最主要是配置文件，windows下是my.ini，linux下是my.cnf


要求：1、主从数据库版本一致
2、必须是同一网段，最起码能ping通
3、关闭防火墙

主从复制作用

• 提高数据库的吞吐量
• 读写分离，避免锁表
• 数据备份（数据冗余）
• 提高数据库的可用性

主从数据库搭建

主库修改

1、修改主库配置文件
【必填】server-id = 1 (一般情况下主的都设置为1)
【必填】log-bin = 设置生成的binlog日志文件的名称

 #0 (默认)表示读写(主机)，1表示只读(从机)
  【选填】read-only=0
	 #设置日志文件保留的时长，单位是秒
  【选填】binlog_expire_logs_seconds=6000
	 #控制单个二进制日志大小。此参数的最大和默认值是1GB
  【选填】max_binlog_size=200M
	 #[可选]设置不要复制的数据库
  【选填】binlog-ignore-db=test
	 #[可选]设置需要复制的数据库，默认全部记录。比如: 
  【选填】binlog-do-db=atguigu_master_slave
  【选填】binlog-do-db=需要复制的主数据库名字
	 #[可选]设置binlog格式
  【选填】binlog_format=STATEMENT

2、重启服务
3、master、slave都关闭防火墙
4、master上建立账号授权给slave，直接用mysql的命令行执行
这个是5.5版本之前的可以用一条指令就可以，要是8.0版本还需要加个指令。

5、show master status; – 查看主机的状态，重要的两个参数就是file和position，这里是作为同步的位置，需告知slave

从库需要配置

1、修改从库配置文件
server-id = 2
#[可选]启用中继日志
relay-log = mysql-relay (该值尽量不要修改)
2、关闭防火墙
3、slave 连接master做同步

3、start slave;
4、show slave status:
show-IO,show-sql 必须同时为YES，否则会导致同步失败

验证

接下来正常在master上做操作即可，自动会同步到slave

十、数据库的备份和恢复

• 1、手动的备份，直接将数据存储位置的文件进行复制操作，在Linux下数据库文件的存放目录一般为/var/lib/mysql
  备份文件前，需要将MySQL服务停止，然后将数据库目录拷贝即可
• 2、使用命令行工具

数据备份 ： mysqldump –user=root –password=root密码 –lock-all-tables 数据库名 > 备份文件.sql
数据恢复：mysql -u root –password=root密码 数据库名 < 备份文件.sql

十一、其他

sql函数使用注意事项

concat(str,substr) 字符串拼接

当str为null时，不能成功拼接，因此拼接时需要使用IFNULL()判断下

locate(substr,str) 判断子串是否包含

str为数据库中字段名称，substr就是要判断的字符串，若str字段中不包含substr的值则返回0，若包含则返回1，但需要注意当str（数据库字段）为null时，返回null

常用指令

# 查看字符集
show variables like '%char%';
# 查看当前数据库的存储引擎
show variables like '%storage_engine%';
# 查看某张表的索引
show index from XX表名;
# 查看表字段定义
desc f_work_permit_info;
# 查询慢日志是否开启
show variables like '%slow_query_log%';
# 查询慢查询日志的阈值
show variables like '%long_query_time%';
# 查询事务隔离级别
show variables like 'tx_isolation';
# 查询当前存在行锁的事务数量，以及总的等待次数
show status like 'innodb_row_lock%';
# 查询当前存在表锁的事务数量，以及总的等待次数
show status like 'table%';
# 查询等待锁
select * from information_schema.innodb_trx where trx_state='LOCK_WAIT';
# 查看表空间
select SUM(data_length)+SUM(index_length) from information_schema.TABLES where table_name = 'dispatch_snapshot';


# 查询页大小，默认是16KB
show variable like '%innodb_page_size%';
# 查看innodb缓存池大小 默认大小128M
show variables like '%innodb_buffer_pool_size%';
# 查看buffer pool实例个数,默认1
show variables like '%innodb_buffer_pool_instances%';

常用函数

# 压缩存储
COMPRESS('${snapshot.spTarget}') / UNCOMPRESS(sp_target) as sp_target

慢sql查询

• 1、检查慢sql日志是否开启，以及慢sql的阈值，可直接在查询
  查询慢日志是否开启
  show variables like ‘%slow_query_log%’;
  查询慢查询日志的阈值
  show variables like ‘%long_query_time%’;
• 2、要是测试环境中，慢sql的情况也比较少，可以直接查看上面查询到的慢sql日志记录；但是对于生产中或者比较大量的sql时，就需要依赖mysql提供的工具
  实际使用：
  

sql优化

子查询优化

由于子查询会将子查询的查询结果生成一张临时表，并且要是对于大结果集查询来说，性能都会受到影响，因此建议使用join连接替换子查询

排序优化

• 排序时也会走到索引，比如建一个联合索引时，可以结合where条件一起使用，但是需要注意的是排序时避免引入filesort
• 不加limit，索引失效

group by 优化

• group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。
• group by先排序再分组，遵照索引建的最佳左前缀法则。
• 当无法使用索引列，增大max_length_for_sort_data 和sort_buffer_size参数的设置。
• where效率高于having，能写在where限定的条件就不要写在having中了。
• 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
• 包含了order by: group by、distinct这些查询的语句， where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

建表设计

• 根据要求选择合适的数据库，比如选择mysql还是oracle，再次选择好存储引擎，要是支持事务的选择Innodb，要是不选择事务的可以使用MyISAM
• 建表时尽量遵从数据库三范式，适当冗余字段
• 合理创建索引，避免全表查询，或者是行锁升级为表锁。定义数据类型尽量使用数值型，要是使用字符型，尽量按照长度指定大小

数据库结构

• 采用读写分离方式（一般是在千万级以上）
• 分库分表（mysql自带的分库分表功能，垂直分表、水平分表、垂直+水平）
• 数据库参数设置（DBA的工作），注意修改后需要重启
• 结合缓存
• 冷热数据分离（历史表）
• 避免全表查询

十二、mysql5.8新特性

窗口函数

1、序号函数

2、分布函数

十三、数据分库分表、读写分离

1、分库分表

1.1 代码实现

1.2 分库分表策略

table-strategy: 表分片策略
分片策略

1.3 分库分表之查询

1.3.1 按照分片字段查询

这一点我们直接在sql中使用分片字段进行查询即可，就可以使用上分片字段，找到对应的库和对应的表，要是没有查询字段的话，那么查询的就是全部的库和所有的表了。

1.3.2 按照非分片字段查询

1) 冗余法

2）索引表法

创建一个中间表，里面只存储order_id和user_id，要是数据量也比较大的情况下，可以也对这张表进行分库分表，然后以order_id作为分片字段，找到对应的user_id就可以再进行查找了。其实就是找个地方存储一下他们的对应关系，匹配上就行。

3）基因法

将分片键的信息保存在想要查询的列中，这样通过查询的列就能直接知道所在的分片信息，这种方法叫做基因法。或者在我们的分片字段中保存上我们要查询的内容，比如我们的分片字段是orderId，但是我们要查询的字段是userId，所以我们就把userId通过一定的规则放在orderId中就行。
这样我们在使用orderId还是使用userId都可以通过分片策略找到对应的表

2、读写分离

引入的pom包和分库分表一样，这里只说明配置。