MySQL count知多少

来源:https://www.cnblogs.com/gjc592/archive/2020/04/04/12634582.html
-Advertisement-
Play Games

統計一個表的數據量是經常遇到的需求,但是不同的表設計及不同的寫法,統計性能差別會有較大的差異,下麵就簡單通過實驗進行測試(大家測試的時候註意緩存的情況,否則影響測試結果)。 1、 準備工作 為了後續測試工作的進行,先準備幾張用於測試的表及數據,為了使測試數據具有參考意義,建議測試表的數據量大一點,以 ...


統計一個表的數據量是經常遇到的需求,但是不同的表設計及不同的寫法,統計性能差別會有較大的差異,下麵就簡單通過實驗進行測試(大家測試的時候註意緩存的情況,否則影響測試結果)。

1、 準備工作

為了後續測試工作的進行,先準備幾張用於測試的表及數據,為了使測試數據具有參考意義,建議測試表的數據量大一點,以免查詢時間太小,因此,可以繼續使用之前常用的連續數生成大法,如下:

/* 創建連續數表 */
CREATE TABLE nums(id INT primary key);

/* 生成連續數的存儲過程,優化過後的 */
DELIMITER $$
CREATE  PROCEDURE `sp_createNum`(cnt INT )
BEGIN
    DECLARE i INT  DEFAULT 1;
    TRUNCATE TABLE nums;
    INSERT INTO nums SELECT i;
    WHILE i < cnt DO
      BEGIN
        INSERT INTO nums SELECT id + i FROM nums WHERE id + i<=cnt;
        SET i = i*2;
      END;
    END WHILE;
END$$

DELIMITER ;

生成數據,本次準備生成1kw條記錄

/* 調用存儲過程 */
mysql> call sp_createNum(10000000);
Query OK, 1611392 rows affected (32.07 sec)

如果逐條迴圈,那時間相當長,大家可以自行測試,參考鏈接 效率提升16800倍的連續整數生成方法

1.1 創建innodb表

生成3張表innodb表,如下:

nums_1表只有字元串主鍵欄位

/*  生成只有一個字元串類型欄位主鍵的表nums_1 */
mysql> create table  nums_1 (p1 varchar(32) primary key ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

/*  導入數據,將id通過md5函數轉換為字元串 */
mysql> insert into  nums_1 select md5(id) from nums;
Query OK, 10000000 rows affected (1 min 12.63 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

nums_2表有5個欄位 ,其中主鍵為字元串類型欄位的p1,其他欄位為整型的id,非空的c1,可為空的c2,可為空的c3。

其中c1,c2欄位內容完全一致,差別是欄位約束不一樣(c1不可為空,c2可為空),c3與c1,c2的差別在於c1中aa開頭的值在c3中為null,其他內容一樣。

/* 創建表nums_2 */
mysql> create table nums_2(p1 varchar(32) primary key ,id int ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=innodb;
Query OK, 0 rows affected (1.03 sec)

/*導入數據 */
mysql> insert into  nums_2(id,p1,c1,c2,c3) select id,md5(id),left(md5(id),10),left(md5(id),10),if(,left(md5(id),10) like 'aa%',null,,left(md5(id),10)) from nums;
Query OK, 10000000 rows affected (5 min 6.68 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

nums_3表的內容與nums_2完全一樣,區別在於主鍵欄位不一樣,c3表為整型的id

/*  創建表nums_3 */
mysql> create table nums_3(p1 varchar(32) ,id int primary key  ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

/* 因為內容完全一致,直接從nums_2 中導入 */
mysql> insert into nums_3 select  * from nums_2;
Query OK, 10000000 rows affected (3 min 18.81 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

1.2 創建MyISAM引擎表

再創建一張MyISAM的表,表結構及內容均與nums_2也一致,只是引擎為MyISAM。

/* 創建MyISAM引擎的nums_4表*/
mysql> create table nums_4(p1 varchar(32) not null  primary key ,id int  ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

/* 直接從nums_2表導入數據 */
mysql> insert into nums_4 select  * from nums_2;
Query OK, 10000000 rows affected (3 min 16.78 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

 

2、 查詢一張表數據量的方法

查詢一張表的數據量有如下幾種:

查詢大致數據量,可以查統計信息,2.1中會介紹具體方法

精確查找數據量,則可以通過count(主鍵欄位),count(*), count(1) [這裡的1可以替換為任意常量]

2.1  非精確查詢

如果只是查一張表大致有多少數據,尤其是很大的表 只是查詢其表屬於什麼量級的(百萬、千萬還是上億條),可以直接查詢統計信息,查詢方式有如下幾種:

查詢索引信息,其中Cardinality 為大致數據量(查看主鍵PRIMARY行的值,如果為多列的複合主鍵,則查看最後一列的Cardinality 值)

mysql> show index from nums_2;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| nums_2 |          0 | PRIMARY  |            1 | p1          | A         |     9936693 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

查看表狀態,其中Rows為大致數據量

mysql> show table status like  'nums_2';
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| nums_2 | InnoDB |      10 | Dynamic    | 9936693 |            111 |  1105182720 |               0 |   2250178560 |   4194304 |           NULL | 2020-04-04 19:31:34 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

直接查看STATISTICS或TABLES表,內容與查看索引信息或表狀態類似,其中TABLE_ROWS的內容為大致的數據量

mysql> select   * from  information_schema.tables where table_schema='testdb' and table_name like  'nums_2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | testdb       | nums_2     | BASE TABLE | InnoDB |      10 | Dynamic    |    9936693 |            111 |  1105182720 |               0 |   2250178560 |   4194304 |           NULL | 2020-04-04 19:31:34 | NULL        | NULL       | utf8_general_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.00 sec)

註意:

  • innodb引起的表通過以上3種方式均可查詢對應表的大致數據量,且結果相同,因為均是取自相同的統計信息
  • MyISAM表的結果是精確值(表數據量,不包含其他欄位)
mysql> select   * from  information_schema.tables where table_schema='testdb' and table_name like  'nums_4';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME          | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------+
| def           | testdb       | nums_4     | BASE TABLE | MyISAM |      10 | Dynamic    |   10000000 |             75 |   759686336 | 281474976710655 |    854995968 |         0 |           NULL | 2020-04-04 19:20:23 | 2020-04-04 19:21:45 | 2020-04-04 19:23:45 | utf8_general_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------+
1 row in set (0.00 sec)

2.2  精確查找

因為2.1中innodb的表查詢的結果都是統計值,非準備值,實際工作中大多數情況下需要統計精確值,那麼查詢精確值的方法有如下幾種,且所有引擎的表都適用。

count(主鍵)

mysql> select count(p1) from nums_2;
+-----------+
| count(p1) |
+-----------+
|  10000000 |
+-----------+
1 row in set (1.60 sec)

count(1)

其中的1可以是任意常量,例如 count(2),count('a‘)等

mysql> select count(1) from nums_2;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)

count(*) 

mysql> select count(*) from nums_2;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.52 sec)

 

3、 count的性能對比

對比 count(主鍵) count(1)  count(*)   count(非空欄位) count(可為空欄位)  性能對比

3.1  MyISAM引擎表

3.1.1 查詢整張表數據量

如果想精確查詢一張MyISAM表的數據量,使用 count(主鍵) count(1)  count(*) 效率均一致,直接查出準確結果,耗時幾乎為0s

mysql> select count(p1) from nums_4;
+-----------+
| count(p1) |
+-----------+
|  10000000 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(1) from nums_4;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from nums_4;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

執行計劃也均一致,可以看出沒有通過主鍵或其他索引掃描的方式統計

mysql> explain select count(*) from nums_4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(p1) from nums_4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(1) from nums_4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

小結:

MyISAM的方法查整表數據量效率情況為 count(主鍵)=  count(1) = count(*)

3.1.2 查詢部分數據

查詢部分數據的時候則無法直接從統計信息獲取,因此耗時情況大致如下:

mysql> select count(p1) from nums_4 where  p1 like 'aa%';
+-----------+
| count(p1) |
+-----------+
|     39208 |
+-----------+
1 row in set (0.14 sec)

mysql> select count(1) from nums_4 where  p1 like 'aa%';
+----------+
| count(1) |
+----------+
|    39208 |
+----------+
1 row in set (0.13 sec)

mysql> select count(*) from nums_4 where p1 like 'aa%';
+----------+
| count(*) |
+----------+
| 39208 |
+----------+
1 row in set (0.13 sec)

執行計劃其實均一樣:

mysql> explain select count(1) from nums_4 where  p1 like 'aa%';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | nums_4 | NULL       | range | PRIMARY       | PRIMARY | 98      | NULL | 42603 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

小結:  MyISAM引擎表統計部分數據的時候直接得出數據量,也許掃描數據進行統計,幾種寫法效率相近。

3.2   innodb引擎表

innodb引擎因為要支持MVCC,因此不能整表數據量持久化保存,每次查詢均需遍歷統計,但是不同的寫法,查詢效率是有差別的,後面將進行不同維度進行對比。

3.2.1  不同寫法的性能對比

通過 count(主鍵),count(1) , count(*) 對比查詢效率

mysql> select count(p1) from nums_2  ;
+-----------+
| count(p1) |
+-----------+
|  10000000 |
+-----------+
1 row in set (1.68 sec)

mysql> select count(1) from nums_2  ;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.37 sec)

mysql> select count(*) from nums_2  ;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.38 sec)

簡單的對比發現,查詢性能結果為 count(主鍵) < count(1) ≈ count(*)

但是查看執行計劃都是如下情況

mysql> explain select count(p1) from nums_2;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | nums_2 | NULL       
              
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 安裝流程 這裡使用 brew 來安裝軟體。 安裝 查看安裝信息(經常用到, 比如查看安裝目錄等) 安裝後,主要看brew把nginx安裝到哪裡去了,預設是安裝到 常用命令記錄 查看nginx版本 啟動nginx服務 訪問: 後,看到如下內容就是啟動成功了。 如果沒有啟動成功,查看一下進程, : 查看 ...
  • 本次實驗環境:cn_windows_server_2019_x64_dvd_4de40f33cn_windows_7_enterprise_x64_dvd_x15-70741第一步:用戶創建第二步:添加主機第三步:資源分配1、用戶創建點擊工具選擇Active Directory用戶和電腦右鍵添加一... ...
  • 本次實驗環境:cn_windows_server_2019_x64_dvd_4de40f33第一步:安裝DNS服務搭建,提供域環境的功能變數名稱解析;第二部:安裝Active Directory服務搭建,域服務。1、安裝DNS服務安裝DNS服務,伺服器必須設置為固定IP地址。伺服器管理模板點擊管理-添加角色... ...
  • CentOS7主要有rpm和yum這兩種包軟體的管理。兩者有功能上的區別,其中主要區別是:yum使用簡單但需要聯網,yum會去網上包源去獲取所需要的軟體包。而rpm的需要做的事情就更細一些,比如我們需要記錄軟體的安裝位置、版本、信息等,類似macOS的homebrew。 rpm的工作原理是以一種數據 ...
  • 更多電腦使用技巧可以訪問 "https://xiaoheidiannao.com" 查看哦 今天打開微信電腦版的時候就自動更新到了2.9.0測試版(可能早已經有人更新到此版本了),更新內容如下: 1.Windows7及以上系統新增小程式面板,可查看最近使用和我的小程式。 2.聊天中可以引用更多類型的 ...
  • 先安裝zsh 安裝oh my zsh 手動安裝 自動安裝 ` 不管使用手動 自動安裝,完成後都需要重啟。oh my zsh才能生效。 修改主題 挑選你喜歡的主題:https://github.com/robbyrussell/oh my zsh/wiki/Themes ...
  • Redis是一個開源的、基於記憶體的數據結構存儲器,可以用作資料庫、緩存和消息中間件 Redis最常用的功能 緩存 分散式鎖 ...
  • 創建3台虛擬機 主機為桌面版 其他為迷你版本 ******************************常用命令、進程名稱****************************啟動集群命令: start-all.sh啟動zookeeper: zkServer.sh start 啟動journal ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...