mysql

码莎拉蒂

已于 2023-03-08 17:06:14 修改

阅读量290

点赞数

分类专栏：数据库文章标签： mysql

于 2022-11-07 22:33:25 首次发布

本文链接：https://blog.csdn.net/ysl19910806/article/details/127741049

版权

数据库专栏收录该内容

14 篇文章 1 订阅

订阅专栏

事务的四个特性ACID

原子性（Atomicity）：事物的所有操作，要么全成功，要么全失败。不会停留在中间状态，如果失败会回滚到事务之前的状态。使用undo log保证在回滚时把数据恢复到事务之前。

一致性（Consistency）：事务操作要保证数据的完整，合法性。比如A向B转账，事务处理后，A和B的总和不变。

隔离性（Isolation）：多个事务修改数据要将事务隔离开，每个事务执行自己的操作，多个事务互不干扰。

持久性（Durability）：事务提交后数据要永久保存。当数据存入时会先写到buffer pool，然后同时写入redolog，redolog可以把日志持久化到磁盘上，如果插入数据时buffer pool没有来得及持久化到磁盘，在服务重启后读取redo log可以恢复最新数据。

mysql 事务隔离级别及实现

读未提交

读不加锁，性能最好；写的时候加行锁，写完释放

读已提交（RC）

使用MVCC技术，写操作：加行级锁，生成undolog，形成undolog版本链，读操作不加锁，而是使用mvcc生成readview，在同一个事物中每一次读操作都会生成一个新的readview。

可重复读（RR mysql默认隔离级别）

使用MVCC技术，写操作：加行级锁，生成undo log，形成undo log版本链，读操作不加锁，使用mvcc生成readview，在同一个事物下，每次读取的readview都是同一个。

串行化：读写都加锁

MySQL事务隔离级别和实现原理（看这一篇文章就够了！） - 知乎

事务的隔离级别主要解决脏读，不可重复读，幻读的问题

脏读：事务读取到了未提交的数据，如果此时未提交的数据事务回滚了，则事务读取到的数据不存在数据库中。
不可重复度（修改）：一个事务中每次读取的同一批数据的结果都不一样，这些数据可能受到了其他事务的影响。
幻读（新增或删除）：A事务修改了一条数据，还未提交，此时B事务插入了一条与A事务修改数据前相同的数据，并在A事务前先提交了事务。此时A提交事务后查询数据时发现刚刚的操作好像没起作用，似乎出现了幻觉。

mvcc

MVCC详解_Jason&Zhou的博客-CSDN博客_mvcc

myisam不支持mvcc的原因是myisam引擎不支持事务

mvcc只有在读已提交（RC）和可重复度（RR）两个事务隔离级别下生效

mvcc是为了解决读写冲突的，正常情况下如果有当前读和写操作同时对一行数据修改时，可能会造成阻塞。

当前读会对当前读取的数据行进行加锁，防止其他事务对其修改，这是一种悲观锁的体现 for update

mvcc是维持一个数据的多版本，使读写没有冲突的抽象概念

mvcc的组成由版本链、undo日志、readview几部分组成

版本链：

数据表中除了用户定义的字段外，还隐藏了一些字段，比如mvcc中用到事务ID（trx_id），回滚指针(roll_pointer)

如果一条新的记录插入到数据库中，会设置他对应的trx_id，和空的回滚指针（因为是刚插入）。此后，如果有事务对其进行修改，则会插入新的undo日志，保存新的事务Id，并设置回滚指针为上一个版本的undo日志，反复操作会由回滚指针将所有undo日志链接起来，形成版本链表。

undo日志：

undo log主要是用来记录被修改之前的数据，用户事务回滚保证数据的原子性一致性

在mvcc中，通过undo log的版本实现了不同事务下生成不同的数据版本。

read view：

事务进行读取数据会生成read view，相当于生成了一个数据快照。

read view中包含几个属性，用来判断事务能读取哪个版本的数据，如下：

trx_ids(当前未提交的事务id集合)

low_limit_id(当前最大事务版本id+1)

up_limit_id(当前未提交事务的最小trxid)

creator_trx_id(记录当前readview是有哪个trxid创建的)

read view 判断可见性的条件

1、trx_idtrx_id

trx_id == creator_trx_id 如果当前事务使用trx_id创建，那么也可以显示，即我自己创建的我当然可以看见。

2、trx_id >= low_limit_id（不可见）

事务id 大于等于当前最大事务id+1 ，这种情况下说明数据的产生是在当前的read view创建之后产生的，所以不可见。

3、trx_id < low_limit_id 条件满足进入下一个判断，判断trx_id 是否在未提交事务id trx_ids中

不存在，说明已提交，可以显示

存在，说明还未提交，不可查询当前数据

read view 在rr 和rc下的区别

rr隔离级别下，同一事物的每次请求所得到的read view都是相同的，不会拿到在事务中修改后的数据。

rc隔离级别下，同一事物的每次请求都会生成最新的read view，可以拿到事务中修改后的数据。

b-tree和b+tree的区别

b-tree的非叶子结点中也会存储数据，这会导致占用非叶子叶节点的空间，增加了树的高度，从而增加磁盘的IO操作。
b+tree的非叶子节点只存储主键，叶子结点存储主键和数据，并且这些主键按升序排列，相邻节点之间有横向指针链接，可以快速进行范围查找。
因为b+tree的特性，所以b+tree比b-tree存储的数据更多，查询的效率更稳定，树的高度相对较低。
innodb数据引擎默认的数据结构为b+tree。

聚簇索引（聚集索引）概念

聚簇索引是指列值（一般是主键那一列）与数据物理存储结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条

非聚集索引（非聚集索引）概念

非聚簇索引是指列值与数据的物理存储结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

聚簇索引和非聚簇索引的区别

在innodb引擎下

聚簇索引，b+tree叶子节点上data域存放的是整行数据，命中了直接获取

非聚簇索引，data域存放的是主键id，获取主键id后再去主索引中查找数据（没有满足索引覆盖条件则进行回表查询）

在MyISAM引擎下索引结构和数据是分开存储的

b+tree叶子节点中的data域存放的是地址，访问时需要通过地址去磁盘获取数据，辅助索引与主键索引的存储结构相同

回表

当在普通索引中查询到id后，又使用id去主索引中查询数据，这种形式称之为回表。

索引覆盖

在一棵索引树能够查询到所需要的字段，即为索引覆盖

执行语句中，如果where 子句中已经使用了索引，那么order by中的列不会使用索引，可以用where 条件列与orderby 排序列建立联合索引。

在以下两种情况下，一条语句可以用多个索引，如：

索引合并（index_merge），select * from t where t1=1 and t2 = 2 / select *from t where t1=1 or t2 = 2 or t3 = 3;

自关联查询， select * from t t1 left join t t2 on t1.id = t2.pid;

尽量建立普通索引刻意使用到 change buffer 特性

尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*);

索引下推ICP

减少回表次数，在存储引擎层根据条件过滤掉不符合条件的索引项，然后回表查询得到结果，将结果返回给服务层。索引下推就是把上层（服务层）负责的事情交给了下层（引擎层）去处理。

SELECT * FROM user_info WHERE name LIKE "大%" AND level = 1;

在5.6以前没有索引下推时，数据引擎会去非聚簇索引中查找符合“大"开头的数据，然后回表查询返回给服务层，服务层根据level再去回表查询判断level字段，整个过程用了两次回表。

在5.6以后有了索引下推后，数据引擎在非聚簇索引中查找符合“大”开头的数据，回表查询数据时，会通过判断level是否等于1，level不等于1则直接跳过，从而避免了服务层再次回表查询。

最左前缀

= 和in都能精准定位
sql优化器会自动调整where后的字段顺序，顺序与索引不一致也可命中最左前缀规则
假设组合索引字段组成为{c1,c2,c3}，当where c1=1,c3=3作为条件时，则只能应用部分索引组成最左前缀原则，此时，如果c2在表中的数据很少，则可以用c1=1 and c2 in(c2,c21,c22) and c3=3 这种填坑的方式让查询条件满足最左
当条件中出现like时，如果%不出现在开头位置则可应用最左
范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
以下不能应用最左前缀
- 当条件参与运算或使用函数时原则
- where子句使用！= 或 <> 操作符优化
- where子句中使用 IS NULL 或 IS NOT NULL 的优化
- where子句使用or的优化

索引失效的场景

联合索引不满足最左匹配原则，如： a、b、c三列，只使用了b和c。
索引列参与了计算
索引列使用了函数
索引字段类型与传入的字段类型不同时，如索引类型为字符串，传入参数为int型，会造成索引失效，因为需要把字符串转为数字进行比较，这就对字段进行了修改所以用不了索引。当索引类型为int传入类型为string时可以应用索引，mysql会为参入进行隐性转换，统一转成数字。
like左边包含%。
列对比，使用主键索引与其他列进行对比无法使用索引
1. explain SELECT * FROM base_project WHERE project_id = dept_id;
使用or关键字，当索引列A or 索引列B 时可以使用索引，会进行index_merge，当索引列A or 非索引列C时不会使用索引。
使用 not in 和 not exists 索引失效
使用联合索引进行order by，order by 单独出现没有使用where 或 limit 时会索引失效。这个最好别说，我不太理解

InnoDB内存结构

Buffer Pool（缓冲池），以page页为单位，默认大小16K，底层采用链表数据结构管理page，在innodb访问表记录和索引时会在page页中缓存，以后使用可以减少磁盘IO操作，提升效率。
- Page管理机制

Page根据状态分为三种类型：

- - free page：空闲page，未被使用过
  - clean page：被使用page，数据没有被修改过
  - dirty page：脏页，被使用page，数据被修改过，页中数据和磁盘的数据产生了不一致。
Change Buffer（写缓冲区），在进行DML（增删改）操作时，如果buffer pool没有其响应的page数据，并不会立刻将磁盘页加载到buffer pool，而是在change buffer中记录变更，等未来数据被读取时，再将数据合并恢复到buffer pool中。
- change buffer 占用 buffer pool空间，默认占25%，最大允许设置为50%，可以根据业务量调整，参数innodb_change_buffer_max_size。
- 当更新一条记录时，该记录在buffer pool存在，则直接在buffer pool修改，一次内存操作。如果该记录在buffer pool中不存在，会直接在change buffer中进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。当下次查询记录时，会先进行磁盘读取，然后再从change buffer中读取信息合并，最终载入buffer pool中。
- change buffer 仅适用于非唯一普通索引页，为什么？
  - 如果在索引设置唯一性，在修改时，innodb必须会做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将机会查询到buffer pool中，然后在池中修改，不会再change buffer操作。
Adaptive hash index（自适应哈希索引），用于优化对buffer pool数据的查询，innodb存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。innodb存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。
Log buffer（日志缓冲区），用来保存要写入磁盘上log文件（redo/undo）的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或者多行更新的大事物操作时，增加日志缓冲区可以节省磁盘IO操作。
- log buffer主要用于记录innodb引擎日志，在dml操作时会产生redo和undo日志。
- log buffer空间满了，会自动写入磁盘。可以通过将innodb_log_buffer_size参数调大，减少磁盘IO频率。
- innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1
  - 0：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS chache，刷盘OS cache-->磁盘文件），最多丢失1秒数据。
  - 1：提交事务，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作。
  - 2：事物提交，立刻写日志文件，每隔1秒钟进行刷盘操作。

mysql--innodb页的内部结构

MySQL页结构详解_Yanní_G的博客-CSDN博客_mysql 页结构

一文搞懂MySQL索引页结构 - 三水点靠木

innodb的最小存储单位是页，页的默认大小为16K，页的组成包括以下属性：

file header 用来存储页的类型，存放了数据页的双向指针，将多个数据页维护成一个双向链表，当前页的校验和，LSN（页最后被修改时的LSN号），页号。
page header 主要用于存储页的各种状态信息，如：当前页中的记录数，页目录中的槽数，第一条记录的地址等。
infimum+supremum 用于记录最小的记录行，与最大的记录行
user records 用于存放用户记录，一个全新的页一开始是没有user records的，用户插入记录时会去free space中申请一块空间，如果free space用完了则申请一个新的页。
free space 可用空间，随着user records写入的申请，逐渐减少。
page directory 主要用来提高页中记录的检索速度，页中的所有非删除数据会分成若干个组，每个组中主键值最大的o_owned记录了组内的记录数量，将其在页中的地址偏移量提取出来，按顺序以此存储到file tailer前，每个地址偏移量占2个字节，称作一个槽，page directory就是由这些槽组成的。
file tailer 用于存储校验和 lsn，在修改内存中的页后，在刷盘前需要先算出校验和，先写入file header ，然后开始写入磁盘，最后写入file tailer中，在断电重启后，会通过校验和判断文件是否完整，正常情况下file tailer中的LSN与file header中的LSN也是相同的。

mysql的b+树的高度怎么计算的，多大数据量有多高

终极面试：InnoDB 中B+Tree索引树有多高，以及能存多少行数据？ - 知乎

数据库中页的大小位16k，就是1024*16=16384个字节
页中可以存储数据，也可以存储主键和指针，一个bigint型主键占8位（一个int占4为），一个指针占6位。所以一个页上可以存放16384/14=1170个主键+指针，指针指向的最底层页可存储16k的数据，假如一条数据占1k，那么每一个指针对应16条数据，当树的高度为2时，那么可以最多存储1170*16条数据。当树的高度为3时，同样只会在叶子层节点存放数据，1、2层树存放的依然是主键+指针，所以当高度为3时，可存放的数据为1170*1170*16=21902400，所以一般树的高度在3-4即可满足

mysql count(*) 和 count(字段)的区别

count(可空字段)
- 扫描全表，读到server层，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加
count(非空字段)与count(主键 id)
- 扫描全表，读到server层，判断字段不可空，按行累加。
count(1)
- 扫描全表，但不取值，server层收到的每一行都是1，判断不可能是null，按值累加。
- 注意：count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
count(*)
- MySQL 执行count(*)在优化器做了专门优化。因为count(*)返回的行一定不是空。扫描全表，但是不取值，按行累加。
看到这里，你会说优化器就不能自己判断一下吗，主键 id 肯定是非空的，为什么不能按照 count(*) 来处理，多么简单的优化。当然 MySQL 专门针对这个语句进行优化也不是不可以。但是这种需要专门优化的情况太多了，而且 MySQL 已经优化过 count(*) 了，你直接使用这种语句就可以了。
性能对比结论
- count(可空字段) < count(非空字段) = count(主键 id) < count(1) ≈ count(*)

慢sql怎么优化

使用最左前缀
=和in可以乱序
选择分区度高的字段做为索引
尽量扩展索引，不要新增索引
索引列不要参与运算和使用函数
使用explain查看索引使用情况

explain的使用

id：执行的序号，按序号从大到小的顺序执行，如果序号相同则按sql中的顺序执行。
select_type：查询的类型包括以下几种：
1. SIMPLE：简单查询，不包括UNION或子查询的select语句
2. PRIMARY：查询中最外层的select
3. UNION：在使用UNION时，位于第二个或后面的select
4. UNION RESULT：UNION的操作结果，通常id为null，
5. DEPENDENT UNION：子语句查询中，位于第二个或后面的select
6. SUBQUERY：子查询语句
7. DEPENDENT SUBQUERY：子查询语句的第一个select
8. DERIVED：派生表中的select diruaiwude
table：表名，如果有别名则展示别名，有时表名会显示为dreived2 表示为select_type=dreived and id =2
type：这个属性很重要，显示了当前查询使用了那种类型，是否使用了索引，用于判断查询语句的质量，从最好到最差的连接类型为：system>const>eq_ref>ref(最好能达到)>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL ，下面说几个典型的
1. ref：当where条件字段为非唯一索引 =
2. const：当where条件字段为唯一索引 =
3. index_merge：索引合并，当where条件字段分别为多个非唯一索引
4. range：索引范围内查找，当条件为范围查询，并且列字段为索引
5. index：全索引树扫描
6. all：全表扫描
possible_keys：指出mysql能使用哪些索引查找到记录，如果查询的字段上存在索引则被列出，但实际不一定会使用到。如果没有索引，则显示为null。
key：查询中实际使用的索引。
key_len：索引使用的字节数，可通过该列计算出索引的长度，在不损失准确性的情况下，长度越小越好。
ref：显示使用哪列或常熟与key一起在从表中选择行。
rows：预计需要读取的数据条数。
filtered：通过条件过滤出的行数的百分比。
extrs：mysql解决查询的详细信息
1. distinct：优化distinct操作，mysql一旦到找了与行相联合匹配的行，就不在搜索了。
2. not exists：mysql优化了left join，一旦找到了匹配left join的行，就不在搜索了。
3. using filesort：说明mysql没有使用表内的索引排序，而是使用一个外部的索引排序（文件排序），出现这种情况应该尽快优化。
4. using index：说明查询中使用了索引覆盖，效果不错。
5. using temporary：使用了临时表存放中间结果，mysql在对结果排序时使用了临时表，常见于order by ，group by，UNION
6. using where：使用了 where 过滤，并且没有应用索引
7. Using sortunion(...), Using union(...), Using intersect(...)：说明使用了索引合并

mysql产生死锁的情况

乐观锁通过比较版本号进行加锁
悲观锁 for update
产生死锁的场景
- 线程1 update A，update B
- 线程2 update B，update A
- 当线程1update A后要尝试获取B的锁，此时线程2 updateB 完成要获取A的锁，由于都有其他线程持有要获取的锁，所以出现了互相等待，从而造成死锁

如果在线上对一个千万级大表做表结构修改会有什么问题，解决方案

因为添加字段会线索表然后对表进行复制修改操作，如果数据太大那么其他用户的insert/update/delete请求会被拒绝
我了解过一些资料，比如可以使用online-schema-change解决，它的具体流程如下：
- 创建一个新表，结构为原表+要增加的字段，把原表数据导入新表
- 在原表中创建触发器，用于记录从拷贝这一刻起用户对原表进行的insert/update/delete，并在拷贝完成后在新表中执行
- 将原表数据拷贝到新表，执行触发器记录的语句
- 将原表rename为old表，将新表改为原表名，删除old表

mysql在线修改表结构大数据表的风险与解决办法归纳 - 王滔 - 博客园

pt-online-schema-change使用详解 - 雪竹子 - 博客园

mysql同步数据到redis

可以在业务层进行同步，如查询数据时先去redis中查找，如果redis不存则去mysql中查找，并将查找结果同步到redis中，最好为数据设置过期时间，避免浪费内存。
可以基于binlog来实时同步数据到redis中，比如使用插件canal，它的主要工作原理是伪装成slave向master发送dump指令，master收到后会对其发送binlog文件，canal接收后更新redis

彻底理解Canal，看这篇就够了_慕城南风的博客-CSDN博客_canal

canal简介及canal部署、原理和使用介绍_王亭_666的博客-CSDN博客_canal

倒排索引

倒排索引是将数据库中的高频搜索字段按照分词规则拆开和主键存到一行，用户在搜索的时候可以直接用关键字到倒排索引表中查找关键字，命中后再拿id取数据表中找到对应的记录，可以减少磁盘检索

倒排索引是什么？_Code攻城狮的博客-CSDN博客_mysql倒排索引

mysql 执行 update语句都会做什么？

如执行update table1 set name = 'zhangsan' where id =1;

首先去查询 id=1 这一行数据，判断这行的数据页是否在内存中，如果不在则去磁盘中查找，然后写入内存，最后返回。

执行器得到引擎返回的数据后，会执行 name = ‘zhangsan’ 得到一条新的数据，并将这条数据插入内存，同时插入redolog，此时redolog会处于准备(prepare)状态，并告诉执行器已经执行完成，等待提交事务。

执行器再将这行数据生成binlog，并把binlog写入磁盘。

最后执行器调用引擎的提交事务接口，将redolog状态改为提交状态(comment)，此时完成所有更新操作。

redo log

redo log是记录mysql数据的修改操作，当mysql宕机恢复时，innodb可以使用redo log恢复数据，保证数据的持久性和完整性。

undo log

undo log用来记录本次修改前的数据状态，主要用于回滚和mvcc的版本链。

bin log

mysql插入数据时，是先写库还是先写binlog

先写库但不提交事务，随后进行写binlog，然后提交事务完成插入。

MYSQL体系架构

MySQL Server架构自顶向下大致可以分为网络连接层、服务层、存储引擎层和文件系统层。

网络连接层

客户端连接器（client connectors）：提供与MySQL服务器建立的支持。几乎支持所有主流的开发语言，如Java、C、Python、.net等。它们通过各自的API技术与MySQL建立连接。

服务层（MySQL Server）
- 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
- 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等。
- SQL接口（SQL Interface）：用于接收客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、试图、触发器等。
- 解析器（Parser）：负责将请求的SQL解析生成一个解析树。然后根据一些MySQL规则进一步检查解析树是否合法。
- 查询优化器（Optimizer）：

当解析树通过解析器语法检测后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

- - select uid,name from user where gender = 1;
  - 选取 -> 投影 -> 联接策略
    - select先根据where语句进行选取，并不是查询出全部数据再过滤。
    - select查询根据uid和name进行属性投影，并不是取出所有字段。
    - 将前面选取和投影联接起来最终生成查询结果。
  - 等价变换策略
    - 5=5 and a>5 改成 a>5
    - b>a and a=5 改成 b>5 and a=5
    - 基于联合索引，调整条件位置等
  - 优化count、min、max等函数
    - Innodb引擎min函数只需要找索引最左边
    - innodb引擎max函数只需要知道索引最右边
    - myIsam引擎count(*)，不需要计算，直接返回
  - 提前终止查询
    - 使用了limit查询，获取limit所需的数据，就不在继续遍历后面的数据
  - in的优化
    - MySQL对in查询，会先进行排序，再采用二分查找法查询数据，比如where id in(2,1,3)，优化为 in (1,2,3)
- 缓存（Cache&Buffer）：

缓存机制是由一些列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询完全相同的SQL语句则会由解析器进行语法语义解析，并生成“解析树”。

- - 缓存select查询结果和SQL语句
  - 执行select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
  - 及时开启查询缓存，以下SQL也不能缓存
    - 查询语句使用SQL_NO_CACHE
    - 查询的结果大于query_cache_limit设置
    - 查询中有一些不确定的参数，比如now()
  - show variables like '%query_cache%'; //查看查询缓存是否启用，空间大小，限制等
  - show status like 'Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

系统文件层（File System）

系统文件层负责将数据库的数据和日志存储在文件系统中，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid文件，socket文件等。

- 日志文件
  - 错误日志（Error log）
    - 默认开启，show variables like '%log_error%'
  - 通用查询日志（General query log）
    - 记录一般查询语句，show variables like '%general%';
  - 二进制日志（binary log）

记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不

记录select、show等不修改数据库的SQL。主要用于数据库恢复和主从复制。

show variables like '%log_bin%'; //是否开启

show variables like '%binlog%'; //参数查看

show binary logs;//查看日志文件

- - 慢查询日志（Slow query log）

记录所有执行时间超时的查询SQL，默认是10秒。

show variables like '%slow_query%'; //是否开启

show variables like '%long_query_time%'; //时长

- 配置文件

用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。

- 数据文件
  - db.opt 文件：记录这个库的默认使用的字符集和校验规则。
  - frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。
  - MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个.MYD 文件。
  - MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。
  - ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。
  - ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等。
  - ib_logfile0、ib_logfile1 文件：Redo log 日志文件。
- pid 文件

pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。

- socket 文件

socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

分库分表

阿里巴巴开发手册建议：单表行数超过500万行或者单表容量超过2GB才推荐进行分库分表，如果预计三年后数据量根本达不到这个级别，请不要在创建表时就分库分表。

可以将近期的数据放入主业务表中，其他历史数据放入历史表，比如用户查询订单，大部分用户只会查询近期的订单，而很少会去查很久以前的订单。
比如在一个社交项目中，社区用户可能只有5%的活跃用户，我们可以把这5%放到主表中，其余用户放到领一张表，从而提高活跃用户的访问速度。

垂直拆分
- 拆字段，把不常用的字段移到从表中
- 按照不同的表来拆分，可以将不同业务模块用到的表拆分到不同的数据库中，拆分方便，可以减少数据库的压力
水平拆分
- 根据表中数据的逻辑关系，将同一个表中的数据按照不同的规则拆分到多张表中，常用的规则如下：
  - 按照日期，根据不同年、月、日等维度进行拆分
  - 根据特定字段求模，将相同数值的数据存放在同一张表中
  - 根据特定范围进行拆分
- 水平拆分带来的痛点是数据的查询，查询一组数据可能会跨表，并且新增数据时，无法继续使用自增ID，还需引入分布式ID生成技术
主键选择
- UUID：本地生产，不依赖数据库，缺点是因为乱序生成作为主键性能太差
- SNOWFLAKE：百度UidGenerator、美团Leaf、基于SNOWFLAKE算法自我实现
数据一致性
- 强一致性：XA协议
- 最终一致性：TCC、saga、Seata
数据库扩展
- 成倍增加数据节点，实现平滑扩容
- 成倍扩展后，表中的部分数据请求已被路由到其他节点上面
业务改造
- 基于代理层方式：MyCat、Sharding-Proxy、mysql proxy
- 基于应用层方式：Sharding-jdbc

分库分表组件ShardingSphere sharding-jdbc sharding-proxy

sharding-jdbc主要功能：

数据分片
- 分库、分表
- 读写分离
- 分片策略
- 分布式主键
分布式事务
- 标准化的事务接口
- XA强一致性事务
- 柔性事务base-saga
数据库治理
- 配置动态化
- 编排和治理
- 数据脱敏
- 可视化链路追踪

mycat

通过配置xml，以代理的方式接入应用服务，开发者在编码过程中除了端口号不同外，其他使用与数据库相同
分布式主键：配置sequnceHandlerType
1. 使用本地文件方式生成
2. 使用数据库方式生成
3. 使用本地时间戳方式生成64为二进制ID，42（毫秒）+5（机器ID）+5（业务编码）+12（重复累加）
4. 基于ZK与本地配置的分部式ID生成器
5. 使用ZK递增方式生成
分布式事务XA提交需要以下几步
1. set autocommit=0；设置手动提交（首次使用需要配置）
2. set xa=on；开始XA事务
3. insert / update /delete ；执行业务sql语句
4. commit / rollback；提交事务或回滚事务
分库分表
1. 单库多表/多库单表
2. 逻辑库：从原有的一个库，被切分为多个分片数据库，所有的分片数据库构成了完整的数据库存储。mycat操作时，使用逻辑库来代表这个完整数据库集群，便于对整个集群操作。
3. 逻辑表：分布式数据库中，对应用来说，读写数据的表就是逻辑表。
4. 分片表：是指那些原有的很大数据的表，需要切分到多个数据库的表，这样每个分片都有一部分数据，所有分片构成了完整的数据。
5. 非分片表：数据量不是很大，不用进行切分的表。
6. ER表：基于E-R关系的数据分片策略，子表与主表放在同一分片上，通过表分组（Table Group）保证数据join不会跨库。
7. 全局表：数据不多，不经常变化，在每个分库中都有一个相同的表，好处是可以比较跨库join。
8. 分片节点：数据切分后，一个大表被分到不同的分片数据库上面，每个表分片所在的数据库就是分片节点dataNode。
9. 节点主机：数据切分后，每个分片节点不一定都会独占一台机器，同一个机器上面可以有多个分片数据库，这样一个或多个分片节点所在的机器就是节点主机，为了规避节点主机并发数限制，尽量将读写压力高的分片节点均衡的放在不同的节点主机dataHost。
10. 分片规则：按照某种业务逻辑把数据分到某个分片的规则。
读写分离
1. 通过在dataHost下配置 writeHost 与 readHost设置读写分离， readHost为writeHost的子标签，如果writeHost挂了，则readHost节点也不可访问。

mysql宕机数据丢失

mysql主从复制过程

mysql5.7以前

master在每个事物完成之前会把操作记录写入binlog文件中。
slave开启一个I/O Thead，该线程在master打开一个普通连接，主要的工作就是把master中的binlog写入slave的relaylog（中继日志）。
slave的SQL Thread会读取relaylog文件，此时是单线程操作，并顺序执行该日志中的SQL事件，从而使主从数据保持一直。

mysql5.7以后

在slave中读取relaylog时，不再是单线程读取，而是改为并行复制模式，将旧版本中的SQL Thead 替换成分发器和多个worker，这个时候需要开启gtid，如果不开启mysql会生成一个匿名的gtid用来保证relaylog中的记录不被重复分发。

mysql延迟同步

从几个方面来解决这个问题

硬件方面：尽量使用ssd代替机械硬盘，增加CPU核心数
架构方面：一主多从，分摊从库压力，利用redis减轻mysql的压力
业务方面：对一致性比较高的业务可以强制查主，保持强一致性；如果能容忍读取等待时间，可以在读取从库的时候sleep一下。
配置方面：开启mysql的并行复制，解决从库复制延迟的问题。

千万级数据库中怎么做翻页

id连续有序：可以用id作为条件进行翻页查询
id不连续：通过使用延迟关联提高查询速度，具体方式是查询是与子查询进行关联，子查询中使用limit查询id 使得子查询的语句可以索引覆盖，从而提高查询效率。
1. SELECT * from daily_mq a inner join (select id from daily_mq LIMIT 1000000,10) as tmp ON a.id = tmp.id

MySQL中的锁

根据锁的粒度分类：

表锁
1. 意向锁：
  1. 一个事务对表中的某一行加了排他锁未提交，其他事务想要获取该表的表锁就需要保证两个条件：没有其他事务持有该表的行锁或表锁。
  2. 为了检测是否存在有其他事务持有行锁，就需要检查表的每一个是否存在行锁，所以效率会非常低。因此就出现了意向锁。
  3. 当一个事务获取某一行的排他锁后，表中其实存在两把锁，一把是加在行上的锁，一把是加在表上的排他锁。此时当有其他事务想要判断该表是否有行锁时，就判断表上是否有排他锁，这样就不用每一次进行检查了。
  4. 意向锁只会在表级共享锁和表级排他锁互斥，不会和行级共享锁和行级排他锁互斥。也就是说可以对一个表中的多行进行上锁。
  5. 意向锁是由数据库引擎自己维护的，用户无法手动操作意向锁。
2. 自增锁：
  1. 当一个索引设置为自增时，对表中新增数据时就会持有自增锁，假设事务A正在插入数据，此时事务B尝试执行insert语句，事务B会被阻塞住，直到事务A释放自增锁。
行锁
1. 间隙锁（Gap Lock）：
  1. 间隙锁自动启动的条件：必须在innodb的RR级别下，检索条件必须有索引，如果没有索引会把整张表锁住。
  2. 间隙锁解决了RR级别下的幻读，间隙锁的区域根据检索条件想做寻找最靠近检索条件的记录值A，作为左区间；向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。
  3. 间隙锁可以锁住（A，B）这个区间，禁止事务进行DML操作。
2. 临建锁（Next-Key Lock）：
  1. 临建锁是行锁和间隙锁的组合，不仅锁定行，也会锁定前后范围(左开有闭区间]。
  2. 临建锁只与非唯一索引列有关，锁定当前记录行并将其作为左区间，检索条件向右查找的第一个值作为有区间
3. 记录锁（Record Lock）：就是行锁，锁定单行记录的锁，在RC、RR级别下都支持，查询时需要精确匹配（=），如果使用>、

根据锁的方式分类：

排他锁--写锁--X锁：针对行锁，当有事务对数据加写锁后，其他事务不能在对锁定的数据加任何锁，又因为Innodb对select语句默认不加锁，所以其他事务除了不能写操作外，是可以进行读操作的。
1. 加锁方式：DML语句默认加写锁，也可以在select语句后加for update，释放方式：commit、rollback。
共享锁--读锁--S锁：当有事务对数据加读锁后，其他事务只能对锁定的数据加读锁，不能加写锁（排他锁），所以其他事务只能读，不能写。主要为了支持并发读的场景，读时不允许写操作。
1. 加锁方式：select * from T where id = 1 lock in share mode; 释放方式：commit、rollback。

根据实现方式分类：