mysql
2020-05-07 09:38:05 167 举报AI智能生成
高性能MySQL
作者其他创作
大纲/内容
事务
事务的特性
ACID
事务并发所带来的问题
不可重复度
幻读
在可重复读隔离级别下， 普通的查询是快照读， 是不会看到别的事务插入的数据的
幻读在“当前读”下才会出现
幻读仅专指“新插入的行”
新插入记录这个动作， 要更新的是记录之间的“间隙”
即使把所有的记录都加上锁， 还是阻止不了新插入的记录
update修改的结果不能称为幻读
如何解决幻读
间隙锁(GapLock)
间隙锁和next-key lock
小结
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
MySQL 事务隔离级别会产生的并发问题
不同数据库在实现时，产生的并发问题是不同的
READ UNCOMMITTED（未提交读）
会导致脏读
不允许脏写
READ COMMITTED（提交读）
会导致不可重复读
不会发生脏读和脏写
REPEATABLE READ（可重复读）
SQL标准里规定RR级别是可以发生幻读的，但是MySQL的RR级别是可以避免幻读发生
不会发生脏写、脏读、不可重复读
MySQL默认隔离级别
依托MVCC机制，能让RR级别避免不可重复读和幻读的问题
SERIALIZABLE（可串行化）
MySQL InnoDB 采用 MVCC 来支持高并发
MySQL 默认的事务隔离级别为可重复读（repeatable-read）
解决可能出现的数据和日志不一致问题
读提交隔离级别加binlog_format=row的组合
如果读提交隔离级别够用， 也就是说， 业务不需要可重复读的保证，
这样考虑到读提交下操作数据的锁范围更小（没有间隙锁）
这样考虑到读提交下操作数据的锁范围更小（没有间隙锁）
修改MySQL的默认事务隔离级别的命令
当前读
for update是当前读
当前读的规则， 就是要能读到所有已经提
交的记录的最新值
交的记录的最新值
快照读
普通的查询是快照读
脏读和脏写问题
脏写
本质就是事务B去修改了事务A修改过的值，但是此时事务A还没提交，所以事务A随时会回滚，导致事务B修改的值也没了，这就是脏写的定义。
怎么防止脏写
依靠锁机制让多个事物更新一行数据的时候串行化，避免同时更新一行数据
脏读
本质其实就是事务B去查询了事务A修改过的数据，但是此时事务A还没提交，所以事务A随时会回滚导致事务B再次查询就读不到刚才事务A修改的数据了！这就是脏读。
不可重复读和幻读
事务的特性原子性、隔离性、持久性是通过什么技术实现的？
原子性：通过undo log实现
隔离性：通过mvcc实现
持久性：通过redo log 和 double write 双写缓冲来实现的
RU（未提交读）不能解决问题
RC（已提交读） 解决脏读
RR（可重复读） 解决脏读、不可重复读
Serializable（可串行化） 解决脏读、不可重复度、幻读
RC（已提交读） 解决脏读
RR（可重复读） 解决脏读、不可重复读
Serializable（可串行化） 解决脏读、不可重复度、幻读
相关概念
redo log
保证事务提交之后，修改的数据绝对不会丢失
binlog
主要用于主从复制
undo log 存在表空间中，会有purge线程清理
rollback segment
事务执行过程
用排他锁锁定该行
记录 undo/redo log，确保日志刷到磁盘上持久存储。
更新数据记录，缓存操作并异步刷盘。
将事务日志持久化到 binlog。
回滚事务：根据当前回滚指针从undo log中找出事务修改前的版本，并恢复
提交事务，在 redo log 中写入commit记录。
释放锁资源
异步清理undo段信息
清理保存点列表
恢复
通过 redo log 来重做事务或通过 undo log 来回滚
以binlog 来确定是提交还是回滚
锁
锁的粒度
行锁
共享锁
加锁方式：select * from student where id = 1 LOCK IN SHARE MODE
读锁，又称共享锁（Share locks，简称 S 锁）
当前读
与排它锁互斥
共享锁与共享锁不互斥
实际开发过程中，主动加共享锁情况较为少见
排他锁
又称写锁，简称X锁，不能与其他锁（包括共享锁和排他锁）并存
加锁方式
自动：delete/update/insert 默认加上X锁
手动：select * from student where id = 1 for update
此时如果执行普通查询，默认是不加锁的，通过mvcc机制读快照版本，可以手动加共享锁，但是会与排他锁互斥
一个事务提交了才会释放自己的独占锁，唤醒下一个事务执行
不是太建议在数据库粒度去通过行锁实现复杂的业务锁机制，
而更加建议通过redis、zookeeper来用分布式锁实现复杂业务下的锁机制，其实更为合适一些
而更加建议通过redis、zookeeper来用分布式锁实现复杂业务下的锁机制，其实更为合适一些
表锁
加锁前提：任意一行数据都没有被其他事务锁定
意向锁
意向共享锁（IS）/意向排它锁（IX）
意向锁之间不互斥
意向独占锁与意向共享锁之间不互斥
自增锁
AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现
AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁
自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况
锁的作用
解决资源竞争问题
锁的原理
锁住的是索引
表没有建立索引，会全表扫描，造成锁表
如果用辅助索引加锁，主键索引也会锁住
因为辅助索引需要去主键索引检索数据(覆盖索引不会，因为覆盖索引不需要回表)
因为辅助索引需要去主键索引检索数据(覆盖索引不会，因为覆盖索引不需要回表)
区间划分
间隙锁（Gap Lock）
执行select * from t where id = 9 for update语句， 由于id=9这一行并不存在， 因此会加上间隙锁
阻塞插入
间隙锁之间不会冲突，有可能导致死锁
间隙锁是开区间
表id最大值10，where id > 20 for update
此时插入一条id=11的数据，会插入失败！
因为间隙锁会锁定id>10后面的数据
表id最大值10，where id > 20 for update
此时插入一条id=11的数据，会插入失败！
因为间隙锁会锁定id>10后面的数据
间隙锁的引入， 可能会导致同样的语句锁住更大的范围，会影响系统的并发度
间隙锁是在可重复读隔离级别下才会生效的
临键锁（Next-KeyLock）
条件范围
包含记录和空间
包含记录和空间
记录锁（Record Lock）
精准匹配到一条记录的时候，锁定一行记录
where id = '4' for update
事务隔离级别的实现
RR
支持行锁、间隙锁、临键锁
RC
只支持行锁，没有解决幻读
增加并发性
死锁
死锁的发生
互斥
不可剥夺，将锁持续到底
形成等待环路
查看哪些事务持有锁
show status like 'innodb row lock %'
查找事务线程id：select * from information schema.INNODB_TRX;
kill 事务线程id
kill 事务线程id
避免
顺序访问
数据排序
申请足够级别的锁
避免没有where条件的操作
大事务分解成小事务
使用等值查询而不是用范围查询，减小锁定范围
悲观锁与乐观锁
乐观锁
大多是基于数据版本（ Version ）记录机制实现
实际就是通过版本号，从而实现 CAS 原子性更新
悲观锁
依靠数据库提供的锁机制，以保证操作最大程度的独占性
就是我们上面看到的共享锁和排他锁
性能优化
需要了解一条select语句的执行过程
客户端发送一条查询给服务器
服务器先检查查询缓存， 如果命中了缓存，则立刻返回存储在缓存中的结果。否则进人下一阶段
服务器端进行sQL解析、预处理，再由优化器生成对应的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
将结果返回给客户端
执行过程
客户端连接查询缓存
连接：使用连接池工具
连接数设置
有时候连接数越大反而效率越低
减少CPU上下文切换
配置优化
操作系统配置
数据库配置
缓存服务
主从复制
启用全局事务id
使用中间件技术简单配置即可
如何减少延迟？
异步复制、半同步复制、全同步复制？
单表数据过大如何优化
垂直分库
水平分库分表
分析慢查询日志
explain
执行计划
所谓的执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描，
拿到数据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序
拿到数据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序
id
id越大就先执行，相同id的从上到下执行
select_type
PRIMARY，查询类型是主查询的意思
SUBQUERY，也就是子查询
SIMPLE，简单的查询
union_result
union_result
MATERIALIZED
物化方式存储子查询的临时结果
derived
type
consts
通过主键或者唯一索引的访问，速度超高
ref
使用了普通二级索引，不是唯一索引，此时这种查询速度也是很快的
使用了主键或者唯一索引并且还使用了is null 语法
ref_or_null
针对一个二级索引同时比较了一个值还有限定了IS NULL，
类似于select * from table where name=x and name IS NULL，
那么此时在执行计划里就叫做ref_or_null
类似于select * from table where name=x and name IS NULL，
那么此时在执行计划里就叫做ref_or_null
range
利用索引做了范围筛选
index
直接扫描二级索引的叶子节点，也就是扫描二级索引里的每条数据
all
全表扫描，直接会扫描表的聚簇索引的叶子节点，按顺序扫描过去拿到表里全部数据
possible_keys
跟type结合起来的，意思就是说你type确定访问方式了，
那么到底有哪些索引是可供选择，可以使用的呢，这都会放这里
那么到底有哪些索引是可供选择，可以使用的呢，这都会放这里
ref
就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息
rows
预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据
filtered
经过搜索条件过滤之后的剩余数据的百分比
例子
key
就是在possible_keys里实际选择的那个索引
key_len
索引的长度
Extra
Nested Loop（嵌套循环的访问方式）
using index(使用了覆盖索引，仅仅涉及到了一个二级索引，不需要回表)
using temporary
对全表数据放到临时表里做大量的磁盘文件操作
相当耗时，性能也是极低
tmp_table_size
Using index condition
Using where
例子
Using join buffer
使用内存技术来提升关联的性能
Using filesort
把表全数据放磁盘文件排序
性能极差
sort_buffer_size
多表关联
inner join
就是要求两个表里的数据必须是完全能关联上的，才能返回回来，这就是内连接
连接条件可以放在where语句里
outer join
左外连接
在左侧的表里的某条数据，如果在右侧的表里关联不到任何数据，也得把左侧表这个数据给返回出来
右外连接
在右侧的表里如果关联不到左侧表里的任何数据，得把右侧表的数据返回出来
外连接一般是把连接条件放在ON字句里
驱动表、被驱动表
先从驱动表里根据WHERE条件去筛选一波数据
从驱动表里扫出来一波数据，接着又来一个for循环一条一条去被驱动表里根据ON连接条件和WHERE筛选条件去查
针对多表查询的语句，我们要尽量给两个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找，
接着对被驱动表查询也通过索引去查找。如果能做到这一点，你的多表关联语句性能就会很高！
接着对被驱动表查询也通过索引去查找。如果能做到这一点，你的多表关联语句性能就会很高！
根据成本优化选择执行计划
IO成本
CPU成本
show warnings技巧的使用
案例
MySQL内部自动使用了半连接优化，结果半连接（semi join）的时候导致大量无索引的全表扫描，引发了性能的急剧下降
通过禁用MySQL的半连接优化或者是改写SQL语句结构来避免自动半连接优化
我们的MySQL数据库在选择索引的时候，选择了一个不太合适的索引，导致了性能极差，引发了慢查询。
慢查询导致的问题
例子
通过force index语法来强制某个SQL用我们指定的索引
慢查询排查
主要就是看他的执行计划
检查MySQL服务器的负载
尤其看看磁盘、网络以及CPU的负载，是否正常
SQL调优的利器 profiling
这个工具可以对SQL语句的执行耗时进行非常深入和细致的分析
优化特定类型的查询
优化 count() 查询
统计结果集行数
统计列的数量
MySQL高可用方案
主从复制
问题
主从进行数据复制的时候，数据不一致
主节点宕机后，怎么自动切换从节点对外提供服务
集群（NDB Cluster）
Galera Cluster for mysql
MHA(美团高可用架构)
MGR
数据类型及占用字节数
索引
Hash 索引
适用于只有等值查询的场景，比如 Memcached、redis 及其他一些 NoSQL 引擎
无法利用索引完成排序
因为存放的时候是经过 Hash 计算过的，计算的 Hash 值和原始数据不一定相等，所以无法排序
Hash索引仅仅能满足 =，IN 和 <=> 查询
因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索
BTree索引和hash索引的区别
主键索引
存储索引和数据
也叫聚簇索引
索引的键值逻辑顺序跟表数据行的存储顺序一致
除了主键索引其他的都是辅助索引
如果一个表没有主键索引，存储引擎会找一个不包含空值的唯一索引作为默认的聚集索引(会隐式定义个主键作为聚簇索引)
注意点有哪些
主键需要是自增 ID
按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键
对于InnoDB 表，我们一般定义主键为不可更新
更新主键的代价很高，因为将会导致被更新的行移动
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据
主键 ID 建议使用整型
每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ，每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID
主键目录
就是把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录
假设你有很多的数据页，在主键目录里就会有很多的数据页和最小主键值，此时你完全可以根据二分查找的方式来找你要找的id到底在哪个数据页里！
叶子节点就是数据页自己本身
聚簇索引
InnoDB必须要有聚簇索引，但不一定要有主键索引
InnoDB一定有而且只有一个聚簇索引
如果定义了主键，主键就是聚簇索引
如果没有定义主键，第一个非空且唯一的列就是聚簇索引
如果没有符合条件的列，会自动创建一个隐藏的row-id作为聚簇索引
辅助索引
存储索引和主键值
二级索引的叶节点存储的是主键值，而不是行指针
为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间
通过辅助索引来查询数据时，需要进过两步
首先，InnoDB 存储引擎会遍历辅助索引找到主键
然后，再通过主键在聚集索引中找到完整的行记录数据，这个过程也称之为回表
列的离散度
越高越适合建立索引
区分度低的字段，例如性别，不适合建立索引
组合索引把区分度高的值放前面
联合索引最左匹配
不是必须严格按照我们创建的索引顺序，优化器会帮我们做优化；
where a ='' and b='' 和 where b='' and a='' 都会走索引；优化器会帮我们调整顺序
where a ='' and b='' 和 where b='' and a='' 都会走索引；优化器会帮我们调整顺序
复合索引index(a,b,c)相当于创建了3个索引
index(a,b,c)
index(a,b)
index(a)
index(a,b,c)
index(a,b)
index(a)
不可中断
覆盖索引
什么叫回表
通过辅助索引检索到主键值之后，在去主键索引检索数据，叫回表
直接从索引上就可以获取到相应的数据，不需要回表
用到了覆盖索引（直接从索引树上获取到数据）
index(a,b)
select a from table where a=''
select a from table where a=''
未用到覆盖索引（需要回到主键索引中获取所需的数据）
index(a,b)
select * from table where a=''
select * from table where a=''
能大大提高查询性能，只需要读取索引而不需要读取数据
优点
索引项通常比记录要小，所以MySQL访问更少的数据
索引都按值得大小存储，相对于随机访问记录，需要更少的I/O
限制
覆盖索引也并不适用于任意的索引类型，索引必须存储列的值
不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引
如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降
前缀索引
使用前缀索引， 定义好长度， 就可以做到既节省空间， 又不用额外增加太多的查询成本
没有定义好长度，可能会导致查询语句读数据的次数变多
如何确定应该使用多长的前缀
select count(distinct email) as L from User
然后， 依次选取不同长度的前缀来看这个值， 比如我们要看一下4~5个字节的前缀索引
select count(distinct left(email,4)) as L4 ,count(distinct left(email,5)) as L5 from User
使用前缀索引很可能会损失区分度， 所以你需要预先设定一个可以接受的损失比例
不能使用覆盖索引
什么时候用不到索引
出现隐式转换
有数据类型转换， 就需要走全索引扫描
隐式字符编码转换
两个表的字符集不同， 一个是utf8， 一个是
utf8mb4， 所以做表连接查询的时候用不上关联字段的索引
utf8mb4， 所以做表连接查询的时候用不上关联字段的索引
字符集utf8mb4是utf8的超集， 所以当这两个类型的字
符串在做比较的时候， MySQL内部的操作是， 先把utf8字符串转成utf8mb4字符集， 再做比较
符串在做比较的时候， MySQL内部的操作是， 先把utf8字符串转成utf8mb4字符集， 再做比较
转换过程中，需要对索引字段做函数操作， 优化器会放弃走树搜索功能
字符集不同只是条件之一， 连接过程中要求在被驱动表的索引字段
上加函数操作， 是直接导致对被驱动表做全表扫描的原因
上加函数操作， 是直接导致对被驱动表做全表扫描的原因
优化
字段的字符集改成一样， 这样就没有字符集转换的问题了
但如果数据量比较大， 或者业务上暂时不
能做这个DDL的话， 那就只能采用修改SQL语句的方法了（条件右边主动将字符编码转换）
能做这个DDL的话， 那就只能采用修改SQL语句的方法了（条件右边主动将字符编码转换）
where条件后面出现函数操作
索引字段不能进行函数操作，但是索引字段的参数可以玩函数
B-Tree 索引
树结构（网上找的图）
每个节点中不仅包含数据的 key 值，还有 data 值。
而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率
磁盘的相关知识
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ，在 MySQL 中可通过如下命令查看页的大小
mysql> show variables like 'innodb_page_size';
mysql> show variables like 'innodb_page_size';
B+Tree 索引
树结构（网上找的图）
所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，
而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度
B+Tree 相对于 B-Tree 有几点不同
非叶子节点只存储键值信息
所有叶子节点之间都有一个链指针
数据记录都存放在叶子节点中
每个叶节点组成双向链表，然后数据页内部的数据行是组成单向链表的，而且数据行是根据主键从小到大排序的
MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作
索引的类型
普通索引：最基本的索引，没有任何约束。
唯一索引：与普通索引类似，但具有唯一性约束。
主键索引：特殊的唯一索引，不允许有空值。
复合索引：将多个列组合在一起创建索引，可以覆盖多个列
外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。
全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。
唯一索引与普通索引的选择
主要区别是change buffer
对于唯一索引，需要先判断是否违反唯一约束；要判断表中是否存在这个数据，而这必须要将数据读入内存才能判断，读入内存涉及随机IO访问，是数据库里面成本最高的操作之一；而普通索引可以使用change buffer，减少了随机磁盘访问，所以对更新性能的提升是会很明显的
利用写缓存（Change Buffer）可以减少 IO 操作，从而提升数据库性能
唯一索引需要校验唯一，但是开销很小
建立索引的事项及提高性能的手段
不加索引将进行全表扫描
扫描过程
优缺点
优点
可以直接根据某个字段的索引B+树来查找数据，不需要全表搜索，性能提升是很高的
缺点
一个是空间上的
每一棵B+树都要占用很多的磁盘空间。在进行增删改查的时候，每次都需要维护各个索引的数据有序性
一个是时间上的
不停的增删改查，必然会导致各个数据页之间的值大小可能会没有顺序，或者是你不停的插入数据，各个索引的数据页就要不停的分裂，不停的增加新的索引页，这个过程都是耗费时间的。
索引设计
第一个索引设计原则：针对SQL语句里的where条件、order by条件以及group by条件去设计索引
尽量使用那些基数比较大的字段
对于那种比较长的字符串类型的列，可以设计前缀索引
设计索引别太多，建议两三个联合索引就应该覆盖掉这个表的全部查询了
必须把经常用做范围查询的字段放在联合索引的最后一个，才能保证SQL里每个字段都能基于索引去查询
核心重点:尽量利用一两个复杂的多字段联合索引，抗下80%以上的 查询，
然后用一两个辅助索引抗下剩余20%的非典型查询，
保证99%以上的查询都能充分利用索引，就能保证查询速度和性能！
然后用一两个辅助索引抗下剩余20%的非典型查询，
保证99%以上的查询都能充分利用索引，就能保证查询速度和性能！
Innodb存储引擎
最小储存单元——页（Page），一个页的大小是16K
页可以用于存放数据也可以用于存放键值+指针
在B+树中叶子节点存放数据，非叶子节点存放键值+指针
索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据
在查找数据时一次页的查找代表一次IO
一个页中能存储多少行数据呢？
假设一行数据的大小是1k，那么一个页可以存放16行这样的数据
只有叶子节点才存储数据
多版本并发控制（MVCC）
基于 undo log 多版本链条以及 ReadView 机制实现
每条记录都会有两个隐藏字段trx_id,一个是roll_pointer
trx_id
当前事务id
roll_pointer
指向日志记录undo log的指针
每次增删改操作时会生成一个undo log
通过undo log多版本链条，加上你开启事务时候生产的一个ReadView，然后再有一个查询的时候，根据ReadView进行判断的机制，你就知道你应该读取哪个版本的数据。
可以保证你只能读到你事务开启前，别的提交事务更新的值，还有就是你自己事务更新的值。假如说是你事务开启之前，就有别的事务正在运行，然后你事务开启之后 ，别的事务更新了值，你是绝对读不到的！或者是你事务开启之后，比你晚开启的事务更新了值，你也是读不到的！
ReadView机制
基于undo log多版本链条实现
简单来说，就是你执行一个事务的时候，就给你生成一个ReadView，里面比较关键的东西有4个
一个是m_ids，这个就是说此时有哪些事务在MySQL里执行还没提交的；这个事物id对应的数据是不能查询到的
一个是min_trx_id，就是m_ids里最小的值；
一个是max_trx_id，这是说mysql下一个要生成的事务id，就是最大事务id；
一个是creator_trx_id，就是你这个事务的id
基于ReadView机制来实现RC隔离级别
关键点在于：每次发起查询，都重新生成一个ReadView
只要事物B的id不在m_ids列表内，说明这个事务B在事物A生成本次ReadView之前就已经提交了
正确的理解MySQL的MVCC及实现原理
MySQL中RR隔离级别，是如何同时避免不可重复读问题和幻读问题的。
不可重复读和幻读
如何实现的
相关概念
Buffer Pool
数据库的一个内存组件
Buffer Pool默认情况下是128MB，有一点偏小，实际生产环境下完全可以对Buffer Pool进行调整
通过命令（SHOW ENGINE INNODB STATUS）查看innodb具体情况
buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数
磁盘随机读的性能是比较差的，所以不可能每次更新数据都进行磁盘随机读，必须是读取一个数据页之后放到Buffer Pool的缓存里去，下次要更新的时候直接更新Buffer Pool里的缓存页。
Change Buffer
写缓存
将数据页从磁盘读入内存中涉及随机 IO 访问，这也是数据库里面成本最高的操作之一
利用写缓存（Change Buffer）可以减少 IO 操作，从而提升数据库性能
通过参数innodb_change_buffer_max_size来动态设置
唯一索引的更新不能使用change buffer
change buffer 因为减少了随机磁盘访问， 所以对更新性能的提升是会很明显的
change buffer只限于用在普通索引的场景下， 而不适用于唯一索引
适合写多读少的业务，因为查询会立即触发merge过程。 这样随机访问IO的次数不会减少， 反而增加了change buffer的维护代价
redo log
本质是保证事务提交之后，修改的数据绝对不会丢失的。
redo log这个机制存在的意义
日志大致格式
因为你MySQL重启之后，把你之前事务更新过做的修改根据redo log在Buffer Pool里重做一遍就可以了，就可以恢复出来当时你事务对缓存页做的修改，然后找时机再把缓存页刷入磁盘文件里去。
为什么不在修改缓存页的同时刷入磁盘？
需要记录的东西
表空间号+数据页号+偏移量+修改几个字节的值+具体的值
结构
日志类型（就是类似MLOG_1BYTE之类的），表空间ID，数据页号，数据页中的偏移量，具体修改的数据
redo log buffer
用来缓冲redo log 写入的
通过设置mysql的innodb_log_buffer_size可以指定这个redo log buffer的大小，默认的值就是16MB，其实已经够大了，毕竟一个redo log block才512字节而已，每一条redo log其实也就几个字节到几十个字节罢了。
子主题
undo log
回滚日志
如果要回滚事务，那么就基于undo log来回滚就可以了，把之前对缓存页做的修改都给回滚了就可以了。
表空间
平时将数据一行一行插入表中，这个表是个逻辑概念，在物理层面，表对应的是表空间这个概念。
表空间的磁盘文件里，有很多的数据页的。
数据区
一组数据区包含256个数据区
一个数据区对应着连续的64个数据页，一个数据区1MB
一个数据页16KB
数据页
磁盘文件里的数据页进行的磁盘随机读写
从表空间的磁盘文件里读取数据页出来，这个过程是磁盘随机读操作
MySQL数据读写机制
一种是对redo log、binlog这种日志进行的磁盘顺序读写
所谓顺序写，就是说在一个磁盘日志文件里，一直在末尾追加日志
磁盘顺序写的性能其实是很高的
一种是对表空间的磁盘文件里的数据页进行的磁盘随机读写
IOPS和响应延迟
IOPS：每秒I/O数的简称，表示一秒中输入输出操作（比如读和写）的次数
可以用IOPS数值来描述一个数据库的IO操作量
拓展
磁盘的顺序读写，随机读写
顺序读写主要时间花费在了传输时间
随机读写需要多次寻道和旋转延迟
页分裂
分裂过程
比如在第一个数据页里有一条数据的主键是10，第二个数据页里居然有一条数据的主键值是8，那此时肯定有问题了。
所以此时就会出现一个过程，叫做页分裂
所以此时就会出现一个过程，叫做页分裂
核心目标就是保证下一个数据页里的主键值都比上一个数据页里的主键值要大
自增主键不会出现，如果你的主键不是自增的，他可能会有一个数据行的挪动过程，保证你下一个数据页的主键值都大于上一个数据页的主键值
索引页
表的实际数据是存放在数据页里的，然后你表的索引其实也是存放在页里的，此时索引放在页里之后，就会有索引页，
假设你有很多很多的数据页，那么此时你就可以有很多的索引页。
假设你有很多很多的数据页，那么此时你就可以有很多的索引页。
索引页里存放的就是下一层的页号和最小索引字段值
数据页/索引页互相之间都是组成双向链表的，而且也都是按照数据大小有序排列的
数据页
页里面的记录都是组成一个单向链表的，而且是按照数据大小有序排列的
下一个数据页的所有主键值大于上一个数据页的所有主键值
回表
MYSQL
常见问题
为什么选择B+树作为索引结构？
索引B+树的叶子节点都可以存哪些东西？
查询在什么时候不走（预期中的）索引
explain是如何解析sql的？
explain出来的各种item的意义
profile的意义以及使用场景
这个工具可以对SQL语句的执行耗时进行非常深入和细致的分析
order by原理
sql如何优化?
binlog,redolog,undolog都是什么，起什么作用？
MySQL索引的原理是什么？
数据库事务的隔离级别？
事务的几大特性
事务的实现原理
MySQL 索引的“创建”原则
最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列
索引列的基数越大，索引效果越好
根据情况创建复合索引，复合索引可以提高查询效率
避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率
对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间
MyISAM 索引与 InnoDB 索引的区别
20个经典面试题
MySQL常见问题总结
MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？
MyISAM 的表锁是怎么实现的？
Innodb 的行锁是怎么实现的
MySQL开发面试题
10道mysql查询语句面试题
MySQL 数据库 CPU 飙升到 500% 的话，怎么处理
在 MySQL 服务器运行缓慢的情况下输入什么命令能缓解服务器压力？
MySQL笔试面试题集合
关于我们的业务， “用读提交就够了”这个结论是怎么得到的？
什么场景， 是必须使用可重复读隔离级别的呢？
你的线上MySQL配置的是什么隔离级别， 为什么会这么配置？
https://github.com/abel-max/Java-Study-Note/blob/master/MySQL%E9%9D%A2%E8%AF%95.md
数据库无法连接故障的定位，Too many connections
线上数据库莫名其妙的性能抖动
原因
第一个，可能buffer pool的缓存页都满了，此时你执行一个SQL查询很多数据，
一下子要把很多缓存页flush到磁盘上去，刷磁盘太慢了，就会导致你的查询语句执行的很慢。
因为你必须等很多缓存页都flush到磁盘了，你才能执行查询从磁盘把你需要的数据页加载到buffer pool的缓存页里来。
一下子要把很多缓存页flush到磁盘上去，刷磁盘太慢了，就会导致你的查询语句执行的很慢。
因为你必须等很多缓存页都flush到磁盘了，你才能执行查询从磁盘把你需要的数据页加载到buffer pool的缓存页里来。
第二个，可能你执行更新语句的时候，redo log在磁盘上的所有文件都写满了，此时需要回到第一个redo log文件覆盖写，覆盖写的时候可能就涉及到第一个redo log文件里有很多redo log日志对应的更新操作改动了缓存页，那些缓存页还没flush到磁盘，此时就必须把那些缓存页flush到磁盘，才能执行后续的更新语句，那你这么一等待，必然会导致更新执行的很慢了。
主要原因
大量缓存页flush到磁盘，就会导致莫名其妙的SQL语句性能抖动了
优化
尽可能减少flush缓存页到磁盘的时间开销到最小
flush缓存页到磁盘采用的随机IO，SSD固态硬盘随机IO性能非常高
合理设置数据库的innodb_io_capacity这个参数
核心
就是把innodb_io_capacity设置为SSD固态硬盘的IOPS，让他刷缓存页尽量快，
同时设置innodb_flush_neighbors为0，让他每次别刷临近缓存页，减少要刷缓存页的数量，
这样就可以把刷缓存页的性能提升到最高。
同时设置innodb_flush_neighbors为0，让他每次别刷临近缓存页，减少要刷缓存页的数量，
这样就可以把刷缓存页的性能提升到最高。
MySQL为什么有时候会选错索引？
0 条评论
下一页
