统计MySQL的DML

在oracle里可以比较方便的统计(user_tab_modifications)每天对某个表做了多次的DML（IUD），在mysql怎么实现呢？有两种方法：
1、 通过mysql表中的gmt_create、gmt_modified字段来判断
2、 Binlog

一、 在TB所有表中都有有gmt_create、gmt_modified字段，gmt_create表示记录创建时间，gmt_modified表示最近修改时间，如果记录没有修改，gmt_create和gmt_modified一致

root@lx 04:03:40>SHOW CREATE TABLE test_rows \G;
*************************** 1. ROW ***************************
       TABLE: test_rows
CREATE TABLE: CREATE TABLE `test_rows` (
  `a` INT(11) NOT NULL AUTO_INCREMENT,
  `gmt_create` datetime NOT NULL,
  `gmt_modified` datetime NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
root@lx 04:27:02>SELECT * FROM test_rows;
+---+---------------------+---------------------+
| a | gmt_create          | gmt_modified        |
+---+---------------------+---------------------+
| 1 | 2012-12-26 16:15:18 | 2012-12-27 16:15:18 |
| 2 | 2012-12-26 16:15:19 | 2012-12-26 16:16:09 |
| 3 | 2012-12-26 16:15:20 | 2012-12-27 16:15:20 |
| 4 | 2012-12-27 16:15:25 | 2012-12-27 16:15:25 |
| 5 | 2012-12-27 16:15:25 | 2012-12-27 16:15:25 |
| 6 | 2012-12-27 16:15:26 | 2012-12-27 16:15:26 |
+---+---------------------+---------------------+

昨天INSERT:

root@lx 04:29:04>SELECT COUNT(*)
    ->   FROM test_rows
    ->  WHERE gmt_create < date_format(DATE(now()), '%Y-%m-%e %H-%i-%s')
    ->    AND gmt_create >= date_format(DATE(date_add(now(), INTERVAL - 1 DAY)),
    ->                                  '%Y-%m-%e %H-%i-%s');

昨天UPDATE:

root@lx 04:31:48>SELECT *
    ->   FROM test_rows
    ->  WHERE gmt_create < date_format(DATE(now()), '%Y-%m-%e %H-%i-%s')
    ->    AND gmt_create >= date_format(DATE(date_add(now(), INTERVAL - 1 DAY)),
    ->                                  '%Y-%m-%e %H-%i-%s')
->    AND date_add(gmt_create, INTERVAL - 1 DAY) != date_add(gmt_modified, INTERVAL - 1 DAY)

昨天DELETE：
每天记录information_schema.TABLES中TABLE_ROWS值，昨天记数数与今天0点记录数相减等到

问题：如果表很大的话，没有索引，这些查询性能会是个问题，可以考虑放到凌晨备库(加个gmt_create索引)去跑

二、 binlog解析

root@lx 04:39:59>INSERT INTO test_rows(gmt_create,gmt_modified) VALUES(date_add(now(),INTERVAL -1 DAY),now());
Query OK, 1 ROW affected (0.01 sec)
 
root@lx 04:40:08>INSERT INTO test_rows(gmt_create,gmt_modified) VALUES(date_add(now(),INTERVAL -1 DAY),now());
Query OK, 1 ROW affected (0.00 sec)
 
root@lx 04:40:09>INSERT INTO test_rows(gmt_create,gmt_modified) VALUES(now(),now());
Query OK, 1 ROW affected (0.00 sec)
 
root@lx 04:40:14>INSERT INTO test_rows(gmt_create,gmt_modified) VALUES(now(),now());
Query OK, 1 ROW affected (0.00 sec)
 
root@lx 04:40:43>UPDATE test_rows SET gmt_modified=now() WHERE a=4;
Query OK, 1 ROW affected (0.00 sec)
ROWS matched: 1  Changed: 1  Warnings: 0
 
root@lx 04:40:48>UPDATE test_rows SET gmt_modified=now() WHERE a=1;
Query OK, 1 ROW affected (0.00 sec)
ROWS matched: 1  Changed: 1  Warnings: 0
root@lx 04:41:04>DELETE FROM test_rows WHERE a=2;
Query OK, 1 ROW affected (0.00 sec)
$ sudo mysqlbinlog --no-defaults -v -v mysql-bin.000081| tr ' ' '\n' | sort | uniq -c | grep -E "INSERT|UPDATE|DELETE"
      1 DELETE
      4 INSERT
      2 UPDATE

如果需要对某个表统计IUD，可以以下命令:

$sudo mysqlbinlog --no-defaults -v -v mysql-bin.000083| grep -w 'lx.t'|uniq -c|grep -E "INSERT|UPDATE|DELETE"
      3 ### INSERT INTO lx.t

问题：如果写入量比较大的话，binlog会很大，解析时间和分析时间会是瓶颈

小结：mysql的ROW_COUNT()能够记录每次DML影响的行数，如果每次影响函数能够累加到内存表中，这样统计就更方便了，修改源码。

Posted on 2012-12-27 at 下午 3:35 | Filed: 数据库 |
Entry RSS 2.0 feed | Leave a Trackback | Post a comment |

6 Responses to “统计MySQL的DML”

  1. Lewis说道：
    2012 年 12 月 28 日 上午 1:25

    昨天的update数量有点问题：如果一条记录更新了两次，统计的时候是不是就丢失了一次呢？

    回复
    • admin说道：
      2012 年 12 月 28 日 上午 10:24

      具体什么场景？

      回复
      • Lewis说道：
        2012 年 12 月 29 日 上午 8:03

        root@lx 04:31:48>select *
        -> FROM test_rows
        -> WHERE gmt_create AND gmt_create >= date_format(DATE(date_add(now(), INTERVAL – 1 DAY)),
        -> ‘%Y-%m-%e %H-%i-%s’)
        -> AND date_add(gmt_create, INTERVAL – 1 DAY) != date_add(gmt_modified, INTERVAL – 1 DAY)

        如果一条记录被更新了两次，是不是就漏统计了一次？

        回复
  2. admin说道：
    2013 年 01 月 08 日 上午 3:30

    是的

    回复
  3. zhchen6688说道：
    2013 年 09 月 25 日 上午 3:46

    如何统计 Select 次数？

    回复

Post a Comment