點(diǎn)擊下載Navicat for MySQL

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

查詢 information_schema 數(shù)據(jù)庫

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

INFORMATION_SCHEMA 數(shù)據(jù)庫是每個(gè) MySQL 實(shí)例存儲有關(guān) MySQL 服務(wù)器維護(hù)的所有其他數(shù)據(jù)庫的信息的位置。它有時(shí)也稱為數(shù)據(jù)字典和系統(tǒng)目錄，是查找有關(guān)數(shù)據(jù)庫、表、列的數(shù)據(jù)類型或訪問權(quán)限的信息的理想位置。

INFORMATION_SCHEMA“TABLES”表提供有關(guān)數(shù)據(jù)庫中還有哪些其他表的信息。通過查詢“TABLES”表，你可以使用單個(gè)查詢獲得精確的行計(jì)數(shù)。

一個(gè)數(shù)據(jù)庫的表計(jì)數(shù)

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

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

整個(gè)模式的表計(jì)數(shù)

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

在語句中，group_concat() 函數(shù)將多行打包成一個(gè)字符串，以便將表名列表轉(zhuǎn)換為由聯(lián)合連接的多個(gè)計(jì)數(shù)的一個(gè)字符串。

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;

我們的串聯(lián) select 語句保存在 @sql 變量中，以便我們可以將它作為已準(zhǔn)備的語句運(yùn)行：

關(guān)于速度和準(zhǔn)確性

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