问题描述
在统计表记录数时，平时我很少注意里面的细节，这几天有空分析了一下，下面是我的分析过程，不妥之处，还请指正。
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的逻辑读情况
root@localhost#mysql.sock : tc0105:39:52>show status like 'innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 200625 11:36:26 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 21727 |
| Innodb_buffer_pool_bytes_data | 355975168 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 353199 |
| Innodb_buffer_pool_pages_free | 11017 |
| Innodb_buffer_pool_pages_misc | 24 |
| Innodb_buffer_pool_pages_total | 32768 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 120950072 |
| Innodb_buffer_pool_reads | 576 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 13289147 |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)
root@localhost#mysql.sock : tc0105:39:54>select count(*) from test2;
+----------+
| count(*) |
+----------+
| 1120198 |
+----------+
1 row in set (0.13 sec)
root@localhost#mysql.sock : tc0105:39:57>show status like 'innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 200625 11:36:26 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 21727 |
| Innodb_buffer_pool_bytes_data | 355975168 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 353199 |
| Innodb_buffer_pool_pages_free | 11017 |
| Innodb_buffer_pool_pages_misc | 24 |
| Innodb_buffer_pool_pages_total | 32768 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 121076043 |
| Innodb_buffer_pool_reads | 576 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 13289147 |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)
root@localhost#mysql.sock : tc0105:39:59>
121076043-120950072=125971
root@localhost#mysql.sock : tc0105:40:30>show status like 'innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 200625 11:36:26 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 21728 |
| Innodb_buffer_pool_bytes_data | 355991552 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 353303 |
| Innodb_buffer_pool_pages_free | 11016 |
| Innodb_buffer_pool_pages_misc | 24 |
| Innodb_buffer_pool_pages_total | 32768 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 121120574 |
| Innodb_buffer_pool_reads | 576 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 13289849 |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)
root@localhost#mysql.sock : tc0105:40:31>select count(*) from test2 use index (PRIMARY);
+----------+
| count(*) |
+----------+
| 1120198 |
+----------+
1 row in set (0.15 sec)
root@localhost#mysql.sock : tc0105:40:33>show status like 'innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 200625 11:36:26 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_pages_data | 21728 |
| Innodb_buffer_pool_bytes_data | 355991552 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 353303 |
| Innodb_buffer_pool_pages_free | 11016 |
| Innodb_buffer_pool_pages_misc | 24 |
| Innodb_buffer_pool_pages_total | 32768 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 121251637 |
| Innodb_buffer_pool_reads | 576 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 13289849 |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)
121251637-121120574=131063
复制Innodb_buffer_pool_read_requests走主键查询用了131063，不走主键用了 125971，相差5092。
小结
- 执行计划不一样，和MySQL的CBO算法有关，使用optimizer_trace没有看到差异，也就不继续分析下去，其他专家如果有更好的方法可以分享一下。
- 逻辑读不一样和使用的索引字段有关。
- 这里提供MySQL的一些分析思路和方法，供大家参考。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
墨天轮个人数说知识点合集
JiekeXu
420次阅读
2025-04-01 15:56:03
MySQL数据库当前和历史事务分析
听见风的声音
413次阅读
2025-04-01 08:47:17
MySQL 生产实践-Update 二级索引导致的性能问题排查
chengang
379次阅读
2025-03-28 16:28:31
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
335次阅读
2025-04-17 17:02:24
MySQL 9.3 正式 GA，我却大失所望，新特性亮点与隐忧并存？
JiekeXu
334次阅读
2025-04-15 23:49:58
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
322次阅读
2025-04-15 14:48:05
云和恩墨杨明翰：安全生产系列之MySQL高危操作
墨天轮编辑部
297次阅读
2025-03-27 16:45:26
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
281次阅读
2025-04-07 12:14:29
记录MySQL数据库的一些奇怪的迁移需求！
陈举超
185次阅读
2025-04-15 15:27:53
[MYSQL] 服务器出现大量的TIME_WAIT, 每天凌晨就清零了
大大刺猬
178次阅读
2025-04-01 16:20:44
