Mysql知识梳理（数据库的锁梳理，Mysql优化）

Mysql构成

服务层：
连接器 ：处理客户端和数据库的连接和身份认证
缓存（缓存）:后面mysql8.0 被废弃，太过鸡肋
分析器:判断SQL的意图，然后判断语法是否正确
优化器:生成对应的执行计划
执行器:调用存储引擎API，执行工作计划。

存储层：主要负责数据的存储和读取

存储引擎

是基于表的而不是数据库
常见的存储引擎有
InnoDB MyIsam memory merge csv

Mysql隐藏知识

1. InnoDB支持表锁、行锁、间隙锁、临键锁（Next-key lock），但是只有在隔离级别RR下，才会出现间隙锁和临键锁
2. 在RR级别，select语句默认使用mvcc来解决幻读。添加for update时，采用临键锁来解决幻读。mvcc不需要添加锁，是通过多个版本的快照记录来解决问题。
3. 数据库对于select语句不会自动加锁，对于DML语句（INSERT、UPDATE、DELETE），在RR级别下会自动加锁。
4. Mysql默认启用自动提交（autocommit=1），每条语句自动作为一个事务提交。SET autocommit = 0; 关闭自动提交
5. 使用commit或rollback后，事务结束。
6. MVCC机制在RC（READ COMMITTED，读已提交）和RR这两个事务隔离级别下都是有效的。

mysql中的日志

Redo Log

1. 事务开始：
   当事务执行开始时，任何对数据的修改都会先写入 redo log，而不是直接写入磁盘中的数据页。

2. 写入内存：
   修改的数据首先会写入内存中的缓冲池，并在内存中更新数据页，同时记录这次修改的操作到 redo log 缓冲区中。

3. 写入磁盘：事务提交时，InnoDB 会将 redo log 缓冲区的内容持久化到磁盘中的 redo log 文件中（即“预写日志”原则，WAL，Write-Ahead Logging），确保即使系统崩溃，仍可以通过 redo log 恢复事务。

4. 数据刷入磁盘：数据页的实际刷盘操作（即将内存中的数据页持久化到磁盘）并不需要立刻完成，数据库可以延迟将数据刷入磁盘。这意味着 redo log 能够保证数据的完整性，即使数据页还没有完全写入磁盘。

5. 崩溃恢复：如果数据库发生故障，InnoDB 会在重启时通过 redo log 进行崩溃恢复。它会根据 redo log 的内容，重做所有已提交但尚未写入磁盘的数据修改，确保数据的一致性。

Redo Log 的特性：

• 循环写：InnoDB 使用的是固定大小的 redo log 文件，它们组成一个循环日志（circular log）。当写满后，会回头覆盖最早的日志。

• 持久性保证：通过 redo log 机制，即使数据库出现崩溃，已提交的事务也能在重启时得到恢复。

• 效率提升：因为 redo log 是顺序写入的操作，相比随机写磁盘效率更高。

Redo Log 与 Binlog 的区别：

• Redo Log：由存储引擎（如 InnoDB）生成，记录数据的物理修改，用于崩溃恢复。
• Binlog：由 MySQL Server 生成，记录逻辑 SQL 操作，用于主从复制和数据库恢复。
  通过 redo log，数据库能够实现事务的持久性（Durability），即使在意外中断或崩溃的情况下，已提交的事务仍能被恢复。

undo 的工作

Undo Log 是数据库系统（如 MySQL 的 InnoDB 存储引擎）中用于实现事务回滚和**多版本并发控制（MVCC）**的重要机制。它记录了事务在执行过程中所做的修改之前的数据快照，从而支持事务的回滚以及提供一致的读视图。

Undo Log 的工作原理：

1. 事务开始：当事务修改数据时，InnoDB 会将修改前的旧数据（即原始数据）记录到 Undo Log 中，以便在事务回滚时恢复原始状态。
2. 回滚操作：如果事务执行过程中发生错误或主动执行回滚操作，数据库会使用 Undo Log 将数据恢复到修改前的状态，从而撤销事务对数据库的影响。
3. 多版本并发控制（MVCC）：在并发控制中，未提交的事务会对数据进行修改，而其他事务需要读取一致的数据快照。在这种情况下，数据库可以通过 Undo Log 提供修改前的数据版本，从而实现快照读，避免读取到未提交的数据。这样，事务可以在不加锁的情况下，读到一致性视图的数据。

Undo Log 的特性：

• 用于回滚：Undo Log 保存的是事务修改前的旧值，因此可以在回滚时使用这些旧值恢复数据库原来的状态。无论事务是主动回滚还是由于系统故障引发的回滚，Undo Log 都能确保数据恢复到事务之前的状态。

• 支持 MVCC：多版本并发控制（MVCC）依赖 Undo Log 机制来维护多个数据版本。通过 Undo Log，数据库可以为不同的事务提供不同的历史版本数据，从而实现非阻塞读操作。

• 存储方式：Undo Log 记录的数据并不会直接存储在独立的文件中，而是存放在系统表空间（System Tablespace）或者独立的 undo 表空间中。

• 删除延迟：当事务修改数据并提交时，这些修改可能会覆盖旧数据。然而，数据库并不会立即删除 Undo Log，特别是在有其他事务依赖这些旧版本数据时。只有当没有事务需要使用旧版本数据时，Undo Log 才会被回收。

Undo Log 的作用：

1. 回滚事务：当事务发生错误或中断时，Undo Log 用于撤销事务的所有修改，确保数据库的一致性。

2. 快照读（Snapshot Read）：在 MVCC 中，数据库使用 Undo Log 提供历史数据的快照，确保读操作不受其他未提交事务的影响，实现一致性读。

Undo Log 与 Redo Log 的区别：

• Undo Log：记录的是事务修改之前的数据，用于支持事务回滚和 MVCC。

• Redo Log：记录的是事务修改后的数据，用于崩溃恢复和持久化。

总之，Undo Log 在数据库中主要用于提供事务的回滚能力和支持一致性读，尤其在并发场景下，通过 Undo Log 保持数据的多个版本，使得事务在读写时能够相互独立，提高了并发性能。

数据库隔离级别问题梳理

脏读：读取到的数据是其他事务写的中间数据，该数据会被再次覆盖
不可重复读：在事务过程中两次读取到的数据是不一致的
幻读：同一个事务中，查询列表时，相同的两次查询查到的数据量不一致

Mysql数据库隔离级别有：
未提交读 RU
已提交读 RC
可重复读 RR
可串行

行锁可以分为共享锁和排他锁

mysql解决幻读的问题

在SQL92标准中RR隔离级别下并不能解决幻读的问题
但是Mysql在RR级别，通过两种方式实现了解决幻读的问题

在Mysql中查询分为两种情况

快照读

• 快照读通过MVCC（多版本并发控制）解决幻读
  当前读
• 需要通过临键锁来解决幻读
• 需要再select语句中添加for update，来显式加锁

MVCC

MVCC 运行在 RC 和 RR 这两个隔离级别下，当 InnoDB 隔离级别设置为二者其一时，在 SELECT 数据时就会用到版本链

• 在 RU 隔离级别下，直接读取版本的最新记录就 OK.
• 对于 SERIALIZABLE 隔离级别，则是通过加锁互斥来访问数据，因此不需要 MVCC 的帮助。

MVCC 的实现原理

在InnoDB中，每行数据会有隐藏列，一个是记录事务ID，一个是记录历史记录的指针（指针指向undo log 中的历史记录版本链）
ReadView （可读视图）是记录系统中事务ID的清单

具体步骤：

• 开始事务，select语句执行前，生成一个ReadView （可读视图），记录当前Mysql系统中所有还在运行的事务ID（事务ID是从小到大生成的），所以ReadView 中的事务ID存在最大值和最小值。
• 在进行select查询时，如果数据中的更新这个数据的事务ID大于视图中最大值，表示当前数据是在生产ReadView 之后更新的，所以这个版本数据不能被访问。
• 如何数据版本的事务ID小于ReadView 中最小事务ID，则表示更新数据的事务已经提交，则这个版本的数据可以访问。
• 如何数据版本的事务ID大于ReadView 最小值，小于最大值，就需要看事物ID是否在ReadView 中，如果在其中，表示事务还没有提交，不能访问，需要根据版本链去查找下一个历史版本。如果不在其中，表示事务已经提交可以访问该版本的数据。

RC和RR的差异

• RC是每次select之前就会产生一个ReadView
• RR是在事务中第一个select之前产出ReadView

Mysql的优化

内存参数调整

参数的修改需要修改配置文件my.cnf中的参数,如果通过set设置参数重启失效。

设置缓存池大小

innodb_buffer_pool_size

默认大小是134217728，单位是B，也就是128M

一般设置Mysql缓存池大小为物理内存的50-75%，如果服务器只是作为数据服务器使用，则一般设置为75%

如果缓存池占用物理内容过大，会影响操作其他其他操作，反正不能提高性能

缓存池大小=（缓存块大小*缓存池实例数）*N
缓存块数
innodb_buffer_pool_chunk_size

缓冲池实例数

innodb_buffer_pool_instances


select @@innodb_buffer_pool_chunk_size;


select @@innodb_buffer_pool_instances;

select @@innodb_buffer_pool_size/@@innodb_buffer_pool_chunk_size;

redo log数据的写入

控制redo log刷新到磁盘的策略。

默认使用innodb_flush_log_at_trx_commit=1

0表示事务提交，并不立刻将redo log日志写入磁盘文件，而是等Mysql主线程刷新磁盘的时候写入。服务宕机时会导致数据丢失。
1 表示事务提交，立刻将数据写入磁盘文件。
2将数据写入操作系统缓存，然后操作系统根据时机写入磁盘。

sync_binlog

同步binlog的时机，=1强制事务提交强制刷新到磁盘

日志缓存大小

innodb_log_buffer_size决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作

select @@innodb_log_buffer_size

设置排序缓存大小

Mysql的 Filesort排序有两种方案
1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定使用哪种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

show variables like 'max_length_for_sort_data';
show variables like 'sort_buffer_size';

Mysql并发参数层面

1. 最大连接数
   根据服务器配置用途判断设置连接数，性能好的服务器，支持 500-1000 个连接不是难事。同时可以在系统运行过程中，通过连接状态参数判断连接数是否出现瓶颈。

通过查询状态参数
SHOW STATUS LIKE ‘Threads_connected’;
查询连接数
SHOW STATUS LIKE ‘connection_errors_max_connections’;
查询连接错误数量，不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这时可以考虑增大max_connections 的值。

2. back_log（积压请求栈大小）
   当达到最连接数时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。
3. 客户服务线程数
   缓存客户服务线程的数量，类似于线程池中核心线程的个数。
4. 表缓存数量
   执行线程可打开表缓存个数
5. 锁等待时间
   事务等待行锁的时间，对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的
   批量处理程序来说， 可以将行锁的等待时间调大， 以避免发生大的回滚操作。

select @@max_connections

select @@thread_cache_size

select @@lock_wait_timeout

select @@back_log

select @@table_open_cache

sql层面的优化

定位问题SQL

1. 通过Mysql慢日志记录
   1. 首先查看慢日志开关是否开始，show variables like ‘%slow_query_log%’;
   2. 查看设置慢查询时间，show variables like ‘long_query_time%’;
   3. 到慢日志文件中查看各SQL的耗时
   4. 定位问题SQL
2. 通过show processlist查看
   

可以通过info字段查看当前长时间执行的SQL语句

explain使用

SQL优化的核心点是SQL执行是否走索引
通过在SQL前面天explain会显示SQL的执行计划，通过对执行计划的分析来侧重优化SQL

explain SELECT * FROM role WHERE id = (SELECT rid FROM admin_role WHERE admin_id = (SELECT id FROM admin WHERE name = '韩愈'))

属性如下

• id，查询的序列号，序列号相同，从上到下执行。序列号不同时，序列号越大执行优先级越高
• select_type,表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
• table,查询的表
• partitions 属性表示查询涉及的分区。如果表使用了分区，该列会列出查询访问的具体分区名称；若未使用分区，则该列为 NULL。
• type，表的访问类型
  • NULL MySQL不访问任何表，索引，直接返回结果。
  • system 表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现。
  • const 表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const会将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
  • eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
  • ref 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
  • range 只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in等操作。
  • index index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。all 将遍历全表以找到匹配的行
• possible_keys : 显示可能应用在这张表的索引， 一个或多个。
• key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
• key_len : 表示索引中使用的字节数。len=3*n+2（n为索引字段的长度）。如何是联合索引，则是索引中各字段的索引长度之后。
• rows ,扫描行的数量。
• extra,其他的额外的执行计划信息，在该列展示 。
  • using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。( EXPLAIN select * from t_user u order by u.name desc;使用非索引字段排序)
  • using temporary，使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by 和 group by； 效率低
  • using index 表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

保证索引生效

• 组合索引，做到全值匹配（如果不能则保证最左原则）
• 组合索引，查询条件出现范围查询时，后面的索引字段不能生效
• 组合索引，使用覆盖索引查询，减少回表查询
• 组合索引，使用or的时候，保证or两边的条件是索引列
• 组合索引使用覆盖索引能解决like中%关键词%走索引的问题
• 保证索引字段的效率比全表高。（如在性别字段添加索引，数据中男的数据量大，则会走全表扫描）
• 不能在查询条件中出现索引列运算
• 字符类型的参数不加单引号，会导致优化器自动做类型转换导致索引失效。
• in 走索引， not in 索引失效。
• 尽量使用复合索引，而少使用单列索引（一个复合索引，相当创建多个索引）

在SQL中设置强制走索引

• 在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表
  explain select * from tb_seller use index(idx_seller_name) where name=‘小米科技’

• 如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index
  explain select * from tb_seller ignore index(idx_seller_name) where name = ‘小米科技’;

• 为强制MySQL使用一个特定的索引，可在查询中使用 force index
  explain select * from tb_seller force index(idx_seller_address) where address =‘北京市’;