一.不同数据库的共性
数据库
磁盘
特点1
随机I/O寻道时间过长
特点2
磁盘读取数据是以盘块(block)为基本单位的。位于同一盘块中的所有数据都能被一次性全部读取出来。而磁盘IO代价主要花费在查找时间Ts上(即寻道上)。因此我们应该尽量将相关信息存放在同一盘块，同一磁道中。或者至少放在同一柱面或相邻柱面上，以求在读/写信息时尽量减少磁头来回移动的次数，避免过多的查找时间Ts。
内存
断电数据丢失
二.通用的解决方案
1.减少寻道时间
1.1索引
1.1.1数据结构
1.1.1.1 hash索引
k-v结构,不适合范围查询
1.1.1.2 有序数组
只有一层,减少I/O次数,也可以用二分法,效率比较高,但只适合存储静态数据,因为扩容成本会比较大
1.1.1.3 B+树索引
(1) 二分法
牛顿二分法,不断减半
(2) m阶树
定义
假如是m阶,则关键字最多有m-1个(这在页的分裂比较重要,后面会详细说),然后B树的数据是每个节点都会有的(这导致每个页可以存储的关键字数目变少,变相的导致树会变高,而树越高,磁盘寻道次数就可能越多,就越可能浪费时间)
示例
每个节点最多四个关键字,叶子节点从左往右,从小到大,通过指针连接是一个双向链表.(即排好序的,我们就可以用二分查找法,速度会很快)
(3) 分类
聚簇索引
叶子节点存的是整行的数据
示例
假如我们执行 select * from T where ID=500,即主键查询,则只需搜索ID这棵B+树.
非聚簇索引
叶子节点存的是主键的值
我们通过主键的值,在到主键索引上查询对应的值,这个过程也称为回表.为什么非主键索引不同样去存整行数据呢?主要考虑空间使用率,冗余数据会太多。
示例
执行的是select * from T where k=5,即普通索引的方式,则只需先搜索k索引数,获取到ID的值为500,再到ID索引树上搜索一次.
结论
主键长度越小,那么普通索引的叶子节点就越小,所占的空间也就越小.
底层双向链表
最左匹配原则
假如我们建的索引是(name,age) 我们查询的时候最好也按照where name=? and age=?去查,而不是反过来。
数据页的分裂与合并
插入操作
假如我们插入新的行ID=700,则只需要在R5的记录后面插入一个新的记录.但如果我们新插入的行ID=400,需要逻辑上挪到后面的数据,空出位置,但如果R5所在的数据页已经满了,那就会新申请一个数据页,然后将部分数据挪过去,就会造成页分裂.
示例
结论
即分裂完后,不断的将中间节点往上提
删除操作
同样删除时,会造成数据页的空洞,也即数据页并没有得到充分利用.利用率达到一定比率时会造成数据页的合并.可参考伙伴分配系统
结论
即不断的合并,然后更新Index Page
1.1.1.4 Elasticsearch的倒排索引
示例
如:Carla,Sara,Elin,Ada,Patty,Kate,Selena
如果按照这样的顺序排列，找出某个特定的 term 一定很慢，因为 term 没有排序，需要全部过滤一遍才能找出特定的 term。排序之后就变成了：
Ada,Carla,Elin,Kate,Patty,Sara,Selena
用二分查找的方式，比全遍历更快地找出目标
前缀树
这颗树是存在内存中的
A 开头的 ……………. Xxx 页
C 开头的 ……………. Xxx 页
E 开头的 ……………. Xxx 页
内存中找到第几页后,再去磁盘直接找
详细可参考
https://www.infoq.cn/article/database-timestamp-02?utm_source
1.1.2 分类
1.1.2.1 主键索引
1.1.2.2 覆盖索引
回表,主要是因为拿主键的值,假如我们的索引上存了,那就没必要回表了,可以减少树的搜索次数
1.1.2.3 前缀索引
对于列的值较长，比如BLOB、TEXT、VARCHAR，就必须建立前缀索引，即将值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
1.1.3 索引优化
语法
explain select * from t where name='张三';
字段
1.type
type显示的是访问类型，是较为重要的一个指标，一般来说，得保证查询至少达到range级别，最好能达到ref。
2.再看key
是否使用了索引,null表示没有使用索引
3.再看rows
表示扫描的行数
4.再看extra
坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
什么时候建索引
1.经常被查询的列(作为where的条件)
2.经常用于表连接的列
3.经常排序分组的列(order by 或者group by)
可以不建索引的情况
1.表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。
2.不建议建索引的情况是索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T
1.1.4 不走索引情况
1.通过索引扫描的行记录数超过全表的30%,优化器就不会走索引,而变成全表扫描
2.联合索引中,第一个查询条件不是最左索引列
3.联合索引中,第一个索引列使用范围查询,只能使用到部分索引.
4.模糊查询条件列最左以通配符%开始
5.两个单列索引,一个用户检索,一个用于排序,那么只会用到一个,可以考虑建立联合索引
6.查询字段上有索引,但使用了函数计算,这里也要注意隐式函数转换,比如定义的是int类型,你传的查询条件时string类型
2.多利用内存
2.1 排序buffer
当需要排序的字段无法通过索引查找直接完成时，MySQL会为排序分配一块内存(sort buffer)。该buffer的大小可以通过参数sort_buffer_size来设置。MySQL遵循“多利用内存，尽量减少磁盘访问”的设计原则。当sort buffer不足以完成需要的排序时，MySQL会利用磁盘临时文件来辅助排序(使用归并排序算法)。
通过设置max_length_for_sort_data参数可以让MySQL调整排序时所用的算法：
2.2 随机写,变为顺序写
3.读多写少
读多
B+树索引
写少
insert和delete较多时造成页的分裂
4.写多读少
LSM树
多利用内存,多次写入内存达到阙值后再合并再统一一次性写入磁盘
二.Mysql引擎
Memory引擎
说明:
存储引擎默认使用哈希(HASH)索引，其速度比使用B型树(BTREE)索引快。如果我们需要使用B型树索引，可以在创建索引时选择使用。
使用场景:
Memory表的所有数据都是存储在内存上的，如果内存出现异常会影响到数据的完整性。
如果重启机器或者关机，表中的所有数据都将消失，因此，基于Memory存储引擎的表的生命周期都比较短，一般都是一次性的。
MyISAM
说明:
插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。
Innodb引擎
1. 数据一致
1.1 强一致
1.1.1 加锁
1.1.1.1 按性质
1.悲观锁
定义
不管三七二十一,上来先加锁
2.乐观锁
定义
while循环+CAS update when version=?
问题
1.ABA问题
定义
解决方案
带上时间戳
2.自旋时间过长,cpu消耗过高
3. 选择方案
case wehn 直接锁的时间<自旋时间 then 悲观锁 else 乐观锁 end;
1.1.1.2 按对象
1.全局锁
作用
让这个库处于只读的状态
适用场景
做全库的逻辑备份的时候
语法
Flush tables with read lock
2.表锁
语法
lock tables .. read/write
3.元数据锁
作用
避免因为表结构变更导致的数据不一致.
适用场景
不需要显示使用,在访问表时自动加上。
注意
事务中的MDL锁,是在语句开始的时候申请,需要等到整个事务提交后再释放,而不是语句结束后立即释放,所以要避免大事务导致的长时间锁表.
结论
其次再大表新增字段或者索引的时候也需要慎重
4.行锁
4.1.独占锁
定义
锁住某一行
加锁位置
加在索引上
4.2.间隙锁
定义
锁住一个范围
加锁位置
一个范围
作用
避免幻读
1.1.1.3 加锁规则
2个原则
原则1:加锁的基本单位是next-key lock 且是前开后闭区间
原则2:查找过程中访问到的对象才会加锁
2个优化
优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁(只锁一行)
优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁(锁一个范围)
1个bug
唯一索引上的范围查询会访问到不满足条件的第一个值为止
1.1.1.4 加锁协议
两阶段锁协议
说明
innodb事务中,行锁时在需要的时候才加上,但并不是不需要了就立刻释放,而是要等到事务结束时才释放.
结论
如果事务中会锁多行,要把最可能造成锁冲突,最可能影响并发度的锁往后面放.
1.1.1.5 锁的监控
Information_schema.INNODB_TRX
Information_schema.INNODB_LOCKS
Information_schema.INNODB_LOCK_WAITS
1.1.1.6 死锁检测
1.设置超时时间
参数innodb_lock_wait_timeout,在innodb中,默认50s
2.发起死锁检测
发现死锁后,主动回滚死锁链条中的某一事务,可以设置innodb_deadlock_detect=on
1.2 最终一致
1.Copy on wirte,建立副本(对自己来说某个时点一致) MVCC 多版本控制
2.paxos协议
3.zab协议
2. 数据不一致
2.1.脏读
A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。
2.2.不可重复读(侧重于更新)
事务A重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务B修改过了。
2.3.幻读(侧重于插入)
事务A重新执行一个查询，返回一系列符合查询条件的行，发现其中插入了被事务B提交的行。
3.事务(ACID)
3.1特性
1.原子性(Atomic)
start a,b,c end commit;
2.一致性(Consistent)
2.1 Mysql层面
2.1.1 binglog
2.1.1.1 按记录格式分类
(1)记录整个sql
可能由于版本兼容性问题导致数据迁移性不好
(2)记录修改记录
a=2,b=3
(2.1)适用场景
数据的全量恢复
(2.2)写入时机
事务执行过程中,先把日志写入到binlog cache,事务提交时,再把binlog cache写入到Binlog文件中(也就是磁盘上了)
(2.3)写入步骤
1.写到内存,即write
2.刷新到磁盘,即fsync
(2.4)参数
binlog_cache_size
用于控制单个线程内binlog_cache所占内存的大小,如果超过这个数,就要暂存到磁盘(磁盘swap)
sync_binlog
1.sync_binlog=0时,每次提交事务都只write,不fsync
2.sync_binlog=1时,每次提交事务都会执行fsync
3.sync_binlog=N(n>1),每次提交事务都write,但累计N个事务才fsync
默认sync_binlog=1
备注
一个事务的binlog是不能被拆开的，因此不论事务有多大,也要确保一次性写入.
结论
尽量避免大事务
2.1.2 Innodb层面
redo log
定义
记录修改后的值
适用场景
数据的增量恢复
写入时机
1.后台轮询线程
redo log是事务执行过程中就会直接写入到redo log buffer中,持久化到磁盘也就是事务可能没提交(备注mysql会有个后台轮询线程),每隔1s轮询一次
2.redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候
3.并行的事务提交的时候,顺带将这个事务的redo log buffer持久化到磁盘
写入步骤
1.先写入到redo log buffer
2.再write到磁盘,再进行磁盘持久化(fsync)
参数
innodb_flush_log_at_trx_commit
1.值等于0:每隔1s就会将redo log buffer中的数据写入到redo log 文件,同时进行刷盘操作.
2.值等于1,每次事务提交,都会触发redo log thread将日志缓冲区中的数据写入文件,并刷新到磁盘
3.值等于2,每次事务提交,都会把redo log buffer中的数据写入到redo log文件,但不会同时刷新到磁盘
默认:一般都设为1,和控制Binlog的参数sync_binlog都设置为1
binlog_group_commit_sync_delay
说明
表示延迟多少微秒后才会调用fsync
作用
减少I/O次数,控制I/O性能瓶颈了
binlog_group_commit_sync_no_delay_count
说明
表示累积多少次以后才会调用fsync
最后再来看一下
作用
减少I/O次数,控制I/O性能瓶颈了
undo log
定义
记录修改前的值
适用场景
rollback
2.1.3 两阶段提交
prepare准备阶段
1.事务SQL先写入redo log buffer,然后做一个准备标记
2.再将log buffer中的数据刷新到redo log
commit阶段
1.将事务产生的binlog写入文件,刷入磁盘
2.再在redo log 中做一个事务提交的标记,并把binlog写成功的标记也一并写入到redo log文件。
2.1.4 数据恢复
binglog+redo log
怎么保证一致性的呢?
情况一
准备阶段,redo log刷新到了磁盘,但是binlog写盘前发生了mysql实例crash.
这时我们redo log是没有binlog提交的日志的,所以我们可以通过回滚来保证数据库一致性
情况二
binlog写盘成功,这时mysql实例crash
进行数据恢复时,只需要使用redo log重做一次就好了。
2.1.5 redolog与binglog的三点区别
1.redo log是InnoDB引擎特有的;binlog是Mysqlde Server层实现的,所有引擎都可以使用
2.redo log是物理日志,记录的是在"某个数据页上做了什么修改"(同时可以引申出后面要说的double write);binlog是逻辑日志,记录的是语句的原始逻辑,比如"给id=2这一行的c字段加1"
3.redo log 是循环写的,空间固定会用完(所以会有checkpoint和刷新到磁盘);binlog是追加写的,当文件写到一定大小后会切换到下一个,并不会覆盖之前的日志.
3.隔离性(Isolated)
(1).读未提交
select 什么都不加
脏读 Yes
不可重复读 Yes
幻读 Yes
(2).读已提交
快照读,Copy on write,副本的概念.不会加间隙锁
脏读 NO
不可重复读 Yes
幻读 Yes
(3).可重复读
快照读,select 会加间隙锁
脏读 NO
不可重复读 NO
幻读 Yes
说明
在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)
范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读
(4).串行化
所有select语句都会被隐式的转化为select ... in share mode.
脏读 NO
不可重复读 NO
幻读 NO
如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。
4.持久性(Durable)
(1).写到内存,即write
(2).刷新到磁盘,即fsync
(3).双写(double write)
实现方式
1.加锁
2.多版本控制
表中的一行记录,可能有多个版本,每个版本都有自己的row_trx_id(按申请顺序严格递增)
在可重复读的隔离级别下,事务启动的瞬间，会有一个视图
结论
查询都是一致性读只使用多版本控制(select 加update的话也是当前读)
更新都是当前读(加锁,就只能当前读(当前可以理解为需要是最新的))
插入缓冲(change buffer)
说明
和磁盘打交道,最主要的性能问题就是I/O.而插入缓冲的作用就是把普通索引上的DML操作从随机I/O变成顺序I/O
原理
先判断插入的普通索引页是否在缓冲池中,如果在就可以直接插入,不在的话就需要先放到change_buffer中,然后进行change_buffer和普通索引的合并操作,可以将多个操作合并为一个操作中。
两次写(double write)
说明
主要用来保证数据写入的安全性,redo log是物理日志,如果页都损坏了,是没办法进行恢复的,所以我们需要一个数据页的备份.
原理
不同的磁盘,物理上的备份
参数
通过innodb_doublewrite为0来关闭双写缓冲.
自适应哈希索引(adaptive hash index)
说明
用于监控我们的查询是否可以通过建立哈希索引得到优化,可以的话,它会自动帮助我们完成这件事.
参数
通过innodb_adaptive_hash_index来进行控制,默认是开启的
网友评论