Server version: 5.0.67
=================================================我断开mysql服务后,在重新启动 .
执行 set profiling=1;(InnoDB引擎)
然后 select * from table_name;
然后 show profiles ; 取query_id
最后 show profile cpu,block io for query n;
==================================================
此时,我是这样想的,既然我重新断开在连,
那么,我在select的时候,我就要访问硬盘数据,竟然我访问了硬盘数据,我就要用到IO吧..然后从硬盘读取数据放到 buffer pool里面吧。。 那么怎么也要用一下IO吧。。那为什么我在show profile block io for query N; 里面没有看到使用IO的情况呢? IO 显示为null ,,,为什么没有使用IO呢??
=================================================我断开mysql服务后,在重新启动 .
执行 set profiling=1;(InnoDB引擎)
然后 select * from table_name;
然后 show profiles ; 取query_id
最后 show profile cpu,block io for query n;
==================================================
此时,我是这样想的,既然我重新断开在连,
那么,我在select的时候,我就要访问硬盘数据,竟然我访问了硬盘数据,我就要用到IO吧..然后从硬盘读取数据放到 buffer pool里面吧。。 那么怎么也要用一下IO吧。。那为什么我在show profile block io for query N; 里面没有看到使用IO的情况呢? IO 显示为null ,,,为什么没有使用IO呢??
问题是,我是重启mysqld后,第一次执行的。。还是没有IO的使用情况。
请ACMAIN_CHM大哥,详解·
mysql> prompt \u:\d> PROMPT set to '\u:\d>' root:(none)>use optimizetest Database changed root:optimizetest>set profiling=1; Query OK, 0 rows affected (0.00 sec) root:optimizetest>select name2 from demo1 where name3 in ('3sinkinto1','3sinkinto2') limit 5; +------------+ | name2 | +------------+ | 2sinkinto1 | | 2sinkinto1 | | 2sinkinto1 | | 2sinkinto1 | | 2sinkinto1 | +------------+ 5 rows in set (0.08 sec) root:optimizetest>show profiles; +----------+------------+-----------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------------------------------------+ | 1 | 0.08230775 | select name2 from demo1 where name3 in ('3sinkinto1','3sinkinto2') limit 5 | +----------+------------+-----------------------------------------------------------------------------+ 1 row in set (0.00 sec) root:optimizetest>show profile block io for query 1; +--------------------+----------+--------------+---------------+ | Status | Duration | Block_ops_in | Block_ops_out | +--------------------+----------+--------------+---------------+ | starting | 0.000124 | NULL | NULL | | Opening tables | 0.081585 | NULL | NULL | | System lock | 0.000007 | NULL | NULL | | Table lock | 0.000012 | NULL | NULL | | init | 0.000061 | NULL | NULL | | optimizing | 0.000016 | NULL | NULL | | statistics | 0.000173 | NULL | NULL | | preparing | 0.000065 | NULL | NULL | | executing | 0.000007 | NULL | NULL | | Sending data | 0.000186 | NULL | NULL | | end | 0.000007 | NULL | NULL | | end | 0.000004 | NULL | NULL | | query end | 0.000006 | NULL | NULL | | freeing items | 0.000041 | NULL | NULL | | closing tables | 0.000007 | NULL | NULL | | logging slow query | 0.000003 | NULL | NULL | | cleaning up | 0.000005 | NULL | NULL | +--------------------+----------+--------------+---------------+ 17 rows in set (0.00 sec) 在此之前,我重启了一次mysqld..
在此之前,我重启了一次mysqld....
SQL codemysql> prompt \u:\d> PROMPT set to '\u:\d>' root:(none)>use optimizetest Database changed root:optimizetest>set profiling=1; Query OK, 0 rows affected (0.00 sec) root:optimizetest>select name2 from demo1 where name3 in ('3sinkinto1','3sinkinto2') limit 5; +------------+ | name2 | +------------+ | 2sinkinto1 | | 2sinkinto1 | | 2sinkinto1 | | 2sinkinto1 | | 2sinkinto1 | +------------+ 5 rows in set (0.08 sec) root:optimizetest>show profiles; +----------+------------+-----------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------------------------------------+ | 1 | 0.08230775 | select name2 from demo1 where name3 in ('3sinkinto1','3sinkinto2') limit 5 | +----------+------------+-----------------------------------------------------------------------------+ 1 row in set (0.00 sec) root:optimizetest>show profile block io for query 1; +--------------------+----------+--------------+---------------+ | Status | Duration | Block_ops_in | Block_ops_out | +--------------------+----------+--------------+---------------+ | starting | 0.000124 | NULL | NULL | | Opening tables | 0.081585 | NULL | NULL | | System lock | 0.000007 | NULL | NULL | | Table lock | 0.000012 | NULL | NULL | | init | 0.000061 | NULL | NULL | | optimizing | 0.000016 | NULL | NULL | | statistics | 0.000173 | NULL | NULL | | preparing | 0.000065 | NULL | NULL | | executing | 0.000007 | NULL | NULL | | Sending data | 0.000186 | NULL | NULL | | end | 0.000007 | NULL | NULL | | end | 0.000004 | NULL | NULL | | query end | 0.000006 | NULL | NULL | | freeing items | 0.000041 | NULL | NULL | | closing tables | 0.000007 | NULL | NULL | | logging slow query | 0.000003 | NULL | NULL | | cleaning up | 0.000005 | NULL | NULL | +--------------------+----------+--------------+---------------+ 17 rows in set (0.00 sec) 在此之前,我重启了一次mysqld....
Database changed
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)mysql> select name2 from demo1 where name3 in ('3sinkinto1','3sinkinto2') limit
5;
+------------+
| name2 |
+------------+
| 2sinkinto1 |
| 2sinkinto1 |
| 2sinkinto1 |
| 2sinkinto1 |
| 2sinkinto1 |
+------------+
5 rows in set (0.20 sec)mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.20212300
Query: select name2 from demo1 where name3 in ('3sinkinto1','3sinkinto2') li
mit 5
1 row in set (0.00 sec)mysql> show profile block io for query 1\G
*************************** 1. row ***************************
Status: starting
Duration: 0.000124
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 2. row ***************************
Status: Opening tables
Duration: 0.200374
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 3. row ***************************
Status: System lock
Duration: 0.000007
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 4. row ***************************
Status: Table lock
Duration: 0.000012
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 5. row ***************************
Status: init
Duration: 0.000069
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 6. row ***************************
Status: optimizing
Duration: 0.000020
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 7. row ***************************
Status: statistics
Duration: 0.000221
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 8. row ***************************
Status: preparing
Duration: 0.000086
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 9. row ***************************
Status: executing
Duration: 0.000009
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 10. row ***************************
Status: Sending data
Duration: 0.000235
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 11. row ***************************
Status: end
Duration: 0.000007
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 12. row ***************************
Status: end
Duration: 0.000004
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 13. row ***************************
Status: query end
Duration: 0.000006
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 14. row ***************************
Status: freeing items
Duration: 0.000935
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 15. row ***************************
Status: closing tables
Duration: 0.000008
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 16. row ***************************
Status: logging slow query
Duration: 0.000003
Block_ops_in: NULL
Block_ops_out: NULL
*************************** 17. row ***************************
Status: cleaning up
Duration: 0.000006
Block_ops_in: NULL
Block_ops_out: NULL
17 rows in set (0.00 sec)===========================================
在此之前,我重启了mysqld``