點擊下載Navicat for MySQL

在上周的在 MySQL 中獲取高級的行計數（第 2 部分）文章中，我們使用了原生的 COUNT() 函數來計算唯一值以及滿足條件的值。在今天的最后一部分中，我們將學習如何從數據庫或整個模式中的所有表中獲取行計數。

查詢 information_schema 數據庫

你不必對每個表運行計數查詢以獲取行數。如果你計劃多次運行它，這將是繁瑣的并且可能需要外部腳本。

INFORMATION_SCHEMA 數據庫是每個 MySQL 實例存儲有關 MySQL 服務器維護的所有其他數據庫的信息的位置。它有時也稱為數據字典和系統目錄，是查找有關數據庫、表、列的數據類型或訪問權限的信息的理想位置。

INFORMATION_SCHEMA“TABLES”表提供有關數據庫中還有哪些其他表的信息。通過查詢“TABLES”表，你可以使用單個查詢獲得精確的行計數。

一個數據庫的表計數

獲取一個數據庫的行計數很容易。只需添加一個 WHERE 子句，其條件是 table_schema 列與你的數據庫名匹配：

SELECT
TABLE_NAME,
TABLE_ROWS
FROM
`information_schema`.`tables`
WHERE
`table_schema` = 'YOUR_DB_NAME';

整個模式的表計數

獲取模式中所有數據庫的行數需要更多的工作量。為此，我們必須使用已準備的語句。

在語句中，group_concat() 函數將多行打包成一個字符串，以便將表名列表轉換為由聯合連接的多個計數的一個字符串。

Select
-- Sort the tables by count
concat(
'select * from (',
-- Aggregate rows into a single string connected by unions
group_concat(
-- Build a "select count(1) from db.tablename" per table
concat('select ',
quote(db), ' db, ',
quote(tablename), ' tablename, '
'count(1) "rowcount" ',
'from ', db, '.', tablename)
separator ' union ')
, ') t order by 3 desc')
into @sql
from (
select
table_schema db,
table_name tablename
from information_schema.tables
where table_schema not in
('performance_schema', 'mysql', 'information_schema')
) t;

我們的串聯 select 語句保存在 @sql 變量中，以便我們可以將它作為已準備的語句運行：

關于速度和準確性

這些查詢在 MyISAM 表上運行得非常快，并生成非常精確的結果。但是，如 InnoDB 之類的事務存儲引擎不會在表中保留內部的行計數。而是，在表中采樣許多隨機頁，然后估計整個表的總行數。MVCC（一種允許并發訪問行的功能）的后果是，在任何一個時間點，都會有多個版本的行。因此，實際的 count(1) 將取決于事務開始的時間及其隔離級別。像 InnoDB 這樣的事務存儲引擎，你可以預料其計數的準確性為實際行數的 ±4% 以內。