MySQL知识体系
2022-06-14 23:12:12 1 举报AI智能生成
登录查看完整内容
为你推荐
查看更多
MySQL知识体系
作者其他创作
大纲/内容
长链接
短链接
它负责和客户端建立连接，获得权限，维持和管理连接。我们查询时，第一步都是链接，这个时候就是连接器在起作用。链接的时候会经过TCP握手，然后身份验证，然后我们输入用户名密码就好了验证ok后，我们就连上了这个MySQL服务了，但是这个时候我们处于空闲状态
连接器
key(查询语句)：value(查询结果)
但是大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利。因为：查询失效非常频繁
查询缓存
在缓存没有命中的情况下，就开始执行语句了。会先做词法分析，你的语句有这么多单词、空格，MySQL就需要识别每个字符串所代表的是什么，是关键字，还是表名，还是列名等等。然后就开始语法分析，根据词法分析的结果，语法分析会判断你sql的对错，错了会提醒你的，并且会提示你哪里错了
分析器
索引选择
它负责查询的优化。优化就比较简单了，因为我们建立表可能会建立很多索引，优化有一步就是要确认使用哪个索引，比如使用你的主键索引，联合索引还是什么索引更好。还有就是对执行顺序进行优化，条件那么多，先查哪个表，还是先关联，会出现很多方案，最后由优化器决定选用哪种方案
优化器
执行权限判读
它负责执行语句，执行器会调用执行引擎去执行。执行的时候，就一行一行的去判断是否满足条件，有索引的执行起来可能就好点，一行行的判断就像是接口都提前在引擎定义好了，所以他比较快
执行器
MySQL内部架构
InnoDB架构
Redolog+binlog执行过程
插入的时候会先到缓冲池查找，如果有，则加入；如果不在，先放入insert buffer对象中，然后以一定频率进行insert buffer和辅助索引页叶子结点的合并操作
非聚集索引
非唯一索引
非聚集索引的插入与更新，
条件
非聚集索引的插入无序，需要离散访问，插入性能变低
why
插入缓冲
解决部分写失败问题
目的
1.缓冲池刷脏页的时候，先写入到内存的double write buffer
2. 在写入到磁盘的共享表空间（2M），每次写入1Mb
3. 同步到本地磁盘
原理
共享表空间中连续的128个页，即两个区，大小同样为2M（物理磁盘）
double write buffer内存
double write
当内存数据页与磁盘数据文件不一致，这个内存也叫做脏页
脏页
相关名词
双写
innodb不支持哈希索引
某些情况下，哈希查找效率很高
innodb存储引擎会监控对表上索引的查找，如果观察到建立hash索引可以提高性能的时候，则自动建立hash索引
不能人工干预，系统自动创建
优化二级索引查找速度
特性
自适应哈希
刷新一个脏页的时候，innodb会检查该页所在区的所有页，如果判断为脏页，就一起进行刷新
刷新临近页
关键特性
Inondb
表锁
不支持外键
不支持事务
不支持mvcc
支持全文索引；innodb不支持但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
是非聚集索引，innodb是聚集索引
innodb支持crash-safe
Myisam
存储引擎
主从复制架构
MySQL架构
子主题
架构图
差异
数据写入过程
执行流程图
MySQL底层执行原理
字符集会影响到CHAR类型是否加入到该头字段中
变长字段长度列表
NULL值列表
图解
delete_mask
记录头信息
隐藏列
记录的真实数据
完整的示例图解
行数据图解
compact行格式
Redundant行格式
行格式
行
在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。
innodb中页的大小默认是16kb
数据页
系统页
Undo页
事务数据页
类型
页中的数据记录是已单向链表的形式
页结构
多页采用双向链表组织
page
在innoDB一个区会分配64个连续的页
一个区的大小是1mb
区
表段
索引段
一个段包含64个连续页
段
表空间（Tablespace）是一个逻辑容器
表空间存储的对象是段
系统表空间
用户表空间
撤销表空间
临时表空间
分类
多张表公用一个ibd文件
on
一个表一个ibd文件
off
show variables like 'innodb_file_per_table';
表空间
基本单元
结构
每个新建B+树的节点会申请一个页
同层节点通过页的结构形成一个双向链表
B+树
数据存取流程
innodb缓冲池
内存读取
整体时间10ms
如果数据不再内存，就需要在磁盘上对该页进行查找
随机读取
顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/O 操作了。
顺序读取
数据页加载的形式
数据存取原理
MySQL存储结构
ALTER TABLE player ADD (age int(11));
添加字段
ALTER TABLE player RENAME COLUMN age to player_age
修改字段名，将age字段改成player_age
修改字段的数据类型
ALTER TABLE student DROP COLUMN player_age;
删除字段
修改表结构
DDL：数据定义语言
DML
DCL
DQL
基本操作
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）
特性:ACID
作用是显式开启一个事务
START TRANSACTION或者 BEGIN
提交事务。当提交事务后，对数据库的修改是永久性的。
COMMIT
意为回滚事务。
ROLLBACK
在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
SAVEPOINT
删除某个保存点。
RELEASE SAVEPOINT
设置事务的隔离级别
SET TRANSACTION
控制语句
mysql> set autocommit =0; //关闭⾃动提交
自动提交
读到了其他事务还没有提交的数据
脏读（Dirty Read）
对某数据进行读取，发现两次读取的结果不同，也就是说没有读到相同的内容。这是因为 有其他事务对这个数据同时进行了修改或删除
不可重复读（Nnrepeatable Read）
事务A根据条件查询得到了N条数据，但此时事务B更改或者增加了M条符合事务A查询条件的数 据，这样当事务A再次进行查询的时候发现会有N+M条数据，产生了幻读
幻读（Phantom Read）
三种异常
并发处理
隔离级别
事务
索引的出现其实就是为了提高数据查询的效率，就像书的目录一样
B+ 树索引模型
InnoDB 的数据是按数据页为单位来读写的
InnoDB 中，每个数据页的大小默认是 16KB
InnoDB 的索引模型
什么是索引
主键索引
普通索引
唯一索引
逻辑功能划分
性能相差不大
查询
唯一索引都需要进行冲突检测
而普通索引不需要，性能更高
更新
唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用
普通索引和唯一索引应该怎么选择。其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。
性能分析
聚簇索引
非聚簇索引：二级索引
物理实现方式
单一索引
联合索引
按照字段个数划分
索引类型
聚集索引的叶子节点存储的就是我们的数据记录，非聚集索引的叶子节点存储的是数据位置。非聚集索 引不会影响数据表的物理存储顺序。
一个表只能有一个聚集索引，因为只能有一种排序存储的方式，但可以有多个非聚集索引，也就是多个 索引目录提供数据检索。
使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。
区别
Hash索引
innodb中会将满足一定条件的热点数据页的地址放到hash表中，并且这些hash放到缓冲池，提升热点数据的查询效率
自适应hash
MySQL 使用 B+ 树是因为数据的遍历在关系型数据库中非常常见，它经常需要处理各个表之间的关系并通过范围查询一些数据；但是 MongoDB 作为面向文档的数据库，与数据之间的关系相比，它更看重以文档为中心的组织方式，所以选择了查询单个文档性能较好的 B 树，这个选择对遍历数据的查询也可以保证可以接受的时延
以B/B+树的数据结构存储，应用范围广
mongodb用的是B树，因为它追求的是读写单个记录的性能，而不是范围查找
LSM树和B+树相比，LSM树牺牲了部分读性能，用来大幅提高写性能
LSM树是一种专门用来优化写入的数据结构，它将随机写变成了顺序写显著地提高了写入性能，但是却牺牲了读的效率，这与大多数场景需要的特点是不匹配的，所以 MongoDB 最终还是选择读取性能更好的 B 树作为默认的数据结构；
LSM树存储引擎的代表数据库就是HBase
Btree索引
mysql只有MEMORY存储引擎显示支持哈希索引。
通过Hash算法计算索引位置，效率高，一次定位。
哈希表结构在等值查询场景比较快
但需要解决Hash冲突。不支持范围检索
哈希表，不支持范围查询
有序数组索引只适用于静态存储引擎
更新插入成本太高
有序数组
树太高，增加io次数
红黑树/二叉搜索树
每个节点都存储数据
比b+树高，会增加io次数
B+的叶子节点中有双向链表关联，范围查找更快
b树
只有叶子节点存储数据
每个叶子节点之间通过指针关联
提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率
聚集索引叶子节点存储整行数据
非聚集索引叶子节点存储主键值
Mysql的基本存储结构是页
各个数据页可以组成一个双向链表
而每个数据页中的记录又可以组成一个单向链表
每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。
节点的大小为1页（16k）倍数最好
b+树
innodb为什么选择B+树
跳表、LSM 树（todo）
数据结构
MySQL索引结构
B+ 树可以使用到范围查找，hash不能
hash排序比较困难
按照顺序的方式对数据进行存储，因此很容易对数据进行排序操作
无法使用联合索引
在联合索引中也可以利用部分索引键进行查询
hash在进行查询的时候，效率非常高
优缺点
当数据量非常小，比如不到1000行
数据重复度，高于10%
频繁更新字段不要创建索引
什么时候不需要创建索引
字段的数值有唯一性的限制，比如用户名
频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
需要经常 GROUP BY 和 ORDER BY 的列
更新索引字段的值比更新普通字段的值的效率要高，因为更新索引字段还需要维护索引树
UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引
DISTINCT 字段需要创建索引
链接表尽量不要超过3张
对where条件创建索引
做多表 JOIN 连接操作时，创建索引需要注意以下的原则
创建索引有那些规律
如果索引进行了表达式计算，则会失效
如果对索引使用函数，也会造成失效
在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。
当我们使用 LIKE 进行模糊查询的时候，前面不能是 %
索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。索引不会储层空值，所以最好将索引设置为not null
我们在使用联合索引的时候要注意最左原则
什么情况下索引会失效
在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列
将 GROUP BY 和 ORDER BY 中的列加入到索引中
将 SELECT 字段中剩余的列加入到索引片中
为什么很难存在理想的索引设计
三星索引会让索引变宽
一张表的索引个数不宜过多，否则一条记录的增加和修改，会因为过多的索引造成额外的负担
缺点
三星索引
如何选择索引
当查询语句中的字段就是索引中的某个字段时，可以达到索引覆盖的效果
覆盖索引
可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
索引下推
联合索引：最左原则
会导致回表
前缀索引对覆盖索引的影响
mysql> select field_list from t where id_card = reverse('input_id_card_string');
第一种方式是使用倒序存储
你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
第二种方式是使用 hash 字段
前缀索引区分度不高的问题
概要
查询主键索引的B+树
回表
索引使用原则
包含索引列数为1或2
窄索引
可以防止回表
包含的索引列数⼤于2
宽索引
where后的条件
过滤因子
1. 在WHERE条件语句中，找到所有等值谓词中的条件列，将它们作为索引⽚中的开始列；
2. 将 GROUP BY和ORDER BY中的列加⼊到索引中；
3. 将SELECT字段中剩余的列加⼊到索引中。
针对SQL查询的理想索引设计：三星索引
2. 增加了索引维护的成本。
你能看到针对⼀条SQL查询来说，三星索引是个理想的⽅式，但实际运⾏起来我们要考虑更多维护的成本， 在索引效率和索引维护之间进⾏权衡。
为什么很难存在理想的的索引设计
为什么没有理想的索引？
一张表的索引个数不宜过多
在索引表中，我们也需要控制索引列的数量
单列索引和复合索引的⻓度也需要控制
设置合理索引
等值查询
范围查询
模糊查询
排序
索引失效
二级索引
索引使用分析
深入浅出索引
Hash 索引不能进行范围查询，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。
Hash 索引不支持联合索引的最左侧原则
Hash 索引不支持 ORDER BY 排序
另外 MySQL 中的 Memory 存储引擎支持 Hash 存储
对比
如何读取数据
做checkpoint的机制将数据回写到磁盘
执行SQL语句的时候更新了缓存池中的数据，那么这些数据会同步到磁盘上吗？
指的是缓冲池中被修 改过的页，与磁盘上的数据不一致。
脏页（dirty page）
show variables like 'innodb_buffer_pool_size'
查看缓冲池的大小
set global innodb_buffer_pool_size = 134217729
设置缓冲池大小 128MB
mysql > show variables like 'innodb_buffer_pool_instances'
查看缓冲池个数
然后 再针对innodb_buffer_pool_instances参数进⾏修改
开启多个缓冲池，你先需要 将innodb_buffer_pool_size参数设置为小于等于1GB
数据库缓冲池
如果该数据存在于内存中，基本上执行时间在1ms左右，效率还是很高的。
1. 内存读取
如果数据没有在内存中，就需要在磁盘上对该⻚进⾏查找，整体时间预估在10ms左右
2.随机读取
3. 顺序读取
通过last_query_cost统计SQL语句的查询成本
数据加载的三种⽅式
缓冲池
全局锁的典型使用场景是，做全库逻辑备份
Flush tables with read lock (FTWRL)
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟
readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库
既然要全库只读，为什么不使用 set global readonly=true 的方式呢
全局锁
另一类表级的锁是 MDL（metadata lock)
表锁的语法是 lock tables … read/write。
表级锁
MyISAM不支撑行锁
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议
行锁
低于行锁
加锁开销
高于行锁
锁死概率
并发性能
页锁
按照粒度划分
读锁或 S 锁
共享锁
排它锁也叫独占锁、写锁或 X 锁
排它锁
意向锁
数据库管理的角度
乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。
适用于读多写少
乐观锁
对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性
适用于写操作多的场景
悲观锁
程序员编程角度
不同数据库或引擎支持情况
如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步来获取，这样可以减少死锁发生的概率；
如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；
不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生的概率
使用过程中避免锁死的方法
将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑
死锁检测
innodb_lock_wait_timeout 来设置
直接进入等待，直到超时
使用MySQL自身提供的机制
如何避免锁死
MVCC是通过数据⾏的多个版本管理来实现数据库的并发控制
读写互相不阻塞
降低了死锁概率
解决了一致性读写问题
使用MVCC的好处
读取的是快照数据(历史版本)
快照读
当前读
Undolog
MV
来决定数据是否显示
针对不同的隔离级别，read view的生成策略不同。
Read View
CC
InnoDB
什么是MVCC
事务版本号
行记录的隐藏列
InnoDB将我们的行记录快照保存在了Undo Log里
Undo Log
InnoDB中的MVCC
1. trx_ids，系统当前正在活跃的事务ID集合。
2. low_limit_id，活跃的事务中最⼤的事务ID。
3. up_limit_id，活跃的事务中最⼩的事务ID。
4. creator_trx_id，创建这个Read View的事务ID。
Read View的结构
这个⾏记录在这些活跃的事务创建之前就已经提 交了，那么这个⾏记录对该事务是可⻅的
trx_id < 活跃的最⼩事务ID（up_limit_id）
该⾏记录在这些活跃的事务创建之后才创建，那 么这个⾏记录对当前事务不可⻅
trx_id > 活跃的最⼤事务ID（low_limit_id）
up_limit_id < trx_id < low_limit_id
Read View的原则
1. ⾸先获取事务⾃⼰的版本号，也就是事务ID；
2. 获取Read View；
3. 查询得到的数据，然后与Read View中的事务版本号进⾏⽐较；
4. 如果不符合ReadView规则，就需要从Undo Log中获取历史快照；
5. 最后返回符合规则的数据
查询数据的步骤
Read View是如何工作的
采用读取undolog的快照
读未提交
读取undolog的当前读
读已提交
在一个事务的select时只会获取一次read view
可重复读
可重复读+ next-key
MVCC下各种隔离级别的实现流程
在可重复读的情况下，InnoDB可以通过Next-Key锁+MVCC来解决幻读问题。
在读已提交的情况下，即使采⽤了MVCC⽅式也会出现幻读。
记录锁：针对单个行记录添加锁
间隙锁（Gap Locking）：可以帮我们锁住一个范围（索引之间的空隙），但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。
Next-Key 锁：帮我们锁住一个范围，同时锁定记录本身，相当于间隙锁 + 记录锁，可以解决幻读的问题。
innodb锁三种类
InnoDB是如何解决幻读的
MVCC：Multiversion Concurrency Control
锁
innodb引擎
当前页做了什么改动
保持持久化
当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了
执行事务的时候，写完内存和日志，事务就算完成
先写日志再写磁盘。
WAL(write ahead logging)
恢复redolog上已经提交的数据
crash safe 保证及时数据库异常重启，之前提交的记录不会丢失
物理日志，记录在某数据页上作了什么修改
innodb_flush_log_at_trx_commit 设置为1时，每次事务的redo log都直接持久化到磁盘
redolog
sever层
逻辑日志，记录语句的原始逻辑
大小不固定，可追加写入，写到一定大小后切换文件
sync_binlog设置为1时，每次事务的binglog都持久化到磁盘
主从复制时，slave去拉取master的binlog在本地执行； 伪装成slave，来接收binlog，然后解析数据，做业务处理，比如监听数据改动，做业务监控，缓存清理等。
基于SQL语句
statement
基于内容
row
混合
mix
三种格式
binlog
逻辑日志
主要目的是记录一个事物的改动 ，这样如果这个事物rollback的话，可以根据undo log来恢复到上一个状态，实现事物的一致性
实现MVCC
undolog
执行器通过引擎获取要执行的行
执行器拿到这一行数据，对数据更新，再调用引擎接口写入这行数据
引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里，此时redo log处于prepare状态。然后告知执行器执行完成了，可以提交事务
执行器生成这个操作的binglog，并把binlog写入磁盘
执行器调用引擎的提交事务接口，引擎吧刚写入的redo log改成提交状态，更新完成
两阶段提交，保证redolog与binlog的数据一致性
更新一行数据的流程
日志
SQL语句执行
查询优化器
创建视图:CREATE VIEW
视图作为一张虚拟表，帮我们封装了底层与数据表的接口
视图本身是虚拟表，本身不存储数据，如果想通过视图对底层数据表进行修改会受到限制
视图一般只用于查询
视图或者虚拟表
视图
如何实现
一次编译可多次使用，提升SQL的执行效率
减少开发工作量
安全性强
减少网络传输量
优点
存储过程在MySQL ，oracle，SQLserver不能完全通用
可移植性差
很多数据库没有调试过程
调试困难
当数据库变更时，存储过程无法使用
版本管理困难
高并发场景下，数据库表存在分库分表，存储过程可能不适用了
不适合高并发场景
存储过程优缺点
存储过程
存储过程与视图
MySQL功能分类
数据库的一个内存组件
Buffer Pool默认情况下是128MB，有一点偏小，实际生产环境下完全可以对Buffer Pool进行调整
通过命令（SHOW ENGINE INNODB STATUS）查看innodb具体情况
buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数
磁盘随机读的性能是比较差的，所以不可能每次更新数据都进行磁盘随机读，必须是读取一个数据页之后放到Buffer Pool的缓存里去，下次要更新的时候直接更新Buffer Pool里的缓存页。
Buffer Pool
写缓存
将数据页从磁盘读入内存中涉及随机 IO 访问，这也是数据库里面成本最高的操作之一
利用写缓存（Change Buffer）可以减少 IO 操作，从而提升数据库性能
通过参数innodb_change_buffer_max_size来动态设置
唯一索引的更新不能使用change buffer
change buffer 因为减少了随机磁盘访问， 所以对更新性能的提升是会很明显的
change buffer只限于用在普通索引的场景下， 而不适用于唯一索引
适合写多读少的业务，因为查询会立即触发merge过程。 这样随机访问IO的次数不会减少， 反而增加了change buffer的维护代价
Change Buffer
本质是保证事务提交之后，修改的数据绝对不会丢失的。
redo log这个机制存在的意义
日志大致格式
因为你MySQL重启之后，把你之前事务更新过做的修改根据redo log在Buffer Pool里重做一遍就可以了，就可以恢复出来当时你事务对缓存页做的修改，然后找时机再把缓存页刷入磁盘文件里去。
为什么不在修改缓存页的同时刷入磁盘？
表空间号+数据页号+偏移量+修改几个字节的值+具体的值
需要记录的东西
日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，具体修改的数据
用来缓冲redo log 写入的
通过设置mysql的innodb_log_buffer_size可以指定这个redo log buffer的大小，默认的值就是16MB，其实已经够大了，毕竟一个redo log block才512字节而已，每一条redo log其实也就几个字节到几十个字节罢了。
redo log buffer
redo log
回滚日志
如果要回滚事务，那么就基于undo log来回滚就可以了，把之前对缓存页做的修改都给回滚了就可以了。
undo log
平时将数据一行一行插入表中，这个表是个逻辑概念，在物理层面，表对应的是表空间这个概念。
表空间的磁盘文件里，有很多的数据页的。
一组数据区包含256个数据区
一个数据页16KB
一个数据区对应着连续的64个数据页，一个数据区1MB
数据区
磁盘文件里的数据页进行的磁盘随机读写
从表空间的磁盘文件里读取数据页出来，这个过程是磁盘随机读操作
所谓顺序写，就是说在一个磁盘日志文件里，一直在末尾追加日志
磁盘顺序写的性能其实是很高的
一种是对redo log、binlog这种日志进行的磁盘顺序读写
IOPS：每秒I/O数的简称，表示一秒中输入输出操作（比如读和写）的次数
可以用IOPS数值来描述一个数据库的IO操作量
IOPS和响应延迟
一种是对表空间的磁盘文件里的数据页进行的磁盘随机读写
顺序读写主要时间花费在了传输时间
随机读写需要多次寻道和旋转延迟
磁盘的顺序读写，随机读写
拓展
MySQL数据读写机制
比如在第一个数据页里有一条数据的主键是10，第二个数据页里居然有一条数据的主键值是8，那此时肯定有问题了。所以此时就会出现一个过程，叫做页分裂
分裂过程
核心目标就是保证下一个数据页里的主键值都比上一个数据页里的主键值要大
自增主键不会出现，如果你的主键不是自增的，他可能会有一个数据行的挪动过程，保证你下一个数据页的主键值都大于上一个数据页的主键值
页分裂
表的实际数据是存放在数据页里的，然后你表的索引其实也是存放在页里的，此时索引放在页里之后，就会有索引页，假设你有很多很多的数据页，那么此时你就可以有很多的索引页。
索引页里存放的就是下一层的页号和最小索引字段值
数据页/索引页互相之间都是组成双向链表的，而且也都是按照数据大小有序排列的
索引页
页里面的记录都是组成一个单向链表的，而且是按照数据大小有序排列的
下一个数据页的所有主键值大于上一个数据页的所有主键值
MYSQL
MySQL核心概念
用户反馈
我们可以通过查看数据库日志和操作系统日志等方式找出异常情况，通过它们来定位遇到的问题
日志分析
通过监控服务器的 CPU、内存、I/O 等使用情况，可以实时了解服务器的性能使用，与历史情况进行对比。
服务器资源使用监控
数据库内部状况监控
有那些方式可以知道数据库的性能问题
MySQL
oracle
SQLserver
选择合适的数据存储
数据库调优有那些维度
加缓存，或者缓存优化
1、索引设计优化2、join表过多，需要优化3、数据表设计优化
执行时间长
服务器参数调优
等待时间长
1、读写分类2、分库分表
数据量大
执行计划分析
慢SQL分析
数据库性能分析
服务器硬件优化
增加服务端可用连接数
使用连接池
减少服务端占用连接数
连接
Centos系统的内核参数，磁盘调度策略等优化
操作系统配置
数据库配置
配置优化
缓存
异步复制
半同步复制
全同步复制
多库并行复制
GTID复制
主从复制
读写分离
垂直分库
水平分库
分库分表
Haproxy+keepallived
高可用
集群（NDB Cluster）
Galera Cluster for mysql
MHA(美团高可用架构)
MGR
架构优化
控制一个库中的数据表数量
采用主从架构优化我们的读写策略
业务分库
表的字段过多，将一张表拆分为多张表
单表行数过多采用数据分配到不同表
除此以外，我们还可以对数据库分库分表
库级优化
表结构要尽量遵循第三范式的原则
如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式进行优化。反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率
在满足业务要求的情况下，数值型>字符型（CHAR>VARCHAR）
表字段的数据类型选择，关系到了查询效率的高低以及存储空间的大小。
通过改变 SQL语句的内容让 SQL 执行效率更高效，采用的方式是对 SQL 语句进行等价变换，对查询进行重写
子查询优化
逻辑查询优化
创建索引应该选择数据重复低，重复度不超过10%
要注意联合索引对索引使用的影响
要注意多个索引对索引使用的影响
物理查询优化
尽量减少扫描数据的行数
单表查询
两张表的连接
多张表的连接
查询路径优化
使用 Redis 或 Memcached 作为缓存
优化逻辑查询
优化表设计
所谓的执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描，拿到数据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序
执行计划
id越大就先执行，相同id的从上到下执行
id
PRIMARY，查询类型是主查询的意思
SUBQUERY，也就是子查询
SIMPLE，简单的查询
union_result
物化方式存储子查询的临时结果
MATERIALIZED
derived
select_type
通过主键或者唯一索引的访问，速度超高
consts
使用了普通二级索引，不是唯一索引，此时这种查询速度也是很快的
使用了主键或者唯一索引并且还使用了is null 语法
针对一个二级索引同时比较了一个值还有限定了IS NULL，类似于select * from table where name=x and name IS NULL，那么此时在执行计划里就叫做ref_or_null
ref_or_null
ref
利用索引做了范围筛选
range
直接扫描二级索引的叶子节点，也就是扫描二级索引里的每条数据
index
全表扫描，直接会扫描表的聚簇索引的叶子节点，按顺序扫描过去拿到表里全部数据
all
type
跟type结合起来的，意思就是说你type确定访问方式了，那么到底有哪些索引是可供选择，可以使用的呢，这都会放这里
possible_keys
就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息
预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据
rows
经过搜索条件过滤之后的剩余数据的百分比
例子
filtered
就是在possible_keys里实际选择的那个索引
key
索引的长度
key_len
Nested Loop（嵌套循环的访问方式）
using index(使用了覆盖索引，仅仅涉及到了一个二级索引，不需要回表)
对全表数据放到临时表里做大量的磁盘文件操作
相当耗时，性能也是极低
tmp_table_size
using temporary
Using index condition
Using where
使用内存技术来提升关联的性能
Using join buffer
把表全数据放磁盘文件排序
性能极差
sort_buffer_size
Using filesort
Extra
就是要求两个表里的数据必须是完全能关联上的，才能返回回来，这就是内连接
连接条件可以放在where语句里
inner join
在左侧的表里的某条数据，如果在右侧的表里关联不到任何数据，也得把左侧表这个数据给返回出来
左外连接
在右侧的表里如果关联不到左侧表里的任何数据，得把右侧表的数据返回出来
右外连接
外连接一般是把连接条件放在ON字句里
outer join
先从驱动表里根据WHERE条件去筛选一波数据
从驱动表里扫出来一波数据，接着又来一个for循环一条一条去被驱动表里根据ON连接条件和WHERE筛选条件去查
驱动表、被驱动表
针对多表查询的语句，我们要尽量给两个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找，接着对被驱动表查询也通过索引去查找。如果能做到这一点，你的多表关联语句性能就会很高！
IO成本
CPU成本
根据成本优化选择执行计划
多表关联
explain
优化层次
分析基本流程
执行show VARIABLES LIKE '%quer%'
第一个表示慢日志是否开启，默认关闭，第二表示慢日志的文件的位置。一般执行时间超过10秒的sql语句就会被放进这个文件里面（10秒是默认值，可以修改），这样就可以定位到执行慢的sql语句。
1.定位到执行慢的sql语句
ID：表示查询中执行select子句或操作表的顺序，当id相同时，按从上到下的顺序执行，id不同时，id数值大的先执行。
table：表示执行的表
ref:显示索引的那一列被使用了，如果可能的话，最好是一个常数。
2.用explain分析执行慢的语句
通过一些提高性能的经验来修改sql语句，或者让sql走索引。
3.对对应的sql语句进行优化
慢SQL分析思路
优化思路
优化工具
MySQL数据库调优
备份例子：mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --databases zsythink -p > zsythink.sql
--flush-logs mysqldump备份时，立刻滚动二进制Binlog文件，也就是生成新的binlog文件开始记录日志，那么我们在恢复的时候，就不用从一个binlog文件的中间开始恢复，高效！
--master-data 指定mysqldump备份时，记录对应二进制日志Binlog文件的位置=0 不记录位置，与不使用此命令没有区别，没用=1 默认值，也就是不指定值就是1，记录具体的Binlog文件的位置，会在备份文件中生成“CHANGE MASTER TO”语句，标明备份开始二进制文件的前缀名，和对应的position位置=2 与1不同的时，它会注释掉备份文件中生成的“CHANGE MASTER TO”语句，如果只是单纯的记录position的位置，建议使用值2
--routines表示备份时，存储过程和存储函数也会被备份
--triggers表示备份时，触发器会被备份
--events表示备份时，事件表会被备份
--single-transaction在备份的过程中使用--single-transaction选项，它会让备份基于独立的事务进行
属性
恢复：执行生成的sql文件就可以了
mysqldump
基本概念：二进制日志，记录对数据发生或潜在发生更改的sql语句，并以二进制的形式保存在磁盘中
复制:Mysql的master-slave协议，让slave可以通过监听binlog文件实现数据复制，达到数据一致的目的一般是在master端开启binlog，然后再slave端通过读取master端的binlog文件来实现主从数据一致性恢复：通过mysqlbinlog工具恢复数据一般我们使用mysqldump备份是全量备份，它备份某一个时间点的所有数据。如果在下一次备份前数据库故障，那么上次备份时间点到数据库故障时间点之间的数据库变化就会丢失，这就需要我们的binlog记录这些增量数据了。
作用
1.ROW 仅保存记录被修改细节，不记录sql语句上下文相关信息2.STATEMENT 每一个会修改数据的sql语句都会记录在binlog中3.MIXED 以上两种level的混合使用执行show variables like 'binlog_format'查看
binlog日志格式
show variables like ‘log_bin’查看是否开启binlog
show master logs； 查看所有Binlog的日志列表show master status； 查看最后一个Binlog日志的编号名称，以及最后一个事件结束的位置flush logs； 刷新Binlog 此刻开始产生一个新编号的Binlog日志文件reset master； 清空所有的Binlog日志（慎用）
管理binlog的sql语句
show binlog events in ‘L1WWITAFW5IVTNG-bin.000001’ from 500 limit 1，2；从指定的位置，带有偏移，开始查看binlog日志 ，限制查询的条件
查看Binlog相关的sql语句
管理binlog
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
常用选项： --start-position=953 起始pos点 --stop-position=1437 结束pos点 --start-datetime=\"2013-11-29 13:18:54\" 起始时间点 --stop-datetime=\"2013-11-29 13:21:53\" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
恢复
mysql binlog
redolog记录着物理变化，即什么页修改了什么数据。每当我们在内存中修改数据时，会在内存中写redo log，假如修改未落到磁盘，可以通过redolog恢复这部分修改
redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。
这能解释mysql如何保证redolog和binlog日志一致1.一条更新语句请求2.在内存中更新3.将修改结果更新到内存4.在n数据页的m处修改了x，并把这行记录记为preprea5.修改好了，可以提交事务6.写入binlog7.commit 提交事务8.将redolog的这条记录置为commit
日志的记录过程
redolog的大小是固定的，在mysql中可以通过修改配置参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件大小，redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。
write pos表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即write pos->checkpoint之间的部分是redo log空着的部分，用于记录新的记录，checkpoint->write pos之间是redo log待落盘的数据修改记录。当writepos追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位置记录新的日志。
redo log的记录方式
有了redo log，当数据库发生宕机重启后，可通过redo log将未落盘的数据恢复，即保证已经提交的事务记录不会丢失。
解决的问题
1、redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。2、redo log是innodb引擎层实现的，并不是所有引擎都有。3、binlog是server层实现的，意味着所有引擎都可以使用binlog日志4、binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。
它和binlog的区别
undolog是回滚日志，主要有两个作用：回滚和多版本控制(MVCC)
undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。
Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback；Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。Redo Log 保证事务的持久性Undo Log 保证事务的原子性
undo log和redo log的区别
MySQL日志与备份
https://www.processon.com/view/5f1998bbe0b34d54dac69f4a?fromnew=1
https://www.processon.com/view/5f3cdb807d9c0806d4216ad0?fromnew=1
参考资料
order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高
而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
因为在事务控制下表中的数据记录是不确定的
innodb为什么不存全部的count呢
count(字段)<count(主键 id)<count(1)≈count(*)
count(*)、count(主键 id) 和 count(1)
count
redolog写满了
内存空间不够了
空闲时机刷
InnoDB 你的磁盘能力
参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%
innodb刷脏也的考虑因素
redolog刷到磁盘的时机
analyze table 来解决
在应用端用 force index 来强行指定索引
修改语句来引导优化器
通过增加或者删除索引来绕过这个问题
索引选择不准的解决方案
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写）
而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
change buffer 和 redo log
未分类
自由主题
MySQL知识体系
0 条评论
回复 删除
下一页
