Penjelasan terperinci tentang count(), union() dan kumpulan mengikut pernyataan dalam MySQL

Artikel ini akan membawa anda melalui kiraan(), kesatuan() dan kumpulan mengikut kenyataan, dan menambah mata pengetahuan MySQL (penggunaan kiraan(), proses pelaksanaan kesatuan, kumpulan demi pernyataan).

1. Penggunaan count() yang berbeza dalam MySQL

count() ialah fungsi agregat, untuk set hasil yang dikembalikan , hakim baris demi baris, jika parameter fungsi kiraan bukan NULL, nilai terkumpul akan ditambah dengan 1, jika tidak, ia tidak akan ditambah. Akhirnya, nilai kumulatif dikembalikan. [Cadangan berkaitan: tutorial video mysql]

1 Untuk kiraan (id kunci utama), enjin InnoDB akan merentasi seluruh jadual, mengeluarkan nilai id setiap baris dan kembali. Ke lapisan pelayan. Selepas lapisan pelayan mendapat id, ia menilai bahawa ia tidak boleh kosong, jadi ia terkumpul baris demi baris

2 Untuk kiraan(1), enjin InnoDB merentasi keseluruhan jadual tetapi tidak mengambil nilai . Lapisan pelayan meletakkan nombor 1 ke dalam setiap baris yang dikembalikan. Ia dinilai bahawa ia tidak boleh kosong dan terkumpul

3 mengikut baris Untuk kiraan (medan), jika medan ini ditakrifkan sebagai bukan null , baca medan ini dari rekod baris demi baris, dan menilai bahawa ia tidak boleh menjadi batal, dan mengumpulkannya baris demi baris jika definisi medan membenarkan batal, maka apabila melaksanakan, ia dinilai bahawa ia mungkin batal, dan nilainya mesti dikeluarkan dan Dinilai. Kini, bukan nol yang terkumpul

4 Untuk count(*), tidak semua medan dialih keluar, tetapi dioptimumkan khas. Tidak mengambil nilai, count(*) pastinya tidak batal, terkumpul mengikut baris

2. Proses pelaksanaan kesatuan

Dalam untuk memudahkan analisis kuantitatif , ambil jadual t1 di bawah sebagai contoh

create table t1(id int primary key, a int, b int, index(a));

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;

END

untuk menganalisis pernyataan SQL berikut:

(select 1000 as f) union (select id from t1 order by id desc limit 2);

Semantik kesatuan ialah mengambil penyatuan hasil daripada dua subkueri ini. Kesatuan bermakna bahawa dua set ditambah bersama, dan hanya satu baris baris pendua dikekalkan

• Kunci=PRIMER dalam baris kedua menunjukkan bahawa. penggunaan klausa kedua Apabila ia datang kepada medan Tambahan dalam baris ketiga id indeks
• , ini bermakna apabila melaksanakan penyatuan pada set hasil subkueri, jadual sementara

digunakan. Aliran pelaksanaan pernyataan ini adalah seperti berikut:

1 Cipta jadual sementara memori ini hanya mempunyai satu medan integer f, dan f ialah medan kunci utama

<🎜. >2. Jalankan subquery pertama dan dapatkan nilai 1000

3. Laksanakan subquery kedua:

Dapatkan id baris pertama=1000 dan cuba masukkan ke dalam jadual sementara . Tetapi memandangkan nilai 1000 sudah wujud dalam jadual sementara, yang melanggar kekangan keunikan, sisipan gagal, dan kemudian terus melaksanakan
untuk mendapatkan baris kedua id=999, dan sisipan ke dalam jadual sementara ialah berjaya

4 Dapatkan baris data demi baris dari jadual sementara, kembalikan hasilnya dan padamkan jadual sementara Hasilnya mengandungi dua baris data, masing-masing 1000 dan 999

<🎜. >

di sini Jadual sementara memori memainkan peranan untuk menyimpan data sementara, dan proses pengiraan juga menggunakan kekangan keunikan ID kunci utama jadual sementara, merealisasikan semantik kesatuan

Jika kesatuan dalam pernyataan di atas ditukar kepada kesatuan semua , tiada semantik untuk penduaan. Apabila dilaksanakan dengan cara ini, subkueri dilaksanakan mengikut urutan, dan hasil yang diperoleh dihantar terus kepada klien sebagai sebahagian daripada set hasil. Oleh itu, tidak ada keperluan untuk jadual sementara

Medan Tambahan dalam baris kedua menunjukkan Menggunakan indeks, yang bermaksud hanya indeks penutup digunakan, dan jadual sementara tidak digunakan

3 Penjelasan terperinci kumpulan mengikut pernyataan

1 Masih menggunakan jadual t1 di atas, analisis pernyataan SQL berikut:

Logik pernyataan ini adalah untuk mengumpulkan data dalam jadual t1 mengikut id, dan kemudian mengeluarkannya selepas mengisih hasil m. Keputusan terangkan adalah seperti berikut:

select id%10 as m, count(*) as c from t1 group by m;

Dalam medan Tambahan, anda boleh melihat tiga maklumat:

Menggunakan indeks, menunjukkan bahawa pernyataan ini menggunakan meliputi indeks, Indeks a yang dipilih tidak perlu dikembalikan ke jadual
Menggunakan sementara, yang menunjukkan bahawa jadual sementara digunakan

Menggunakan filesort, yang menunjukkan bahawa pengisihan diperlukan
• Aliran pelaksanaan pernyataan ini adalah seperti berikut:
• 1 Cipta jadual memori sementara terdapat dua medan m dan c dalam jadual 🎜>2. Imbas indeks a jadual t1, keluarkan nilai id pada nod daun secara bergilir, dan kira Hasil id direkodkan sebagai baris, tambah 1 pada nilai c baris >

内存临时表排序流程图：

如果并不需要对结果进行排序，在SQL语句末尾增加order by null：

select id%10 as m, count(*) as c from t1 group by m order by null;

由于表t1中的id值是从1开始的，因此返回的结果集中第一行是id=1

这个例子里由于临时表只有10行，内存可以放得下，因此全程只使用了内存临时表。但是，内存临时表的大小是有限的，参数tmp_table_size就是控制整个内存大小的，默认是16M

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

把内存临时表的大小限制为最大1024字节，并把语句改成id%100，这样返回结果里有100行数据。但是，这时的内存临时表大小不够存下这100行数据，也就是说，执行过程中会发现内存临时表大小达到了上限。那么，这时候会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB

2、group by优化方法——索引

group by的语义逻辑，是统计不同的值的个数。但是，由于每一行的id%100的结果是无序的，所以就需要有一个临时表来记录并统计结果。那么，如果扫描过程中可以保证出现的数据是有序的就可以了

假设，现在有一个类似下图的这么一个数据结构

如果可以确保输入的数据是有序的，那么计算group by的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个流程：

• 当碰到第一个1的时候，已经知道累积了X个0，结果集里的第一行就是(0,X)
• 当碰到第一个2的时候，已经知道累积了Y个1，结果集里的第一行就是(1,Y)

按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到group by的结果，不需要临时表，也需要再额外排序

在MySQL5.7版本支持了generated column机制，用来实现列数据的关联更新。创建一个列z，在z列上创建一个索引

alter table t1 add column z int generated always as(id % 100), add index(z);

这样，索引z上的数据就是有序的了。group by语句就可以改成：

select z, count(*) as c from t1 group by z;

从这个Extra字段可以看到，这个语句的执行不再需要临时表，也不需要排序了

3、group by优化方法——直接排序

在group by语句中加入SQL_BIG_RESULT这个提示，就可以告诉优化器：这个语句涉及的数据量很大，直接用磁盘临时表。因为磁盘临时表是B+树存储，存储效率不如数组来得高。所以MySQL优化器直接用数组来存

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

1.初始化sort_buffer，确定放入一个整型字段，记为m

2.扫描表t1的索引a，依次取出里面的id值，将id%100的值存入sort_buffer中

3.扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序）

4.排序完成后，就得到了一个有序数组

根据有序数组，得到数组里面的不同值，以及每个值的出现次数

这个语句的执行没有再使用临时表，而是直接用了排序算法

更多编程相关知识，请访问：编程入门！！

Atas ialah kandungan terperinci Penjelasan terperinci tentang count(), union() dan kumpulan mengikut pernyataan dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!