桥下の民 - 数据库
type: channel
link: https://wordplay.work/index.php/category/数据库
items: ClickHouse
items: Btree 索引详解
items: MySQL 索引 B-Tree（B+Tree）
items: MySQL 索引类型
items: Oracle 常用 SQL
items: MySQL 常用 SQL
items: MySQL 主从复制延时
items: MySQL 巡检
items: 编程式事务&声明式事务
items: MyBatis--插件开发

ClickHouse
type: item
date: 2024-01-30T02:06:00+00:00
description: 一、ClickHouse 入门 ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库（DBMS），使用 C++语言编写，主要用于在线分析处理查询（OLAP），能够使用 SQL 查询实时生成分析数据报告。 1.1、ClickHouse 的特点 1.1.1、列式存储 以下面的表为例： Id Name Age 1 张三 18 2 李四 22 3 王五 34 1）采用行式存储时，数据在磁盘上的组织结构为： 好处是想查某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的。 2）采用列式存储时，数据在磁盘上的组织结构为： 3）列式储存的好处： 对于列的聚合，计数，求和等统计操作原因优于行式存储。 由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。 由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于 cache 也有了更大的发挥空间。 1.1.2、DBMS 的功能 几乎覆盖了标准 SQL 的大部分语法，包括 DDL 和 DML，以及配套的各种函数，用户管理及权限管理，数据的备份与恢复。 1.1.3、多样化引擎 ClickHouse 和 MySQL 类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类 20 多种引擎。 1.1.4、高吞吐写入能力 ClickHouse 采用类 LSM Tree的结构，数据写入后定期在后台 Compaction。通过类 LSM tree的结构，ClickHouse 在数据导入时全部是顺序 append 写，写入后数据段不可更改，在后台compaction 时也是多个段 merge sort 后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在 HDD 上也有着优异的写入性能。 官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力，按照每行100Byte 估算，大约相当于 50W-200W 条/s 的写入速度。 1.1.5、数据分区与线程级并行 ClickHouse 将数据划分为多个 partition，每个 partition 再进一步划分为多个 index granularity(索引粒度)，然后通过多个 CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下，单条 Query 就能利用整机所有 CPU。极致的并行处理能力，极大的降低了查询延时。 所以，ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu，就不利于同时并发多条查询。所以对于高 qps 的查询业务，ClickHouse 并不是强项。 1.1.6、性能对比 ClickHouse 像很多 OLAP 数据库一样，单表查询速度优于关联查询，而且 ClickHouse 的两者差距更为明显。 二、ClickHouse 的安装 2.1、准备工作 2.1.1、确定防火墙处于关闭状态 2.1.2、CentOS 取消打开文件数限制 （1）在 hadoop102 的 /etc/security/limits.conf 文件的末尾加入以下内容 [atguigu@hadoop102 ~]$ sudo vim /etc/security/limits.conf * soft nofile 65536 * hard nofile 65536 * soft nproc 131072 * hard nproc 131072 （2）在 hadoop102 的/etc/security/limits.d/20-nproc.conf 文件的末尾加入以下内容 [atguigu@hadoop102 ~]$ sudo vim /etc/security/limits.d/20-nproc.conf * soft nofile 65536 * hard nofile 65536 * soft nproc 131072 * hard nproc 131072 2.1.3、安装依赖 sudo yum install -y libtool sudo yum install -y *unixODBC* 2.1.4、CentOS 取消 SELINUX （1）修改/etc/selinux/config 中的 SELINUX=disabled [atguigu@hadoop102 ~]$ sudo vim /etc/selinux/config SELINUX=disabled 2.2、单机安装 官网：https://clickhouse.t…

Btree 索引详解
type: item
date: 2023-10-04T03:03:00+00:00
description: Btree索引 Btree索引(或Balanced Tree)，是一种很普遍的数据库索引结构，oracle默认的索引类型（本文也主要依据oracle来讲）。其特点是定位高效、利用率高、自我平衡，特别适用于高基数字段，定位单条或小范围数据非常高效。理论上，使用Btree在亿条数据与100条数据中定位记录的花销相同。 数据结构利用率高、定位高效。 Btree索引的数据结构如下： 结构看起来Btree索引与Binary Tree相似，但在细节上有所不同，上图中用不同颜色的标示出了Btree索引的几个主要特点： 树形结构：由根节(root)、分支(branches)、叶(leaves)三级节点组成，其中分支节点可以有多层。 多分支结构：与binary tree不相同的是，btree索引中单root/branch可以有多个子节点(超过2个)。 双向链表：整个叶子节点部分是一个双向链表(后面会描述这个设计的作用)。 单个数据块中包括多条索引记录。 这里先把几个特点罗列出来，后面会说到各自的作用。 结构上Btree与Binary Tree的区别，在于binary中每节点代表一个数值，而balanced中root和Btree节点中记录了多条”值范围”条目(如:[60-70][70-80])，这些”值范围”条目分别指向在其范围内的叶子节点。既root与branch可以有多个分支，而不一定是两个，对数据块的利用率更高。 在Leaf节点中，同样也是存放了多条索引记录，这些记录就是具体的索引列值，和与其对应的rowid。另外，在叶节点层上，所有的节点在组成了一个双向链表。 了解基本结构后，下图展示定位数值82的过程： 演算如下： 读取root节点，判断82大于在0-120之间，走左边分支。 读取左边branch节点，判断82大于80且小于等于120，走右边分支。 读取右边leaf节点，在该节点中找到数据82及对应的rowid。 使用rowid去物理表中读取记录数据块(如果是count或者只select rowid，则最后一次读取不需要)。 在整个索引定位过程中，数据块的读取只有3次。既三次I/O后定位到rowid。 而由于Btree索引对结构的利用率很高，定位高效。当1千万条数据时，Btree索引也是三层结构(依稀记得亿级数据才是3层与4层的分水岭)。定位记录仍只需要三次I/O，这便是开头所说的，100条数据和1千万条数据的定位，在btree索引中的花销是一样的。 平衡扩张 除了利用率高、定位高效外，Btree的另一个特点是能够永远保持平衡，这与它的扩张方式有关。(unbalanced和hotspot是两类问题)，先描述下Btree索引的扩张方式： 新建一个索引，索引上只会有一个leaf节点，取名为Node A，不断的向这个leaf节点中插入数据后，直到这个节点满，这个过程如下图（绿色表示新建/空闲状态，红色表示节点没有空余空间）： 当Node A满之后，我们再向表中插入一条记录，此时索引就需要做拆分处理：会新分配两个数据块NodeB & C，如果新插入的值，大于当前最大值，则将Node A中的值全部插入Node B中，将新插入的值放到Node C中；否则按照5-5比例，将已有数据分别插入到NodeB与C中。 无论采用哪种分割方式，之前的leaf节点A，将变成一个root节点，保存两个范围条目，指向B与C，结构如下图（按第一种拆分形式）： 当Node C满之后，此时 Node A仍有空余空间存放条目，所以不需要再拆分，而只是新分配一个数据块Node D，将在Node A中创建指定到Node D的条目： 如果当根节点Node A也满了，则需要进一步拆分：新建Node E&F&G，将Node A中范围条目拆分到E&F两个节点中，并建立E&F到BCD节点的关联，向Node G插入索引值。此时E&F为branch节点，G为leaf节点，A为Root节点： 在整个扩张过程中，Btree自身总能保持平衡，Leaf节点的深度能一直保持一致。 实际应用中的一些问题 前面说完了Btree索引的结构与扩张逻辑，接下来讲一些Btree索引在应用中的一些问题： 单一方向扩展引起的索引竞争(Index Contention) 若索引列使用sequence或者timestamp这类只增不减的数据类型。这种情况下Btree索引的增长方向总是不变的，不断的向右边扩展，因为新插入的值永远是最大的。 当一个最大值插入到leaf block中后，leaf block要向上传播，通知上层节点更新所对应的“值范围”条目中的最大值，因此所有靠右边的block(从leaf 到branch甚至root)都需要做更新操作，并且可能因为块写满后执行块拆分。 如果并发插入多个最大值，则最右边索引数据块的…

MySQL 索引 B-Tree（B+Tree）
type: item
date: 2023-10-03T16:19:00+00:00
description: 一、索引的分类 1、从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式。 2、从应用层次来分：普通索引，唯一索引，复合索引。 3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。 1中所描述的是索引存储时保存的形式，2是索引使用过程中进行的分类，两者是不同层次上的划分。不过平时讲的索引类型一般是指在应用层次的划分。 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。 唯一索引：索引列的值必须唯一，但允许有空值。 复合索引：即一个索引包含多个列。 聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。 非聚簇索引：不是聚簇索引，就是非聚簇索引。 二、索引的底层实现 MySQL 默认存储引擎 innodb 只显式支持 B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb 会透明建立自适应 hash 索引，即在 B 树索引基础上建立 hash 索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。 2.1、Hash索引 基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。 2.2、B-Tree索引（MySQL使用B+Tree） B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。 2.3、B+Tree索引 是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比 B-Tree 来说，进行范围查找时只需要查找两个节点，进行遍历即可。而 B-Tree 需要获取所有节点，相比之下 B+Tree 效率更高。 结合存储引擎来讨论（一般默认使用 B+Tree） 案例：假设有一张学生表，id为主键 id name birthday 1 Tom 1996-01-01 2 Jann 1996-01-04 3 Ray 1996-01-08 4 Michael 1996-01-10 5 Jack 1996-01-13 6 Steven 1996-01-23 7 Lily 1996-01-25 在MyISAM引擎中的实现（二级索引也是这样实现的） 在InnoDB中的实现 三、问题 问：为什么索引结构默认使用 B-Tree，而不是 hash，二叉树，红黑树？ hash：虽然可以快速定位，但是没有顺序，IO 复杂度高。 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。 红黑树：树的高度随着数据量增加而增加，IO 代价高。 问：为什么官方建议使用自增长主键作为索引。 结合 B+Tree 的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

MySQL 索引类型
type: item
date: 2023-10-03T15:13:00+00:00
description: 一、简介 MySQL 主要索引类型有如下几种： 1、主键索引 2、唯一索引 3、普通索引 4、空间索引 5、全文索引 假设有如下一张表: CREATE TABLE `t1` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `u1` int unsigned NOT NULL DEFAULT '0', `u2` int unsigned NOT NULL DEFAULT '0', `u3` varchar(20) NOT NULL DEFAULT '', `u4` varchar(35) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB 二、索引类型 2.1、聚簇索引 索引与数据存放在一起，找到索引的同时也找到了数据；聚簇索引具有唯一性，一张表只有一个聚簇索引。 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个非空唯一索引代替。如果没有，InnoDB 会定义一个隐藏的_rowid 列来作为聚簇索引。 2.2、非聚簇索引 索引与数据分开存放，索引结构的叶子节点指向了数据的对应行。 2.3、主键索引 # 查看创建的索引关键词 PRIMARY KEY (`id`) 2.4、唯一索引 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 # 创建唯一索引 mysql> alter table t1 add unique idx_u1(`u1`); # 查看创建的索引关键词 UNIQUE KEY `idx_u1` (`u1`) 2.5、单列索引 即索引创建在单个列上。 # 创建单列索引 mysql> alter table t1 add index idx_u1(`u1`); # 查看创建的索引关键词 KEY `idx_u1` (`u1`) 2.6、多列索引 创建多列索引时记得遵循最左匹配原则，把区分度较高的字段放前面。 # 创建多列索引 mysql> alter table t1 add index idx_u1_u2(`u1`,`u2`); # 查看创建的多列索引关键词 KEY `idx_u1_u2` (`u1`,`u2`) 2.7、前缀索引 即当字段的长度超过索引限制，可以为字段的部分前缀创建索引。 # 创建前缀索引，取字符串的前4个字符 mysql> alter table t1 add index idx_u3(`u3`(4)); # 查看创建的索引关键词 KEY `idx_u3` (`u3`(4)) 2.8、倒序索引 8.0 版本出的新功能，以往创建倒叙索引可以创建成功，但实际上仍然是顺序的。 # 5.7 创建倒序索引 mysql> select u1 from t1 limit 10; +----+ | u1 | +----+ | 12 | | 23 | | 12 | | 34 | +----+ 10 rows in set (0.00 sec) # 创建倒序索引 mysql> alter table t1 add index idx_u3(u3 desc); # 实际查出来还是顺序 mysql> select u1 from t1 limit 10; +----+ | u1 | +----+ | 12 | | 12 | | 12 | | 12 | +----+ # 8.0 创建倒序索引 mysql> select u1 from t1 limit 5; +----+ | u1 | +----+ | 12 | | 23 | | 12 | | 34 | +----+ 5 rows in set (0.00 sec) # 添加索引 mysql> alter table t1 add index idx_u1(u1 desc); # 查询倒序索引成功 mysql> select u1 from t1 limit 5; +----+ | u1 | +----+ | 74 | | 74 | | 74 | | 74 | +----+ 5 rows in set (0.00 sec) 2.9、函数索引 # 创建函数索引 mysql> alter table t1 add index idx_abs_u2 ((ABS(`u2`))); # 查看创建的索引关键词 KEY `idx_abs_u2` ((abs(`u2`))) 2.10、表达式索引 # 创建表达式索引 mysql> alter table t1 add index idx_u1u2 ((u1 + u2)); # 查看创建的索引关键词 KEY `idx_u1u2` (((`u1` + `u2`))) 2.11、不可见索引 # 设置不可见…

Oracle 常用 SQL
type: item
date: 2023-06-27T13:14:00+00:00
description: 请问如何分辨某个用户是从哪台机器登陆oracle的? select machine , terminal from v$session; 怎样计算一个表占用的空间的大小？ select owner,table_name, num_rows, blocks*aaa/1024/1024 "size m", empty_blocks, last_analyzed from dba_tables where table_name='xxx'; here: aaa is the value of db_block_size ; xxx is the table name you want to check 如何查看系统被锁的事务时间？ select * from v$locked_object ; 怎么获取有哪些用户在使用数据库 select username from v$session; 如何解锁? alter system kill session ‘sid,serir#’; 如何在字符串里加回车？ select 'welcome to visit'||chr(10)||'www.csdn.net' from dual ; 中文是如何排序的？ oracle9i之前，中文是按照二进制编码进行排序的。 在oracle9i中新增了按照拼音、部首、笔画排序功能。设置nls_sort值 schinese_radical_m 按照部首（第一顺序）、笔划（第二顺序）排序 schinese_stroke_m 按照笔划（第一顺序）、部首（第二顺序）排序 schinese_pinyin_m 按照拼音排序 如何改变win中sql*plus启动选项？ sql*plus自身的选项设置我们可以在$oracle_home/sqlplus/admin/glogin.sql中设置。 怎样修改oracel数据库的默认日期? alter session set nls_date_format='yyyymmddhh24miss'; or 可以在init.ora中加上一行 nls_date_format='yyyymmddhh24miss' 如何将小表放入keep池中? alter table xxx storage(buffer_pool keep); 如何检查是否安装了某个patch? check that orainventory 如何使select语句使查询结果自动生成序号? select rownum,col from table; 怎么可以快速做一个和原表一样的备份表? create table new_table as (select * from old_table); 怎样解除procedure被意外锁定? alter system kill session ,把那个session给杀掉，不过你要先查出她的session id or 把该过程重新改个名字就可以了。 sql reference是个什么东西？ 是一本sql的使用手册，包括语法、函数等等，oracle官方网站的文档中心有下载. 如何查看数据库的状态? unix下 ps -ef | grep ora windows下 看服务是否起来 是否可以连上数据库 怎样查看oracle中有哪些程序在运行之中？ 查看v$sessions表 怎么可以看到数据库有多少个tablespace? select * from dba_tablespaces; 如何修改oracle数据库的用户连接数？ 修改initsid.ora，将process加大，重启数据库. 如何查出一条记录的最后更新时间? 可以用logminer 察看 如何在pl/sql中读写文件？ utl_file包答应用户通过pl/sql读写操作系统文件。 怎样把“&”放入一条记录中？ insert into a values (translate ('at{&}t','at{}','at')); exp 如何加query参数？ exp user/pass file=a.dmp tables(bsempms) query='"where emp_no="'s09394"'"" ﹔ 关于oracle8i支持简体和繁体的字符集问题？ zhs16gbk可以支 data guard是什么软件？ 就是standby的换代产品 如何创建spfile? sql> connect / as sysdba sql> select * from v$version; sql> create pfile from spfile; sql> create spfile from pfile='e:"ora9i"admin"eygle"pfile"init.ora'; 文件已创建。…

MySQL 常用 SQL
type: item
date: 2023-05-27T13:44:00+00:00
description: 根据库名获取所有表的信息 SELECT * FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'srm'; 获取指定数据库中所有表的名称和注释 SELECT TABLE_NAME, TABLE_COMMENT FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'srm'; 获取指定数据库中所有表的所有字段信息 SELECT TABLE_SCHEMA AS '库名', TABLE_NAME AS '表名', COLUMN_NAME AS '列名', ORDINAL_POSITION AS '列的排列顺序', COLUMN_DEFAULT AS '默认值', IS_NULLABLE AS '是否为空', DATA_TYPE AS '数据类型', CHARACTER_MAXIMUM_LENGTH AS '字符最大长度', NUMERIC_PRECISION AS '数值精度(最大位数)', NUMERIC_SCALE AS '小数精度', COLUMN_TYPE AS '列类型', COLUMN_KEY AS 'KEY', EXTRA AS '额外说明', COLUMN_COMMENT AS '注释' FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'srm' ORDER BY TABLE_NAME, ORDINAL_POSITION; 获取指定数据库中所有表和字段的基本信息 SELECT C.TABLE_SCHEMA AS '库名', T.TABLE_NAME AS '表名', T.TABLE_COMMENT AS '表注释', C.COLUMN_NAME AS '列名', C.COLUMN_COMMENT AS '列注释', C.ORDINAL_POSITION AS '列的排列顺序', C.COLUMN_DEFAULT AS '默认值', C.IS_NULLABLE AS '是否为空', C.DATA_TYPE AS '数据类型', C.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度', C.NUMERIC_PRECISION AS '数值精度(最大位数)', C.NUMERIC_SCALE AS '小数精度', C.COLUMN_TYPE AS '列类型', C.COLUMN_KEY AS 'KEY', C.EXTRA AS '额外说明' FROM information_schema.`TABLES` T LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME AND T.TABLE_SCHEMA = C.TABLE_SCHEMA WHERE T.TABLE_SCHEMA = 'srm' ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION;

MySQL 主从复制延时
type: item
date: 2023-05-19T14:05:00+00:00
description: 一、主从复制原理 MySQL 的主从复制是依赖于 binlog，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。 主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的操作不会等待 binlog 同步地完成。 详细流程如下： 主库写 binlog：主库的更新 SQL(update、insert、delete) 被写到 binlog； 主库发送 binlog：主库创建一个 log dump 线程来发送 binlog 给从库； 从库写 relay log：从库在连接到主节点时会创建一个 IO 线程，以请求主库更新的 binlog，并且把接收到的 binlog 信息写入一个叫做 relay log 的日志文件； 从库回放：从库还会创建一个 SQL 线程读取 relay log 中的内容，并且在从库中做回放，最终实现主从的一致性。 二、如何保证主从一致 当主库和从库数据同步时，突然中断怎么办？因为主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接。 对于下面的情况，假如主库执行如下 SQL，其中 a 和 create_time 都是索引： delete from t where a > '666' and create_time<'2022-03-01' limit 1; 数据选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的。 所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条 SQL 时，使用的是索引 a，而从库在执行这条 SQL 时，使用了索引 create_time，最后主从数据不一致了。 如何处理？ 可以把 binlog 格式修改为 row，row 格式的 binlog 日志记录的不是 SQL 原文，而是两个 event:Table_map 和 Delete_rows。 Table_map event 说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。row 格式的 binlog 记录的就是要删除的主键 ID 信息，因此不会出现主从不一致的问题。 但是如果 SQL 删除 10 万行数据，使用 row 格式就会很占空间，10 万条数据都在 binlog 里面，写 binlog 的时候也很耗 IO。但是 statement 格式的 binlog 可能会导致数据不一致。 MySQL 的一个折中的方案，mixed 格式的 binlog，其实就是 row 和 statement 格式混合使用，当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用就用 statement 格式。 三、主从延迟 有时候我们遇到从数据库中获取不到信息的诡异问题时，会纠结于代码中是否有一些逻辑会把之前写入的内容删除，但是你又会发现，过了一段时间再去查询时又可以读到数据了，这基本上就是主从延迟在作怪。 主从延迟，其实就是“从库回放” 完成的时间，与 “主库写 binlog” 完成时间的差值，会导致从库查询的数据，和主库的不一致。 3.1、主从延迟原理 谈到 MySQL 数据库主从同步延迟原理，得从 MySQL 的主从复制原理说起： MySQL 的主从复制都是单线程的操作，主库对所有 DDL 和 DML 产生 binlog，binlog 是顺序写，所以效率很高； Slave 的 Slave_IO_Running 线程会到主库取日志，放入 relay log，效率会比较高； Slave 的 Slave_SQL_Running 线程将主库的 DDL 和 DML 操作都在 Slave 实施，DML 和 DDL 的 IO 操作是随机的，不是顺序的，因此成本会很高，还可能是 Slave 上的其他查询产生 lock 争用，由于 Slave_SQL_Running 也是单线程的，所以一个 DDL 卡住了，需要执行 10 分钟，那么所有之后的 DDL 会等待这个 DDL 执行完才会继续执行，这就导致了延时。 总结一下主从延迟的主要原因：主从延迟主要是出现在 “relay log 回放” 这一步，当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了，当然还有就是可能与从库的大型 query 语句产生了锁等待。 3.2、主从延迟情况 从库机器性能：从库机器比主库的机器性能差，只需选择主从库一样规格的机器就好。 从库压力大：可以搞了一主多从的架构，还可以把 binlog 接入到 Hadoop 这类系统，让它们提供查询的能力。 从库过多：要避免复制的从节点数量过多，从库数据一般以3-5个为宜。 大事务：…

MySQL 巡检
type: item
date: 2023-04-16T13:41:00+00:00
description: 一、巡检内容 表巡检 大小超过10G的表 索引超过6个的表 碎片率超过50%的表 行数超过1000万行的表 非默认字符集的表 含有大字段的表 varchar定义超长的表 无主键/索引的表 索引巡检 重复索引 索引列超过5个的索引 无用索引 重要参数 version innodb_buffer_pool_size innodb_flush_log_at_trx_commit innodb_log_file_size innodb_log_files_in_group innodb_file_per_table innodb_max_dirty_pages_pct sync_binlog max_connections query_cache_type table_open_cache table_definition_cache 重要状态指标 Uptime Opened_files Opened_table_definitions Opened_tables Max_used_connections Threads_created Threads_connected Aborted_connects Aborted_clients Table_locks_waited Innodb_buffer_pool_wait_free Innodb_log_waits Table_locks_waited Innodb_row_lock_waits Innodb_row_lock_time_avg Binlog_cache_disk_use Created_tmp_disk_tables 用户检查 无密码用户 %用户 权限检查 二、使用脚本 check_mysql.py 创建巡检用户 grant select,process on *.* to monitor@localhost identified by '123456' 巡检脚本填入相应ip、端口号、账号、密码 执行巡检 #!/usr/bin/env python # -*- coding: utf-8 -*-# import sys import time from datetime import datetime import MySQLdb class DBUtil: def __init__(self, user=None, passwd=None, host=None, port=None, db=None): self.user = user self.passwd = passwd self.host = host self.port = port self.db = db self._conn = None self._cursor = None def __enter__(self): self._conn = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, db=self.db) self._cursor = self._conn.cursor() return self def __exit__(self, exc_type, exc_val, exc_tb): self._conn.close() self._conn = None def check_table_size(self): try: sql = "select table_schema,table_name,concat(round((data_length+index_length)/1024/1024,2),'M') FROM \ information_schema.tables where (DATA_LENGTH+INDEX_LENGTH) > 10*1024*1024*1024 and table_schema not in \ ('information_schema','mysql','performance_schema','sys')" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查超过10G大小的表') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s size: %s") % (row[0], row[1], row[2])) except Exception as e: raise (e) def check_table_index(self): tr…

编程式事务&声明式事务
type: item
date: 2023-03-14T16:44:00+00:00
description: 事务管理在系统开发中是不可缺少的一部分，Spring 提供了很好事务管理机制，主要分为编程式事务和声明式事务两种。 一、编程式事务 基于底层的 API，如PlatformTransactionManager、TransactionDefinition 和 TransactionTemplate等核心接口，开发者完全可以通过编程的方式来进行事务管理。 编程式事务方式需要是开发者在代码中手动的管理事务的开启、提交、回滚等操作。 public void test() { TransactionDefinition def = new DefaultTransactionDefinition(); TransactionStatus status = transactionManager.getTransaction(def); try { // 事务操作 // 事务提交 transactionManager.commit(status); } catch (DataAccessException e) { // 事务提交 transactionManager.rollback(status); throw e; } } 如以上代码，开发者可以通过 API 自己控制事务。 二、声明式事务 声明式事务管理方法允许开发者配置的帮助下来管理事务，而不需要依赖底层API进行硬编码。开发者可以只使用注解或基于配置的 XML 来管理事务。 @Transactional public void test() { // 事务操作 } 如上，使用@Transactional即可给test方法增加事务控制。 当然，上面的代码只是简化后的，想要使用事务还需要一些配置内容。 2.1、声明式事务的约定流程 首先 Spring 通过事务管理器（PlatformTransactionManager的子类）创建事务，与此同时会把事务定义中的隔离级别、超时时间等属性根据配置内容往事务上设置。而根据传播行为配置采取一种特定的策略，后面会谈到传播行为的使用问题，这是 Spring 根据配置完成的内容，你只需要配置，无须编码。然后，启动开发者提供的业务代码，我们知道 Spring 会通过反射的方式调度开发者的业务代码，但是反射的结果可能是正常返回或者产生异常返回，那么它给的约定是只要发生异常，并且符合事务定义类回滚条件的，Spring 就会将数据库事务回滚，否则将数据库事务提交，这也是 Spring 自己完成的。 编程式事务每次实现都要单独实现，但业务量大且功能复杂时，使用编程性事务无疑是痛苦的；而声明式事务不同，声明式事务属于非侵入性，不会影响业务逻辑的实现，只需在配置文件中做相关的事务规则声明（或通过基于@Transactional注解的方式），便可以将事务规则应用到业务逻辑中； 非侵入式的开发方式，声明式事务管理使业务代码不受污染，一个普通的 POJO 对象，只要加上注解就可以获得完全的事务支持； 是侵入性事务管理，直接使用底层的PlatformTransactionManager、使用TransactionTemplate(Spring推荐使用)； 编程式事务管理对基于 POJO 的应用来说是唯一选择。我们需要在代码中调用beginTransaction()、commit()、rollback()等事务管理相关的方法； 三、总结 3.1、声明式事务的优点 声明式事务帮助我们节省了很多代码，他会自动帮我们进行事务的开启、提交以及回滚等操作，把程序员从事务管理中解放出来。 声明式事务管理使用了 AOP 实现的，本质就是在目标方法执行前后进行拦截。 在目标方法执行前加入或创建一个事务，在执行方法执行后，根据实际情况选择提交或是回滚事务。 使用这种方式，对代码没有侵入性，方法内只需要写业务逻辑就可以了。 3.2、声明式事务的粒度问题 声明式事务有一个局限，那就是他的最小粒度要作用在方法上。 也就是说，如果想要给一部分代码块增加事务的话，那就需要把这个部分代码块单独独立出来作为一个方法。 但是，正是因为这个粒度问题，并不建议过度的使用声明式事务。 首先，因为声明式事务是通过注解的，有些时候还可以通过配置实现，这就会导致一个问题，那就是这个事务有可能被开发者忽略。 事务被忽略了有什么问题呢？ 首先，如果开发者没有注意到一个方法是被事务嵌套的，那么就可能会再方法中加入一些如RPC远程调用、消息发送、缓存更新、文件写入等操作。 我们知道，这些操作如果被包在事务中，有两个问题： 1、这些操作自身是无法回滚的，这就会导致数据的不一致。可能RPC调用成功了，但是本地事务回滚了，可是PRC调用无法回滚了。 2、在事务中有远程调用，就会拉长整个事务。那么久会导致本事务的数据库连接一直被占用，…

MyBatis--插件开发
type: item
date: 2023-02-11T15:08:00+00:00
description: 十、MyBatis-插件开发 MyBatis在四大对象的创建过程中，都会有插件进行 介入。插件可以利用动态代理机制一层层的包装目标对象，而实现在目标对象执行目标方法之前进行拦截 的效果。 MyBatis 允许在已映射语句执行过程中的某一点进行 拦截调用。 默认情况下，MyBatis 允许使用插件来拦截的方法调 用包括： Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed) ParameterHandler (getParameterObject, setParameters) ResultSetHandler (handleResultSets, handleOutputParameters) StatementHandler (prepare, parameterize, batch, update, query) 10.1、插件开发 插件开发步骤 1）、编写插件实现Interceptor接口，并使用 @Intercepts注解完成插件签名。 2）、在全局配置文件中注册插件。 /** * 完成插件签名： * 告诉MyBatis当前插件用来拦截哪个对象的哪个方法 */ @Intercepts( { @Signature(type=StatementHandler.class,method="parameterize",args=java.sql.Statement.class) }) public class MyFirstPlugin implements Interceptor{ /** * intercept：拦截： * 拦截目标对象的目标方法的执行； */ @Override public Object intercept(Invocation invocation) throws Throwable { // TODO Auto-generated method stub System.out.println("MyFirstPlugin...intercept:"+invocation.getMethod()); //动态的改变一下sql运行的参数：以前1号员工，实际从数据库查询3号员工 Object target = invocation.getTarget(); System.out.println("当前拦截到的对象："+target); //拿到：StatementHandler==>ParameterHandler===>parameterObject //拿到target的元数据 MetaObject metaObject = SystemMetaObject.forObject(target); Object value = metaObject.getValue("parameterHandler.parameterObject"); System.out.println("sql语句用的参数是："+value); //修改完sql语句要用的参数 metaObject.setValue("parameterHandler.parameterObject", 3); //执行目标方法 Object proceed = invocation.proceed(); //返回执行后的返回值 return proceed; } /** * plugin： * 包装目标对象的：包装：为目标对象创建一个代理对象 */ @Override public Object plugin(Object target) { // TODO Auto-generated method stub //我们可以借助Plugin的wrap方法来使用当前Interceptor包装我们目标对象 System.out.println("MyFirstPlugin...plugin:mybatis将要包装的对象"+target); Object wrap = Plugin.wrap(target, this); //返回为当前target创建的动态代理 return wrap; } /** * setProperties： * 将插件注册时 的property属性设置进来 */ @Override public void setProperties(Properties properties) { // TODO Auto-generated method stub System.out.println("插件配置的信息："+properties); } } 测试代码： /** * 1、获取sqlSession…