Mengapa count(*) sangat perlahan? Analisis punca-tutorial mysql-php.cn

Mengapa count(*) sangat lambat? Artikel berikut akan menganalisis sebab untuk anda dan bercakap tentang proses pelaksanaan kiraan(*).

Mengapa count(*) sangat perlahan? Analisis punca

Saya tidak mahu menulis artikel ini, kerana saya rasa kebanyakan pembangun yang berpengalaman telah menemui perkara ini dan mesti memahami sebab yang berkaitan, tetapi baru-baru ini saya melihat bahawa beberapa teknikal akaun awam yang mengikuti saya sedang menolak artikel berkaitan. Ia benar-benar mengejutkan saya!

Mula-mula pergi ke kesimpulan artikel akaun awam:

count(*): Ia akan mendapat data semua baris tanpa sebarang pemprosesan, dan bilangan baris akan ditambah 1.
count(1): Ia akan mendapat data semua baris, dengan nilai tetap 1 untuk setiap baris, yang juga merupakan bilangan baris ditambah 1.
count(id): id mewakili kunci utama Ia perlu menghuraikan medan id daripada semua baris data Id mestilah tidak NULL, dan bilangan baris ditambah 1.
kira (lajur indeks biasa): Ia perlu menghuraikan lajur indeks biasa daripada data semua baris, dan kemudian menentukan sama ada ia adalah NULL Jika ia bukan NULL, bilangan baris + 1.
kira (lajur tidak terindeks): Ia mengimbas keseluruhan jadual untuk mendapatkan semua data, tidak menambah lajur diindeks dalam analisis, dan kemudian menentukan sama ada ia adalah NULL Jika ia bukan NULL, bilangan baris + 1.

Kesimpulan: count(*) ≈ count(1) > count(id) > count (lajur indeks biasa) > Tidak mahu menjualnya, kesimpulan di atas

adalah kentut murni

. Ia hanya sesuatu yang dicipta oleh seseorang, dan saya tidak mahu mengesahkannya Walaupun saya melihat rancangan pelaksanaannya, saya tidak boleh membuat kesimpulan yang keterlaluan. Saya tidak percaya ini adalah artikel yang telah disiarkan semula oleh berbilang akaun awam teknikal!

Semua kandungan berikut adalah berdasarkan analisis

mysql 5.7 + InnoDB引擎Sambungan:

MyISAM Jika tiada syarat pertanyaan, tetapi hanya mengira jumlah bilangan data dalam jadual, pulangan akan menjadi sangat pantas, kerana jumlah bilangan baris dalam jadual maklumat yang diperolehi oleh lapisan perkhidmatan adalah tepat dan InnoDB hanyalah anggaran.

Contoh

Tanpa berlengah lagi, mari kita lihat contoh dahulu.

Berikut ialah jadual dengan volum data 1 juta Medan dalam jadual agak pendek, dan volum data keseluruhan tidak besar.

Jadual ini hanya mempunyai satu keadaan awal

CREATE TABLE `hospital_statistics_data` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT &#39;主键&#39;,
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT &#39;外键&#39;,
  `hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT &#39;医院编码&#39;,
  `biz_type` tinyint NOT NULL COMMENT &#39;1服务流程  2管理效果&#39;,
  `item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT &#39;考核项目编码&#39;,
  `item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT &#39;考核项目名称&#39;,
  `item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT &#39;考核结果&#39;,
  `is_deleted` tinyint DEFAULT NULL COMMENT &#39;是否删除 0否 1是&#39;,
  `gmt_created` datetime DEFAULT NULL COMMENT &#39;创建时间&#39;,
  `gmt_modified` datetime DEFAULT NULL COMMENT &#39;gmt_modified&#39;,
  `gmt_deleted` datetime(3) DEFAULT &#39;9999-12-31 23:59:59.000&#39; COMMENT &#39;删除时间&#39;,
  PRIMARY KEY (`pk_id`)
) DEFAULT CHARSET=utf8mb4  COMMENT=&#39;医院统计数据&#39;;

Salin selepas log masuk

聚簇索引Berikut ialah melihat pelan pelaksanaan COUNT(*) dalam situasi indeks yang berbeza.

1) Lihat pelan pelaksanaan apabila hanya terdapat satu indeks berkelompok.

Hasil:

EXPLAIN select COUNT(*) from hospital_statistics_data;

Salin selepas log masuk

Maksud setiap parameter pelan pelaksanaan tidak berada dalam skop artikel ini, anda boleh faham itu sendiri.

Kami hanya menumpukan pada atribut berikut di sini.

, dalam kes semasa, indeks berkelompok digunakan.

count(*)也会走索引Baiklah, lihat ke bawah.

2) Terdapat indeks tidak berkelompok (indeks kedua)

Tambahkan indeks kod_hospital pada jadual.

Terdapat dua indeks dalam jadual pada masa ini,

alter table hospital_statistics_data add index idx_hospital_code(hospital_code)

Salin selepas log masuk

dan

. 主键 hospital_codeBegitu juga, laksanakan semula:

Keputusan:

EXPLAIN select COUNT(*) from hospital_statistics_data;

Salin selepas log masuk

Begitu juga, lihat medan jenis, kunci dan key_len.

Adakah anda berasa sedikit "

ajaib

"? Mengapa indeks menjadi

yang baru ditambah?

idx_hospital_codeJangan tergesa-gesa membuat kesimpulan, kemudian lihat situasi berikut.

3) Terdapat dua indeks bukan berkelompok (indeks sekunder)

Berdasarkan perkara di atas, tambahkan indeks sekunder.

Terdapat tiga indeks dalam jadual pada masa ini, kunci utama, hospital_code dan biz_type.

alter table hospital_statistics_data add index idx_biz_type(biz_type)

Salin selepas log masuk

Begitu juga, laksanakan:

Keputusan:

EXPLAIN select COUNT(*) from hospital_statistics_data;

Salin selepas log masuk

Adakah anda semakin keliru? ..diubah.

menjadi idx_biz_type yang baharu.

Jangan bercakap tentang sebab perubahan di atas berlaku, mari kita teruskan analisis di bawah.

Berdasarkan tiga indeks di atas, mari kita lihat empat kes

, count(1), count(id)count(index)count(无索引) dan kira(*) Apakah ialah perbezaan antara rancangan pelaksanaan.

Mengapa count(*) sangat perlahan? Analisis punca

count(index)

这里选取biz_type索引字段。

count(无索引)

小结：

count(index) 会使用当前index指定的索引。
count(无索引) 是全表扫描，未走索引。
count(1) , count(*), count(id) 一样都会选择idx_biz_type索引

看到这，你还觉得那些千篇一律的公众号文章的结论正确吗？

必要知识点

mysql 分为service层和引擎层。
所有的sql在执行前会经过service层的优化，优化分为很多类型，简单的来说可分为成本和规则。
执行计划所反映的是service层经过sql优化后，可能的执行过程。并非绝对（免得有些人说我只看执行计划过于片面）。绝大多数情况执行计划是可信的。
索引类型分为聚簇索引和非聚簇索引（二级索引）。其中数据都是挂在聚簇索引上的，非聚簇索引上只是记录的主键id。
抛开数据内存，只谈数据量，都是扯淡。什么500w就是极限，什么2个表以上的join都需要优化了，什么is null不会走索引等，纯纯的放屁。
相信一点，编写mysql代码的人比，看此文章的大部分人都要优秀。他们会尽可能在执行前，对我这样菜逼写的乱七八糟的sql进行优化。

原因分析

其实原因非常非常简单，上面也说了，service层会基于成本进行优化。

并且，正常情况下，非聚簇索引所占有的内存要远远小于聚簇索引。所以问题来了，如果你是mysql的开发人员，你在执行count(*)查询的时候会使用那个索引？

我相信正常人都会使用非聚簇索引。

那如果存在2个甚至多个非聚簇索引又该如何选择呢？

那肯定选择最短的，占用内存最小的一个呀，在回头看看上面的实例，还迷惑吗。

同样都是非聚簇索引。idx_hospital_code的len是146字节；而idx_biz_type的len只有1。那还要选吗？

那为何count(*)走了索引，却还是很慢呢？

这里要明确一点，索引只是提升效率的一种方式，但不能完全的解决效率问题。count(*)有一个明显的缺陷，就是它要计算总数，那就意味着要遍历所有符合条件的数据，相当于一个计数器，在数据量足够大的情况下，即使使用非聚簇索引也无法优化太多。

官方文档：

InnoDBhandlesSELECT COUNT(*)andSELECT COUNT(1)operations in the same way. There is no performance difference.

简单的来说就是，InnoDB下 count(*) 等价于 count(1)

既然会自动走索引，那么上面那个所谓的速度排序还觉得对吗？ count(*)的性能跟数据量有很大的关系，此外最好有一个字段长度较短的二级索引。

拓展：

另外，多说一下，关于网上说的那些索引失效的情况，大多都是片面的，我这里只说一点。量变才能引起质变，索引的失效取决于你圈定数据的范围，若你圈定的数据量占整体数据量的比例过高，则会放弃使用索引，反之则会优先使用索引。但是此规则并不是完美的，有时候可能与你预期的不同，也可以通过一些技巧强制使用索引，但这种方式少用。

举个栗子：

通过上面这个表hospital_statistics_data，我进行了如下查询：

select * from hospital_statistics_data where hospital_code is not null;

Salin selepas log masuk

此时这个sql会使用到hospital_code的索引吗？

这里也不卖关子了，若hospital_code只有很少一部分数据是null值，那么将不会走索引，反之则走索引。

原因就2个字：回表。

Ia seperti membeli oren gula Jika anda hanya membeli beberapa kilogram, maka anda boleh memilih yang terbaik dalam bakul. Tetapi jika anda ingin membeli bakul, saya percaya bos tidak akan membenarkan anda memilih satu demi satu, tetapi akan memberikan anda satu bakul sekaligus, sudah tentu, semua orang tidak bodoh, dan mereka semua tahu bahawa mesti ada beberapa buah-buahan yang tidak baik di dalam bakul. Tetapi ini adalah yang paling cekap dan menyebabkan kurang kerugian kepada bos.

Proses pelaksanaan

Dipetik daripada "Memahami MySQL dari Akar". Saya amat mengesyorkan mereka yang belum mempelajari MySQL secara sistematik membaca buku ini.

1 Mula-mula mengekalkan pembolehubah kiraan dalam lapisan pelayan

2 Lapisan pelayan meminta enjin InnoDB untuk rekod pertama

3 rekod dan kembali ke lapisan pelayan (nota: memandangkan ia hanya mengira bilangan rekod pada masa ini, tidak perlu mengembalikan jadual)

4 Memandangkan parameter fungsi COUNT ialah *, MySQL akan menganggap * sebagai pemalar 0 . Oleh kerana 0 bukan NULL, lapisan pelayan menambah 1 pada pembolehubah kiraan.

5. Lapisan pelayan meminta InnoDB untuk rekod seterusnya.

6.InnoDB mencari rekod indeks sekunder seterusnya melalui atribut next_record rekod indeks sekunder dan mengembalikannya ke lapisan pelayan.

7 Lapisan pelayan terus menambah 1 pada pembolehubah kiraan.

8 Ulangi proses di atas sehingga InnoDB tidak mengembalikan mesej boleh rakam ke lapisan pelayan.

9 Lapisan pelayan menghantar nilai akhir pembolehubah kiraan kepada klien.

Ringkasan

Selepas saya selesai menulis, saya masih berasa agak tertekan. Semakin sedikit artikel bagus yang boleh diperolehi daripada akaun awam, dan pengetahuan kini dibayar Sudah tiba masanya.

Saya sangat merindui zaman mula-mula bekerja Masa tu saya habiskan masa baca artikel akaun rasmi setiap pagi, tapi sekarang semua dah jadi iklan. Kenapa!

Tetapi itu perkara biasa, tiada siapa yang sentiasa boleh menjana tenaga untuk cinta.

Untuk belajar, adalah disyorkan untuk membaca lebih banyak buku Secara umumnya, yang boleh ditulis ke dalam buku tidak terlalu buruk. Sekarang apa yang saya boleh cari pada waktu malam adalah artikel yang sama, saya tidak tahu sama ada ia betul atau salah. Dalam talian

[Cadangan berkaitan: tutorial video mysql]

Atas ialah kandungan terperinci Mengapa count(*) sangat perlahan? Analisis punca. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!