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这种
表锁
读写锁
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 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%以内是可以接受的，如下图所示。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
