日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問(wèn)題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
MySQL之COUNT性能到底如何？

前言

在實(shí)際開(kāi)發(fā)過(guò)程中，統(tǒng)計(jì)一個(gè)表的數(shù)據(jù)量是經(jīng)常遇到的需求，用來(lái)統(tǒng)計(jì)數(shù)據(jù)庫(kù)表的行數(shù)都會(huì)使用COUNT(*)，COUNT(1)或者COUNT(字段)，但是表中的記錄越來(lái)越多，使用COUNT(*)也會(huì)變得越來(lái)越慢，本文我們就來(lái)分析一下COUNT的性能到底如何。

創(chuàng)新互聯(lián)主要從事網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)鹽都,十年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來(lái)電咨詢建站服務(wù):028-86922220

1.COUNT(1)、COUNT(*)與COUNT(字段)哪個(gè)更快？

執(zhí)行效果：

  • COUNT(*)?MySQL 對(duì)COUNT(*)?進(jìn)行了優(yōu)化，COUNT(*)直接掃描主鍵索引記錄，并不會(huì)把全部字段取出來(lái)，直接按行累加。
  • COUNT(1)InnoDB引擎遍歷整張表，但不取值，server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1”進(jìn)去，按行累加。
  • COUNT(字段)如果這個(gè)“字段”是定義為NOT NULL，那么InnoDB 引擎會(huì)一行行地從記錄里面讀出這個(gè)字段，server 層判斷不能為NULL，按行累加;如果這個(gè)“字段”定義允許為NULL，那么InnoDB 引擎會(huì)一行行地從記錄里面讀出這個(gè)字段，然后把值取出來(lái)再判斷一下，不是 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ù)庫(kù)采用的是（存儲(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)開(kāi)始：

#首先我們創(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ǔ)過(guò)程
call insert_1000w();

接下來(lái)我們分別來(lái)實(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)

我們可以再來(lái)測(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性能到底如何？                                                

                                                本文來(lái)源：http://www.5511xx.com/article/dpejsss.html