Pendekatan "everything is a nested-loop join" MySQL untuk eksekusi kueri tidak ideal untuk mengoptimalkan setiap jenis kueri. Untungnya, hanya ada beberapa kasus di mana pengoptimal kueri MySQL melakukan pekerjaan yang buruk, dan biasanya mungkin untuk menulis ulang kueri semacam itu dengan lebih efisien.
MySQL terkadang mengoptimalkan subkueri dengan sangat buruk. Pelanggar terburuk adalah
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_9 subkueri dalam klausa
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
0. Sebagai contoh, mari temukan semua film dalam tabel
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
1 database sampel Sakila yang pemerannya termasuk aktris Penelope Guinness (
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
2). Ini terasa alami untuk menulis dengan subquery, sebagai berikut
mysql> SELECT * FROM sakila.film
-> WHERE film_id IN(
-> SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);
_
Sangat menggoda untuk berpikir bahwa MySQL akan mengeksekusi kueri ini dari dalam ke luar, dengan menemukan daftar nilai
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
3 dan menggantinya ke dalam daftar
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
9. Kami mengatakan daftar
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_9 umumnya sangat cepat, jadi Anda mungkin mengharapkan kueri dioptimalkan untuk sesuatu seperti ini
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
Sayangnya, justru sebaliknya yang terjadi. MySQL mencoba untuk "membantu" subquery dengan mendorong korelasi ke dalamnya dari tabel luar, yang menurutnya akan membuat subquery menemukan baris dengan lebih efisien. Itu menulis ulang kueri sebagai berikut
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
_
Sekarang subquery membutuhkan
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
_6 dari tabel
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
7 luar dan tidak dapat dijalankan terlebih dahulu.
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
_8 menunjukkan hasilnya sebagai
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
9 (Anda dapat menggunakan
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
0 untuk melihat persis bagaimana kueri ditulis ulang)
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
Menurut output
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
_8, MySQL akan memindai tabel
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
7 tabel dan mengeksekusi subquery untuk setiap baris yang ditemukannya. Ini tidak akan menyebabkan kinerja yang mencolok di meja kecil, tetapi jika meja luar sangat besar, kinerjanya akan sangat buruk. Untungnya, mudah untuk menulis ulang kueri seperti
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
3
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
Pengoptimalan lain yang baik adalah membuat daftar
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
9 secara manual dengan menjalankan subquery sebagai kueri terpisah dengan
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
5. Terkadang ini bisa lebih cepat daripada
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
3
MySQL telah dikritik secara menyeluruh untuk jenis rencana eksekusi subquery khusus ini. Meski sudah pasti perlu diperbaiki, kritik tersebut seringkali membingungkan dua masalah yang berbeda. perintah eksekusi dan caching. Menjalankan kueri dari dalam ke luar adalah salah satu cara untuk mengoptimalkannya; . Menulis ulang kueri sendiri memungkinkan Anda mengontrol kedua aspek tersebut. Versi MySQL yang akan datang harus dapat mengoptimalkan jenis kueri ini dengan lebih baik, meskipun ini bukanlah tugas yang mudah. Ada kasus terburuk yang sangat buruk untuk rencana eksekusi apa pun, termasuk rencana eksekusi luar-dalam yang menurut beberapa orang akan mudah dioptimalkan
Ketika subquery berkorelasi baik
MySQL tidak selalu mengoptimalkan subkueri berkorelasi dengan buruk. Jika Anda mendengar nasihat untuk selalu menghindarinya, jangan dengarkan. Sebaliknya, tolok ukur dan buat keputusan Anda sendiri. Terkadang subkueri yang dikorelasikan adalah cara yang masuk akal, atau bahkan optimal, untuk mendapatkan hasil. Mari kita lihat sebuah contoh
mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
Saran standar untuk kueri ini adalah menuliskannya sebagai
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
7 alih-alih menggunakan subkueri. Secara teori, rencana eksekusi MySQL pada dasarnya akan sama. Mari kita lihat
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
Rencananya hampir identik, tetapi ada beberapa perbedaan
Jenis
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
_8 terhadap mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
9 adalah SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
9 dalam satu kueri dan mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
1 dalam kueri lainnya. Perbedaan ini hanya mencerminkan sintaks, karena kueri pertama menggunakan subkueri dan yang kedua tidak. Itu tidak membuat banyak perbedaan dalam hal operasi penanganPermintaan kedua tidak mengatakan "Menggunakan di mana" di kolom
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
2 untuk tabel SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
7. Tapi itu tidak masalah. klausa mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
_4 kueri kedua sama saja dengan klausa SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
0Permintaan kedua mengatakan "Tidak ada" di kolom
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
9 tabel mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
2. Ini adalah contoh dari algoritma terminasi dini yang kami sebutkan sebelumnya di bab ini. Itu berarti MySQL menggunakan pengoptimalan yang tidak ada untuk menghindari membaca lebih dari satu baris dalam indeks mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
9 tabel mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
9. Ini setara dengan mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
0 mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
1 subquery berkorelasi, karena berhenti memproses baris saat ini segera setelah menemukan kecocokan
Jadi, secara teori, MySQL akan mengeksekusi kueri hampir sama. Pada kenyataannya, pembandingan adalah satu-satunya cara untuk mengetahui pendekatan mana yang benar-benar lebih cepat. Kami membandingkan kedua kueri pada penyiapan standar kami. Hasilnya ditampilkan di
Tabel 4-1. NOT EXISTS versus LEFT OUTER JOIN
Pertanyaan
Menghasilkan kueri per detik (QPS)
mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
_2 subquery
360 QPS
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
_7
425 QPS
Tolok ukur kami menemukan bahwa subquery sedikit lebih lambat
Namun, ini tidak selalu terjadi. Terkadang subquery bisa lebih cepat. Misalnya, ini bisa bekerja dengan baik saat Anda hanya ingin melihat baris dari satu tabel yang cocok dengan baris di tabel lain. Meskipun kedengarannya seperti menggambarkan gabungan dengan sempurna, itu tidak selalu sama. Penggabungan berikut, yang dirancang untuk menemukan setiap film yang memiliki aktor, akan mengembalikan duplikat karena beberapa film memiliki banyak aktor
mysql> SELECT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
Kita perlu menggunakan
mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
_4 atau
mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
5 untuk menghilangkan duplikat
mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
Tapi apa yang sebenarnya kami coba ungkapkan dengan kueri ini, dan apakah itu jelas dari SQL? . Inilah kueri yang ditulis sebagai subkueri, bukan gabungan
mysql> SELECT film_id FROM sakila.film
-> WHERE EXISTS(SELECT * FROM sakila.film_actor
-> WHERE film.film_id = film_actor.film_id);
Sekali lagi, kami membandingkan untuk melihat strategi mana yang lebih cepat. Hasilnya ditampilkan di
Tabel 4-2. ADA vs GABUNG DALAM
Pertanyaan
Menghasilkan kueri per detik (QPS)
mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
_9
185 KPS
mysql> EXPLAIN SELECT film_id, language_id FROM sakila.film
-> WHERE NOT EXISTS(
-> SELECT * FROM sakila.film_actor
-> WHERE film_actor.film_id = film.film_id
-> )\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index
_6 subquery
325 QPS
Dalam contoh ini, subquery bekerja lebih cepat daripada join
Kami menunjukkan contoh panjang ini untuk mengilustrasikan dua poin. Anda tidak boleh mengindahkan saran kategoris tentang subkueri, dan Anda harus menggunakan tolok ukur untuk membuktikan asumsi Anda tentang rencana kueri dan kecepatan eksekusi
MySQL terkadang tidak dapat "menekan" kondisi dari luar
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
1 ke dalam, di mana kondisi tersebut dapat digunakan untuk membatasi hasil atau mengaktifkan pengoptimalan tambahan
Jika menurut Anda salah satu kueri individual di dalam
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
1 akan mendapat manfaat dari
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
3, atau jika Anda tahu bahwa kueri tersebut akan tunduk pada klausa
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
4 setelah digabungkan dengan kueri lain, Anda perlu memasukkan klausa tersebut di dalam setiap bagian
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
1. Misalnya, jika Anda
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
_1 bersama dua tabel besar dan
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
3 hasil ke 20 baris pertama, MySQL akan menyimpan kedua tabel besar ke tabel sementara dan kemudian mengambil hanya 20 baris darinya. Anda dapat menghindari ini dengan menempatkan
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
_8 pada setiap kueri di dalam
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
1
Pengoptimalan penggabungan indeks
Algoritma penggabungan indeks, diperkenalkan di MySQL 5. 0, biarkan MySQL menggunakan lebih dari satu indeks per tabel dalam kueri. Versi MySQL sebelumnya hanya dapat menggunakan indeks tunggal, jadi ketika tidak ada indeks tunggal yang cukup baik untuk membantu semua batasan dalam klausa
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
0, MySQL sering memilih pemindaian tabel. Misalnya, tabel
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
_9 memiliki indeks pada
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
6 dan indeks pada
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
3, tetapi keduanya bukanlah pilihan yang baik untuk kedua kondisi
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
0 dalam kueri ini
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
0
Di versi MySQL yang lebih lama, kueri tersebut akan menghasilkan pemindaian tabel kecuali jika Anda menulisnya sebagai
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
1 dari dua kueri
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_1
Di MySQL5. 0 dan yang lebih baru, bagaimanapun, kueri dapat menggunakan kedua indeks, memindainya secara bersamaan dan menggabungkan hasilnya. Ada tiga variasi pada algoritma. penyatuan untuk
mysql> SELECT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
6 kondisi, persimpangan untuk
mysql> SELECT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
7 kondisi, dan penyatuan persimpangan untuk kombinasi keduanya. Kueri berikut menggunakan gabungan dari dua pemindaian indeks, seperti yang Anda lihat dengan memeriksa kolom
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
2
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_2
MySQL dapat menggunakan teknik ini pada klausa
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
_0 yang kompleks, sehingga Anda dapat melihat operasi bersarang di kolom
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
2 untuk beberapa kueri. Ini sering bekerja dengan sangat baik, tetapi terkadang operasi penyanggaan, penyortiran, dan penggabungan algoritme menggunakan banyak sumber daya CPU dan memori. Ini terutama benar jika tidak semua indeks sangat selektif, sehingga pemindaian paralel mengembalikan banyak baris ke operasi penggabungan. Ingatlah bahwa pengoptimal tidak memperhitungkan biaya ini—pengoptimal hanya mengoptimalkan jumlah halaman acak yang dibaca. Hal ini dapat membuatnya "di bawah harga" kueri, yang sebenarnya mungkin berjalan lebih lambat daripada pemindaian tabel biasa. Penggunaan memori dan CPU yang intensif juga cenderung memengaruhi kueri bersamaan, tetapi Anda tidak akan melihat efek ini saat menjalankan kueri secara terpisah. Ini adalah alasan lain untuk merancang tolok ukur yang realistis
Jika kueri Anda berjalan lebih lambat karena batasan pengoptimal ini, Anda dapat mengatasinya dengan menonaktifkan beberapa indeks dengan
mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
1, atau kembali ke taktik lama
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
1
Penyebaran kesetaraan terkadang memiliki biaya yang tidak terduga. Misalnya, pertimbangkan daftar
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_9 besar pada kolom yang diketahui pengoptimal akan sama dengan beberapa kolom pada tabel lain, karena
mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
4, atau
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
4 klausa yang menyetel kolom sama satu sama lain
Pengoptimal akan "berbagi" daftar dengan menyalinnya ke kolom terkait di semua tabel terkait. Ini biasanya membantu, karena memberikan pengoptimal kueri dan mesin eksekusi lebih banyak opsi untuk tempat mengeksekusi pemeriksaan
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
9. Namun jika daftarnya sangat besar, ini dapat mengakibatkan pengoptimalan dan eksekusi yang lebih lambat. Tidak ada solusi bawaan untuk masalah ini pada saat penulisan ini—Anda harus mengubah kode sumber jika itu masalah bagi Anda. (Ini bukan masalah bagi kebanyakan orang. )
MySQL tidak dapat menjalankan satu kueri secara paralel di banyak CPU. Ini adalah fitur yang ditawarkan oleh beberapa server database lain, tetapi bukan MySQL. Kami menyebutkannya agar Anda tidak menghabiskan banyak waktu untuk mencari tahu cara mendapatkan eksekusi kueri paralel di MySQL
MySQL tidak dapat melakukan gabungan hash yang sebenarnya pada saat penulisan ini—semuanya adalah gabungan loop bersarang. Namun, Anda dapat meniru gabungan hash menggunakan indeks hash. Jika Anda tidak menggunakan mesin penyimpanan Memori, Anda juga harus meniru indeks hash. Kami menunjukkan kepada Anda bagaimana melakukan ini di "Membangun indeks hash Anda sendiri".
MySQL secara historis tidak dapat melakukan pemindaian indeks longgar, yang memindai rentang indeks yang tidak berdekatan. Pemindaian indeks MySQL umumnya memerlukan titik awal yang ditentukan dan titik akhir yang ditentukan dalam indeks, bahkan jika hanya beberapa baris yang tidak bersebelahan di tengah yang benar-benar diinginkan untuk kueri. MySQL akan memindai seluruh rentang baris dalam titik akhir ini
Sebuah contoh akan membantu memperjelas hal ini. Misalkan kita memiliki tabel dengan indeks pada kolom
mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
7 dan kita ingin menjalankan kueri berikut
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_3
Karena indeks dimulai dengan kolom
mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
_8, tetapi klausa
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
0 kueri tidak menentukan kolom
mysql> SELECT DISTINCT film.film_id FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id);
8, MySQL akan melakukan pemindaian tabel dan menghilangkan baris yang tidak cocok dengan klausa
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
0, seperti yang ditunjukkan pada
Gambar 4-5. MySQL memindai seluruh tabel untuk menemukan baris
Sangat mudah untuk melihat bahwa ada cara yang lebih cepat untuk menjalankan kueri ini. Struktur indeks (tetapi bukan API mesin penyimpanan MySQL) memungkinkan Anda mencari ke awal setiap rentang nilai, memindai hingga akhir rentang, lalu mundur dan melompat ke awal rentang berikutnya. menunjukkan seperti apa strategi itu jika MySQL mampu melakukannya
Perhatikan tidak adanya
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
0 klausa, yang tidak diperlukan karena indeks saja memungkinkan kita melewati baris yang tidak diinginkan. (Sekali lagi, MySQL belum bisa melakukan ini. )
Gambar 4-6. Pemindaian indeks longgar, yang saat ini tidak dapat dilakukan MySQL, akan lebih efisien
Memang ini adalah contoh sederhana, dan kami dapat dengan mudah mengoptimalkan kueri yang telah kami tampilkan dengan menambahkan indeks yang berbeda. Namun, ada banyak kasus di mana menambahkan indeks lain tidak dapat menyelesaikan masalah. Salah satu contohnya adalah kueri yang memiliki kondisi jangkauan di kolom pertama indeks dan kondisi persamaan di kolom kedua
Mulai dari MySQL 5. 0, pemindaian indeks longgar dimungkinkan dalam keadaan terbatas tertentu, seperti kueri yang menemukan nilai maksimum dan minimum dalam kueri yang dikelompokkan
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_4
Informasi “Menggunakan indeks untuk kelompok menurut” dalam paket
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
8 ini menunjukkan pemindaian indeks yang longgar. Ini adalah pengoptimalan yang bagus untuk tujuan khusus ini, tetapi ini bukan pemindaian indeks longgar untuk tujuan umum. Mungkin lebih baik disebut "probe indeks longgar. ”
Hingga MySQL mendukung pemindaian indeks longgar tujuan umum, solusinya adalah menyediakan konstanta atau daftar konstanta untuk kolom utama indeks. Kami menunjukkan beberapa contoh cara mendapatkan kinerja yang baik dengan jenis kueri ini dalam studi kasus pengindeksan kami di bab sebelumnya
MySQL tidak mengoptimalkan kueri
mysql> SELECT film_id FROM sakila.film
-> WHERE EXISTS(SELECT * FROM sakila.film_actor
-> WHERE film.film_id = film_actor.film_id);
4 dan
mysql> SELECT film_id FROM sakila.film
-> WHERE EXISTS(SELECT * FROM sakila.film_actor
-> WHERE film.film_id = film_actor.film_id);
5 tertentu dengan sangat baik. Ini sebuah contoh
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
5
Karena tidak ada indeks pada
mysql> SELECT film_id FROM sakila.film
-> WHERE EXISTS(SELECT * FROM sakila.film_actor
-> WHERE film.film_id = film_actor.film_id);
6, kueri ini melakukan pemindaian tabel. Jika MySQL memindai kunci utama, secara teoritis dapat berhenti setelah membaca baris pertama yang cocok, karena kunci utama benar-benar menaik dan setiap baris berikutnya akan memiliki
SELECT * FROM sakila.film
WHERE EXISTS (
SELECT * FROM sakila.film_actor WHERE actor_id = 1
AND film_actor.film_id = film.film_id);
3 yang lebih besar. Namun, dalam kasus ini, MySQL akan memindai seluruh tabel, yang dapat Anda verifikasi dengan membuat profil kueri. Solusinya adalah menghapus
mysql> SELECT film_id FROM sakila.film
-> WHERE EXISTS(SELECT * FROM sakila.film_actor
-> WHERE film.film_id = film_actor.film_id);
_4 dan menulis ulang kueri dengan
mysql> EXPLAIN SELECT film.film_id, film.language_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists
3, sebagai berikut
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_6
Strategi umum ini sering bekerja dengan baik ketika MySQL memilih untuk memindai lebih banyak baris daripada yang diperlukan. Jika Anda seorang yang murni, Anda mungkin keberatan bahwa kueri ini tidak memiliki inti dari SQL. Kami seharusnya dapat memberi tahu server apa yang kami inginkan dan server seharusnya mengetahui cara mendapatkan data itu, sedangkan, dalam hal ini, kami memberi tahu MySQL cara menjalankan kueri dan, akibatnya, tidak . Benar, tetapi terkadang Anda harus mengkompromikan prinsip Anda untuk mendapatkan kinerja yang tinggi
SELECT dan UPDATE di meja yang sama
MySQL tidak membiarkan Anda
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
_8 dari tabel sambil menjalankan
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
01 di atasnya secara bersamaan. Ini sebenarnya bukan batasan pengoptimal, tetapi mengetahui bagaimana MySQL mengeksekusi kueri dapat membantu Anda mengatasinya. Berikut adalah contoh kueri yang tidak diizinkan, meskipun itu adalah SQL standar. Kueri memperbarui setiap baris dengan jumlah baris serupa dalam tabel
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
_7
Untuk mengatasi batasan ini, Anda dapat menggunakan tabel turunan, karena MySQL mewujudkannya sebagai tabel sementara. Ini secara efektif mengeksekusi dua kueri. satu
mysql> EXPLAIN SELECT * FROM sakila.film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type | table | type | possible_keys |
+----+--------------------+------------+--------+------------------------+
| 1 | PRIMARY | film | ALL | NULL |
| 2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+
_8 di dalam subkueri, dan satu multitabel
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
01 dengan hasil gabungan dari tabel dan subkueri. Subkueri membuka dan menutup tabel sebelum
-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakila.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);
01 luar membuka tabel, sehingga kueri sekarang akan berhasil
Bisakah MySQL menangani 1 juta catatan?
Berapa banyak baris yang terlalu banyak untuk MySQL?
Batas ukuran baris maksimum MySQL sebesar 65.535 byte ditunjukkan dalam contoh InnoDB dan MyISAM berikut. Batas diterapkan terlepas dari mesin penyimpanan, meskipun mesin penyimpanan mungkin mampu mendukung baris yang lebih besar.
Apa cara tercepat untuk mengeksekusi kueri dengan jutaan catatan?
Hindari kunci utama peningkatan otomatis. Sebagian besar sistem saat ini tidak hanya menargetkan satu wilayah, tetapi juga bisa menjadi pasar global. .
Hindari bergabung dengan catatan tabel (gabung kiri, gabungan luar, gabungan dalam, dll).
Jangan gunakan kunci SQL. .
Hindari fungsi agregasi. .
Coba gunakan fungsi SQL hanya dengan satu kueri rekaman
Berapa kinerja ukuran tabel maksimum di MySQL?
Anda menggunakan tabel MyISAM dan ruang yang diperlukan untuk tabel melebihi yang diizinkan oleh ukuran penunjuk internal. MyISAM mengizinkan data dan file indeks untuk tumbuh hingga 256TB secara default, tetapi batas ini dapat diubah hingga ukuran maksimum yang diizinkan 65.536TB (2567 − .
