一、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.tech/ 下载地址：http://repo.red-soft.biz/repos/clickhouse/stable/el7/ 2.2.1、在 hadoop102 的/opt/software 下创建 clickhouse 目录 2.2.2、上传 2.2.3、将安装文件同步到 hadoop103、hadoop104 [atguigu@hadoop102 software]$ xsync clickhouse 2.2.4、分别在三台机子上安装这 4 个 rpm 文件 [atguigu@hadoop102 clickhouse]$ sudo rpm -ivh *.rpm 2.2.5、修改配置文件 [atguigu@hadoop102 clickhouse]$ sudo vim /etc/clickhouse-server/config.xml （1）把 <listen_host>::</listen_host> 的注释打开，这样的话才能让 ClickHouse 被除本机以外的服务器访问。 （2）分发配置文件sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml 在这个文件中，有 ClickHouse 的一些默认路径配置，比较重要的 数据文件路径：/var/lib/clickhouse/ 日志文件路径：/var/log/clickhouse-server/clickhouse-server.log 2.2.6、启动 Server [atguigu@hadoop102 clickhouse]$ sudo systemctl start clickhouse-server 2.2.7、三台机器上关闭开机自启 [atguigu@hadoop102 clickhouse]$sudo systemctl disable clickhouse-server 2.2.8、使用 client 连接 server [atguigu@hadoop102 clickhouse]$ clickhouse-client -m 四、表引擎 4.1、表引擎的使用 表引擎是 ClickHouse 的一大特色。可以说， 表引擎决定了如何存储表的数据。包括： 数据的存储方式和位置，写到哪里以及从哪里读取数据。 支持哪些查询以及如何支持。 并发数据访问。 索引的使用（如果存在）。 是否可以执行多线程请求。 数据复制参数。 表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。 特别注意：引擎的名称大小写敏感。 4.2、TinyLog 以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。 如： create table t_tinylog ( id String, name String) engine=TinyLog; 4.3、Memory 内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过 10G/s）。 一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。 4.4、MergeTree ClickHouse 中最强大的表引擎当属 MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎，支持索引和分区，地位可以相当于 innodb 之于 Mysql。而且基于 MergeTree，还衍生除了很多小弟，也是非常有特色的引擎。 1）建表语句 create table t_order_mt( id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime ) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id); 2）插入数据 insert into t_order_mt values (101,'sku_001',1000.00,'2020-06-01 12:00:00') , (102,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 13:00:00'), (102,'sku_002',12000.00,'2020-06-01 13:00:00'), (102,'sku_002',600.00,'2020-06-02 12:00:00'); MergeTree 其实还有很多参数(绝大多数用默认值即可)，但是三个参数是更加重要的，也涉及了关于 MergeTree 的很多概念。 4.4.1、partition by 分区(可选) 1）作用 分区的目的主要是降低扫描的范围，优化查询速度。 2）如果不填 只会使用一个分区。 3）分区目录 MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。 4）并行 分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。 5）数据写入与分区合并 任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中。 optimize table xxxx final; 6）例如 再次执行上面的插入操作 insert into t_order_mt values (101,'sku_001',1000.00,'2020-06-01 12:00:00') , (102,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 13:00:00'), (102,'sku_002',12000.00,'2020-06-01 13:00:00'), (102,'sku_002',600.00,'2020-06-02 12:00:00'); 查看数据并没有纳入任何分区 手动 optimize 之后 hadoop102 :) optimize table t_order_mt final; 再次查询 4.4.2、primary key 主键(可选) ClickHouse 中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。 主键的设定主要依据是查询语句中的 where 条件。 根据条件通过对主键进行某种形式的二分查找，能够定位到对应的 index granularity,避免了全表扫描。 index granularity： 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。 稀疏索引： 稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。 4.4.3、order by（必选） order by 设定了分区内的数据按照哪些字段顺序进行有序保存。 order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照 order by 的字段进行处理（比如后面会讲的去重和汇总）。 要求：主键必须是 order by 字段的前缀字段。 比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id) 4.4.4、二级索引 目前在 ClickHouse 的官网上二级索引的功能在 v20.1.2.4 之前是被标注为实验性的，在这个版本之后默认是开启的。 1）老版本使用二级索引前需要增加设置 是否允许使用实验性的二级索引（v20.1.2.4 开始，这个参数已被删除，默认开启） set allow_experimental_data_skipping_indices=1; 2）创建测试表 create table t_order_mt2( id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime, INDEX a total_amount TYPE minmax GRANULARITY 5 ) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id, sku_id); 其中 GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。 3）插入数据 insert into t_order_mt2 values (101,'sku_001',1000.00,'2020-06-01 12:00:00') , (102,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 13:00:00'), (102,'sku_002',12000.00,'2020-06-01 13:00:00'), (102,'sku_002',600.00,'2020-06-02 12:00:00'); 4）对比效果 那么在使用下面语句进行测试，可以看出二级索引能够为非主键字段的查询发挥作用。 [atguigu@hadoop102 lib]$ clickhouse-client --send_logs_level=trace <<< 'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)'; 4.4.5、数据 TTL TTL 即 Time To Live，MergeTree 提供了可以管理数据表或者列的生命周期的功能。 1）列级别 TTL （1）创建测试表 create table t_order_mt3( id UInt32, sku_id String, total_amount Decimal(16,2) TTL create_time+interval 10 SECOND, create_time Datetime ) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id, sku_id); （2）插入数据（注意：根据实际时间改变） insert into t_order_mt3 values (106,'sku_001',1000.00,'2020-06-12 22:52:30'), (107,'sku_002',2000.00,'2020-06-12 22:52:30'), (110,'sku_003',600.00,'2020-06-13 12:00:00'); （3）手动合并，查看效果 到期后，指定的字段数据归 2）表级 TTL 下面的这条语句是数据会在 create_time 之后 10 秒丢失 alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND; 涉及判断的字段必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段。 能够使用的时间周期： - SECOND - MINUTE - HOUR - DAY - WEEK - MONTH - QUARTER - YEAR 4.5、ReplacingMergeTree ReplacingMergeTree 是 MergeTree 的一个变种，它存储特性完全继承 MergeTree，只是多了一个去重的功能。 尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个ReplacingMergeTree。 1）去重时机 数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。 2）去重范围 如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。 所以 ReplacingMergeTree 能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。 3）案例演示 （1）创建表 create table t_order_rmt( id UInt32, sku_id String, total_amount Decimal(16,2) , create_time Datetime ) engine =ReplacingMergeTree(create_time) partition by toYYYYMMDD(create_time) primary key (id) order by (id, sku_id); ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。 （2）向表中插入数据 insert into t_order_rmt values (101,'sku_001',1000.00,'2020-06-01 12:00:00') , (102,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 13:00:00'), (102,'sku_002',12000.00,'2020-06-01 13:00:00'), (102,'sku_002',600.00,'2020-06-02 12:00:00'); （3）执行第一次查询 hadoop102 :) select * from t_order_rmt; （4）手动合并 OPTIMIZE TABLE t_order_rmt FINAL; （5）再执行一次查询 hadoop102 :) select * from t_order_rmt; 4）通过测试得到结论 实际上是使用 order by 字段作为唯一键 去重不能跨分区 只有同一批插入（新版本）或合并分区时才会进行去重 认定重复的数据保留，版本字段值最大的 如果版本字段相同则按插入顺序保留最后一笔 4.6、SummingMergeTree 对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。 ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎 SummingMergeTree 1）案例演示 （1）创建表 create table t_order_smt( id UInt32, sku_id String, total_amount Decimal(16,2) , create_time Datetime ) engine =SummingMergeTree(total_amount) partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id ); （2）插入数据 insert into t_order_smt values (101,'sku_001',1000.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 13:00:00'), (102,'sku_002',12000.00,'2020-06-01 13:00:00'), (102,'sku_002',600.00,'2020-06-02 12:00:00'); （3）执行第一次查询 hadoop102 :) select * from t_order_smt; （4）手动合并 OPTIMIZE TABLE t_order_smt FINAL; （5）再执行一次查询 hadoop102 :) select * from t_order_smt; 2）通过结果可以得到以下结论 以 SummingMergeTree（）中指定的列作为汇总数据列 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列 以 order by 的列为准，作为维度列 其他的列按插入顺序保留第一行 不在一个分区的数据不会被聚合 只有在同一批次插入(新版本)或分片合并时才会进行聚合 3）开发建议 设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。 4）问题 能不能直接执行以下 SQL 得到汇总值 select total_amount from XXX where province_name=’’ and create_date=’xxx’ 不行，可能会包含一些还没来得及聚合的临时明细 如果要是获取汇总值，还是需要使用 sum 进行聚合，这样效率会有一定的提高，但本身 ClickHouse 是列式存储的，效率提升有限，不会特别明显。 select sum(total_amount) from province_name=’’ and create_date=‘xxx’ 五、SQL 操作 基本上来说传统关系型数据库（以 MySQL 为例）的 SQL 语句，ClickHouse 基本都支持，这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL（MySQL）不一致的地方。 5.1、Insert 基本与标准 SQL（MySQL）基本一致 （1）标准 insert into [table_name] values(…),(….) （2）从表到表的插入 insert into [table_name] select a,b,c from [table_name_2] 5.2、Update 和 Delete ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation 查询，它可以看做 Alter 的一种。 虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。 “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。 （1）删除操作 alter table t_order_smt delete where sku_id ='sku_001'; （2）修改操作 alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102; 由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。 5.3、查询操作 ClickHouse 基本上与标准 SQL 差别不大 支持子查询 支持 CTE(Common Table Expression 公用表表达式 with 子句) 支持各种 JOIN，但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句，ClickHouse 也会视为两条新 SQL 窗口函数(官方正在测试中...) 不支持自定义函数 GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。 （1）插入数据 hadoop102 :) alter table t_order_mt delete where 1=1; insert into t_order_mt values (101,'sku_001',1000.00,'2020-06-01 12:00:00'), (101,'sku_002',2000.00,'2020-06-01 12:00:00'), (103,'sku_004',2500.00,'2020-06-01 12:00:00'), (104,'sku_002',2000.00,'2020-06-01 12:00:00'), (105,'sku_003',600.00,'2020-06-02 12:00:00'), (106,'sku_001',1000.00,'2020-06-04 12:00:00'), (107,'sku_002',2000.00,'2020-06-04 12:00:00'), (108,'sku_004',2500.00,'2020-06-04 12:00:00'), (109,'sku_002',2000.00,'2020-06-04 12:00:00'), (110,'sku_003',600.00,'2020-06-01 12:00:00'); （2）with rollup：从右至左去掉维度进行小计 hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup; （3）with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计 hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube; （4）with totals: 只计算合计 hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals; 5.4、alter 操作 同 MySQL 的修改字段基本一致 1）新增字段 alter table tableName add column newcolname String after col1; 2）修改字段类型 alter table tableName modify column newcolname String; 3）删除字段 alter table tableName drop column newcolname; 5.5、导出数据 clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv 六、副本 副本的目的主要是保障数据的高可用性，即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据。 https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/replication/ 6.1、副本写入流程 6.2、配置步骤 （1）启动 zookeeper 集群 （2）在 hadoop102 的/etc/clickhouse-server/config.d 目录下创建一个名为 metrika.xml的配置文件,内容如下： 注：也可以不创建外部文件，直接在 config.xml 中指定 <?xml version="1.0"?> <yandex> <zookeeper-servers> <node index="1"> <host>hadoop102</host> <port>2181</port> </node> <node index="2"> <host>hadoop103</host> <port>2181</port> </node> <node index="3"> <host>hadoop104</host> <port>2181</port> </node> </zookeeper-servers> </yandex> （3）同步到 hadoop103 和 hadoop104 上 sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.d/metrika.xml （4）在 hadoop102 的/etc/clickhouse-server/config.xml 中增加 <zookeeper incl="zookeeper-servers" optional="true" /> <include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from> （5）同步到 hadoop103 和 hadoop104 上 sudo /home/atguigu/bin/xsync /etc/clickhouse-server/config.xml 分别在 hadoop102 和 hadoop103 上启动 ClickHouse 服务 注意：因为修改了配置文件，如果以前启动了服务需要重启 [atguigu@hadoop102|3 ~]$ sudo clickhouse restart 注意：我们演示副本操作只需要在 hadoop102 和 hadoop103 两台服务器即可，上面的操作，我们 hadoop104 可以你不用同步，我们这里为了保证集群中资源的一致性，做了同步。 （6）在 hadoop102 和 hadoop103 上分别建表 副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表 1、hadoop102 create table t_order_rep2 ( id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime ) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_102') partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id); 2、hadoop103 create table t_order_rep2 ( id UInt32, sku_id String, total_amount Decimal(16,2), create_time Datetime ) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_103') partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku_id); 3、参数解释 ReplicatedMergeTree 中， 第一个参数是分片的 zk_path 一般按照：/clickhouse/table/{shard}/{table_name} 的格式写，如果只有一个分片就写 01 即可。 第二个参数是副本名称，相同的分片副本名称不能相同。 （7）在 hadoop102 上执行 insert 语句 insert into t_order_rep2 values (101,'sku_001',1000.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 12:00:00'), (103,'sku_004',2500.00,'2020-06-01 12:00:00'), (104,'sku_002',2000.00,'2020-06-01 12:00:00'), (105,'sku_003',600.00,'2020-06-02 12:00:00'); （8）在 hadoop103 上执行 select，可以查询出结果，说明副本配置正确
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)都需要做更新操作，并且可能因为块写满后执行块拆分。 如果并发插入多个最大值，则最右边索引数据块的的更新与拆分都会存在争抢，影响效率。在AWR报告中可以通过检测enq: TX – index contention事件的时间来评估争抢的影响。解决此类问题可以使用Reverse Index解决，不过会带来新的问题。 ndex Browning 索引枯萎(不知道该怎么翻译这个名词，就是指leaves节点”死”了，树枯萎了) 其实oracle针对这个问题有优化机制，但优化的不彻底，所以还是要拿出来的说。 我们知道当表中的数据删除后，索引上对应的索引值是不会删除的，特别是在一性次删除大批量数据后，会造成大量的dead leaf挂到索引树上。考虑以下示例，如果表100以上的数据会部被删除了，但这些记录仍在索引中存在，此时若对该列取max()： 通过与之前相同演算，找到了索引树上最大的数据块，按照记录最大的值应该在这里，但发现这数据块里的数据已经被清空了，与是利用Btree索引的另一个特点：leaves节点是一个双向列表，若数据没有找到就去临近的一个数据块中看看，在这个数据块中发现了最大值99。 在计算最大值的过程中，这次的定位多加载了一个数据块，再极端的情况下，大批量的数据被删除，就会造成大量访问这些dead leaves。 针对这个问题的一般解决办法是重建索引，但记住! 重建索引并不是最优方案，详细原因可以看看这。使用coalesce语句来整理这些dead leaves到freelist中，就可以避免这些问题。理论上oracle中这步操作是可以自动完成的，但在实际中一次性大量删除数据后，oracle在短时间内是反应不过来的。
一、索引的分类 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 主要索引类型有如下几种： 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、不可见索引 # 设置不可见（invisible），修改为可见（visible） mysql> ALTER TABLE t1 ALTER INDEX idx_u1 INVISIBLE; # 查看不可见索引关键词 KEY `idx_u1` (`u1` DESC) /*!80000 INVISIBLE */ # 解析查询时时全表扫描 mysql> explain select * from t1 order by u1 desc limit 3; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 50184 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ 1 row in set, 1 warning (0.01 sec) # 执行SQL时即便force index也不可用，提示不存在 mysql> explain select * from t1 force index(idx_u1) order by u1 desc limit 3; ERROR 1176 (42000): Key 'idx_u1' doesn't exist in table 't1' 2.12、不可见索引 # 设置不可见（invisible），修改为可见（visible） mysql> ALTER TABLE t1 ALTER INDEX idx_u1 INVISIBLE; # 查看不可见索引关键词 KEY `idx_u1` (`u1` DESC) /*!80000 INVISIBLE */ # 解析查询时时全表扫描 mysql> explain select * from t1 order by u1 desc limit 3; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 50184 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ 1 row in set, 1 warning (0.01 sec) # 执行SQL时即便force index也不可用，提示不存在 mysql> explain select * from t1 force index(idx_u1) order by u1 desc limit 3; ERROR 1176 (42000): Key 'idx_u1' doesn't exist in table 't1' 2.13、空间索引 空间索引是对空间数据类型的字段建立的索引，MYSQL使用SPATIAL关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引。 # 建表 CREATE TABLE `gis_position` ( `id` int NOT NULL, `gis` geometry NOT NULL COMMENT '空间位置信息', `geohash` varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,12)) VIRTUAL, primary key(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='空间位置信息'; # 创建空间索引 mysql> alter table gis_position add SPATIAL KEY `idx_gis` (`gis`) ; # 查看索引关键信息 SPATIAL KEY `idx_gis` (`gis`) 2.14、全文索引 # 创建全文索引 mysql> alter table t1 add fulltext index idx_fulltext_u4(`u4`) with parser ngram; # 查看索引关键信息 FULLTEXT KEY `idx_fulltext_u4` (`u4`) /*!50100 WITH PARSER `ngram` */ 三、索引创建注意点 3.1、最左前缀原则 高版本Mysql，不遵守最左前缀原则，索引也会生效。 但是我们仍应遵守最左前缀原则，以免版本更迭带来的麻烦。 3.2、选择合适的字段 1、不为NULL的字段 索引字段的数据应该尽量不为NULL，因为对于数据为NULL的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为NULL，建议使用0,1,true,false这样语义较为清晰的短值或短字符作为替代。 2、被频繁查询的字段 创建索引的字段应该是查询操作非常频繁的字段。 3、被作为条件查询的字段 被作为WHERE条件查询的字段，应该被考虑建立索引。 4、被经常频繁用于连接的字段 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。 3.2、不合适创建索引的字段 1、被频繁更新的字段应该慎重建立索引 虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。 2、不被经常查询的字段没有必要建立索引 3、尽可能的考虑建立联合索引而不是单列索引 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。 4、注意避免冗余索引 冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。 5、考虑在字符串类型的字段上使用前缀索引代替普通索引 前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
请问如何分辨某个用户是从哪台机器登陆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'; 文件已创建。 sql> create spfile='e:"ora9i"database"spfileeygle.ora' from pfile='e:"ora9i"admin"eygle"pfile"init.ora'; 文件已创建。 如何单独备份一个或多个表？ exp 用户/密码 tables=(表1,…,表2) 如何单独备份一个或多个用户？ exp system/manager owner=(用户1,用户2,…,用户n) file=导出文件 如何对clob字段进行全文检索？ select * from a where dbms_lob.instr(a.a,'k',1,1)>0; 如何显示当前连接用户? show user 如何查看数据文件放置的路径 ? col file_name format a50 sql> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 如何查看现有回滚段及其状态 ? sql> col segment format a30 sql> select segment_name,ccbzzp,tablespace_name,segment_id,file_id,status from dba_rollback_segs 登陆sqlplus sqlplus / as sysdba 查找用户 select * from dba_users; -- 查看所有用户： select * from all_users; -- 查看用户或角色系统权限(直接赋值给用户或角色的系统权限)： select * from dba_sys_privs; select * from user_sys_privs; -- 查看角色(只能查看登陆用户拥有的角色)所包含的权限 select * from role_sys_privs; -- 查看用户对象权限： select * from dba_tab_privs; select * from all_tab_privs; select * from user_tab_privs; -- 查看所有角色： select * from dba_roles; -- 查看用户或角色所拥有的角色： select * from dba_role_privs; select * from user_role_privs; -- 查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限) select * from V$PWFILE_USERS; -- 查看当前有哪些用户正在使用数据 SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine from v$session a, v$sqlarea b where a.sql_address =b.address order by cpu_time/executions desc; 查找工作空间的路径 select * from dba_data_files; 删除当前用户下的所有表 SQL> show parameter deferred_segment_creation NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SQL> alter system set deferred_segment_creation=false; 系统已更改。 SQL> show parameter deferred_segment_creation NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean FALSE SQL> select 'drop table '||table_name||';' from cat where table_type='TABLE'; 删除用户及表空间 -- 删除用户 drop user 用户名称 cascade; -- 删除表空间 drop tablespace 表空间名称 including contents and datafiles cascade constraint; --例如：删除用户名成为LYK，表空间名称为LYK --删除用户，及级联关系也删除掉 drop user LYK cascade; --删除表空间，及对应的表空间文件也删除掉 drop tablespace LYK including contents and datafiles cascade constraint; drop tablespace LYK_TMP including contents and datafiles cascade constraint; 查询数据库连接SID SELECT sid,serial#,username FROM v$session WHERE username = 'SYSTEM'; ALTER SYSTEM KILL SESSION '148, 2329'; 查看数据库连接 --查看数据库连接 SELECT * FROM V$SESSION; --查看数据库的连接数 select count(*) from v$session; --查询oracle的并发连接数 select count(*) from v$session where status='ACTIVE'; --数据库允许的最大连接数 select value from v$parameter where name = 'processes' ; --修改最大连接数: alter system set processes = 300 scope = spfile; --查看不同用户的连接数 select username,count(username) from v$session where username is not null group by username; 查询oracle哪个表被锁 -- 查询oracle那个表被锁 select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id; 查询正在执行的sql -- 查询正在执行的sql select a.program, b.spid, c.sql_text, c.SQL_ID from v$session a, v$process b, v$sqlarea c where a.paddr = b.addr and a.sql_hash_value = c.hash_value and a.username is not null; 查询某PID占用资源 --查询那个PID占用资源 SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value), DECODE(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '26025')) ORDER BY piece ASC; 数据库导入导出 -- 导出数据库时可能操作系统编码和数据库不一致会导致EXP-00091错误， select * from nls_database_parameters t where t.parameter = 'NLS_CHARACTERSET'; select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET'; -- 根据⑴查出的NLS_CHARACTERSET（AL32UTF8）来设定exp的环境变量： set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 echo $NLS_LANG -- 导出数据库 exp HM_DEV/123456 owner=HM_DEV file=HM_DEV_2019_01_17.dmp; exp green/light@MSSPS file=e:\ren.dmp tables=(tab_mobilephone_recharge,trade_detail,terminals,shops,locations,citys) query=\"where rownum<=5000\" -- 导入数据库 imp HM_DEV/123456@orcl file=HM_DEV_2019_01_17.dmp ignore=y FULL=Y; 数据导出 1、 将数据库test完全导出,用户名system 密码manager 导出到d:/daochu.dmp中 exp system/manager@test file=/u01/app/daochu.dmp full=y 2 、将数据库中system用户与sys用户的表导出 exp system/manager@test file=/u01/app/daochu.dmp owner=\(system,sys\); 3 、将数据库中的表inner_notify、notify_staff_relat导出 exp aichannel/aichannel@testdb2 file=/u01/app/newsmgnt.dmp tables=\(inner_notify,notify_staff_relat\); 4 、将数据库中的表table1中的字段filed1以"00"打头的数据导出 exp system/manager@test file=/u01/app/daochu.dmp tables=\(table1\) query=/" where filed1 like 00%/" 上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。 也可以在上面命令后面 加上 compress=y 来实现。 数据导入 1 、将/u01/app/daochu.dmp 中的数据导入 test数据库中。 imp aichannel/aichannel@hust full=y file=/u01/app/newsmgnt.dmp ignore=y 上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。 在后面加上 ignore=y 就可以了。 2 、将/u01/app/aochu.dmp中的表table1 导入 imp system/manager@test file=/u01/appdaochu.dmp tables=(table1) 基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入 解决oracle11g 空表不能exp导出的问题 show parameter deferred_segment_creation NAME TYPE VALUE ------------------------------------ ----------- ------- deferred_segment_creation boolean TRUE -- 数据库参数deferred_segment_creation=true，将导致dmp备份无法导出空表结构； alter system set deferred_segment_creation=false; -- 此方法只对以后的表有效，之前的表还是没分配空间。 -- 1、先查询一下当前用户下的所有空表 ,优先使用segment_created = 'NO' select * from user_tables where segment_created = 'NO'; --select table_name from user_tables where NUM_ROWS=0; -- 2、批量生成修改语句 select 'alter table ' || table_name || ' allocate extent;' from user_tables where segment_created = 'NO'; --select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; -- 3、执行修改语句 alter table 表名 allocate extent; ORACLE删除表 Oracle如果开启了 flash 功能, 当我们删除表drop table HM_TABLE; 后会出现类似BIN$dJ4cxvVNZVHgQAB/AQBx3w==$0表, 这个表还BIN$dJ4cxvVNZVHgQAB/AQBx3w==$0存在，可以使用命令：来恢复至删除前。 -- 来恢复至删除前 flashback table "BIN$dJ4hg1pva6/gQAB/AQByAw==$0" to before drop; commit; -- 如果想彻底删除一个表 drop table HM_TABLE purge; commit; -- 如果想清空flash存储已经删除的表，用命令： purge recyclebin; commit; -- 如果想清除flash中指定的表，可以使用命令： purge table HM_TABLE; commit; 查询用户下的所有表名 select * from user_tab_comments; 查询用户下所有字段名 select * from user_col_comments; 导出当前用户的所有备注（包含表和字段） SELECt 'COMMENT ON TABLE ' || TABLE_NAME || ' IS ' || '''' || nvl(COMMENTS, '') || ''';' from user_tab_comments UNION all SELECt 'COMMENT ON COLUMN ' || TABLE_NAME || '.' || COLUMN_NAME || ' IS ' || '''' || nvl(COMMENTS, '') || ''';' from user_col_comments; Oracle查询SQL语句执行的耗时 select a.sql_text SQL语句, b.etime 执行耗时, c.user_id 用户ID, c.SAMPLE_TIME 执行时间, c.INSTANCE_NUMBER 实例数, u.username 用户名, a.sql_id SQL编号 from dba_hist_sqltext a, (select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime from dba_hist_sqlstat -- where ELAPSED_TIME_DELTA / 1000000 >= 1 ) b, dba_hist_active_sess_history c, dba_users u where a.sql_id = b.sql_id and u.username = 'EIS_DEV' and c.user_id = u.user_id and b.sql_id = c.sql_id and a.sql_text like '%SELECT%' order by SAMPLE_TIME desc, b.etime desc; 执行过的SQL SELECT SQL_ID, HASH_VALUE, ADDRESS, B.SQL_FULLTEXT, B.FIRST_LOAD_TIME FROM V$SQLAREA B ORDER BY B.FIRST_LOAD_TIME DESC; 正在执行的SQL SELECT SSN.USERNAME, SSN.SID, SSN.SQL_ID, SAA.ADDRESS, SAA.HASH_VALUE, SAA.SQL_FULLTEXT FROM V$SESSION SSN, V$SQLAREA SAA WHERE SSN.SQL_ADDRESS = SAA.ADDRESS AND SSN.SQL_HASH_VALUE = SAA.HASH_VALUE; 读取磁盘次数最多的SQL SELECT * FROM (SELECT SQL_ID, ADDRESS, HASH_VALUE, COMMAND_TYPE, PARSING_USER_ID, PARSING_SCHEMA_NAME, EXECUTIONS, SORTS, DISK_READS, BUFFER_GETS, CPU_TIME, SQL_FULLTEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM < 10; 消耗CPU时间最多的SQL SELECT * FROM (SELECT SQL_ID, ADDRESS, HASH_VALUE, COMMAND_TYPE, PARSING_USER_ID, PARSING_SCHEMA_NAME, EXECUTIONS, SORTS, DISK_READS, BUFFER_GETS, CPU_TIME, SQL_FULLTEXT FROM V$SQLAREA ORDER BY CPU_TIME DESC) WHERE ROWNUM < 10; 用户表空间及使用率 select * from ( Select a.tablespace_name, to_char(a.bytes/1024/1024,'99,999.999') total_bytes, to_char(b.bytes/1024/1024,'99,999.999') free_bytes, to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes, to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%'use from ( select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name union all select c.tablespace_name, to_char(c.bytes/1024/1024,'99,999.999') total_bytes, to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes, to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes, to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use from (select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) c, (select tablespace_name,sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) d where c.tablespace_name = d.tablespace_name ) order by tablespace_name; 查看表空间是否为online select * from v$tempfile; 查看普通数据文件是否扩展 select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d; 查看临时表空间是否可以扩展 select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d; 临时表空间和数据表空间 select tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space from dba_temp_files union all select tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space from dba_data_files; 修改数据表空间大小 alter database datafile '/u01/app/oracle/product/11.2.0/db_1/EIS_QA.dbf' resize 2048m; 删除表空间 alter database datafile '/u01/app/oracle/product/11.2.0/db_1/EIS.dbf' drop; 修改临时表空间大小 alter database tempfile '/u01/app/oracle/product/11.2.0/db_1/EIS_TEMP_01.dbf' drop; 临时表空间添加新的临时数据文件 alter tablespace EIS add tempfile '/u01/app/oracle/product/11.2.0/db_1/EIS_ADD_01.dbf' size 400m; 临时文件自动扩展 alter database tempfile '/u01/app/oracle/product/11.2.0/db_1/EIMS_TMP.dbf' autoextend on next 5m maxsize unlimited; 关闭(启动)临时文件的自动增长 alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' autoextend off(on); oracle中用户删除不了，ORA-01940提示 “无法删除当前已连接用户” 先锁定用户、然后查询进程号，最后删除对应的进程、在删除对应的用户 alter user EIS account lock; SELECT * FROM V$SESSION WHERE USERNAME='EIS'； alter system kill session 'xx, xx' drop user xx cascade Oracle 表添加索引 -- 查询索引 select index_name from all_indexes where table_name = 'BELL_ENT_UPDATE'; -- 创建索引 create index bell_ent_update_subid_idx on BELL_ENT_UPDATE(SUB_ID); -- 删除索引 drop index bell_ent_update_subid_idx; 参考资料： https://www.cnblogs.com/Im-Victor/p/7795192.html
根据库名获取所有表的信息 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 的主从复制是依赖于 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个为宜。 大事务：如果一个事务执行就要 10 分钟，那么主库执行完后，给到从库执行，最后这个事务可能就会导致从库延迟 10 分钟啦。日常开发中，不要一次性 delete 太多 SQL，需要分批进行，另外大表的 DDL 语句，也会导致大事务。 网络延迟：优化网络，比如带宽 20M 升级到 100M。 MySQL 版本低：低版本的 MySQL 只支持单线程复制，如果主库并发高，来不及传送到从库，就会导致延迟，可以换用更高版本的 MySQL，支持多线程复制。 3.3、主从延迟解决方案 一般会把从库落后的时间作为一个重点的数据库指标做监控和报警，正常的时间是在毫秒级别，一旦落后的时间达到了秒级别就需要告警了。 解决该问题的方法，除了缩短主从延迟的时间，还有一些其它的方法，基本原理都是尽量不查询从库，具体解决方案如下： 使用缓存：我们在同步写数据库的同时，也把数据写到缓存，查询数据时，会先查询缓存，不过这种情况会带来 MySQL 和 Redis 数据一致性问题。 查询主库：直接查询主库，这种情况会给主库太大压力，不建议这种方式。 数据冗余：对于一些异步处理的场景，如果只扔数据 ID，消费数据时，需要查询从库，我们可以把数据全部都扔给消息队列，这样消费者就无需再查询从库。 在实际应用场景中，对于一些非常核心的场景，比如库存，支付订单等，需要直接查询主库，其它非核心场景，就不要去查主库了。
一、巡检内容 表巡检 大小超过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): try: sql = "select t1.name,t2.num from information_schema.innodb_sys_tables t1, (select table_id,count(*) as num from \ information_schema.innodb_sys_indexes group by table_id having count(*) >=6) t2 where t1.table_id =t2.table_id" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查索引超过6个的表') if not result: print("结果不存在") for row in result: print() print(("schema: %s tablename: %s index_num: %s") % (row[0].split('/')[0], row[0].split('/')[1], row[1])) except Exception as e: raise (e) def check_table_fragment_pct(self): try: sql = "SELECT TABLE_SCHEMA as `db`, TABLE_NAME as `tbl`, \ 1-(TABLE_ROWS*AVG_ROW_LENGTH)/(DATA_LENGTH + INDEX_LENGTH + DATA_FREE) AS `fragment_pct` \ FROM information_schema.TABLES WHERE TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys') \ and (1-(TABLE_ROWS*AVG_ROW_LENGTH)/(DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) > 0.5 and (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) > 1024*1024*1024 ;" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查碎片率超过50%的表') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s fragment_pct: %s") % (row[0], row[1], row[2])) except Exception as e: raise (e) def check_table_rows(self): try: sql = "select table_schema,table_name,table_rows from \ information_schema.TABLES where table_schema not in ('information_schema','mysql','performance_schema','sys') \ and table_rows > 10000000 order by table_rows desc;" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查行数超过1000万行的表') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s rows: %s") % (row[0], row[1], row[2])) except Exception as e: raise (e) def check_table_chaset(self): try: self._cursor.execute("show variables like 'character_set_server';") default_charset = str(self._cursor.fetchone()[1]) default_charset = default_charset + "_general_ci" sql = "select table_schema,table_name,table_collation from information_schema.tables where table_schema not \ in ('information_schema','mysql','performance_schema','sys') and table_collation !='" + default_charset + "';" result = self._cursor.fetchall() print('检查非默认字符集的表') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s fragment_pct: %s") % (row[0], row[1], row[2])) except Exception as e: raise (e) def check_table_big_columns(self): try: sql = "select table_schema,table_name,column_name,data_type from information_schema.columns where data_type in \ ('blob','clob','text','medium text','long text') and table_schema not in \ ('information_schema','performance_schema','mysql','sys')" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查含大字段的表') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s column_name: %s data_type: %s") % (row[0], row[1], row[2], row[3])) except Exception as e: raise (e) def check_table_long_varchar(self): try: sql = "select table_schema,table_name,column_name,data_type,CHARACTER_MAXIMUM_LENGTH from information_schema.columns \ where DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH > 500 and table_schema not in \ ('information_schema','performance_schema','mysql','sys');" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查varchar定义长的表') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s column_name: %s data_type: %s(%s)") % ( row[0], row[1], row[2], row[3], row[4])) except Exception as e: raise (e) def check_table_no_index(self): try: sql = "SELECT t.table_schema,t.table_name FROM information_schema.tables AS t LEFT JOIN \ (SELECT DISTINCT table_schema, table_name FROM information_schema.`KEY_COLUMN_USAGE` ) AS kt ON \ kt.table_schema=t.table_schema AND kt.table_name = t.table_name WHERE t.table_schema NOT IN \ ('mysql', 'information_schema', 'performance_schema', 'sys') AND kt.table_name IS NULL;" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查无主键/索引的表') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s") % (row[0], row[1])) except Exception as e: raise (e) def check_index_redundant(self): try: sql = "select table_schema,table_name,redundant_index_name,redundant_index_columns from \ sys.schema_redundant_indexes group by table_schema,table_name,redundant_index_name,redundant_index_columns;" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查重复索引') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s redundant_index_name：%s redundant_index_columns:%s ") % ( row[0], row[1], row[2], row[3])) except Exception as e: raise (e) def check_index_columns(self): try: sql = "select s.table_schema,s.table_name,s.index_name,s.column_name from information_schema.STATISTICS s,\ (select table_name,index_name,count(*) from information_schema.STATISTICS where table_schema not in \ ('information_schema','performance_schema','mysql','sys') group by table_name,index_name having count(*)>5)t where \ s.table_name=t.table_name and s.index_name=t.index_name;" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查索引列超过5个的索引') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s index_name：%s column_name:%s ") % (row[0], row[1], row[2], row[3])) except Exception as e: raise (e) def check_index_unused(self): try: sql = "select * from sys.schema_unused_indexes;" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查无用的索引') if not result: print("结果不存在") for row in result: print(("schema: %s tablename: %s indexname：%s") % (row[0], row[1], row[2])) except Exception as e: raise (e) def check_important_variables(self): print('检查重要参数') variables_list = ['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'] for variable in variables_list: try: sql = ("show global variables like '%s'" % variable) self._cursor.execute(sql) result = self._cursor.fetchone()[1] print(('%s : %s') % (variable, result)) except Exception as e: raise (e) def check_important_status(self): print('检查重要状态') status_list = ['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', 'Innodb_row_lock_waits', 'Innodb_row_lock_time_avg', 'Binlog_cache_disk_use', 'Created_tmp_disk_tables'] for status in status_list: try: sql = ("show global status like '%s'" % status) self._cursor.execute(sql) result = self._cursor.fetchone()[1] print(('%s : %s') % (status, result)) except Exception as e: raise (e) self._cursor.execute("show engine innodb status") innodb_status = self._cursor.fetchall() innodb_status_format = str(innodb_status).split('\\n') for item in innodb_status_format: if "Log sequence number" in item: logsequencenumber = item.split(' ')[3] print(('%s : %s') % ('Log sequence number', logsequencenumber)) if "Log flushed up to" in item: logflushnumber = item.split(' ')[6] print(('%s : %s') % ('Log flushed up to', logflushnumber)) if "Last checkpoint at" in item: checkpoint = item.split(' ')[4] print(('%s : %s') % ('Last checkpoint at', checkpoint)) if "History list length" in item: historylength = item.split(' ')[3] print(('%s : %s') % ('historylength', historylength)) def check_user_nopass(self): try: sql = "select user,host from mysql.user where authentication_string='';" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查无密码用户') if not result: print("结果不存在") for row in result: print(("user: %s host: %s") % (row[0], row[1])) except Exception as e: raise (e) def check_user_nowhere(self): try: sql = "select user,host from mysql.user where host='%';" self._cursor.execute(sql) result = self._cursor.fetchall() print('检查%用户') if not result: print("结果不存在") for row in result: print(("user: %s host: %s") % (row[0], row[1])) except Exception as e: raise (e) def check_user_privileges(self): try: sql = "select user,host from mysql.user where user not in ('mysql.session','mysql.sys');" self._cursor.execute(sql) result = self._cursor.fetchall() user_list = [] for row in result: user_list.append("'" + row[0] + "'" + "@" + "'" + row[1] + "'") print('检查用户权限') for user in user_list: sql = "show grants for %s;" % user # print(sql) self._cursor.execute(sql) result = self._cursor.fetchall() for row in result: print(row[0]) except Exception as e: raise (e) if __name__ == '__main__': with DBUtil('root', 'root', '127.0.0.1', 3306, 'information_schema') as client: client.check_table_size() client.check_table_index() client.check_table_fragment_pct() client.check_table_rows() client.check_table_chaset() client.check_table_big_columns() client.check_table_long_varchar() client.check_table_no_index() client.check_index_redundant() client.check_index_columns() client.check_index_unused() client.check_important_variables() client.check_important_status() client.check_user_nopass() client.check_user_nowhere() client.check_user_privileges()
事务管理在系统开发中是不可缺少的一部分，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、在事务中有远程调用，就会拉长整个事务。那么久会导致本事务的数据库连接一直被占用，那么如果类似操作过多，就会导致数据库连接池耗尽。 有些时候，即使没有在事务中进行远程操作，但是有些人还是可能会不经意的进行一些内存操作，如运算。或者如果遇到分库分表的情况，有可能不经意间进行跨库操作。 但是如果是编程式事务的话，业务代码中就会清清楚楚看到什么地方开启事务，什么地方提交，什么时候回滚。这样有人改这段代码的时候，就会强制他考虑要加的代码是否应该方法事务内。 3.3、声明式事务用不对容易失效 除了事务的粒度问题，还有一个问题那就是声明式事务虽然看上去帮我们简化了很多代码，但是一旦没用对，也很容易导致事务失效。 如以下几种场景就可能导致声明式事务失效： @Transactional 应用在非 public 修饰的方法上。 @Transactional 注解属性 propagation 设置错误。 @Transactional 注解属性 rollbackFor 设置错误。 同一个类中方法调用，导致 @Transactional 失效。 异常被catch捕获导致 @Transactional 失效。 数据库引擎不支持事务。 以上几个问题，如果使用编程式事务的话，很多都是可以避免的。 因为Spring的事务是基于 AOP 实现的，但是在代码中，有时候我们会有很多切面，不同的切面可能会来处理不同的事情，多个切面之间可能会有相互影响。 四、事务特性 原子性（Atomicity）：事务是一个原子操作，由一系列动作组成。事务的原子性确保动作要么全部完成，要么完全不起作用； 一致性（Consistency）：一旦事务完成（不管是成功还是失败），系统必须确保它所建模的业务处于一致的状态，而不会是部分完成部分失败。在现实中的数据不应该被破坏； 隔离性（Isolation）：可能有许多事务会同时处理相同的数据，因此每个事务都应该与其他事务隔离开来，防止数据损坏； 持久性（Durability）：一旦事务完成，无论发生什么系统错误，它的结果都不应该受到影响，这样就能从任何系统崩溃中恢复过来。通常情况下，事务的结果被写到持久化存储器中；
十、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、获取sqlSessionFactory对象: * 解析文件的每一个信息保存在Configuration中，返回包含Configuration的DefaultSqlSession； * 注意：【MappedStatement】：代表一个增删改查的详细信息 * * 2、获取sqlSession对象 * 返回一个DefaultSQlSession对象，包含Executor和Configuration; * 这一步会创建Executor对象； * * 3、获取接口的代理对象（MapperProxy） * getMapper，使用MapperProxyFactory创建一个MapperProxy的代理对象 * 代理对象里面包含了，DefaultSqlSession（Executor） * 4、执行增删改查方法 * * 总结： * 1、根据配置文件（全局，sql映射）初始化出Configuration对象 * 2、创建一个DefaultSqlSession对象， * 他里面包含Configuration以及 * Executor（根据全局配置文件中的defaultExecutorType创建出对应的Executor） * 3、DefaultSqlSession.getMapper（）：拿到Mapper接口对应的MapperProxy； * 4、MapperProxy里面有（DefaultSqlSession）； * 5、执行增删改查方法： * 1）、调用DefaultSqlSession的增删改查（Executor）； * 2）、会创建一个StatementHandler对象。 * （同时也会创建出ParameterHandler和ResultSetHandler） * 3）、调用StatementHandler预编译参数以及设置参数值; * 使用ParameterHandler来给sql设置参数 * 4）、调用StatementHandler的增删改查方法； * 5）、ResultSetHandler封装结果 * 注意： * 四大对象每个创建的时候都有一个interceptorChain.pluginAll(parameterHandler); * * @throws IOException */ @Test public void test01() throws IOException { // 1、获取sqlSessionFactory对象 SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); // 2、获取sqlSession对象 SqlSession openSession = sqlSessionFactory.openSession(); try { // 3、获取接口的实现类对象 //会为接口自动的创建一个代理对象，代理对象去执行增删改查方法 EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); Employee employee = mapper.getEmpById(1); System.out.println(mapper); System.out.println(employee); } finally { openSession.close(); } } /** * 插件原理 * 在四大对象创建的时候 * 1、每个创建出来的对象不是直接返回的，而是 * interceptorChain.pluginAll(parameterHandler); * 2、获取到所有的Interceptor（拦截器）（插件需要实现的接口）； * 调用interceptor.plugin(target);返回target包装后的对象 * 3、插件机制，我们可以使用插件为目标对象创建一个代理对象；AOP（面向切面） * 我们的插件可以为四大对象创建出代理对象； * 代理对象就可以拦截到四大对象的每一个执行； * public Object pluginAll(Object target) { for (Interceptor interceptor : interceptors) { target = interceptor.plugin(target); } return target; } */ 全局配置文件mybatis-config.xml中配置注册 插件 <plugins> <plugin interceptor="com.atguigu.mybatis.dao.MyFirstPlugin"> <property name="username" value="root"/> <property name="password" value="root"/> </plugin> <plugin interceptor="com.atguigu.mybatis.dao.MySecondPlugin"></plugin> </plugins> 输出结果： 插件配置的信息：{password=root, username=root} MyFirstPlugin...plugin:mybatis将要包装的对象org.apache.ibatis.executor.CachingExecutor@56ac3a89 MySecondPlugin...plugin:org.apache.ibatis.executor.CachingExecutor@56ac3a89 MyFirstPlugin...plugin:mybatis将要包装的对象org.apache.ibatis.scripting.defaults.DefaultParameterHandler@3632be31 MySecondPlugin...plugin:org.apache.ibatis.scripting.defaults.DefaultParameterHandler@3632be31 MyFirstPlugin...plugin:mybatis将要包装的对象org.apache.ibatis.executor.resultset.DefaultResultSetHandler@13a5fe33 MySecondPlugin...plugin:org.apache.ibatis.executor.resultset.DefaultResultSetHandler@13a5fe33 MyFirstPlugin...plugin:mybatis将要包装的对象org.apache.ibatis.executor.statement.RoutingStatementHandler@370736d9 MySecondPlugin...plugin:org.apache.ibatis.executor.statement.RoutingStatementHandler@370736d9 DEBUG 09-29 21:10:52,348 ==> Preparing: select id,last_name lastName,email,gender from tbl_employee where id = ? (BaseJdbcLogger.java:145) MySecondPlugin...intercept:public abstract void org.apache.ibatis.executor.statement.StatementHandler.parameterize(java.sql.Statement) throws java.sql.SQLException MyFirstPlugin...intercept:public abstract void org.apache.ibatis.executor.statement.StatementHandler.parameterize(java.sql.Statement) throws java.sql.SQLException 当前拦截到的对象：org.apache.ibatis.executor.statement.RoutingStatementHandler@370736d9 sql语句用的参数是：1 DEBUG 09-29 21:10:52,372 ==> Parameters: 3(Integer) (BaseJdbcLogger.java:145) DEBUG 09-29 21:10:52,391 <== Total: 1 (BaseJdbcLogger.java:145) org.apache.ibatis.binding.MapperProxy@33723e30 Employee [id=3, lastName=jerry4, email=null, gender=1] 10.2、插件原理 1）、按照插件注解声明，按照插件配置顺序调用插件plugin方 法，生成被拦截对象的动态代理 2）、多个插件依次生成目标对象的代理对象，层层包裹，先声 明的先包裹；形成代理链 3）、目标方法执行时依次从外到内执行插件的intercept方法。 4）、多个插件情况下，我们往往需要在某个插件中分离出目标对象。可以借助MyBatis提供的SystemMetaObject类来进行获 取最后一层的h以及target属性的值。 10.3、Interceptor接口 Intercept：拦截目标方法执行。 plugin：生成动态代理对象，可以使用MyBatis提 供的Plugin类的wrap方法。 setProperties：注入插件配置时设置的属性。 十一、扩展：MyBatis实用场景 1）、PageHelper插件进行分页 2）、批量操作 3）、存储过程 4）、typeHandler处理枚举 11.1、PageHelper插件进行分页 PageHelper是MyBatis中非常方便的第三方分页 插件。 官方文档： https://github.com/pagehelper/MybatisPageHelper/blob/master/README_zh.md 使用步骤： 1、导入相关包pagehelper-x.x.x.jar 和 jsqlparser0.9.5.jar。 2、在MyBatis全局配置文件中配置分页插件。 3、使用PageHelper提供的方法进行分页。 4、可以使用更强大的PageInfo封装返回结果。 11.2、批量操作 默认的 openSession() 方法没有参数,它会创建有如下特性的会开启一个事务(也就是不自动提交) 连接对象会从由活动环境配置的数据源实例得到。 事务隔离级别将会使用驱动或数据源的默认设置。 预处理语句不会被复用，也不会批量处理更新。 openSession 方法的 ExecutorType类型的参数，枚举类型: ExecutorType.SIMPLE: 这个执行器类型不做特殊的事情（这是默认装配 的）。它为每个语句的执行创建一个新的预处理语句。 ExecutorType.REUSE: 这个执行器类型会复用预处理语句。 ExecutorType.BATCH: 这个执行器会批量执行所有更新语句。 批量操作我们是使用MyBatis提供的BatchExecutor进行的，他的底层就是通过jdbc攒sql的方式进行的。我们可以让他攒够一定数量后发给数据库一次。 @Test public void testBatch() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); //可以执行批量操作的sqlSession SqlSession openSession = sqlSessionFactory.openSession(ExecutorType.BATCH); long start = System.currentTimeMillis(); try{ EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); for (int i = 0; i < 10000; i++) { mapper.addEmp(new Employee(UUID.randomUUID().toString().substring(0, 5), "b", "1")); } openSession.commit(); long end = System.currentTimeMillis(); //批量：（预编译sql一次==>设置参数===>10000次===>执行（1次）） //Parameters: 616c1(String), b(String), 1(String)==>4598 //非批量：（预编译sql=设置参数=执行）==》10000 10200 System.out.println("执行时长："+(end-start)); }finally{ openSession.close(); } } 与Spring整合中，我们推荐，额外的配置一个可以专门用来执行批量操作的sqlSession。 需要用到批量操作的时候，我们可以注入配置的这个批量 SqlSession。通过他获取到mapper映射器进行操作。 注意： 1、批量操作是在session.commit()以后才发送sql语句给数、据库进行执行的。 2、如果我们想让其提前执行，以方便后续可能的查询操作 获取数据，我们可以使用sqlSession.flushStatements()方法，让其直接冲刷到数据库进行执行。 11.3、存储过程 实际开发中，我们通常也会写一些存储过程， MyBatis也支持对存储过程的调用。 一个最简单的存储过程： delimiter $$ create procedure test() begin select 'hello'; end $$ delimiter ; 存储过程的调用 1、select标签中 statementType=“CALLABLE” 2、标签体中调用语法： {call procedure_name(#{param1_info},#{param2_info})} 存储过程过-游标处理 MyBatis对存储过程的游标提供了一个JdbcType=CURSOR的支持， 可以智能的把游标读取到的数据，映射到我们声明的结果集中。 调用实例： {call hello_test( #{start,mode=IN,jdbcType=INTEGER}, #{end,mode=IN,jdbcType=INTEGER}, #{count,mode=OUT,jdbcType=INTEGER}, #{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=PageEmp} )} <resultMap type="com.atguigu.mybatis.bean.Employee" id="PageEmp"> <id column="EMPLOYEE_ID" property="id"/> <result column="LAST_NAME" property="email"/> <result column="EMAIL" property="email"/> </resultMap> 测试代码： /** * oracle分页： * 借助rownum：行号；子查询； * 存储过程包装分页逻辑 * @throws IOException */ @Test public void testProcedure() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); OraclePage page = new OraclePage(); page.setStart(1); page.setEnd(5); mapper.getPageByProcedure(page); System.out.println("总记录数："+page.getCount()); System.out.println("查出的数据："+page.getEmps().size()); System.out.println("查出的数据："+page.getEmps()); }finally{ openSession.close(); } } OraclePage： /** * 封装分页查询数据 * @author lfy * */ public class OraclePage { private int start; private int end; private int count; private List<Employee> emps; } 存储过程： create or replace procedure hello_test(p_start in int, p_end in int, P_count out int, ref_cur out sys_refcursor) AS BEGIN select count(*) into p_count from emp; open ref_cur for select * from (select e.*,rownum as r1 from emp e where rownum<p_end)where r1>p_start; END hello_test; 11.4、自定义TypeHandler处理枚举 可以通过自定义TypeHandler的形式来在设置参数或 者取出结果集的时候自定义参数封装策略。 步骤： 1、实现TypeHandler接口或者继承BaseTypeHandler。 2、使用@MappedTypes定义处理的java类型 使用@MappedJdbcTypes定义jdbcType类型。 3、在自定义结果集标签或者参数处理的时候声明使用自定义 TypeHandler进行处理 或者在全局配置TypeHandler要处理的javaType。 测试实例 1、一个枚举类 /** * 希望数据库保存的是100,200这些状态码，而不是默认0,1或者枚举的名 * @author lfy * */ public enum EmpStatus { LOGIN(100,"用户登录"),LOGOUT(200,"用户登出"),REMOVE(300,"用户不存在"); private Integer code; private String msg; private EmpStatus(Integer code,String msg){ this.code = code; this.msg = msg; } public Integer getCode() { return code; } public void setCode(Integer code) { this.code = code; } public String getMsg() { return msg; } public void setMsg(String msg) { this.msg = msg; } //按照状态码返回枚举对象 public static EmpStatus getEmpStatusByCode(Integer code){ switch (code) { case 100: return LOGIN; case 200: return LOGOUT; case 300: return REMOVE; default: return LOGOUT; } } } 2、配置 <typeHandlers> <typeHandler handler="com.atguigu.mybatis.typehandler.MyEnumEmpStatusTypeHandler" javaType="com.atguigu.mybatis.bean.EmpStatus"/> <!--2、也可以在处理某个字段的时候告诉MyBatis用什么类型处理器 保存：#{empStatus,typeHandler=xxxx} 查询： <resultMap type="com.atguigu.mybatis.bean.Employee" id="MyEmp"> <id column="id" property="id"/> <result column="empStatus" property="empStatus" typeHandler=""/> </resultMap> 注意：如果在参数位置修改TypeHandler，应该保证保存数据和查询数据用的TypeHandler是一样的。 --> </typeHandlers> 3、测试代码 /** * 1、实现TypeHandler接口。或者继承BaseTypeHandler * @author lfy */ public class MyEnumEmpStatusTypeHandler implements TypeHandler<EmpStatus> { /** * 定义当前数据如何保存到数据库中 */ @Override public void setParameter(PreparedStatement ps, int i, EmpStatus parameter, JdbcType jdbcType) throws SQLException { // TODO Auto-generated method stub System.out.println("要保存的状态码："+parameter.getCode()); ps.setString(i, parameter.getCode().toString()); } @Override public EmpStatus getResult(ResultSet rs, String columnName) throws SQLException { // TODO Auto-generated method stub //需要根据从数据库中拿到的枚举的状态码返回一个枚举对象 int code = rs.getInt(columnName); System.out.println("从数据库中获取的状态码："+code); EmpStatus status = EmpStatus.getEmpStatusByCode(code); return status; } @Override public EmpStatus getResult(ResultSet rs, int columnIndex) throws SQLException { // TODO Auto-generated method stub int code = rs.getInt(columnIndex); System.out.println("从数据库中获取的状态码："+code); EmpStatus status = EmpStatus.getEmpStatusByCode(code); return status; } @Override public EmpStatus getResult(CallableStatement cs, int columnIndex) throws SQLException { // TODO Auto-generated method stub int code = cs.getInt(columnIndex); System.out.println("从数据库中获取的状态码："+code); EmpStatus status = EmpStatus.getEmpStatusByCode(code); return status; } } 测试代码： /** * 默认mybatis在处理枚举对象的时候保存的是枚举的名字：EnumTypeHandler * 改变使用：EnumOrdinalTypeHandler： * @throws IOException */ @Test public void testEnum() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try{ EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); Employee employee = new Employee("test_enum", "enum@atguigu.com","1"); mapper.addEmp(employee); System.out.println("保存成功"+employee.getId()); openSession.commit(); // Employee empById = mapper.getEmpById(30026); // System.out.println(empById.getEmpStatus()); }finally{ openSession.close(); } }