mysql更新数据量建议（MySQL5.7统计表记录数执行效率高吗）

MySQL在统计表记录数时，指定使用主键查询反而慢，在执行效率上进行对比分析。

问题描述

在统计表记录数时，平时我很少注意里面的细节，这几天有空分析了一下，下面是我的分析过程，不妥之处，还请指正。

root@localhost#mysql.sock : tc0112:28:09>select count(*) from test2; ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.13 sec) root@localhost#mysql.sock : tc0112:28:11>select count(*) from test2; ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.13 sec) root@localhost#mysql.sock : tc0112:28:13>select count(*) from test2; ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.13 sec) root@localhost#mysql.sock : tc0112:28:14>

查询了三次每次查询耗时都是0.13秒。

root@localhost#mysql.sock : tc0112:31:01>select count(*) from test2 use index (PRIMARY); ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.15 sec) root@localhost#mysql.sock : tc0112:31:02>select count(*) from test2 use index (PRIMARY); ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.15 sec) root@localhost#mysql.sock : tc0112:31:05>select count(*) from test2 use index (PRIMARY); ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.15 sec)

使用主键查询了三次，每次查询耗时都是0.15秒，为什么走主键会有0.02秒的差异呢？

分析过程

查看执行计划

我们首先想到的就是下看看执行计划是否一样。

root@localhost#mysql.sock : tc0112:33:34>explain select count(*) from test2; ---- ------------- ------- ------------ ------- --------------- ---------------- --------- ------ --------- ---------- ------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------- --------------- ---------------- --------- ------ --------- ---------- ------------- | 1 | SIMPLE | test2 | NULL | index | NULL | index_vote_num | 4 | NULL | 1114117 | 100.00 | Using index | ---- ------------- ------- ------------ ------- --------------- ---------------- --------- ------ --------- ---------- ------------- 1 row in set, 1 warning (0.07 sec) root@localhost#mysql.sock : tc0112:33:35>explain select count(*) from test2 use index (PRIMARY); ---- ------------- ------- ------------ ------- --------------- --------- --------- ------ --------- ---------- ------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------- --------------- --------- --------- ------ --------- ---------- ------------- | 1 | SIMPLE | test2 | NULL | index | NULL | PRIMARY | 4 | NULL | 1114117 | 100.00 | Using index | ---- ------------- ------- ------------ ------- --------------- --------- --------- ------ --------- ---------- ------------- 1 row in set, 1 warning (0.00 sec)

从显示的执行计划我们可以看出，两个SQL语句的执行计划不一样，走主键索引反而慢0.02秒，什么原因？

查看索引情况如下：

root@localhost#mysql.sock : tc0112:34:27>show index from test2; ------- ------------ ---------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ------- ------------ ---------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- | test2 | 0 | PRIMARY | 1 | id | A | 1054391 | NULL | NULL | | BTREE | | | | test2 | 1 | index_name | 1 | name | A | 1055674 | NULL | NULL | | BTREE | | | | test2 | 1 | index_vote_num | 1 | vote_num | A | 9929 | NULL | NULL | | BTREE | | | | test2 | 1 | index_group | 1 | group_id | A | 501 | NULL | NULL | | BTREE | | | | test2 | 1 | index_group | 2 | sdate | A | 824214 | NULL | NULL | YES | BTREE | | | ------- ------------ ---------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 5 rows in set (0.00 sec)

cardinality是索引中不重复记录的预估值。

使用profile查看耗时差异在什么地方

root@localhost#mysql.sock : tc0112:41:21>select count(*) from test2; ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.14 sec) root@localhost#mysql.sock : tc0112:41:33>show profile cpu,block io for QUERY 3; ---------------------- ---------- ---------- ------------ -------------- --------------- | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | ---------------------- ---------- ---------- ------------ -------------- --------------- | starting | 0.000047 | 0.000033 | 0.000009 | 0 | 0 | | checking permissions | 0.000005 | 0.000004 | 0.000001 | 0 | 0 | | Opening tables | 0.000013 | 0.000010 | 0.000003 | 0 | 0 | | init | 0.000011 | 0.000008 | 0.000003 | 0 | 0 | | System lock | 0.000006 | 0.000005 | 0.000001 | 0 | 0 | | optimizing | 0.000003 | 0.000003 | 0.000001 | 0 | 0 | | statistics | 0.000010 | 0.000007 | 0.000002 | 0 | 0 | | preparing | 0.000007 | 0.000006 | 0.000002 | 0 | 0 | | executing | 0.000002 | 0.000001 | 0.000000 | 0 | 0 | | Sending data | 0.133695 | 0.133527 | 0.000000 | 0 | 0 | | end | 0.000016 | 0.000009 | 0.000000 | 0 | 0 | | query end | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | | closing tables | 0.000008 | 0.000008 | 0.000000 | 0 | 0 | | freeing items | 0.000017 | 0.000017 | 0.000000 | 0 | 0 | | cleaning up | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | ---------------------- ---------- ---------- ------------ -------------- --------------- 15 rows in set, 1 warning (0.00 sec) root@localhost#mysql.sock : tc0112:41:27>select count(*) from test2 use index (PRIMARY); ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.15 sec) root@localhost#mysql.sock : tc0112:41:56>show profile cpu,block io for query 4; ---------------------- ---------- ---------- ------------ -------------- --------------- | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | ---------------------- ---------- ---------- ------------ -------------- --------------- | starting | 0.000052 | 0.000036 | 0.000010 | 0 | 0 | | checking permissions | 0.000005 | 0.000003 | 0.000001 | 0 | 0 | | Opening tables | 0.000013 | 0.000011 | 0.000004 | 0 | 0 | | init | 0.000010 | 0.000007 | 0.000002 | 0 | 0 | | System lock | 0.000006 | 0.000005 | 0.000001 | 0 | 0 | | optimizing | 0.000003 | 0.000002 | 0.000001 | 0 | 0 | | statistics | 0.000010 | 0.000008 | 0.000002 | 0 | 0 | | preparing | 0.000008 | 0.000006 | 0.000002 | 0 | 0 | | executing | 0.000002 | 0.000001 | 0.000000 | 0 | 0 | | Sending data | 0.149678 | 0.149268 | 0.000191 | 0 | 0 | | end | 0.000014 | 0.000006 | 0.000002 | 0 | 0 | | query end | 0.000009 | 0.000007 | 0.000002 | 0 | 0 | | closing tables | 0.000008 | 0.000006 | 0.000002 | 0 | 0 | | freeing items | 0.000014 | 0.000011 | 0.000003 | 0 | 0 | | cleaning up | 0.000015 | 0.000011 | 0.000003 | 0 | 0 | ---------------------- ---------- ---------- ------------ -------------- --------------- 15 rows in set, 1 warning (0.00 sec)

差异主要出现在Sending data部分，Sending data包括“收集 发送" 数据。

查看optimizer_trace

root@localhost#mysql.sock : tc0112:45:02>set optimizer_trace='enabled=on'; Query OK, 0 rows affected (0.00 sec) root@localhost#mysql.sock : tc0112:46:05>set optimizer_trace_max_mem_size=1000000; Query OK, 0 rows affected (0.00 sec) root@localhost#mysql.sock : tc0112:46:11>set end_markers_in_json=on; Query OK, 0 rows affected (0.00 sec) root@localhost#mysql.sock : tc0112:46:16>select count(*) from test2; ---------- | count(*) | ---------- | 1120198 | ---------- 1 row in set (0.13 sec) root@localhost#mysql.sock : tc0112:46:22>select * from information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: select count(*) from test2 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `test2`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`test2`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`test2`", "table_scan": { "rows": 1114117, "cost": 6375 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`test2`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 1114117, "access_type": "scan", "resulting_rows": 1.11e6, "cost": 229198, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1.11e6, "cost_for_plan": 229198, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`test2`", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`test2`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec) root@localhost#mysql.sock : tc0112:46:33> root@localhost#mysql.sock : tc0104:49:23>select * from information_schema.optimizer_trace\G *************************** 1. row *************************** QUERY: select count(*) from test2 use index (PRIMARY) TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `test2` USE INDEX (PRIMARY)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "table_dependencies": [ { "table": "`test2` USE INDEX (PRIMARY)", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "rows_estimation": [ { "table": "`test2` USE INDEX (PRIMARY)", "table_scan": { "rows": 1114117, "cost": 6375 } /* table_scan */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`test2` USE INDEX (PRIMARY)", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 1114117, "access_type": "scan", "resulting_rows": 1.11e6, "cost": 229198, "chosen": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 1.11e6, "cost_for_plan": 229198, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": null, "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`test2` USE INDEX (PRIMARY)", "attached": null } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`test2` USE INDEX (PRIMARY)" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ ] /* steps */ } /* join_execution */ } ] /* steps */ } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.00 sec)

根据收集到的optimizer_trace信息，没有看到查异。

使用strace分析

使用的命令

strace -o ./xxxx.log -T -tt -f -p `pidof mysqld`

收集到的主要内容如下：

2779 17:31:57.090832 recvfrom(19, "\3select count(*) from test2", 27, MSG_DONTWAIT, NULL, NULL) = 27 <0.000011> 2746 17:31:57.131173 <... futex resumed> ) = -1 ETIMEDOUT (Connection timed out) <1.000589> 2746 17:31:57.131221 futex(0x3176e68, FUTEX_WAKE_PRIVATE, 1) = 0 <0.000009> 2746 17:31:57.131471 futex(0x3176e94, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 388151, {1593250318, 131449000}, ffffffff <unfinished ...> 2745 17:31:57.198173 <... futex resumed> ) = -1 ETIMEDOUT (Connection timed out) <1.000774> 2745 17:31:57.198219 futex(0x420a018, FUTEX_WAKE_PRIVATE, 1) = 0 <0.000008> 2745 17:31:57.198251 futex(0x420a044, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 388233, {1593250318, 198244000}, ffffffff <unfinished ...> 2779 17:31:57.221047 sendto(19, "\1\0\0\1\1\36\0\0\2\3def\0\0\0\10count(*)\0\f?\0\25\0\0"..., 62, MSG_DONTWAIT, NULL, 0) = 62 <0.000157> 2779 17:31:57.221269 recvfrom(19, 0x57addc0, 4, MSG_DONTWAIT, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable) <0.000009> 2779 17:31:57.221303 poll([{fd=19, events=POLLIN|POLLPRI}], 1, 600000 <unfinished ...> 2779 17:32:21.413376 recvfrom(19, "\3select count(*) from test2 use "..., 47, MSG_DONTWAIT, NULL, NULL) = 47 <0.000010> 2748 17:32:21.446209 <... nanosleep resumed> NULL) = 0 <1.009241> 2748 17:32:21.446265 nanosleep({1, 0}, <unfinished ...> 2738 17:32:21.501216 <... futex resumed> ) = -1 ETIMEDOUT (Connection timed out) <1.007208> 2738 17:32:21.501262 futex(0x3177018, FUTEX_WAKE_PRIVATE, 1) = 0 <0.000008> 2738 17:32:21.501297 futex(0x3177044, FUTEX_WAIT_BITSET_PRIVATE|FUTEX_CLOCK_REALTIME, 388723, {1593250342, 501290000}, ffffffff <unfinished ...> 2737 17:32:21.523241 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510550> 2736 17:32:21.523280 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510589> 2735 17:32:21.523287 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510591> 2734 17:32:21.523292 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510593> 2733 17:32:21.523298 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510594> 2732 17:32:21.523303 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510597> 2731 17:32:21.523309 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510598> 2730 17:32:21.523314 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510600> 2729 17:32:21.523319 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510601> 2728 17:32:21.523324 <... io_getevents resumed> [], {0, 500000000}) = 0 <0.510594> 2737 17:32:21.523368 io_getevents(140091831586816, 1, 256, <unfinished ...> 2736 17:32:21.523377 io_getevents(140091831599104, 1, 256, <unfinished ...> 2735 17:32:21.523382 io_getevents(140091831611392, 1, 256, <unfinished ...> 2734 17:32:21.523385 io_getevents(140091831623680, 1, 256, <unfinished ...> 2733 17:32:21.523389 io_getevents(140091831926784, 1, 256, <unfinished ...> 2732 17:32:21.523393 io_getevents(140091831939072, 1, 256, <unfinished ...> 2731 17:32:21.523397 io_getevents(140092021788672, 1, 256, <unfinished ...> 2730 17:32:21.523401 io_getevents(140092021800960, 1, 256, <unfinished ...> 2729 17:32:21.523405 io_getevents(140091831902208, 1, 256, <unfinished ...> 2728 17:32:21.523408 io_getevents(140091831914496, 1, 256, <unfinished ...> 2779 17:32:21.563856 sendto(19, "\1\0\0\1\1\36\0\0\2\3def\0\0\0\10count(*)\0\f?\0\25\0\0"..., 62, MSG_DONTWAIT, NULL, 0) = 62 <0.000103> 2779 17:32:21.564086 recvfrom(19, 0x57addc0, 4, MSG_DONTWAIT, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable) <0.000008> 2779 17:32:21.564120 poll([{fd=19, events=POLLIN|POLLPRI}], 1, 600000 <unfinished ...>

发现使用主键查询的步骤更多，自然也就增加了耗时。

查看一下SQL的逻辑读情况

...

✨ 接下来内容请查看原文（https://www.modb.pro/db/26860?YYF）进行观看~

更多数据库相关内容，可访问墨天轮（https://www.modb.pro?YYF）进行浏览。