MySQL 快记

2025-11-27 10:46:11 分类：mysql 热度：914 评论： 0

MySQL 快记

MySQL 架构

客户端
连接器 登录
分析器<词法分析、语法分析> 、（查询缓存5.7）
优化器 生成执行计划、索引选择
执行器 操作引擎、返回结果、触发器、bin log

server 层 bin log
引擎层 redo log、 undo log

bin log

  • 执行器
  • 所有引擎可用
  • 追加写
  • 只记录修改不记录查询
  • 事务提交时刷盘
  • 误删数据恢复：从备份数据开始的时间的执行binlog
  • server-id=1
  • log-bin=mysql-binlog
  • max_binlog_size = 200 # 默认1G最大单个日志文件
  • expire_logs_day= 15 # 自动删除
  • binlog一下情况会重新生成
    • 服务器重启
    • 执行flush logs
    • 达到单个最大
  • bin log格式
    • STATEMENT：记录sql，日志量小，UUID()\SYSDATE()会导致主从不一致
    • ROW: 基于行复制，数据量大，能解决调用函数不一致的问题
    • MINXED：自动选择二选一
  • 写入磁盘机制
    • 0 page cache
    • 1 fsync写入磁盘 最安全
    • N>1, 每次写page cache， 积累N个后写入磁盘
  • 恢复 mysqlbinlog 指定位置或时间

redo log (也叫WAL 预写日志）

  • InnoDB引擎特有
  • 顺序写，性能高！
  • 固定4*1GB文件空间循环写
  • 写满一个刷新write point -> check point
  • 一个update语句先 更新到缓存redo log buffer、提交redo log (prepare) 、再写bin log、 再***mit
  • innodb_flush_log_at_trx_***mit
    • 0 redo log buffer
    • 1 磁盘
    • 2 os page cache

MySQL 内部两阶段提交（XA）

  • 如果redo log （***mitted）直接提交
  • 如果redo log（prepare）binlog检查事务是否完整，如果完整可执行，如果不完整回滚。
  • redo log 和 bin log 都有Xid（全局事务id）
  • 分布式事务-- 事务管理器中间件

MySQL 索引

innodb_page_size=16384 16kb
16KB/（8+6）B

B-Tree

  • 叶节点具有相同的深度，叶节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增

B+Tree

  • 非叶子节点不存储Data，只存储索引（冗余叶子节点第一个元素），可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用指针链接，提高区间访问性能

InnoDB索引实现（聚集）

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件
  • 聚集索引-叶节点包含了完整的数据记录
  • InnoDB的主键索引就是聚集索引Primary Key，建议建整型的自增主键
  • 非主键索引（Secondary Key) 结构叶子节点存储的是主键值（一致性、节省空间
  • 一个表只有一个聚集索引（Primary Key)

MyISAM索引文件和数据文件是分离的（非聚集）（MYI、MYD）

hash索引

  • 不支持范围查询，仅能满足“=”，“in”

联合索引

  • 做前缀原则

Explain 实战

explain extended
show warnings

id

  • id越大越先执行

select_type

  1. simple:简单查询:
  2. primary:
  3. subquery:子查询，
  4. dervied: 衍生查询，from 一个临时表
  5. union:

type

system>const>re_ref>ref>rang>index>all

NULL: 优化不需要查表，直接从树中得到最小值

const: 常量，主键，唯一索引
system: 是const一种特列，本身只有一行记录匹配

dual: 一个为了满足语法的空表

eq_ref: primary key或unique key索引的所有部分被链接使用，可能是const之外最好的链接类型了。

ref: 普通索引，可能返回多个值

rang: 范围查找
index: 扫描全索引，一般是二级索引，因为二级索引小（id+name)已包含所有数据在辅助索引表。
ALL: 全表扫描聚簇索引。

key_len

联合索引情况可以看到用了多少长度的索引。

ref

where条件字段

Extra

  • Using where
  • Using index
  • Using index condition
  • Using temporary 优化成Using index
  • Using filesort 需要额外排序，

用覆盖索引优化 like ‘%xxxx ’ – 查询字段被索引全覆盖

范围查找可能不会走索引，数据太少不如直接全表扫描。
比如 范围太大不会走，范围小就会

实战优化

  • 是否走索引和数据量有关 or in > 范围查找 不一定
  • select * 回表次数多可能不走索引
  • 强制索引 select * from xxx force index(idx_name_age_position) where xxx
  • where name = ‘1000’ 字符串不见单引号 索引失效

  • ‘Lilei’ 不会用索引

  • like ‘Lilei%’ and age = 22 and position = ‘manager’ 会用索引，——《索引下推》找到 ’Lilei%’ 之后继续在索引中向下找age\position并检查

trace工具

Order by \ Group by

  • filesort全数据排序等于没索引
  • where name = ‘Lilei’ and position = dev’ order by age; 走索引 Using index
  • where name = ‘Lilei’ order by position; 不走索引 Using filesort
  • order by age asc, position desc; 不走索引
  • 尽量用覆盖索引优化select name, age, position
  • 遵照最左前缀原则
  • Group by默认还包含一次Order by操作，可以用order by null禁止排序

Using filesort 文件排序理解

  • 单路排序：一次取出所有字段，然后sort buffer中排序
  • 双路排序：回表，根据排序字段，再取数据
  • 数据量小 sort buffer足够就会单路排序，一般不需要优化

索引设计

  1. 代码先行，索引后上
  2. 联合索引尽量覆盖条件
    尽量减少单值索引而使用联合索引，让每一个联合索引都取包含where order group, 且满足最左前缀
  3. 不要再小基数 小差异 字段上建立索引
  4. 长字符使用前缀索引 Key index(name(20), age, position)
  5. where 与 order by 冲突先where
  6. 慢查询, 通过日志 单独优化

设计实战

  • 为了满足索引补充中间值
  • 加入状态字段使用定时任务维护

分页

  1. select * from employees limit 90000,5; 90000之后取5条
    优化：有自增主键
    select * from employees where id > 90000 limit 5;

  2. 根据非主键字段排序的分页 select * from employees order by name limit 90000,5;.
    优化: 覆盖索引
    select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

关联查询

select * from t1 inner join t2 on t1.a = t2.a;a为索引字段
select * from t1 inner join t2 on t1.b = t2.b;b为非索引

  1. 联字段加索引, 被驱动表一定要走索引
  2. 小表驱动大表
  3. straight_join 只适用于inner join

in 和 exists

  • in 接 小表
  • exists 接 大表

count(*)

  • count(1) count(id) count(name) count(*)差不多
  • 字段有索引：count(*) ~ count(1) > count(name) > count(id)
  • 字段无索引：count(*) ~ count(1) > count(id) > count(name)
  • show status like 'employees' 直接查看行数，或者维护到redis中
  • 增加一个计数表

优化规范

  • 单表行数不超过500w 单表容量不超过2G
  • 业务上具有唯一特性的字段，必须建立唯一索引。高速查找和唯一性校验是值得影响insert
  • 禁止超过三个表join
  • 禁止左模糊和全模糊，搜索引擎
  • 禁止存储过程
  • in尽量避免，集合元素控制1000之内

数据类型选择

  • 数值、字符串、时间
  • 有无符号、取值范围、变长定长
  • int(2) 显示宽度不是长度，id TINYINT(@) UNSIGNED ZEROFILL 1会输出01，一般不用加
  • 日期Date 3. Datetime 8, timestamp 4,
  • 三个强制列，id, gmt_create, gmt_modify, datetime格式
  • 字符串：CHAR定长字符串（长度不足尾部空格补全），VARCHAR变长字符串

事务

  • Atomicity 原子性 - undo log实现，用于回滚
  • Consistency 一致性 - 最终目的
  • Isolation 隔离性 - 并发时互不干扰 锁、MV***
    • read un***mit(读未提交）：脏读
    • read ***mit(读已提交）： 不可重复读
      • 同一个事务中重复查询结果可能不一样，
    • repeatable read(可重复读）：幻读
      • 执行一次语句时生成一个整个数据库的快照
    • serializable（串行）：解决所有问题，会加读锁 lock in share mode
  • Durability 持久性 - redo log实现

MV*** 多版本并发控制

  • select 快照读

  • insert\update\delete 当前读

  • 幻读，读到了其他事务新提交的数据

  • read ***mit， 语句级快照

  • repeatable read， 事务级快照

  • 解决脏写：

    • update a***ount set balance = balance + 500 where id = 1;做运算时会去DB取一次当前数据

  • 读锁：（共享锁，S锁）：select … lock in share mode，多个事务可以读取同一个资源，但不允许其他事务i需改

  • 写锁：（排他锁、X锁） ：select … for update ；会阻塞其他的读写锁，update\delete\insert都会加写锁

  • trx_id, roll_pointer > insert undo log, 事务id， 回滚指针

MV***底层原理

锁分类

  • 乐观锁：RR读场景，如果要写可以带version，开发实现的锁。
  • 悲观锁：适合写多的场景，sql级别更新：update a***ount set balance = balance + 500 where id =1; 有行锁，读锁写锁都是悲观锁。
    • select xxx for update; for update代表加了写锁
  • 意向锁：I锁，针对表锁，为了提高效率，设置一个标识，其他事务查看到有I锁（可能是表锁）就不用尝试加表锁了，musql自己完成。
  • 表锁，页锁，行锁
    • 表锁：一般用于数据迁移：
      • 加锁：lock table t1 read(write), t2 read(write)...,
      • 查看表上的锁 show open tables
    • 页锁：BDB引擎支持页锁
    • 行锁：
      • InnonDB支持事务
      • InnonDB支持行锁
      • 行锁实际锁的是索引项上做标记，不是对整个行记录，索引失效会升级为表锁。（RR会升级为表锁，RC不会升级为表锁）因为RR解决脏读问题，更新幻读等。
  • 间隙锁：gap lock, 只有在RR中生效。不想其他事务占有锁，我先锁住这个范围
    • id: 1，2，3，6, 10中3-6之间就有间隙或10-∞，此时如果select … where id = 5 则会写锁住3-6，其他事务不能插入3-6，不会锁6-10. 解决了幻读问题
  • 临键锁： 行锁+间隙锁 where id> 3 and id <=10;

锁分析

show status like 'innodb_row_lock%'

优化实践

  • 所有数据通过索引，避免升级表锁
  • 尽量缩小锁范围，间隙锁尽量小20《id《100，范围查找
  • 尽量控制事务大小，涉及到加锁尽量放到后面执行，RC>RR，
  • 事务级别小效率高

事务QA

  1. 查询方法需要加事务吗
    • 如果方法只有一条select 不用加
    • 多次select 最好加 read only = true, 加一个读事务
    • 如果还需要用结果做计算，RR时间切片（报表），RC粒度不统一并发高
    • 只查询 RC加不加事务都一样，所以不加
  2. 避免用大事务
  3. 事务优化原则
  • 不用事务就没有undo log性能大大提升
  • select * from innformation_schema.innodb_trx where TIME_TO_SEC(xxx) 查询大事务

Innodb底层原理

  1. 加载缓存数据
  2. 写入被更新的旧值便于回滚undo log
  3. update cache
  4. 写redo log buffer
  5. redo log顺序写，准备提交事务
  6. bin log写入磁盘，准备提交事务
  7. 写入***mit标记到redo log，提交事务完成
  8. 在系统空闲时随机写入磁盘以page为单位写入

全局优化

SQL及索引 > 库表结构 > 系统配置 > 硬件

主从复制

转载请说明出处内容投诉
CSS教程网 » MySQL 快记
红袖添香

红袖添香

分享到：

发表评论

欢迎 访客 发表评论

一个令你着迷的主题！

查看演示 官网购买