mysql 间隙锁_百里度的博客-CSDN博客_mysql间隙锁
主键索引 范围查询有记录锁 对这些记录的操作会阻塞;
对不存在的记录加锁 会产生间隙锁 id 1,3 之间有id为2的间隙锁插入时,会阻塞
从图中可以看出,当 number 相同时,会根据主键 id 来排序
在普通索引列上,不管是何种查询,只要加锁,都会产生间隙锁,这跟唯一索引不一样;
在普通索引跟唯一索引中,数据间隙的分析,数据行是优先根据普通索引排序,再根据唯一索引排序。
sql执行顺序
jdbc->连接池->sql接口->查询缓存->解析器->优化器->存储引擎->文件系统->返回
连接层 服务层 引擎层
适合创建索引的情况
1.业务上有唯一限制的字段
2.频繁作为where查询的字段
3.经常group by和order by的字段
4.update delete 的where条件列
6.多表join外键 类型必须一致
7.字符串前缀索引。但不利于排序
查询选择度select count(distinct address) / count(*) from shop;
8.使用区分度高的字段,性别不适合
show variables like 'profiling'; 查看是否开启
set profiling=1;开启
show profiles; 查看最近执行SQL
show profile; 查看SQL执行顺序
慢查询日志可以过mysqldumpslow 分析,或explain
show STATUS like 'slow_queries' --查看慢查询次数
show VARIABLES like '%slow_query_log%' --慢查询是否开启及日志存放目录
开启慢查询日志记录
set GLOBAL slow_query_log =off;
set slow_query_log =off;
慢查询时间查看及设置
show VARIABLES like '%long_query_time%%'
set long_query_time =1;
还可通过
show VARIABLES like 'PROFILing' --查看是否开启
show PROFILES --查看最近一条执行情况,耗时在哪一步
show PROFILE FOR query 566; --指定queryid查看
mysql为什么推荐自增主键,依次递增,一个是b+tree 叶子节点存放数据有指针,递增可以快递找到,第二个防止页分裂,原来一页的数据突然插入中间id的数据,是非常损耗性能的。
可重复读 一个事务只生一个read view 所以重复查询数据一样
读已提交每次查询生成一个readview 根据事务ID查询小于当前事务的undolog
undo原子性redo持久性 只有表设置为innodb引擎才有
undo redo是物理(psysical)日志记录磁盘页改动,例
"Page 42:image at 367,2; before:'ke';after:'ca'"
binlog是逻辑(logic)日志update这种
表锁
读写锁
![f7ac423baf434ae8acd12389e93bd768.png](https://i-blog.csdnimg.cn/blog_migrate/898fa66bed915c8cdf1c607987f10a0f.png)
- 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
- 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务(将持有最少行级排他锁的事务进行回滚),让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on ,表示开启这个逻辑
行转列 用if(字段,判断值,else的值) 再分组
列传行 union all
分区
1.range分区
range分区,顾名思义,就是按照范围进行分区,下面是创建一个range分区表:
drop table if exists `range_table`;
create table `range_table`(
`id` int,
`name` varchar(10)
)
partition by range(id)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than maxvalue
);
2.list分区
list就是枚举的意思,list分区就是在创建各分区时具体指定哪些值属于这些分区,下面是创建list分区表的代码:
drop table if exists `list_table`;
create table `list_table`(
`id` int,
`name` varchar(10)
)
partition by list(id)(
partition p1 values in (1),
partition p2 values in (10,15,50)
);
3.1.常规hash
drop table if exists `hash_table`;
create table `hash_table`(
`id` int,
`name` varchar(10)
)
partition by hash(id)
partitions 3;
3.2.线性hash
线性hash在建表时只是比常规hash多了个linear
字段:
drop table if exists `hash_linear_table`;
create table `hash_linear_table`(
`id` int,
`name` varchar(10)
)
partition by linear hash(id)
partitions 3;
关于线性分区的具体计算规则可以参考官方文档:https://dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html,这里假设num是分区个数,value是某条记录的分区字段对应的值,N是最终经过计算得到的某个分区编号,则N的计算过程如下:
step1:V = power(2, ceil(log(2, num)))
step2:N = value & (V-1)
step3:if N>=num: N=N & (ceil(V/2) - 1)
4.key分区
主要还是参考官方文档吧,https://dev.mysql.com/doc/refman/8.0/en/partitioning-key.html,里面主要说的是,key分区类似于hash分区,只不过分区列不再强制为整型,可以为除text和BLOB两种类型外的其它类型。key分区也有两种,常规key和线性key,常规key对分区字段采用的是MD5算法,线性key对分区字段采用的是二次方算法,参考hash分区中的线性hash,分区列选取的具体规则为:
当表中只有主键primary key或只有唯一键unique key时,分区列必须包含主键或唯一键中的部分或全部字段,不允许出现主键或唯一键中字段以外的其它字段
当表中主键和唯一键同时存在时,分区列为主键和唯一键公共字段的部分或全部
当表中主键唯一键都没有时:任意指定除text和BLOB类型外的其它字段,可以为1个或多个
分区列也可以缺省不指定,但必须要求表中存在主键或唯一键,优先以主键作为分区字段,没有主键时以唯一键作为分区字段,此时唯一键必须显示指定not null。
下面是常规key分区建表的一个demo,name为分区字段:
drop table if exists `key_table`;
create table `key_table`(
`id` int,
`name` varchar(10) not null,
unique `uk_name` (name)
)
partition by key()
partitions 3;
线性key分区的建表也只是多了一个linear字段:
5.子分区(复合分区)
文档地址:https://dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html,里面有这么一段话,
说的是我们可以对采用range分区或者list分区的表,进行二次分区,二次分区只能为hash分区或者key分区。这种分区方式有两种建表写法,一种是指定子分区名,一种是不指定子分区名由系统默认。
不指定子分区名创建:
drop table if exists `subpart_table`;
create table `subpart_table`(
dt date
)
partition by range(year(dt))
subpartition by hash(month(dt))
subpartitions 2 (
partition p1 values less than (1990),
partition p2 values less than (2000),
partition p3 values less than maxvalue
);
优化
-
调整buffer_pool大小
查看命中率
方法一:
mysql> show engine innodb status\G;Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 314 / 1000 #缓冲池命中率
结果:最近6秒缓冲池命中率是99.8%方法二:
mysql> show global status like 'innodb%read%';(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request)*100
- 但如果我们将 IBP 的大小设置为物理内存的 80% 以后,发现命中率还是很低,此时我们就 应该考虑扩充内存来增加 IBP 的大小innodb_buffer_pool_instances
- 如果 innodb_buffer_pool_size 大小超过 1GB, innodb_buffer_pool_instances 值就默认为 8;否则,默认为 1。
- 建议指定 innodb_buffer_pool_instances 的大小,并保证每个缓冲池 实例至少有 1GB 内存。通常,建议 innodb_buffer_pool_instances 的大小不超过 innodb_read_io_threads + innodb_write_io_threads 之和,建议实例和线程数量比例为 1:1。
- SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace','Com_select');
- 根据查询调整读写线程数默认都是4 innodb_read_io_threads innodb_write_io_threads
- innodb_flush_log_at_trx_commit 默认1
- 0每秒从redo log日志 到刷盘1每次事务刷盘2 异步刷盘 先写到redo log buffer
扩容 平滑2N方案(大数据量)
-
当需要扩容的时候,我们把A0和B0升级为新的主库节点,如此由2个分库变为4个分库。同时在上层的分片配置,做好映射,规则如下:
把uid%4=0和uid%4=2的数据分别分配到A和A0主库中
把uid%4=1和uid%4=3的数据分配到B和B0主库中
由于之前uid%2的数据是分配在2个库里面,扩容之后需要分布到4个库中,但由于旧数据仍存在(uid%4=0的节点,还有一半uid%4=2的数据),所以需要对冗余数据做一次清理。
这个清理,并不会影响线上数据的一致性,可以随时随地进行。
处理完成之后,为保证数据的高可用,以及将来下一步的扩容需求。
可以为现有的主库再次分配一个从库。
2. 什么时候考虑分库分表
注意,分库和分表是两件事。可以只分库,在不同的库中有一些相同结构的同名表,即多库单表;也可以只分表,一张表在一个库中有多个不同名的表,即单库多表;当然也可以同时既分库又分表,即多库多表,总之依具体需求而定。通常分库分表后预期达到什么效果是:分库后可承受的并发量增加,服务器得以扩充,磁盘容量等资源不再紧张;分表后单表数据量变少,SQL 执行速度提升。
如果你的系统处于快速发展时期,每天的订单流水都新增几十万或更多记录,并且订单表的查询效率明显变慢时,就需要规划分库分表了。如前所述,一般B+树索引高度为2~3层最佳,如果数据量在几千万级别,当高度变成4层,数据读写操作就会显著变慢。
3. 如何分库分表
根据行业惯例,通常按照水平或垂直两种方式进行数据分片。当然,有些复杂业务场景也可能选择两者结合的方式。
(1)水平拆分与垂直拆分
水平拆分是一种横向按业务维度拆分的方式,比如常见的按用户维度拆分,根据一定的规则把不同用户相关的数据分散在不同库表中。如果业务场景决定都是从用户视角进行数据读写,就可以选择按照水平方式进行用户数据分库分表。
垂直拆分可以简单理解为,把一张表的不同字段拆分到不同的表中。例如,假设有个小型电商业务,把一个订单相关的商品信息、买卖家信息、支付信息都放在一张大表里。可以考虑通过垂直拆分的方式,把商品信息、买家信息、卖家信息、支付信息都单独拆分成独立的表,并通过订单号与订单基本信息关联起来。还有一种情况,如果一张表有10个字段,其中只有3个字段需要频繁修改,那么就可以考虑把这3个字段拆分到子表,避免在更新这3个数据时,影响到其余7个字段的查询行锁定。
如果真的采用分库分表的话,我个人认为还是更专注水平拆分。而垂直拆分,一般在定需求的时候就已经划分好了。
(2)水平分库分表策略
常用的水平分库分表策略一般有range、hash取模、range+hash取模混合三种,分别用于不同场景。
range,即范围分片策略。比如我们可以将表的主键,按照每1000万的划分为一个表。有时候也可以按时间范围来划分,如不同年月的订单放到不同的表,这也是一种range划分策略。范围分片策略有利于扩容,因为不需要数据迁移。假设数据量增加到5千万,只需要水平增加一张表即可,之前0~4000万的数据不需要迁移。但这种方案会有数据热点,例如订单id一直递增,也就是说最近一段时间的数据都存储在一张表里。比如最近一个月的订单都在1000万~2000万之间,平时用户一般查最近一个月的订单比较多,都请求到order_1表,这就导致了数据热点问题。
hash取模分片策略是对指定的分片键hash后再对分表总数进行取模,把数据分散到各个表中。比如把原始订单表分成4张分表,id列作为分片键。对于给定id行的存储表为hash(id)%4,即把数据分存储到t_order_0 - t_order_3四张表中。这种方案的优点是不会存在明显的数据热点,缺点是弹性伸缩(缩扩容)需要做数据迁移。如从4张表扩容到8张表,那之前id=5的数据存储在t_order_1表中(5%4=1),现在应该放到t_order_5(5%8=5),也就是说历史数据要做迁移。
既然range存在热点数据问题,hash取模扩容迁移数据比较困难,我们可以把两种方案综合在一起取长补短,即range+hash取模混合分片策略。比较简单的做法就是,分库时用range范围方案,比如订单id在0~4000万的区间,划分为订单库1;id在4000万~8000万的数据,划分到订单库2。将来扩容时,id在8000万~1.2亿的数据,划分到订单库3。然后在每个订单库内,再用hash取模的策略,把不同订单数据划分到不同的表中。
(3)分成多少库多少表
分库的时候主要考虑业务峰值读写QPS和并发数。根据实际业务场景,可以根据历史QPS等数据进行评估。假设只需要3500个数据库连接,如果单库可以承担最高1000个数据库连接,那么就可以拆分成四个库。
分表数量依据业务数据量进行估算。假设一个表平均每天产生20万条数据,如果系统设计使用年限3年,那么该表总的数据量约为 365 * 20 * 3 = 2.19亿,如果每张表的数据量限定为1000万行,则需要21.9张表,再考虑业务高峰余量,可以按32张表来分片。
(4)数据倾斜问题
一个良好的分库分表方案,它的数据应该是比较均匀地分散在各个库表中。如果设计不当,很容易遇到以下类似问题:
某个数据库实例中,部分表的数据很多,而其他表中的数据却寥寥无几,业务上的表现经常是延迟忽高忽低,飘忽不定。
数据库集群中,部分集群的磁盘使用增长特别块,而部分集群的磁盘增长却很缓慢,每个库的增长幅度相差很大。这种情况会给后续的扩容带来步调不一致,无法统一操作的问题。
可以定义分库分表最大数据偏斜率为:(数据量最大样本 - 数据量最小样本)/ 数据量最小样本。一般来说,最大数据偏斜率在5%以内是可以接受的,如下图所示。