Adakah pertanyaan kiraan penuh benar-benar lambat pada jadual MySQL InnoDB yang besar?

Question

Kami mempunyai meja besar dengan berjuta-juta penyertaan. Pengiraan penuh adalah sangat perlahan, lihat kod di bawah. Adakah ini biasa untuk jadual MySQL InnoDB? Tidakkah ada cara untuk mempercepatkan proses ini? Walaupun dengan caching pertanyaan, ia masih "lambat". Saya juga ingin tahu mengapa kiraan jadual "komunikasi" dengan entri 2.8 juta adalah lebih perlahan daripada kiraan jadual "urus niaga" dengan entri 4.5 juta.

Saya tahu ia akan menjadi lebih pantas untuk menggunakan klausa mana. Saya hanya tertanya-tanya sama ada prestasi buruk adalah perkara biasa.

Kami menggunakan Amazon RDS MySQL 5.7 dan m4.xlarge (4 CPU, 16 GB RAM, storan 500 GB). Saya juga telah mencuba contoh yang lebih besar dengan lebih banyak CPU dan RAM, tetapi tiada perubahan besar dalam masa pertanyaan.

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| COUNT(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1 min 37.88 sec)

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| count(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1.44 sec)

mysql> SELECT COUNT(*) FROM communication;
+----------+
| count(*) |
+----------+
|  2821486 |
+----------+
1 row in set (2 min 19.28 sec)

Answer 1

Ini disokong menggunakan Multi-Version Concurrency Control (MVCC).

InnoDB membenarkan pertanyaan anda diasingkan dalam urus niaga tanpa menyekat pelanggan serentak lain yang membaca dan menulis baris data. Kemas kini serentak ini tidak menjejaskan pandangan transaksi anda terhadap data.

Tetapi, berapakah bilangan baris dalam jadual, memandangkan banyak baris sedang ditambah atau dipadamkan semasa mengira? Jawapannya samar-samar.

Transaksi anda seharusnya tidak dapat "melihat" versi baris yang dibuat selepas transaksi dimulakan. Begitu juga, transaksi anda harus mengira baris walaupun orang lain meminta pemadaman baris, tetapi mereka berbuat demikian selepas transaksi anda bermula.

Jawapannya ialah apabila anda melaksanakan SELECT COUNT(*) atau apa-apa jenis pertanyaan lain yang memerlukan menyemak berbilang baris, InnoDB mesti mengakses setiap baris, melihat versi semasa baris itu yang boleh dilihat pada paparan transaksi pangkalan data dan melakukan pertanyaan pada ia jika ia boleh dilihat kiraan.

Dalam jadual yang tidak menyokong transaksi atau kemas kini serentak (seperti MyISAM), enjin storan mengekalkan jumlah bilangan baris sebagai metadata untuk jadual. Enjin storan tidak boleh menyokong berbilang benang mengemas kini baris secara serentak, jadi jumlah baris adalah kurang samar-samar. Jadi apabila anda meminta SELECT COUNT(*) dari jadual MyISAM ia hanya akan mengembalikan bilangan baris dalam memori (tetapi ini tidak berguna jika anda melakukan SELECT COUNT(*)) Gunakan klausa WHERE untuk berpasangan mengikut beberapa kriteria Beberapa subset baris dikira, jadi dalam kes ini ia mesti benar-benar mengiranya).

Secara keseluruhan, kebanyakan orang berpendapat sokongan InnoDB untuk kemas kini serentak amat berbaloi, dan mereka sanggup mengorbankan SELECT COUNT(*) pengoptimuman.

Answer 2

Kecuali apa yang Bill cakap...

Indeks minimum

InnoDB memilih indeks "minimum" untuk melaksanakan COUNT(*)。可能所有communication的索引都大于transaction的最小索引，因此存在时间差。判断索引的大小时，请将 PRIMARY KEY lajur yang disertakan bersama-sama dengan mana-mana indeks sekunder:

PRIMARY KEY(id),   -- INT (4 bytes)
INDEX(flag),       -- TINYINT (1 byte)
INDEX(name),       -- VARCHAR(255) (? bytes)

Untuk mengukur saiz, PRIMARY KEY 很大，因为它包含（由于集群）表的所有列。 INDEX(flag) 是“5 个字节”。 INDEX(name) 平均可能有几十个字节。 SELECT COUNT(*) 将明确选择 INDEX(flag).

Nampaknya交易有一个“小”索引，但通信tidak.

TEXT/BLOG Lajur kadangkala disimpan "tidak dilog". Oleh itu, ia tidak termasuk dalam saiz indeks PK.

Cache Pertanyaan

Jika "Cache Pertanyaan" dihidupkan, larian kedua pertanyaan mungkin jauh lebih pantas daripada kali pertama. Tetapi ini hanya berlaku jika jadual tidak berubah. QC jarang berguna dalam sistem pengeluaran kerana sebarang perubahan pada jadual membatalkan semua entri QC untuk jadual tersebut. Dengan "lebih cepat" saya maksudkan kira-kira 0.001 saat; bukan 1.44 saat.

Perbezaan antara 1m38s dan 1.44s mungkin disebabkan oleh apa yang dicache dalam buffer_pool (kawasan cache umum InnoDB). Larian pertama mungkin tidak menemui sebarang indeks "minimum" dalam RAM, jadi ia melakukan banyak I/O, mengambil masa 98 saat untuk mendapatkan kesemua 4.5 juta baris untuk indeks tersebut. Larian kedua mendapati bahawa semua data telah dicache dalam buffer_pool, jadi ia berjalan pada kelajuan CPU (tiada I/O), jadi ia lebih pantas.

cukup baik

Dalam kes ini, saya secara asasnya mempersoalkan keperluan untuk menguatkuasakan COUNT(*). Perhatikan cara anda menyebut "kemasukan 2.8 juta" seolah-olah 2 angka bererti adalah "cukup baik". Bukankah itu "cukup baik" jika anda menunjukkan kiraan kepada pengguna pada UI? Jika ya, satu penyelesaian untuk prestasi ialah mengiranya sekali sehari dan menyimpannya di suatu tempat. Ini akan membolehkan akses segera kepada nilai "cukup baik".

Ada teknologi lain. Salah satunya ialah menggunakan tag aktiviti atau beberapa bentuk jadual ringkasan untuk memastikan kaunter dikemas kini.

Lempar perkakasan padanya

Anda telah mendapati bahawa menukar perkakasan tidak membantu.

• 98 berjalan sepantas mana-mana produk I/O RDS.
• 1.44s berjalan sepantas mana-mana CPU RDS.
• MySQL (dan variannya) tidak menggunakan berbilang CPU bagi setiap pertanyaan.
• Anda mempunyai RAM yang mencukupi supaya keseluruhan indeks "kecil" boleh dimasukkan ke dalam buffer_pool sehingga kedua anda SELECT COUNT(*).. (RAM yang terlalu sedikit akan menyebabkan larian kedua menjadi sangat perlahan) .)