mysql5.7 执行表_MySQL 5.7 统计表记录数执行效率对比分析

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=125971root@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