在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影响的行数,如果每次影响函数能够累加到内存表中,这样统计就更方便了,修改源码。
昨天的update数量有点问题:如果一条记录更新了两次,统计的时候是不是就丢失了一次呢?
具体什么场景?
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)
如果一条记录被更新了两次,是不是就漏统计了一次?
是的
如何统计 Select 次数?
com_XXX