MySQL
2023-01-09 01:57:02 11 举报
AI智能生成
学习思维导图
作者其他创作
大纲/内容
索引
使用索引的目的
需要在磁盘中快速找到定位查找的记录,所以需要维护一组数据结构来进行快速定位到查询的记录
查找相关算法
有序
二分查找
排序二叉树
红黑树(平衡排序树)
B树
B+树
无序
暴力查找——ONE BY ONE
Hash结构
数组
数组 + 链表
索引的概念
为了高效查询(排好序)的数据结构
常见索引的结构
二叉树
存在单边增长的数据导致查询效率降低
红黑树
通过平衡二叉树的特点解决了单边增长的问题
但是当数据量较大时查询效率还是较低
B树
每个节点页可以存储多个数据,从而减小了树的高度
节点页大小16KB
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'
主键的一般使用bigInt类型占 8byte
节点页的指针大小为 6 btye
叶子节点中数据大小约为 1kb
B+树
数据挂载在叶子节点
非叶子节点只存储索引来提升节点页存放索引个数
叶子节点通过双向链表连接,提高空间访问效率(方便进行范围查找)
根节点可能长期保存在内存中,提高查询效率
Hash表
仅通过一次hash运算就可定位到存储位置(很多时候效率会高于B+树查询效率)
存在Hash冲突
仅支持等值相关的运算,不支持范围查询
索引的分类
聚集索引
索引结构和数据存放在一起的索引
InnoDB引擎采用的就是聚集索引
.frm/.idb
非聚集索引
索引结构和数据分开存放的索引
MyISAM引擎采用的就是非聚集索引
.frm/.MYD/.MYI
稠密索引
叶子节点保存的不只是键值,还保存了位于同一行记录里的其他列的信息
InnDB中的主键索引
稀疏索引
叶子节点仅保存了键位信息以及该行数据的地址,有的稀疏索引只保存了键位信息机器主键
MyISAM的所有的索引类型
InnoDB的非主键索引外的其他类型索引
包括
唯一索引
普通索引
叶子节点存储的是主键的值
为什么非主键索引不保存所有数据
节省存储空间
减小数据一致性维护的成本
查询是通过主键到主键索引的B+树进行数据的查找
字段个数
单值索引
尽可能避免单值索引的创建
联合索引
特点
节点内按索引字段顺序进行排列
满足最左前缀匹配
根据业务场景进行分析创建
面试题(key point)
InnoDB使用B+树做索引的优势?(和B树相比)
降低了树的深度,提高了数据查询效率
B+树维护了叶子节点的双向指针,提交空间访问效率
为什么Innodb必须包含主键,并且推荐主键整型自增?
1、为什么要有主键
通过主键来构建一棵包括所有数据B+树,B+树的叶子节点包括所有的数据
如果没有主键,优先选择唯一的字段组织构建索引;如果不存在这样的字段则默认生成一个默认列(虚拟列—行号)来组织构建索引
2、为什么整型自增
整型
方便比较大小
存储空间使用的较小
自增
方便节点的拓展,避免因为插入已有序结构后的节点分裂和树的节点平衡
为什么满足最左前缀匹配才能使用联合索引?
因为InnoDB的索引是一个排好序的数据结构
联合索引是按照定义顺序进行排的结构,所以要按照最左前缀原则来进行使用
Explain详解
explain关键字的使用
5.7版本后
使用explain关键字即可
使用 show warnings 语句查看优化有执行计划的大概的sql语句
5.7版本前
explain extended
filtered列
row*filtered/100可以估算出将要和explain中前一个表进行连接的行数
explain patitons
partitions列
基于分区表的查询
执行计划表字段
id
select 语据对应的编号
大小与执行顺序
id越大执行的优先级越高
id相同则按照从上到下进行执行
id为NULl最后执行
select_type
select语句的类型
分类
simple
不好含union和子查询的简单语句
primary
复杂查询中的最外层不是from后跟的select语句
subquery
select语句中非from子句中的子查询
derived
跟在from语句后的子查询
from子句中的子查询结果会生成一个临时表(派生表)
union
在union中的第二个以后的所有select语句
table
这一列表示explain的一行正在访问哪个表
当from子句中有子查询时,table列是<derivenN>格式
表示当前查询依赖id=N的查询,于是先执行id=N的查询
当有union时,UNIONRESULT的table列的值为<union1,2>
1和2表示参与union的select行id
type
表示关联类型或访问类型,即如何进行数据的查询
分类
NULL
在优化阶段分解语句,在执行阶段无需使用访问表或索引的时候
比如对索引字段进行一些聚合操作,仅针对索引字段聚合就可以得到结果
const(system)
const
常量级别
语句优化后某些部分可以优化成为常量
一般是针对主键索引和唯一索引的等值比较时
表中只能找到一条记录,读取一次,速度较快
system
const的一种特例
如果数据表中只有一条记录时查询的级别就会是system
eq_ref
使用主键索引或唯一索引进行连接查询时所有部分被连接使用(即查询结果集中只会返回一条符合条件的记录)
所有部分指的是使用到了索引中的所有字段
不会出现在简单的查询中
ref
使用普通索引,或者联合的唯一性的索引部分前缀进行查询
即在进行比较后查询出包含多条数据的结果集
range
通常出现在in()、between、> 、<、>=、<=等范围查询的语句中
index
通过扫描全索引来进行数据查询
eq_ref、ref和range都是通过根节点进行树的查找方式来进行查询
但是index其实是直接从叶子节点中的第一个记录开始进行数据的查找,有点类似与链表的数据查找
一般使用的是二级索引
二级索引的创建只使用到的记录中的部分数据(创建索引的字段和主键),所以二级索引较小,加载到内存中进行查找效率更高
一般是使用二级索引找到主键,在回表到主键构建的主键索引的B+树中进行数据查找
all
全表扫描
扫描聚簇索引中的所有叶子节点
一般这种需要使用查询字段添加索引来优化到index级别
但是当数据量较小时可能使用二级索引的效率不如全表扫描的效率高
二级索引涉及到回表操作
回表可能就涉及到频繁IO操作
system>const>eq_ref>ref>range>index>ALL
最好保证到range级别
最好达到ref以上的级别
possible_key
查询时可能会使用到的索引
但是具体最后是否会使用索引,要看MySQL底层优化器的选择,是否使用了索引以key的内容为主
只是说明当前sql语句的执行时可以使用索引的
key
mysql进行成本计算和优化后实际会使用到的索引
如果查询条件中字段有索引但是执行计划中key是NULL表示不用索引的话
(不建议使用强制或者忽略索引语句)
(不建议使用强制或者忽略索引语句)
使用force index 强制使用索引
使用ignore index 忽略索引
key_len
索引使用的字节数,可以算出联合索引中到底使用那些字段
计算原则
字符串
utf8中字符数字和字母占一个字节,汉字占3个字节
char(n)
n字节
3n字节
varchar(n)
n+2字节
3n+2字节
两个字节来存储字符串长度
数值类型
tinyint
1字节
smallint
2字节
int
4字节
bigint
8字节
时间类型
date
3字节
timestamp
4字节
datetime
8字节
NULL
1字节
如果字段允许为空,需要一个字节的大小来存储是否为NULL
索引最大长度为768字节,如果字符串过长则会进行一个类似最左前缀的匹配来进行检索
ref
使用索引查询时使用的到列字段名或者常量const(等值查询时)
rows
估计要读取并检索的行数
Extra
额外信息
常用的值
Using index
使用索引
表示查询时使用的时索引
既包括根节点查询的过程
也包括叶子节点遍历查询的过程
【覆盖索引】
select中查询的字段在索引中都可以获得,这样的时候我们说查询使用到了覆盖索引
一般时针对于二级索引,不需要通过回表来进行查询相应数据
Using where
使用where语句来处理,而且查询的字段未被索引覆盖
一般type都会是ALL
Using index condition
查询的列不完全被覆盖,where条件是联合索引中的一部分
Using temporary
查询过程中是需要从临时表中进行查找
比如使用distinct进行去重操作
这样需要通过给相对应的字段新增索引来进行优化
Using filesort
在排序中使用外部进行排序,则将数据全部找出来在进行排序
这种情况要考虑到添加索引来优化
索引本身就是一个排好序的结构
Select tables optimized away
使用某些聚集函数来放访问存在索引中的某个字段
索引使用实践
联合索引的使用要满足最左前缀原则
尽可能写sql语句时就按顺序书写条件,避免Mysql的优化阶段的工作量
设计联合索引时按照业务实际来设计
字段
字段的顺序
联合索引中范围条件右侧列的字段不会使用
索引字段上做任何操作导致索引失效,使用全表进行扫描
操作类型
计算
聚合函数
类型转化
优化原则
使用索引字段对应的类型进行匹配
将函数表示的含义通过业务含义来进行规避
尽量使用覆盖索引,避免回表操作
业务需要哪些字段查询哪些字段
减少select * 的使用
!=、not in、not exists有的时候无法使用索引导致全表扫描
但数据量不大时可能范围查询也不会使用索引,因为可能进行全表扫描效率更高
优化范围查询没有使用索引时可以通过缩小查询范围的方式来使其走索引
like使用通配符开头时会导致索引失效
可以使用覆盖索引来进行优化,如果查询的数据字段都在索引树中则可以通过直接遍历字节节点来进行查找
底层执行原理
MySQL的执行原理
客户端(client)
服务层(service)
连接器
功能
和客户端进行连接的建立
获取权限
维持和管理连接
过程
连接成功后会维护一个用户连接的session,session中维护这个用户的相关权限信息
在连接使用的过程中执行语句的鉴权都是通过维护的session进行比较
所以连接创建后root用户修改权限后不会对当前连接造成影响,除非重新创建连接
相关设置查看
当前连接的状态查看
show processlis
用户权限查看
show grants for root@"%";
默认连接时长查看
show global variables like "wait_timeout";
set global wait_timeout=28800; 设置全局服务器关闭非交互连接之前等待活动的秒数
连接类型
短链接
连接时间短
断开后再次查询时会再创建一个连接
长连接
长连接
默认连接时长为8小时
生产开发过程中通常使用的是长连接
长连接通常放在Pool中进行管理,但是长连接会导致Mysql的内存增长较快导致内存使用较快
定期断开长连接
当连接占用内存大后,要断开后,下次使用再重连
Mysql5.7版本以上,再执行一个较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源
这个过程不需要重连和重新做权限验证
即恢复到刚刚创建完的状态
查询相关部分
查询缓存
缓存形式
类似与 key-value的格式来存储
key
对应的sql语句
value
结果集
缓存的使用
5.8版本后取消了service层的缓存
5.7和以前版本
需要在my.cnf配置文件中进行参数配置
query_cache_type
0
关闭缓存
1
开启缓存
2
使用 SQL_CACHE 来进行缓存的记录
set global query_cache_size=0;
set global query_cache_type=0;
set global query_cache_type=0;
查看当前Mysql实例是否开启缓存机制
show global variables like "%query_cache_type%";
运用范围
当数据更新后,缓存就会被清空,所以对于频繁更新数据的表来说,缓存时没有太大的存在意义的
所以,通常不会频繁跟新的表(系统配置表、字典表)是可以开启缓存的
show global variables like "%query_cache_type%";
监控缓存相关的参数
Qcache_free_blocks
表示查询缓存中目前还有多少剩余的blocks
Qcache_free_memory
查询缓存的内存大小,通过这个参数可以很清晰的知道当前系统的查询内存是否够用
Qcache_hits
表示有多少次命中缓存
Qcache_inserts
表示多少次未命中然后插入,意思是新来的SQL请求在缓存中未找到,不得不执行查询处理,执行查询处理后把结果insert到查询缓存中
Qcache_lowmem_prunes
该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值,用户可以适当的调整缓存大小。
Qcache_not_cached
表示因为query_cache_type的设置而没有被缓存的查询数量
Qcache_queries_in_cache
当前缓存中缓存的查询数量
Qcache_total_blocks
当前缓存的block数量
语句查询
词法分析器
作用
校验sql语句是否合法,是否有语法格式错误
步骤
词法分析
语法分析
语义分析
构造执行树
生成执行计划
计划的执行
执行树的作用
提取分析构造出的树中的关键数据
作用
分库分表中的路由键的获取用于路由策略中使用
分布式事务中子事务执行失败后补偿机制的相关数据的提取
执行过程
优化器
作用
可使用多索引时,决定使用哪个索引
关联查询时,决定各表的连接顺序(小表驱动大表)
执行器
过程
权限的查看
使用对应引擎的的执行接口
引擎层(store)
负责数据的存储和提前
使用的是插拔式设计,支撑多种存储引擎
MySQL5.5.5版本默认使用InnoDB引擎
流程分析
binlog归档
作用
记录数据表的变动相关操作
用户进行数据的恢复
binlog日志的开启
进行my.cnf文件的配置
配置参数
log-bin=/usr/local/mysql/data/binlog/mysql-bin
日志的开启和存放位置的设置
server-id=123454
自定义,保证唯一性
注意5.7以及更高版本需要配置本项
binlog-format=ROW
含义:记录的格式
分类
statement
记录当前语句
优劣
占用资源小,效率较高
但是进行主从同步时可能会有数据不一致问题的发生
主从库可能使用不同的索引导致数据更新不一致
row
记录当前语句的执行结果
优劣
使用的资源较多
但是可以保证主从库的数据同步安全,数据一致
mixed
statement + row混合模式
当使用statement时不会出现数据不同步问题时使用statement
反之使用 row
binlog相关命令
show variables like '%log_bin%'; 查看bin-log是否开启
flush logs; 会多一个最新的bin-log日志
show master status; 查看最后一个bin-log日志的相关信息
reset master; 清空所有的bin-log日志
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 ;查看binlog内容
通过binlog进行数据恢复
恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)
恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731" /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)
恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00" --start-date= "2019-03-02 11:55:00"|mysql -uroot -p test(数据库)
索引优化
查询的一些特例解析
联合索引第一个字段使用范围查找不会走索引
可能使用索引的范围找到的结果集较大,还涉及到回表操作导致了效率的降低
强制使用索引
force index
一般不建议使用
但是可以通过使用覆盖索引来进行优化
in 和 or 在不同数据量下执行计划不同
数据量较大
使用索引
因为在大数据的基础上做全表的操作来说,将语句拆分成等值的索引操作在回表的效率可能回提升
数据量较小
使用索引时的回表效率较低导致不如全表的效率
LIKE KK% 一般会使用索引
使用到了索引下推(Index Condition PushDown)
原理
当联合索引中查询字段有使用后模糊like查询时,会使用like中确定部分进行匹配,并且也会使用后续的字段进行查找,找到所有满足条件的id,在进行回表操作
使用like匹配字段以及其顺序后的字段进行筛选,可以一定程度上的减少查询到的id个数,从而减少回表次数
MySQL5.6以前不会进行索引下推,找到所有使用到Like条件前的所有结果id,回表找找到所有的记录,再进行like字段后的字段条件匹配
索引下推会减少回表次数,对于innodb引擎的表索引下推只能用于二级索引
【总结】具体是否使用索引还是全表要注意几点
数据量
回表操作的次数
查询字段是否可以被索引进行覆盖
是否满足索引树构造的结构
trace工具的使用
使用
set session optimizer_trace="enabled=on",end_markers_in_json=on;
开启trace工具
执行sql语句
SELECT * FROM information_schema.OPTIMIZER_TRACE;
查询trace字段
set session optimizer_trace="enabled=off";
关闭trace
关键内容
join_preparation
第一阶段:SQL准备阶段,格式化sql
join_optimization
--第二阶段:SQL优化阶段
具体
condition_processing
条件处理
table_dependencies
表依赖详情
rows_estimation
预估表的访问成本
具体
range_analysis
table_scan
全表扫描情况
全表扫描情况
rows
扫描行数
cost
查询成本
potential_range_indexes
可能使用的索引
可能使用的索引
analyzing_range_alternatives
分析各个索引使用成本
分析各个索引使用成本
ranges
索引范围
rowid_ordered
使用该索引获取的记录是否按照主键排序
index_only
是否使用覆盖索引
rows
索引扫描行数
cost
索引使用成本
chosen
是否使用索引
considered_execution_plans
最终执行计划
具体
table
数据表
considered_access_paths
最终选择路径
rows_to_scan
扫描行数
access_type
访问类型
resulting_rows
结果行数
cost
成本
chosen
确定选择
use_tmp_table
是否使用临时表
join_execution
第三阶段:SQL执行阶段
常见深入优化
Order By 和 Group by的优化
排序的方式
using index(效率较高)
使用MySQL扫描的索引本身
using filesort(效率较低)
使用文件排序,即将数据load到内存中进行文件排序
文件排序
分类
单路排序
是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;
是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;
排序后的结果就我我们想要的结果集
trace工具的标识为【< sort_key, additional_fields >】/【< sort_key, packed_additional_fields >】
多路排序
(回表排序)
(回表排序)
是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID,然后在 sort buffer 中进行排序,排序完后需要再次取回其它需要的字段
sort buffer排序后的id结果集需要在进行回表才能得到我们想要的结果集
trace工具的标识为【< sort_key, rowid >】
判断依据
MySQL 通过比较系统变量 max_length_for_sort_data进行判断
不建议进行调整
默认大小为:1024字节
大于
多路排序
小于等于
单例排序
当需要加载到sort buffer记录字段较多时,则使用多路排序,只将唯一标识id和排序字段加入到sort buffer中进行比较
不会使用索引的情况
非wher条件中等值排序字段不能跳跃
非where条件中等值排序字段不能颠倒顺序
where条件使用 in
不使用覆盖索引的where条件使用了范围查询
优化方式
尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则
order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列
能用覆盖索引尽量用覆盖索引
分组group是基于排序的,实质就是先排序后分组
遵照索引创建顺序的最左前缀法则
group by的优化如果不需要排序的可以加上order by null禁止排序
避免使用having,将条件在where条件中进行指定
分页查询优化
自增连续主键排序的分页
如果sql语句中没有指定排序字段,则默认使用主键进行排序
使用自增连续主键排序的使用可以将limit start rownumbers 的格式优化成捅过主键范围后的limt rownumbers
select * from table limit start rownumber
优化到
select * from table where id > start limit rownumber
原写法全表进行查找
优化后使用主键索引
如果数据记录有删除操作则不能使用该方法进行优化,会导致数据集不正确
非主键排序分页
优化原理
先使用索引进行排序后找到对应主键,在捅过主键在回表来进行结果集的查询
案例
employee表
字段
id
name
age
position
索引
id
name_age_posisiton
语句
select * from employee order by name limit 1000,5
执行计划使用ALL进行全表扫描
优化思想
子查询使用索引的排序特性查询到排序后对应分页的数据id
通过id关联表来进行表关联
通小表驱动大表来在全表中使用id主键索引
select * from employee e inner join (select id form employee order by name limit 1000,5 ) ed on e.id=ed.id
子查询使用二级覆盖索引查询到id
nam_age_posistion
两个表通过id字段进行关联
通过id使用主键索引查询数据
PRIMARY
Join关联字段优化
连接算法
【大表10000条数据】
【小表100条数据】
【大表10000条数据】
【小表100条数据】
嵌套循环连接-NLJ
(Nested-Loop Join)
(Nested-Loop Join)
从驱动表中一次取出一行数据,找到这行数据的关联字段,使用字段在被驱动表中取出满足条件的数据,然后取出两张表的结果集合
使用NLJ算法,是执行计划的Extra中不会出现 Using join buffer
一般两张表中的关联字段都为索引字段时,会使用NLJ算法
连接表分类
inner join
优化器会将需要进行关联的小表作为小表(驱动表)
另一张表就会成为大表(被驱动表)
所以inner join 中排在前面的表不一定为驱动表
left join
左表为驱动表
右表为被驱动表
优化器不会进行自己优化,所以使用是要注意表的大小和位置
right join
左表为被驱动表
右表为驱动表
优化器不会进行自己优化,所以使用是要注意表的大小和位置
大概过程
1、从驱动表(经过where条件筛选)数据中取出一行数据
2、从第一步中的数据,取出关联字段,到被驱动表中进行查找
3、取出被驱动表中找到对应的数据,取出的数据和驱动表中的数据进行合并,返回给客户端
4、重复前三步的操作
算法效率
扫描行数
10000 + 100
小表逐行扫描
大表通过索引进行扫描
判断次数
10000
通过索引在大表中进行判断匹配
基于块的循环嵌套连接-BNL
(Block Nested-Loop Join)
(Block Nested-Loop Join)
把驱动表的数据读取到join_buffer中,让后扫描被驱动表,把驱动表中的每行读取出来和join_buffer数据进行标胶
使用BLJ算法的执行计划中的Extra中会有 Using join buffer(Block Nested Loop)
通常情况是关联的字段不是索引字段会使用过这种算法进行关联
大概过程
1、驱动表将数据放在join_bufferz中(以buffer大小进行分批)
join_buffer的默认大小时256K,不建议自己进行修改
2、把被驱动表的每行数据取出,和join_buffer中是数据进行比较
3、返回满足join条件的数据
4、驱动表较大时分批重复上述三个步骤
算法效率
扫描行数
100 + n*10000
驱动表行数 + 分批每次都会扫描一次被驱动表
判断次数
100 * 10000
表中每行都要进行两两比较
为什么使用BLJ时不使用NLJ算法
如果关联字段没有索引,则都需要扫描全表进行比较,所以扫描行数的就是 100 * 10000
关联查询的优化
关联字段添加索引,让大表走索引
小表驱动大表
【经过条件过滤后的数据量】
【经过条件过滤后的数据量】
小表作为驱动表
explain中先执行的表
大表作为被驱动表
explain中后执行的表
explain 中小表先执行,查出对应连接的数据,再通过索引在大表中查询
如果执行计划有问题,可以自己指定驱动表
straight_join
使用左表驱动右表
只针对inner join有效
尽可能减少关联
in 和 exist 的优化
优化原则:使用小表来驱动大表
in
select * from A where id in (select id from B)
#等价于:
for(select id from B){
select * from A where A.id = B.id
}
for(select id from B){
select * from A where A.id = B.id
}
B表小于A表时,in优于exist
exsist
select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
for(select * from A){
select * from B where B.id = A.id
}
A表和B表关联字段要添加索引
当A表的数据集小于B表的数据集时,exists优于in
使用方法
EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
EXISTS子查询往往也可以用JOIN来代替,何种最优需要具体问题具体分析
count查询优化
日常使用方式
count(1)
找到对应的数据后直接对结果做累加
count(id)
5.7版本做了优化,如果count的字段有索引,就是使用的count(id)也会优化成为使用二级索引来进行计算
count(字段)
该方式统计时不会统计null的数据行
通过索引找到数据后会加载到内存中,在进行计数操作,所以效率低于count(1)
count(*)
MySQL底层做了优化按行进行累加,不会取值,效率较高
效率
字段有索引
count(*)≈count(1)>count(字段)>count(主键 id)
count(字段)会走二级索引,所以效率比使用主键效率高
字段没有索引
count(*)≈count(1)>count(主键 id)>count(字段)
count(字段)无法走索引,所以效率就低于使用主键索引的count(id)
常见优化方法
查询mysql自己维护的数据
myisam会维护数据,无需计算
innoDB由于MVCC机制,count需要实时计算
show table status 获取大致的数据行数
将数据存储到redis中
使用redis中的incr或decr命令计数
但是这种方式不准,很难保证表操作和redis操作事务一致性
双写一致维护成本高
使用数据额外表进行计数,使用数据库事务进行维护
MySQL底层对查询SQL进行的优化
join中的驱动表和被驱动表的选择
联合索引字段顺序的优化
联合索引查询时的索引下推
二级索引查询优化——自适应哈希索引
二级索引回表前的排序MRR
索引设计的原则
代码先行,索引后上
索引的设计要贴合业务的需求来进行设计
避免盲目的创建过多冗余的索引,从而提升索引维护的成本
联合索引尽量的覆盖条件
尽量减少单值索引的创建
即要考虑到where条件中的字段还用注意到order group 的字段
当索引字段where和order/group冲突是优先考虑where
避免在小基数字段创建索引
字段的数据的离散度较低会大大降低索引的效率
时长针对慢sql做索引的优化
业务系统使用初期和使用一段时间后的数据量是有较大改变的,索引的执行可能也会发生变化
所以要时长监控慢sql,针对慢sql来进行索引的优化
MySQL慢查询
概念:用来记录超过响应阈值的SQL语句
条件:通过 long_query_time 值来判断是否会被记录
默认long_query_time是10秒
参数配置
slow_query_log
1
开启
0
关闭
log-slow-queries
旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。
slow_query_log_file
新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。
long_query_time
慢查询阈值,当查询时间多于设定的阈值时,记录日志。(大于时记录,不包括等于)
log_queries_not_using_indexes
未使用索引的查询也被记录到慢查询日志中(可选项)。
log_output
日志存储方式
方式
'FILE'表示将日志存入文件
默认
推荐优先使用
'TABLE'表示将日志存入数据库。
写入到mysql.slow_log表中
log_out='FILE,TABLE'
混合使用
配置
show variables like '%slow_query_log%';
查看慢日志相关信息
slow_query_log
slow_query_log_file
set global slow_query_log=1
设置当前数据库有效,重启后失效
永久失效需要在my.cnf中进行配置
show variables like 'long_query_time%';
查看响应阈值
set global long_query_time=4;
设置阈值,但是重新开启新的会话才会生效
show variables like 'log_queries_not_using_indexes';
查看是否开启了非索引查询的记录
set global log_queries_not_using_indexes=1;
使用full index scan的SQL也会被记录到慢查询日志。
未使用索引的查询也被记录到慢查询日志中
show global status like '%Slow_queries%';
查询慢sql的条数
长字符串使用前缀进行索引的创建
长字符串做索引会耗费大部分磁盘空间
使用前缀可以减少磁盘空间的占用
但是只使用前缀会导致order 和 group 没法使用索引
阿里MySQL规范
设计规约
单行数据超过500万行或者容量大于2G时建议进行分库分表
业务的唯一字段,即使时组合字段也创建唯一索引
禁止超过3张表以上的关联查询,多表设计和优化索引起来比较复杂
这种多表联合建议在Java层级进行业务的拆分,使用Java层级的业务拆分如果出现了并发瓶颈问题可以通过扩容和并发来解决,但是MySQL处理难度较大
varchar字段创建索引要指定长度,根据业务进行长度设置
要避免左模糊和全模糊查询
避免使用in关键字,使用的话in集合大小在1000以内
字段类型选择
原则
确定合适的大类型
数字、字符串、时间、二进制
具体类型
有无符号、取值范围、定变长
类型注意事项
数值类型
确定有无符号可以增加可表示范围
确定取值范围,尽可能选择就满足条件的小类型
括号后的参数只显示长度
配合ZEROFILL使用
使用0进行填充
日期和时间
DATETIME
8个字节
范围大
TIMESTAMP
4个字节
范围到2038年
字符串
字符串的长度相差较大用VARCHAR;
字符串短,且所有值都接近一个长度用CHAR
尽量少用BLOB和TEXT,如果实在要用可以考虑将BLOB和TEXT字段单独存一张表,用id关联
事务隔离级别和锁机制
事务隔离级别
事务
概念
由一组SQL语句组成的逻辑处理单元
特性【ACID】
原子性
Atomicity
Atomicity
操作层面来说,一组操作同时成功或者失败
一致性
Consistent
Consistent
数据层面来说,一组SQL影响的数据的状态要保持一致,以保持数据的完整性
隔离性
Isolation
Isolation
数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。
一个事务的操作不影响其他事务中的数据
持久性
Durable
Durable
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持
并发处理事务带来的问题
脏写(更新丢失)
后面执行的更新操作导致了覆盖了其他事务进行的更新操作
解决
不在业务层级完成与原数据有关的更新操作,将这些操作放在数据库的层级来做
使用乐观锁的思想,在更新前进行版本号的比对
脏读
事务A读取到了事务B已经修改但是没有提交的数据
如果A使用了没有提交的数据做了操作进行了数据更新
B事务发生了回滚,那么其实A使用的数据就是一个脏数据,使用脏数据做的运算也就没有意义了
不可重复读
事务A内部的相同查询语句在不同时刻读取的结果不一致,不符合隔离性
针对与update操作
幻读
事务A读取到了事务B提交的新增数据,不符合隔离性
针对于insert和delete操作
事务的隔离级别
读未提交、读已提交、可重复度、可串行化
show variables like 'tx_isolation';
查看使用隔离级别
设置隔离级别
set tx_isolation='read-uncommitted';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable';
MySQL默认的隔离级别是可重复度(REPEATABLE-READ)
为了解决并发的事务问题才出现了事务的隔离级别这么一说,不同的事务隔离级别可以解决相应的问题
事务优化
将查询等数据准备操作放在事务之外
事务中避免远程调用,远程调用要设置超时时间,防止造成大事务的情况
事务中避免一次性处理过多的数据量,将数据进行拆分多事务进行
更新等涉及加锁的操作尽可能放在事务靠后的位置
能异步处理的尽量异步处理,异步处理也要做超时日志
通过应用侧(业务代码)保证数据一致性,可非事务执行
在业务代码中的try...catach进行业务补充
业务逻辑复杂不推荐使用
锁机制
概念
计算机协调多个进程或线程并发访问同一资源的机制
数据库的锁机制就可以保证不同的隔离级别,换句话说事务的隔离级别就是通过锁机制来实现的
锁的分类
性能
乐观锁
使用版本控制来实现
比如在数据表中增加版本字段实现
MVCC
悲观锁
通过排他性来保证
粒度
表锁
每次操作锁住整张表
开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
一般用在整表数据迁移的场景
操作
lock table 表名称 read(write),表名称2 read(write);
手动增加表锁
show open tables;
查看表上加过的锁
unlock tables;
删除表锁
存在于MyISAM、InnoDB引擎中;而且MyISAM只有这种锁,不支持事务
行锁
每次操作锁住一行数据
开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高
行锁是加在索引之上的,如果对非索引行做更新时,行锁会升级为表锁
页锁
每次锁定相邻的一组记录
锁定粒度界于表锁和行锁之间,开销和加锁时间界于表锁和行锁之间,并发度一般
应用在BDB 存储引擎中
BDB引擎
操作类型
(都属于悲观锁)
(都属于悲观锁)
读锁(共享锁,S锁)
针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排他锁,X锁)
当前写操作没有完成前,它会阻断其他写锁和读锁
数据修改操作都会加写锁
意向锁(I锁,针对表锁)
主要是为了提高加表锁的效率
当有事务给表的数据行加了共享锁或排他锁,同时会给表设置一个标识,代表已经有行锁了
其他事务要想对表加表锁时,就不必逐行判断有没有行锁可能跟表锁冲突了,直接读这个标识就可以确定自己该不该加表锁
分类
意向共享锁,IS锁
对整个表加共享锁之前,需要先获取到意向共享锁
意向排他锁,IX锁
对整个表加排他锁之前,需要先获取到意向排他锁
引擎中的锁
MyISAM
执行查询语句SELECT前,会自动给涉及的所有表加读锁
在执行update、insert、delete操作会自动给涉及的表加写锁
InnoDB
在执行查询语句SELECT时(非串行隔离级别),不会加锁
在执行update、insert、delete操作会加行锁。
事务隔离级别的实现
(针对InnoDB引擎)
(针对InnoDB引擎)
读已提交和可重复读
通过MVCC机制保证的,MVCC实际的设计思路就是一种乐观锁的设计
可以有效的解决脏读、不可重复读
通过间隙锁可以部分解决幻读的问题
间隙锁
Gap Lock
Gap Lock
根据id字段当前已有的值做出相应等多个范围
查询的id落在对应的区间,则对应的区间范围的id行都会都会进行加锁,不能进行写操作
间隙锁+区间右行的行锁成为临键锁
串行化
通过读写锁以及偏向锁来(即互斥锁)实现了脏读、不可重复读和幻读的问题
这些悲观锁就导致了并发效率较低,所以一般不会使用这种隔离级别
优化建议
尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
无索引行锁会升级为表锁
锁主要是加在索引上,如果对非索引字段更新,行锁可能会变表锁
合理设计索引,尽量缩小锁的范围
尽量控制事务大小
减少锁定资源量和时间长度
涉及事务加锁的sql尽量放在事务最后执行
涉及事务相关的业务SQL尽可能使用数据库中数据进行运算
读已提交虽然读出单数据是保持一致的
但是做写操作时会使用数据库中最新的数据进行运算
尽可能低级别事务隔离
事务分析和运维
行锁的分析
show status like 'innodb_row_lock%';
结果
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
INFORMATION_SCHEMA系统库锁相关数据
事务查看
select * from INFORMATION_SCHEMA.INNODB_TRX;
等待锁查看
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
锁查看
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
锁等待详细信息
show engine innodb status\G;
释放锁
kill trx_mysql_thread_id
运维
查看锁等待详细信息中发现了死锁deadlock相关的信息
在INFORMATION_SCHEMA.INNODB_TRX表找到事务对应的线程ID trx_mysql_thread_id
通过kill 线程id的方式解决死锁
一般简单的死锁出现MySQL自己就会处理,将相互依赖的事务中的锁进行提交,解决死锁问题
MVCC和BufferPool缓存机制
MVCC
Multi-version Concurrency Control:多版本并发控制机制
其中相关概念
undo日志
为了事务操作失败而需要进行回滚而进行数据记录成为undo日志
undo日志版本链
指一行数据被多个事务依次修改过后,在每个事务修改完后,Mysql会保留修改前的数据undo回滚日志
通过两个隐藏字trx_id和roll_pointer进行undo日志串联起来形成版本链
trx_id
更新所处的事务id
roll_pointer
指向上一个版本的undo日志
结构
read-view(查询)一致性视图
生成时间
可重复读
事务执行了第一条查询语句就会生成
读已提交
每次进行查询之前
组成
当前未提交事务id的数组
数组中最小事务id
(即未提交事务的最小id)
(即未提交事务的最小id)
min_trx_id
当前最大的事务id
max_trx_id
隔离级别区别
可重复读
执行任何sql生成的一致性视图,在该事务结束之前不会改变
读已提交
执行完成一条sql前会生成最新的read-view视图
事务的起点
(生成事务id)
(生成事务id)
一个事务的起点不是从begin/start transaction开始的,当事务中执行第一个写语句,事务才真正的开始,才会像mysql来申请事务id
mysql内部是严格按照事务的启动顺序来分配事务id的
实现过程
开启事务
第一次执行查询sql时生成本次是事务对应的read-view视图
具体操作
读操作
根据undo日志版本链和当前事务的read-view视图进行数据查找
比较原则
从日志版本链的开头进行比较匹配
当前undo日志维护的trx_id
trx_id<min_trx_id
说明该undo日志对应的版本已提交
数据可见,直接使用数据即可
trx_id>max_trx_id
该版本对应的trx_id是未来的,当前一定没有进行事务提交
数据不可见,通过roll_pointer找到下一个undo日志
min_trx_id <= trx_id <= max_trx_id
当前undo日志维护的trx_id没在未提交事务的事务id集合中则读取相应记录值
当前undo日志维护的trx_id在未提交事务的事务id集合中则通过roll_pointer找到上个版本的undo日志进行重复比较
写操作
对undo日志进行追加
事务提交
总结
MVCC机制的实现就是通过read-view机制与undo版本链比对机制,使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。
所有事务公用一个undo日志,但是每个事务根据不同的隔离级别会有自己独有的read-view视图
BufferPool机制
存储流程
重点梳理
Buffer Pool中是以Page为单位进行数据读取
三个文件
undo日志文件
InnoDB引擎特有的
用户事务回滚恢复Buffer Pool中的数据
InnoDB引擎特有的
是进行顺序操作的
开辟一块连续的空间
操作效率比随机操作效率高得多
redo日志文件
InnoDB引擎特有的
用于恢复Buffer Pool中的丢失数据的
同样也是顺操作的
binlog日志文件
由server层所控制
用户恢复数据库的数据使用
binlog日志文件存储磁盘完成后会给rido日志设置一个标识,标识redo和binlog日志数据的一致
同步标识后进行事务提交
Buffer Pool中更改的数据是由一个专门进行刷盘操作的IO线程执行的,所以具体写回磁盘的时间是不定的
Buffer Pool中的数据不是一成不变的,根据LRU算法进行数据的淘汰
InnoDB引擎底层执行原理
InnoDB引擎底层存储和缓存原理
InnoDB引擎的三大特性
AdaptiveHashIndex
自适应哈希索引
为了提高索引更好的查询数据的效率,而进行对二级索引的优化
查看自适应索引是否打开
show variables like '%ap%hash_index';
说明
经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据),自适应哈希索引通过缓冲池的B+树构造而来
特点
无序,没有树高
降低对二级索引树的频繁访问
缺点
占用InnoDB Buffer Pool 的空间
自适应hash索引只适合搜索等值的查询
极端情况下,自适应hash索引才有比较大的意义,可以降低逻辑读
查询原理
Buffer Pool
缓存池
下面详细说明
Doublewrite Buffer
双写缓冲区
下面详细说明
InnoDB的内存和磁盘结构
内存
Buffer Pool
Log Buffer
AdaptiveHashIndex
磁盘
System Tablespace
InnoBD Data Dictionary
Doublewrite Buffer
Change Buffer
Undo Logs
Undo Tablespaces
Redo Log
File-per-Table Tablespaces
General Tablespaces
Temporary Tablesapces
InnoDB的体系结构
【以页位单位的I/O操作】
【以页位单位的I/O操作】
表空间
数据存放和空间的管理
记录
每条数据记录
页
B+树中数据存储的基本单元
大小16KB
页号是又4个字节来记录
区
对于16KB的页,连续的64个页组成了一个区
区的默认占用1MB
设置区的目的
更方便的管理页
以区为单位进行磁盘空间的申请
尽可能将随机IO转化为顺序IO
一个区中的页物理地址是连续的
组
256个区组成了一个组
各组最开始的2个页面的类型是固定的,用来登记本组256个区的属性
段
为了更高效的进行分为查询,按是否为叶子节点分为了两个段
段只是一个逻辑上的概念,不一定是一段连续的物理空间
页节点段
存放B+树的叶子节点
非页节点段
存放B+树的非叶子节点
表空间
表空间是一个抽象的概念
每个表空间对应了一个或多个文件
相关关系图
独立表空间
File-Per_Table Tablespace
存放业务表,也就是我们自己创建的数据表
对应的就是文件系统中的.bd文件
系统表空间
整个MySQL进行只有一个系统表空间
双写缓存/双写机制
双写缓冲区
Doublewrite Buffer
系统表空间中的特定的两个区(128个页)
2M大小
这个双写缓冲区不仅在内存中,也存在于MySQL的系统表空间,属于磁盘文件的一部分
为什么要使用双写缓存
提高innodb把缓存的数据写到硬盘这个过程的安全性
为了解决部分页写入问题
在极端的情况下,由于操作系统分批进行磁盘记录的写入就可能出现"断页",导致数据混乱
innodb的事务日志不需要包含所有数据的前后映像,而是二进制变化量
节省大量的IO
涉及到双写缓冲区进行脏页刷盘的过程
使用mymcop将脏数据赋值到内存中一个2M的空间
通过两次将数据刷新到系统表空间中
马上调用fsync函数同步到磁盘上
fsync函数:将文件数据同步到硬盘
弊端
由于需要进行双写,既要写入Doublewrite buffer,又要对表的.bd文件写入,所以性能会比直接进行独立表空间写入降低5%~10%
但是在主从集群模式下,我们可以将从slave的双写缓存机制关掉,因为即使出现了部分写入的问题,也可以通过中继日志中恢复数据
redo日志和双写缓冲区的比较
redo日志
记录的是页的物理操作,即记录的是具体那条数据发生了什么变化
不是整页数据的记录
doublewrite buffer
进行的是整页数据的记录
部分写入问题问什么不用redo日志恢复?
redo日志只记录具体数据的变化,并没记录变化数据所在页的所有数据
redo日志需要配合磁盘中的原始语句一起才能进行异常页的恢复
但是使用doublewrite buffer 只需要将改变的页刷入磁盘即可
InnoDB数据字典
用户保存数据库的基本信息的数据
基本系统表
SYS_TABLES
整个InnoDB存储引擎中所有的表的信息
SYS_COLUMNS
整个InnoDB存储引擎中所有的列的信息
SYS_INDEXES
整个InnoDB存储引擎中所有的索引的信息
SYS_FIELDS
整个InnoDB存储引擎中所有的索引对应的列的信息
以B+树的形式保存在系统表空间的某些页面中
Max Row ID字段
我们不显式的为表定义主键,而且表中也没有UNIQUE索引,那么InnoDB存储引擎会默认为我们生成一个名为row_id的列作为主键
系统是不可以进行修改的,只给我么提供的读数据的功能
通用表空间
允许多个表结构存储在一个表空间中
undo表空间
默认是包含在系统表空间的
但是可以通过参数设置的方式独立出来
Buffer Pool
为了提高MySQL的查询效率在内存中申请的一篇连续的空间
内存大小
how variables like 'innodb_buffer_pool_size';
默认大小
128M
最小空间大小
5M
通常的设置方法
如果没有专门的DBA做实时监测,设置为机器内存的60%就较为合适
如果有专门的检测人员,可以75%,根据业务和实时数据来进行大小的调整
通常根据缓存的命中率来进行调整
缓存命中的查看
show engine innodb status\G
具体规则
命中率没达到98%以上,都说明buffer不够,可以扩大
如果给命中都能达到98%~100%了,而且还有大量的free page那说明够用了
InnoDB 为缓冲区和控制结构保留了额外的内存
所以最终分配的空间比Buffer Pool分配的空间会大10%
Buffer Pool的组成
缓存页
数据的存放
和数据页的大小一致16KB
控制块
记录缓存页的相关信息
包括:页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息
控制块和缓存页一一对应
结构
Buffer Pool的缓存页管理
free链表
空闲的缓存页对应的控制块作为一个节点组成的链表
处于free链表中控制块对应的缓存页就是空闲的可以进行数据缓存的缓存页
通过控制块上的缓存页地址就可以找到对应的缓存页,之后将使用的缓存页的控制块移出该链表就可表示控制块对应的缓存页已被使用
结构
缓存页hash表
用于快速判断某个数据页是否在Buffer Pool数据结构
使用Hash表存储
key
表空间号+页号
value
数据页对应的缓存页地址
过程
访问某个页的数据时,先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页
有的话直接使用缓存页中的数据
没有的话在free链表中申请一个缓存页,将磁盘中对应的数据加载到缓存也中并维护到缓存页的hash表中
flush链表
有过数据更新操作的页(脏页)对应的控制块组成的链表
结构和free链表差不多
LRU链表
LRU算法
最近最少使用算法
长期不被使用的数据,在未来被用到的几率也不大,所以当新的数据进来时我们可以优先把这些数据替换掉
问题:
查询语句中可能导致进行数据表的全表扫描
这样就导致了表的所有页都会被加载到Buffer Poll
而且数据的每条加载会导致页会频繁的被加载到,导致数据页成为热点页
这样存入缓存其实会导致使用Buffer Pool的查询效率不高
预读机制
方案
线性预读
顺序访问了某个区(extent)的页面超过这个系统变量的值
就会触发一次异步读取下一个区中全部的页面到Buffer Pool的请求
随机预读
Buffer Pool中已经缓存了某个区的13个连续的页面
都会触发一次异步读取本区中所有其他的页面到Buffer Pool的请求
问题:
这样的预加载就会导致一些不用的页加载到缓存中
解决
将LRU链表进行分区,分为yong和old区
young区域
一部分存储使用频率非常高的缓存页
存放热数据
old区域
使用频率不是很高的缓存页
存放冷数据
yong和old的默认比利是old区域在LRU链表中所占的比例是37%
即yong:old=5:3
存放规则
初次加载到Buffer Pool中的某个缓存页时,该缓存页对应的控制块会被放到old区域的头部
解决预读的问题
某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间,如果后续的访问时间与第一次访问的时间在某个时间间隔内,那么该页面就不会被从old区域移动到young区域的头部
SHOW VARIABLES LIKE 'innodb_old_blocks_time';
这样就解决了全表扫描导致数据页成为伪热点数据的情况
访问的缓存页位于young区域的1/4的后边,才会被移动到LRU链表头部
降低调整LRU链表的频率,从而提升性能
链表结构
刷新脏页到磁盘
MySQL会维护一个专门进行脏数据磁盘刷新的磁盘的线程
刷新方式
从LRU链表的冷数据中刷新一部分页面到磁盘
从flush链表中刷新一部分页面到磁盘
系统特别繁忙时,也可能出现用户线程批量的从flush链表中刷新脏页的情况
多个Buffer Pool实例
(Buffer Pool的隔离)
(Buffer Pool的隔离)
可以减少并发情况下的加锁导致的效率低的情况
每个Buffer Pool都是独立的,在多线程并发访问时并不会相互影响,从而提高并发处理能力
innodb_buffer_pool_instances能设置的最大值是64
每个实例的innodb_buffer_pool_size(默认128M)的值小于1G的时候设置多个实例是无效的
Buffer Pool的扩容
MySQL 5.7.5之前,是需要继续申请一块新的连续的内存空间,然后进行Buffer Pool的转移
运行过程中不允许进行调整
MySQL在5.7.5以及之后的版本中支持了在服务器运行过程中调整Buffer Pool大小
每次回申请一个chunk
一个chunk的默认大小是128M
Buffer Pool的状态查看
SHOW ENGINE INNODB STATUS\G
重要参数
Total memory allocated:代表Buffer Pool向操作系统申请的连续内存空间大小
Dictionary memory allocated:为数据字典信息分配的内存空间大小
Buffer pool size:代表该Buffer Pool可以容纳多少缓存页,注意,单位是页!
Free buffers:代表当前Buffer Pool还有多少空闲缓存页
Database pages:代表LRU链表中的页的数量,包含young和old两个区域的节点数量
Old database pages:代表LRU链表old区域的节点数量。
Modified db pages:代表脏页数量,也就是flush链表中节点的数量。
BufferPool缓存淘汰机制是怎样的
主要使用了LRU算法
1.3/8的list信息是作为old list,这些信息是被驱逐的对象。
2.list的中点就是我们所谓的old list头部和new list尾部的连接点,相当于一个界限
3.新数据的读入首先会插入到old list的头部,
4.如果是old list的数据被访问到了,这个页信息就会变成new list,变成young page,就会将数据页信息移动到new sublist的头部。但是要注意innodb_old_blocks_time参数
5.在数据库的buffer pool里面,不管是new sublist还是old sublist的数据如果不会被访问到,最后都会被移动到list的尾部作为牺牲者
Insert/Change Buffer
主要是用于对二级索引的写入优化
Buffer Pool是针对于聚集索引的,但是数据变化时也会影响二级索引
MySQL对二级索引处理的优化就使用到了Change Buffer
InnoDB引擎底层事务的原理
事务四大特性
目的
一致性
手段
原子性
undo日志
持久性
redo日志
隔离性
MVCC+读写锁
MySQL的事务实现方式
WAL(Write-ahead logging)
日志预写
先将修改的数据写到日志中,然后再被到系统中
包括redo和undo日志
redo日志
重做日志,用来恢复断电情况下恢复未做完变更的数据
undo日志
撤销日志,用户恢复原子操作中以变更部分的数据
其他事务实现方式
Commit Logging 提交日志
日志全部安全落盘后,才会根据日志信息进行数据修改
和WAL区别
WAL在事务未提交前,可以提前写入变动数据;提交日志必须日志全部落盘成功(事务提交)在写入变动数据
WAL中有undo日志
Ocean Base数据库使用的就是该方法实现的事务
Shadow Paging 影子分页
使用的时写实复制
修改数据复制,读取从原数据进行,修改在复制区进行,修改完成在做原数据区域的覆盖
事务持久性的保证——Redo日志
redo日志作用
InnoDB的操作都是以数据页为单位操作的,如果页中的一个数据变化就针对一个页做记录就会有一下问题
以页为单位会浪费很多资源和空间
一个事务会有很多语句执行,可能涉及多个数据页的操作,就导致会做很多次随机 I/O 导致效率的降低
所以我们使用redo日志来进行记录
只需要记录修改的内容即可,就避免了整页数据的记录
如果系统崩溃只需要执行redo日志中对应的改动就可以恢复数据了
只要牢记:redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来,在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来。
redo日志的优势
redo日志占用的空间较小,记录表空间ID、页号、偏移量和改动数据即可
redo日志是一片连续的空间,进行顺序写入即可,即使用的时顺序IO
日志格式
通用格式
type
类型
redo日志大概有53种
space ID
表空间ID
page Number
页号
data
数据的改动
普通redo日志格式
offset
偏移量
在页上的偏移量和页号配合使用就就可以找到表中对应的数据
系统表空间中维护的最大行号(用于维护隐藏列row_id)Max Row ID就是通过这个格式的redo日志进行记录的
每当这个变量的值为256的倍数时,就会将该变量的值刷新到系统表空间的页号为7的页面中一个称之为Max Row ID的属性处
使用的类型是:MLOG_8BYTE
事务的id的维护也类似于row_id一样,内存会维护一个全局变量
每当需要为某个事务分配一个事务id时,就会把该变量的值当作事务id分配给该事务,并且把该变量自增1
每当这个变量的值为256的倍数时,就会将该变量的值刷新到系统表空间的页号为5的页面中一个称之为Max Trx ID的属性处,这个属性占用8个字节的存储空间
redo日志格式较多,没必要都进行了解
redo日志的写入
相关概念
log buffer
【日志缓冲】
【日志缓冲】
log buffer 被分成了多个大小为512字节的块(Bolck)
log buffer 默认大小为 16KB
可以通过 innodb_log_buffer_size 来进行设置
redo文件
redo日志也被分为了大小为512字节的块
redo日志大小设置
innodb_log_file_size
默认大小为48MB
redo日志文件个数设置
innodb_log_files_in_group
默认有2个,最多可以设置为100
MySQL数据目录下默认有两个文件
ib_logfile0和ib_logfile1
文件组
redo日志文件组中的每个文件大小都一样,格式也一样,都是由两部分组成:前2048个字节,也就是前4个block是用来存储一些管理信息的
从第2048字节往后是用来存储log buffer中的block镜像的
过程
redo日志首先会写入到 log buffer 中
然后再从 log buffer 中顺序的将数据块写入到redo日志文件中
块中进行顺序IO写入,当一块写满后再往下一个块中写入
redo log 会循环写入,在覆盖前会保证脏页以进行了刷盘
当负荷较大时,InnoDB会进行强制刷盘
redo避免覆盖的保证
Log Sequence Number(LSN)
全局变量:日志序列号
规定初始的lsn值为8704
每条redo日志和LSN一一对应
LSN值越小,说明redo日志产生的越早
系统LSN的查看
SHOW ENGINE INNODB STATUS\G
Log sequence number
当前所有的redo日志最大的日志序号
Log flushed up to
已经进行了刷盘的redo日志的的日志序号
代表了系统变量中的flushed_to_disk_lsn的值
redo日志的刷盘时机
log buffer空间不足时
事务提交时
为了保准数据的持久性,需要把修改的redo日志刷新到磁盘中
innodb_flush_log_at_trx_commit的系统变量
表示redo日志刷盘的时机
值分类
0
事务提交时不立即向磁盘中同步redo日志,这个任务是交给后台线程做的
1
事务提交时需要将redo日志同步到磁盘,可以保证事务的持久性
默认值
2
事务提交时需要将redo日志写到操作系统的缓冲区中,但并不需要保证将日志真正的刷新到磁盘
将事务的持久性的命运交到了操作系统
后台有一个线程,大约每秒都会刷新一次log buffer中的redo日志到磁盘
正常关闭服务器时
事务原子性的保证——Undo日志
事务相关概念补充
事务分类
只读事务
特点
只读事务中不可以对普通的表(其他事务也能访问到的表)进行增、删、改操作
但可以对用户临时表做增、删、改操作
START TRANSACTION READ ONLY语句开启一个只读事务
读写事务
特点
对普通表可以进行任何操作
通过START TRANSACTION READ WRITE语句开启一个读写事务
或者直接使用 BEGIN、START TRANSACTION开启
我们默认使用的事务就是读写事务
事务id
由系统变量进行统一维护
只有开启的事务开始执行写操作InnoDB才会给事务分配一个事务Id
日志
undo日志的断电恢复也需要依靠undo日志,undo日志的改变也会记录到redo日志中
日志格式
使用FIL_PAGE_UNDO_LOG类型的页面是专门用来存储undo日志的
日志格式:和数据页和索引页类似
roll_pointer的作用
roll_pointer本质上就是一个指向记录对应的undo日志的一个指针
不同操作对应的undo日志
Insert
类型
TRX_UNDO_INSERT_REC
说明
原则上需要添加聚集索引和二级索引便哈的多条undo日志
只需要考虑向聚簇索引插入记录时的情况就好了
Delete
类型
TRX_UNDO_DEL_MARK_REC类型
说明
行记录中有一个删除的标记位 delete_mask以及指向下一个记录的next_record
数据页中的整行链表
数据页中的垃圾链表
页中Page Header中PAGE_FREE的属性
它指向由被删除记录组成的垃圾链表中的头节点
页数据的组织形式
过程
第一阶段:delete_mask标识位设置为1
第二阶段:正常记录链表中移除,并且加入到垃圾链表中
Update
更新主键
类型:
TRX_UNDO_DEL_MARK_REC类型
TRX_UNDO_INSERT_REC
说明
实际的操作就是删除原记录后再进行新记录的插入
所以会生成两条undo日志
不更新主键
类型
TRX_UNDO_UPD_EXIST_REC类型
存储空间不变化
就地更新(in-place update)
更新后的列和更新前的列占用的存储空间都一样大
直接在原记录的基础上修改对应列的值
存储空间发生变化
先删除掉旧记录,再插入新记录
被更新的列更新前和更新后占用的存储空间大小不一致
旧的记录从聚簇索引页面中删除掉,然后再根据更新后列的值创建一条新的记录插入到页面中
事务的执行过程
大体执行过程
事务断电的恢复过程
大体过程
为什么断电恢复使用的redo日志而不用binlog日志
binlog
记录表所有的更新操作
binlog是逻辑的记录,记录的是语句的原始逻辑
一般用于人工数据的恢复
binlog是追加日志,是个全量日志
redo log
redo日志对于我们是不可可见的
redo日志是物理记录,记录的是数据具体的变化
是MySQL用来实现断电数据重写的保证
redo日志是循环写入并且可以通过LSN得知当前数写入磁盘的情况
重点
想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时,binlog 是无法恢复的。虽然 binlog 拥有全量的日志,但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)
redo日志和undor日志关系
undo日志的恢复需要redo日志的支持
redo日志和binlog日志的一致性保证
使用了两阶段提交(2PC)来实现
步骤
当事务提交时InnoDB存储引擎进行prepare操作。
MySQL上层会将数据库、数据表和数据表中的数据的更新操作写入BinLog文件。
InnoDB存储引擎将事务日志写入Redo Log文件中。
MySQL8.0新特性
索引
隐藏索引
通过标识visiable来标识索引是否可以
索引调优使用
降序索引
MySQL8.0以前的降序索引底层还是通过升序保存的
MySQL8.0以后真正开始支持了底层以降序的排列
group by操作
MySQL8.0前会有隐式的排序
MySQL8.0后不在进行隐式排序
创建表时创建函数索引后
索引中就可以使用函数表达式
账户安全
用户的创建和授权分开
新增了密码管理的安全机制
系统方面
原子性的ddl
同在一个原子中的操作,其中一个操作失败,其他也都不会执行
自增列持久化
解决了自增主键重复的问题
重构了SQL优化器
更好的支持了文档型数据库和JSON
支持了窗口函数
正则表达式的增强
参数配置有了改动
InnoDB增强
并发性增强
行缓存
扫描性能增强了
成本模型改进
移除了server层的query cache
MySQL的内容拓展
表结构和索引设计原则
表设计
范式设计
反范式设计
比较
范式在工作中使用
缓存表的使用
计数器表
数据分库存放时可以利用反范式设计
表字段类型的选择
索引设计
索引列的类型尽量小
利用索引选择性和前缀索引
只为用于搜索、排序或分组的列创建索引
尽可能设计三星索引
0 条评论
下一页