MySQL

最新推荐文章于 2023-03-23 15:00:00 发布

atlantis_c

最新推荐文章于 2023-03-23 15:00:00 发布

阅读量724

点赞数

文章标签： mysql java 数据库

本文链接：https://blog.csdn.net/atlantis_c/article/details/125792819

版权

MySQL知识点

索引篇

一、索引概述

1.1索引的定义：

索引就是帮助MySQL高效获取数据的数据结构。

1.2聚簇索引

特点：
- 使用记录的主键值大小进行记录和页的排序，页内的记录是有序的单链表结构，页与页之间是有序的双向链表，叶子节点存储完整的记录。
优点：
- 访问数据快，对于主键的范围查找和排序查找速度很快。
- 可以减少大量的IO操作。
缺点：
- 插入速度严重依赖插入的顺序，因为主键是有序的。
- 更新主键的代价很高，所以一般定义主键为不可更新。

1.3二级索引（非聚簇索引）

特点：

只存储主键和当前列的值，并不是完整的记录，若查找其他字段，需要回表。
回表：

如果想要根据当前列查找完整的用户记录，仍然需要到聚簇索引中再次查找一边，这个过程叫做回表。

1.4联合索引

多个字段同时建立一个索引。叶子节点存储这些字段的值和主键。
遵循最左前缀原则，会先按最左边的字段值排序，如果存在相同值，则再按照第二个字段的值排序。所以要把经常查询的字段放在最左边。

1.5聚簇索引与非聚簇索引的区别

一张表只有一个聚簇索引，但是可以有多个非聚簇索引。
非聚簇索引只存储主键和当前列的值，聚簇索引存储完整的记录。
通常情况下聚簇索引只需要查一次，而非聚簇索引需要回表。
只有Innodb有聚簇索引。

1.6与MyISAM索引的区别

MyISAM索引叶子节点存储的是地址，而innodb对于聚簇索引来说存储的是完整的记录，对于非聚簇索引来说存储的是当前列和主键，MyISAM只有非聚簇索引。
MyISAM索引文件和数据是分离的，索引只保存记录的地址，而innodb的数据本身就是索引。
MyISAM的回表操作是非常迅速的，因为他是拿着地址取数据，而innodb是先找主键再去聚簇索引中找记录。

1.7索引的代价（缺点）

空间上的代价：每建立一个索引，都要创建一棵B+树，每一个节点存储一个数据页，一个页的默认大小是16KB。
时间上的代价：创建索引和维护索引都要耗费时间，对表数据进行增、删、改，索引也会动态的维护，降低更新速度。

二、索引的几种数据结构

2.1hash结构（例如hashmap）

不采取hash结构的原因（缺点）：
1. 因为数据存储没有顺序，所以范围查询复杂度O(n)，不如树形结构的O(log2N)。并且在ORDER BY情况下hash索引还需要重排序。
2. 对于联合索引的情况，hash值是合并计算的，无法对单独的一个或几个字段进行查询。
3. 在重复值特别多的情况下，效率很低。
采用hash索引的情况：
1. Redis的存储核心是hash表。
2. Memory引擎支持hash索引。
3. innodb本身不支持hash索引，但是提供hash表，当某个数据经常被访问，在满足一定条件时可以将其数据页的地址放入hash表。

2.2B树

与B+树最主要的区别在于，非叶子节点（目录页）页也要存放记录。

2.3B+树

与B树的区别：
1. B+树的非叶子节点的关键字同时也页会存在在叶子节点中，并且为最大值或者是最小值。B树不会。
2. B+树的非叶子节点仅用于索引，不存储记录，跟记录有关的信息都存放在叶子节点中。B树中，非叶子节点也要存放记录。
3. B+树页内记录之间是有序的单链表，页与页之间是有序的双向链表。B树中没有这种结构。
相比于B树的优点：
1. B+树的查询效率更高，同样磁盘页的大小B+树可以存储更多的关键字（树的高度就越矮，IO次数就越少）。
2. 对于范围查找来说，B+树效率更高，因为记录都存储在叶子节点中，记录之间，页之间都是有序的链表，可以通过指针链接查找。而B树需要中序遍历。
B+树的存储能力如何？
- innodb中默认页（基本单位）的大小是16kb，一个页大概可以存储1000条记录。那么一棵深度为3的B+树可以存储1000的三次方条记录。

三、innodb数据存储结构

3.1数据库的存储结构-页

是磁盘于内存交互的基本单位，一个页默认大小是16kb，也就是说，一次最少从磁盘中读取16kb内容到内存，最少将16kb内容刷新到磁盘。IO操作的最小单位是页。

3.2页的结构

页与页之间可以不再物理结构上相连，只需要通过双向链表相关联就可以。

3.3页的内部结构

名称	占用大小	说明
File Header	38字节	文件头，描述页的信息
Page Header	56字节	页头，页的状态信息
Infimum+Supremum	26字节	最大和最小记录，2个虚拟的行记录
User Records		用户记录，存储行记录内容
Free Space		空闲记录，页中还没有被使用的空间
Page Directory		页目录，存储用户记录的相对位置
File Trailer	8字节	文件尾，校验页是否完整

双向链表的实现方式：文件头中prev、next。
单链表的实现原理：行格式中的，记录头信息中的，next_record地址偏移量。

3.4页的上层结构

为什么要有区？

以页为单位分配空间，物理位置可能离的很远，这样是随机IO的过程，引入区，一个区在屋里位置上有连续的64个页，尽量减少了随机IO。
为什么要有段？

比如区分叶子节点和非叶子节点：分为叶子节点段和非叶子节点段，还有一些特殊的数据定义的段，比如回滚段、索引段、数据段。段是一个逻辑上的概念，并不对应物理区域。
为什么要有碎片区？

以完整的区为单位分配给某个段，对于数据量小的表太浪费存储空间，引入碎片区可以存放多个段的页。碎片区直属于表空间；

所以先从碎片区分配存储空间，当某个段占用了32个碎片区后，申请完整的区。
表空间：

表空间是一个逻辑容器，逻辑上的最高层。

四、索引的设计原则

4.1适合创建索引的情况

唯一约束的字段
频繁作为WHERE查询条件的字段
经常GROUP BY \ ORDER BY的列
DISTINCT的字段
多表JOIN，对于WHERE条件、用于连接的字段创建索引
使用类型小的、区分度高的列
使用最频繁的列放在索引的左侧
多个字段都要创建索引的情况下，联合索引更优

4.2不适合创建索引的情况

数据量小不使用索引
有大量重复数据的列不建立索引
经常更新的表不建立过多索引
不定义冗余或者重复的列
使用很少或不再使用的索引进行删除
无序的值不适合作为索引
WHERE、GROUP BY 、 ORDER BY使用不到的字段

五、执行流程与存储引擎

5.1执行流程

客户端sql==》查询缓存（8.0之后被抛弃）》解析器解析（词法、语法分析，生成语法树）》查询优化器（逻辑、物理查询优化）==》存储引擎执行

（调用存储引擎API）

5.2存储引擎

innodb存储引擎
- 特点：
  1. 支持外键
  2. 支持事务
  3. 具有行级锁
  4. 适合更新和删除多的场景
- 缺点：
  1. 数据量少的情况小性能较差，更适合用于巨大数据量的场景
  2. 内存要求较高（数据和索引存放在一起，.ibd文件中）
MyISAM特点：
1. 访问速度快，适用于select、insert为主的应用。
2. count（*）效率很高，有额外的常数存储数据统计。
3. 不支持事务、外键、行级锁。
Archive：归档的意思，用于数据存档，适用于日志、数据采集应用
CSV：作为一种数据的交换机制
Memory：置内存的表，适用于数据量较小，且访问非常频繁的场景

事务与锁篇

一、事务概述

1.1事务的ACID特性

原子性：

指事务是一个不可分割的工作单位，要么全部提交要么全部失败回滚。
一致性：

指数据从一个合法状态变换到另一个合法状态，是语义上的，与具体业务有关。

例如：将姓名设置为唯一约束，若一个事务提交或回滚导致姓名不一致，则破坏了一致性
隔离性：

指事务的执行不能被其他事务干扰，即一个事务内部对数据的操作对于其他事务是隔离的。
持久性：

一旦事务提交，那么这些修改就永久保存下来了。

1.2事务的隔离级别

脏写：事务a修改了，还没有提交的事务b修改过的数据。
脏读：事务a读取了，已经被事务b更新但还没有提交的数据。
不可重复读：事务a读取了一个字段，然后事务b更新了该字段，事务a再读取该字段，值就不同了。
幻读：事务a读取一个字段，然后事务b在表中插入了新的行，事务a再读同一个表，就会多出几行。

1.3sql中的四种隔离级别

读未提交：解决脏写
读已提交：解决脏读
可重复读：解决不可重复读（MySQL默认）
串行化：都可以解决

二、事务日志

2.1redo log（重做日志）

作用

提供再写入操作，恢复数据，用来保证持久性。记录的是“物理”层面上数据的修改操作。
为什么需要？
- 首先，innodb在访问页之前，需要先将磁盘中的页缓存到内存中的buffer pool，才可以进行操作。所有的变更操作必须在buffer pool中进行，完成层后，再以一定的频率被刷入磁盘，通过缓冲优化CPU与磁盘之间的鸿沟。
- 其次，刷盘的操作是master线程每隔一秒才进行处理的，所以可能会发生：事务提交后，刚写完缓冲池，数据库宕机，那么这份数据就丢失了，无法恢复。就不满足数据库的持久性原则。
- 如果说，在事务提交之前把所有的更改刷盘，这种方式的问题在于：
  1. 随机IO的方式刷盘很盘，磁盘中的页可能是不相邻的。
  2. 只修改1个字节，刷新16kb的工作严重不成比例的情况。
- 所以，将事务修改的数据先记录到redo日志文件中，先写日志，再写磁盘的方式。只有日志写入成功才算提交成功。
优点：
1. 降低了刷盘的频率，并且redo日志占用空间小
2. 事务执行过程中，redo log不断记录，并且是顺序IO。
redo log的工作流程
1. redo log由两部分组成：buffer（内存中的）和 file（磁盘中的）
2. 事务从磁盘获取数据==》先在内存中操作==》完成后写入redo log buffer==》根据刷盘策略写入redo log file，也称刷盘（这个过程会先写到操作系统层面的page cache，再到file）
redo log刷盘策略

由innodb_flush_log_at_commit决定：
- 0：每次事务提交不进行刷盘操作，由后台线程操作（最快，mysql宕机丢失上一秒数据）
- 1：每次事务提交时进行刷盘操作（默认值，最慢也是最安全的）
- 2：每次事务提交时只把redo log buffer写入 page cache，由操作系统决定什么时候刷盘（操作系统宕机，丢失上一秒数据）
- 【注】：innodb有一个后台线程master，每隔一秒进行刷盘操作。

2.2undo log（回滚日志）

作用
- 回滚记录到某一个特定的版本，用来保证原子性、一致性的操作。在事务中，更新数据的前置操作先写入undo log，记录的是逻辑操作日志，类似sql语句，用于回滚和MVCC。
- undo日志是逻辑日志（物理层面的改动不会恢复）。
两种类型
- insert undo log： insert只对事务本身可见，对其他事务不可见，故事务提交后可直接删除。
- update undo log：记录update和delete操作，需要提供MVCC机制，不可以提交时删除。
关于隐藏列
- DB_ROW_ID：没有主键和唯一索引的情况，自动生成一个隐藏列为主键
- DB_TRX_ID：每个事务都会分配一个id，当记录发生变更时，记录事务的id
- DB_ROLL_PTR：回滚指针，指向undo log的指针

三、锁

3.1写-写情况

可能会发生脏写的情况，任何一种隔离级别都不允许，通过锁实现，由事务生成一个锁结构与当前记录关联。

锁结构

trx信息：代表这个锁是哪个事务生成的。

is_waiting：当前事务是否在等待

3.2读-写情况

可能出现脏读、不可重复读、幻读问题。

解决方案
1. 读操作使用多版本并发控制MVCC，写操作加锁。
2. 读写操作都加锁。（有些场景不允许读记录的旧版本，必须读新的版本）

3.3从数据操作的类型划分

读锁
- 也称共享锁，同一份数据，多个事务的读操作可以同时进行而不阻塞。
- 读锁也可以添加共享锁和排他锁。（SELECT…FOR SHARE\SELECT…FOR UPDATE）
- 只有共享锁之间是互相兼容的，只要由排他锁就会阻塞。
- 5.7以前，获取不到锁会一直等待，直到超时，8.0可以添加报错、跳过锁等待等方式。
写锁
- DELETE
  - 先在B+树中定位，然后获取排他锁，执行delete mark操作
- UPDAE
  1. 未修改记录的键值，且被更新的列占用空间修改前后没有变化，排他锁
  2. 未修改记录的键值，但是存储空间有变化
    - 先将该记录彻底删除，再插入一条新的记录（隐式锁保护）
  3. 修改了记录的键值，相当于先delete再insert
- INSERT
  - 一般情况下不加锁，隐式锁保护

3.4从数据操作的粒度划分

3.4.1表锁

mysql最基本的锁策略，并不依赖存储引擎，避免死锁的问题，并发效率差。

innodb一般不使用表锁，myisam使用。

意向锁
- innodb支持多粒度锁，允许行锁与表锁共存，意向锁就是一种表锁。
- 给某一行添加排他锁，数据库会自动给更大一级的空间加意向锁，代表已经加过锁了，由存储引擎自己维护。
- 避免为了判断表是否存在行锁而去扫描全表的系统消耗。
- 意向锁之间互不排斥，并且只会与表级锁互斥（只有is和s兼容）。
自增锁
- 向AUTO_INCREMENT的列插入数据时，获取的特殊的锁。
元数据锁
- 保证数据再表结构做变更时，读写的正确性。在DDL操作时写锁，在DML操作时加读锁

3.4.2行锁

锁住某一行记录，只在存储引擎层实现，锁的粒度小，并发度高，缺点是开销比较大，会发生死锁。

记录锁
- 分为共享和排他
间隙锁
- mysql在可重复读隔离级别中可以解决幻读问题，一是加锁，二是MVCC，而间隙锁就是解决幻读问题，无法给幻影记录加锁的问题
- 如果给一条记录加gap锁，则该记录的前边间隙不允许插入新纪录。gap锁仅仅是为了防止插入幻影记录，对他来说，共享和独占锁的作用是相同的，不会影响该记录加别的锁。
- 死锁问题：两个事务加相同的间隙锁，又都在相同的区间插入数据。
临键锁
- 本质就是一个记录锁+gap锁
插入意向锁
- 插入意向锁是一种gap锁，当一个事务在插入一条记录时，需要判断插入位置是否被别的事务加了gap锁，如果有需要等待，innodb规定在等待时页需要生成一个锁结构，表明有事务想在某间隙插入新纪录。与间隙锁一样，不会阻止事务继续获取该记录的其他锁。

3.4从对待锁的态度划分

悲观锁
- 总是假设最坏的情况，每次拿数据都会认为别人会修改，所有每次在拿数据时都会上锁。（资源只给一个线程使用，其他线程阻塞）
- SELECT…FOR UPDATE就是mysql中的悲观锁，。并且语句执行过程中所有扫描的行都会上锁，因此使用悲观锁必须确定使用了索引，而不是全表扫描。
- 缺点：不适用场景较多，对于长事务开销往往不能承受。
乐观锁
- 不采用数据库的锁机制，通过程序实现，适用于多读的应用。
- 版本号机制：在表中设计一个版本字段version，对数据进行更新或者删除操作时，判断version相同才进行操作，操作成功后会修改version的值。

3.5按加锁的方式来分

隐式锁
- 通常用于插入操作，如果一个事务先插入了一条记录，然后另外一个事务要获取这条记录的s或者x锁，此时会触发隐式锁。
  1. 对于聚簇索引来说：有一个trx_id的隐藏列，记录最后改动该记录的事务id。此时其他事务相对该记录添加锁时，会先看trx_id中是否是活跃的事务，如果是活跃的事务，就帮助当前事务创建一个x锁，然后再为自己创建一个锁结构，自己进入等待状态。
  2. 对于二级索引来说：页结构的page header有一个page_max_trx_id，记录事务id，然后重复情况1。
显示锁

3.6其他锁

全局锁
- 对中各数据库加锁，让整个数据库处于只读状态。（全库备份使用）
死锁
- 如何处理死锁？
  1. 等待超时：当一个事务等待时间超过阈值，将其回滚，另外事务继续运行。缺点：等待时间长，无法接受
  2. 死锁检测：
    - innodb提供了wait-for-graph算法主动进行过死锁检测，每当获取锁请求进入等待时，会触发该算法。是一种主动的检测机制，要求数据库保存锁的信息链表和事务等待链表，基于这两个信息绘制wait-for-graph等待图。
    - 原理：构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。
- 如何避免死锁？
  1. 合理设计索引，尽可能通过索引定位更少的行，减少锁竞争。
  2. 避免大事务，将大事务拆分成多个小事务进行处理，同样也是减少锁竞争
  3. 调整业务顺序，避免UPDATE\DELETE长时间持有锁的sql在前面
  4. 在并发度较高的场景，不显示加锁

3.7锁的内存结构

满足以下条件的记录放到一个锁记录中：
- 在同一个事务中进行过加锁的操作
- 被加锁的记录在同一个页面中
- 加锁类型一致
- 等待状态一致

innodb的锁结构

锁结构	作用
锁所在事务信息	记录生成所的事务的信息（指针）
索引信息	对于行锁来所需要记录索引（指针）
表/行锁信息	表锁：表信息行锁：space id、page number、n_bits
type_mode	记录锁的类型和模式
其他信息
一些比特位	对于行锁来说，一条记录对应着一个比特位，用不同的比特位表示哪条记录加了锁。

【补充】

行锁信息：
1. space id：记录所在表空间
2. page number：记录页号
3. n_bits：记录一堆比特位的个数
type_mode:
1. lock_mode：锁的模式（意向共享：0、意向排他：1、共享锁：2、排他锁：3、自增所：4），低四位表示，
2. lock_type：锁的类型（表锁：16也即第5位是1、行锁：32，第6位是1）
3. rec_lock_type：行锁的具体类型（略）
4. is_waiting：在第9位

锁监控
1. 执行语句：SHOW STATUS LIKE ‘INNODB_ROW_LOCK%’
2. information_schema -----> innodb_trx、innodb_locks、innodb_lock_waits
3. 8.0之后添加了performance_schema.data_lock 、performance_schema.data_lock_waits

四、MVCC

4.1概述

什么是MVCC
- 通过数据行的多个版本管理来实现数据库的并发控制，换言之，为了查询一些正在被另一个事务更新的行，并且可以看见他们被更新之前的值，这样就可以不需要等待另一个更新的事务释放锁，只有innodb支持。
快照读与当前读
- MVCC主要是为了提高并发性能，用更好的方式处理读写冲突，即使有冲突，也能做到不加锁，非阻塞并发读，这个读指的是快照读。当前读实际上一是一种加悲观锁的操作，MVCC的本质是乐观锁。
- 快照读
  
  快照读又叫一致性读，读的是快照数据。不加锁的简单的SELECT都属于快照读。快照读不一定读取到数据的最新版本。并且前提是隔离级别不是串行级别，否则会退化位当前读。
- 当前读
  
  当前读读取的是当前记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的SELECT
- 再谈隔离级别
  
  mysql中默认隔离级别是可重复读，可以解决幻读问题，由MVCC实现，可以不采用锁的机制，通过乐观锁方式解决不可重复读和幻读。

4.2MVCC需要什么？

隐藏字段：innodb聚簇索引中包含两个必要的隐藏列
- trx_id：每次一个事务对某条记录进行改动时，都会把该事务的id赋值给trx_id
- roll_pointer：每次对某条记录进行改动时，都会把旧的版本写入undo日志中，这个列相当于一个指针，通过它来找到该记录修改前的信息。
undo日志：
- 每次对记录的改动，都会记录一条undo日志，每条undo日志都有一个roll_pointer属性，（INSERT操作没有），可以将这些undo日志连起来，串成一个链表。也即版本链。
read View

4.3什么是read view？

MVCC的实现依赖于：隐藏字段、undo log、read view

是什么
- MVCC机制中，多个事务对同一个记录进行更新会产生多个历史快照，这些历史快照保存在undo log中，如果一个事务要查询这个记录，需要读取哪个版本的记录呢？read view负责解决。
- read view就是事务在使用MVCC机制进行快照读操作时产生的读视图。
设计思路
- 首先，读未提交和串行化隔离级别使用不到MVCC。
- read view核心问题是需要判断你版本链中哪些版本是当前事务可见的。
核心内容
1. creator_trx_id：创建这个read view的事务id。
2. trx_ids：生成read view时当前系统活跃事务的id列表（只有修改操作才会分配id，只读事务id默认是0）。
3. up_limit_id：活跃事务中最小的事务id
4. low_limit_id：生成当前read view时，系统要分配给下一个事务的id值。（最大值）
read view规则：
1. 如果被访问版本的trx id等于creator trx id，说明当前事务正在访问自己修改的记录==》可以访问
2. 如果被访问版本的trx id小于up limit id，说明生成该版本的事务在当前事务生成read view之前已经提交了==》可以访问
3. 如果被访问版本的trx id大于low limit id，说明生成该版本的事务在当前事务生成read view之后开启的==》不可访问
4. 如果被访问版本的trx id在up limit id和low limit id之间：
  - 如果trx id在trx ids中，说明在创建read view时，生成该版本的事务是活跃的==》不可访问
  - 否则代表生成该版本的事务已经被提交==》可以访问

4.4MVCC整体操作流程：

首先获取当前事务的事务id
创建read view
查询得到的数据，判断是否符合read view 规则
- 符合规则；返回符合规则的数据
- 不符合规则；就顺着版本链找到下一个版本的数据进行判断

【注】：在读以提交的隔离级别：一个事务中的每一次SELECT都会重新获取一次read view。

在可重复读的隔离级别：一个事务中只有第一次SELECT会获取一次read view，后面的select会复用这个read view（解决幻读和不可重复读）。

日志篇

一、MySQL日志

日志类型	作用
慢查询日志	记录所有执行时间超过long_query_time的所有查询，方便优化
通用查询日志	记录所有连接的起始和中止时间，记录发送给数据库服务器所有指令的详细信息，方便审计、发现问题等（默认关闭）
错误日志	记录存在的问题（默认开启）
二进制日志	记录所有更改数据的语句，用于主从服务器之间的数据同步，方便恢复数据。
中继日志	用于主从服务器架构中，从服务器用来存放主服务器二进制内容的一个中间文件。从u武器通过赌气中继日志的内容，同步服务器上的操作
数据定义语句日
redo log	物理日志，记录物理层面上数据的更改，用于数据恢复，保证事务的持久性
undo log	逻辑日志，记录更新操作的前置操作，保证事务的一致性和原子性，用于回滚操作和MVCC机制。

日志的弊端：
- 日志会降低数据库性能，在查询非常频繁时，如果开启慢查询日志和通用查询日志，会花费很多时间记录日志。
- 日志会占用大量的磁盘空间。

1.1二进制日志

概念：也叫做变更日志，记录数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改过任何数据的语句（select）
主要应用场景：
1. 数据恢复；mysql bin log命令，可以做到数据库无损失恢复。
2. 数据复制；由于日志的延续性和时效性，master把它的bin log传递给slave，达到master-slave数据一致的目的。
3. 可以说，mysql数据库的数据备份、主从、都离不开bin log，需要bin log来同步数据，保证数据的一致性。
写入机制
- 在事务执行过程中，先写入内存中的binlog cache。
- write：先把日志写入page cache中，并没有持久化到磁盘。
- fsync：持久化到磁盘（类似redo log的机制）
刷盘策略sync_binlog
- 0：表示每次提交事务只写入page cache，由系统自行判断什么时候进行刷盘，如果操作系统挂了，page cache数据丢失。
- 1：表示每次提交事务都会刷盘。如同 redo log。
- N：每次提交事务写入page cache，累积N个事务才进行刷盘操作。
bin log与redo log对比：
1. 从日志的存储方面：
  - redo log是物理日志，记录内容是物理层面上的数据的修改，属于innodb存储引擎层产生的。
  - bin log是逻辑日志，记录的是语句的原始逻辑，类似于sql语句，属于mysql server层。
2. 从功能方面：虽然都是持久化的保证，但是侧重不同：
  - redo log让innodb拥有了崩溃恢复能力
  - bin log保证了mysql集群架构的数据一致性
两阶段提交
- 为什么使用两阶段提交？
  - 在执行更新语句过程，会记录redo log与bin log，可能会导致redo log中有记录，而bin log中没有。如果此时数据库宕机，在恢复的过程中，redo log中的数据恢复了，而bin log中没有数据可恢复，这样的话，主机数据恢复而从机没有。这就会导致主从服务器数据的不一致。
  - 所以为了保证redo log 和bin log的数据一致性，将redo log的写入拆成了两个步骤，使用两阶段提交。
- 两阶段提交过程：
  - 开始事务==》更新数据==》写入redo log（prepare阶段）》（提交事务）写入bin log》redo log（commit阶段）
  - 这样的话，写入bin log发生异常也不会有影响，因为mysql根据redo log恢复数据时，发现还在prepare阶段，并且bin log没有相应日志，则进行回滚，如果能够通过事务id找到对应的bin log日志则认为是完整的，不需要回滚。

1.2中继日志

中继日志只在主从服务器的架构中从服务器上存在，为了保证与主服务器保持数据一致，从主服务器读取二进制日志的内容，并且把读到的信息写入本地的日志文件中，即中继日志。然后从服务器根据中继日志完成数据同步。

二、主从复制

1.1主从复制概述

如何提升数据库并发能力？
- 通常将redis作为缓存与mysql配合使用，当有请求的时候先经过缓存，不存在的话再访问数据库。提升读取效率，减少后端数据库的访问压力。
- 此外，一般应用对于数据库来说都是读多写少，读取压力比较大，采用集群方案，做主从架构，进行读写分离。
- 如果目的在于提升数据库的高并发效率，首先考虑如何优化sql和索引，其次采用缓存，最后才考虑主从架构。
主从复制的作用
- 读写分离：主库负责写入数据，从库负责读取数据，当主库进行更新时，自动同步到从库。采用这种读写分离的方式，可以实现更高的并发访问，同时还可以对从服务器进行负载均衡，让不同的请求按策略均匀分发。与此同时也会减少锁表的影响，读取更加顺畅。
- 数据备份：当主从复制将主库的数据复制到从库上，相当于一种热备份机制，主库正常运行备份，不会影响到服务。
- 高可用性：数据备份其实是一种冗余机制，但是可以换取高可用性，当服务器故障，可以切换到从服务器。

1.2主从复制原理

三个线程：
1. 二进制日志转储线程：主库线程，将bin log发送给从库。
2. 从库IO线程：连接主库线程，接收bin log，并且拷贝到本地的relay log中。
3. 从库sql线程：读取relay log，将从库数据与主库保持同步。
主从复制最大的问题：
- 延时，主服务器写完还没复制到从服务器时，在从服务器进行读取。
主从复制的基本原则
- 每个slave只有一个master
- 每个slave只有一个唯一的服务器id
- 每个master可有多个slave
数据一致性问题如何解决？
- 异步复制：一致性最弱
- 半同步复制：等待至少有n个从库收到binlog
- 组复制：基于paxos协议。

调优篇

一、调优的维度和步骤

选择合适的DBMS
优化逻辑查询和物理查询
- 逻辑查询：优化sql语句，让sql语句执行更高效
- 物理查询：优化索引，创建和使用两方面
优化数据库表结构
考虑redis作为缓存
服务器端优化
- 优化sql参数
- 优化服务器硬件
库级优化、大表优化
- 读写分离、数据分片

二、查询优化与索引优化

2.1分析查询语句Explain

列名	描述
id	每个select对应一个唯一的id
select_type	select对应的那个查询类型
table	表名；查询的每一行对应一个单表，驱动表在前，被驱动表在后
partitions	匹配的分区信息
type	对表查询时的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	实际用到索引的长度（联合索引有一定参考意义）
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估要读取的记录条数
filtered	某个表经过搜索条件后剩余记录条数的百分比
Extra	额外信息

type：
- 对表查询时的访问方法，判断高效的重要依据。
- 当表中只有一条记录的时候==》system
- 使用主键或者唯一二级索引列，与常数等值匹配==》const
- 连接查询中，被驱动表，使用主键或唯一二级索引，等值连接==》eq_ref
- 使用普通的二级索引，与常量等值匹配==》ref
- 使用索引覆盖范围区间==》range
- 扫描索引上的全部数据==》index
- 扫描全部数据（无索引）==》all

2.2索引失效案例

最左前缀原则，联合索引中，必须按建立索引的顺序依次使用，否则失效。
计算、函数、类型转换
范围条件的右侧列索引失效
不等于，索引失效
is null 可以使用索引，is not null无法使用索引（尽量将字段设置为not null）
like以%开头的查询，索引失效（页面搜索严谨全模糊，左模糊）
or前后存在非索引的列

2.3关联查询优化

左外连接：给被驱动表添加索引，连接的字段类型要一致。
内连接：查询优化器可以决定谁作为驱动表、谁作为被驱动表。（如果只有一个连接条件又索引，则有索引的字段所在的表为被驱动表；如果都不存在索引，会选择小表作为驱动表，”小表驱动大表“）
join原理：
- 简单嵌套循环连接：驱动表A取出一个数据，遍历被驱动表B进行匹配，以此类推。
- 索引嵌套循环连接：被驱动表使用索引，则驱动表直接与索引进行匹配。
- 块嵌套循环连接：驱动表不再是逐条获取，而是一块一块获取，放入join_buffer缓冲区进行匹配。
- 8.0加入hash join：大数据集连接的常用方式。（与hash索引类似）
【总结】：

用小结果集驱动大结果集（减少外层循环数量）
被驱动表加索引，减少内层匹配次数。
增大join buffer大小，一次缓存越多，内层匹配的次数就越少。

2.4子查询优化

子查询可以通过一个sql语句实现复杂的查询，但是往往效率不高：
1. 执行子查询时，需要为内层的查询结果建立一个临时表，然后外层查询语句查询完临时表后再撤销，这样会消耗过多资源，产生大量的慢查询。
2. 临时表不会存在索引。
3. 子查询返回结果越大，性能影响越大。
将子查询改造成join多表查询。

2.5排序优化

mysql支持两种查询方式：
1. index：索引可以保证数据的有序性，不需要再进行排序，效率更高
2. FileSort：一般在内存中排序，占用CPU较多，还可能产生磁盘排序的情况，数据量大的情况下，效率低。
优化建议：
1. 在where、orderby 使用索引，where避免全表扫描，orderby避免filesort
2. 尽量使用索引排序，如果orderby后面和where后面是相同列，则用单索引，如果不同就用联合索引。
3. 无法使用index对filesort进行调优：
  - 增加sort_buffer_size
  - 不在orderby时 select *
  - 提高max_length_for_sort_data
实际情况：
- 没有limit限制导致索引失效
- 排序规则不一致，索引失效

2.6优先考虑索引覆盖

一个索引包含了满足查询结果的数据，不需要回表，就叫索引覆盖。
优点：避免了回表，减少了随机IO，可以显著提升性能，是常用优化手段。

2.7索引条件下推

作用：只作用于二级索引，目的是在回表之前缩小查询范围，减少回表次数。
比如说select*from table where name = “张三” and age = 10; （name，age是联合索引）
- 在使用索引下推之前的执行流程：
  - 存储引擎根据name=”张三“找到索引记录，回表找到完整记录，返回给server做后面的判断。
- 使用索引下推之后的流程：
  - 存储引擎根据name=”张三“找到索引记录后，继续判断后面的条件，找到都满足的记录再进行回表，然后将完整记录返回给server。
【总结】：也就是说，索引下推将server层的判断交给了存储引擎层，减少回表次数。

2.8其他优化策略

EXISTS和IN的区分：
- 前表小的话用EXISTS，前表大的话用IN
COUNT(*)与CONUT(具体字段)效率差距：
- 本质上没有区别，不同存储引擎有区别，具体字段来说，尽量采用二级索引（key_len最小的），比聚簇索引包含信息更少，更快。
关于SELECT(*)：
- 建议使用明确字段，不要使用*作为查询，因为会解析为所有列名。
多使用COMMIT

2.9淘宝数据库，主键如何设计？

自增id的问题：
- 可靠性不高，安全性不高
- 性能差，自增id需要在服务器端生成
- 局部唯一性，分布式无法使用
不建议使用业务相关的字段做主键，无法预测项目周期中，哪个字段因为业务需要而有重用的情况
非核心业务：可以使用自增id作为主键，日志、警告等不需要全局统一的。
核心业务：至少是全局唯一且单调递增的，保证唯一性和插入数据不影响性能。
UUID特点：唯一、无序，将时间低位放在最前面，这部分一直变化，无序。
改造UUID：8.0可以更换时间低位和时间高位，将高位放在前面变得单调递增了。同时8.0还减少了UUID的存储空间。

三、数据库结构优化（表级）

拆分表：冷热数据分离
增加中间表：将经常联合查询的诗句插入到中间表中，将原来的联合查询改为对中间表的查询
反范式化：增加冗余，提高效率，空间换时间
优化数据类型：优先选择符合存储需要的最小的数据类型（避免使用TEXT\BLOB\ENUM类型；decimal代替float、double）
使用非空约束：业务允许，尽量使用非空约束。
分析、检查表：analyze table、check table
【总结】：一定结合实际业务需求进行权衡，比如说更改数据类型，要注意取值范围；增加冗余字段，要保证数据一致性；拆分表，就意味着增加表的连接。

四、服务器端优化

CPU使用率达到峰值怎么办？
- innodb_flush_at_trx_commit：调整redo log刷盘的策略
- innodb_buffer_pool_size：表示innodb的表和索引的最大缓存，这个值越大，可以加载到缓存区的索引和数据量就越多，需要的磁盘IO就越少。
- innodb_buffer_pool_instance：将缓存区分成几个部分，可以提高系统的并行能力，允许多个线程同时处理不同部分的缓存区。