table_rows查詢優化_ZenDei技術網路在線

日常應用運維工作中，Dev或者db本身都需要統計表的行數，以此作為應用或者維護的一個信息參考。也許很多人會忽略select count(*) from table_name類似的sql對資料庫性能的影響，可當你在慢日誌平臺看到執行了數千次，每次執行4秒左右的查詢，你還會無動於衷嗎？作為一個有擔當敢於 ...

　　日常應用運維工作中，Dev或者db本身都需要統計表的行數，以此作為應用或者維護的一個信息參考。也許很多人會忽略select count(*) from table_name類似的sql對資料庫性能的影響，可當你在慢日誌平臺看到執行了數千次，每次執行4秒左右的查詢，你還會無動於衷嗎？作為一個有擔當敢於挑戰的dba，你們應該勇於說no，我覺得類似的需求不可避免但不應該是影響資料庫性能的因素，如果連這個都擺不平公司還能指望你乾什麼。經過幾番深思總結，我根據查詢的需求，分為模糊查詢和精確查詢，可以通過下麵的三種方式來擇優選擇。下麵測試是線上一個日誌表，表大小在6個G左右。

　　1、精確查詢知曉表中數據行數，這個時候我們就要使用count（）函數來統計表中行數的大小了。在innodb存儲引擎中count（*）函數是先從記憶體中讀取表中的數據到記憶體緩衝區，然後全表掃描獲得記錄行數的。但是這種方式過於簡單、直接暴力，對於小表查詢比較合適，對於頻繁的大表查詢就不適用了。尤其是在生產中表很大，且表除了聚集索引（主鍵索引）外，沒有其他非聚集索引（二級索引）的時候，無疑是一種巨大的災難。

mysql> select count(*) from operation_log;
+----------+
| count(*) |
+----------+
| 21049180 |
+----------+
1 row in set (10.92 sec)

mysql> explain select count(*) from rule_ceshi.operation_log;
+----+-------------+---------------+-------+---------------+----------+---------+------+----------+-------------+
| id | select_type | table         | type  | possible_keys | key      | key_len | ref  | rows     | Extra       |
+----+-------------+---------------+-------+---------------+----------+---------+------+----------+-------------+
|  1 | SIMPLE      | operation_log | index | NULL          | user_key | 194     | NULL | 20660338 | Using index |
+----+-------------+---------------+-------+---------------+----------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> show index from rule_ceshi.operation_log;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| operation_log |          0 | PRIMARY  |            1 | id          | A         |    20660338 |     NULL | NULL   |      | BTREE      |         |               |
| operation_log |          1 | user_key |            1 | user_key    | A         |     2951476 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> drop index user_key on rule_ceshi.operation_log;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select count(*) from rule_ceshi.operation_log; 
+----------+
| count(*) |
+----------+
| 21049180 |
+----------+
1 row in set (23.39 sec)

　　上面的測試結果表明，count（*）走聚集索引和非聚集索引都是索引全掃描，但是走非聚集索引比走聚集索引獲取記錄數更快，這是為什麼呢？我們通常不是說走主鍵索引是最快，難道這個原則在這裡不適用還是優化器出現bug。當我產生這個疑問的時候，也曾這樣懷疑，經過幾次度娘和FQ後，排除錯誤答案，終於可以很遺憾的告訴你主鍵索引確實是最快的，只是主鍵索引查詢是有前提條件的，至於什麼條件煩請查看我下一篇關於count（*）怎麼走索引，走那種索引分析。

　　2、上面的方式對單次查詢，在足夠配置的物理機上，顯然我們還是可以接受的。然而很多次的類似sql出現，對資料庫的性能也是一種不必要的損耗，因為這對業務發展並沒有很深的意義。我們知道對於select count（*） from table_name這樣的sql是沒有辦法通過索引優化的，那麼只能通過改寫sql進行優化了，這也是一個精通sql優化高手必備的技能。

　　如果你也想精確查詢表中的行數，又想查詢的時間能儘可能短，這個時候我們就要想到max（）和min（）函數了，通常我們統計最大值和最小值都是很快返回結果的。

 1 mysql> select ifnull(max(id),0)-ifnull(min(id),0)+1 as rows from rule_ceshi.operation_log;
 2 +----------+
 3 | rows     |
 4 +----------+
 5 | 21124162 |
 6 +----------+
 7 1 row in set (0.02 sec)
 8 
 9 mysql> explain select ifnull(max(id),0)-ifnull(min(id),0)+1 as rows from rule_ceshi.operation_log;
10 +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
11 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
12 +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
13 |  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
14 +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
15 1 row in set (0.01 sec)

　　當然使用這種優化改寫的前提是你的上產中表中有主鍵且是整數類型的，主鍵還需是連續的，也就是你的上產中沒有進行過delete from table where xxx=xxx的刪除行記錄操作，否則這樣統計還是不精準的。

　　3、我們知道MySQL自帶一個統計信息，平時我們的show命令之類的都來源資料庫中的統計表。如果我們的Dev告訴我們，只需要模糊查詢知曉表中數據行數呢？這個時候，你就可以通過MySQL自帶的information_schema.tables表的統計信息，初步判斷表的數據行大小。

mysql> select table_schema,table_name,table_type,table_rows from information_schema.tables where table_schema='rule_ceshi' and table_name='operation_log';
+--------------+---------------+------------+------------+
| table_schema | table_name    | table_type | table_rows |
+--------------+---------------+------------+------------+
| rule_ceshi   | operation_log | BASE TABLE |   20660338 |
+--------------+---------------+------------+------------+
1 row in set (0.01 sec)

mysql> explain select table_schema,table_name,table_type,table_rows from information_schema.tables where table_schema='rule_ceshi' and table_name='operation_log';
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
| id | select_type | table  | type | possible_keys | key                     | key_len | ref  | rows | Extra                                             |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
|  1 | SIMPLE      | tables | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Open_full_table; Scanned 0 databases |
+----+-------------+--------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
1 row in set (0.00 sec)

　　上面這種方式對於dba日常維護，判斷一個表的行數大小很有作用，必需知曉。