- 浏览: 1598205 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
jsrgzhangzhiyong:
关于null值的转换还是感觉不太友好,就像 mapstruct ...
我也造了个轮子:BeanMapping(属性拷贝) -
he037:
a417930422 写道引用使用EPHEMERAL会引出一个 ...
基于zookeeper的分布式lock实现 -
seancheer:
qianshangding 写道首先节点启动后,尝试读取本地的 ...
zookeeper学习记录三(session,watcher,persit机制) -
雪夜归人:
您好,我想咨询一下,开源的canal都能支持mysql的哪些版 ...
Canal BinlogChange(mysql5.6) -
zhoudengyun:
copy 一份做记录,后续学习,请知悉
阿里巴巴开源项目: 基于mysql数据库binlog的增量订阅&消费
背景
今天早上,领导给了我一个任务:在老的系统运行中,DBA反馈说获取database TableMeta操作有点慢,让我分析下基于oracle driver驱动是否可以做下优化。由此引出了本文,仅仅做一个记录。
内容
在补充几点背景知识:
1. 老系统介绍
- 老系统主要负责的业务是做跨机房之间的数据库记录同步,需要获取数据库的table meta信息,进行构造对应的sql。将源数据的columns变化,通过sql方式更新到目标库上。
- table meta信息分析时,需要获取table的字段,主键,需要支持视图,同义词等表查询
2. table meta操作原理
jdbcTemplate.execute(new ConnectionCallback() { public Object doInConnection(Connection c) throws SQLException, DataAccessException { DatabaseMetaData meta = c.getMetaData(); meta.getTables(catalog, schemaPattern, tableNamePattern, types); meta.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern); meta.getPrimaryKeys(catalog, schema, table); return null; } });
简单一点说,就是利用java.sql.DatabaseMetaData接口中定义的meta信息获取接口进行处理。
mysql/oracle实现
oracle实现(oracle.jdbc.driver.OracleDatabaseMetaData):
1. getTables
主要是通过构造对应的SQL进行查询,主要是关联了all_objects 和 all_tab_comments, all_synonyms
SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS table_name, o.object_type AS table_type, c.comments AS remarks FROM all_objects o, all_tab_comments c WHERE o.owner LIKE #schema# ESCAPE '/' AND o.object_name LIKE #table# ESCAPE '/' AND o.object_type IN ('TABLE', 'SYNONYM', 'VIEW') AND o.owner = c.owner (+) AND o.object_name = c.table_name (+) UNION SELECT NULL AS table_cat, s.owner AS table_schem, s.synonym_name AS table_name, 'SYNONYM' AS table_table_type, c.comments AS remarks FROM all_synonyms s, all_objects o, all_tab_comments c WHERE s.owner LIKE #schema# ESCAPE '/' AND s.synonym_name LIKE #table# ESCAPE '/' AND s.table_owner = o.owner AND s.table_name = o.object_name AND o.object_type IN ('TABLE', 'VIEW') AND o.owner = c.owner (+) AND o.object_name = c.table_name (+) ORDER BY table_type, table_schem, table_name
注意一下#schema# , #table#的替换,可以使用%进行模糊匹配
2. getColumns
主要是通过构造对应的SQL进行查询,主要关联了all_tab_comments, all_synonyms, all_col_comments
SELECT NULL AS table_cat, DECODE(s.table_owner, NULL, t.owner, s.table_owner) AS table_schem, DECODE(s.synonym_name, NULL, t.table_name, s.synonym_name) AS table_name, t.column_name AS column_name, DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3, 'LONG', -1, 'DATE', 93 , 'RAW', -3, 'LONG RAW', -4, 'BLOB', 2004, 'CLOB', 2005, 'BFILE', -13, 'FLOAT', 6, 'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', -101, 'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102, 'INTERVAL YEAR(2) TO MONTH', -103, 'INTERVAL DAY(2) TO SECOND(6)', -104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS data_type, t.data_type AS type_name, DECODE (t.data_precision, null, t.data_length, t.data_precision) AS column_size, 0 AS buffer_length, t.data_scale AS decimal_digits, 10 AS num_prec_radix, DECODE (t.nullable, 'N', 0, 1) AS nullable, c.comments AS remarks, t.data_default AS column_def, 0 AS sql_data_type, 0 AS sql_datetime_sub, t.data_length AS char_octet_length, t.column_id AS ordinal_position, DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable FROM all_tab_columns t , all_col_comments c , all_synonyms s WHERE (t.owner LIKE #schema# ESCAPE '/' OR (s.owner LIKE #schema# ESCAPE '/' AND t.owner = s.table_owner)) AND (t.table_name LIKE #table# ESCAPE '/' OR s.synonym_name LIKE #table# ESCAPE '/') AND t.column_name LIKE #column# ESCAPE '/' AND t.owner = c.owner (+) AND t.table_name = c.table_name (+) AND t.column_name = c.column_name (+) AND s.table_name (+) = t.table_name AND ((DECODE(s.owner, t.owner, 'OK','PUBLIC', 'OK',NULL, 'OK','NOT OK') = 'OK') OR (s.owner LIKE 'SRF' AND t.owner = s.table_owner)) ORDER BY table_schem, table_name, ordinal_position
注意一下#schema# , #table# , #column#的替换,可以使用%进行模糊匹配
3. getPrimaryKeys
主要是通过构造对应的SQL进行查询,主要关联了 all_cons_columns, all_constraints
SELECT NULL AS table_cat, c.owner AS table_schem, c.table_name, c.column_name, c.position AS key_seq, c.constraint_name AS pk_name FROM all_cons_columns c, all_constraints k WHERE k.constraint_type = 'P' AND k.table_name = #table# AND k.owner like #schema# escape '/' AND k.constraint_name = c.constraint_name AND k.table_name = c.table_name AND k.owner = c.owner ORDER BY column_name
注意一下#schema# , #table# 的替换,可以使用%进行模糊匹配
mysql实现:
有两种获取meta信息的方式
- 使用sql语法查询对应的INFORMATION_SCHEMA信息
SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE='BASE TABLE' THEN 'TABLE' WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE #schema# AND TABLE_NAME LIKE #table# AND TABLE_TYPE IN ('BASE TABLE','VIEW','TEMPORARY') ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
- 使用show命令查询对应的meta信息
SHOW TABLES from retl like 'columns'; show full columns from columns from retl like 'text%' ;
具体的getTables,getColumns,getPrimaryKeys的实现就不一一贴了,有兴趣的可以自己去看看
优化方案
目前我们新老系统分别使用了ddlutils和schemacrawler两种tablemeta分析方案,最终都是基于DatabaseMetaData进行数据获取。
- ddlutils:版本1.0(比较早的版本,目前最新为1.3)
- schemacrawler:版本8.7
两者在实现上没有本质的区别,只不过在schemacrawler在meta信息的获取上可自定义性更强,比如你只关注table,不关注columns,primarykeys,foreignkey等,都可以通过SchemaCrawlerOptions进行指定
schemaCrawler例子:
Connection connection = dataSource.getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); String nameSpace = dataMedia.getNamespace(); String name = dataMedia.getName(); if (databaseMetaData.storesUpperCaseIdentifiers()) {// 识别大小写 nameSpace = nameSpace.toUpperCase(); name = name.toUpperCase(); } if (databaseMetaData.storesLowerCaseIdentifiers()) { nameSpace = nameSpace.toLowerCase(); name = name.toLowerCase(); } final SchemaCrawlerOptions options = new SchemaCrawlerOptions(); options.setSchemaInfoLevel(SchemaInfoLevel.standard()); options.setSchemaInclusionRule(new InclusionRule(nameSpace, InclusionRule.NONE)); options.setTableInclusionRule(new InclusionRule(nameSpace + "." + name, InclusionRule.NONE)); Database database = SchemaCrawlerUtility.getDatabase(connection, options); Schema[] schemas = database.getSchemas(); for(Schema schema : schemas) { for (Table table: Schema.getTables()) { Column[] columns = table.getColumns(); } }
分析本质,主要还是调用DatabaseMediaData进行操作
MetadataResultSet results = null; results = new MetadataResultSet(getMetaData() .getTables(unquotedName(catalogName), unquotedName(schemaName), tableNamePattern, TableType.toStrings(tableTypes)));// 调用getTables方法 while (results.next()) { // "TABLE_CAT", "TABLE_SCHEM" final String tableName = quotedName(results.getString("TABLE_NAME")); final TableType tableType = results.getEnum("TABLE_TYPE",TableType.unknown); final String remarks = results.getString("REMARKS"); final MutableSchema schema = lookupSchema(catalogName, schemaName); ..... if (tableInclusionRule.include(table.getFullName())) { table.setType(tableType); table.setRemarks(remarks); schema.addTable(table); } }
通过代码分析,可以看到获取meta信息的方式,总共有3次SQL查询.
- 先获取匹配的表 getTables
- 对应的所有字段 getColumns
- 获取字段的主键信息 getPrimaryKeys
因此总结一下优化方案:
- 因为我们是精确的table匹配,所以第一次的匹配表查询SQL可以避免。如果需要优化需要copy schemacrawl的部分代码进行优化。(少一次SQL查询,不过会给代码带来一定的维护成本)
- oracle driver中针对同义词表的查询,在整个查询过程中都会去关联all_synonyms表,影响查询性能。(不过后续otter4.0上线后,可以支持非同名表的查询,以后可以逐步废弃同义词表的使用,从而优化meta信息的查询)
- oracle/mysql driver在查询所有字段上都支持批量查询多个表,即意味着我们可以一次性查询相同schema下的所有同步表的信息。 (调整有一定的成本,需要完全自己解析ResultSet的结果对象,支持将Result解析为多个 Table)
最后
本文可能对他人借鉴意义并不是非常大,只为自己做一下记录,项目第一个版本上线后再来做一下对应的优化方案。优化无止境,fighting!!!!!
发表评论
-
yugong QuickStart
2016-03-05 01:52 0几点说明 a. 数据迁移的方案可参见设计文档,oracl ... -
阿里巴巴开源项目: 阿里巴巴去Oracle数据迁移同步工具
2016-03-05 18:29 6551背景 08年左右,阿里巴巴开始尝试MySQL的相关 ... -
愚公performance
2016-03-02 17:29 0性能测试 全量测试 场景1 (单主键, ... -
yugong AdminGuide
2016-03-02 16:40 0环境要求 操作系统 数据库 迁移方案 部署 ... -
Tddl_hint
2014-01-27 13:52 0背景 工作原理 Hint格式 direct模 ... -
tddl5分库规则
2014-01-26 14:41 0背景 工作原理 构建语法树 元数据 基于 ... -
tddl5优化器
2014-01-22 15:12 0背景 工作原理 构建语法树 元数据 抽象语 ... -
Canal BinlogChange(mariadb5/10)
2014-01-20 17:25 4633背景 先前开源了一个 ... -
asynload quickstart
2013-10-08 22:49 0几点说明: 1. asyncload是做为一个j ... -
映射规则配置
2013-09-26 11:25 0背景 因为alibaba的特殊业务,比如: 同 ... -
网友文档贡献
2013-09-18 15:50 01. Otter源代码解析系列 链接:http://e ... -
Manager配置介绍
2013-09-16 13:00 0通道配置说明 多种同步方式配置 a. 单向同步 ... -
canal&otter FAQ
2013-09-05 17:30 0常见问题 1. canal和 ... -
阿里巴巴开源项目:分布式数据库同步系统otter(解决中美异地机房)
2013-08-22 16:48 40479项目背景 阿里巴巴B2B公司,因为业务的特性 ... -
Otter AdminGuide
2013-08-19 11:06 0几点说明 otter系统自带了manager,所以简化了一 ... -
Otter高可用性
2013-08-17 23:41 0基本需求 网络不可靠,异地机房尤为明显. man ... -
Otter数据一致性
2013-08-17 23:39 0技术选型分析 需要处理一致性的业务场景: 多地修改 ( ... -
Otter扩展性
2013-08-17 22:20 0扩展性定义 按照实现不同,可分为两类: 数据处理自定 ... -
Otter双向回环控制
2013-08-17 21:37 0基本需求 支持mysql/oracle的异构数据库的双 ... -
Otter调度模型
2013-08-17 20:13 0背景 在介绍调度模型之前,首先了解一下otter系统要解 ...
相关推荐
C#编程 数据库操作应用 Database(源码)(源码)C#编程 数据库操作应用 Database(源码)(源码)C#编程 数据库操作应用 Database(源码)(源码)C#编程 数据库操作应用 Database(源码)(源码)C#编程 数据库操作应用 Database...
它是一个轻量级的数据库系统,支持多种数据库操作,如创建、查询、更新和删除数据。然而,在多线程环境中,由于并发访问数据库,可能会遇到“database locked”(数据库被锁定)的问题。本文将深入探讨如何在Android...
安装AccessDatabaseEngine时,需要注意以下几点: - 安装前确保操作系统与安装程序版本匹配。 - 如果系统中已经安装了早期版本,建议先卸载,以避免版本冲突。 - 安装过程中,选择适合的应用范围,如“所有用户”或...
方便的数据库操作类! 可利用数据库存储过程,方便快捷地实现数据库操作。该类封装完美
在实际开发中,为了确保程序的兼容性和稳定性,需要注意以下几点: 1. **版本匹配**:确保使用的Access Database Engine版本与Access数据库文件的版本相匹配。 2. **64位与32位**:根据你的应用程序和操作系统是32位...
AccessDatabaseEngine 2007是微软发布的一款重要的数据库引擎,主要用于支持对Access数据库(.mdb和.accdb格式)的读写操作。这个组件在多种场景下都扮演着关键角色,比如在没有完整安装Microsoft Office的情况下,...
LabVIEW Database Connectivity Toolkit是一款专为2012版LabVIEW设计的数据连接工具,它允许用户通过图形化编程环境与各种类型的数据库进行交互,从而实现数据的读取、写入、更新和删除等操作。这款工具包极大地简化...
数据库工具Database4,各种数据库连接方便; 数据库工具Database4,各种数据库连接方便; 数据库工具Database4,各种数据库连接方便; 数据库工具Database4,各种数据库连接方便; 数据库工具Database4,各种数据库连接...
以下是关于Database Navigator的一些关键知识点: 1. **SQLite数据库**:SQLite是Android系统内置的关系型数据库管理系统,用于存储应用程序的数据。它轻量级、易于使用且无需单独的服务器进程,适合移动设备的资源...
标题中的"AccessDatabaseEngine.exe"是指微软公司提供的一个数据库引擎,用于与Access数据库进行交互。在Windows操作系统中,这个程序通常用于处理以.MDB或.ACE为扩展名的Access数据库文件。当用户尝试连接到Access...
"alter database操作命令详解" alter database命令是Oracle数据库中的一种数据库管理命令,用于修改数据库的结构和参数。本文将详细介绍alter database命令的各个方面,包括修改数据文件、临时文件、归档日志模式、...
在"DataBase 5.0_database:5_数据库、文件_"这个主题中,我们将深入探讨数据库的基本概念、数据库管理系统(DBMS)以及如何对文本表格数据进行操作。 数据库是一种有组织地存储和检索数据的系统,它可以视为一个...
9. **应用示例**:为了帮助用户快速上手,Labview 2018 Database Connectivity Toolkit通常会包含一些示例VI,展示如何连接数据库、执行查询以及处理结果集等基本操作,为初学者提供指导。 10. **兼容性**:尽管此...
安装AccessDatabaseEngine时,用户需要注意以下几点: 1. 选择正确的版本:根据你的操作系统是32位还是64位来选择合适的安装文件。 2. 兼容性问题:确保安装的版本与你的Office版本相匹配,避免可能出现的兼容性问题...
database systems and database applications. Our presentation stresses the funda- mentals of database modeling and design, the languages and models provided by the database management systems, and ...
- **7-Zip**:这是一款免费的文件压缩和解压软件,用于打开和提取MSI安装包的内容。你可以从提供的链接(http://dl.pconline.com.cn/html_2/1/74/id=307&pn=0.html)下载并安装7-Zip。 - **ORCA**:这是微软提供的...
1. 数据浏览与编辑:Database4提供直观的数据查看器,用户可以方便地浏览、编辑数据库中的数据,包括增删改查等基本操作,同时还支持数据过滤、排序、分组等功能,极大地提升了数据处理的效率。 2. SQL编辑器:内置...
AccessDatabaseEngine 2007 是微软发布的一款用于处理和访问Microsoft Access数据库的组件,它主要包含Access Database Engine (ACE)。这个组件是OleDb驱动程序的一种,允许开发者通过编程接口(如OLE DB或ODBC)...
标题“AccessDatabaseEngine2010_X86”所指的是微软的一个重要组件,它用于处理与Access数据库相关的数据操作,特别是在使用OLEDB技术进行Excel和Word的数据导入导出时。这个组件是Microsoft Access Database Engine...
标题“AccessDatabaseEngine2016_X64”指的是Microsoft Access Database Engine 2016 64位版本,这是一个重要的数据库组件,主要用于处理和操作Access格式的数据库,包括.mdb和.accdb文件。该组件提供了对数据的访问...