MySQL slow_query_log 报告的 Rows_examined 多于表中的行(无连接)

Question

当重构我的 entries 表并将 source 列拉入一个单独的 sources 表时，我正在执行更新，您可以看到在下面的 slow_query_log 中。

entries 表有 2 到 300 万行。

# Time: 120121 22:24:44
# User@Host: bt_user[bt_user] @ localhost []
# Query_time: 3910.984208  Lock_time: 0.091942 Rows_sent: 0  Rows_examined: 2092977602
SET timestamp=1327202684;
update entries set source_id = 4 where source = 'SOME_SOURCE';

另一个糟糕的情况:

mysql> update entries set source_id = 7 where source = 'SOME_OTHER_SOURCE';
Query OK, 31270 rows affected (53 min 3.37 sec)
Rows matched: 31270  Changed: 31270  Warnings: 0

# Time: 120121 23:43:36
# User@Host: root[root] @ localhost []
# Query_time: 3183.845250  Lock_time: 0.000110 Rows_sent: 0  Rows_examined: 875871174
SET timestamp=1327207416;
update entries set source_id = 7 where source = 'SOME_OTHER_SOURCE';

这是一个更正常的情况:

mysql> update entries set source_id = 6 where source = 'YET_ANOTHER_SOURCE';
Query OK, 138592 rows affected (23.84 sec)
Rows matched: 138592  Changed: 138592  Warnings: 0

# Time: 120121 22:49:54
# User@Host: root[root] @ localhost []
# Query_time: 23.843946  Lock_time: 0.000149 Rows_sent: 0  Rows_examined: 554368
SET timestamp=1327204194;
update entries set source_id = 6 where source = 'YET_ANOTHER_SOURCE';

知道非常大Rows_examined是怎么回事吗？如果我能确定这一点，我或许就能理解为什么有问题的交易需要一个多小时才能执行。

---

如果您需要更多信息，请询问，我会尽力提供。感谢您的帮助!

mysql> show create table entries\G
*************************** 1. row ***************************
       Table: entries
Create Table: CREATE TABLE `entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `source` varchar(100) NOT NULL,
  `source_id` int(10) unsigned NOT NULL DEFAULT '1000',
  `ip` int(10) unsigned DEFAULT NULL,
  `domain` varchar(255) DEFAULT NULL,
  `url` varchar(2038) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  `md5` varchar(32) NOT NULL,
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `both_date_source_index` (`start_date`,`end_date`,`source`),
  KEY `source_index` (`source`),
  KEY `ip_index` (`ip`),
  KEY `domain_part_index` (`domain`(20)),
  KEY `url_part_index` (`url`(30)),
  KEY `md5_index` (`md5`)
) ENGINE=InnoDB AUTO_INCREMENT=2355472 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

索引可能需要一些审查，但如果这影响到我的问题......

我的触发器(可能有更好的方法来做到这一点):

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: after_insert_count
               Event: INSERT
               Table: entries
           Statement: BEGIN

    IF NEW.ip IS NOT NULL THEN
      IF (SELECT COUNT(*) FROM ip_counts WHERE ip = NEW.ip) > 0 THEN
        UPDATE ip_counts SET count = count+1 WHERE ip = NEW.ip;
      ELSE
        INSERT INTO ip_counts (ip, count) VALUES (NEW.ip, 1);
      END IF;
    END IF;
    IF NEW.domain IS NOT NULL THEN
      IF (SELECT COUNT(*) FROM domain_counts WHERE domain = NEW.domain) > 0 THEN
        UPDATE domain_counts SET count = count+1 WHERE domain = NEW.domain;
      ELSE
        INSERT INTO domain_counts (domain, count) VALUES (NEW.domain, 1);
      END IF;
    END IF;
    IF NEW.url IS NOT NULL THEN
      IF (SELECT COUNT(*) FROM url_counts WHERE url = NEW.url) > 0 THEN
        UPDATE url_counts SET count = count+1 WHERE url = NEW.url;
      ELSE
        INSERT INTO url_counts (url, count) VALUES (NEW.url, 1);
      END IF;
    END IF;
  END
              Timing: AFTER
             Created: NULL
            sql_mode:
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: after_update_count
               Event: UPDATE
               Table: entries
           Statement: BEGIN

    IF OLD.ip IS NOT NULL THEN


      UPDATE ip_counts SET count = count-1 WHERE ip = OLD.ip;
    END IF;
    IF OLD.domain IS NOT NULL THEN
      UPDATE domain_counts SET count = count-1 WHERE domain = OLD.domain;
    END IF;
    IF OLD.url IS NOT NULL THEN
      UPDATE url_counts SET count = count-1 WHERE url = OLD.url;
    END IF;

    IF NEW.ip IS NOT NULL THEN
      IF (SELECT COUNT(*) FROM ip_counts WHERE ip = NEW.ip) > 0 THEN
        UPDATE ip_counts SET count = count+1 WHERE ip = NEW.ip;
      ELSE
        INSERT INTO ip_counts (ip, count) VALUES (NEW.ip, 1);
      END IF;
    END IF;
    IF NEW.domain IS NOT NULL THEN
      IF (SELECT COUNT(*) FROM domain_counts WHERE domain = NEW.domain) > 0 THEN
        UPDATE domain_counts SET count = count+1 WHERE domain = NEW.domain;
      ELSE
        INSERT INTO domain_counts (domain, count) VALUES (NEW.domain, 1);
      END IF;
    END IF;
    IF NEW.url IS NOT NULL THEN
      IF (SELECT COUNT(*) FROM url_counts WHERE url = NEW.url) > 0 THEN
        UPDATE url_counts SET count = count+1 WHERE url = NEW.url;
      ELSE
        INSERT INTO url_counts (url, count) VALUES (NEW.url, 1);
      END IF;
    END IF;
  END
              Timing: AFTER
             Created: NULL
            sql_mode:
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 3. row ***************************
             Trigger: after_delete_count
               Event: DELETE
               Table: entries
           Statement: BEGIN

    IF OLD.ip IS NOT NULL THEN


      UPDATE ip_counts SET count = count-1 WHERE ip = OLD.ip;
    END IF;
    IF OLD.domain IS NOT NULL THEN
      UPDATE domain_counts SET count = count-1 WHERE domain = OLD.domain;
    END IF;
    IF OLD.url IS NOT NULL THEN
      UPDATE url_counts SET count = count-1 WHERE url = OLD.url;
    END IF;
  END
              Timing: AFTER
             Created: NULL
            sql_mode:
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
3 rows in set (0.00 sec)

Answer 1

当使用更新、插入或删除语句时，触发器可以响应极长的查询执行时间和 slow_query_log 文件中惊人的高 Rows_examined 计数。

要查看表的所有触发器，请使用:

SHOW TRIGGERS WHERE `table`='tblName'

Mysql 必须运行触发器 block 中的每个语句，通常会影响其他表，因此 Rows_examined 可以是任何东西，不一定与更新表的行数相关。

在您的情况下，更新中受影响的每一行都会调用一个触发器，检查某些字段是否 NOT NULL 这并不意味着它们实际上被更改了，而只是检查是否有给定行中符合更新条件的该列的非空值。