统计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影响的行数,如果每次影响函数能够累加到内存表中,这样统计就更方便了,修改源码。


6 Responses to “统计MySQL的DML”

  1. Lewis说道:

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

    • admin说道:

      具体什么场景?

      • Lewis说道:

        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. zhchen6688说道:

    如何统计 Select 次数?

Post a Comment