MySQL
文章平均质量分 51
wzy0623
25年的数据库、数据仓库、大数据相关工作。《Hadoop构建数据仓库实践》、《HAWQ数据仓库与数据挖掘实战》、《SQL机器学习库——MADlib技术解析》、《MySQL高可用实践》、《Kettle构建Hadoop ETL》、《Greenplum构建实时数据仓库实践》作者。
展开
-
维度模型数据仓库(十八) —— 迟到的事实
(五)进阶技术 13. 迟到的事实 装载日期在生效日期后的事实就是迟到的事实。晚于订单日期进入源数据的销售订单可以看做是一个迟到事实的例子。销售订单被装载进其事实表时,装载的日期晚于销售订单的订单日期,因此是一个迟到的事实。(因为定期装载的是前一天的数据,所以这里的晚于指的是晚2天及其以上。) 迟到事实影响周期快照事实表的装载,如(五)进阶原创 2015-11-19 10:09:58 · 3561 阅读 · 1 评论 -
维度模型数据仓库(九) —— 角色扮演维度
(五)进阶技术 4. 角色扮演维度 当一个事实表多次引用一个维度表时会用到角色扮演维度。例如,一个销售订单有一个是订单日期,还有一个交货日期,这时就需要引用日期维度表两次。 本篇将说明两类角色扮演维度的实现,分别是表别名和数据库视图。这两种都使用了MySQL的功能。表别名是在SQL语句里引用维度表多次,每次引用都赋予维度表一个别名。而数据库原创 2015-11-08 15:02:50 · 4442 阅读 · 0 评论 -
维度模型数据仓库(十一) —— 维度层次
(五)进阶技术 6. 维度层次 大多数维度都具有一个或多个层次。例如,日期维度就有一个四级层次:年、季度、月和日。这些级别用date_dim表里的列来表示。日期维度是一个单路径层次,因为除了年-季度-月-日这条路径外,它没有任何其它层次。除此之外,本篇还将讨论在维度的层次上进行分组和钻取查询。多路径层次在下一篇“多路径和参差不齐的层次”中讨论。原创 2015-11-10 17:50:43 · 10266 阅读 · 4 评论 -
维度模型数据仓库(十四) —— 杂项维度
(五)进阶技术 9. 杂项维度 本篇讨论杂项维度。简单地说,杂项维度就是一种包含的数据具有很少可能值的维度。例如销售订单,它可能有很多离散数据(yes-no这种类型的值),如verification_ind(如果订单已经被审核,值为yes)credit_check_flag(表示此订单的客户信用状态是否已经检查)new_customer_ind(如果这是原创 2015-11-13 13:45:43 · 4227 阅读 · 0 评论 -
维度模型数据仓库(十六) —— 间接数据源
(五)进阶技术 11. 间接数据源 本篇讨论如何处理间接数据源。间接数据源与维度表具有不同的粒度,因此不能直接装载进数据仓库。在这里通过修改(五)进阶技术7. “多路径和参差不齐的层次”里的推广源数据说明怎样处理间接数据源。CAMPAIGN SESSION,MONTH,YEAR2014 First Campaign,1,20142014 First原创 2015-11-16 13:48:58 · 1958 阅读 · 0 评论 -
维度模型数据仓库(十七) —— 无事实的事实表
(五)进阶技术 12. 无事实的事实表 本篇讨论一种技术,用来处理源数据中没有度量的需求。例如,产品源数据不包含产品数量信息,如果系统需要得到产品的数量,很显然不能简单地从数据仓库中直接得到。这时就要用到无事实的事实表技术。使用此技术可以通过持续跟踪产品的发布来计算产品的数量。可以创建一个只有产品(计什么数)和日期(什么时候计数)维度代理键的事实表。之所以叫做无原创 2015-11-17 14:26:43 · 7862 阅读 · 1 评论 -
维度模型数据仓库(二十) —— 累积的度量
(五)进阶技术 15. 累积的度量 本篇说明如何实现累积月底金额,并对数据仓库模式和初始装载、定期装载脚本做相应地修改。累积度量是半可加的,而且它的初始装载比前面做的要复杂的多。 可加、半可加、不可加事实 事实表中的数字度量可划分为三类。最灵活、最有用的度量是完全可加的,可加性度量可以按照与事实表关联的任意维度汇总。半可加原创 2015-11-24 16:03:42 · 2831 阅读 · 0 评论 -
Data Vault初探(十) —— 星型模型向Data Vault模型转化
源数据库模型(3NF)如下图:星型模型如下图:Data Vault模型如下图:说明:星型模型(star schema)的事实表采取了完全规范化的第三范式(3NF)模型,而维表采取了第二范式的设计模型。有时也会把维表的设计规范化,就成了所谓的雪花模型(snowflake schema)。星型模型向Data Vault模型转化:星型模趔的主要构成部分是维表与事实原创 2015-12-15 15:07:49 · 7879 阅读 · 1 评论 -
MySQL Fabric实验(二)Sharding
一、概述 MySQL Fabric这一新的架构为MySQL提供了高可用和向外扩展的特性。本实验专注于使用Fabric对多个MySQL服务器进行读写实现向外扩展。当单个MySQL服务器(或HA组)的写性能达到极限时,可以使用Fabric把数据分布到多个MySQL服务器组。注意这里说的组可以是单一服务器,也可以是HA组。管理员通过建立一个分片映射定义数据如何在多个服务中分片。一个分片原创 2015-12-31 14:03:56 · 2025 阅读 · 0 评论 -
MySQL Fabric实验(三)HA与Sharding
实验步骤:1. 安装虚拟机 使用VirtualBox安装四个CentOS release 6.4虚拟机,安装Python 2.6或以上版本,关闭iptables和selinux。虚拟机和网卡说明如下表所示。主机名内部网络IP说明fab_connector192.168.56.101安装原创 2016-01-02 11:21:19 · 2249 阅读 · 0 评论 -
Data Vault初探(一) —— 基础
摘自:《Pentaho Kettle 解决方案: 使用PDI构建开源ETL解决方案》 Data Vault(DV)模型是用于企业级的数据仓库建模。由Dan Linstedt在20世纪90年代提出(http://www.danlinstedt.com)。在最近几年,Data Vault模型获得了很多关注,并在BI社区里拥有了一批追随者。 Dan Linsted转载 2015-12-08 17:56:29 · 12056 阅读 · 0 评论 -
Data Vault初探(二) —— 构建及参考原则
摘自:《下一代数据仓库模型Data Vault的研究及其应用》 Data Vault模型的构建 在Data Vault模型中,各个实体组件有着严格、通用的定义与准确、灵活的功能描述,这不但使得Data Vault模型能够最直观、最一般地反映数掘之间内含的业务规则,同时也为构建Data Vault模型提供了一致而普遍的方法。简单地讲,Data Vault模转载 2015-12-08 18:00:10 · 9549 阅读 · 0 评论 -
Data Vault初探(三) —— 建立Data Vault模型
本示例源数据库是一个订单销售的普通场景,共有省、市、客户、产品类型、产品、订单、订单明细7个表。ERD如下图所示。使用下面的脚本建立源数据库表:CREATE TABLE province ( province_id varchar(2) NOT NULL COMMENT '省份编码', province_name varchar(20) DEFAULT NULL COMMENT原创 2015-12-08 18:05:54 · 8771 阅读 · 2 评论 -
MySQL Fabric实验(一)HA
一、概述 MySQL Fabric这一新的架构为MySQL提供了高可用和向外扩展的特性。本实验专注于高可用。高可用指的是系统提供持续服务的能力。下图显示了一个系统中应该为服务可用提供的不同层次。 MySQL Fabric在MySQL复制上增加了一个管理和监控层,它和一组MySQL Fabric-aware连接器一起,把写和一致性读操作路由的当前的主服务器原创 2015-12-28 15:07:12 · 2428 阅读 · 1 评论 -
MySQL树形遍历
原文出自http://blog.itpub.net/29254281/viewspace-1851457/借鉴Oracle Hr模式下的Employees表的结构和数据DROP TABLE IF EXISTS `employees`;CREATE TABLE `employees` ( `employee_id` int(11) NOT N转载 2016-02-20 09:43:47 · 4004 阅读 · 0 评论 -
利用sqoop将hive和mysql数据互导简单实验
1. Hadoop、Hive、MySQL安装(略)2. 下载sqoophttp://www.apache.org/dyn/closer.lua/sqoop/1.4.63. 解压tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz4. 建立软连接ln -s sqoop-1.4.6.bin__hadoop-2.0.原创 2016-03-18 11:16:22 · 5454 阅读 · 0 评论 -
MySQL使用变量实现部分分组聚合
在实际应用中经常有这种需求,按照select的字段中的部分字段分组聚合,比如下面的例子:create table t1 (a varchar(20),b varchar(20),c int);insert into t1 values('a','123',1);insert into t1 values('b','456',2);insert into t1 values('a','78原创 2016-02-21 14:59:31 · 2304 阅读 · 0 评论 -
使用Kettle连接动态分库
一、问题提出 在一个数据仓库应用中,每天新建一个MySQL数据库,以当天日期命名,如d_p20161201、d_p20161202等,并使用Kettle连接这些数据库做数据清洗和ETL工作。由于数据库是用脚本每天动态生成的,Kettle如何连接动态分库呢?二、解决方案1. 建立数据库连接,在数据库名称中引入变量。此时是无法连接到数据库的。2. 建立转换,用JavaScript步骤设置原创 2016-12-26 09:36:29 · 4212 阅读 · 0 评论 -
Kettle之“检查表是否存在”
想用Kettle实现一个非常简单的需求,从MySQL向Oracle导入一个表的数据,如果在oracle里表不存在,先建表再导入数据。这个功能看似非常简单,但对于刚开始接触Kettle的用户来说,可能会有些疑惑。Kettle的转化和作业中都有“检查表是否存在”步骤,但是如果要实现上述的需求,只能用作业,而不能使用转换。 在Kettle中,作业是串行执行的,只有前一个作业项执行成功才会开始下一个作业原创 2016-12-26 10:39:30 · 9671 阅读 · 1 评论 -
Kettle之“获取文件名”
收到的需求是这样的:有几百个文本文件,每个文件内容的格式相同,都是有固定分隔符的两列,每个文件有几千行记录。现在需要把这些文件的内容导入一个表,除了文件中的两列,还要存一列记录对应的文件名。 作为一个搞数据库的,导入数据本来是小事一桩,可这有几百个文件要手工逐个处理未免太麻烦了,于是想起了Kettle。Kettle的转换处理数据流,其中有一个“获取文件名”的输入对象,可以使用它在导入文件数据时添原创 2016-12-26 11:07:01 · 21116 阅读 · 0 评论 -
在Kettle里使用参照表进行数据校验(流查询实现)
参照表一个常见的用途就是做数据的查询和检验。提供一个输入字段,如果输入字段里的值没有匹配上,就给对应的数据行做一个错误标志。下面使用城市和邮政编码查询做个例子,演示如何使用计算器步骤和查询步骤来判断地址和邮政编码是否匹配。完整的转换如下图: 首先,需要一些输入数据,本例使用了“自定义常量数据”步骤,并添加一些测试数据作为输入,如下图: 第一个清洗步骤就是从邮政编码里提取数字,要使用计算器步骤。原创 2016-12-26 16:10:19 · 16533 阅读 · 1 评论 -
在Kettle里使用参照表进行数据校验(子转换实现)
有一种参照表叫数据确认主表。性别编码就是这种参照表的例子。有的系统使用字母M、F和U,分别代表男、女、未知;有的系统使用NULL来代表未知的性别;有的系统使用Male和Female代表男、女;而有的系统则使用完全不同的编码,如0(男)、1(女)或0(未知)、1(男)、2(女),等等。还有更复杂的情况,有的系统使用C代表儿童,使用F代表父亲,M代表母亲,各种变化和组合都有可能。要把从这些来源的数据整原创 2016-12-26 16:33:31 · 6589 阅读 · 0 评论 -
在Kettle里使用时间戳实现变化数据捕获(CDC)
1. 建立测试表,插入数据。use test; create table t_color ( id int unsigned not null auto_increment primary key, color varchar(10), create_date datetime, last_update timestamp ) eng原创 2016-12-26 14:03:21 · 17414 阅读 · 3 评论 -
在标准MySQL 5.6上查询没有使用过的索引的SQL
select distinct mysql.innodb_index_stats.table_name, mysql.innodb_index_stats.index_name from mysql.innodb_index_stats where concat(mysql.innodb_index_stats.index_name,原创 2016-12-27 15:13:57 · 2033 阅读 · 0 评论 -
MySQL 数字辅助表去重、排序、行转列
一、需求一个字段有多行记录,查询结果为去重排序的一行记录,例如记录值为:1,2,41,4,52,323,56,673,4要求查询结果为:1,2,3,4,5,23,56,67二、方案使用数字辅助表实现-- 建立数字辅助表 create table nums ( a int not null primary key ); delimiter $$ create proced原创 2016-12-27 15:19:03 · 1232 阅读 · 0 评论 -
MySQL执行binlog的两种方法
维护mysql的时候,总会遇到数据库恢复的例子。如果把备份集恢复出来相对比较简单。然而如果遇到恢复到时间点的例子,把一个MySQL实例恢复出来之后,需要执行binlog做增量恢复。 常见的办法是用mysqlbinlog解析binlog,将解析出来的内容重定向到mysql命令行执行。在MySQL手册中也是推荐使用mysqlbinlog工具来实现指定时间点的数据恢复。事实上,这是一个经常“让人郁闷”原创 2016-12-27 16:29:02 · 6531 阅读 · 1 评论 -
使用blackhole存储引擎表模拟多主复制
MySQL 5.6 不支持多主复制,但可以blackhole存储引擎表模拟多主复制。将server1复制到server2,再从server2复制到备库。如果在server2上为从server1上复制的数据使用blackhole存储引擎,就不会包含任何server1的数据,如下图所示。一、环境MySQL 5.6.14server1的IP 192.168.1.1,my.cnf如下图:server2的I原创 2016-12-27 16:35:08 · 1005 阅读 · 0 评论 -
MySQL 5.6 插入缓冲测试
设置autocommit = offinnodb_flush_log_at_trx_commit = 0innodb_buffer_pool_size = 134217728innodb_change_buffer_max_size = 25innodb_change_buffering = all建表create table t1 (id int auto_increment not n原创 2016-12-28 08:46:35 · 1067 阅读 · 0 评论 -
在MySQL中建立自己的哈希索引(书摘备查)
在MySQL中,只有Memory存储引擎支持显式的哈希索引,但是可以按照InnoDB使用的方式模拟自己的哈希索引。这会让你得到某些哈希索引的特性,例如很大的键也只有很小的索引。 想法非常简单:在标准B-Tree索引上创建一个伪哈希索引。它和真正的哈希索引不是一回事,因为它还是使用B-Tree索引进行查找。然而,它将会使用键的哈希值进行查找,而不是键自身。你所要做的事情就是在where子句中手动地原创 2016-12-28 13:16:48 · 1765 阅读 · 0 评论 -
MySQL优化特定类型的查询(书摘备查)
1. 优化countcount有两种不同的工作方式:统计值的数量和统计行的数量。值是一个非空的表达式(null意味着没有值)。如果在count()的括号中定义了列名或其它表达式,count就会统计这个表达式值的次数。count的另外一种形式就是统计结果中行的数量。当MySQL知道括号中的表达式永远不会为null的时候,它就会按这种方式工作。最明显的例子就是count(*),它是count的一种特例原创 2016-12-28 13:19:55 · 741 阅读 · 0 评论 -
InnoDB缓冲池命中率(书摘备查)
通常InnoDB存储引擎的缓冲池的命中率不应该小于99%。缓冲池命中率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)平均每次读取的字节数 = Innodb_data_read/Inno原创 2016-12-28 13:23:39 · 5302 阅读 · 0 评论 -
加速MySQL的alter table操作(书摘备查)
MySQL的alter table性能在表很大的时候会出现问题。MySQL执行大部分更改操作都是新建一个需要的结构的空表,然后把所有老的数据插入到新表,最后删除旧表。这会耗费很多时间,尤其是在内存紧张,而表很大并有很多索引的时候。 不是所有的alter table操作都会导致重建表。例如,可以通过两种方式创建或去掉列的默认值(一种快、一种慢)。下面是较慢的方式:alter table film原创 2016-12-28 14:17:16 · 1434 阅读 · 0 评论 -
MySQl里类似Oracle rownum的实现
[sql] view plain copy-- dense rank,写法1 set @curr_cut:=0, @prev_cnt:=0, @rank:=0; select actor_id, @curr_cnt:=cnt as cnt, @rank:=if(@prev_cntas rank @prev_cnt:=@curr_cnt as d原创 2016-12-28 14:26:38 · 4167 阅读 · 1 评论 -
MySQL里用一个表的数据更新另一个表
MySQL的update语句里可以使用join,这在用一个表的数据更新另一个表时很方便,看下面一个统计点击数的例子:[sql] view plain copy-- 建立每天点击统计表 create table daily_hit_counter ( day date not null, slot tinyint unsigned not null, cnt int uns原创 2016-12-28 14:57:29 · 1378 阅读 · 0 评论 -
MySQL 分析和调整查询缓存的流程(书摘备查)
总的SELECT查询数 = Com_select + Qcache_hits + queries with errors found by parserCom_select = Qcache_inserts + Qcache_not_cached+ queries with errors found during the column-privileges check原创 2016-12-28 15:41:07 · 605 阅读 · 0 评论 -
MySQL内存相关的主要变量
一、查询缓存1. 查询缓存命中率计算公式:Qcache_hits/(Qcache_hits + Com_select)2. 状态变量Qcache_hits查询缓存命中数,即可以从查询缓存中直接返回结果的次数Qcache_not_cached不可缓存查询数,current_date等不确定函数或者查询结果大于query_cache_limit使得查询不可缓存Qcahce_inserts被加入缓存的查原创 2016-12-28 15:46:43 · 659 阅读 · 0 评论 -
qcache_inserts com_select 与缓存命中率
高性能MySQL这本书中关于查询缓存有一段这样的描述: Cache invalidations can happen because of fragmentation, insufficient memory, ordata modifications. If you have allocated enough memory to the cache and tuned thequery_cach原创 2016-12-28 16:21:04 · 2200 阅读 · 0 评论 -
MySQL实现树的遍历
经常在一个表中有父子关系的两个字段,比如empno与manager,这种结构中需要用到树的遍历。在Oracle 中可以使用connect by简单解决问题,参见http://blog.csdn.net/wzy0623/archive/2007/06/18/1656345.aspx,但MySQL 5.1中还不支持(据说已纳入to do中),要自己写过程或函数来实现。一、建立测试表和数据:[c-sha原创 2016-12-29 09:42:13 · 2766 阅读 · 0 评论 -
去除重复数据
有两个意义上的重复记录,一是完全重复的记录,也即所有字段均都重复,二是部分字段重复的记录。对于第一种重复,比较容易解决,只需在查询语句中使用distinct关键字去重,几乎所有数据库系统都支持distinct操作。发生这种重复的原因主要是表设计不周,通过给表增加主键或唯一索引列即可避免。select distinct * from t; 对于第二类重复问题,通常要求查询出重复记录中的任一条记录。假原创 2016-12-29 13:02:24 · 1077 阅读 · 0 评论 -
将MySQL去重操作优化到极致之三弹连发(三):用rocksdb替代innodb
前面已经建立了索引,优化了SQL语句,并将单线程变为多线程并行执行,去重时间由最初的35秒优化为3.5秒,是不是就到此为止呢?吴老师又使用了rocksdb存储引擎替代innodb的方法。这里有必要交代一下命题的背景。这道MySQL数据库优化的题目出自是阿里内部的竞赛题,当然我是听吴老师口述的,真正的题目及其竞赛规则与竞赛环境不甚明确,但有一条是允许自由选择MySQL存储引擎。在实际的生产环境中,几原创 2017-01-12 15:50:50 · 6523 阅读 · 2 评论