Mysql

隔离级别

级别	描述
READ UNCOMMITTED	读未提交存在脏读
READ COMMITTED	读已提交不可重复的读，解决脏读
REPEATABLE READ	可重复读存在幻读
SERIALIZABLE	串行化解决幻读

幻读：当某个事物在读取某个范围内的记录时，另一个事务又在该范围内插入新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

REPEATABLE READ能否解决幻读？

能解决“普通的幻读”

查询（SELECT）使用一致性视图（ReadView），同一个事务内的所有查询，始终看到事务开始时的数据快照，因此不会看到别的事务插入的新数据，避免了“普通的幻读”。
对于 SELECT … WHERE … 这类查询，REPEATABLE READ 级别能保证结果一致。

不能解决“范围上的幻读”（范围修改）

如果事务 A 试图更新或删除符合条件的行，可能仍会受到其他事务插入数据的影响，导致数据不一致。
因为 MVCC 只影响 SELECT，但 UPDATE/DELETE 需要锁定数据行，而插入的新行可能不在锁范围内。

示例：UPDATE可能导致幻读

1 2	START TRANSACTION; UPDATE employee SET salary = salary + 1000 WHERE salary > 10000;

如果事务B在事务A运行过程中插入Jack，那么事务A不会修改Jack的salary，造成数据不一致。

如何彻底解决幻读

使用 SERIALIZABLE（可串行化）隔离级别
使用 SELECT … FOR UPDATE（间隙锁，Next-Key Lock）

MVCC原理

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL InnoDB 存储引擎的一种并发控制机制，通过维护数据的多个版本来实现高效的事务并发控制，避免长时间锁定数据，提高数据库的性能。

核心组件

隐藏列

DB_TRX_ID（事务 ID）：记录最后一次修改该行的事务 ID。
DB_ROLL_PTR（回滚指针）：指向 undo log 记录，用于回溯旧版本数据。

undo log（回滚日志）

存储旧版本的数据，用于 MVCC 读取历史数据。

Read View（读视图）

事务读取时生成的一致性快照，用于判断可见性。

MVCC 适用的隔离级别

MVCC适用于READ COMMITTED（读已提交）和REPEATABLE READ（可重复读）

Read View（读视图）机制

在 MVCC 中，事务查询数据时会生成 Read View，用于判断哪些数据对当前事务可见。
Read View 的主要字段

trx_ids：当前数据库中活跃事务的 ID 列表
up_limit_id：当前最小的活跃事务 ID
low_limit_id：创建 Read View 时，数据库已分配的最新事务 ID
trx_id：当前事务的 ID

Read View 规则
假设 Read View 的 trx_ids = {10, 11, 12}：

如果 DB_TRX_ID < up_limit_id（事务已提交）可见
如果 DB_TRX_ID ∈ trx_ids（事务正在执行）不可见
如果 DB_TRX_ID > low_limit_id（事务开始后才修改）不可见

MVCC 优势

避免加锁：MVCC 主要通过多版本管理数据，而非锁定数据，提升了查询效率。
提高并发性能：读取操作不需要等待写入事务完成，提高数据库吞吐量。
可见性控制：事务读取的 Read View 确保数据一致性，不影响其他事务。

MVCC 存在的问题

MVCC只支持READ COMMITTED和REPEATABLE READ，SERIALIZABLE仍然需要锁机制。
Undo Log占用空间：长期未提交事务可能导致undo log过多，占用存储空间。
删除和更新操作的开销：每次UPDATE/DELETE操作都要记录undo log，影响性能。

数据类型

整数

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
INT(11) 不会限制值的合法范围，交互工具显示字符的个数

实数

FLOAT、BOUBLE 浮点运算
DECINAL 精确计算

字符串类型

VARCHAR 可变长字符使用1或2个（大于255字节）额外字节记录字符串的长度
CHAR 定长
BLOB 二进制数据值太大时，使用外部存储区域来进行存储，此时每个值在行内需1-4个字节存储指针
TEXT 字符集和排序

日期和时间

DATETIME 与时区无关，8个字节，存储范围大，1001-9999年
TIMESTAMP 有时区，4个字节，1970-2038年，默认NOT NULL

哈希索引

只包含哈希值和行指针，无法排序，只支持等值比较

B+Tree

非叶子节点不存储数据，只存储索引，可以放更多的索引，树的高度比B树矮，磁盘IO少
叶子节点包含所有索引字段
叶子节点存储数据且用指针相互连接，范围查询更好

索引覆盖

一个索引包含所有需要查询的字段的值

延迟关联

先刷选主键id，再关联查询（需要索引）

InnoDb行锁

记录锁（Record Lock）：单个行记录上的锁
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身
临键锁（Next-key Lock）：记录锁+间隙锁，锁定一个范围，包括记录本身

索引失效

使用了LIKE模糊查询的开头
使用了OR操作符 OR操作符会导致两个条件分别使用不同的索引
对索引列使用了函数
使用NULL查询
在查询中对索引列进行了类型转换字符串字段发生隐式转换时
使用了DISTINCT或GROUP BY时索引失效
使用了ORDER BY排序不符合索引顺序
表的数据量很小

执行过程

客户段发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
服务器端通过解析器验证语法是否正确，通过预处理器验证表和字段是否存在，再由优化器生成对应的执行计划，从中选取最优的计划
执行引擎根据优化器生成的执行计划，调用存储引擎的API执行查询
将结果返回给客户端

优化器的提示

提示	描述
STRAIGHT_JOIN	按顺序执行表关联
SQL_SMALL_RESULT	将结果集放在内存中的索引临时表，避免排序操作
SQL_BIG_RESULT	使用磁盘临时表做排序操作
SQL_BUFFER_RESULT	告诉优化器将查询结果放到一个临时表，然后尽可能快地释放表锁
SQL_CACHE	结果集缓存在查询缓存中
SQL_NO_CACHE	结果集缓不存在查询缓存中
FOR_UPDATE	排他锁
LOCK IN SHARE MODE	共享锁
USE INDEX	使用索引
FORCE INDEX	告诉优化器全表扫描比索引成本高
IGNORE INDEX	不使用索引

分页获取总数

SQL_CALC_FOUND_ROWS 加此提示获取总数
FOUND_ROW() 查询

运行表和历史表返回一个

SELECT GREATEST(@found:=-1,id) as id,'user' as which_tbl
FROM users where id = 1
UNION ALL
SELECT id,'user_archived'
FROM user_archived WHERE id = 1 AND @found IS NULL
UNION ALL
SELECT 1,'reset' FROM DUAL WHERE (@found:=NULL) IS NOT NULL

第一句sql，如果找到了记录，就会执行 @found := -1
第二句sql，只有当 @found IS NULL 时才执行
第三句sql，这条记录不会被真正返回执行， @found := NULL，重置变量

视图算法

合并算法：将视图SQL和查询SQL合并后，基于底层表查询
临时表算法：将视图数据放到临时表中，查询SQL查临时表

校对规则

utf8_general_ci 大小写不敏感
utf8_general_cs 大小写敏感
utf8_bin 二进制大小写敏感

主从复制

基于行复制：可处理任何场景，没有记录语句，无法判断执行了什么SQL
基于语句复制：操作更灵活，存在无法复制的情况

复制步骤

在主库上把数据更改记录在二进制日志中
备库将主库的日志复制到自己的中继日志（Relay Log）中
备库读取中继日志中的事件，将其重放到备库数据上

主要宕机原因

磁盘空间耗尽
运行性能差的SQL
糟糕的表设计、索引设计
复制问题，主备不一致

符号分隔文件备份

SELECT * INTO OUTFILE '/tmp/t1.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test.t1;

LOAD DATA INFILE '/tmp/t1.txt'
INTO TABLE test.t1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

查询状态

Sleep：线程正在等待客户端发送新的请求
Query：线程正在执行查询或正在将结果发送给客户端
Locked：该线程正在等待表锁
Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
Copying to tmp table：线程正在执行查询，并且将其结果都复制到一个临时表中，排序或union
sorting result：线程正在对结果进行排序
sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

执行计划

id:标识SELECT所属行
select_type:对应简单还是复杂SELECT
table:对应访问的哪个表
type:访问类型

ALL:全表扫描
index:全表扫描，按索引排序扫
range:有限制的索引扫描 BETWEEN,WHERE >
ref:索引访问，匹配单个值的所有行
eq_ref:唯一索引
const,system:查询优化成一个常量
null:分解查询语句，执行阶段不访问索引或表

possible_key:可以使用哪些索引
key:使用哪个索引
key_len:在索引里使用的字节数
ref:索引使用的哪个字段或常量
rows:估计读取行数
filtered:符合条件行数的悲观预估
extra:额外信息

Using index:使用索引覆盖
Using where:检索行后再进行过滤
Using temporary:使用临时表
Using filesort:使用外部索引排序
Range checked for each record:没有好用的索引

mysql中为什么 like ‘%abc%’ 没有走索引，’abc%’ 就会走索引

MySQL索引（如 +树索引）通常是基于前缀匹配来优化查询的。这意味着，从索引的起始位置开始，索引可以高效地定位到符合条件的记录。
当查询模式为LIKE ‘abc%’ 时，’abc%’表示以abc开头的字符串，这时，MySQL可以利用前缀匹配来加速查询。

sql执行怎么保证幂等

唯一约束（最简单有效）
插入前先判断是否存在（幂等逻辑判断）
使用 INSERT IGNORE / REPLACE INTO / ON DUPLICATE KEY UPDATE
- INSERT IGNORE：如果违反唯一索引，就忽略这条数据。
- REPLACE INTO：如果主键已存在，先删除再插入（⚠️不推荐，会产生副作用）。
- INSERT … ON DUPLICATE KEY UPDATE：如果唯一索引冲突就更新。
悲观锁 + 乐观锁控制更新幂等

1
2
3

UPDATE orders
SET status = 'PAID', version = version + 1
WHERE order_id = '123456' AND version = 1;

幂等 Token / 幂等 ID（配合接口设计）
比如接口请求带一个 request_id 或 token

Oracle

oracle多表关联

先过滤数据，再关联
两个大表使用hash join， /* +USE_HASH(a b) */
关联字段，where字段，排序字段加索引
如果有时间过滤条件，可以加时间分区
如果表变化不大，使用物化视图，materialized

Redis

redis和数据库怎么保持数据一致性

同一个事务中，先删除缓存，更新数据库，发送mq，mq消费端接收消息删除缓存。

redis节点key同步

主从、哨兵通过节点PSYNC协议异步复制主节点数据

redis复制过程

redis从节点第一次复制，向主节点发送psync，主节点返回runid和offset，执行全量复制
后续执行增量复制，从之前主节点offset开始，如果主节点挂了，重新选举，需要重写全量复制。如果是网络问题，原来的runid主节点存活，则继续增量复制。

redis强一致性

redisraft，多数节点确认后才能提交
使用腾讯的Tendis

redis哨兵

监控：检查主从是否正常，哨兵是否在线
通知：服务器出现问题，通知其他节点
自动故障转移：断开主从连接，重新选取master，并通知各节点
哨兵本身不提供数据服务，数量为单数

哨兵选主过程

单个哨兵认为主挂了，设置为SRI_S_DOWN，多个哨兵都认为主挂了，则设置为SRI_O_DOWN
哨兵竞选，各自有一票，谁先发信息过来，就把票给谁，过半胜。未决出则进行下一次，竞选次数+1。
选master，先排除离线的，选响应速度快的，与master最近联系过的，再根据优先级、offset、runid选

击穿雪崩穿透

缓存击穿

热点key突然失效

热点key永不过期
加锁排队，第一个访问数据库的加redis

缓存雪崩

缓存集中过期

随机失效时间
redis高可用哨兵模式

缓存穿透

数据库不存在数据

缓存空值
参数校验
布隆过滤器
限流相同key的查询频率

redis过期键删除

惰性过期：访问时才判断是否过期，删除
定时过期：每个一段时间扫描一定数量的数据库expires字典中的key

redisson

分布式锁，是可重入锁
hash结构，使用lua脚本操作
锁会自动续期，间隔为锁的过期时间/3，如默认30秒过期，则10秒续一次期

ElasticSearch

倒排索引

对文本进行切分，分成一个个词项，把词项从小到大排序，查找词项时，通过二分查找，迅速找到文档id。
词项索引（term index），提取前缀，构建一个精简的目录树，节点中存放词项在硬盘中的偏移量。
用Stored Filed（行式存储）获取文档，用Doc Value（列式存储）排序
倒排索引不可变

什么是ES分片

es中最小的工作单元，是一个lucene的index

lucene index

lucene

在lucene中，单个倒排索引文件被称为segment。segment是自包含的，不可变更。多个segment汇总在一起，称为lucene的index，对应es中的分片
当有新文档写入时，会生成新segment，查询时会同时查询所有segment，并且对结果汇总。lucene中有一个文件，用来记录所有segment信息，叫做commit point。
删除的文档信息，保存在.del文件中

什么是Refresh

将index buffer写入segment的过程叫Refresh，refresh不执行fsync操作
refresh频率：默认1秒
index buffer被占满时会触发refresh，默认jvm的10%

什么是Transaction Log

segment写入磁盘的过程相对耗时，借助文件系统缓存，refresh时先将segment写入缓存以开放查询
为了保证数据不会丢失，所以在index文档时，同时写入Transaction Log。每个分片有一个Transaction Log。
在es refresh时，index buffer被清空，Transaction Log不会清空

什么是Flush

调用refresh，index buffer清空并且refresh
调用fsync，将缓存中的segment写入磁盘
清空（删除）Transaction Log
默认30分钟调用1次
Transaction Log满，默认512MB

字段

keyword：精确匹配
text：模糊匹配
nested：嵌套字段
type=text filed中keyword的type是keyword，即可精确又可模糊

分析器

tsconvert 繁体转简体

"analysis": {
  "char_filter": {
    "tsconvert": {
      "convert_type": "t2s",
      "type": "stconvert"
    }
  },
  "analyzer": {
    "default": {
      "char_filter": ["tsconvert"],
      "type": "custom"
    }
  }
}

char_filter（字符过滤器）
将 .、·、・、_、,、，这些符号替换为空格（” “），但仅在前后均为非空白字符的情况下生效。

"analysis": {
  "char_filter": {
    "special_char_filter": {
      "pattern": "(?<=[^\\s_.·・,，])[_.·・,，](?=[^\\s_.·・,，])",
      "type": "pattern_replace",
      "replacement": " "
    }
  },
  "analyzer": {
    "default": {
      "char_filter": ["special_char_filter"],
      "type": "custom"
    }
  }
}

ik分词器

"analysis": {
  "analyzer": {
    "default": {
      "type": "custom",
      "tokenizer": "ik_smart"
    }
  }
}

转小写

"analysis": {
  "analyzer": {
    "default": {
      "filter": "lowercase",
      "type": "custom"
    }
  }
}

未来日记

数据库