mysql

最新推荐文章于 2025-08-15 10:57:29 发布
有头有脸
最新推荐文章于 2025-08-15 10:57:29 发布
阅读量911
点赞数 7
CC 4.0 BY-SA版权
文章标签： mysql 数据库
本文链接：https://blog.youkuaiyun.com/sinat_37925007/article/details/147539520
                    
                        
                    
                    索引
B+树
叶子节点是记录（按主键顺序存放），非叶子节点是索引
叶子节点之间是双向链表
索引会存在子节点中，是最小值或最大值
查询复杂度为树的高度，稳定

在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」
联合索引
最左匹配原则， 字段在 where 子句的顺序并不重要。字段是否包括更重要，这个就要求，查询字段每次都出现的放在联合索引的最左边。同时要把区分度大的字段排在前面
在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配
Extra 为 Using index condition，那么说明使用了索引下推的优化。下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。减少IO操作：https://juejin.cn/post/7005794550862053412

索引缺点
需要占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候适合索引
字段唯一
经常用于where、group by 、order by条件

什么时候不适合索引
字段中存在大量重复数据，
表数据太少的时候，
经常更新的

索引优化
覆盖索引
是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

使用自增主键
插入数据不需要移动，移动可能从一个页到另一个页，导致大量内存碎片

索引最好设置为 NOT NULL
索引难以统计，不便于优化器做索引选择

避免索引失效
最左匹配原则
左匹配和左右匹配会失效
or 不是索引列
在查询条件中对索引列做了计算、函数、类型转换操作
索引保存的是索引字段的原始值
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较

使用explain 查看执行计划
type
all 全表
index 全索引
range 索引范围。这一级别以后到的索引效果才明显
只检索给定范围的行

ref 非唯一索引
索引列的值不唯一，还需要附近小范围扫描（存储读取连续）

er_ref 唯一索引扫描
使用主键或唯一索引

const 结果只有一条
使用主键或唯一索引与常量值比较


key 索引名称
rows 扫描行数
extra
Using filesort 文件排序
语句中包含 group by、GROUP BY 操作，而且无法利用索引完成排序操作的时候。效率低

Using where 未完全通过索引完成过滤，需回表后再筛选
在 WHERE 条件的列上添加索引（若区分度高
使用覆盖索引（包含所有查询字段）避免回表




建议
单列等值查询，为该等值列加索引

多列等值查询，每列求取散粒度，按从大到小排序取前N列添加到索引
单列非等值查询，为该非等值列加索引

多列非等值查询，每列求取散粒度，为散粒度最大的列加索引。
等值 & 非等值组合查询优化

先按等值查询优化为等值列添加索引

再将非等值查询优化的列追加在等值列索引后
OR的优化需要依赖UNION ALL或Index Merge等多索引访问技术来实现
GROUP BY、ORDER BY相关字段能否加入索引列表需要依赖WHERE子句中的条件。当查询指定了WHERE条件，在满足WHERE子句只有等值查询时，可以对GROUP BY字段添加索引。当查询未指定WHERE条件，可以直接对GROUP BY字段添加索引。
按照GROPU BY的先后顺序添加索引

GROUP BY字段出现常量，数学运算或函数运算时会给出警告
多个字段之间如果指定顺序相同，按照ORDER BY的先后顺序添加索引

多个字段之间如果指定顺序不同，所有ORDER BY字段都不添加索引

ORDER BY字段出现常量，数学运算或函数运算时会给出警告

LEFT JOIN为右表加索引

RIGHT JOIN为左表加索引

INNER JOIN两张表都加索引
散粒度计算
直接对线上表进行COUNT(DISTINCT)操作会影响数据库请求执行效率，
计算公式：count（列）/total

PRIMARY > UNIQUE > KEY
(a, b) > (a)


count
count（1）或者count（*）包含null
count（列）不包含null
count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。
如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。
为什么是遍历循环计数，而不是写的时候直接计数，读直接查count字段
事务原因，有时读取的数要求不一样

大表count 扫描时间长
近似值，使用max（id）-min（id），删除使用逻辑删除
count常使用在分页，如何解决深分页，随着 offset 增大，MySQL需要扫描并跳过大量数据
延时关联
子查询查询主键
通过主键关联查询完整数据

游标分页
使用上一次查询的最大的主键id作为这次的起始值





事务
要么全部执行成功 ，要么全部失败
事务有哪些特性（为什么要用事务）
原子性
undo log （回滚日志）

一致性
数据库保持一致性状态。
其他性能保证


隔离性
允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致
MVCC

持久性
事务处理结束后，对数据的修改就是永久的，重启也不会更改
redo log（重做日志）


事务级别
并发事务会出现的问题
脏读
一个事务「读到」了另一个「未提交事务修改过的数据」，就意味着发生了「脏读」现象

不可重复读
在同一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。

幻读
在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。


四种隔离级别
分级是为了不同业务场景的并发和性能要求
默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象
读未提交
不做隔离

读已提交
事务提交后，变更才能被人看到

可重复读
同一个事务的两次读结果是一样的

串行

未提交读
无事务

已提交读
解决脏读
读到了其他线程写的数据，然后其他线程回滚了，就读到了脏数据

mvcc 每个读创建一个read view

可重复读
解决不可重复读
mvcc
undo log
同样也用于保持事务原子性
没有提交事务时崩溃，需要undo log用于回滚

存储的是回滚时需要的信息，回滚时做相反操作
事务提交后不会马上删除，由一个专门的线程处理

read view 事务开始时创建
read view 包含 创建这个read view的事务id，当前数据库中活跃未提交的最小事务id，当前数据库下一个事务id，活跃未提交的事务id列表
每条记录有两个隐藏字段
最近对这条记录进行更改的事务id
指向上一个版本记录（undo log)的指针

对比记录中的事务id，小于最小id，说明事务已提交，在最大和最小中间，就要找活跃未提交的事务列表，在列表里面就要根据指针找undo long里面的旧版本，不在就可见
mvcc不能保证不出现幻读
A在读时读不到这条记录，另一个请求插入了这条记录
然后A更新这条记录（tx_id变小），再查询就看到了这条记录



串行
解决幻读



锁
幻读
快照读 select
mvcc

当前读
next-key lock 间隙锁+记录锁

锁类型
全局锁
备份数据库
一般用事务，mvcc


表级锁
表锁
限制读写，且阻塞线程

元数据锁MDL
事务提交时释放，所以当一个长事务执行时，先读锁后无法生成MDL写锁，之后大量读阻塞
对表结构做修改时写锁
对表进行curd时加读锁，会阻塞写

意向锁
在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；
意向锁只会和共享表锁和独占表锁冲突
为什么要有意向锁
对表加锁时都要遍历一遍记录锁，消耗大
快速判断表里记录是否被加锁


AUTO-INC 锁
自增锁
插入后释放


行级锁
记录锁
S锁：相当于读锁
X锁：相当于写锁

间隙锁
锁的是范围 ，左开右开
在间隙锁范围插入记录，会生成插入意向锁
允许多个事务在同一间隙（Gap）的不同位置插入数据，无需互相等待
两个事务同时拥有同一个范围的间隙锁，然后在范围内插入记录会造成死锁
监控锁状态：定期使用 SHOW ENGINE INNODB STATUS 或性能监控工具分析锁争用情况


next-key lock
锁的是范围，但是也锁定记录
左开，右闭
间隙锁之间不冲突，但是next-key lock相同范围而且都是x型则会则色



在 InnoDB 事务中，对记录加锁带基本单位是 next-key 锁，但是会因为一些条件会退化成间隙锁，或者记录锁
执行 update 语句的时候，确保 where 条件中带上了索引列，防止因为扫描全表，而对表中的所有记录加上锁

一条查询sql流程
客户端与服务器建立tcp连接，获取权限
服务器分配一个线程处理请求；空闲超时后自动断开

查询缓存，8.0已经删除
先查缓存，是不是执行了这条语句，语句和结果为一个键值对
只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

分析
对语句进行词法分析，语法分析
词法分析：字符串拆解为关键字，表名，列名等
语法分析：验证 SQL 语法正确性


优化
基于统计信息（如索引基数）和成本模型（I/O + CPU 开销）选择最优路径，确定执行方案
索引选择：使用主键索引还是二级索引。优先二级索引
多表 JOIN 时的驱动表选择（如小表优先）


执行
判断权限
根据表的引擎定义调用提供的接口
主键查询：若 tb_id 是主键，直接通过 B+ 树定位记录。
二级索引查询：先按二级索引查主键，再根据主键查询
全表扫描：从表的第一行开始遍历判断是否符合条件，是则放入结果集，最后返回客户端

取数据，从buffer pool有限访问内存数据页，没命中再访问磁盘


一条更行sql流程
之前都类似
WAL，先写日志，等一定时机再写磁盘，而不是找到记录再更改
redo log
是固定大小的
循环写
用于持久化
物理日志，记录的是在某个数据页上做了什么修改
什么时候刷盘
mysql正常关闭
redo logo buffer占用内存超过一半
每隔一秒
事务提交（可控制）


bin log
逻辑日志，记录的是这个语句的原始逻辑
默认格式，记录sql，函数执行结果可能不一样

执行过程
判断记录是否一致，一致则返回
开启事务
生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页，同时记录对应redo log
InnoDB 引擎就会先把记录写到内存
同时标记为脏页，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

同时写入 redo log里面，redo log这条记录处于prepare状态。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
然后生成这个操作的 binlog，被保存到 binlog cache，
调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，把 binlog 从cache写入磁盘。


一行记录是怎么存储的
一条记录是一行
逆序，为了能和头几列在一个cpu cache line，一起取
变长字段长度列表
空值列表
二进制

记录头信息

读写按页，默认16kb
在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB
段由区组成
每行，有变长字段长度列表，空值列表，记录头信息

单表不要超过2000w条
树高为3，索引页，页数据15k，一个索引为主键+页号（12byte）,索引页两级，大概(15k/12)2=12802个索引
数据页每行大概1k，则每页15行，则数据总数为1280128015约为2000万
行数据大的话，这个2000条需要减少

主从复制
依赖于binlog
MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

buffer pool
默认128m，一般为可用物理内存的60-80%
Innodb 通过三种链表来管理缓页：
Free List （空闲页链表），管理空闲页；
Flush List （脏页链表），管理脏页；
LRU List，管理脏页+干净页，
LRU 将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。；
InnoDB 做 2 点优化：
将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，
目的是为了解决预读失效的问题。
预加载，指相邻的页加载过来，但是没被访问导致预读失败，但是占用了前排位置

当「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」时，才会将页插入到 young 区域，否则还是插入到 old 区域，
目的是为了解决批量数据访问，刚取的数据占用了大量空间，导致所有页都替换出去了，大量热数据淘汰的问题。








                