MySQL 5.7 统计表记录数执行效率对比分析

浅浅的花香味﹌ 2023-03-01 13:55 3阅读 0赞

# **问题描述** #

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

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;

\+\----+-------------+-------+------------+-------+---------------+----------------+---------+------+---------+----------+-------------+

\+\----+-------------+-------+------------+-------+---------------+----------------+---------+------+---------+----------+-------------+

\+\----+-------------+-------+------------+-------+---------------+----------------+---------+------+---------+----------+-------------+

1 row in set, 1 warning (0.07 sec)

root@localhost\#mysql.sock : tc0112:33:35>explain select count(\*) from test2 use index (PRIMARY);

\+\----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

\+\----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

\+\----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

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

root@localhost\#mysql.sock : tc0112:34:27>show index from test2;

\+\-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

\+\-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| test2 |          0 | PRIMARY        |            1 | id          | A         |     1054391 |     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;

\+\----------------------+----------+----------+------------+--------------+---------------+

\+\----------------------+----------+----------+------------+--------------+---------------+

| 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;

\+\----------------------+----------+----------+------------+--------------+---------------+

\+\----------------------+----------+----------+------------+--------------+---------------+

| 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%';