Kemahiran pengoptimuman SQL yang mesti dikuasai oleh pengaturcara biasa

Sama ada di tempat kerja atau dalam temu duga, pada asasnya anda perlu menguasai beberapa kemahiran pengoptimuman SQL, seperti menggunakan explain untuk melihat pelan pelaksanaan SQL, dan kemudian mengoptimumkan SQL mengikut pelan pelaksanaan.

Penggunaan explain dan analisis bidang berkaitan kini pada asasnya adalah standard untuk pengaturcara.

Tidak, sila baca dengan teliti.

1. Apakah rancangan pelaksanaan MySQL untuk memahami dengan lebih baik tentang rancangan pelaksanaan, anda perlu mempunyai pemahaman yang mudah tentang struktur asas MySQL dan prinsip asas pertanyaan.

Seni bina fungsional MySQL sendiri terbahagi kepada tiga bahagian iaitu lapisan aplikasi, lapisan logik, dan lapisan fizikal Bukan sahaja MySQL, tetapi kebanyakan produk pangkalan data yang lain dibahagikan mengikut seni bina ini.

Lapisan aplikasi bertanggungjawab terutamanya untuk berinteraksi dengan pelanggan, mewujudkan pautan, mengingat status pautan, mengembalikan data dan bertindak balas kepada permintaan.
Lapisan logik bertanggungjawab terutamanya untuk pemprosesan pertanyaan, pengurusan transaksi dan pemprosesan fungsi pangkalan data lain, mengambil pertanyaan sebagai contoh.
Selepas mula-mula menerima SQL pertanyaan, pangkalan data akan segera memperuntukkan benang untuk memprosesnya Dalam langkah pertama, pemproses pertanyaan akan mengoptimumkan pertanyaan SQL Selepas pengoptimuman, pelan pelaksanaan akan dijana dan kemudian diserahkan kepada pelaksana pelan untuk pelaksanaan.

Pelaksana pelan perlu mengakses pengurus urus niaga peringkat rendah dan pengurus storan untuk mengendalikan data Pembahagian kerja masing-masing adalah berbeza Akhirnya, maklumat struktur pertanyaan diperoleh dengan memanggil fail lapisan fizikal, dan hasil akhir bertindak balas kepada lapisan aplikasi.

Lapisan fizikal, fail yang disimpan pada cakera fizikal sebenar, terutamanya termasuk fail data sen dan fail log.
Melalui huraian di atas, menjana pelan pelaksanaan merupakan langkah penting untuk melaksanakan SQL Prestasi SQL boleh dilihat secara intuitif dengan melihat pelan pelaksanaan menyediakan pelbagai jenis dan tahap pertanyaan semak dan gunakannya sebagai asas untuk analisis prestasi.

2. Bagaimana untuk menganalisis pelan pelaksanaanMySQL memberikan kami kata kunci terangkan untuk melihat pelan pelaksanaan SQL secara visual.

terangkan menunjukkan cara MySQL menggunakan indeks untuk memproses pernyataan terpilih dan menyertai jadual, yang boleh membantu memilih indeks yang lebih baik dan menulis pernyataan pertanyaan yang lebih dioptimumkan.

Di bawah ini kami gunakan explain untuk membuat pertanyaan, seperti berikut:

mysql> explain select * from payment;
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16086 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Terdapat 12 lajur dalam struktur pertanyaan Memahami maksud setiap lajur adalah penting untuk memahami rancangan pelaksanaan .

.

SELECT type, yang boleh menjadi mana-mana yang berikut: SIMPLE: simple SELECT (tidak menggunakan UNION atau subquery) PRIMARY: terluar ON SELECTION : Pernyataan SELECT kedua atau berikut dalam UNION, bergantung pada pertanyaan luaran: Keputusan UNIONSUBQUERY: SELECT: SELECT of the derived table (subquery of the FROM clause)sistem: Jadual hanya mempunyai satu baris (=jadual sistem). Ini adalah kes khas jenis gabungan const. : Untuk setiap gabungan baris daripada jadual sebelumnya, baca satu baris daripada jadual ini. Ini mungkin jenis gabungan terbaik, selain jenis const. ref: Jenis gabungan ini seperti ref, tetapi menambah MySQL untuk mencari baris yang mengandungi nilai NULL secara khusus. kekunci tunjukkan MySQL sebenarnya memutuskan untuk menggunakan ( index). Jika tiada indeks dipilih, kuncinya adalah NULL. Menunjukkan panjang kunci yang MySQL memutuskan untuk digunakan. Jika kuncinya NULL, panjangnya ialah NULL. Lebih pendek panjangnya lebih baik tanpa kehilangan ketepatan Menunjukkan lajur atau pemalar yang digunakan bersama-sama dengan kunci untuk memilih baris daripada jadual. Menunjukkan bilangan baris yang MySQL fikir ia mesti semak semasa melaksanakan pertanyaan. Mendarab data merentas berbilang baris memberikan anggaran bilangan baris untuk diproses. menunjukkan anggaran peratusan bilangan baris yang ditapis mengikut keadaan.

select_type
berikut SELECT pernyataanDEPENDENT UNION	UNION RESULT
DEPENDENT pertama dalam subquery SUBQUERY: SELECT pertama dalam subquery, bergantung pada pertanyaan luarDERIVED	tableJadual yang dirujuk oleh baris output ws yang partition yang akan diakses oleh pertanyaan jika pertanyaan adalah berdasarkan jadual partition.
taip	Taip sertai. Pelbagai jenis gabungan diberikan di bawah, disusun daripada yang terbaik kepada yang paling teruk:
const: Jadual mempunyai paling banyak satu baris yang sepadan, yang akan dibaca pada permulaan pertanyaan. Oleh kerana hanya terdapat satu baris, nilai lajur dalam baris ini boleh dianggap sebagai pemalar oleh pengoptimum yang lain. jadual const adalah pantas kerana ia dibaca sekali sahaja!	eq_ref
: Untuk setiap gabungan baris daripada jadual sebelumnya, semua baris dengan nilai indeks yang sepadan akan dibaca daripada jadual ini. ref_or_null	index_merge: Jenis gabungan ini menunjukkan bahawa kaedah pengoptimuman gabungan indeks digunakan. unique_subquery: Jenis ini menggantikan ref subquery IN dalam bentuk berikut: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery ialah fungsi carian indeks yang boleh menggantikan sepenuhnya subquery dan lebih cekap. index_subquery: Jenis gabungan ini serupa dengan unique_subquery. IN subqueries boleh digantikan, tetapi hanya untuk indeks bukan unik dalam subqueries dalam bentuk berikut: nilai IN (SELECT key_column FROM single_table WHERE some_expr) julat: Dapatkan hanya baris dalam julat tertentu, menggunakan indeks untuk memilih baris ( Cadangan, paling teruk tahap ni). index: Jenis cantuman ini adalah sama seperti SEMUA, kecuali hanya pokok indeks yang diimbas. Ini biasanya lebih pantas daripada SEMUA kerana fail indeks biasanya lebih kecil daripada fail data. SEMUA: Lakukan imbasan jadual lengkap untuk setiap gabungan baris daripada jadual sebelumnya, menunjukkan bahawa pertanyaan perlu dioptimumkan. Secara umumnya, adalah perlu untuk memastikan bahawa pertanyaan mencapai sekurang-kurangnya tahap julat, dan sebaik-baiknya mencapai rujukan. Sistem di atas adalah yang terbaik, dalam susunan menurun, SEMUA adalah yang paling teruk
possible_keys	menunjukkan indeks mana yang boleh digunakan oleh MySQL untuk mencari baris dalam jadual
	key_len
	ref
	baris
	ditapis
	Tambahan Lajur ini mengandungi butiran cara MySQL menyelesaikan pertanyaan Distinct: ​​​​Selepas MySQL menemui baris pertama yang sepadan, ia berhenti mencari lebih banyak baris untuk gabungan baris semasa. Pilih jadual yang dioptimumkan jauhMySQL mengembalikan data tanpa melintasi jadual atau indeks sama sekali, menunjukkan bahawa ia telah dioptimumkan sehingga ia tidak boleh dioptimumkan lagi 🎜Tidak wujud🎜: MySQL boleh mengoptimumkan pertanyaan dengan LEFT JOIN dan ditemui 1 padanan standard LEFT JOIN Selepas satu baris, tiada lagi baris disemak dalam jadual untuk gabungan baris sebelumnya. 🎜julat disemak untuk setiap rekod (peta indeks: #): MySQL tidak menemui indeks yang baik yang boleh digunakan, tetapi mendapati bahawa jika nilai lajur dari jadual sebelumnya diketahui, beberapa indeks boleh digunakan. Menggunakan filesort: MySQL memerlukan pas tambahan untuk memikirkan cara mendapatkan semula baris dalam tertib diisih, menunjukkan bahawa pertanyaan perlu dioptimumkan. Menggunakan indeks: Dapatkan maklumat lajur daripada jadual dengan membaca baris sebenar menggunakan hanya maklumat dalam pepohon indeks tanpa mencari lebih lanjut. Menggunakan sementara: Untuk menyelesaikan pertanyaan, MySQL perlu mencipta jadual sementara untuk menampung keputusan, yang bermaksud pertanyaan itu perlu dioptimumkan. Menggunakan where:Klausa WHERE digunakan untuk mengehadkan baris mana yang sepadan dengan jadual seterusnya atau dihantar kepada pelanggan. Menggunakan sort_union(...), Menggunakan union(...), Menggunakan intersect(...): Fungsi ini menggambarkan cara untuk menggabungkan imbasan indeks untuk jenis gabungan index_merge. Menggunakan indeks untuk kumpulan-oleh: Sama seperti kaedah Menggunakan indeks untuk mengakses jadual, Menggunakan indeks untuk kumpulan-oleh bermakna MySQL telah menemui indeks yang boleh digunakan untuk menanyakan semua lajur pertanyaan GROUP BY atau DISTINCT tanpa tambahan mencari cakera keras Akses jadual sebenar.

Mengikut jadual di atas, ia boleh memberikan bantuan yang baik dalam analisis pelan pelaksanaan.

Nota: Jika mahu menghadapi temu duga, sebaiknya menghafalnya Jika anda tidak dapat menghafal semuanya, anda juga harus dapat menyebut 123, dan kemudian mengatakan bahawa anda tidak ingat begitu banyak. Anda boleh membaca dokumen yang berkaitan untuk membandingkan.

Atas ialah kandungan terperinci Kemahiran pengoptimuman SQL yang mesti dikuasai oleh pengaturcara biasa. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!