MySQL Handler

首先看一個示例。

mysql --socket=/tmp/mysql5173.sock -uroot -p

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE t2;
ERROR 1051 (42S02): Unknown table 't2'
mysql> CREATE TABLE t2
    -> (id int auto_increment primary key,
    -> name varchar(20),
    -> password varchar(20),
    -> age int) ENGINE=INNODB DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t2(name, password, age) \
VALUES('robin', '123456', '18');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2(name, password, age) \
VALUES('jack', '123456', '18');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO t2(name, password, age) \
VALUES('keven', '123456', '18');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+----+-------+----------+------+
| id | name  | password | age  |
+----+-------+----------+------+
|  1 | robin | 123456   |   18 |
|  2 | jack  | 123456   |   18 |
|  3 | keven | 123456   |   18 |
+----+-------+----------+------+
3 rows in set (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE '%delete%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_delete          | 0     |
| Com_delete_multi    | 0     |
| Handler_delete      | 0     |
| Innodb_rows_deleted | 0     |
+---------------------+-------+
4 rows in set (0.00 sec)

mysql> DELETE FROM t2;
Query OK, 3 rows affected (0.07 sec)

mysql> SHOW GLOBAL STATUS LIKE '%delete%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Com_delete          | 1     |
| Com_delete_multi    | 0     |
| Handler_delete      | 3     |
| Innodb_rows_deleted | 3     |
+---------------------+-------+
4 rows in set (0.00 sec)

翻了下官方文檔，原來這兩個變量有不同之處。

Handler_delete

The number of times that rows have been deleted from tables.

Com_xxx

The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements, respectively. Com_delete_multi and Com_update_multi are similar but apply to DELETE and UPDATE statements that use multiple-table syntax.

也就是說Handler_delete是刪除的記錄數，Com_delete是執行Delete命令的次數。