mysql–架构、事务、索引

mysql–架构、事务、索引

本文为总结文档, 原文档链接

概述

整个mysql分为两部分, 一个是server, 一个是db

1. server层放的是下图内组件, 包括内置函数和跨存储引擎的功能(存储过程, 触发器, 视图)
2. db层负责数据存储和提取. 架构是插件式, innodb, myisam等都在这块
3. 不同的存储引擎会用到同一个server层, 比如我们创建表的时候可以指定engine=innodb或者myisam

一 丶查询语句心路历程

1. 连接器

通俗来讲就是客户端连接server端, 连接器负责建立连接, 获取权限, 维持和管理连接

mysql -uuser -ppasswd -hhost -Pport -Ddatabase

1. 短连接: 执行完一次sql会申请一次连接
2. 可以看出每次连接mysql经历的事情太复杂, 比如tcp连接, 鉴权等等, 这些都会加大server端的开销, 所以还是建议使用长连接
3. 长连接: 连接上之后会一直存在, 再次期间可以执行sql
4. 长连接在存在长时间后, 系统的内存会涨的比较快, 这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的, 资源会在断开连接之后才会被释放. 内存占用过大, 连接会被强行kill掉(专业名词叫OOM);
5. 可以在自己的业务中定期重连, 解决长连接堆积
6. 5.7版本可以使用mysql_reset_connection命令来初始化资源(这个命令不会再次鉴权等等)
7. 可以通过下面sql查看长连接在系统内可以sleep的时间, wait_timeout这个参数决定着长连接的存在时间

show variables like "wait_timeout";

3. mysql在每次查询之后会把数据以key-value(key: sql语句, value: 数据)保存在缓存中, 所以在缓存中如果有数据的话会直接返回给客户端, 可以通过下面方式不走缓存(比如你是个穷逼, 买了个1核1G的云数据库)
4. set global query_cache_type=“DEMAND”;
5. 每次执行sql都加上不缓存的标识: select SQL_CACHE * from table;
6. 8.0将查询缓存的功能整个去除掉了

2. 分析器

1. 分析sql有没有错
2. 判断所查询的表或者字段匹不匹配
3. 将字符串table转换为表名, 将where后面转换为字段

3. 优化器

选择最佳的执行方案, 比如会根据sql语句和已有的索引进行查询优化

4. 执行器

1. 鉴权: 查看当前用户有没有对该表的操作权限, show grants for user;
2. 调用db层接口一行一行取数据, 取出所有符合查询条件的数据返回给客户端

二 丶 更新语句心路历程

1. 查询的一套流程更新也会走一遍
2. 缓存: 由于是更新, 所以语句会把相关表上面的所有缓存数据清空
   

redo log(存储层)

1. innodb特有
2. 可以想象, 如果每次更新都要写到磁盘, 那么对io的开销就会变大, 整个io成本会提高
3. 所以相当于是个buffer, 会将所有的更新记录在redo log中. (wal: 全称Write-Ahead Logging, 功能就是先写日志, 再写磁盘)
4. redo log大小是可以配置的, 大小固定
5. 写满之后会把最开始的数据写入存储块, 循环使用空间
6. 如果数据库重启, 重放redo log可以使数据不丢失(crash-safe)
7. 为什么都是磁盘操作, 要多出来redolog?
8. redo log是连续存在磁盘内的, 数据则是随机存储的
   

binlog(server层)

1. 归档日志
2. 增量日志
3. server层
   

redo log 和 binlog 区别

1. redo log是innodb特有的, 数据存储层; binlog是server层, 每种存储引擎都有
2. redo log是物理日志; binlog是逻辑日志
3. redo log是循环写入; binlog是追加写入

数据更新

假设现在的sql是update t set k = N + 1 where id = 2

浅色: innodb, 深色: 执行器

1. 执行器找引擎取id=2这行; 如果这行的数据页在内存中, 直接返回给执行器, 否则从磁盘中读取
2. 执行器更新数据, 调用引擎接口写入新数据
3. 引擎将数据更新到内存中同时记录到redo log中, 此时redo log处于prepare状态, 告诉server我已经ok了, 你那边可以随时提交事务
4. 执行器生成binlog并写入磁盘
5. 执行器调用引擎接口提交事务, 引擎将redo log commit

两阶段提交

可以看出上述redo log有两次处于完成的状态, 这样其实是确保了数据的完整性, 比如

1. redo log写了之后就提交了, 这个时候服务挂了, 那么在恢复数据库的时候由于binlog缺少该阶段的数据, 导致数据丢失
2. binlog 写了之后redo log没提交, 当服务正常后redo log回放并没有回放出什么东西, 所以binlog又比数据库里面的数据多了, 恢复数据库的时候会数据冗余
3. 两阶段提交会保证redo log和binlog是一致的, 数据最终是完整的

其他

1. innodb_flush_log_at_trx_commit参数设置为1的时候每次事务的redo log都会持久化到磁盘
2. sync_binlog参数设置为1的时候每次事务的binlog都会持久化到磁盘

三 丶 事务

1. ACID(Atomicity, Consistency, Isolation, Durablity; 原子性, 一致性, 隔离性, 持久性)
2. innodb有, myisam没有

begin;
savepoint a; # 可以手动添加一个回滚点
update ...;
savepoint b;
rollback to a; # 回滚到a处
commit;

标准隔离级别

1. 读未提交(read uncommitted): 事务还没提交, 其做的变更就会被其他事务看到
2. 读提交(read committed): 事务提交之后, 其做的变更才会被其他事务看到
3. 可重复读(repeatable read): 事务执行过程中看到的数据, 总是跟事务刚开启时候的数据是一致的
4. 串行化(serializable): 多个事务串行执行
5. 上述四种隔离级别依次升高

show variables like "%tx%"; # 可以看到当前事务隔离级别

举个栗子

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

1. 读未提交: v1,v2,v3都是2, 虽然B还没有commit, 但是更新的数据已经可以被A看到
2. 读提交: v1是1, v2,v3都是2, 只有在B提交之后A才能看到B更新后的数据
3. 可重复读: v1,v2是1, v3是2, 可以看下概念, A事务在执行期间看到的数据前后是一致的
4. 串行化: v1,v2是1, v3是2, B执行更新的时候会被锁住, 知道A提交之后才会执行更新.

实现

1. 视图, 数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准
   1. 可重复读: 在事务启动的时候创建视图, 整个事务都用这个视图
   2. 读提交: 每个sql执行的时候创建视图
   3. 读未提交: 因为可以看到其他事务的执行情况, 所以没有视图
   4. 串行化: 不需要视图, 我没弄完之前你别弄, 要什么视图, 整张表都是我的视图
      

举个栗子(以可重复读为例)

每条记录更新的时候都会同时记录一条回滚操作, 记录点上的值, 通过回滚都可以得到前一个状态的值

1. 假设一个值从1按顺序改成了2,3,4; 在回滚日志里面会有下面的记录
2. 在视图ABC中, 记录值分别为124
3. 同一条记录可以再系统中存在多个版本(MVCC)
4. 当前值为4的时候, 需要得到1就需要依次会滚至A
5. 当没有事务需要这些回滚日志的时候, 回滚日志会删除

其他

1. 回滚日志会占用物理空间
2. 事务提交之前, 回滚日志会一直保留
3. 大事务的回滚日志可见一斑
4. 开发过程中尽量少使用长事务, 如果必须要用的话, 保证日志空间足够; 监控information_schema.innodb_trx表可以查看长事务

四 丶 索引

常见的索引类型

1. hash: key-value型: 哈希表这种结构适用于只有等值查询的场景, 如nosql
2. 有序数组: 在等值查询和范围查询场景中的性能就都非常优秀, 有序数组索引只适用于静态存储引擎
3. 树
   

innoDB

1. 索引类型为B+树, 每个树枝会有1200左右的分支, 当树高为4的时候, 可以检索17亿的数据
   - 为什么是1200?
2. 数据全部在叶子节点上
3. 索引分为主键索引和非主键索引
   1. 主键索引: 叶子节点上是整行数据, 通过主键进行检索的时候会快速的将数据查出来
   2. 非主键索引: 叶子节点上是主键的值, 通过非主键索引检索会先取到主键id, 再通过主键id取值
4. 数据页
   1. 删除数据的时候会将数据页上的该条值删除
   2. 插入的时候需要将数据页在该条值之后的数据搬到其他数据页
   3. 具体这块自行百度
5. 主键长度越小, 非主键索引上叶子节点的占用空间就越小, 所以选择主键一般用自增int(bigint)
   

查询流程

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

1. 当我们执行select * from T where k between 3 and 5 这条sql时, 流程是这样的:
   1. 在k索引树上找到k=3, 取主键id=300
   2. 再到主键索引上由id=300取对应的值r3
   3. 在k索引树上取下一个值k=5, 取主键id=500
   4. 再回到主键索引上取id=500对应的值r4
   5. 回到k索引树上取下一个值k=6, 不满足查询条件, 结束
2. 可以看到每次查询都需要回表, 如果我们常用这两个字段进行查询取值的话, 回表次数也会大大提升
3. 解决方法–> 覆盖索引

覆盖索引

如果我们的查询语句是select id from T where k between 3 and 5; 主键id已经在k索引树上, 不需要回表. 在这个查询里面, 索引k已经覆盖了我们的查询需求. 称为覆盖索引.

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_name` (`id_card`, `name`),
  KEY `name_age` (`name`, `age`)
) ENGINE=InnoDB

如上面tuser表:

1. 我们如果频繁的使用id_card去查询name, 那么我们在使用联合索引的时候, 叶子节点存放的数据是(id_card, name) --> 主键id
2. 可以发现我们在叶子节点上直接就有name的数据, 这个时候就不需要再次使用id去回表查询

最左前缀原则

简单来说联合索引会优先对最左边的字段进行排序, 比如索引是(a,b)

1. 当我们使用where a = xxx的时候, 会使用索引性能快速查询
2. 当我们使用where b = xxx的时候, 索引不生效

索引下推

当我们执行select * from tuser where name = ‘xxx’ and age > 10;的时候

1. 在mysql5.6之前, 当找出name='xxx’的时候, 后续的条件会在表中去比对, 也就是说
   1. 先找出name='xxx’对应的主键
   2. 回表
   3. 查出数据, 用age字段与条件进行比对
   4. 在取出所有匹配的值
2. 5.6之后由于我们有(name, age)的联合索引, 可以在该索引的叶子节点取到name和age的值, 会直接在叶子结点存在的age的值进行比对, 取出满足条件的主键id
   1. 找出name='xxx’的对应叶子节点的数据
   2. 筛选出满足age条件的主键id
   3. 回表
   4. 返回数据
3. 可以看出, 第二种方法回表之后并不需要在进行数据比对, 回表只是为了返回全量的数据, 这样查询性能会大大提高