Berbagi teknologi

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

Pertama, perkenalkan secara singkat sql:

Bahasa SQL dibagi menjadi empat kategori: bahasa kueri data DQL, bahasa manipulasi data DML, bahasa definisi data DDL, dan bahasa kontrol data DCL.

1. DQL Bahasa Kueri Data
Struktur dasar bahasa query data DQL adalah blok query yang terdiri dari klausa SELECT, klausa FROM, dan klausa WHERE: SELECT <nama field tabel> FROM <nama tabel atau tampilan> WHERE <kondisi query>

2. DML Bahasa Manipulasi Data
Ada tiga bentuk utama bahasa manipulasi data DML:
1) Sisipkan: MASUKKAN
2) Pembaruan: PEMBARUAN
3) Hapus: HAPUS

3. DDL Bahasa Definisi Data
Bahasa definisi data DDL digunakan untuk membuat berbagai objek dalam database ----- tabel, tampilan, indeks, sinonim, cluster, dll, seperti: CREATE TABLE / VIEW / INDEX / SYN / CLUSTER | Cluster sinonim indeks tampilan tabel . Operasi DDL dikirimkan secara implisit! Tidak dapat melakukan rollback

4. Bahasa Kontrol Data DCL
Bahasa kontrol data DCL digunakan untuk memberikan atau mencabut hak istimewa tertentu untuk mengakses database, mengontrol waktu dan efek transaksi manipulasi database, dan memantau database. menyukai:
1) HIBAH: Otorisasi.
2) ROLLBACK [WORK] TO [SAVEPOINT]: Mengembalikan ke titik tertentu. Rollback---ROLLBACK Perintah rollback mengembalikan status database ke status terakhir yang dikirimkan. Formatnya adalah: SQL>ROLLBACK;
3) KOMITMEN [KERJA]: Kirim. Selama operasi penyisipan, penghapusan, dan modifikasi basis data, transaksi diselesaikan hanya ketika transaksi tersebut diserahkan ke basis data. Sebelum transaksi dilakukan, hanya orang yang mengoperasikan database yang mempunyai hak untuk melihat apa yang telah dilakukan.

Tentang pernyataan otorisasi

Perintah otorisasi basis data:

GRANT<izin> pada nama tabel (atau nama kolom) kepada pengguna

Pilihan jawaban yang benar B: masukkan, pilih: izin nama tabel: pengguna pengguna: nkw

Izin daur ulang poin pengetahuan tambahan

REVOKE <izin> pada nama tabel (atau nama kolom) DARI pengguna

Menjelaskan pendahuluan pernyataan

Saya yakin semua orang sudah familiar dengan pernyataan penjelasan, yang digunakan sebagai pernyataan untuk melihat rencana eksekusi. jelaskan sering digunakan dalam analisis optimasi SQL.

Perlu diperhatikan di sini: penjelasan tidak benar-benar mengeksekusi pernyataan, tetapi hanya menampilkan rencana eksekusi.

Informasi apa yang dapat saya lihat?

• Urutan membaca tabel
• Jenis operasi operasi pembacaan data
• Indeks mana yang dapat digunakan
• Indeks mana yang sebenarnya digunakan
• Referensi antar tabel
• Berapa banyak baris di setiap tabel yang ditanyakan oleh pengoptimal

Pengenalan sintaksis dasar

EXPLAIN SELECT select具体语句
如：
EXPLAIN SELECT * FROM userpro

jelaskan fungsi masing-masing kolom

Pengenalan rinci untuk setiap kolom EXPLAIN

1，id

Setiap kata kunci SELECT berhubungan dengan sebuah id

Semakin besar nilai id maka semakin tinggi prioritasnya dan akan dieksekusi terlebih dahulu.

Jika ID-nya sama, maka dapat dianggap sebagai satu grup dan dieksekusi secara berurutan dari atas ke bawah.

Setiap nomor ID mewakili kueri independen. Semakin sedikit jumlah kueri dalam pernyataan SQL, semakin baik.

2，pilih_jenis

3. Kolom tabel mewakili nama tabel dari tabel (terkadang bukan nama tabel sebenarnya, bisa juga singkatannya).

4. partisi (opsional)

5. ketik ☆

Tipe yang umum adalah sebagai berikut

Secara umum, cobalah untuk menghindari semuanya

6， kunci dan kemungkinan_kunci

1. kunci：

   • key bidang menunjukkan indeks sebenarnya yang digunakan oleh kueri.Jika nilai bidang ini adalahNULL , menunjukkan bahwa tidak ada indeks yang digunakan. Jika bidang ini memiliki nilai, itu berarti MySQL menggunakan indeks yang ditentukan untuk menjalankan kueri.

2. kemungkinan_kunci：

   • possible_keys Bidang menampilkan daftar indeks yang dapat digunakan MySQL. Indeks ini adalah indeks yang dapat dipertimbangkan dalam kueri, namun sebenarnya tidak dapat digunakan. biasanya,possible_keys Indeks yang tercantum ditentukan berdasarkan kondisi kueri dan struktur tabel.

• jika key Bidang memiliki nama indeks, danpossible_keys Beberapa nama indeks terdaftar di , menunjukkan bahwa MySQL telah memilihkey Indeks tempat bidang dicantumkan digunakan untuk melakukan kueri, sedangkan indeks lainnya dicantumkanpossible_keys Medium bilang bisa juga dipertimbangkan, tapi akhirnya tidak dipakai.

• jika key Bidangnya adalahNULL,Dan possible_keys Beberapa nama indeks dicantumkan di , yang menunjukkan bahwa MySQL tidak menggunakan indeks apa pun saat menjalankan kueri, yang dapat mengakibatkan pemindaian tabel penuh atau metode akses lain yang tidak dioptimalkan indeks.

7, kunci_len

key_len Adalah bidang yang menjelaskan panjang kunci indeks. Ini menunjukkan penggunaan indeks dan panjang kunci indeks ketika MySQL menggunakan indeks tertentu untuk mengeksekusi kueri.

1. Indeks kolom tunggal：

   • Jika indeks kolom tunggal digunakan dan tipe kolom memiliki panjang tetap (mis. INT),Tetapi key_len Nilainya adalah panjang kolom.
   • Jika bidang panjang variabel digunakan (mis. VARCHAR),Tetapi key_len Nilai adalah panjang maksimum bidang.

2. indeks komposit：

   • Untuk indeks gabungan (yaitu, indeks yang berisi banyak kolom),key_len Mewakili total panjang semua kolom dalam indeks.

3. indeks gabungan：

   • Jika indeks gabungan pada beberapa kolom digunakan dalam kueri,key_len adalah panjang total semua kolom dalam indeks gabungan.

4. awalan indeks：

   • Dalam beberapa kasus, MySQL hanya menggunakan sebagian dari indeks. Misalnya, Anda bisa menggunakan awalan indeks sebagai bagian dari indeks untuk menjalankan kueri. pada kasus ini,key_len Panjang sebenarnya dari bagian indeks yang digunakan akan ditampilkan.

8,referensi

ref Ini adalah bidang yang menggambarkan kondisi koneksi antar tabel atau menggunakan indeks non-unik untuk mencari. Ini menunjukkan kondisi gabungan mana atau indeks mana yang digunakan MySQL untuk mengakses tabel saat menjalankan kueri.

9,baris

rows Merupakan perkiraan jumlah baris yang diakses atau diperiksa saat mengeksekusi query.

• untuk sederhana SELECT Menanyakan,rows Mewakili perkiraan jumlah baris yang akan dikembalikan.
• Untuk pertanyaan bergabung (JOIN) atau subkueri,rows Dapat mewakili jumlah baris yang diakses selama operasi penggabungan.
• Untuk pemindaian tabel (pemindaian tabel penuh atau pemindaian indeks),rows Mungkin mewakili jumlah baris yang dipindai.

10,disaring

Mewakili persentase baris yang difilter berdasarkan kondisi WHERE dan kondisi indeks. difilter mencerminkan efek pengoptimalan kueri yang diperkirakan oleh pengoptimal.

Kapan filtered Jika mendekati 100%, ini berarti kondisi kueri telah secara efektif memfilter sebagian besar baris yang tidak memenuhi kondisi, yang biasanya merupakan indikator pengoptimalan yang baik.

Sebaliknya jika filtered Nilai yang rendah mungkin menunjukkan bahwa kondisi kueri tidak cukup tepat atau pengoptimal tidak menggunakan indeks secara efektif untuk memfilter data.

11，Tambahan

Bidang informasi tambahan

Berikut ini beberapa hal yang umum Extra Bidang dan artinya:

1. Menggunakan indeks：

   • Menunjukkan bahwa kueri menggunakan indeks penutup, yaitu hasil kueri dapat dikembalikan sepenuhnya melalui indeks tanpa mengakses baris data aktual dalam tabel.

2. Menggunakan dimana：

   • Menunjukkan bahwa server MySQL akan melakukan pemfilteran bersyarat setelah mesin penyimpanan mengambil baris, alih-alih menyelesaikannya dalam indeks.

3. Menggunakan sementara：

   • Menunjukkan bahwa MySQL telah membuat tabel sementara di memori untuk memproses query. Biasa digunakan dalam operasi pengurutan atau kueri yang berisi fungsi agregat.

4. Menggunakan filesort：

   • Menunjukkan bahwa MySQL melakukan penyortiran file untuk memproses kueri. Ini biasanya terjadi ketika pengurutan tidak dapat dilakukan menggunakan indeks.

5. Rentang diperiksa untuk setiap catatan (peta indeks: ...)：

   • Menunjukkan bahwa MySQL menggunakan indeks untuk memeriksa apakah setiap catatan berada dalam rentang yang ditentukan.Ini biasanya terjadi ketikarange Dalam tipe kueri.

6. Pemindaian penuh pada kunci NULL：

   • Menunjukkan bahwa MySQL melakukan pemindaian tabel penuh dalam indeks untuk menemukan baris yang cocok dengan nilai NULL.

7. Berbeda：

   • Menunjukkan bahwa MySQL akan berhenti mencari baris duplikat setelah menemukan baris pertama yang cocok.

8. Menggunakan buffer gabungan (Block Nested Loop)：

   • Menunjukkan bahwa MySQL menggunakan buffer koneksi untuk menangani operasi koneksi. Biasanya terjadi ketika jumlah tabel koneksi besar atau ukuran tabel koneksi besar.

9. Tidak mungkin DIMANA：

   • Menunjukkan bahwa pengoptimal MySQL telah menentukan bahwa kondisi dalam klausa WHERE tidak dapat dipenuhi dan oleh karena itu tidak ada baris yang akan dipindai.

10. Tidak ada tabel yang digunakan：

    • Menunjukkan bahwa kueri tidak melibatkan tabel apa pun, misalnya SELECT NOW()。

menjelaskan tampilan eksekusi sebenarnya

persiapan data

Buat tabel s1

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

Buat tabel s2

CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

Data disiapkan sendiri.

Contoh eksekusi dan hasilnya

Permintaan sederhana

Kueri tabel tunggal

EXPLAIN SELECT * FROM `s1`;

Bergabunglah dengan kueri

EXPLAIN SELECT * FROM `s1` INNER JOIN `s2`;

subkueri

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field
= 'a');

tampilan khusus

tampilkan pilih_tipe

#Union 去重
EXPLAIN SELECT * FROM `s1` UNION SELECT * FROM `s2`;
#Union 全查
EXPLAIN SELECT * FROM `s1` UNION ALL SELECT * FROM `s2`;

Langkah terakhir adalah deduplikasi, sehingga tabel sementara akan digunakan. Namun, UNION ALL adalah kueri untuk semua, dan tidak ada pesan kueri tabel sementara yang akan muncul.

kelas tipe

const (temukan indeks sekali, hanya satu baris hasil)

EXPLAIN SELECT * FROM s1 WHERE id = 10002;

eq_ref (temukan baris dari tabel lain menggunakan indeks unik atau kunci utama)

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

ref (gunakan indeks non-unik untuk menemukan satu atau lebih baris dari tabel lain)

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

range (mengembalikan rentang baris menggunakan indeks)

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

indeks (pindai indeks sepenuhnya untuk menemukan baris)

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

Tampilan lainnya

waktu desimal yang difilter

EXPLAIN SELECT * FROM s1 WHERE key1 > 'za' AND common_field = 'la'