MySQL中隐藏了外键约束的代价
2022-04-07 22:05 abce 阅读(70) 评论(0) 编辑 收藏 举报MySQL中隐藏了外键约束的代价
示例表
CREATE TABLE `product` (
`category` int NOT NULL,
`id` int NOT NULL,
`price` decimal(10,0) DEFAULT NULL,
PRIMARY KEY (`category`,`id`)
) ENGINE=InnoDB;
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: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: Using where
1 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: product
REFERENCED_COLUMN_NAME: category
*************************** 2. row ***************************
TABLE_NAME: product_order
COLUMN_NAME: product_id
CONSTRAINT_NAME: product_order_ibfk_1
REFERENCED_TABLE_NAME: product
REFERENCED_COLUMN_NAME: id
2 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\G
Query 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\G
Query 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\G
Query 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)
不幸的是，performance_schema也没有考虑到外键约束造成的真正影响。是否是bug，需要参看：https://bugs.mysql.com/bug.php?id=106012
在使用外键约束时查看DML查询和系统负载时需要谨慎！也许你对一个简单的单行更新或删除需要这么多时间感到惊讶？可能是MySQL 在后台更改了数千行并隐藏了这个事实！
