MySQL中隐藏了外键约束的代价

vlambda
2022-04-07

MySQL中隐藏了外键约束的代价

示例表

CREATE TABLE `product` (  `category` int NOT NULL,  `id` int NOT NULL,  `price` decimal(10,0) DEFAULT NULL,  PRIMARY KEY (`category`,`id`)) ENGINE=InnoDB;

我想知道针对此表的UPDATE的update示例的成本是多少：

mysql > EXPLAIN update product set id=id+1 where id=65032158 and category=3741760\G*************************** 1. row ***************************           id: 1  select_type: UPDATE        table: product   partitions: NULL         type: rangepossible_keys: PRIMARY          key: PRIMARY      key_len: 8          ref: const,const         rows: 1     filtered: 100.00        Extra: Using where1 row in set, 1 warning (0.00 sec)

执行计划可以看到，只有一个表和一行记录。这看上去是对的，因为使用主键检索，只有一行记录满足条件：

mysql > select * from product where id=65032158;+----------+----------+-------+| category | id       | price |+----------+----------+-------+|  3741760 | 65032158 |     2 |+----------+----------+-------+1 row in set (0.02 sec)

在这个例子中，因为表有外键关系，我们看到的并不是所有的真相：

mysql > SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='product' AND REFERENCED_TABLE_SCHEMA='db1'\G*************************** 1. row ***************************            TABLE_NAME: product_order           COLUMN_NAME: product_category       CONSTRAINT_NAME: product_order_ibfk_1 REFERENCED_TABLE_NAME: productREFERENCED_COLUMN_NAME: category*************************** 2. row ***************************            TABLE_NAME: product_order           COLUMN_NAME: product_id       CONSTRAINT_NAME: product_order_ibfk_1 REFERENCED_TABLE_NAME: productREFERENCED_COLUMN_NAME: id2 rows in set (0.01 sec)

关联的表定义了on update cascade，连接到我们的product表：

CREATE TABLE `product_order` (  `no` int NOT NULL AUTO_INCREMENT,  `product_category` int NOT NULL,  `product_id` int NOT NULL,  `customer_id` int NOT NULL,  PRIMARY KEY (`no`),  KEY `product_category` (`product_category`,`product_id`),  KEY `customer_id` (`customer_id`),  CONSTRAINT `product_order_ibfk_1` FOREIGN KEY (`product_category`, `product_id`) REFERENCES `product` (`category`, `id`) ON DELETE RESTRICT ON UPDATE CASCADE,  CONSTRAINT `product_order_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)) ENGINE=InnoDB;

因此，explain命令完全没有考虑外键的事情。执行计划也只是试图告诉我们，该update操作在我们的数据库也只是要更新一行而已。

另一个分析慢查询的典型的方法是检查每个会话状态句柄(handlers)。本例子结果是：

mysql > flush status; update product set id=id+1 where id=65032158 and category=3741760\GQuery OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql > show status like 'handler%';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| Handler_commit             | 2     || Handler_delete             | 0     || Handler_discover           | 0     || Handler_external_lock      | 2     || Handler_mrr_init           | 0     || Handler_prepare            | 2     || Handler_read_first         | 0     || Handler_read_key           | 1     || Handler_read_last          | 0     || Handler_read_next          | 0     || Handler_read_prev          | 0     || Handler_read_rnd           | 0     || Handler_read_rnd_next      | 0     || Handler_rollback           | 0     || Handler_savepoint          | 0     || Handler_savepoint_rollback | 0     || Handler_update             | 1     || Handler_write              | 0     |+----------------------------+-------+18 rows in set (0.01 sec)

Handler_update：也没有考虑到外键约束；如果没有其它的检查，我们也不会意识还有其它的工作要做。

监控

让我们看看外键约束是如何影响数据库活动的。

我们已经知道监控Handler_update并不能表示真实的结果。让我们来检查innodb相关的计数器(在空闲的mysql实例上测试)：

mysql > show status like 'Innodb_rows_updated';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Innodb_rows_updated | 21369 |+---------------------+-------+1 row in set (0.00 sec)

以下是执行update操作，以及真正更行的行数：

mysql > select product_id,count(*) from product_order where product_id in (65032159,65032160) group by product_id;+------------+----------+| product_id | count(*) |+------------+----------+|   65032159 |      897 |+------------+----------+1 row in set (0.02 sec)
mysql > update product set id=id+1 where id=65032159 and category=3741760\GQuery OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql > select product_id,count(*) from product_order where product_id in (65032159,65032160) group by product_id;+------------+----------+| product_id | count(*) |+------------+----------+|   65032160 |      897 |+------------+----------+1 row in set (0.01 sec)
mysql > show status like 'Innodb_rows_updated';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Innodb_rows_updated | 22267 |+---------------------+-------+1 row in set (0.00 sec)
mysql > select 22267-21369;+-------------+| 22267-21369 |+-------------+|         898 |+-------------+1 row in set (0.00 sec)

这里innodb展示了真正更新的记录数。

通过show engine innnodb status也可以看到子表上加了的锁。(需要开启innodb_status_output_locks)

Performance Schema提供了另外一种监控方法：

mysql > truncate `performance_schema`.`table_io_waits_summary_by_table`;Query OK, 0 rows affected (0.00 sec)
mysql > select OBJECT_NAME,COUNT_UPDATE,COUNT_WRITE,COUNT_READ,COUNT_DELETE,COUNT_INSERT from `performance_schema`.`table_io_waits_summary_by_table` where OBJECT_NAME like "prod%";+---------------+--------------+-------------+------------+--------------+--------------+| OBJECT_NAME   | COUNT_UPDATE | COUNT_WRITE | COUNT_READ | COUNT_DELETE | COUNT_INSERT |+---------------+--------------+-------------+------------+--------------+--------------+| product       |            0 |           0 |          0 |            0 |            0 || product_order |            0 |           0 |          0 |            0 |            0 |+---------------+--------------+-------------+------------+--------------+--------------+2 rows in set (0.00 sec)
mysql > update product set id=id+1 where id=65032159 and category=3741760\GQuery OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql > select OBJECT_NAME,COUNT_UPDATE,COUNT_WRITE,COUNT_READ,COUNT_DELETE,COUNT_INSERT from `performance_schema`.`table_io_waits_summary_by_table` where OBJECT_NAME like "prod%";+---------------+--------------+-------------+------------+--------------+--------------+| OBJECT_NAME   | COUNT_UPDATE | COUNT_WRITE | COUNT_READ | COUNT_DELETE | COUNT_INSERT |+---------------+--------------+-------------+------------+--------------+--------------+| product       |            1 |           1 |          1 |            0 |            0 || product_order |            0 |           0 |          0 |            0 |            0 |+---------------+--------------+-------------+------------+--------------+--------------+2 rows in set (0.00 sec)
mysql > select product_id,count(*) from product_order where product_id in (65032159,65032160) group by product_id;+------------+----------+| product_id | count(*) |+------------+----------+|   65032160 |      897 |+------------+----------+1 row in set (0.02 sec)
mysql > select OBJECT_NAME,COUNT_UPDATE,COUNT_WRITE,COUNT_READ,COUNT_DELETE,COUNT_INSERT from `performance_schema`.`table_io_waits_summary_by_table` where OBJECT_NAME like "prod%";+---------------+--------------+-------------+------------+--------------+--------------+| OBJECT_NAME   | COUNT_UPDATE | COUNT_WRITE | COUNT_READ | COUNT_DELETE | COUNT_INSERT |+---------------+--------------+-------------+------------+--------------+--------------+| product       |            1 |           1 |          1 |            0 |            0 || product_order |            0 |           0 |      54028 |            0 |            0 |+---------------+--------------+-------------+------------+--------------+--------------+2 rows in set (0.00 sec)