建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
MySQL之COUNT性能到底如何？
前言
在實(shí)際開發(fā)過程中，統(tǒng)計(jì)一個(gè)表的數(shù)據(jù)量是經(jīng)常遇到的需求，用來統(tǒng)計(jì)數(shù)據(jù)庫表的行數(shù)都會(huì)使用COUNT(*)，COUNT(1)或者COUNT(字段)，但是表中的記錄越來越多，使用COUNT(*)也會(huì)變得越來越慢，本文我們就來分析一下COUNT的性能到底如何。
創(chuàng)新互聯(lián)主要從事網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、網(wǎng)頁設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)鹽都,十年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):028-86922220
1.COUNT(1)、COUNT(*)與COUNT(字段)哪個(gè)更快？
執(zhí)行效果：
- COUNT(*)?MySQL 對(duì)COUNT(*)?進(jìn)行了優(yōu)化，COUNT(*)直接掃描主鍵索引記錄，并不會(huì)把全部字段取出來，直接按行累加。
- COUNT(1)InnoDB引擎遍歷整張表，但不取值，server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1”進(jìn)去，按行累加。
- COUNT(字段)如果這個(gè)“字段”是定義為NOT NULL，那么InnoDB 引擎會(huì)一行行地從記錄里面讀出這個(gè)字段，server 層判斷不能為NULL，按行累加;如果這個(gè)“字段”定義允許為NULL，那么InnoDB 引擎會(huì)一行行地從記錄里面讀出這個(gè)字段，然后把值取出來再判斷一下，不是 NULL才累加。
實(shí)驗(yàn)分析
- 本文測(cè)試使用的環(huán)境：
[root@zhyno1 ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)
[root@zhyno1 ~]# uname -a
Linux zhyno1 3.10.0-1160.62.1.el7.x86_64 #1 SMP Tue Apr 5 16:57:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux
- 測(cè)試數(shù)據(jù)庫采用的是（存儲(chǔ)引擎采用InnoDB，其它參數(shù)默認(rèn)）：
(Mon Jul 25 09:41:39 2022)[root@GreatSQL][(none)]>select version();
+-----------+
| version() |
+-----------+
| 8.0.25-16 |
+-----------+
1 row in set (0.00 sec)
實(shí)驗(yàn)開始：
#首先我們創(chuàng)建一個(gè)實(shí)驗(yàn)表
CREATE TABLE test_count (
`id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(20) NOT NULL,
`salary` int(1) NOT NULL,
KEY `idx_salary` (`salary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入1000W條數(shù)據(jù)
DELIMITER //
CREATE PROCEDURE insert_1000w()
BEGIN
DECLARE i INT;
SET i=1;
WHILE i<=10000000 DO
INSERT INTO test_count(name,salary) VALUES('KAiTO',1);
SET i=i+1;
END WHILE;
END//
DELIMITER ;
#執(zhí)行存儲(chǔ)過程
call insert_1000w();
接下來我們分別來實(shí)驗(yàn)一下：
- COUNT(1)花費(fèi)了4.19秒
(Sat Jul 23 22:56:04 2022)[root@GreatSQL][test]>select count(1) from test_count;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (4.19 sec)
- COUNT(*)花費(fèi)了4.16秒
(Sat Jul 23 22:57:41 2022)[root@GreatSQL][test]>select count(*) from test_count;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (4.16 sec)
- COUNT(字段)花費(fèi)了4.23秒
(Sat Jul 23 22:58:56 2022)[root@GreatSQL][test]>select count(id) from test_count;
+-----------+
| count(id) |
+-----------+
| 10000000 |
+-----------+
1 row in set (4.23 sec)
我們可以再來測(cè)試一下執(zhí)行計(jì)劃
- COUNT(*)
(Sat Jul 23 22:59:16 2022)[root@GreatSQL][test]>explain select count(*) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_count | NULL | index | NULL | idx_salary | 4 | NULL | 9980612 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
(Sat Jul 23 22:59:48 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
- COUNT(1)
(Sat Jul 23 23:12:45 2022)[root@GreatSQL][test]>explain select count(1) from test_count;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_count | NULL | index | NULL | idx_salary | 4 | NULL | 9980612 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
(Sat Jul 23 23:13:02 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select count(1) AS `count(1)` from `test`.`test_count` |
+-------+------+------------------------------------------------<
網(wǎng)站題目：MySQL之COUNT性能到底如何？
網(wǎng)站URL：http://m.fisionsoft.com.cn/article/dpejsss.html
