Apakah prinsip prestasi MySQL COUNT(*)

1.Yang manakah lebih cepat, COUNT(1), COUNT(*) atau COUNT(medan)?

Kesan pelaksanaan:

• COUNT(*)MySQL telah mengoptimumkan count(*) count(*) terus mengimbas rekod indeks kunci utama dan tidak Semua medan akan dikeluarkan dan dikumpul terus mengikut baris.

• COUNT(1)Enjin InnoDB merentasi seluruh jadual, tetapi tidak mengambil nilai Lapisan pelayan meletakkan nombor "1" dalam setiap baris yang dikembalikan dan mengumpulkannya baris demi baris.

• COUNT(字段)Jika "medan" ini ditakrifkan sebagai NOT NULL, maka enjin InnoDB akan membaca medan ini dari rekod baris demi baris, dan lapisan pelayan akan menilai bahawa ia tidak boleh menjadi NULL, Pengumpulan baris demi baris; Jika takrifan "medan" membenarkan NULL, maka enjin InnoDB akan membaca medan ini dari baris demi baris, dan kemudian mengeluarkan nilainya dan menilai semula terkumpul.

Analisis eksperimen

Persekitaran yang digunakan untuk ujian dalam artikel ini:

[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

Pangkalan data ujian menggunakan ( enjin storan Menggunakan InnoDB, parameter lain adalah lalai):

(Mon Jul 25 09:41:39 2022)[root@GreatSQL][(none)]>select version();
+-----------+
| version() |
+-----------+
| 8.0.25-16 |
+-----------+
1 row in set (0.00 sec)

Mulakan percubaan:

#首先我们创建一个实验表

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条数据
DELIMITER //
CREATE PROCEDURE insert_1000w()
BEGIN
    DECLARE i INT;
    SET i=1;
    WHILE i<=10000000 DO
        INSERT INTO test_count(name,salary) VALUES(&#39;KAiTO&#39;,1);
        SET i=i+1;
    END WHILE;
END//
DELIMITER ;
#执行存储过程
call insert_1000w();

Seterusnya, mari bereksperimen secara berasingan:

COUNT(1)Ia mengambil masa 4.19 saat

(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(*)Ia mengambil masa 4.16 saat

(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(字段)Ia mengambil masa 4.23 saat

(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)

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` |
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

COUNT(字段)

(Sat Jul 23 23:13:14 2022)[root@GreatSQL][test]>explain select count(id) 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:29 2022)[root@GreatSQL][test]>show warnings;
+-------+------+-----------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(`test`.`test_count`.`id`) AS `count(id)` from `test`.`test_count` |
+-------+------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Perlu diingatkan bahawa jika tiada dalam COUNT Untuk medan kunci utama,

(Tue Jul 26 14:01:57 2022)[root@GreatSQL][test]>explain select count(name) from test_count where id <100 ;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_count | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   99 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Hasil eksperimen

• 1 Daripada eksperimen di atas, kita boleh membuat kesimpulan bahawa

  dan COUNT(*) adalah yang terpantas, Seterusnya ialah COUNT(1). COUNT(id)

• 2.

  telah ditulis semula ke dalam count(*) oleh pengoptimum pertanyaan MySQL dan indeks idx_salary telah dipilih. count(0)

• 3. Kedua-dua

  dan count(1) pilih indeks idx_gaji. count(id)

Kesimpulan eksperimen

Ringkasan:

COUNT(*)=COUNT(1)>COUNT(id)

Dokumen rasmi MySQL juga berkata:

InnoDB mengendalikan operasi SELECT COUNT(*) dan SELECT COUNT(1) dengan cara yang sama. Tiada perbezaan prestasi

Terjemahan: InnoDB SELECT. Operasi COUNT(*) dan SELECT COUNT(1) dikendalikan dengan cara yang sama. Tiada perbezaan prestasi

, jadi ia menunjukkan bahawa untuk

atau COUNT(1), pengoptimuman MySQL sebenarnya betul-betul sama, dan tiada perbezaan prestasi. COUNT(*)

Tetapi adalah disyorkan untuk menggunakan

kerana ini adalah sintaks standard untuk mengira baris yang ditakrifkan oleh MySQL92. COUNT(*)

2.COUNT(*) dan TABLES_ROWS

Dalam InnoDB, ruang yang diduduki oleh setiap jadual pangkalan data MySQL dan bilangan baris yang direkodkan dalam jadual boleh digunakan untuk membuka MySQL

pangkalan data. Terdapat information_schema jadual dalam pustaka Medan utama TABLES ialah:

• TABLE_SKEMA: Nama pangkalan data

• NAMA_JADUAL: Nama jadual

• ENGIN: Enjin storan digunakan

• TABLES_ROWS: Bilangan rekod

• DATA_LENGTH: Saiz data

• INDEX_LENGTH: Saiz indeks

  >

TABLE_ROWS digunakan untuk memaparkan bilangan baris pada jadual ini pada masa ini Perintah ini dilaksanakan dengan cepat. Bolehkah TABLE_ROWS ini menggantikan

? count(*)

Kami menggunakan TABLES_ROWS untuk menanyakan bilangan rekod jadual:

(Sat Jul 23 23:15:14 2022)[root@GreatSQL][test]>SELECT TABLE_ROWS
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = 'test_count';
+------------+
| TABLE_ROWS |
+------------+
|    9980612 |
+------------+
1 row in set (0.03 sec)

Anda boleh melihat bahawa bilangan rekod tidak tepat kerana kiraan baris TABLES_ROWS di bawah enjin InnoDB hanyalah anggaran Anggaran.

3. Bagaimanakah COUNT(*) dilaksanakan

Perkara pertama yang perlu dijelaskan ialah MySQL mempunyai banyak enjin yang berbeza,

mempunyai kaedah pelaksanaan yang berbeza, artikel ini terutamanya memperkenalkan proses pelaksanaan pada enjin InnoDB count(*)

Dalam enjin storan InnoDB, fungsi

mula-mula membaca data dalam jadual daripada memori ke penimbal memori, dan kemudian mengimbas keseluruhan jadual Dapatkan nombor daripada rekod baris. Ringkasnya, ia adalah imbasan jadual penuh A gelung menyelesaikan masalah dalam gelung: Mula-mula baca satu baris, dan kemudian tentukan sama ada baris itu disertakan dalam kiraan count(*) Gelung mengira baris demi baris. count

Dalam enjin MyISAM, jumlah bilangan baris jadual disimpan pada cakera, jadi apabila

dilaksanakan, nombor ini akan dikembalikan terus, yang sangat cekap. count(*)

Sebab mengapa InnoDB tidak menyimpan nombor seperti MyISAM adalah kerana walaupun terdapat berbilang pertanyaan pada masa yang sama, disebabkan oleh kawalan pertukaran mata wang berbilang versi (MVCC), bilangan baris yang perlu dikembalikan oleh jadual InnoDB. tidak penting. InnoDB berprestasi lebih baik daripada MyISAM dari segi sokongan transaksi, konkurensi atau keselamatan data.

Walaupun begitu, InnoDB telah mengoptimumkan operasi count(*). InnoDB ialah jadual tersusun indeks Nod daun pokok indeks kunci utama ialah data, manakala nod daun pokok indeks biasa ialah nilai kunci utama. Oleh itu, pokok indeks biasa jauh lebih kecil daripada pokok indeks kunci primer. Untuk operasi seperti count(*), hasil yang diperoleh dengan melintasi mana-mana pokok indeks secara logiknya adalah sama. Oleh itu, pengoptimum MySQL akan mencari pokok terkecil untuk dilalui.

Perlu diambil perhatian bahawa apa yang kita bincangkan dalam artikel ini adalah tanpa syarat penapiscount(*) Jika WHERE syarat ditambah, jadual enjin MyISAM tidak boleh dikembalikan begitu cepat.

4. Ringkasan

• 1.COUNT(*)=COUNT(1)>COUNT(id)

• 2 jadual Bilangan baris. Penggunaan utama ialah COUNT(*)、COUNT(字段)和COUNT(1)

• 3 Oleh kerana COUNT(*) ialah sintaks standard untuk mengira baris yang ditakrifkan oleh SQL92, MySQL telah membuat banyak pengoptimuman untuknya jadual ke dalam Jumlah bilangan baris direkodkan secara berasingan untuk pertanyaan COUNT(*), manakala InnoDB akan memilih indeks terkecil apabila mengimbas jadual untuk mengurangkan kos. Premis pengoptimuman ini ialah tiada pertanyaan bersyarat WHERE dan GROUP.

• 4 Dalam InnoDB, tiada perbezaan dalam pelaksanaan antara COUNT(*) dan COUNT(1), dan kecekapan adalah sama, tetapi COUNT(字段) memerlukan pertimbangan bukan NULL medan , jadi kecekapan akan menjadi lebih rendah.

• 5 Oleh kerana COUNT(*) ialah sintaks standard untuk mengira baris yang ditakrifkan oleh SQL92 dan sangat cekap, adalah disyorkan untuk menggunakan COUNT(*) untuk menanyakan bilangan baris dalam jadual. .

• 6 Sama seperti kes penggunaan COUNT(name) sebelum ini, semasa proses penciptaan jadual, adalah perlu untuk mewujudkan indeks berprestasi tinggi mengikut keperluan perniagaan, dan pada masa yang sama. , perhatian harus diberikan untuk mengelak daripada mencipta indeks yang tidak perlu.

Atas ialah kandungan terperinci Apakah prinsip prestasi MySQL COUNT(*). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!