宁波本地模板网站建设平台,crm系统永久免费,东莞网站seo价格,搜索引擎优化方法与技巧文章目录 一、创建1. 磁盘1.1 页、扇区、寻道、寻址、硬盘性能 2. 行结构row_format2.1 Compact紧凑2.1.1 行溢出2.1.2 作用2.1.3 内容1-额外信息1、变长字段长度2、NULL值列表3、记录头信息 2.1.4 内容2-真实数据4、表中列的值5、transaction_id6、roll_point7、row_id 2.2 dy… 文章目录 一、创建1. 磁盘1.1 页、扇区、寻道、寻址、硬盘性能 2. 行结构row_format2.1 Compact紧凑2.1.1 行溢出2.1.2 作用2.1.3 内容1-额外信息1、变长字段长度2、NULL值列表3、记录头信息 2.1.4 内容2-真实数据4、表中列的值5、transaction_id6、roll_point7、row_id 2.2 dynamic(默认)2.3 Compressed压缩 3. 页结构3.1 基本信息3.1.1 大小3.1.2 最小单位3.1.3 页结构 3.2 File Header3.2.1 作用3.2.2 内容 3.3 File Trailer3.4 用户记录User Records3.5 最大和最小记录3.6 页目录3.6.1 作用3.6.2 页目录二分法实现快速查找slot槽位 3.7 Header3.8 多页数据存储 4. 区段表空间5. 索引5.1 聚簇索引5.1.1 数据结构5.1.2 Innodb的索引模型-N叉树5.1.3 B树 5.2 二级索引5.2.1 单列索引5.2.2 联合索引5.2.3 前缀索引 5.3 索引创建SOP5.3.1 强制5.3.2 建议 6. 三范式6.1 键和属性6.2 一范式6.3 二范式6.4 三范式6.5 反范式 7. 表设计规范8. 字段设计规范8.1 SOP8.2 主键8.2.1 默认主键8.2.2 自增id存在的问题8.2.3 UUID作主键的劣势8.2.4业务字段作主键8.2.5 自增主键不一定连续 8.3 Varchar8.3.1 长度8.3.2 char,varchar,json类型的选用8.3.3 varchar(20)与varchar(255)8.3.4 varchar(255) 和varchar(256)8.3.5 BloB 8.4 字符集COLLATE8.4.1 定义8.4.2 作用8.4.3 默认值8.4.4 utf8mb4分类8.4.5 大小写敏感 8.5 Json类型8.5.1 使用场景8.5.2 大小8.5.3 创建字段8.5.4 CRUD8.5.5 对象转为json字符串8.5.6 json字符串转对象8.5.7 json字符串比较不同 8.6不使用明文存储密码。 二、查询1. sql执行顺序2. sop2.1 强制2.2 建议 3. 常见函数3.1 distinct3.2 count3.3 when then3.4 日期格式转换 4. 索引4.1 数据展示顺序4.2 回表 覆盖索引4.3 索引下推4.4 唯一索引和二级索引 5. join5.1 left join5.2 inner join5.3 on 和 where区别5.3.1 概述5.3.2 举例5.3.3 on where5.3.4 on5.3.5 IS NOT NULL 和 IS NULL 6. group by6.1 分组、聚合6.2 临时表问题 7. union 和 UNION ALL8. order by8.1 背景8.2 全字段filesort过程8.3 rowId排序过程8.4 sop 9. limit10. json类型10.1 json相关函数10.2 k-v作为查询条件10.3 查询k对应的v10.4 查询所有的一级k10.5 json_search函数10.6 在原有的k-v基础上再增加k-v10.7 更新k对应的v值 11. 综合查询11.1 背景表11.2 运算介绍11.3 分析11.4 具体Sql 12. 慢查询检测12.1 常用命令12.2 mysqldumpslow工具 13. Explain13.1 table13.2 Type连接类型13.3 rows13.4 extra13.5 filtered13.6 key13.7 key_length13.8 ref13.9 格式13.10 优化器优化后的语句13.11 监控分析视图-sys schema13.12 mybatis自定义Explain插件 三、事务1. 事务定义2. 事务特性ACID3. 事务状态4. 隔离性和隔离级别4.1 读未提交4.2 读已提交4.3 可重复读RR【mysql的隔离级别】4.4 可串行化【最安全】 5. 事务使用SOP5.1 不要使用长事务的原因5.2 事务的生效范围5.3 采坑记录5.3.1 本地写 rpc写5.3.2 Transactional使用注意事项5.3.3 多数据源问题 6. 事务日志6.1 一个事务中2条更新语句的执行过程6.2 redo log6.2.1 组成6.2.2 redo log刷盘操作含义过程 6.2.3 两阶段提交最终版 四、锁1. 数据操作类型1.1 共享锁1.2 排他锁 2. 数据操作粒度2.1 表锁2.1.1 优缺点2.1.2 自增锁Auto Icr2.1.3 元数据锁MDL 2.2 行锁2.2.1 快照读和当前读2.2.2 加行锁SOP2.2.3 影响并发度的锁放在事务最后2.2.4 行锁变表锁场景2.2.5 Record lock记录锁2.2.6 Gap lock间隙锁2.2.7 next-key lock临键锁2.2.8加锁2原则2优化1bug原则1原则2优化1优化2一个“bug” 2.3 乐观悲观锁2.3.1 悲观锁2.3.2 乐观锁 2.4 死锁2.4.1 产生死锁的条件2.4.2 避免死锁的方式2.4.3 发生死锁时的策略2.4.4 死锁实战间隙锁导致死锁锁商品时遇到的死锁 3. 锁结构3.1 产生一条锁结构3.2 锁结构 4. 锁监控4.1 查看行锁的竞争信息4.2 查询锁信息 5. MVCC多版本并发控制5.1 作用5.2 组成5.2.1 隐藏字段5.2.2 uodo log回滚日志5.2.3 ReadView一致性视图定义事务能读取到哪条数据 5.3 作用于事务隔离级别5.3.1 MVCC保证可重复读5.3.2 MVCC解决幻读5.3.3 可重复读、读已提交区别 五、主从复制、数据库备份与恢复1. bin log1.1 内容1.2 作用Redo和bin在数据恢复上的不同点 1.3 特点1.4 写入机制 2. 数据恢复3. 主从复制3.1 水平分表3.2 垂直分表 4. 主从延时4.1 表象4.2 产生场景4.3 解决4.4 从根本上解决数据一致性 5. 主从切换6. 数据库集群|db|表备份 六、参数和命令写在最后 一、创建
1. 磁盘
1.1 页、扇区、寻道、寻址、硬盘性能
1、页和扇区大小如图2.20 2、磁盘内部结构如图2.53
磁头、扇区、磁道 硬盘的容量存储容量磁头数×磁道柱面数×每道扇区数×每扇区字节数
3、影响硬盘性能的因素
磁盘完成一个I/O请求所花费的时间它由寻道时间、旋转延迟和数据传输时间三部分构成。
寻道时间
Tseek 即将读写磁头移动至正确的磁道上所需要的时间。avg在3-15ms
旋转延迟寻址
Trotation 即盘片旋转将请求数据所在的扇区移动到读写磁盘下方所需要的时间。
旋转延迟取决于磁盘转速通常用磁盘旋转一周所需时间的1/2表示。比如7200rpm的磁盘平均旋转延迟大约为60*1000/7200/2 4.17ms
数据传输时间
Transfer即完成传输所请求的数据所需要的时间它取决于数据传输率其值等于数据大小除以数据传输率。
SATA II可达到300MB/s的接口数据传输率数据传输时间通常远小于前两部分消耗时间可忽略。
4、衡量性能的指标
机械硬盘的连续读写性能很好但随机读写性能很差
这主要是因为磁头移动到正确的磁道上需要时间随机读写时磁头需要不停的移动时间都浪费在了磁头寻址上
衡量磁盘的重要主要指标是IOPS和吞吐量。
1IOPS
IOPSInput/Output Per Second即每秒的输入输出量或读写次数即指每秒内系统能处理的I/O请求数量。
随机读写频繁的应用如小文件存储等关注随机读写性能IOPS是关键衡量指标。可以推算出磁盘的IOPS 1000ms / (Tseek Trotation Transfer)如果忽略数据传输时间理论上可以计算出随机读写最大的IOPS。常见磁盘的随机读写最大IOPS为
7200rpm的磁盘 IOPS 76 IOPS
10000rpm的磁盘IOPS 111 IOPS
15000rpm的磁盘IOPS 166 IOPS
2吞吐量
吞吐量Throughput指单位时间内可以成功传输的数据数量。
顺序读写频繁的应用如视频点播关注连续读写性能、数据吞吐量是关键衡量指标。
它主要取决于磁盘阵列的架构、通道的大小、磁盘的个数。
不同的磁盘阵列存在不同的架构但他们都有自己的内部带宽一般情况下内部带宽都设计足够充足不会存在瓶颈。磁盘阵列与服务器之间的数据通道对吞吐量影响很大比如一个2Gbps的光纤通道其所能支撑的最大流量仅为250MB/s假如磁盘的吞吐量为40MB/s一秒就可以读取2560个页。一个页的读取耗时为0.4ms左右
2. 行结构row_format
2.1 Compact紧凑
2.1.1 行溢出
一页16kb 16384字节varchar(65533)显然一行数据的列值 一页的大小这时campact行格式采取的存储措施是一页只存储该字段值的一部分剩余部分存储在其他页中使用20字节记录指向其他页的地址和存了多少字节
2.1.2 作用
Compact是一种基于固定长度存储的方式存储的额外信息较少具有较高的存储效率
2.1.3 内容1-额外信息
行结构整体如图2.34 1、变长字段长度
字段为name varchar(32)时实际存储数据’mjp’没用到32字符。这时候就需要记录下变长字段的真实长度
egname varchar(32) 、mark varchar(255)实际存储一条数据时为(‘mjp’‘test’)则变长字段长度为03、04倒过来即为 04 03即在.ibd文件中使用16进制查看数据时这一行数据的变长字段长度内容为04 03Null值不记录
2、NULL值列表
插入一条数据(1,‘mjp’,null,‘2024-01-21’,null)如果列字段值为null则使用二进制1表示0表示非null则此数据二进制形式0 0 1 0 1,倒过来即10100对应十六进制结果为20在ibd文件中存储的就是10100。如果某一列明确声明NOT NULL则不需要使用0、1来表示会忽略所以在.ibd文件中(1,‘mjp’,null,‘2024-01-21’,null)并不会记录null只会通过10100 记录1、mjp和2024-01-21
3、记录头信息
记录头整体结构如图2.35所示 1delete_mark
大小占用1个二进制位作用标记着当前记录是否被删除内容 0代表记录并没有被删除 1代表记录被删除掉了 delete语句实际上并没有真实的将数据从页中从磁盘上物理删除这些被删除的记录之所以不立即从磁盘上移除是因为移除它们之后其他的记录在磁盘上需要重新排列导致性能消耗只打一个删除标记被删除掉的记录都会组成一个所谓的垃圾链表在这个链表中的记录占用的空间称之为可重用空间之后如果有新记录插入到表中的话可能把这些被删除的记录占用的存储空间覆盖掉被覆盖掉时数据才算是真正的被物流删除了
2record_type
作用记录的类型内容 0表示普通记录 1表示B树非叶节点记录即表示目录项 2表示最小记录 3表示最大记录
3heap_no 作用表示当前记录在本页中的位置 内容插入数据时图2.35中四条数据heap_no从2开始算起依次为2、3、4、5 0最小记录的位置对应record_type 2 1最大记录的位置对应record_type 3 它俩的位置最靠前
4next_record
多条数据的简单结构如图2.25 作用从当前记录的数据到下一条记录的数据的地址偏移量即单链表 eg第一条记录的next_record值为32意味着从第一条记录的数据的地址处向后找32个字节便是下一条按照主键值由小到大的顺序记录的真实数据最小记录heap_no 0 record_type2的下一条记录就是本页中主键值最小的用户记录而本页中主键值最大的记录的下一条记录就是最大记录上图用箭头代替偏移量表示next_record 删除操作 如图2.26所示 第2条数据的delete_mark会从0 - 1第一条数据的next_record会指向第三条数据n_owned会从5 - 4 和页目录有关最小记录作为组一数据个数1条、4条数据最大记录作为组二个数5条会将组个数记录在每组的最大记录中组一的个数为1记录在最大成员中即最小记录组一就它一个成员n_owned1组二的个数为5记录在最大成员中即最大记录组二中最大成员就是最大记录n_owned5现在组二失去了第二条数据所以成员个数从5 - 4所以最大记录中的n_owned从5 - 4 如果删除了多条数据多条垃圾数据之间会用链表串联起来
2.1.4 内容2-真实数据
4、表中列的值
正常列的数据
5、transaction_id
事务id6字节
6、roll_point
回滚指针7字节
7、row_id
行id6字节非空且唯一如果用户未声明主键idInnoDB会使用row_id作为primary key。
如果表中有主键id了则不会存在row_id
2.2 dynamic(默认)
mysql会根据行的大小自动选择不同的存储方式进行存储以实现更好的查询性能和空间利用率
1、和Compact的区别
发生行溢出时dynamic是将该字段的全不值都存到其它页中
2.3 Compressed压缩
行溢出时处理方式和dynamic相同在此基础上使用zlib算法进行压缩处理
3. 页结构
3.1 基本信息
3.1.1 大小
16kb
3.1.2 最小单位
数据库I/O操作的最小单位是页即使修改了一条数据从内存回刷磁盘IO也是页维度
mysql中的存储空间如图2.32 最大的就是表空间即对立表空间.ibd文件存储了索引和数据
3.1.3 页结构
如图2.33 3.2 File Header
3.2.1 作用
描述各种页的通用信息如页的编号、其上一页、下一页是谁等
3.2.2 内容
1、offset
每一个页都有一个单独的页号就跟你的身份证号码一样InnoDB通过页号可以唯一定位一个页。
2、type
代表当前页的类型页类型常用如下
Undo log日志页事务系统数据索引页数据页
3、prev和next
InnoDB都是以页为单位存放数据的如果数据分散到多个不连续的页中存储的话需要把这些页关联起来通过双向链表关联prev和next就是双向链表的2个指针保证这些页之间不需要是物理上的连续而是逻辑上的连续。
4、checksum校验和
1代表当前页面的校验和
2校验和定义
类似hash算法给定2个长字符串进行比较字符串是否相同如果hash结果值不一样则两个字符串一定不相同校验和同理用于校验2个页是否相同
3作用
如果发生了update操作在修改后的某个时间需要把数据同步到磁盘中但是在同步了一半的时候断电了造成了该页传输的不完整可能只同步了半页的数据不满足页作为最小IO操作的条件为了检测一个页是否完整这时可以通过文件尾的校验和checksum 值与文件头的校验和做比对如果两个值不相等则证明页的传输有问题需要重新进行传输。具体的 当完全写完时没断电正常情况下校验和也会被写到页的尾部则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了那么在File Header中的校验和就代表着已经修改过的页而在File Trailer中的校验和代表着原先的页二者不同则意味着同步中间出了错 eg 磁盘中页10的头校验和值 111尾检验和111此时内存中对页10进行了update操作页校验和更新为777回刷将磁盘中页10的头检验和更新为了777此时断电了磁盘中页10的尾检验和还是111显然头尾不一样说明不是一个完整的回刷页要么就是重启后继续将页10剩下的数据回刷完成这样尾校验和也更新为777要么就是回滚本次回刷操作
5、lsn
Log Sequence Number页面被最后修改时对应的日志序列位置
作用结合校验和一起校验页的完整性的
3.3 File Trailer
检验和 lsn同File Header一起检验页的完整性
3.4 用户记录User Records
按照比如Compact行格式一条一条相互之间形成单链表参考行结构
3.5 最大和最小记录
Compact行格式中的记录头信息中的heap_no和record_type
heap_no 0最小记录,record_type2heap_no 1最大记录,record_type3
3.6 页目录
3.6.1 作用
在页中记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便但是检索效率不高
页目录通过二分查找法的方式快速定位到具体的某行数据提升效率。
3.6.2 页目录二分法实现快速查找
slot槽位
页目录结构如图2.36和2.37 1、定义
每一页中将一部分数据划分成一组第 1 组也就是最小记录所在的分组只有 1 个记录最后一组就是最大记录所在的分组会有 1-8 条记录其余的组记录数量在 4-8 条之间n_owned在每个组中最后一条记录的头信息中会存储该组一共有多少条记录页目录[]用来存储每组最后一条记录的地址偏移量即槽slot每个槽相当于指针指向了对应组的最后一个记录如果是聚簇索引则指向这一组id最大值对应的数据如果是二级索引则指向这一组索引字段最大值对应的数据
2、作用
当确认数据在某一页中则从目录页中按照二分法查找所有槽位slot的值快速定位到具体在哪个组中
3、查找实操
页10对应的页目录[1,9,18,27]找id 20的很明显在slot427对应的分组中但是slot427是这个组中最大值链表是单向从小到大的不能向前找。所以需要找slot318的next_record依次找到id20的数据
3.7 Header
1、slots页槽的个数方便二分查找
2、还未使用空间的最小地址当插入新数据时可以快速获取要插入此页中的哪儿
3、页数据个数
4、最后插入记录的位置
5、max_trx_id二级索引中修改当前页的最大事务的id
6、页的在B数中的层级
7、当前页属于哪个索引
3.8 多页数据存储
简略如图2.27
页之间的地址不一定连续不需要页10地址0x01 -必须指向页11地址0x02自增id必须连续 必须是1 - 3 - 4 - 5如果页10中已有数据1 -3 - 5且页10已经插满了数据此时插入数据id 4不能将id 4的数据插入页28必须将页10分裂重新排为1-3-4id 5的数据存入页28 每一页的多条数据之间是单向链表、页之间是双向链表
4. 区段表空间
1、区定义
一个区是1M可以存放64个连续的页
2、区作用
顺序IO因为页之间可能不是物理连续的存储而是使用的双向链表进行逻辑上连续。为了顺序IO查找更快将64个物理上连续的页放在一个区中。这样where c between 10 and 300很有可能就在一个区的连续页中这样顺序IO一次就可以将这些页都捞取出来。
3、段定义
如果区1、区2都放叶子节点区3、区4都放目录页即非叶子节点那么区1、2放在一个段中数据段这样更方便查询。区3、4放在一个段中索引段
4、表空间定义
InnoDB的最高层的逻辑结构数据和索引都存放于此。 独立表空间每张表都有一个单独的表空间便于空间管理 大小新表创建时.ibd文件默认96kb(6页)mysql8.0之后.frm和.ibd都和为.ibd了默认112kb(7页)
5. 索引
5.1 聚簇索引
5.1.1 数据结构 主键索引的叶子节点存的是整行数据。在InnoDB里主键索引也被称为聚簇索引clustered index。
非主键索引的叶子节点内容是主键的值。在InnoDB里非主键索引也被称为二级索引secondary index。 叶子节点装的是每一行的数据对应的record_type 0表示普通记录 非页面节点record_type 1表示目录项记录页30中 第一行 2最小,1目录,1,1,1,3(最大)第二行1表示这一页的最小数据id1第三行10表示id1的数据在第10页是个地址 数据即索引索引即数据 Innodb的索引和数据存放在.ibd文件中因为数据本身就是要一个索引(数据结构)存储的索引的叶子节点又是数据。 聚簇表示叶子节点存放数据(聚簇索引)非聚簇表示叶子节点不存放数据(二级索引叶子节点只存放主键id不存放数据所有才会有根据主键id再回表这一说)
5.1.2 Innodb的索引模型-N叉树
以InnoDB的一个整数字段索引为例这个N差不多是1200。这棵树高是4的时候就可以存1200的3次方个值这已经17亿了。
考虑到树根【叶子节点】的数据块总是在内存中的一个10亿行的表上一个整数字段的索引查找一个值最多只需要访问3次磁盘。
在机械硬盘时代从磁盘随机读一个数据块需要10 ms左右的寻址时间。也就是说对于一个10亿级别行的表使用1200叉树来存储单独访问一个行需要3个10 ms的时间
5.1.3 B树
数据都是存储在B树中的每一个索引在InnoDB里面对应一棵B树。
5.2 二级索引
5.2.1 单列索引
如图2.29
叶子节点索引列的排序值以及对应的主键id值非叶子节点即目录页中实际上也存储了主键id如图2.31 这样在存入(id9,c21)的数据时就可以在页3中先根据c21判断再根据id 9判断可推断出存入页5中
5.2.2 联合索引
如图2.30以c2、c3两字段组成联合索引 页55中第1、2条数据的c2列值相同则再按照c3列的值进行递增排序o、u当我们where c2 4 and c3 ‘u’ 页72找到c2 4的数据在c22对应的页59内因为页59对应的c2最小值为2页70对应的c2最小值为9了肯定不满足页59找c24的数据可能在页50对应c2的最小值为2c24可能在此页中也可能在页55中但是页50中c2min2但是c3min为e页55c2min 4且c3min o我们要找的c3 u的所以可以判断处c24 and c3 u’一定在页55中页55找到c24 and c3 u’对应的主键id 1 目录记录的唯一性事实上非叶子节点中除了存储了c2、c3、页地址还存储了主键id的值。通过c2c3id保证目录记录的唯一性因为只靠c2 c3是无法保证目录的唯一性的因为c2 c3都相同的数据也可能存在这样在插入一条新的数据时加入c2x and c3 y的数据也存在这个时候就需要靠id来判断存入哪个页中
5.2.3 前缀索引
为字符串创建前缀索引定义好长度就可以做到既节省空间又不用额外增加太多的查询成本
alter table SUser add index index1(email); alter table SUser add index index2(email(6));select id,name,email from SUser where emailzhangssxyzxxx.com;查询步骤如图2.4回表1次 从index1索引树找到满足索引值是’zhangssxyzxxx.com’的这条记录取得ID2的值到主键上查到主键值是ID2的行判断email的值是正确的将这行记录加入结果集取index1索引树上刚刚查到的位置的下一条记录发现已经不满足emailzhangssxyzxxx.com’的条件了循环结束。
前缀索引查询步骤如图2.5回表4次 从index2索引树找到满足索引值是’zhangs’的记录找到的第一个是ID1到主键上查到主键值是ID1的行判断出email的值不是’zhangssxyzxxx.com’这行记录丢弃取index2上刚刚查到的位置的下一条记录发现仍然是’zhangs’取出ID2再到ID索引上取整行然后判断这次值对了将这行记录加入结果集重复上一步直到在idxe2上取到的值不是’zhangs’时循环结束。
在这个过程中要回主键索引取4次数据也就是扫描了4行。
1优点
对于这个查询语句来说如果你定义的index2不是email(6)而是email(7也就是说取email字段的前7个字节来构建索引的话即满足前缀’zhangss’的记录只有一个也能够直接查到ID2只扫描一行就结束了。
2缺点 有可能额外增加回表的次数 使用前缀索引就用不上覆盖索引对查询性能的优化了必须回到ID索引再去判断email字段的值 前缀索引无法做orderBy、groupBy可以在代码中使用map的groupBy
3确定前缀的长度
算出这个列上有多少个不同的值 select count(distinct email) as L from user;//假设有1000个不同的值依次选取不同长度的前缀来看这个值比如我们要看一下4~7个字节的前缀索引可以用这个语句
select count(distinct left(email,4) as L4, //只有200个不同的值损失了太多区分度select count(distinct left(email,5) as L5, //有500个不同值仍损失一半区分度select count(distinct left(email,6) as L6, //有800个不同值select count(distinct left(email,7) as L7, //有950个不同值损失比例5%可以接受。
left(email,7)既节省空间又不用额外增加太多的查询成本损失区分度导致的多次回表4如果前缀索引很难区分则其他优化方式 倒序存储 如果你存储身份证号的时候把它倒过来存每次查询的时候可以这么写
select x,x,x from t where id_card reverse(xxxxx);由于身份证号的最后6位没有地址码这样的重复逻辑所以最后这6位很可能就提供了足够的区分度。
5使用limit时候不能使用前缀索引
alter table user add index index2(email(6));select id,name,email from user where name mjp order by email limit 3;在order by的时候可能前3个数据都一样无法支持使用索引排序只能用文件排序
5.3 索引创建SOP
5.3.1 强制
1、索引字段禁止为NULL请为字段设置默认值
2、创建联合索引时区分度高的字段在前
区分度计算
select count(distinct(a)) / count(*) from t;发展角度 表中字段poi_id和sale_date已知poi_id总共就只会有100种不同的值而sale_date每个月就是30种不同的值而且随着日期不断推迟sale_date字段的区分度只会越来越高。这种情况下联合索引的顺序建议为:
sale_date,poi_id
3、对于区分度很低的字段没必要放在联合索引中。
eg性别非男即女50%的区分度eg是否有效字段有效|无效没必须放在索引中尤其是在创建唯一键的时候这种区分度为50%的字段更不要作为唯一键的一部分想想这种字段是否可以不存在于表中最好
4、ctime字段要加上索引(便于后续数据归档)
5.3.2 建议
1、单张表中索引数量不超过6个, 单个索引中的字段数不超过5个
2、建议使用联合索引而非单个索引
3、如果必须建立(a,b)和b两个索引注意索引字段大小。egname字段是比age字段大的 那我就建议你创建一个name,age)的联合索引和一个(age)的单字段索引。
4、sale_date日期字段作为索引时建议使用int(11)而非varchar
sale_date int(11) NOT NULL COMMENT ‘销售日期’好处1统一格式均为20240101好处2sale_date作为索引字段相较于varchar更好比较大小便于查询
5、对频繁更新的字段尽量不要创建索引
egstatus字段、utime
6. 三范式
要想设计一个结构合理的表必须满足一定的范式(标准)
6.1 键和属性
球员表(player) id、球员编号 、 姓名 、 身份证号 、 年龄 、 球队编号 球队表(team) id、球队编号 、主教练 球队所在地 主键 默认为自增主键id 外键 球员表中的球队编号。 主属性 能够唯一确定一条数据的字段。在球员表中主属性是id、球员编号、身份证号 非主属性 其他的属性姓名 年龄球队编号都是非主属性。
6.2 一范式
1、定义每一列原子不可再分
2、举例address列上海市杨浦区大桥街道1000弄
可以拆分为
省市区详细地址上海上海杨浦大桥街道1000弄
3、原子性
是主观的主要是根据业务是否会用于搜素统计等诉求。加入要统计上海市-杨浦区的用户有多少人则address字段就需要拆分为原子的更小粒度。如果没有这种诉求则直接作为user_info一起存储也可。
6.3 二范式
1、定义
非主键必须完全依赖主键不能有部分依赖
2、举例
完全依赖
商品表: skuId是主键skuName、品类id、商品价格等都是非主键。都完全依赖skuId
skuId已知则skuName就知
skuId已经则对应的品类id就知
skuId已知则对应的价格就知
非完成依赖
学生id老师id学生name老师name100012001张三李丽100022002李四韩理100032003王五齐其
主键学生id 老师id
部分依赖学生name字段只依赖主键中学生id即部分依赖。同理老师name
问题数据冗余
3、解决多对多
学生表老师表学生-老师关联表多对多关系
id学生id老师id11000120012100022002310003200341000420035100042004
学生1老师N
10004学生的数学老师是2003语文老师是2004
老师1学生N
2003数学老师既教10003同学又教10004同学
6.4 三范式
1、定义非主键必须直接依赖主键不能传递依赖
本质两个非主键之间不能有依赖关系
球队名称 --球队编号 --球员编号部门名称 -- 部门编号 --员工编号品类名称 -- 品类id -- sku_id
2、解决
产生依赖传递的两个非主键单独抽出来一张表。
表1品类id、skuId、商品价格等表2品类id、品类名称
3、优点和缺点 优点冗余低 缺点需要各种join查询效率低
6.5 反范式
1、背景
范式的缺点就是需要各种join查询效率低。我司甚至不允许复杂sql查询比如join、子查询等。所以这时候就需要反范式
2、举例
品类名称 -- 品类id -- sku_id不满足3NF。但业务一般展示sku的品类id同时也会展示这个品类名称。如果为了满足3NF拆开则需要用join查询这样业务访问量大的时候反而影响查询性能。所以在性能和标准之间我们也需要考量
3、问题
如果我们为了满足性能采用了反范式可能存在以下问题
品类名称 -- 品类id -- sku_id
原本品类id100100的品类名称是蔬菜。所以sku表中的一条数据为id1、skuId777、category_id:100100、category_name: 蔬菜有一天业务语义调整了品类id100100不表示蔬菜了表示牛奶或者干脆品类id100100不存在了。这样的话id1这条数据中category_name就是脏数据这样后续查询等操作就不准确了为了数据准确就需要刷新id1的数据所以如果我们满足3NF单独抽出来category_id和category_name关系表这样category_id对应的category_name变了就只需要更新下最新的name即可。category_id没了则直接删除category_id即可。不会有脏数据。
4、思考和感悟
对玉sku_id和sku_name这种name一般不会改变或删除的且又需要一起查询到并展示可以使用反范式对于category_id和category_name这种经常可能变化的可以遵循3NF单独抽出来
7. 表设计规范
不建议使用复数做列名或表名建表
create table my_table(id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 主键,order_no varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT 单号,supply_code int(11) DEFAULT 0 COMMENT 供应商code,sku_category_level tinyint(4) NOT NULL DEFAULT 0 COMMENT 品类级别,sku_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT COMMENT sku名,extra_info json DEFAULT NULL COMMENT sku额外信息,price decimal(26,6) DEFAULT NULL COMMENT sku价格,trigger_date date NOT NULL COMMENT 触发日期2024-01-01,trigger_time time NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 触发时间10:00:00,apply_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 申请时间,approve_time datetime DEFAULT NULL COMMENT 审批时间,ctime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,utime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间,valid tinyint(1) NOT NULL DEFAULT 1 COMMENT 是否有效0无效,1有效,PRIMARY KEY(id),UNIQUE KEY uniq_order_no(order_no),KEY idx_ctime(ctime),KEY idx_code_level(supply_code,sku_category_level),
) ENGINE InnoDB AUTO_INCREMENT 1 DEFAULT utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 自定义表补充order_no使用了utf8mb4_bin大小写敏感区分大小写
8. 字段设计规范
8.1 SOP
建议字段not null因为null的存储会占用更多的空间相比较默认值0等效’0’、等在索引统计和值的比较时都更加复杂索引必须是not null的varchar 是可变长字符串不预先分配存储空间长度不要超过1024如果存储长度过长 MySQL 将定义字段类型为 json。甚至考虑独立出来一张表用主键来对应避免影响其它字段索引效率。请为列添加默认值tinyint(1)tinyint值有符号范围: -128 - 127tinyint(1)代表显示1位boolean即1tinyint(4)表示byte显示4位数字符号 》4位int(11)用来展示字符的个数11个int(1)和int(20)是相同的主要是mysql客户端命令行用来显示字符的个数varchar(1000)一个字母是1字节一汉字是3字节所以最多占用1000*32(varchar 255需要额外与的2字节记录字符串的长度)3002字节当需要唯一约束时才使用 NULL仅当列不能有缺失值时才使用 NOT NULL
8.2 主键
8.2.1 默认主键
如果你创建的表没有主键那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。
8.2.2 自增id存在的问题
id是在数据库服务端生成的不是在业务层面生成安全性不高前端入参id 1很明显能够知道where id 1是查询条件。容易攻击
8.2.3 UUID作主键的劣势
对比自增整型UUID大小8字节mysql是用字符串存储占36字节使用二进制方式存储则占16字节浪费存储空间比较大小字符串比大小慢整型快插入随机插入如果4k页已满则需要移动分页顺序插入不需要移动已经数据效率高查询慢连续存储查询是一段范围更快 自增UUIDmysql8.0中对UUID作为优化uuid可以单调递增将秒-分-时改为时-分-秒同时存储占用16字节 (uuid_to_bin(UUID(), true));综上顺序io不需要再额外的寻道只需要第一次寻址完成后磁头顺序读取数据即可。
所以顺序I/O一般比随机I/O快几千倍
8.2.4业务字段作主键 商品id不可以这个id可能会注销给别的商品用 手机号:不可以用户可能会注销运营商再给别人用 身份证号不可以因为主键id不能为空但是并不是所有用户都愿意将隐私信息输入。可能会影响业务推进 单据号可以。TG|HT 01|02|03|04 231231 10位随机数(一个业务类型一天最多有10位数的单据生成可以使用雪花算法生成、也可以使用leaf生成、也可以使用redis自增生成) 推荐生成单据号
8.2.5 自增主键不一定连续
1、背景
唯一键冲突导致id自增1后无法恢复到错误发生之前的值事务的回滚也会产生类似的现象
2、描述
表中name字段是唯一键假如表中有了一条数据(1,mjp,18)此时再insert(null,mjp,28)
3、执行流程如下 Innodb发现用户没有传入自增id值会去获取当前表的自增值应为2 将传入的内容改为(2,mjp,18) 再insert之前会先修改自增至为3然后再插入 报唯一键冲突。语句返回 此时id2的数据没有插入成功但是id此时已经为3了不会被改回去。下次再插入数据之前会获取到id3 所以没有id2这行数据 补充批次一200、批次二200、批次三200批量插 public void batchInsert(ListTestTableDO DOList) {if (CollectionUtils.isEmpty(DOList)) {return;}Lists.partition(DOList, 200).forEach(partitionData - {this.testTableAutoGeneratorMapper.batchInsert(partitionData);});
}同一个批次插入多条数据时是原子性的一条失败这个批次200条都失败不会写入表中 不同批次之间没有原子性报错的批次 以及 其后面的批次都不会写入。报错之前的批次数据可正常插入 eg1批次一失败批次一中200条数据都不会写入。且后续批次二、三都不会执行 eg2批次一成功会插入200条数据批次二中有失败则批次二全部写入失败批次三不会执行 eg3原本id 1批次一中因为唯一键冲突导致插入失败则批次2再插入时下一条数据的id 1 batchSize即id从201开始
4、优化 可以使用alter table A engineInnoDB语句来优化空洞 。
8.3 Varchar
8.3.1 长度 varchar(100) 就是指 100 个字符 最大65535但是只能Varchar(65532)行结构中变长字段长度2字符、NULL值列表1字符如果声明了Not Null则不需要业务中不建议超过5000超过了5000可选择将此字段拆出来text65535放在另外一张表或使用Json
8.3.2 char,varchar,json类型的选用
知道固定长度的用char0-255,比如MD5串固定是32位经常变化的字段用varchar最好不要超过255超过255字符的只能用varchar或者text,不能用char
8.3.3 varchar(20)与varchar(255) 都是可变的字符串 使用二者保存较短的字符串’hello world’时,占用的空间都是一样的 当20字符长度255,varchar(20)会报错,varchar(255)会正常插入 字符串特别长 字符长度255,可以选择更大的值,VARCHAR(M)定义的列长度为可变长字符串M取值可以为0~65535(64K)
8.3.4 varchar(255) 和varchar(256)
当定义varchar长度小于等于255时长度标识位需要一个字节当大于255时长度标识位需要两个字节
故
当我们定义一个varchar(255)的字段时其实它真实使用的空间是2562551字节 当我们定义一个一个varchar(256)字段时它真实使用的空间是2582562字节
8.3.5 BloB
大小BLOB16kmediumblob16M、LongBlob4G
8.4 字符集COLLATE
8.4.1 定义
字符集是一套符号和编码用于比较字符的一套规则。
8.4.2 作用
在mysql中字符类型的列比如char、varchar都需要有collate和mysql进行交互告诉mysql这些列该如何进行排序COLLATE和order by、distinct、group by、having语句息息相关同时也会影响where条件中大于小于等于的查询
8.4.3 默认值
通常的字符集都utf8mb4MySQL8.0默认utf8mb4编码的默认值为utf8mb4_general_ci不区分大小写
8.4.4 utf8mb4分类
utf8mb4_bin将字符串每个字符用二进制数据编译存储其数据比较方法其实就是直接将所有字符看作二进制串然后从最高位往最低位比对。所以很显然它是区分大小写utf8mb4_general_ci没有实现Unicode排序规则在遇到某些特殊语言或者字符集排序结果可能不一致。但是在绝大多数情况下这些特殊字符的顺序并不需要那么精确。utf8_general_ci校对规则进行的比较速度很快但是与使用 utf8mb4_unicode_ci的校对规则相比比较正确性较差utf8mb4_unicode_ci是基于标准的Unicode来排序和比较能够在各种语言之间精确排序Unicode排序规则为了能够处理特殊字符的情况实现了略微复杂的排序算法。
总结general_ci 更快unicode_ci 更准确、utf8mb4_bin对字符大小写敏感。补充现在的CPU来说它远远不足以成为考虑性能的因素索引涉及、SQL设计才是。使用者更应该关心字符集与排序规则在db里需要统一
8.4.5 大小写敏感
1、概念
ci即case insensitive不区分大小写即大小写不敏感。A和a在排序和比较的时候是一视同仁。
selection * from test where cloumna同样可以把cloumn为A的值选出来。对于mysql来说a和‘A’没有区别2、实战
使用单据号作为唯一键 数据001A落表的时候会变成001a因为mysql默认是不区分大小写的。
这样当表里面有001a的时候 其是唯一键再存001A就会导致唯一键冲突。
3、情景复现
CREATE TABLE user (name varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 名字,UNIQ_KEY name (name) COMMENT 唯一索引
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT这里的建表语句使用的是COLLATEutf8mb4_unicode_ci这种方式创建的表中的字符串字段都是不区分大小写的。表里面已经有name aaaa的数据 name字段是唯一键 name为字符串且为ci格式 再次插入数据name AAAA’时就会报错。唯一键冲突 复现
4、解决
utf8mb4_unicode_ci或utf8mb4_general_ci utf8mb4_bin
方式一改变表的属性区分表中字符串的列使所有字符列都区分大小写不建议
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;方式二也可以针对某一字符的字段属性utf8mb4_bin推荐 alter table table modify column name varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT COMMENT 姓名;5、sop
建表时使用
ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENTxxxx需要大小写区分 如用于存用户登陆的账号、密码。密码就必须大小写敏感 当业务需要某个字段需要区分大小写。即大小写不同有不同的含义时则需要将字段设置为大小写敏感即utf8mb4_bin
8.5 Json类型
8.5.1 使用场景
一般用于记录单据对应的明细即1N场景
{单据某个属性1:{属性1的name、描述:, 属性1的类型type:,属性1的值value:属性1的额外extra信息:}
}8.5.2 大小
实战中单行json字段列最大存储6553564kb
8.5.3 创建字段
xxx json default null comment 8.0.13版本之前json类型字段不允许设置默认值。之后则可以default(‘’)
8.5.4 CRUD
插入 INSERT INTO table (id, data) VALUES (1, {name: John, age: 30}); 更新
UPDATE table SET data JSON_SET(data, $.age, 31) WHERE id 1; 删除JSON列中的数据
UPDATE my_table SET data JSON_REMOVE(data, $.age) WHERE id 1;8.5.5 对象转为json字符串
1、GsonUti
UtilityClass
Slf4j
public class GsonUtils {private static final Gson GSON new Gson();public static String toJsonStr(Object object) {return GSON.toJson(object);}public static String toJsonStr(Object obj, Type type) {return GSON.toJson(obj, type);}public static T T fromJson(String json, ClassT classOfT) {return GSON.fromJson(json, classOfT);}public static T T fromJson(String json, Type typeOfT) {return GSON.fromJson(json, typeOfT);}public static String toJson(Object object) {try {return GSON.toJson(object);} catch (Exception e) {log.error(序列化失败, e);}return StringUtils.EMPTY;}
}2、map-json
map.put(0915, 1);
map.put(0916, 2);
String jsonStr GsonUtils.toJsonStr(map);3、date - json
如果类中有Date类型的属性字段则需要在创建gson的时候设置一下日期格式Gson gson new GsonBuilder().setDateFormat(yyyy-MM-dd).create();System.out.println(gson.toJson(new Date()));8.5.6 json字符串转对象
1、{} - 对象
String str {\status\:{\code\:0,\__isset_bit_vector\:[1]},\poiId\:1};TResponse tResponseA GsonUtils.fromJson(str, TResponse.class);2、json-map
MapString,String json2Map GsonUtils.fromJson(jsonStr,new TypeTokenHashMapString,String(){}.getType());3、json-list
ListSkuDTO list GsonUtils.fromJson(jsonStr,new TypeTokenListSkuDTO(){}.getType());ListLong list2 GsonUtils.fromJson(Lists.newArrayList(1L,2L), new TypeTokenListLong() {}.getType());8.5.7 json字符串比较不同
public void t() throws JSONException {String s1 {\skuId\:1,\skuName\:\test\,\temp\:2,\address\:\bj\};String s2 {\skuId\:1,\skuName\:\test\,\temp\:3,\author\:\mjp\};// 方式一JSONCompareResult result JSONCompare.compareJSON(s1, s2, JSONCompareMode.STRICT);dependencygroupIdorg.skyscreamer/groupIdartifactIdjsonassert/artifactIdversion1.5.1/versionscopetest/scope/dependency// 1中有2中没有System.out.println(result.isMissingOnField());ListFieldComparisonFailure fieldMissing result.getFieldMissing();for (FieldComparisonFailure fieldComparisonFailure : fieldMissing) {System.out.println(fieldComparisonFailure.getExpected());//address}// 2中有1中没有System.out.println(result.isUnexpectedOnField());ListFieldComparisonFailure fieldUnexpected result.getFieldUnexpected();for (FieldComparisonFailure fieldComparisonFailure : fieldUnexpected) {System.out.println(fieldComparisonFailure.getActual());//author}// 1中2中都有但是val值不一样System.out.println(result.isFailureOnField());ListFieldComparisonFailure list result.getFieldFailures();for (FieldComparisonFailure fieldComparisonFailure : list) {System.out.println(fieldComparisonFailure.getField());//tempSystem.out.println(fieldComparisonFailure.getActual());//3System.out.println(fieldComparisonFailure.getExpected());//2}// 方式二HashMapString, Object diffMap Maps.newHashMap();Gson gson new Gson();MapString,Object json1Map gson.fromJson(s1,new TypeTokenHashMapString,Object(){}.getType());MapString,Object json2Map gson.fromJson(s2,new TypeTokenHashMapString,Object(){}.getType());for (Map.EntryString, Object entry : json1Map.entrySet()) {String k1 entry.getKey();Object v1 entry.getValue();Object v2 json2Map.get(k1);// 1中有2中没有if (v2 null) {diffMap.put(k1, v1);continue;}// 1中2中都有但是不一样if (!Objects.equals(v1, v2)){diffMap.put(k1, expect: v1 , actual: v2);}}json2Map.forEach((k2, v2) - {Object v1 json1Map.get(k2);// 2中有1中没有if (v1 null) {diffMap.put(k2, v2);}});System.out.println(diffMap);//{tempexpect:2.0, actual:3.0, addressbj, authormjp}}8.6不使用明文存储密码。
使用RSA加密实现如下
public class TestRSA {/*** RSA最大加密明文大小*/private static final int MAX_ENCRYPT_BLOCK 117;/*** RSA最大解密密文大小*/private static final int MAX_DECRYPT_BLOCK 128;/*** 获取密钥对** return 密钥对*/public static KeyPair getKeyPair() throws Exception {KeyPairGenerator generator KeyPairGenerator.getInstance(RSA);generator.initialize(1024);return generator.generateKeyPair();}/*** 获取私钥** param privateKey 私钥字符串* return*/public static PrivateKey getPrivateKey(String privateKey) throws Exception {KeyFactory keyFactory KeyFactory.getInstance(RSA);byte[] decodedKey Base64.decodeBase64(privateKey.getBytes());PKCS8EncodedKeySpec keySpec new PKCS8EncodedKeySpec(decodedKey);return keyFactory.generatePrivate(keySpec);}/*** 获取公钥** param publicKey 公钥字符串* return*/public static PublicKey getPublicKey(String publicKey) throws Exception {KeyFactory keyFactory KeyFactory.getInstance(RSA);byte[] decodedKey Base64.decodeBase64(publicKey.getBytes());X509EncodedKeySpec keySpec new X509EncodedKeySpec(decodedKey);return keyFactory.generatePublic(keySpec);}/*** RSA加密** param data 待加密数据* param publicKey 公钥* return*/public static String encrypt(String data, PublicKey publicKey) throws Exception {Cipher cipher Cipher.getInstance(RSA);cipher.init(Cipher.ENCRYPT_MODE, publicKey);int inputLen data.getBytes().length;ByteArrayOutputStream out new ByteArrayOutputStream();int offset 0;byte[] cache;int i 0;// 对数据分段加密while (inputLen - offset 0) {if (inputLen - offset MAX_ENCRYPT_BLOCK) {cache cipher.doFinal(data.getBytes(), offset, MAX_ENCRYPT_BLOCK);} else {cache cipher.doFinal(data.getBytes(), offset, inputLen - offset);}out.write(cache, 0, cache.length);i;offset i * MAX_ENCRYPT_BLOCK;}byte[] encryptedData out.toByteArray();out.close();// 获取加密内容使用base64进行编码,并以UTF-8为标准转化成字符串// 加密后的字符串return new String(Base64.encodeBase64String(encryptedData));}/*** RSA解密** param data 待解密数据* param privateKey 私钥* return*/public static String decrypt(String data, PrivateKey privateKey) throws Exception {Cipher cipher Cipher.getInstance(RSA);cipher.init(Cipher.DECRYPT_MODE, privateKey);byte[] dataBytes Base64.decodeBase64(data);int inputLen dataBytes.length;ByteArrayOutputStream out new ByteArrayOutputStream();int offset 0;byte[] cache;int i 0;// 对数据分段解密while (inputLen - offset 0) {if (inputLen - offset MAX_DECRYPT_BLOCK) {cache cipher.doFinal(dataBytes, offset, MAX_DECRYPT_BLOCK);} else {cache cipher.doFinal(dataBytes, offset, inputLen - offset);}out.write(cache, 0, cache.length);i;offset i * MAX_DECRYPT_BLOCK;}byte[] decryptedData out.toByteArray();out.close();// 解密后的内容return new String(decryptedData, UTF-8);}/*** 签名** param data 待签名数据* param privateKey 私钥* return 签名*/public static String sign(String data, PrivateKey privateKey) throws Exception {byte[] keyBytes privateKey.getEncoded();PKCS8EncodedKeySpec keySpec new PKCS8EncodedKeySpec(keyBytes);KeyFactory keyFactory KeyFactory.getInstance(RSA);PrivateKey key keyFactory.generatePrivate(keySpec);Signature signature Signature.getInstance(MD5withRSA);signature.initSign(key);signature.update(data.getBytes());return new String(Base64.encodeBase64(signature.sign()));}/*** 验签** param srcData 原始字符串* param publicKey 公钥* param sign 签名* return 是否验签通过*/public static boolean verify(String srcData, PublicKey publicKey, String sign) throws Exception {byte[] keyBytes publicKey.getEncoded();X509EncodedKeySpec keySpec new X509EncodedKeySpec(keyBytes);KeyFactory keyFactory KeyFactory.getInstance(RSA);PublicKey key keyFactory.generatePublic(keySpec);Signature signature Signature.getInstance(MD5withRSA);signature.initVerify(key);signature.update(srcData.getBytes());return signature.verify(Base64.decodeBase64(sign.getBytes()));}public static void main(String[] args) {try {// 生成密钥对KeyPair keyPair getKeyPair();String privateKey new String(Base64.encodeBase64(keyPair.getPrivate().getEncoded()));String publicKey new String(Base64.encodeBase64(keyPair.getPublic().getEncoded()));System.out.println(私钥: privateKey);System.out.println(公钥: publicKey);// RSA加密String data 待加密的文字内容;String encryptData encrypt(data, getPublicKey(publicKey));System.out.println(加密后内容: encryptData);// RSA解密String decryptData decrypt(encryptData, getPrivateKey(privateKey));System.out.println(解密后内容: decryptData);// RSA签名String sign sign(data, getPrivateKey(privateKey));System.out.println(加签后sign);// RSA验签boolean result verify(data, getPublicKey(publicKey), sign);System.out.print(验签结果: result);} catch (Exception e) {e.printStackTrace();System.out.print(加解密异常);}}
}二、查询
1. sql执行顺序
fromjoinonwheregroup by(开始使用select中的别名后面的语句中都可以使用)avg,sum…【selec后面的分组函数、having后面的分组函数。having max(salary) 8000)】havingselect : 拿出所有数据distinctunionorder bylimit
如图1.1
两块一块是Server层它主要做的是MySQL功能层面的事情
一块是引擎层负责存储相关的具体事宜。
1、连接器-建立连接
长连接防止长连接占用内存过快导致OOM5.7后执行 mysql_reset_connection来重新初始化连接资源
2、分析器-解析sql
你输入的是由多个字符串和空格组成的一条SQL语句MySQL需要识别出里面的字符串分别是什么代表什么。一般语法错误会提示第一个出现错误的位置所以你要关注的是紧接“use near”的内容。
3、优化器优化
sql执行顺序、索引的选择mysql会选择总代价最小的sql进行执行
总代代价 IO代价 CPU代价 内存代价 远程代价
1其中IO代价
从磁盘中读取一页的数据的代价默认代价值1
2CPU代价
键比较和行估算
3内存代价
创建临时表默认代价值为20。在内存中创建临时表还好点磁盘中代价太大
4、执行器执行
第一次调用的是“取满足条件的第一行”这个接口之后循环取“满足条件的下一行”这个接口这些接口都是引擎中已经定义好的。
5、调用Innodb的API接口查询数据并返回
2. sop
2.1 强制
1、不要使用负向查询如NOT IN/NOT LIKE
2、严禁 like ‘%abc’、like ‘%abc%如果非要则走es等搜索引擎’、not in 、! 、not exists、等语法。推荐like ‘北京%’
3、禁止使用select for update
4、禁止使用select子查询使用join替代
原因子查询一般会建临时表
select * from t where t.id in(select f.id from table where table.id in(10,20,30)
);
// 使用join替换
select * from t inner join table on t.id table.id where table.id in(10,20,30);2.2 建议
1、不建议使用前项通配符查找例如 “foo”查询参数有一个前项通配符的情况无法使用已有索引。
2、对于连续的数值能用 BETWEEN 就不要用 IN 了
select id from t where num in(1,2,3,4);3、conut任意统计某列非空个数一般都是全表扫描尽量减少使用有NULL的行不算。
count查询where条件要能走索引
4、新增的查询SQL一定要执行过执行计划访问类型type至少要达到 range 级别要求是 ref 级别如果可以是 consts 最好。禁止all和index
5、ORDER BY、GROUP BY、DISTINCTUNION的字段,充分利用索引先天顺序避免排序否则会消耗大量磁盘IO
6、对于select, in 操作建议控制在200个之内。最好在100内
7、索引中断问题 使用了函数 对字段做了函数计算就用不上索引 对索引字段做函数操作可能会破坏索引值的有序性因此优化器就决定放弃走树搜索功能。 8.0之后的版本a_b_c联合索引当where条件是b and c 也会使用到联合索引
8、not in、not exists建议使用left join on where xxx is null替代
9、如果要使用where (col1 a and col2 b) or (col1 c and col2 d)
mysql 5.7.3之后优化支持以下等效查询
where (col1,col2) in ((a,b),(c,d)
) 对应代码
Select
(scriptSELECT * FROM fulfill_data where (sale_dt, rdc_id) in foreach collectionpairs item pair open( separator , close) (#{pair.saleDate}, #{pair.rdcId}) /foreach limit 0,200
/script)
ListXxxDO select(Param(pairs) ListListKey pairs);不可使用以下方式查询
SELECT * FROM t WHERE sale_dt in(20240115, 20240116) and rdc_id in (111,777)原因但是使用上述方式查询可能会产生笛卡尔乘积结果
sale_dtrdc_id20240115111202401161112024011577720240116777
但实际我们查询的是(sale_da 20240115 and rdc_id 111) or (sale_da 20240116 and rdc_id 777) 应该只返回2条数据才对
3. 常见函数
3.1 distinct
1、对结果中某一列去重
select * from 表名 where id in(select max(id) from 表名 group by 要去重的字段 )2、去重
select distinct(rdc_id) from table where sale_date 2024-01-22;使用distinct时去重字段最好作为联合索引的一部分因为索引本身就是排序好的这样找where满足条件的数据后本身就是排序好的去重即可。否则会using temp
3.2 count
按照效率排序的话count(普通字段)count(主键id)count(索引字段)count(1)≈count(*)
3.3 when then
(
CASE table1.sku_temperature_zone WHEN 5 THEN 常温 WHEN 2 THEN 冷藏 WHEN 1 THEN 冷冻
END
) as 温层3.4 日期格式转换
1、yyyymmdd 转 yyyy-mm-dd wheresale_date concat(substr(20220517, 1, 4),-,substr(20220517, 5, 2),-,substr(20220517, 7, 2));2、yyyy-mm-dd 转yyyymmdd
concat(substr(2022-05-18,1,4),substr(2022-05-18,6,2),substr(2022-05-18,9,2));方式2
sale_date replace(2021-11-03,-,);4. 索引
4.1 数据展示顺序
如果使用到了联合索引则按照联合索引的顺序依次展示数据。如果没有用到索引则按照主键id自增顺序展示数据
4.2 回表 覆盖索引
1、索引覆盖
假设我们有一个主键列为ID的表表中有字段k并且在k上有索引。
表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)两棵树的示例示意图如下2.1 如果语句是select * from T where ID500即主键查询方式则只需要搜索ID这棵B树如果语句是select * from T where k5即普通索引查询方式则需要先搜索k索引树得到ID的值为500再到ID索引树搜索一次。这个过程称为回表。【除非全覆盖索引查询selcet 字段和联合索引字段一致则不需要回表】也就是说基于非主键索引的查询需要多扫描一棵索引树。因此我们在应用中应该尽量使用主键查询。同时主键长度越小普通索引的叶子节点就越小普通索引占用的空间也就越小。如果执行的语句是select ID from T where k between 3 and 5这时只需要查ID的值而ID的值已经在k索引树上了因此可以直接提供查询结果不需要回表。也就是说在这个查询里面索引k已经“覆盖了”我们的查询需求我们称为覆盖索引或者联合索引的字段id字段包含了所有要查询的字段即为覆盖索引即不需要回表如果现在有一个高频请求要根据市民的身份证号查询他的姓名这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引不再需要回表查整行记录减少语句的执行时间。
2、回表
需要执行几次树的搜索操作会扫描多少行
select * from T where k between 3 and 5在k索引树上找到k3的记录取得 ID 300再到ID索引树查到ID300对应的R3在k索引树取下一个值k5取得ID500再回到ID索引树查到ID500对应的R4在k索引树取下一个值k6不满足条件循环结束。
在这个过程中回到主键索引树搜索的过程我们称为回表回表本质是随机I/O比较耗时。可以看到这个查询过程读了k索引树的3条记录步骤1、3和5回表了两次步骤2和4。 回表属于随机IO 原因二级索引叶子节点字段1 id1 、字段2 id2其中字段1、2是顺序排序的在一个页或一个区中但是id1和id2可能在不同的页不同的区所以回表本质上是随机IO
4.3 索引下推
背景
联合索引name, age现在需要检索出表中“名字第一个字是张而且年龄是10岁的所有男孩 select * from tuser where name like 张% and age10 and ismale1;问题
like后面索引失效。索引联合索引只能走到第一个name字段
无索引下推图2.2 在MySQL 5.6之前只能从ID3开始一个个回表。到主键索引上找出数据行再对比字段值。
此时不关注age字段只关注name字段nameage二级索引找到叶子节点id然后回表聚簇索引查询id对应的这行数据。然后再根据age值与这行数据中的age值对比一致才留下。因此需要回表4次。
索引下推图2.3 而MySQL 5.6 引入的优化index condition pushdown) 可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。
在(name,age)索引内部就判断了age是否等于10对于不等于10的记录直接判断并跳过。只需要对ID4、ID5这两条记录回表取数据判断就只需要回表2次。 优点减少回表次数所以无需回表的查询都不会触发索引下推 索引下推ICP触发的条件 name_age是联合索引其中age在联合索引字段中 8.0优化
8.0之后的版本where name like ‘张%’ and age10 and ismale1联合索引为name_age不需索引下推直接正常走索引
4.4 唯一索引和二级索引
1、查询
唯一索引
唯一索引查找到数据在某一页中的某一条数据时则可以结束。因为数据是唯一的
二级索引
这里以单字段二级索引为例当查到是某一页的某一条数据时还需要向后继续判断因为可能name mjp’的有多条数据
性能可以忽略
2、更新
唯一索引
由于需要提前将数据页读取到内存中用于判断插入的数据是否满足唯一性故无法使用change_buffer(用于缓存更新语句的内容类似于懒加载缓存多条更新语句等某条语句需要查询最新的内容了再去刷盘merge操作也会有后台线程定期的merge。这种懒加载避免了每次更新一条语句都要直接读取页刷页刷盘减少读盘操作)
二级索引可以使用到change_buffer
3、结论
如果更新频繁建议使用普通索引查询性能几乎无差、更新又可以使用到buffer减少读盘普通索引改为唯一索引有风险可能阻塞整个表的更新因为唯一索引会导致更新变得“复杂”写多读少的业务日志、账单类业务可以打开change_buffer且值设置大一些
5. join
5.1 left join
1、功能获取左表的所有记录即使右表没有对应的匹配记录用null填充
2、建议使用小表作为驱动表
1本质
双层for循环
2小表作驱动表的原因 假设驱动表全表扫描、被驱动表走索引 被驱动表行数M每次查询一条数据需要先搜索索引索引是B树结构且需要回表综上复杂度2回表二级索引搜索 * Log2M索引查询M条数据 驱动表行数N没索引全表扫描N行且每一行都要到被驱动表上去匹配一次 双层For循环复杂度 N * 2 * Log2M N扩大1000倍整体复杂度就扩大1000倍所以一定要让N小的做驱动表即“小表”作驱动表的原理
3“小表”定义
不是表本身数据量的多少而是
两个表按照各自的条件过滤过滤完成之后计算参与join的各个字段的总数据量数据量小的那个表就是“小表”正常情况下数据量小的表一般是小表
3、强制 被驱动表的查询条件必须是索引字段。理论上驱动表的匹配字段也最好是索引字段。 匹配的字段类型一定要一样要么都是整型要么都是字符串否则会使用函数转换索引失效
select * from t1 left join t2 on t1.name t2.user_name;5.2 inner join
1、功能获取两个表中字段匹配关系记录最终留下的每行数据中左表和右表对应的列一定有数据
2、建议使用inner join 即简写join时mysql会选择小表作为驱动表
3、优化器
如果t1的name字段没索引t2的user_name有索引会选择t2作为驱动表如果t1.name和t2.user_name都有索引会选择小表作为驱动表如果不想让优化器选择类似于强制走某个索引可以使用
straight_join代替join5.3 on 和 where区别
5.3.1 概述
on用于生产临时表的条件
where在临时表产生后再对临时表进行过滤返回最终的数据
5.3.2 举例
t1
idsku_id111122223333
t2
sku_idsale_date1112023-01-012222023-06-302222023-12-31
5.3.3 on where
select * from t1 left join t2 on t1.sku_id t2.sku_id where t2.sale_date 2023-06-30;1、步骤一将t1表中每行数据用on条件t1.sku_id t2.sku_id去获取匹配到的数据
t1.idt1.sku_idt2.sku_idt2.sale_date11111112023-01-0122222222023-06-3022222222023-12-31
2、t1未匹配到的行数据也保留下来这一行仅仅除了t1有正常数据t2的数据字段列均为null
t1.idt1.sku_idt2.sku_idt2.sale_date3333nullnull
3、经过on条件后生成的临时表temp
t1.idt1.sku_idt2.sku_idt2.sale_date11111112023-01-0122222222023-06-3022222222023-12-313333nullnull
5.3.4 on
select * from t1 left join t2 on t1.sku_id t2.sku_id and.sale_date 2023-06-30;1、将t1表中每行数据用on条件t1.sku_id t2.sku_id and.sale_date ‘2023-06-30’去获取匹配到的数据
t1.idt1.sku_idt2.sku_idt2.sale_date22222222023-06-30
2、t1未匹配到的行数据也保留下来这一行仅仅除了t1有正常数据t2的数据字段列均为null
t1.idt1.sku_idt2.sku_idt2.sale_date1111nullnull3333nullnull
3、经过on条件后生成的临时表temp
t1.idt1.sku_idt2.sku_idt2.sale_date22222222023-06-301111nullnull3333nullnull
5.3.5 IS NOT NULL 和 IS NULL
如图2.38 场景4
select * from a left join b on a.key b.key where b.key is null结合上述on where的执行过程先生成临时表然后在临时表的基础上进行b.key is null过滤。明显是过滤掉a中存在b中不存在的数据。所以场景4的图如上
结论查询条件not in 、not exists都可以替换成left join on where x is null
6. group by
6.1 分组、聚合 自动忽略null值 需要先分组、再聚合avg、sum否则整张表就是一个分组 使用group by 的场景当出现每种、每个的时候后续的内容就是需要分组的字段 eg1查询每种性别的最高分 select gender, max(math_score) from user group by gender;eg2: 查询每个部门的员工数 select depart_id, count(*) from emp group by depart_id;eg3: 接上having过滤 查询部门员工数 100的部门id步骤一每个部门的员工数
步骤二: 人数 100
select depart_id, count(*) from emp group by depart_id
having count(*) 100;每种工种最高工资 1w的工种id和最高工资数步骤一每种工种的最高工资
步骤二最高工资 1w
select job_id, max(salary) from emp group by job_id
having max(salary) 10000;查询最低工资 10号部门最低工资的 部门id和其对应的最低工资步骤一每个部门的最低工资
步骤二10号部门的最低工资
步骤三每个部门的最低工资 10号部门的最低工资
select depart_id, min(salary) from emp group by depart_id
having min(salary) (select min(salary) from emp where depart_id 10
);
建议改成join连接查询6.2 临时表问题
联合索引a_b_c
1、简单的临时表
select a,b,c from t where a 1 group by c;group by未使用到索引会产生临时表和排序using filesort、using temporary
2、复杂临时表
索引为a
select id % 10 as m , count(*) as c from t where a 1 group by m;产生带临时表内含2字段m主键和 c 扫描表t的索引a依次取出其叶子节点上的id值 计算id % 10 》 x 此时查看临时表中是否有值为x的行(1 % 10 1)第一次没有在临时表对应的m、c两列中插入一条记录(x, 1) mc11如果后续有了值为x的行(11 % 10 1)则m列值为x的这行对应的c列值1即x2 mc12 遍历完索引a后对临时表按照主键m进行正排序结果返回给客户端
3、不产生临时表、排序
select * from t where a 1 and b 2 group by c;
select * from t where a 1 group by b;
7. union 和 UNION ALL
1、union选取表1和表2不同的值
2、union all选取表1和表2所有的值允许重复
3、表1order by UNION ALL 表2 order by
(select * from t1 where xxx order by xxx limit 100)
UNION ALL
(select * from t2 where xxx order by xxx limit 100) 如果在UNION中使用order by那么必须使用()来包含查询参考综合查询中
4、如果不强制要求数据不可重复建议使用union all
因为union会给临时表加上distinct用上了临时表主键id的唯一约束。唯一性校验代价很高
如果非要用可以在java程序内存中进行去重。
如图2.19union执行去重过程 8. order by
8.1 背景
查询城市是“杭州”的所有人名字并且按照姓名排序返回前1000个人的姓名、年龄。
索引city
select city,name,age from t where city杭州 order by name limit 1000 ;Extra这个字段中的“Using filesort”表示的就是需要排序MySQL会给每个线程分配一块内存用于排序称为sort_buffer。 Extra字段显示Using temporary表示的是需要使用临时表 二者都出现需要临时表并且需要在临时表上排序
8.2 全字段filesort过程
如图2.6 1初始化sort_buffer确定放入name、city、age这三个字段
2从索引city找到第一个满足city杭州’条件的主键id也就是图中的ID_X
3到主键id索引取出整行取name、city、age三个字段的值存入sort_buffer中
4从索引city取下一个记录的主键id
5重复步骤3、4直到city的值不满足查询条件为止对应的主键id也就是图中的ID_Y
6对sort_buffer中的数据按照字段name做快速排序
7按照排序结果取前1000行返回给客户端。
如果排序数据量太大超过sort_buffer_size的大小默认1M
内存放不下则不得不利用磁盘临时文件辅助排序。外部排序一般使用归并排序算法MySQL将需要排序的数据分成12份每一份单独排序后存在这些临时文件中。然后把这number_of_tmp_files 12个有序文件再合并成一个有序的大文件。
8.3 rowId排序过程
原理
max_length_for_sort_data是mysql中专门用于控制排序的行数据的长度参数默认1024
如果select的字段加起来长度超过这个值则会从全字段排序换为rowId排序
实现步骤如图2.7 1初始化sort_buffer确定放入两个字段name和id
2从索引city中找到满足city杭州的第一个主键id
3然后使用id回表取name和id字段值放入sort_buffer
4从索引city字段取下一个满足的id同时使用id再回表然后再存值直到不满足city“杭州”
5对sort_buffer中的数据按照name排序
6对排序后的结果取limit 1000
7然后再使用这1000个id去回表依次拿出city、name、age三个字段返回给客户端 总结 默认会选择全字段排序内存sort_buffer_size(1M|256k)够用直接将所有字段放入sort_buffer中排序内存不够用会在堆外进行排序用临时文件进行排序然后再归并如果查询的单行数据内容超过1024则降为rowId排序
8.4 sop
1、如果无法避免filesort
可以调整max_length_for_sort_data尽量使用全字段排序而非rowId排序可以调整sort_buffer_size尽量在内存中排序而非在磁盘上排序
2、避免filesort
where查询条件可以使用到索引即可
select city,name,age from t where city杭州 order by name limit 1000 ;联合索引city_name在数据存储时就是有序的所以查询的还是直接匹配到city杭州的排序好的数据然后limit取1000条即可然后根据city、name、id使用id去回表拿到所需字段即可结合limit
3、发生了filesort但性能很好
联合索引name_age_address
select * from t where name m and age 10 order by address;
1会使用filesort但是性能比不使用filesort还好
2)只使用到了联合索引的前半部分name_age进行过滤后发现数据量不大1w级别这种可以直接在内存中排序反而更快4、limit起作用
联合索引name_age
全表扫描 filesort
select * from t where age 18 order by name;走索引 using where
select * from t where age 18 order by name limit 10
原因是优化器认为先按照name进行order by然后再where比对 age 18的取10条认为走索引更快9. limit
不要直接limit 10000, 200的写法正例:select table_name from table where id 10000 limit 200实战
ListDO result Lists.newArrayList();
boolean loop true;
long id 0L;
do {XxxDOExample example new XxxDOExample();example.limit(200);example.setOrderByClause(id asc);XxxDOExample.Criteria criteria example.createCriteria();criteria1.andIdGreaterThan(id);ListDO selectByExample myMapper.selectByExample(example);if (CollectionUtils.isNotEmpty(selectByExample)) {result.addAll(selectByExample);int size selectByExample.size();if (size 200) {loop false;} else {id selectByExample.get(size - 1).getId();}}
} while (loop);10. json类型
1、背景json字段net_pois_json数据内容如下
{test1:{poiId:323,skuId2Qty:{1:1,2:2},skuId2Date:{3:2023-01-01,4:2024-01-01}},test2:{poiId:10005977,skuId2Qty:{5:5,6:6},skuId2Date:{7:2027-01-01,8:2028-01-01}},test3:10,test4:[mjp,wxx]
}2、MapString, Object map 转为jsonString
map的key是String第test1、test2的v是个对象test3的v是Integer、test4的v是ListStirng
Gson g new Gson();
String netPpoisJson g.toJson(map);10.1 json相关函数
json_extract()从json中返回想要的字段
json_contains()json格式数据是否在字段中包含特定对象
json_object():将一个键值对列表转换成json对象
json_array():创建json数组
10.2 k-v作为查询条件
1、k-v是一级
select * from t where 联合索引字段查询 and json_contains(net_pois_json, json_object(test3, 10));等价
select * from t where 联合索引字段查询 and net_pois_json - $.test3 10;结果
这一行的数据
explain
ref 使用联合索引 using where
2、k-v是二级
背景
test1:{poiId:323,skuId2Qty:{1:1,2:2},skuId2Date:{3:2023-01-01,4:2024-01-01}},查询一级k1test1对应的v1中二级kpoiId对应的v2
select * from t where id 1 and json_contains(json_extract(net_pois_json, $.test1), json_object(poiId, 323)
);即判断
{poiId:323,skuId2Qty:{1:1,2:2},skuId2Date:{3:2023-01-01,4:2024-01-01}
}中是否有指定的对象poiId, 323
3、k-v是三级
select * from t where id 1 and json_contains(json_extract(net_pois_json, $.test1.skuId2Date), json_object(3, 2023-01-01)
);4、k-v作为范围查询条件
select * from t where id 1 and json_contains(net_pois_json - $.test4, json_array(mjp));10.3 查询k对应的v
1、k是一级
select id, json_extract(net_pois_json, $.test1) as v from t where id 1;等价
select id, net_pois_json - $.test1 as v from t where id 1;结果
id, test1对应的v
{poiId:323,skuId2Qty:{1:1,2:2},skuId2Date:{3:2023-01-01,4:2024-01-01}
}2、k是二级
select id, json_extract(net_pois_json, $.test1.skuId2Qty) as v from t where id 1;3、k是三级
select id, json_extract(net_pois_json, $.test1.skuId2Qty.3) as v from t where id 1;10.4 查询所有的一级k
select id, json_keys(net_pois_json) from t where id 1 ;结果[“test1”,“test2”,“test3”,“test4”]
10.5 json_search函数
1、作用查询包含v的数据
2、前提v必须是字符串
net_pois_json列{“id”:1, “name”:“苹果”, “price”:0.5}
select * from t where json_serarch(net_pois_json, all, 苹果) is not null;10.6 在原有的k-v基础上再增加k-v
{“id”:1, “price”:0.5} - {“id”:1, “name”:“苹果”, “price”:0.5}
update t set net_pois_json json_insert({id:1, price:0.5}, $.name, 苹果);10.7 更新k对应的v值
{“id”:1, “name”:“苹果”, “price”:0.5} - {“id”:1, “name”:“香蕉”, “price”:0.5}
update t set net_pois_json json_replace({id:1, name:香蕉, price:0.5}, $.name, 香蕉) where id 1;补充
如果id1的数据不是 {“id”:1, “name”:“苹果”, “price”:0.5}则会被替换成 {“id”:1, “price”:0.5}替换三级k对应的v
update t set net_pois_json json_replace(test1:{poiId:323,skuId2Qty:{1:1,2:2},skuId2Date:{3:2023-01-01,4:2024-01-01}}, $.test1.skuId2Date.3, 1994-11-23
)where id 1 ;参考文档
MySQL5.7中文文档
11. 综合查询
11.1 背景表
1、sale_log as result: 主表大部分字段都是取自这个表
2、sale_num as sale需要从这个表获取真实销量sale_num字段
3、schedule as snap: 需要从这个表获取最终售最大售卖量total_stock字段
11.2 运算介绍
(sale.sale_num - result.origin_max_sale) as 降低|带来多货|提高销量PCS;
(sale.sale_num - result.origin_max_sale) * result.sku_price as 销售额提高;11.3 分析
如图2.18
1、场景1
result表 left join sale 表并按照 where条件形成场景1-降多货
on条件result和sale二者中一一对应的关系字段where条件即降多货的条件
2、场景2
result 表 left join sale 表 on条件result和sale二者中一一对应的关系字段 where条件形成场景2-提升售卖量即提升GMV
3、场景1 UNION ALL场景2组成临时表t
4、result表left join snap表组成临时表m on条件result和snap二者中一一对应的关系字段 where条件是日期范围
5、临时表t INNER JOIN 临时表m形成最终的表 on条件t和m二者中一一对应的关系字段 where条件是网店id
6、概述sql
selectt.主要字段t.真实销量, m.最终最大售卖量
from t
inner joinm
ont和m一一对应关系
wherexxx11.4 具体Sql
select distinctt.商品SKUID,t.网店ID,t.网店名称,t.销售日期,t.角色,t.修改前数值,t.修改后数值,t.真实销量,m.total_stock as 实际修改量,t.OR值,t.降低|带来多货|提高销量,t.降低|带来多货|提高销量PCS,t.销售价,t.销售价 * t.真实销量 as GMV
from((selectresult.sku_id as 商品SKUID,result.poi_id as 网店ID,sale.poi_name as 网店名称,result.sale_date as 销售日期,(CASE result.role WHEN 1 THEN 商家 WHEN 2 THEN 普通用户 END) as 角色,result.origin_max_sale as 修改前数值,result.update_max_sale as 修改后数值,sale.sale_num as 真实销量,result.or_qty as OR值,(CASE 1 WHEN 1 THEN 降低多货 END) as 降低|带来多货|提高销量,(result.update_max_sale - result.origin_max_sale) as 降低|带来多货|提高销量PCS,result.sku_price as 销售价fromsale_log as resultLeft joinsale_num as saleonresult.sku_id sale.sku_idand result.poi_id sale.poi_idand replace(result.sale_date,_,) sale.sale_datewhereresult.sale_date between $$begindate and $$enddateand result.update_status 1and result.update_type 1and sale.sale_num result.update_max_saleOrder byresult.operate_timeDescLimit5000000)Union All(selectresult.sku_id as 商品SKUID,result.poi_id as 网店ID,sale.poi_name as 网店名称,result.sale_date as 销售日期,(CASE result.role WHEN 1 THEN 商家 WHEN 2 THEN 普通用户 END) as 角色,result.origin_max_sale as 修改前数值,result.update_max_sale as 修改后数值,sale.sale_num as 真实销量,result.or_qty as OR值,(CASE 1 WHEN 1 THEN 提高销量 END) as 降低|带来多货|提高销量,(result.update_max_sale - result.origin_max_sale) as 降低|带来多货|提高销量PCS,result.sku_price as 销售价fromsale_log as resultLeft joinsale_num as saleonresult.sku_id sale.sku_idand result.poi_id sale.poi_idand replace(result.sale_date,_,) sale.sale_datewhereresult.sale_date between $$begindate and $$enddateand result.update_status 0and result.update_type 2and sale.sale_num result.origin_max_saleOrder byresult.operate_timeDescLimit5000000)
) as t
Inner join(selectsnap.total_stock as total_stock, snap.base_sku_id as base_sku_id, snap.poi_id as poi_id,snap.schedule_date as schedule_datefromsale_log as resultLeft joinschedule as snaponresult.sku_id snap.base_sku_idand result.poi_id sanp.poi_idand result.sale_date snap.schedule_dateand snap.dt replace(snap.schedule_date,-,)whereresult.sale_date between $$begindate and $$enddate
) as mOnt.商品SKUID m.base_sku_idand t.网店ID m.poi_idand t.销售日期 m.schedule_date
wheret.网店ID in($poiId)注意
引号 和 的区别order by和union一起使用必须使用()包括查询语句小表驱动
12. 慢查询检测
12.1 常用命令
1、查看最近执行的sql语句读取了几页数据
SHOW STATUS LIKE last_query_cost;两条sql语句sql1between and从100条页中读取的数据sql2从1000页中读取的数据数据页查了一个数量级但是查询耗时基本一样。原因就是顺序I/O
2、是否开启慢查询日志
SHOW variables LIKE %slow_query_log;默认是off的因为开启ON时可能会影响性能当需要慢查询分析时可以开启慢查询日志
3、开启慢查询日志
set global slow_query_log on;4、慢查询时间为10s
show variables like %long_query_time%;5、慢查询时间定义为0.1s set global long_query_time 0.1;#全局set long_query_time 0.1;#session级别2条语句都要执行否则只执行global只会对新增的表查询生效6、慢查询日志
名称主机名-slow.log
查看日志地址
show variables like %slow_query_log_file;//opt/tmp/mysql.slow7、慢查询定义
当一条sql的执行时间超过了设定的long_query_time时间 扫描的记录数数据条数 min_examined_row_limit
show variables like %min_examined_row_limit; // 默认为0,表示扫描过的最小记录数这里加入将min_examined_row_limit值设置为10w即使sql执行时间 慢查询定义0.1s但是sql扫描的记录数不足10w那这条sql也不算慢查询
12.2 mysqldumpslow工具
分析步骤
1、找到slow.sql文件地址
2、常用查询
具体参数如下
-a: 不将数字抽象成N字符串抽象成S
-s: 是表示按照何种方式排序
c: 访问次数l: 锁定时间r: 返回记录t: 查询时间al:平均锁定时间ar:平均返回记录数at:平均查询时间 默认方式ac:平均查询次数
-t: 即为返回前面多少条的数据
-g: 后边搭配一个正则匹配模式大小写不敏感的
#得到返回记录集最多的10个SQL
mysqldumpslow -a -s r -t 10 /var/lib/mysql/slow.log#得到访问次数最多的10个SQL
mysqldumpslow -a -s c -t 10 地址#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -a -s t -t 10 -g left join 地址#另外建议在使用这些命令时结合 | 和more 使用 否则有可能出现爆屏情况
mysqldumpslow -a -s r -t 10 地址 | more13. Explain
13.1 table
select a.id, b.name from a left join b on a.id b.id where a.date 2024-01-23;table查询语句中涉及几张表explain语句就会生产几行。 对于join操作会生成多行explain解析结果第一行table关键字对应的表名是驱动表a第二行table对应的表名是被驱动表b id同一条查询语句中id值大的先执行
13.2 Type连接类型
all index rang index_merge ref eq_ref const system
const使用主键或唯一索引进行等值匹配
select * from table where id 1;eq_ref
join查询on对应的驱动表table2通过主键、唯一索引、或者二级索引的等值匹配
select * from table1 inner join table2 on table2.id table1.id;//或table2.age 18二级索引等值匹配ref
使用二级索引列进行常量等值匹配查询
联合索引name_age
select * from table where name m and age 10;index_merge 索引1 name 索引2 remark
select * from table where name mjp or remard sorry;如果or前后字段有不是索引的字段则type直接变为all全表扫描
rang
使用到了范围查找 between and like in or等
index
联合索引name_address_age
select id,name,address,age from table where age 18;//key使用了name_address_agekey使用到了name_address_age联合索引且查询的列被联合索引覆盖。
但没有通过此索引进行过滤数据需要扫描此联合索引的全部数据本质属于“全表”扫描
all
全表扫描
联合索引name_address_age
select id,name,age,sex from table where age 18;
等价
select * from table where age 18;全表扫描且查询返回大量的数据几十万条数据并不会把MySQL的内存打爆但是会打爆Java内存
因为MySQL查询数据后把数据返回给客户端流程如下
1获取一行数据写到net_buffer默认16k由net_buffer_length决定中
2再次获取一行数据写到net_buffer中当写满时调用网络接口发送出去
3发送成功后清空net_buffer然后重复1、2步骤
13.3 rows
理论上rows值越小越好。表示此次查询总共扫描了多少行是一个预估值
查询用到了唯一索引rows 1
其他二级索引只要回主键索引取一次数据系统就会对row row 1。
13.4 extra using index 覆盖索引查询 使用到了二级覆盖索引查询且索引内容覆盖了要获取的字段 联合索引name_age select id, name, age from table where name mjp;加上order by id则降为using where using index using index condition 索引下推查询参考上文-索引下推减少回表次数 联合索引name_age select * from table where name like 张% and age 10;加上order by id则降为using index conditionusing filesort using where using index 使用索引访问数据并达到索引覆盖但是 where 子句中有属于索引一部分(可以是联合索引第一个字段或第二个字段)但无法使用索引的条件比如 like ‘%abc’ 左侧字符不确定时 联合索引 name_age_address select id, name, age from table where name like m% and age 10;
select id, name, age from table where age 10;
select id, name, age from table where age 10;加上order by id则降为using where using where 场景1使用全表扫描type all 联合索引 name_address_age select * from table where address 铁岭;
select * from table where address like 铁岭%;加上order by id仍为using where 场景2type ref 联合索引name_age select * from table where name m and age 10 and valid 1;查询条件中有除了索引包含的列之外的其它列查询 思考我们设计表的时是否需要valid字段1表示有效、0表示无效用于逻辑删除。这样的话查询语句都要加上where xxx and valid 1 using filesort order by、group by时无法使用到联合索引。只能在内存中排序 using temporary 场景1order by、group by时无法使用到联合索引。而且内存大小不够排序需要io创建临时文件用于排序 场景2 select distinct(name) from t where id 1;name字段非索引字段则需要使用临时表在内存中去重
性能分析
using index null using index condition using index; using where using where
order by| group by排序时using filesort using temporary。其他性能同上
13.5 filtered
1、单表查询 查询条件过滤数据的百分比越接近100越好。 结合rows一起理解。rows值为999表示预估扫描行数999filtered 100表示rows扫描的999行全部都是我们需要的。说明索引的过滤性很好
如果rows 5000filtered 50说明扫描了5000行最终符合的数据只有2500行说明查询条件的过滤性不好白白的扫描了页中的数据
单表查询的时候filtered的指标意义不大
2、join
filtered指标显示被驱动表要执行的次数
select * from t1 join t2 on t2.key t1.key where t1.name mjp;explain执行结果
tablerowsfilteredt1900010t21100
外层驱动表t1大概需要扫描的行数9000行大概10%满足t1.name ‘mjp’也就是900条数据内存被驱动表t2rows 1 filtered 100则t2大概需要执行900次的t2.key t1.key
13.6 key
真正使用到的索引
强制走索引语句
select * from t force index(idx_name) where xxx;13.7 key_length
索引的长度
这个字段可以确认联合索引是否所有字段都被用上查询了还是只是用到了最左部分。比如联合索引name_age_address是全部用上了还是只用到了name联合索引中的字段被使用的越多即key_length越大越好这样能更好的使用联合索引进行数据过滤
eg:
select * from t where name mjp and address cn;不满足最左前缀匹配原则即使查询用到了name_age_address索引但是从ken_length结果可以看到只用到了索引的最前部分name字段
总结
eg: 联合索引a_b_c
where a m and b j and cp
优于
where a m and b j //等效where a m and b like j% and c p;
优于
where a m //等效 where a m and c p;计算规则如下
字符串varchar(n)对应utf8mb4编码则长度为4n2字节如果字段允许为null则需要再1字节索引字段最好not null数值类型 tinyint1int4bigint8
13.8 ref
结合type理解当索引列使用等值查询时ref内容即与索引列进行等值匹配的对象的信息
1、如果type类型是ref
select * from table where id 1;则ref是const表示与索引列(这里是id主键索引)进行等值匹配的对象信息这里与id进行等值匹配的对象是1是个常量const
2、场景2
联合索引name_address
select * from table where name m and address 铁岭;则ref指标会显示: const,const与联合索引进行等值匹配的对象是2个常量
3、场景3
select * from table1 inner join table2 on table2.id table1.id;这里type是eq_refref是table1.id表示与索引列table2.id进行等值查询的对象的信息为table1.id
4、场景4
select * from table where id abs(18);ref: func表示与索引列id进行等值匹配的对象信息是个函数
13.9 格式
正常情况下的explain是普通的展示还是使用explain FORMAT JSON语句json格式的展示会多出2个指标 本次查询读取的数据量大小 data_read_per_join成本cost read_cost IO成本 rows*(1-filtered)条记录的cpu成本eval_cost: 检测rows * filtered条记录的成本
13.10 优化器优化后的语句
背景
where id in(1)优化器帮我们会优化成where id 1驱动表和被驱动表的顺序优化器也会帮我们优化联合索引a_b_cwhere a x and c x and b x 优化为where a x and b x and c x
那么我们怎么查看真正执行的sql语句是啥样子的呢步骤如下 步骤一explain select * from t where id in(1) 步骤二: SHOW WARNINGS message中会有上述sql真正的执行内容
13.11 监控分析视图-sys schema
类似dump文件一定不要在业务高峰期执行收集信息时会影响性能影响业务
索引相关
#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
// eg有了联合索引name_age又创建了name单值索引属于冗余#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schemadbname ;表相关
# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requestsio_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;# 2. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where dbdbname;语句相关
#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;#2. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables0 or tmp_disk_tables 0
order by (tmp_tablestmp_disk_tables) desc;锁
#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;13.12 mybatis自定义Explain插件
mybatis自定义Explain拦截
三、事务
1. 事务定义
事务就是要保证事物内的一组 操作单元CRUD要么全部成功要么全部失败
2. 事务特性ACID
1、Atomicity原子性-全部提交成功/全部失败回滚
用户A给用户B转 50块要么就是转成功A-50、B50要么就是转失败二者账户都不增不减。
2、Consistency一致性-数据从一个合法状态 变换到另外一个合法状态
用户A有200块现在给用户B转300块200-300 -100显然-100不是一个合法状态不满足一致性A给B转钱A-的必须和B的一样总额不能变
3、Isolation隔离性-隔离级别决定
4、Durability持久性-一旦事物提交则修改会永久保存到数据库
通过事务日志重做日志redo log和回滚日志undo log来保障
总结
A是基础、C是约束条件、I是手段、D是目的
3. 事务状态
部分提交内存更新了但未刷盘提交持久化
4. 隔离性和隔离级别
事务并发时各个事务之间不能互相干扰类似于多线程并发操作共享数据多线程可以通过加锁解决并发事务可以通过隔离级别解决
总述如图2.17
4.1 读未提交
含义一个事务还没提交时它做的变更就能被别的事务看到
问题读未提交级别下可能会有脏读、不可重复读、幻读。这里简单介绍下脏读
读取到一条不存在的数据读取到的数据别人回滚了
解决读已提交
4.2 读已提交
含义一个事务开始时只能读到已经提交的事务所做的修改。即一个事务从开始到提交所做的任何修改对其他事务都是不可见的
问题可能会有不可重复读、幻读这里简单介绍下不可重复读
两次执行相同的sql查询可能得到不一样的结果如图2.9 不可重复读的重点是修改!!!update操作
解决可重复读
4.3 可重复读RR【mysql的隔离级别】
含义 在一个事务中从开始到结束的任意一瞬间读取到的数据应该都是一致的。
存在问题幻读如图2.10幻读 事务A第一次查询db没有id 5的数据。事务B插入了一条id 5的数据事务A第二次查询db id 5发现有5这条数据了。像是幻觉一样 幻读的重点在于新增或者删除操作
解决 可串行化-不推荐 快照读普通读中的幻读使用的MVCC解决的 当前读select for update中的幻读采用next-key lock解决 select * from table where id 5 for update;
假如id在13,6有值在可重读读隔离级别下select for update会对数据加锁 如果id 5值不存在则从record lock 降级为next-lock间隙锁锁住范围(1,6)这样就确保id5无法插入即图中步骤3无法执行。这样两次读取到的数据一样不会出现幻读
4.4 可串行化【最安全】
含义在读取的每一行数据都加锁。这样就不会出现第一次和第二次读中间事务B插入一条数据了。因为id 5这行被加锁了
问题大量的超时和锁竞争【几乎不用】
场景除非非常需要数据一致性 且 没有并发
5. 事务使用SOP
5.1 不要使用长事务的原因
长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据所以这个事务提交之前数据库里面它可能用到的回滚记录都必须保留这就会导致大量占用存储空间。
除了对回滚段的影响长事务还占用锁资源也可能拖垮整个库场景
delete大量数据一次会占满整个事务日志锁住很多数据会阻塞重要的查询。
建议在业务低峰期批量且有时间间隔的删除1w、1w这种方式
5.2 事务的生效范围 Transactional(rollbackFor Exception.class)public void main() {//1. insert A//2. insert B 、Ctry {insertBC();} catch (Exception e) {}//3.其他业务逻辑 }Transactional(rollbackFor Exception.class, propagation Propagation.NESTED)public void insertBC() {// insert b//insert c}
B和C任意一个失败B、C都会一起失败B、C失败不会影响A的成功因为try-catch住了3中其他业务逻辑失败会抛出Exception会导致整个main方法回滚则A、B、C都会一起失败回滚事务
注意点
main方法必须加上事务注解否则会因为在方法中a中调用方法baop会失效。try-catch要加上吃掉异常
5.3 采坑记录
5.3.1 本地写 rpc写
1、结论
本地写事务和rpc写操作最好不要放在一个事物中
2、原因
因为若本地写事务成功了rpc写操作ReadTimeout执行失败实际上此rpc写已经在远程成功了会导致本地写操作回滚。
造成本地未写远程写成功了
3、特殊
非强一致性
如果本地写和rpc写不要求那么强的一致性而且rpc写失败了又可以重拾幂等。那么允许放在一起
Job触发有重试机制
在保证幂等的情况下job执行失败会有重试。同样能达到重试的机制
4、解决
背景就要本地写rpc写而且要保证事务一致性方案最大努力重试
Transaction{// 1、本地写// 2、使用最大努力重试机制保证rpc或者多个rpc一定成功最大努力重试{ rpc1rpc2}
}
5.3.2 Transactional使用注意事项
1、踩坑-Methods should not call same-class methods with incompatible “Transactional” values问题
方法和调用事务的方法在同一个类中 问题描述 同一个类中无事务方法a()内部调用有事务方法b()b方法上的事务不会生效 public class Demo{public void funcA(){funcB();}Transactional(rollbackFor Exception.class)public void funcB() {}
} 解决 方式1不推荐 ((类名) AopContext.currentProxy()).funcB()Transactional(rollbackFor Exception.class)
public void processBill() {}方式2推荐 public class Demo{Transactional(rollbackFor Exception.class)public void funcA(){funcB();}Transactional(rollbackFor Exception.class)public void funcB() {}
}
3、其他Transactional不生效的场景
Transactional 只能被应用到public方法上否则事务不会生效AOP原理在具体的类或类的方法上使用 Transactional 注解而不要使用在类所要实现的任何接口上
5.3.3 多数据源问题 问题描述 NoUniqueBeanDefinitionException: No qualifying bean of type ‘org.springframework.transaction.PlatformTransactionManager’ available: expected single matching bean but found 2: default 原因 多数据源的时某一数据源未配置事务name导致事务失效 第一个数据源事务默认名称为default第二个数据源事务名称为sale_smart 当第一个数据源使用事务但是没有指定事务处理器就会报这个错误。 因为每个数据源都有自己的事务配置单纯地用Transactional 没法确定是哪个事务处理 解决
在多数据源配置文件中指定name
数据源1 transactionName sale_smart
数据源2 transactionName other
在使用注解时指定对应的数据源名称
Transactional(value sale_smart, rollbackFor Exception.class)
事务1方法Transactional(value other, rollbackFor Exception.class)
事务2方法6. 事务日志
6.1 一个事务中2条更新语句的执行过程
update table set name mjp where id 2;
update table set name wxx where id 3;如图2.22 1将id 2数据从磁盘中读取到内存放入data_buffer中 准备更新内存数据前先将id2的旧值写入undo log中便于回滚 将data_buffer中的id 2数据进行值修改 将id2的更改动作实时记录到内存的redo log buffer中
2将id 3的更新语句执行步骤1
3当这2条更新语句对应的事务提交commit时将redo log buffer中的更新记录会按照一定的策略写到磁盘中的redo log file中 再进行半异步方式-主从复制bin log 和 中继日志参考后文主从复制过程 主从复制中继日志写成功后发动ACK主库确认ACk后返回客户端事务处理成功
4再将内存data_buffer中的更新结果以一定的时间间隔频率刷盘data中
6.2 redo log
InnoDB引擎特有
内容物理日志记录的是“在某个数据页上做了什么DML操作”作用保障事务的持久性。即如何将更新的数据从内存中可靠的刷到磁盘中区别一个事务中10条更新语句redo log是不间断的顺序记录的特点循环写空间固定会用完会覆盖。固定大小的比如可以配置为一组4个文件每个文件的大小是1GB从头开始写写到末尾就又回到开头循环写。write pos是当前记录的位置一边写一边后移写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置也是往后推移并且循环的擦除记录前要把记录更新到数据文件因为随着内存data中数据刷盘完成刷盘部分的数据就没必要再存在于redo log中了就可以被覆盖了。
循环写过程如图2.42 其中checkpoint - write pos范围内的数据是内存data_buffer还未完成刷盘的数据redo log必须要存好
write pos - checkpoint范围内的数据是内存data_buffer已完成刷盘的数据redo log就没必要存储了。这部分就可以被循环写
6.2.1 组成
redo log buffer 保存在内存中默认大小16M又可以分为一个一个512kb的redo log block redo log file 保存在磁盘中的是持久化的在文件ib_logfile0 和 ib_logfile1中单个默认大小48M
6.2.2 redo log刷盘操作
含义
事务提交时将内存中的redo log buffer的更新记录以一定的频率写到磁盘中的redo log file中这个动作执行成功才能真正的保证事务的持久性即使是服务器宕机了重启后也能从redo log file中读取到磁盘data中如果事务没提交系统挂了内存中的redo log buffer的更新记录没了也没关系。因为事务本身也没提交仍是事务的最初状态。一致性也没改变
过程
1、先将内存中的redo log buffe刷到文件系统缓存page cacahe中
2、至于什么时候将page cache中内容写入redo log file中完全交于操作系统自己决定
3、Innodb提供了参数innodb_flush_log_at_trx_commit可以调整redo log刷盘的策略
设置为1 默认值 表示每次事务提交时都会立即将redo log buffer内容记录到page cache中然后再立即将page cache中数据写到入redo log file。实时性很好可靠性高效率性差设置为0 表示每次事务提交时不直接进行刷盘操作值1是事务提交时会立即触发Innodb后台线程每间隔1s不管你有没有事务提交commit动作都会执行redo log buffer -- page cache -- 然后调用fysnc刷到 redo log file设置为2 表示每次事务提交时会立即把 redo log buffer 内容写入 page cache。由os自己决定什么时候从page cache -- redo log filepage cache是os系统级别的所以事务提交后 page cache写成功后MySQL数据库服务器宕机了没关系。重启MySQL数据库后可以从系统os的 page cache中读取数据但是如果os操作系统挂了那就无法刷盘了所以如果os系统基本不可能宕机的情况下想优化事务comit时间3w个事务提交动作默认1花费的时间是值2的3倍大概可以采取值为2的方式
6.2.3 两阶段提交最终版
提示两阶段提交过程是写redo log、写bin log与事务commit提交动作先后顺序关系的最终解释版
1、背景
redo log是边更新边写入的。bin log是事务提交时一把写入的当事务提交时假如先刷盘的redo log成功了然后服务器宕机了bin log刷盘失败了则重启服务器时 master会根据redo log进行事务持久性恢复恢复到更新后的值slaver需要根据bin log进行主从同步但是bin log没有本次更细的记录导致主从数据不一致了
2、解决redo log两阶段提交
两个阶段如图2.50 3、两阶段如何解决上述主从数据不一致问题
如图2.51
重启服务器时
因为写bin log失败了所以还没走到redo log的commit阶段判断是否存在bin log显然不存在则回滚事务此时redo log也回滚了bin log也没写入主从的数据还是一致的
四、锁
1. 数据操作类型
1.1 共享锁
1、定义
对于同一份数据多个事务之间可以并发读取相互之间不阻塞
2、加锁
select * from t whrer id 1 lock in share mode;即事务1为上述sql、事务2也为上述sql二者都加的共享锁则读-读之间互不影响
3、生效范围
表、行
1.2 排他锁
1、定义
当前写操作没有完成之间会阻塞其他的写、读操作。
确保只有一个事务可以写其它事务不能写并且其它用户不能读取正在写的同一资源
2、加锁
这里演示的是对读加排他锁所以共享锁不能简单概括为读锁因为读也可以加排他锁
select * from t whrer id 1 for update;即事务1为上述sql事务2也为上述sql或事务2为select * from t whrer id 1 lock in share mode。事务1未提交之前事务2都会被阻塞
对写加排他锁 delete、update直接加排他锁insert正常情况下插入操作并不加排他锁而是通过隐式锁保驾护航确保插入的数据未提交之前不能被其他事务访问隐式锁类似于懒加载只有别的事务企图来共享|排他访问新增但还未提交的这条数据时才会加上隐式锁
3、生效范围
表、行
2. 数据操作粒度
2.1 表锁
2.1.1 优缺点
优点
资源开销小无需大量的获取、检查、释放锁不会产生死锁
缺点
并发度差
行锁优缺点相反
2.1.2 自增锁Auto Icr
1、定义
当我们主键id auto increment时而且我们batchInsert时或insert的内容是select的结果时我们不知道要插入多少条数据这个时候一个事务持有表级锁-自增锁时其他事务无法执行插入操作
2、特殊
我们普通的mybatis的单条数据的insert知道要插入数据的条数。
只需使用metux轻量级锁在分配id值期间保持即可无需像上述批量插入需要保持自增锁到插入语句结束为止
2.1.3 元数据锁MDL
场景
CRUD时对表加MDL读锁Alter table时加MDL写锁更改字段属性、表会锁全表。读-写、写-读、写-写都是阻塞的
总结
Alter table操作导致的MDL-写锁必须要在业务低峰期进行否则可能阻塞CRUD-MDL读锁
原因如图2.8 session A先启动这时候会对表t加一个MDL读锁【语句结束后并不会马上释放】。由于session B需要的也是MDL读锁因此可以正常执行session C会被blocked是因为session A的MDL读锁还没有释放而session C需要MDL写锁因此只能被阻塞之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。等于这个表现在完全不可读写了如果某个表上的查询语句频繁而且客户端有重试机制也就是说超时后会再起一个新session再请求的话这个库的线程很快就会爆满。故Alter操作一定要在业务低峰期防止大量的读被阻塞
2.2 行锁
2.2.1 快照读和当前读
快照读即普通读不加锁读取的快照数据
select * from table where id 5;当前读读取最新的数据需要加锁读取的不是快照
select * from table where id 5 for update;如果查询条件字段不是索引字段会降级为表锁
2.2.2 加行锁SOP
2.2.3 影响并发度的锁放在事务最后
1、背景
顾客A要在影院B购买电影票
从顾客A账户余额中扣除电影票价给影院B的账户余额增加这张电影票价记录一条交易日志。
2、解析 这个交易需要update两条记录并insert一条记录 为了保证交易的原子性把这三个操作放在一个事务中 如果同时有另外一个顾客C要在影院B买票那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额需要修改同一行数据。
3、建议
把语句2安排在最后按照3、1、2这样的顺序那么影院账户余额这一行的锁时间就最少最大程度地减少了事务之间的锁等待提升了并发度
4、总结
如果你的事务中需要锁多个行在不影响业务的情况下把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
2.2.4 行锁变表锁场景
一旦某个加锁操作没有使用到索引则该锁就会退化为表锁
2.2.5 Record lock记录锁
场景1排他锁-读
SELECT * FROM table WHERE id 5 FOR UPDATE;//锁住id 5单行如果这里的where查询的字段是唯一键则加锁的效果等同主键updateByPrimaryKey也会对id 匹配这行仅对这一行加锁匹配在没数据时也会降为间隙锁 id 1、3、6,执行 WHERE id 5 FOR UPDATE的查询此时会降级为间隙锁锁住(1,6)所以我们需要偶尔执行一下alter table A engineInnoDB防止自增主键因为插入冲突、事回滚导致的有过多的空隙不连续问题 如果where条件不是匹配而是 like等会变成临键锁上述语句未提交时再执行共享锁-读时也会被阻塞当然再执行排他锁更是阻塞
select * from table where id 5 share in mode;场景2排他锁-写
update table set name mjp WHERE id 5 ;//锁住id 5单行此时记录锁锁住了id 5的这一行update未提交时再执行共享锁-读时也会被阻塞当然再执行排他锁更是阻塞
select * from table where id 5 share in mode;2.2.6 Gap lock间隙锁
锁定一个范围()是基于非唯一索引和非主键索引的即二级索引age是普通索引时where age 5则锁定(1,5)不包含行本身
SELECT * FROM table WHERE age 5 FOR UPDATE;
SELECT * FROM table WHERE id 1 and id 10 FOR UPDATE;间隙锁锁分析 在(110区间内的记录行都会被锁住所有id为 2、3、4、5、6、7、8、9 的数据行的插入(排他锁-写)会被阻塞排他锁-读for update、共享锁-读均是但是 1 和 10 两条记录行并不会被锁住。
补充
1、当前读情况下可以通过加此锁解决可重复的读时的幻读问题
2、间隙锁不区分共享间隙锁和排他间隙锁即以下两种查询会加间隙锁锁住(1,10)
SELECT * FROM table WHERE age 5 FOR UPDATE;
SELECT * FROM table WHERE age 5 share in mode;3、 如果是between 1 and 10则会锁住[1,10]。同理如果是id 1 and id 10也是锁定[1,10]
2.2.7 next-key lock临键锁 本质Record lock Gap lock 每个数据行上的非唯一索引字段列上都会存在一把临键锁 如图2.11 假设age为索引字段则改表中age列上潜在的临键锁锁定的范围有
(010]
(10,24]
(24,32]
(32,45]
(45,无穷]
则
SELECT * FROM table WHERE age 24 FOR UPDATE;锁定的范围是左gap lock record lock 右gap lock 》 (10,24) 24 (24,32) (10,32) 作用解决事务隔离级别为可重复读时当前读for update可能存在的幻读问题 举例事务A在写数据id24时会先查db有无id24这条数据有则不插入。无则插入 事务A第一次查询select where id 24 for update间隙锁会锁住(10,32)事务B打算插入了一条id 24的数据发现(0,32)范围被锁住了阻塞事务A第二次查询db id 24因为可重复读所以第二次读取的结果和第一次一样没有出现幻读事务A按照自己的逻辑认为可以插入id24的一条数据
2.2.8加锁2原则2优化1bug
原则1
加锁的基本单位是next-key lock ,锁定范围( ]
原则2
查找过程中访问到的对象才会加锁
如果age不是索引字段则查询where age 5仅访问age5这一条记录是不能马上停下来的需要向右遍历查到age6才放弃因为age是二级索引不是唯一索引所以需要再往下继续查找直到不满足查询条件为止根据原则2访问到的都要加锁故where age 5的查询也会对age6这行数据加锁(1,6]聚簇索引即主键where id5就可以立即停下来(前提是id5这一行有数据唯一索引字段查询同理)不会访问id6
优化1
索引上的等值查询where id 5给唯一索引加锁的时候id字段next-key lock退化为record lock只锁id5这一行的数据(前提是id5这一行有数据)。
优化2
索引上的等值查询where age 5为二级索引向右遍历时且最后一个值不满足等值条件的时候age 6next-key lock退化为间隙锁Gap lock即锁定范围不是(1,6]还是间隙锁(1,6)
一个“bug”
唯一索引上的范围查询id7这一行无数据会访问到不满足条件的第一个值为止。
CREATE TABLE table (id int(11) NOT NULL, c int(11) DEFAULT NULL, d int(11) DEFAULT NULL,PRIMARY KEY (id), KEY c (c)
) ENGINEInnoDB; insert into table values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);1、等值查询从record lock降级为间隙锁场景
SELECT * FROM table WHERE id 7 FOR UPDATE;//没有id7的数据原则1加锁范围(5,7]优化2这是一个等值查询(id7)而id10不满足查询条件next-key lock退化成间隙锁因此最终加锁的范围是(5,10)。所以session B要往这个间隙里面插入id8的记录会被锁住但是session C修改id10这行是可以的。
2、据此再分析图2.11的sql
SELECT * FROM table WHERE age 24 FOR UPDATE;原则1临键锁024]优化2:这是一个等值查询(age24)会向右查找直到找到第一个不满足的age 32此时临键锁退化为间隙锁2432] - (24,32)锁定范围是032
2.3 乐观悲观锁
2.3.1 悲观锁
认为在更细数据时总有别的线程会更新我们这份数据所以会加锁如表锁、行锁、Java锁关键字
2.3.2 乐观锁
不加锁通过代码层面如CASversion版本号的方式解决潜在的资源可能别修改问题。适用于读操作多的场景
其中版本号操作如下
先查询出来数据的版本号version 10此时对这条数据进行更新操作update t set name ‘mjp’, version version 1 where version 10如果此期间没有别的事务对这条数据进行update则这条语句能更新成功。如果别的事务更新了这条语句n那么version值变为11了则此条更新语句不会成功注意使用version版本号乐观锁的前提是主从没延时否则读到的version可能不是最新的。所以读取的时候一定要走主
2.4 死锁
2.4.1 产生死锁的条件
进程A和进程B举例
互斥
在一段时间内资源被A占用。若此时B来请求此资源那么B只能等待
不可剥夺
A获到的资源在其未使用结束前B不能强行夺走此资源。B只能等待A主动释放
请求与保持
B提出资源获取请求但是此资源正在被A占有此时B被阻塞
B被阻塞的过程中B占有的资源也保持持有不释放
即阻塞申请资源的同时也不释放自己占有的资源
循环等待
A持有a资源B持有b资源A访问b资源B访问a资源。形成一个循环等待
2.4.2 避免死锁的方式
即避免产生死锁的四个条件
避免循环加锁的时候可以先对对象排序防止循环避免阻塞获取资源的时候不要一直阻塞获取尽量使用tryLock(timeout)一段时间内没获取到则不再获取这样自己占有的资源也可以被释放。
2.4.3 发生死锁时的策略
当发生死锁时会主动检测将死锁链条中持有最少行排他锁的事务进行回滚。这样便打破了死锁产生的条件让其他事务得以继续执行
2.4.4 死锁实战
间隙锁导致死锁 背景Mybatis的upsert方法 插入数据时本质为
insert into xxx on duplicate key update;原因假设code字段是唯一索引
1初始化数据 insert into table (code, other) values(1,1),(3,3),(5,5);2事务1 insert into table (code, other) values(3,3) on duplicate key update由于33这条数据已经存在所以会在(1,3)范围内加间隙锁
3事务2
insert into table (code, other) values(5,5) on duplicate key update由于55这条数据已经存在所以会在(3,5范围内加间隙锁
4产生死锁
如果此时事务1要插入一条code4的数据就需要等待事务2释放(3,5的锁如果事务2要插入一条code2的数据就需要等待事务1释放(1,3的锁锁住期间其他事务不能向此范围内插入数据此时会形成死锁
总结过程如图2.44 5建议
在并发事务执行的insert语句中最好不要使用可能会造成死锁
如果要用那么inset的batch大小要小一些这样单个事务获取的next-key范围就会变小减少死锁发生的概率
锁商品时遇到的死锁
背景
下单操作需要锁定订单汇总多个商品的库存手机、鞋子、衣服
步骤
先拿到所有商品的锁
依次进行扣减库存
释放锁
注意
为了避免死锁可以在对商品加锁的时候可以通过对购物车中的商品先进行排序来实现顺序的加锁。这样就能有效避免产生死锁的循环等待这一条件
实现
public void createOrder(List orderList) {//定义存放锁的集合List lockList Lists.newArrayList();// 1、对所有购物车中待下单的商品进行排序Collections.sort(orderList);// 2、对排序好的商品进行依次加锁try {for (Object order : orderList) {if (order.lock.tryLock(10, TimeUnit.SECONDS)) {//加锁成功lockList.add(order.lock);}}//3、依次扣减库存orderList.forEach(order - order.remaining--);} catch (Exception e) {} finally {lockList.forEach(ReentrantLock::unlock);}}3. 锁结构
3.1 产生一条锁结构
同一个事务中进行加锁 被加锁的记录在同一页 加的相同类型的锁 等待状态是一样的。则为这个事务生成一个锁结果否则一个事务中1000个更新语句生成1000个锁结构性能太差了
3.2 锁结构
锁结构如图2.45 1、锁所在的事务信息
不论是表锁还是行锁哪个事务生成了这个锁结构 这里就记录这个事务的id。
2、 索引信息
对于行锁来说需要记录一下加锁的记录是属于哪个索引的
3、表锁信息
对哪个表加的锁
4、行锁信息
Space ID 记录所在表空间Page Number 记录所在页号。
5、type_mode
32位的数被分成了 lock_mode 、 lock_type 和 rec_lock_type 三部分如图2.46 1锁的模式 lock_mode
占用低4位值如下 LOCK_IS 十进制的 0 表示共享意向锁也就是 IS锁LOCK_IX 十进制的 1 表示独占意向锁也就是 IX锁LOCK_S 十进制的 2 表示共享锁也就是 S锁LOCK_X 十进制的 3 表示独占锁也就是 X锁LOCK_AUTO_INC 十进制的 4 表示 AUTO-INC锁 。
2锁的类型 lock_type
占用第58位LOCK_TABLE 十进制的 16 也就是当第5个比特位置为1时表示表级锁LOCK_REC 十进制的 32 也就是当第6个比特位置为1时表示行级锁
3行锁的具体类型 rec_lock_type
LOCK_ORDINARY 十进制的 0 表示 next-key临建锁LOCK_GAP 十进制的 512 也就是当第10个比特位置为1时表示 gap间隙锁LOCK_REC_NOT_GAP 十进制的 1024 也就是当第11个比特位置为1时表示record lock记录锁LOCK_INSERT_INTENTION 十进制的 2048 也就是当第12个比特位置为1时表示插入意向锁is_waiting type_mode 这个32 位的数字中当第9个比特位置为 1 时表示 is_waiting true 即当前事务尚未获取到锁处在等待状态当这个比特位为 0 时表示 is_waiting false 即当前事务获取锁成功。
6、一堆比特位
如果是行锁结构 的话在该结构末尾还放置了一堆比特位作用因为上述事务中的多个更新在一个页中用于记录这个页中的哪些数据被加了行锁哪些数据没有加锁
4. 锁监控
4.1 查看行锁的竞争信息
show status like innodb_row_lock%;Innodb_row_lock_waits 系统启动后到现在总共等待的次数Innodb_row_lock_time_avg 每次等待所花平均时间
如果这两个参数值比较大即等待次数较多且等待的时间avg较大则有可能是事务语句可能有问题
4.2 查询锁信息
查询正在被锁阻塞的sql语句
SELECT * FROM information_schema.INNODB_LOCKS;指标1trx_query等待锁的sql语句
指标2trx_rows_locked锁定的行数
查询目前处于等待锁的事务
SELECT * FROM information_schema.INNODB_LOCK_WAITS;指标1 REQUESTING_ENGINE_TRANSACTION_ID: 13845 #被阻塞的事务ID
指标2BLOCKING_ENGINE_TRANSACTION_ID: 13844 #正在执行的事务ID阻塞了13845
5. MVCC多版本并发控制
5.1 作用
在数据库并发场景中只有读-读之间的操作才可以并发执行读-写**写-读**写-写操作都要阻塞这样就会导致 MySQL 的并发性能极差。采用了 MVCC 机制后只有写写之间相互阻塞其他三种读读、写-读、读-写操作都可以并行读使用Mvcc写加锁这样就可以提高了 MySQL 的并发性能。多事务并发时MySQL通过 隐藏字段组成的undo log版本链 undo log ReadView解决各种问题
5.2 组成
5.2.1 隐藏字段
每行数据有两个隐藏的字段trx_id、roll_pointer trx_id 就是最近一次更新这条数据的事务id它是在事务开始的时候向InnoDB的事务系统申请的是按申请顺序严格递增的。 roll_pointer指向了你更新这个事务之前生成的链。隐藏字段组成的undo log版本链如图2.12 5.2.2 uodo log回滚日志
1、概念
更新数据时先将数据从磁盘读取到内存的data_buffer中更新data_buffer中对应的数据之前会先将旧值拷贝到undo log里insert之前记录主键id便于delete、delete之前需要记录全部要被删除的内容便于后续insert和redo log一样是Innodb的并且undo log的产生过程也需要redo log保护它持久性用于数据可恢复
2、特点
是逻辑上的回滚不是物流上的回滚
加入insert了一条语句开辟了新的页然后插入了一条数据id100事务回滚了并不能物理上将开辟的页再收回因为可能别的事务也insert了语句也放到了这一页中只能执行delete语句将id 100的数据再删除eg用户A转账了100给用户B事务需要回滚并不是物理上让A和B都回到未转账之前的状态事情发生了就无法倒流是能通过逻辑上的弥补即用户B再转账100给用户A达到回到最初的状态。物理操作发生的事情再让它不发生即类似时光可以倒流逻辑操作发生的事情就只能让它发生了唯一能做的就是想办法弥补让状态回到事情未发生时候的样子吵架了伤害已经实打实的发生了无法倒流只能通过好好沟通弥补感情回到之前的状态0.o
3、作用
事务回滚保证事务原子性和一致性可以用undo log的数据进行恢复Mvcc当用户读取一行记录时若该记录被其他事务占用则该事务可以通过undo log读取之前的行版本信息以此实现非锁定读取即读-写的读是不加锁的读。
4、存储结构
类似redo log的block块undo log是段即回滚段rollback segement。每个回滚段中包含1024个undo log segement这里面有undo页一个undo log页中可能有多个事务用于记录待恢复的内容并发度Innodb支持128个段可通过参数innodb_undo_logs设置每个段1024个undo log segement所以支持事务的并发度为128*1024
5、回滚段rollback segement中数据类型分类
未来提交的
随时可能作为回滚的恢复内容所以不能被其他事务的数据覆盖 已提交 未过期 mvcc作用可能有其他事务需要undo log内容来读取之前的行版本信息。所以不能被立即删除由purge线程来决定什么时候删除 已过期 超过了undo retention参数指定的时间属于过期数据当回滚段满了之后会优先覆盖这部分的数据
6、undo 类型
insert
commit时可直接删除 update 事务在进行 update 或 delete 时产生的 undo log 不仅在事务回滚时需要作用1在Mvcc机制中作用2也需要。所以不能随便删除等待 purge 线程统一清除。 所以delete语句不要有大事务会长时间的占满整个事务日志 长时间不能及时的删除日志 占满undo log需要记录待删除的数据全部内容内容很多
7、一条语句的更新过程redo undo log
如何2.43基本和图2.22一致 5.2.3 ReadView一致性视图
定义
帮我们解决行的可见性问题。分析如图2.13 m_ids表示在生成ReadView时当前系统中 还没有提交的事务id列表min_trx_idm_ids中的最小值max_trx_id表示生成ReadView时系统中应该分配给下一个事务的id值。当前系统里面已经创建过的事务ID最大值加1creator_trx_id表示生成该ReadView的事务的事务id读操作事务id0更新操作才会分配事务id
事务能读取到哪条数据 规则1 当前事务内的更新能读到 即被访问的trx_id 视图中的creator_trx_id说明是当前事务生成视图的事务内的更新能读到 规则2 其他事务已提交且在视图创建前提交能读到 即被访问的trx_id(其他事务) 视图中的当前事务min_trx_id表明被访问的事务在生成该视图之前就提交了(因为不是活跃事务了)所以被访问的事务可以被当前事务读到对应图中的绿色部分 规则3 其他事务已提交但在视图创建后提交不能读到 即被访问的trx_id(其他事务) 视图中的当前事务max_trx_id表明被访问的事务在生成该视图之后才开启的所以被访问的事务不能被当前事务读到 对应图中的红色部分 规则4 被访问的事务min_trx_id trx_id max_trx_id trx_id ∈m_ids集合中说明是活跃的事务还未提交不可被读取 不在m_ids集合中说明不是活跃的事务说明已提交可被读取 5.3 作用于事务隔离级别
5.3.1 MVCC保证可重复读
有两个事务并发过来执行事务A(id45事务Bid59事务A要去读取这行数据事务B要去修改这行数据事务A开启一个ReadView如图2.14 事务A第一次查询快照读即普通读非当前读这行数据时如图2.15 判断被访问的这行数据的trx_id是否小于ReadView中的min_trx_id 此时这行数据的trx_id 32小于事务A的ReadView里min_trx_id45根据规则2能读到trx_id 32的这行数据 事务B开始修改这行数据 事务B把值修改为B然后这行数据的trx_id设置为自己的id59同时roll_pointer指向了修改之前生成的版本链 log如图2.16 事务A第二次查询 被访问的事务trx_id59min trx_id max 且trx_id ∈m_ids[45,59]根据规则4读取不到 此时事务A会根据隐藏字段roll pointer顺着undo log版本链查询之前的版本于是就会查到trx_id32的数据trx_id32是小于ReadView里min_trx_id45的可以查到值仍为A 事务A第一次读 和 第二次读值都为A即可重复读。即通过MVCC实现MySQL的可重复读快照读情况下事务隔离级别
5.3.2 MVCC解决幻读
MVCC可以解决快照读下的幻读 只不过可重复读是普通读的id 1匹配查询幻读是普通读的范围查询id 1原理分析同上可重复读的隔离级别下视图使用同一个所以插入数据前后两次id 1查询结果个数一样 MVCC无法解决当前读下的幻读当前读for update是通过加间隙锁解决幻读。参考
5.3.3 可重复读、读已提交区别
它们生成ReadView的时机不同
在可重复读隔离级别下一个事务在执行过程中只有第一次执行select时会生成一个视图之后的select都会复用这个视图在读提交隔离级别下每一个select语句执行前都会重新算出一个新的视图。
五、主从复制、数据库备份与恢复
1. bin log
Server层所有引擎都可以使用
1.1 内容
binary log二进制日志。记录了所有的DDL、DML事件一个事件由begin和commit组成使用使用pos记录事件的开始和结束位置可以使用mysqlbinlog -v /bin log完整路径查看二进制内容内容是逻辑日志即伪sql
伪sql内容如图2.52
1.2 作用
主从复制、数据恢复
Redo和bin在数据恢复上的不同点
Redo log 记录数据修改操作DML当数据库发生崩溃或意外关闭时通过Redo log可以将数据库恢复到最近的一次提交点。保证事务的持久性和原子性确保了在事务提交之后所做的修改操作可以被恢复。 Bin log 记录所有的数据库更新操作包括对数据的增删改以及数据库结构的修改。DDLDML用于数据恢复、主从同步。在数据恢复方面Bin log可以用于恢复到指定的时间点或指定的事务。
1.3 特点
不同于redo log边更新边写。bin log直到整个事务提交才会一次性将10条更新语句写入不同于redo log的覆盖写bin log是追加写写到一定大小后默认1G可伸缩防止大事务无法放在同一个文件中会切换到下一个并不会覆盖以前的日志。
1.4 写入机制
同redo log一样也不是直接刷盘。而是采用默认策略sync_binlog 0
如图2.49 更新操作先写入内存bin log cache类似redo log buffer每次事务提交都只写入文件系统缓存page cache最后os决定什么时候刷盘到bin log文件中不怕MySQL服务器宕机因为写到文件系统page cache了但是怕os操作系统宕机因为事务提交了page cache内容却丢了
2. 数据恢复
因为所有DDL、DML操作事件都有记录备份在bin log中。所有可以据此进行数据恢复
执行步骤
1、flush logs生成一个新的bin log2专门记录本次数据恢复事件动作不要影响原有的正常bin log1
2、方式一通过读取bin log1中的pos位置来恢复[pos,pos]之间的事件
方式二通过读取bin log1中的指定世间段内的事件
找打对应事件的pos或时间准备恢复
3、恢复读取到的内容
方式一
/usr/bin/mysqlbinlog --start-position1 --stop-position100 --database 你的db名 /bilog的全路径 | /usr/bin/mysql -uroot -p密码 -v 你的db名;方式二
--start-datetime2024-01-01 12:00:01 --stop-datetime2024-01-02 12:00:013. 主从复制
作用读写分离、数据备份、高可用性。
1、事务提交后redo log刷盘完成后主库把更新操作先记录到bin log中状态为Prepare
2、从库将主库上的bin log复制到自己的中继日志relay log中如图2.23 从库启一个I/O线程去和主库建立客户端连接主库启一个binary down 线程该线程会读取主库上的bin log中的更新操作事件将其发送给从库如果binary down线程读取bin log中事件追上了主库写入bin log中的事件则其会进入sleep状态。直到主库bin log中又写入新的事件从库I/O线程会将接收到的bin log事件写入到中继日志relay log中
3、从库会读取其中继日志relay log内容重放执行sql存于从库数据中
如图2.24 从库中的sql线程coordinator线程会从中继日志relay log中读取事件和分发事务。worker线程去执行在从库中回放实现从库数据的更新 分发事务时更新同一行的事务会放在一个worker线程中同一个事务不会被拆开会放在一个worker线程中 sql线程支持并行的回放中继日志relay log中的事件
3.1 水平分表
1、散列hash取模 hash算法取模 可解决数据热点问题但是无法扩容和数据迁移 一旦扩容 %值变了导致% 值结果也变了原本6%2 0,去table0查询数据现在6%4 2需要去table2查询数据查不到数据 shardingsphere实战
根据order_id取模order_id%20插入表11插入表22、Range增量
按照年月日拆分便于扩容但是有数据热点问题热点查询都集中在某一张表中
3、group分组hashrange
1hash解决热点数据
如图2.393个db、10张table eg
插入id 12先确定dbid % 10 2落在了[0,1,2,3]区间内即db0再确定table: id∈[0,1000w]所以在db0的table0中
2range解决不易扩容
如图2.40 Group2的解决热点原理同group1扩容原理id - group(0-4000w是14000-8000w是2)id % 10确认dbid大小∈确认table
3组-库-表结构
如图2.41 3.2 垂直分表
按照业务分订单、库存等
4. 主从延时
4.1 表象
主库写入bin log时刻为T1从库读取完bin log时刻为T2从库sql线程完全回放完成sql时刻为T3假如T3-T1100ms即从库的seconds_behind_master值用户insert后立即select假如间隔50ms。这个时候就有可能查不到最新的数据
4.2 产生场景
网络波动大事务 delete大量数据必须业务低峰期一次性insert太多数据强制只能200批量插入大表的DDL如新增字段
4.3 解决
T2 - T1的时间几乎可以忽略现在就是如何让T3 - T2的时间更短 mysql做的sql线程支持并行的回放中继日志relay log中的事件我们做的避免大事务 兜底写完立马读的业务场景走主 写完数据发送MQ消费者会立即读新增的数据页面修改动作结束立即触发查询也可以让前端针对这种更细操作更新后延时200ms再查询
4.4 从根本上解决数据一致性
1异步复制
主commit后不 care从库的复制过程和复制结果直接返回客户端成功。
主库写效率高数据一致性最弱
2半异步复制
过程如前文图2.22
必须等从库Ack之后再返回给客户端成功有一个从库Ack即可
3组复制
MySQL5.7.17版本基于Paxos协议推出的MGR复制方案
简单理解就是一半以上的从Ack了即可返回客户端成功
5. 主从切换
1、停止向master中写入
2、让slave追上master
3、找到可以成为mater的slaver
确认当前哪个slaver的数据最新通过在slaver中选择master_log_file/reader_master_log_pos值最新的slaver成为master
4、其他slaver指向新的master开启新的master写
其他slaver如何指向master指向的reader_master_log_pos的值如何确定如图2.48
老master的insert对应的pos 1582Server2的reader_master_log_pos值最新被选为新masterServer3继续为slaver其pos 1493距离新master的1582差89新master的insert值为8167所以Server3指向新master时change_master_to_master_host “Server2”Pos 8167 - 89 8078这样就完成了新master的确认以及slaver指向新master
5、通过posslaver确认指向master的pos
Server3slaver在一个特定的偏移位置pos 8078连接到新masterServer2。一个给定的二进制未见汇总master再从给定的连接点8078开始发送所有的事件Server3需要告诉Server2从哪个偏移量Pos再次开启增量同步。如果指定位置错误的话会造成事件的遗漏数据的丢失
6. 数据库集群|db|表备份
参考附件
六、参数和命令 show index from sellout_warn_sku; 查看索引的区分度一个索引上不同的值的个数我们称之为“基数”cardinality。也就是说这个基数越大索引的区分度越好。 “采样统计”。 采样统计的时候InnoDB默认会选择N个数据页统计这些页面上的不同值得到一个平均值然后乘以这个索引的页面数就得到了这个索引的基数。 而数据表是会持续更新的索引统计信息也不会固定不变。所以当变更的数据行数超过1/M的时候会自动触发重新做一次索引统计。 在MySQL中有两种存储索引统计的方式可以通过设置参数innodb_stats_persistent的值来选择 设置为on的时候表示统计信息会持久化存储。这时默认的N是20M是10。设置为off的时候表示统计信息只存储在内存中。这时默认的N是8M是16。 force index(idx_a) 如果使用索引a每次从索引a上拿到一个值都要回到主键索引上查出整行数据这个代价优化器也要算进去的。直接在主键索引上扫描的没有额外的代价。 优化器会估算这两个选择的代价如果优化器认为直接扫描主键索引更快就不会走索引a。当然从执行时间看来这个选择并不是最优的。所以可以强制其走索引a alter table A engineInnoDB 重建表主键索引更紧凑数据页的利用率也更高。这个方案在重建表的过程中允许对表A做增删改操作 SET max_length_for_sort_data 16; 如果单行的长度超过这个值MySQL就认为单行太大要换一个算法。 long_query_time 0 捕获所有的查询。几乎没有额外的IO开销 分析工具pt-query-digest show table status like ‘表名’ : 可以查看表的基本信息 innodb_change_buffer_max_size 值表示占用buffer_size整体大小的百分比。更新频繁的表此值可以设置的大一点减少读盘次数 SHOW variables LIKE ‘log_err%’ 查询错误日志的地址默认是开启的 sql_safe_updates 执行update和delete语句必须要有where条件
写在最后
本文主要参考了书籍《高性能MySQL》、 林晓斌老师 的《MySQL实战45讲》、尚硅谷康师傅的《MySQL高级教程》以及网上资料并结合自己工作经验总结而出。理解有误的地方欢迎批评指正感谢
2024-01-31 22:00:00 mjp 文章转载自： http://www.morning.seoqun.com.gov.cn.seoqun.com http://www.morning.rhsg.cn.gov.cn.rhsg.cn http://www.morning.cwwbm.cn.gov.cn.cwwbm.cn http://www.morning.ctwwq.cn.gov.cn.ctwwq.cn http://www.morning.yjknk.cn.gov.cn.yjknk.cn http://www.morning.shangwenchao4.cn.gov.cn.shangwenchao4.cn http://www.morning.bctr.cn.gov.cn.bctr.cn http://www.morning.lrnfn.cn.gov.cn.lrnfn.cn http://www.morning.gtmdq.cn.gov.cn.gtmdq.cn http://www.morning.rgwrl.cn.gov.cn.rgwrl.cn http://www.morning.ggnkt.cn.gov.cn.ggnkt.cn http://www.morning.gybnk.cn.gov.cn.gybnk.cn http://www.morning.qzpkr.cn.gov.cn.qzpkr.cn http://www.morning.qmmfr.cn.gov.cn.qmmfr.cn http://www.morning.syynx.cn.gov.cn.syynx.cn http://www.morning.qjxxc.cn.gov.cn.qjxxc.cn http://www.morning.cwwbm.cn.gov.cn.cwwbm.cn http://www.morning.ptwqf.cn.gov.cn.ptwqf.cn http://www.morning.jqkrt.cn.gov.cn.jqkrt.cn http://www.morning.ktfnj.cn.gov.cn.ktfnj.cn http://www.morning.pzss.cn.gov.cn.pzss.cn http://www.morning.bmzxp.cn.gov.cn.bmzxp.cn http://www.morning.cfqyx.cn.gov.cn.cfqyx.cn http://www.morning.dyhlm.cn.gov.cn.dyhlm.cn http://www.morning.dodoking.cn.gov.cn.dodoking.cn http://www.morning.ygrdb.cn.gov.cn.ygrdb.cn http://www.morning.nxpqw.cn.gov.cn.nxpqw.cn http://www.morning.snjpj.cn.gov.cn.snjpj.cn http://www.morning.xdhcr.cn.gov.cn.xdhcr.cn http://www.morning.jwsrp.cn.gov.cn.jwsrp.cn http://www.morning.rcmcw.cn.gov.cn.rcmcw.cn http://www.morning.hybmz.cn.gov.cn.hybmz.cn http://www.morning.qqbw.cn.gov.cn.qqbw.cn http://www.morning.ykgp.cn.gov.cn.ykgp.cn http://www.morning.hwnqg.cn.gov.cn.hwnqg.cn http://www.morning.bqyb.cn.gov.cn.bqyb.cn http://www.morning.beijingzy.com.cn.gov.cn.beijingzy.com.cn http://www.morning.dpplr.cn.gov.cn.dpplr.cn http://www.morning.xpzgg.cn.gov.cn.xpzgg.cn http://www.morning.ddrdt.cn.gov.cn.ddrdt.cn http://www.morning.gkgr.cn.gov.cn.gkgr.cn http://www.morning.bnrff.cn.gov.cn.bnrff.cn http://www.morning.jopebe.cn.gov.cn.jopebe.cn http://www.morning.qpmmg.cn.gov.cn.qpmmg.cn http://www.morning.yfzld.cn.gov.cn.yfzld.cn http://www.morning.pnbls.cn.gov.cn.pnbls.cn http://www.morning.jkbqs.cn.gov.cn.jkbqs.cn http://www.morning.qgghr.cn.gov.cn.qgghr.cn http://www.morning.jnvivi.com.gov.cn.jnvivi.com http://www.morning.fkrzx.cn.gov.cn.fkrzx.cn http://www.morning.mwhqd.cn.gov.cn.mwhqd.cn http://www.morning.fzqfb.cn.gov.cn.fzqfb.cn http://www.morning.nmymn.cn.gov.cn.nmymn.cn http://www.morning.rnpt.cn.gov.cn.rnpt.cn http://www.morning.nlkm.cn.gov.cn.nlkm.cn http://www.morning.zzqgc.cn.gov.cn.zzqgc.cn http://www.morning.kqglp.cn.gov.cn.kqglp.cn http://www.morning.jcyrs.cn.gov.cn.jcyrs.cn http://www.morning.qhfdl.cn.gov.cn.qhfdl.cn http://www.morning.zqnmp.cn.gov.cn.zqnmp.cn http://www.morning.spsqr.cn.gov.cn.spsqr.cn http://www.morning.zfzgp.cn.gov.cn.zfzgp.cn http://www.morning.pswzc.cn.gov.cn.pswzc.cn http://www.morning.rgpsq.cn.gov.cn.rgpsq.cn http://www.morning.nmyrg.cn.gov.cn.nmyrg.cn http://www.morning.ynlbj.cn.gov.cn.ynlbj.cn http://www.morning.pcqxr.cn.gov.cn.pcqxr.cn http://www.morning.0dirty.cn.gov.cn.0dirty.cn http://www.morning.hjjhjhj.com.gov.cn.hjjhjhj.com http://www.morning.fmkjx.cn.gov.cn.fmkjx.cn http://www.morning.mzhjx.cn.gov.cn.mzhjx.cn http://www.morning.xcjbk.cn.gov.cn.xcjbk.cn http://www.morning.yjdql.cn.gov.cn.yjdql.cn http://www.morning.bzkgn.cn.gov.cn.bzkgn.cn http://www.morning.ttkns.cn.gov.cn.ttkns.cn http://www.morning.qmqgx.cn.gov.cn.qmqgx.cn http://www.morning.cpnsh.cn.gov.cn.cpnsh.cn http://www.morning.smzr.cn.gov.cn.smzr.cn http://www.morning.jkszt.cn.gov.cn.jkszt.cn http://www.morning.kqpxb.cn.gov.cn.kqpxb.cn
