首页 / MYSQL / mysql–架构、事务、索引
mysql–架构、事务、索引
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql–架构、事务、索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7960字，纯文字阅读大概需要12分钟。
内容图文
mysql–架构、事务、索引
本文为总结文档, 原文档链接
概述
整个mysql分为两部分, 一个是server, 一个是db
- server层放的是下图内组件, 包括内置函数和跨存储引擎的功能(存储过程, 触发器, 视图)
- db层负责数据存储和提取. 架构是插件式, innodb, myisam等都在这块
- 不同的存储引擎会用到同一个server层, 比如我们创建表的时候可以指定engine=innodb或者myisam
一 丶查询语句心路历程
1. 连接器
通俗来讲就是客户端连接server端, 连接器负责建立连接, 获取权限, 维持和管理连接
mysql -uuser -ppasswd -hhost -Pport -Ddatabase
- 短连接: 执行完一次sql会申请一次连接
- 可以看出每次连接mysql经历的事情太复杂, 比如tcp连接, 鉴权等等, 这些都会加大server端的开销, 所以还是建议使用长连接
- 长连接: 连接上之后会一直存在, 再次期间可以执行sql
- 长连接在存在长时间后, 系统的内存会涨的比较快, 这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的, 资源会在断开连接之后才会被释放. 内存占用过大, 连接会被强行kill掉(专业名词叫OOM);
- 可以在自己的业务中定期重连, 解决长连接堆积
- 5.7版本可以使用mysql_reset_connection命令来初始化资源(这个命令不会再次鉴权等等)
- 可以通过下面sql查看长连接在系统内可以sleep的时间, wait_timeout这个参数决定着长连接的存在时间
show variables like "wait_timeout";
- mysql在每次查询之后会把数据以key-value(key: sql语句, value: 数据)保存在缓存中, 所以在缓存中如果有数据的话会直接返回给客户端, 可以通过下面方式不走缓存(比如你是个穷逼, 买了个1核1G的云数据库)
- set global query_cache_type=“DEMAND”;
- 每次执行sql都加上不缓存的标识: select SQL_CACHE * from table;
- 8.0将查询缓存的功能整个去除掉了
2. 分析器
- 分析sql有没有错
- 判断所查询的表或者字段匹不匹配
- 将字符串table转换为表名, 将where后面转换为字段
3. 优化器
选择最佳的执行方案, 比如会根据sql语句和已有的索引进行查询优化
4. 执行器
- 鉴权: 查看当前用户有没有对该表的操作权限, show grants for user;
- 调用db层接口一行一行取数据, 取出所有符合查询条件的数据返回给客户端
二 丶 更新语句心路历程
redo log(存储层)
- innodb特有
- 可以想象, 如果每次更新都要写到磁盘, 那么对io的开销就会变大, 整个io成本会提高
- 所以相当于是个buffer, 会将所有的更新记录在redo log中. (wal: 全称Write-Ahead Logging, 功能就是先写日志, 再写磁盘)
- redo log大小是可以配置的, 大小固定
- 写满之后会把最开始的数据写入存储块, 循环使用空间
- 如果数据库重启, 重放redo log可以使数据不丢失(crash-safe)
- 为什么都是磁盘操作, 要多出来redolog?
- redo log是连续存在磁盘内的, 数据则是随机存储的
binlog(server层)
redo log 和 binlog 区别
- redo log是innodb特有的, 数据存储层; binlog是server层, 每种存储引擎都有
- redo log是物理日志; binlog是逻辑日志
- redo log是循环写入; binlog是追加写入
数据更新
假设现在的sql是update t set k = N + 1 where id = 2
浅色: innodb, 深色: 执行器
- 执行器找引擎取id=2这行; 如果这行的数据页在内存中, 直接返回给执行器, 否则从磁盘中读取
- 执行器更新数据, 调用引擎接口写入新数据
- 引擎将数据更新到内存中同时记录到redo log中, 此时redo log处于prepare状态, 告诉server我已经ok了, 你那边可以随时提交事务
- 执行器生成binlog并写入磁盘
- 执行器调用引擎接口提交事务, 引擎将redo log commit
两阶段提交
可以看出上述redo log有两次处于完成的状态, 这样其实是确保了数据的完整性, 比如
- redo log写了之后就提交了, 这个时候服务挂了, 那么在恢复数据库的时候由于binlog缺少该阶段的数据, 导致数据丢失
- binlog 写了之后redo log没提交, 当服务正常后redo log回放并没有回放出什么东西, 所以binlog又比数据库里面的数据多了, 恢复数据库的时候会数据冗余
- 两阶段提交会保证redo log和binlog是一致的, 数据最终是完整的
其他
- innodb_flush_log_at_trx_commit参数设置为1的时候每次事务的redo log都会持久化到磁盘
- sync_binlog参数设置为1的时候每次事务的binlog都会持久化到磁盘
三 丶 事务
- ACID(Atomicity, Consistency, Isolation, Durablity; 原子性, 一致性, 隔离性, 持久性)
- innodb有, myisam没有
begin;
savepoint a; # 可以手动添加一个回滚点
update ...;
savepoint b;
rollback to a; # 回滚到a处
commit;
标准隔离级别
- 读未提交(read uncommitted): 事务还没提交, 其做的变更就会被其他事务看到
- 读提交(read committed): 事务提交之后, 其做的变更才会被其他事务看到
- 可重复读(repeatable read): 事务执行过程中看到的数据, 总是跟事务刚开启时候的数据是一致的
- 串行化(serializable): 多个事务串行执行
- 上述四种隔离级别依次升高
show variables like "%tx%"; # 可以看到当前事务隔离级别
举个栗子
mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);
- 读未提交: v1,v2,v3都是2, 虽然B还没有commit, 但是更新的数据已经可以被A看到
- 读提交: v1是1, v2,v3都是2, 只有在B提交之后A才能看到B更新后的数据
- 可重复读: v1,v2是1, v3是2, 可以看下概念, A事务在执行期间看到的数据前后是一致的
- 串行化: v1,v2是1, v3是2, B执行更新的时候会被锁住, 知道A提交之后才会执行更新.
实现
- 视图, 数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准
举个栗子(以可重复读为例)
每条记录更新的时候都会同时记录一条回滚操作, 记录点上的值, 通过回滚都可以得到前一个状态的值
- 假设一个值从1按顺序改成了2,3,4; 在回滚日志里面会有下面的记录
- 在视图ABC中, 记录值分别为124
- 同一条记录可以再系统中存在多个版本(MVCC)
- 当前值为4的时候, 需要得到1就需要依次会滚至A
- 当没有事务需要这些回滚日志的时候, 回滚日志会删除
其他
- 回滚日志会占用物理空间
- 事务提交之前, 回滚日志会一直保留
- 大事务的回滚日志可见一斑
- 开发过程中尽量少使用长事务, 如果必须要用的话, 保证日志空间足够; 监控information_schema.innodb_trx表可以查看长事务
四 丶 索引
常见的索引类型
innoDB
- 索引类型为B+树, 每个树枝会有1200左右的分支, 当树高为4的时候, 可以检索17亿的数据
- 数据全部在叶子节点上
- 索引分为主键索引和非主键索引
- 主键索引: 叶子节点上是整行数据, 通过主键进行检索的时候会快速的将数据查出来
- 非主键索引: 叶子节点上是主键的值, 通过非主键索引检索会先取到主键id, 再通过主键id取值
- 数据页
- 删除数据的时候会将数据页上的该条值删除
- 插入的时候需要将数据页在该条值之后的数据搬到其他数据页
- 具体这块自行百度
- 主键长度越小, 非主键索引上叶子节点的占用空间就越小, 所以选择主键一般用自增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');
- 当我们执行select * from T where k between 3 and 5 这条sql时, 流程是这样的:
- 在k索引树上找到k=3, 取主键id=300
- 再到主键索引上由id=300取对应的值r3
- 在k索引树上取下一个值k=5, 取主键id=500
- 再回到主键索引上取id=500对应的值r4
- 回到k索引树上取下一个值k=6, 不满足查询条件, 结束
- 可以看到每次查询都需要回表, 如果我们常用这两个字段进行查询取值的话, 回表次数也会大大提升
- 解决方法–> 覆盖索引
覆盖索引
如果我们的查询语句是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表:
- 我们如果频繁的使用id_card去查询name, 那么我们在使用联合索引的时候, 叶子节点存放的数据是(id_card, name) --> 主键id
- 可以发现我们在叶子节点上直接就有name的数据, 这个时候就不需要再次使用id去回表查询
最左前缀原则
简单来说联合索引会优先对最左边的字段进行排序, 比如索引是(a,b)
- 当我们使用where a = xxx的时候, 会使用索引性能快速查询
- 当我们使用where b = xxx的时候, 索引不生效
索引下推
当我们执行select * from tuser where name = ‘xxx’ and age > 10;的时候
- 在mysql5.6之前, 当找出name='xxx’的时候, 后续的条件会在表中去比对, 也就是说
- 先找出name='xxx’对应的主键
- 回表
- 查出数据, 用age字段与条件进行比对
- 在取出所有匹配的值
- 5.6之后由于我们有(name, age)的联合索引, 可以在该索引的叶子节点取到name和age的值, 会直接在叶子结点存在的age的值进行比对, 取出满足条件的主键id
- 找出name='xxx’的对应叶子节点的数据
- 筛选出满足age条件的主键id
- 回表
- 返回数据
- 可以看出, 第二种方法回表之后并不需要在进行数据比对, 回表只是为了返回全量的数据, 这样查询性能会大大提高
内容总结
以上是互联网集市为您收集整理的mysql–架构、事务、索引全部内容，希望文章能够帮你解决mysql–架构、事务、索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】