MySQL 如何设计统计数据表

缓存型数据表通常在统计数据时会经常用到，因此也会叫统计性数据。举个例子来说，对于员工、部门数据表而言，我们可能会需要查询一个部门下有多少员工。这时候有三种方式实现：

  • 在部门下增加一个员工数量的字段，每次对员工进行增、改、删操作时都需要同步更新员工数量（如果员工换部门，则需要更新多个部门的员工数量）。这种方式能够保证实时性，但是却很低效。对于如果是操作不频繁时是没问题的，假设相当频繁，就意味着每次都需要操作两张表，而且业务代码都需要做埋点处理，将统计业务和普通业务深度耦合在一起了。
  • 每次查询的时候，从员工表中执行 SUM 函数，获取该部门的员工数。这种方式避免了埋点，但是每次都需要去员工数据表求和，如果员工数据量大的话会很低效。
  • 新建一张统计表，每隔一定时间从员工表中汇总每个部门的人员数量。这种定时抽取数据的方式会牺牲一定的实时性，但降低了代码的耦合，由于部门不会太多，这张表的大小是可预测的，也提高了数据访问的效率。这种方式即缓存型数据表。

以掘金的手机端个人中心为例，为展示每个用户的关注人数、关注者和掘力值，不可能每次查询都去做一次 SUM，这意味着需要做多张表的 SUM 操作，效率会很低，而且掘力值的计算还涉及到更为复杂的计算方法（与文章的浏览量和点赞数有关）。因此，可以猜测一下大致的表设计，这样在查询用户个人主页信息的时候只需要从这一张表就可以读取到所有数据了。

CREATE t_user_summay (
  id INT PRIMARY KEY,
  user_id BIGINT(20),
  focused_user_cnt INT,
  followed_user_cnt INT,
  user_value INT,
  user_level ENUM('Lv1', 'Lv2', ..., 'Lv8'),
  created_time DATETIME,
  updated_time DATETIME,
);

是否需要实时更新

在实际应用过程中，统计表有两种方式，一种是实时更新，一种是周期性的重建数据。两种方式有利有弊，实时更新保证了查询数据的即时性，但是会牺牲性能，并且要求代码埋点，而且由于数据更新是没有规律的，可能产生碎片。周期性的重建数据牺牲了实时性，如果说大部分数据都不变的话会带来不必要的统计计算，但如果数据经常变动，那周期性地重建数据显然会更高效而且避免了埋点的情况。当然，避免应用程序的埋点也可以通过触发器来完成，可以参考//www.jb51.net/article/213062.htm

物化视图工具（Flexviews）

在 MySQL 中，有一个 Flexviews 的开源工具用于从数据库的binlog 中提取数据完成数据统计。有点类似与视图，但与视图所不同的是，Flexviews 产生的数据表是物理表，这也是为什么称之为物化视图的原因。而且，Flexviews 还支持增量更新和全量更新。推荐使用增量更新，以避免所有行的统计数据都需要重建的情况。增量更新会检查哪些数据行数据发生了改变，再执行更新操作，相比全量更新而言性能会更高。但为了检测数据改变，需要引入一个视图记录数据行的变化日志。

计数表

在实际开发中，我们经常会需要对一些操作进行计数，比如文章的阅读数、点赞数。如果将计数值放入同一张表很可能在更新的时候出现并发问题。使用独立的计数表可以避免查询缓存失效问题并使用一些更高级的技巧。例如统计文章的阅读数、点赞数的数据表：

CREATE TABLE t_article_counter (
  article_id INT PRIMARY KEY,
  read_cnt INT UNSIGNED NOT NULL,
  praise_cnt INT UNSIGNED NOT NULL
);

在更新阅读数的时候，可以使用 MySQL 的内置加1操作：

UPDATE t_article_counter
SET read_cnt = read_cnt + 1
WHERE article_id = 1;

这种方式可以使得操作是单行的，对事物而言是互斥的，因此会将事务序列化处理避免并发问题。但是却会影响并发请求量。可以对文章增加多个插槽来提高并发量。

CREATE TABLE t_article_counter (
  id INT NOT NULL PRIMARY KEY,
  slot TINYINT UNSIGNED,
  article_id INT,
  read_cnt INT UNSIGNED NOT NULL,
  praise_cnt INT UNSIGNED NOT NULL,
  INDEX(article_id)
);

这时可以创建100个插槽初始化数据，在更新的时候可以这样操作：

UPDATE t_article_counter
SET read_cnt = read_cnt + 1
WHERE slot = RAND() * 100 AND article_id = 1;

获取某篇文章的总阅读数时，需要使用一个 SUM 操作：

SELECT SUM(read_cnt) FROM t_article_counter
WHERE article_id = 1;

这种方式实际上是空间换时间，提高了并发量。

总结

本篇介绍了如何设计统计数据表，关键的核心在于业务类型。对于更新频率低、数据量小的表使用实时同步或者直接 SUM 求和问题都不大。而对于大数据表，高频率的更新的情况，则可以使用独立的统计表。同时，若存在高并发的情况，统计表中可以考虑每项主体增加多个插槽的方式提高并发量。如果是周期性地同步数据，也可以使用 Flexviews 物化视图插件实现。

以上就是MySQL 如何设计统计数据表的详细内容，更多关于MySQL 设计统计数据表的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • MySQL创建数据表时设定引擎MyISAM/InnoDB操作

    我在配置mysql时将配置文件中的默认存储引擎设定为了InnoDB.今天查看了MyISAM与InnoDB的区别,在该文中的第七条"MyISAM支持GIS数据,InnoDB不支持.即MyISAM支持以下空间数据对象:Point,Line,Polygon,Surface等." 作为一个地理信息系统专业的学生(其实是测绘专业)来讲,能存储空间数据的数据库才是好数据库,原谅我是数据库小白的身份. 有三种方式可以设定数据库引擎: (1)修改配置文件 将安装目录下~\MySQL\mysql-5.6

  • MySQL数据表分区策略及优缺点分析

    为什么需要分区? 当面对巨大的数据表的时候,至少有一件事情是确定的,表太大了以至于每次查询的时候我们没法做全表扫描.而这个时候也没法使用索引,或者说索引意义不大,更不用说索引的维护代价和空间占用非常高.如果是依赖索引,会导致大量的碎片和低聚集度的数据,这会导致查询的时候有上千次的随机 I/O 访问而导致宕机.这种情况下一般只会使用1-2个索引,而不会更多.这种情况下,有两个可行的选项:查询必须从数据表的指定的部分顺序查找或者是期望的部分数据及其索引与服务器的内存匹配. 需要再次重申:在存储空间过

  • MySQL高级特性——数据表分区的概念及机制详解

    MySQL 的分区的实现方式是对数据表进行一层包装,这意味着索引实际是基于每个分区定义的,而不是整张表.这个特性和 Oracle 是不同的,在 Oracle 中的索引和数据表可以使用更灵活和更复杂的方式进行分区.​ MySQL 的分区通过定义 PATITION BY 子句的条件来决定数据行所属分区的归属.在执行查询的时候,查询优化器会区分所在分区,这意味着查询不会检查全部分区,而仅仅是那些包含索要查询数据所在的分区.​ 分区的主要目的是对数据表进行大致形式的索引和聚集.这样可以减少数据表的过大范

  • MySQL创建数据表并建立主外键关系详解

    前言 为mysql数据表建立主外键需要注意以下几点: 需要建立主外键关系的两个表的存储引擎必须是InnoDB. 外键列和参照列必须具有相似的数据类型,即可以隐式转换的数据类型. 外键列和参照列必须创建索引,如果外键列不存在索引,mysql将自动创建索引. 一.SQL语句创建数据表并设置主外键关系 create table demo.ChineseCharInfo ( ID int not null auto_increment, Hanzi varchar(10) not null, prima

  • MySQL如何构建数据表索引

    理解索引概念最简单的方式是通过一个案例来进行,以下就是这样的一个案例. 假设我们需要设计一个在线的约会网站,这个网站的用户资料有许多列,例如国籍.省份.城市.性别.年龄.眼睛颜色等等.这个网站必须支持通过多种组合方式搜索用户资料.同时,也需要支持支持排序和根据用户最近在线时间和其他用户的评价返回有限的结果等等.对于这种复杂场景我们如何设计索引? 有点奇怪,首先要做的事情是要决定我们是否必须使用索引排序,或者检索后再排序是否能够接受.索引排序限制了索引和查询构建的方式.例如,在WHERE age

  • MySQL数据表合并去重的简单实现方法

    场景: 爬取的数据生成数据表,结构与另一个主表相同,需要进行合并+去重 解决:(直接举例) 首先创建两个表pep,pep2,其中pep是主表 CREATE TABLE IF NOT EXISTS `pep/pep2`( `id` INT UNSIGNED AUTO_INCREMENT, `no` VARCHAR(100) NOT NULL, PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8; 然后向pep中插入两条数据,pep2中插

  • MySQL 索引和数据表该如何维护

    MySQL 索引和数据表该如何维护

    查找和修复数据表冲突 数据表最糟糕的事情就是发生冲突.使用MyISAM存储引擎时,通常因为崩溃导致冲突.然而,当存在硬件故障.MySQL内部Bug或操作系统Bug时,所有的存储引擎都可能遭受索引冲突. 冲突的索引可能导致查询返回错误的结果,在没有重复值时的重复索引错误增加,甚至可能导致全表扫描或崩溃.如果你遇到过偶发的事件,例如一个你认为不会发生的错误,这个时候运行CHECK TABLE命令去检测数据表是否有冲突(注意有些数据库引擎不支持这个命令,有些则支持多种选项参数去指定如何检测表).通常,

  • Mysql删除数据以及数据表的方法实例

    在Mysql 中删除数据以及数据表非常的容易,但是需要特别小心,因为一旦删除所有数据都会消失. 删除数据 删除表内数据,使用delete关键字. 删除指定条件的数据 删除用户表内id 为1 的用户: delete from User where id = 1; 删除表内所有数据 删除表中的全部数据,表结构不变. 对于 MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间. delete from User; 释放磁盘空间 optimize table User; 删除数据表 删除数据

  • 删除mysql数据表如何操作

    MySQL中删除数据表是非常容易操作的, 但是你再进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失. 语法 以下为删除MySQL数据表的通用语法: DROP TABLE table_name ; 在命令提示窗口中删除数据表 在mysql>命令提示窗口中删除数据表SQL语句为 DROP TABLE 方法扩展: 在MySQL中有两种方法可以删除数据,一种是DELETE语句,另一种是TRUNCATE TABLE语句.DELETE语句可以通过WHERE对要删除的记录进行选择.而使用TRUN

  • 关于MYSQL 你需要知道的数据类型和操作数据表

    数据类型和操作数据表 1.1 MySQL类型之整型 1.2 MySQL数据类型之浮点型 1.3 日期时间型 DATE 1支持时间:1000年1月1日~9999年12月31日 DATETIME 3 支持时间:1000年1月1日0时0秒~9999年12月31日23时59分59秒 TIMESTAMP 3 支持时间:1970年1月1日~2037年12月31日 TIME 8 支持时间:-8385959~8385959 YEAR 4 支持时间:1970-2069 1.4 字符型 CHAR:定长,不足则后面补

  • MySQL 常见的数据表设计误区汇总

    误区一:过多的数据列 MySQL 存储引擎的 API 是按照行缓冲区方式从服务端和存储引擎复制数据.服务端将缓冲区数据解码成数据列.然而,将行缓冲区的格式转换为数据行数据结构的列可能会代价很高.MyISAM 固定使用与服务端匹配的行格式,因此无需转换.然而,MyISAM 的可变行格式以及 InnoDB 的行格式总是需要进行转换.转换的代价依赖于列的数量.如果当数据表的列超过上百列的时候,会引起很高的 CPU 资源消耗--即便是使用到的列很少.曾经看过一篇文章,指的是一个多语言的解决方案,直接简单

随机推荐