Cara menggunakan perbezaan dan kumpulan mengikut dalam MySQL

Mari kita bincangkan tentang kesimpulan umum dahulu:

• Dalam kes semantik dan pengindeksan yang sama: group by dan distinct Kedua-duanya boleh menggunakan indeks dengan kecekapan yang sama.

• Dengan semantik yang sama dan tiada indeks: distinct lebih cekap daripada group by. Sebabnya ialah kedua-dua yang berbeza dan group by akan melaksanakan operasi pengelompokan, tetapi group by boleh melakukan pengisihan dan mencetuskan pengisihan fail, mengakibatkan pelaksanaan SQL yang tidak cekap.

Berdasarkan kesimpulan ini, anda mungkin bertanya:

• Mengapakah group by dan distinct kecekapan yang sama?

• Dalam keadaan apakah group by akan melakukan operasi isihan?

Cari jawapan dengan dua soalan ini. Seterusnya, mari kita lihat kegunaan asas distinct dan group by.

Penggunaan

Penggunaan berbeza

SELECT DISTINCT columns FROM table_name WHERE where_conditions;

Contohnya:

mysql> select distinct age from student;
+------+
| age  |
+------+
|   10 |
|   12 |
|   11 |
| NULL |
+------+
4 rows in set (0.01 sec)

DISTINCT Kata kunci digunakan untuk mengembalikan nilai unik berbeza. Ia digunakan sebelum medan pertama dalam pernyataan pertanyaan dan digunakan pada semua lajur dalam klausa utama.

Jika lajur mempunyai nilai NULL ​​dan klausa DISTINCT digunakan pada lajur, MySQL akan mengekalkan satu nilai NULL dan memadamkan nilai NULL yang lain kerana klausa DISTINCT merawat semua nilai NULL sebagai nilai yang sama.

penyahduplikasi berbilang lajur yang berbeza

distinct Penyahduplikasian berbilang lajur dilakukan berdasarkan maklumat lajur penyahduplikasi yang ditentukan, iaitu, hanya apabila semua maklumat lajur yang ditentukan adalah sama, tin akan dianggap maklumat pendua.

SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions;
mysql> select distinct sex,age from student;
+--------+------+
| sex    | age  |
+--------+------+
| male   |   10 |
| female |   12 |
| male   |   11 |
| male   | NULL |
| female |   11 |
+--------+------+
5 rows in set (0.02 sec)

Penggunaan kumpulan oleh

Untuk penduaan asas, penggunaan group by adalah serupa dengan distinct.

Penyahduplikasi lajur tunggal

Sintaks:

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

Pelaksanaan:

mysql> select age from student group by age;
+------+
| age  |
+------+
|   10 |
|   12 |
|   11 |
| NULL |
+------+
4 rows in set (0.02 sec)

Penyahduplikasian berbilang lajur

Sintaks:

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

Pelaksanaan:

mysql> select sex,age from student group by sex,age;
+--------+------+
| sex    | age  |
+--------+------+
| male   |   10 |
| female |   12 |
| male   |   11 |
| male   | NULL |
| female |   11 |
+--------+------+
5 rows in set (0.03 sec)

Contoh perbezaan

Perbezaan sintaks antara keduanya ialah group by boleh melakukan penyahduplikasian lajur tunggal dan prinsip group by ialah mengumpulkan dan mengisih hasil dahulu, dan kemudian kembalikan Sekeping data pertama dalam setiap kumpulan. Dan deduplikasi dilakukan berdasarkan medan berikut group by.

Contohnya:

mysql> select sex,age from student group by sex;
+--------+-----+
| sex    | age |
+--------+-----+
| male   |  10 |
| female |  12 |
+--------+-----+
2 rows in set (0.03 sec)

berbeza dan kumpulan mengikut prinsip

Dalam kebanyakan contoh, DISTINCT boleh dianggap sebagai GROUP BY istimewa, dan pelaksanaannya ialah It is berdasarkan operasi kumpulan dan boleh dilaksanakan melalui imbasan indeks longgar dan imbasan indeks padat (kandungan imbasan indeks akan diperkenalkan secara terperinci dalam artikel lain, jadi saya tidak akan memperkenalkannya secara terperinci di sini). Kedua-dua

DISTINCT dan GROUP BY boleh diimbas dan dicari menggunakan indeks. Sebagai contoh, dua sql berikut (lihat sahaja kandungan tambahan terakhir dalam jadual), kita menganalisis kedua-dua sql ini, kita dapat melihat bahawa dalam tambahan, kedua-dua sql ini menggunakan imbasan indeks padat Using index for group-by.

Jadi, secara umum, untuk pernyataan DISTINCT dan GROUP BY dengan semantik yang sama, kita boleh menggunakan kaedah pengoptimuman indeks yang sama untuk mengoptimumkannya.

mysql> explain select int1_index from test_distinct_groupby group by int1_index;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)
mysql> explain select distinct int1_index from test_distinct_groupby;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | range | index_1       | index_1 | 5       | NULL |  955 |   100.00 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)

Tetapi untuk GROUP BY, sebelum MYSQL8.0, GROUP Y akan diisih secara tersirat mengikut medan secara lalai.

Seperti yang anda lihat, penyataan sql berikut menggunakan jadual sementara dan juga melakukan penyusunan fail.

mysql> explain select int6_bigger_random from test_distinct_groupby GROUP BY int6_bigger_random;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | test_distinct_groupby | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97402 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set (0.04 sec)

Isih tersirat

Untuk pengisihan tersirat, kita boleh merujuk kepada penjelasan rasmi MySQL:

https://dev.mysql.com/doc/refman/5.7 /ms /order-by-optimization.html

GROUP BY tersirat mengisih secara lalai (iaitu, jika tiada penunjuk ASC atau DESC untuk lajur GROUP BY, bagaimanapun, bergantung pada pengisihan GROUP BY tersirat (). iaitu, pengisihan jika tiada penunjuk ASC atau DESC) atau pengisihan eksplisit untuk GROUP BY (iaitu, dengan menggunakan penunjuk ASC atau DESC yang jelas untuk lajur GROUP BY) ditamatkan Untuk menghasilkan susunan isihan yang diberikan, sediakan klausa ORDER BY .

Penjelasan luas:

GROUP BY lalai kepada pengisihan tersirat (bermaksud ia juga akan diisih walaupun lajur GROUP BY tidak mempunyai penunjuk ASC atau DESC). Walau bagaimanapun, GROUP BY untuk pengisihan eksplisit atau tersirat tidak digunakan untuk menjana susunan isihan yang diberikan, sediakan klausa ORDER BY.

Jadi, sebelum MySQL8.0, GROUP BY akan mengisih keputusan secara lalai mengikut medan kesan (medan yang mengikuti GROUP BY). Apabila indeks boleh digunakan, GROUP BY tidak memerlukan operasi pengisihan tambahan; tetapi apabila indeks tidak boleh digunakan untuk pengisihan, pengoptimum MySQL perlu memilih untuk melaksanakannya GROUP BY dengan menggunakan jadual sementara dan kemudian mengisih.

Dan apabila saiz set hasil melebihi saiz jadual sementara yang ditetapkan oleh sistem, MySQL akan menyalin data jadual sementara ke cakera sebelum beroperasi, dan kecekapan pelaksanaan pernyataan akan menjadi sangat rendah. Inilah sebab mengapa MySQL telah memilih untuk menghentikan operasi ini (isihan tersirat).

Berdasarkan sebab di atas, Mysql telah mengoptimumkan dan mengemas kini ini dalam 8.0:

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

Sebelum ini (MySQL 5.7 dan lebih rendah), GROUP BY diisih secara tersirat di bawah syarat tertentu. Dalam MySQL 8.0, itu tidak lagi berlaku, jadi menyatakan ORDER BY NULL pada penghujung untuk menyekat pengisihan tersirat (seperti yang dilakukan sebelum ini) tidak lagi diperlukan, namun, hasil pertanyaan mungkin berbeza daripada versi MySQL sebelumnya susunan urutan, sediakan klausa ORDER BY.

Penjelasan kasar:

Pada masa lalu (sebelum versi MySQL5.7), Group by akan melakukan pengisihan tersirat berdasarkan syarat tertentu. Dalam MySQL 8.0, ciri ini telah dialih keluar, jadi tidak perlu lagi menambah order by null untuk melumpuhkan pengisihan tersirat, namun, hasil pertanyaan mungkin berbeza daripada versi MySQL sebelumnya. Untuk menghasilkan hasil dalam susunan tertentu, nyatakan medan yang perlu diisih mengikut ORDER BY.

Oleh itu, kesimpulan kami juga dibuat:

• Dalam kes semantik dan pengindeksan yang sama: group by dan distinct boleh kedua-duanya menggunakan indeks, iaitu cekap sama. Memandangkan group by dan distinct hampir sama, berbeza boleh dianggap sebagai group by istimewa.

• Dengan semantik yang sama dan tiada indeks: distinct lebih cekap daripada group by. Sebabnya ialah kedua-dua distinct dan group by akan melaksanakan operasi pengelompokan, tetapi group by akan melakukan pengisihan tersirat sebelum MySQL8.0, menyebabkan penyusunan fail dicetuskan dan pelaksanaan SQL menjadi tidak cekap. Tetapi bermula dari MySQL 8.0, MySQL telah memadamkan pengisihan tersirat Oleh itu, di bawah semantik yang sama dan tiada indeks, kecekapan pelaksanaan group by dan distinct adalah hampir sama.

Berbanding dengan distinct, group by mempunyai semantik yang jelas. Dan memandangkan kata kunci yang berbeza akan berkuat kuasa pada semua medan, group by lebih fleksibel apabila melaksanakan pemprosesan perniagaan komposit group by boleh melakukan pemprosesan data yang lebih kompleks mengikut situasi pengumpulan, seperti melalui having Tapis data atau. beroperasi pada data melalui fungsi agregat.

Atas ialah kandungan terperinci Cara menggunakan perbezaan dan kumpulan mengikut dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!