作为Java开发你需要知道的MySQL基础知识汇总

1.MySQL逻辑架构

Server层：连接器、查询缓存、分析器、优化器、执行器等。

存储引擎：InnoDB、MyISAM、Memory等。

连接器：负责跟客户端建立连接、获取权限、维持和管理连接；

查询缓存：之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。执行更新则清空缓存；

分析器：词法分析、语法分析；

优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；

执行器：判断是否有表权限，执行引擎提供的接口，扫描行将所有满足条件的合并到结果集返回给客户端。

2.redo log（重做日志） 和 binlog（归档日志）

WAL（Write-Ahead Logging）：先写日志，再写磁盘。

redo log：用于保证 crash-safe 能力（保证服务器宕机后能恢复已提交事务的数据），只有InnoDB有，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

binlog：MySQL的Server层实现的，所有引擎都可以使用。

redo log与binlog的区别:

  1. redo log是InnoDB独有的，binlog属于MySQL；
  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”；
  3. redo log 循环写，有固定空间；而binlog是追加写，可以有多个文件，不会覆盖。

redo log的两阶段提交：更新操作之后写入redo log此时是prepare状态，然后写入binlog，事务提交之后redo log改为commit状态，用于保证两个日志的一致性。

脏页：指的是内存的数据页跟磁盘数据页不一致称为脏页。

刷脏页场景：

  1. redo_log满了，需要暂停所有更新操作，推进check_point，将脏页刷到磁盘上，腾出空间；
  2. 系统内存不足，需要淘汰数据页，被淘汰的数据页是脏页的话则需要flush到磁盘；
  3. 当系统空闲的时候，只要有机会就会flush；
  4. 当MYSQL正常关闭的时候，也会flush掉所有的脏页。

InnoDB刷脏页策略：如果一个查询要淘汰的脏页过多，或者redo_log写满，会大大影响查询和更新的性能；所以可以通过调整脏页比例和redo_log写盘速度来控制刷脏页的速度。

3.事务隔离：读未提交、读提交、可重复读、串行化

实现方式：创建视图，“可重复读”在事务启动时创建视图；“读提交”在每个SQL开始执行时创建；“读未提交”没有视图，直接读最新值；“串行化”通过加锁避免并行访问。

“可重复读”实现方式: MVCC(多版本并发控制)，通过事务创建时的视图和回滚日志来实现重复读。

可重复读的核心是一致性读，而事务更新数据的时候，只能用当前读，如果当前事务的记录的行锁被其他事务占有，则会进入锁等待。

当前读：每次更新需要读取当前的最新值，这里会加行锁。

可重复读和读提交的区别：可重复读是在事务启动的时候创建一致性视图，之后事务内的所有查询都是共用这一个视图；读提交则是每执行一条SQL语句前重新计算新视图。

MVCC：每行数据会存在多个版本，即row trx_id，每次事务更新的时候，都会生成一个版本。通过undo_log来计算旧版本的值。

幻读

幻读：指的是当进行一致性读的时候，读到了其他事务新插入的行。

幻读造成的影响：因为行锁只能锁住当前行，如：select * from t where d=5 for update(d是一个普通索引)，而其他事务如果update了其他行的d改为5，就不能锁住新更新的d=5的记录了，新插入的数据d=5也是同理，所以无法阻止其他事务更新和插入。

  1. 语义：指的是破坏了加锁声明，加了行锁发现没用，其他事务还是能更新；
  2. 数据一致性：破坏了数据内部状态此刻的一致性和日志的逻辑一致性；

InnoDB解决幻读的方式是新引入了间隙锁(Gap Lock)。

间隙锁：锁的是两个值之间的空隙，确保了不能插入新的记录，间隙锁与读锁、行锁以及自己都不会发生冲突，只会跟新插入的记录冲突。

next-key lock：指的是间隙锁和行锁的统称，格式是前开后闭区间((-∞,m)...(n,supernum])，闭区间是InnoDB给每个索引加了一个不存在的最大值supremum。

间隙锁的引入可能会影响到并发度，而且可能会出现死锁，如两个事务加了一样的间隙锁，然后他们又执行一样的插入语句，此时都需要等待对方释放间隙锁才能插入，就形成了闭环。

4.索引

常见模型：哈希表、有序数组和搜索树

哈希表：适用于等值查询，不适合范围查询；

有序数组：适用于静态数据存储，数组优势等值查询和范围查询效率高，但是插入更新效率低；

二叉搜索数：查询效率最快，查询复杂度O(log(N))，但不适用于实际数据库存储，树的层数太高导致需要多次访问磁盘，常用是多叉树，最多访问3次。

B+树：主键索引（聚簇索引）的叶子节点存储整行数据，非主键索引（二级索引）的叶子节点存储主键的值。

一般情况下尽量使用自增主键，防止页分裂，提高空间利用率。如果只有一个唯一索引（相当于K-V）则可以使用业务主键。

覆盖索引：一般如果索引上的数据不满足查询需要的结果，则需要再通过主键ID再去主键索引数上查询整行数据，返回需要的字段数据，这个过程称为回表；如果查询只需要主键ID，而这个ID索引数上有，则不需要进行回表，直接返回结果，即覆盖索引。

最左缀原则：联合索引可以引用最左缀原则，从左边开始匹配，字符串如“张%”也可以满足。

索引下推：MySQL 5.6提供的功能，即可以在索引遍历的过程中，提前过滤掉不满足条件的结果，不需要进行回表查询。

change buffer：当需要更新一个数据页的时候，如果这个数据页在内存中就直接更新，如果不在，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，下次访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，这个过程叫做merge，系统后台线程也会定期merge，数据库正常关闭时也会merge。change buffer减少了读磁盘，避免占用内存，提高内存利用率。

change buffer只能普通索引使用，而且适用于写多读少的场景，如账单、日志类系统，过多的merge反而会增加IO次数及change buffer的维护。

在更新收益上，redo_log是节省随机写磁盘的IO消耗（转为顺序写），change_buffer是减少随机读磁盘的IO消耗。

MySQL选错索引解决方案：

  1. 用focus index指定一个索引
  2. 修改SQL语句逻辑，引导MySQL使用正确的索引
  3. 把误用的没用的索引删掉或新加一个更合适的索引

MySQL索引放弃使用搜索树的案例：

  1. 条件字段函数操作：对索引字段做函数操作，会破坏索引有序性，不能使用搜索树功能，从而只能遍历整个索引；
  2. 隐式类型转换：等式左右两边类型不同，触发类型转换，如果索引字段（等式左边）发生转换，也就是对索引字段做函数操作，一样会放弃使用搜索树功能；
  3. 隐式字符编码转换：用连接查询的时候，表之间的字符编码不同，被驱动表触发字符编码转换（转换函数），导致放弃使用搜索树功能；

order by的排序原理：

如果order by的字段是索引，则天然有序；

若无序，则会有两种情况：

  1. 全字段排序：MYSQL会在内存中分配一块sort_buffer用于排序，将需要返回的字段放入sort_buffer，根据满足条件的主键id查询行取出对应字段的值放入sort_buffer，然后按照条件进行排序；如果数据量小于sort_buffer_size则排序直接在内存中进行，如果超过则需要用磁盘临时文件辅助排序；将数据分成多份临时文件进行排序，最后用归并排序合成一个有序的大文件。
  2. rowid排序：如果排序的单行数据太大，就会分成很多份临时文件，效率很低，则会优化成用rowid排序；rowid排序就是将主键id和排序字段值放入sort_buffer中进行排序，排好序后根据主键id回表返回需要的字段结果。

MYSQL的排序思想是如果内存够，尽量用内存，减少磁盘访问次数，在InnoDB表中，rowid排序会有多次磁盘读，所以一般不会被优先使用，如果我们在排序的字段和需要返回的字段上组成一个联合索引，就可以使用到覆盖索引，不需要回表，而且索引天然有序，查询效率会大大提升。

随机排序：可通过随机生成最大行数内的随机数结合limit(r,1)来随机获取行数据，如随机获取某三条数据：

select count(*) into @R from t;
set @A = floor(@R * rand());
set @B = floor(@R * rand());
set @C = floor(@R * rand());
假设随机数从小到大排序为@A @B @C
select * from t limit @A,1; // id1
select * from t where id > id1 limit @B-@A, 1 //id2
select * from t where id > id2 limit @C-@B, 1

5.MySQL的锁：全局锁、表锁、行锁

全局锁：对整个数据库实例加锁，FTWRL全局读锁，典型应用场景是做全库逻辑备份，如果存储引擎支持可重复读（MVCC支持），则直接是用mysqldump来做备份。

表级锁：分为表锁和元数据锁（MDL），表锁是锁住整个表，并发小，一般不推荐；MDL作用是保证数据读写的正确性，MySQL5.5引入的，如果对表做增删改查则加MDL读锁，如果对表结构作更新则加MDL写锁；读锁之间不互斥，可以有多个线程同时增删改查；读写锁和写锁之间互斥，保证表结构变更操作的安全性。

注：MDL锁是事务提交之后才会释放，所以对表结构更新需要注意，申请写锁之前需要释放读锁，申请读锁需要释放写锁，所以会被阻塞。

行锁：InnoDB支持，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。所以在实际场景中一般把需要加行锁的操作放到后面，减少持有锁的时间。

死锁检测：每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，在并发更新同一行的时候，需要耗费大量CPU资源。

预防死锁检测应对方案：

  1. 确保业务不会出现死锁，临时关掉死锁检测；
  2. 削峰，控制并发度，在数据库服务端做并发控制，可以用中间件或者修改MySQL源码，对于相同行的更新，在进入InnoDB引擎前排队，减少死锁检测工作；
  3. 通过将一行改成逻辑上的多行来减少锁冲突，如本来一条记录分成10条记录然后选其中之一进行更新，减少冲突概率。

查询语句响应慢的可能原因：

  1. 等MDL锁：当前有一个线程正在请求或者持有MDL写锁，导致查询语句获取MDL读锁需要等待；
  2. 等flush：当前有线程正在等待表flush， flush tables 命令被别的语句堵住了，然后它又堵住了 select 查询语句；
  3. 等行锁：当前有事务持有写锁，导致查询那一行数据时需要等待获取读锁；
  4. 在事务可重复读的时候，查询需要一致性读，此前其它事务更新了这一行数据100万次，则根据MVCC版本控制，会生成100万个回滚日志，需要执行100万次回滚才能读取到结果。

6.MySQL主备架构：主库用来读写，备库用来一部分读

MySQL保证主从一致：主库用来读写，备库readonly，与主库数据同步，readonly对超级权限无效。

主备同步流程：

  1. 备库通过change master设置主库IP，端口，用户名密码，binlog文件名及偏移量；
  2. 备库执行start slave，启动io_thread与主库进行连接，以及sql_thread；
  3. 主库A校验用户名密码，按照备库传过来的位置，本地读取binlog发送给备库；
  4. 备库拿到binlog后，写入到本地的中转日志，即relay-log；
  5. sql_thread读取relay-log，解析命令并执行。

MySQL双M结构（双主结构）循环复制解决逻辑：

1.两个库的sever_id必须不一致；
2.备库在重放binlog的时候，生成新的binlog的server_id与原binlog的一致；
3.每个库收到自己主库发过来的binlog，判断server_id，如果一致则表示是自己生成的，直接丢弃。

主备延迟的可能：

  1. 备库性能较差
  2. 备库的压力大，可以用一主多从来解决
  3. 大事务，一次性删除太多数据或大表DDL
  4. 备库的并行复制能力

主备延迟下主备切换策略：

可靠性优先策略：主备切换的过程会有不可用的状态，取决于seconds_behind_master。

  1. 判断备库的seconds_behind_master是否小于设定的值，小于则进行下一步，否则重试
  2. 将主库设置为只读状态（read-only）
  3. 判断备库的seconds_behind_master的值，直到为0
  4. 把备库的read-only设置为false
  5. 业务请求切换到备库

可用性优先策略：直接切换到备库并设置可读写，高可用，可能出现数据不一致的情况。

一般实际场景通常使用可靠性优先策略保证数据准确性，通过减少主备延迟提高可用性。

备库并行复制：coordinator负责读取中转日志和分发事务。

分发原则：1.更新同一行的事务需分发到同一个worker；2.同一个事务不能拆分，只能分发到一个worker。

MySQL5.5并行复制策略：

  1. 按表分发策略：每个worker对应一个hash表，key是库名.表名，value是一个数字表示多少个事务在修改这张表；
    处理冲突：1.如果所有worker不冲突，coordinator线程就会分给最空闲的线程；2.如果存在多个冲突，则进行等待，直到冲突只剩一个；3.如果只跟一个worker冲突，则直接分发给这个worker。
    这个策略适用于表负载均衡的场景，不适合热点表场景，会退化成单线程复制。
  2. 按行分发策略：key是库名+表名+索引名+索引的值，binlog必须是row格式。相比于按表分发并发度更高，但是不适合大事务场景，会占内存和CPU，处理方案是设置阈值，超过指定行数的事务退化成单线程复制处理。

MySQL5.6并行复制策略：按库分发，热点表均匀分配在多个库，key是库名，优势是构建key名快，binlog格式不严格，由于需要特地移动数据，所以不常用。

MySQL5.7.22并行复制策略：基于 MySQL 5.5 版本的按行分发的策略，主库在binlog中写入writeset，key是库名+表名+索引名+索引值唯一标识；
优化：备库执行时减少了计算量，根据writeset直接分发，不需要扫描所有binlog，不要求binlog格式，通用性高。如果表没有主键以及有外键约束则退化成单线程复制。

一主多从的主备切换：

  1. 基于位点的主备切换：切换之后需要找到同步位点（由备库寻找），为了防止丢失数据，总是需要找比较靠前的位点，再通过判断跳过那些在从库已经执行过的事务
  2. 基于GTID的主备切换：同步位点由主库内部已经找好，需要保证发给备库的日志是完整的，直接根据主库和备库的set_id差集判断需要执行的事务。

一主多从结构下的读写分离方案：读写分离的目标主要就是分摊主库的压力。

1.** 客户端直连：由客户端来选择连接对应的库进行查询；
2. proxy转发：**根据请求路径和上下文决定请求分发的路由。

读写分离主备库延迟下处理过期读的问题：

  1. 强制走主库方案：对于更新后必须拿到最新结果的查询直接强制走主库查询
  2. sleep方案：请求查询的时候先执行 sleep命令，主备同步延迟时间
  3. 判断主备无延迟方案：只有主备无延迟才进行查询，具体方式有三种：1.每次查询前判断seconds_behind_master 是否为0，2.对比位点，3.对比GTID集合

semi-sync：事务提交时，主库把binlog发给从库，从库收到后返回ack，主库收到ack再返回给客户端。这种模式不适用于一主多从。

等主库位点和等主库GTID方案，如果不一致则走主库查询。

判断主库是否出现问题：1.外部统计，更新系统表 2.内部统计，检测performance_schema

误删数据的处理方案：

  1. 误删行：Flashback恢复工具，原理是修改binlog内容拿回原库重放，最好不要在主库恢复。
  2. 误删库/表：恢复全量备份数据作为临时库，再用binlog恢复之后的数据。
    可通过搭建延迟复制备库减少恢复数据的时间。
  3. 预防误删表/库方法：1.账号权限分离 2.制定操作规范，删除之前先改表名（固定后缀），确认之后再删掉。

7.临时表

临时表与内存表的区别：内存表是Memory引擎的表，表数据存在于内存，重启会清空数据保留结构，跟基本表差不多；而临时表可应用于各种引擎，可以与普通表重名，临时表只存在于自己的session中，用InnoDB或MyISAM引擎写数据的时候是直接写到磁盘的。

临时表的特性:

  1. 建表语法是 create temporary table …。
  2. 一个临时表只能被创建它的事务访问，对其他线程不可见；
  3. 临时表可以与普通表同名，不同session的临时表也可以同名，临时表的命名规则：库名+表名+server_id+线程id；
  4. 事务内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表；
  5. show tables 命令不显示临时表；
  6. 临时表在线程退出之后会自动删除；
  7. 在 binlog_format='row’的时候，临时表的操作不记录到 binlog。

临时表的应用：应用于各种复杂的查询场景，如分库分表的跨库查询，建立一个临时表，将各个分库拿到的数据汇总到临时表内进行逻辑操作。

MySQL应用内部临时表的场景：

  1. union：union语义是合并结果集，取的是并集（不包括重复行），所以在进行union的时候，MySQL会使用一张二维临时表，这张表有一个主键字段，先插入union左边的结果集，union右边的结果集插入的时候，如果违反唯一性则插入失败，最后返回临时表的数据，如果是union all不需要去重则不需要使用到临时表。
  2. group by：如果group by的字段是无序的，则会用到临时表和sort_buffer，先把结果放入临时表，再用sort_buffer进行排序，最后返回结果。如果临时表内存空间不足则会转换成磁盘临时表。所以如果group by后面的字段是索引（有序）则不会触发排序，可以直接顺序扫描得到最终的结果直接返回，就不会用到临时表。如果数据量太大且需要排序，则在查询语句加上‘SQL_BIG_RESULT’让MySQL直接使用排序算法优化，也不会用到临时表。

MySQL使用内部临时表的条件：

  1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
  2. join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；
  3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。

生产环境中为什么不建议使用普通内存表：

  1. 锁粒度问题: 内存表只有表锁，所以并发很低；
  2. 数据持久性问题：内存表重启之后数据会全部被清空。

8.MySQL的自增主键

InnoDB的自增主键存储：MySQL5.7及之前的版本都是保存在内存中，没有持久化，重启的时候根据max(id)+1获取当前自增值，MySQL8.0则放入了redo_log，重启时根据redo_log恢复自增值。

引起自增不连续的原因：

  1. 唯一键冲突
  2. 事务回滚
  3. 批量生成自增id策略
posted @ 2021-11-06 16:50  Conwie  阅读(244)  评论(0)  编辑  收藏  举报