Rumah >pangkalan data >tutorial mysql >Kuasai sepenuhnya kemahiran pengindeksan mysql (perkongsian ringkasan)
Artikel ini membawa anda pengetahuan yang berkaitan tentang indeks mysql, termasuk struktur logik pernyataan pelaksanaan mysql dan sql saya harap ia akan membantu anda.
Seni bina enjin storan MySQL memisahkan pemprosesan pertanyaan daripada penyimpanan/pendapatan data. Berikut ialah gambar rajah seni bina logik MySQL:
Setiap sambungan pelanggan sepadan dengan urutan pada pelayan. Kumpulan benang dikekalkan pada pelayan untuk mengelak daripada mencipta dan memusnahkan benang untuk setiap sambungan. Apabila pelanggan menyambung ke pelayan MySQL, pelayan mengesahkannya. Pengesahan boleh dilakukan melalui nama pengguna dan kata laluan, atau melalui sijil SSL. Selepas pengesahan log masuk diluluskan, pelayan juga akan mengesahkan sama ada klien mempunyai kuasa untuk melaksanakan pertanyaan tertentu.
menyusun SQL dan mengoptimumkannya (seperti melaraskan susunan bacaan jadual, memilih indeks yang sesuai, dsb. .). Untuk pernyataan SELECT, sebelum menghuraikan pertanyaan, pelayan akan menyemak cache pertanyaan terlebih dahulu Jika hasil pertanyaan yang sepadan boleh ditemui di dalamnya, hasil pertanyaan akan dikembalikan secara langsung tanpa memerlukan penghuraian pertanyaan, pengoptimuman, dsb. Prosedur tersimpan, pencetus, pandangan, dsb. semuanya dilaksanakan dalam lapisan ini.
Enjin storan bertanggungjawab untuk menyimpan data dalam MySQL, mengekstrak data, memulakan transaksi, dsb. Enjin storan berkomunikasi dengan lapisan atas melalui API ini melindungi perbezaan antara enjin storan yang berbeza, menjadikan perbezaan ini telus kepada proses pertanyaan lapisan atas. Enjin storan tidak akan menghuraikan SQL.
MyISAM: Setiap MyISAM disimpan sebagai tiga fail pada cakera. Ia adalah: fail definisi jadual, fail data dan fail indeks. Nama fail pertama bermula dengan nama jadual dan sambungan menunjukkan jenis fail. Fail .frm menyimpan takrif jadual. Sambungan fail data ialah .MYD (MYData). Sambungan fail indeks ialah .MYI (MYIndex).
InnoDB: Semua jadual disimpan dalam fail data yang sama (atau berbilang fail, atau fail ruang jadual bebas Saiz jadual InnoDB hanya dihadkan oleh saiz fail sistem pengendalian umumnya 2GB).
MyISAM: MyISAM menyokong tiga format storan yang berbeza: jadual statik (lalai, tetapi ambil perhatian bahawa tidak boleh ada ruang di hujung data, ia akan dialih keluar ), jadual dinamik, jadual termampat. Selepas jadual dibuat dan data diimport, ia tidak akan diubah suai Anda boleh menggunakan jadual termampat untuk mengurangkan penggunaan ruang cakera.
InnoDB: Memerlukan lebih banyak memori dan storan, ia akan mewujudkan kumpulan penimbal khususnya sendiri dalam memori utama untuk menyimpan data dan indeks.
MyISAM: Data disimpan dalam bentuk fail, jadi ia sangat mudah untuk pemindahan data merentas platform. Anda boleh melakukan operasi di atas meja secara individu semasa sandaran dan pemulihan.
InnoDB: Penyelesaian percuma termasuk menyalin fail data, menyandarkan binlog atau menggunakan mysqldump, yang agak menyakitkan apabila volum data mencapai berpuluh-puluh gigabait.
MyISAM: Penekanan adalah pada prestasi, setiap pertanyaan adalah atom dan masa pelaksanaannya lebih cepat daripada jenis InnoDB, tetapi ia tidak menyediakan sokongan urus niaga.
InnoDB: Menyediakan sokongan transaksi, kunci asing dan fungsi pangkalan data lanjutan yang lain. Jadual selamat urus niaga (patuh ACID) dengan keupayaan transaksi (komit), gulung balik (balik semula) dan ranap sistem.
MyISAM: Anda boleh mencipta indeks bersama dengan medan lain. Lajur pertumbuhan automatik enjin mestilah indeks Jika ia adalah indeks gabungan, lajur pertumbuhan automatik tidak perlu menjadi lajur pertama Ia boleh diisih mengikut lajur sebelumnya dan kemudian ditambah.
InnoDB: InnoDB mesti mengandungi indeks dengan medan ini sahaja. Lajur pertumbuhan automatik enjin mestilah indeks, dan jika indeks komposit, lajur itu juga mesti menjadi lajur pertama indeks komposit.
MyISAM: Hanya kunci peringkat jadual disokong Apabila pengguna mengendalikan jadual myisam, pilih, kemas kini, padam dan masukkan penyata akan semuanya ditugaskan secara automatik ke jadual Mengunci, jika jadual yang dikunci memenuhi konkurensi sisipan, data baharu boleh dimasukkan di hujung jadual.
InnoDB: Menyokong transaksi dan kunci peringkat baris ialah ciri terbesar innodb. Kunci baris meningkatkan prestasi operasi serentak berbilang pengguna. Walau bagaimanapun, kunci baris InnoDB hanya sah pada kunci utama WHERE Sebarang kunci bukan utama WHERE akan mengunci seluruh jadual.
MyISAM: menyokong indeks teks penuh jenis FULLTEXT
InnoDB: tidak menyokong indeks teks penuh jenis FULLTEXT , tetapi innodb boleh menggunakannya Pemalam sphinx menyokong pengindeksan teks penuh dan kesannya lebih baik.
MyISAM: Membenarkan jadual tanpa sebarang indeks dan kunci utama wujud.
InnoDB: Jika tiada kunci utama atau indeks unik bukan kosong ditetapkan, kunci utama 6 bait (tidak kelihatan kepada pengguna) akan dijana secara automatik Data adalah sebahagian daripada indeks utama, dan indeks tambahan menyimpannya nilai indeks utama.
MyISAM: Menyimpan jumlah baris dalam jadual Jika anda memilih count() daripada jadual; ia akan dibawa keluar terus nilai.
InnoDB: Jumlah bilangan baris dalam jadual tidak disimpan Jika anda menggunakan pilih count(*) daripada jadual, ia akan menggunakan banyak wang. selepas menambah syarat wehre, myisam dan innodb memprosesnya.
MyISAM: Jika anda melakukan sejumlah besar PILIHAN, MyISAM ialah pilihan yang lebih baik.
InnoDB: Jika data anda melakukan banyak INSERT atau UPDATE, anda harus menggunakan jadual InnoDB atas sebab prestasi.
MyISAM: Tidak disokong
InnoDB: Disokong
select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...(2) Proses penghuraian
from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
alter table user add INDEX `user_index_username_password` (`username`,`password`)5 Prinsip indeks MySQL -> B-tree Struktur data asas indeks MySQL ialah B-tree B Tree ialah Pengoptimuman berdasarkan B-Tree menjadikannya lebih sesuai untuk melaksanakan struktur indeks storan luaran Enjin storan InnoDB menggunakan B-Tree untuk melaksanakan struktur indeksnya. Setiap nod dalam rajah struktur B-Tree mengandungi bukan sahaja nilai utama data, tetapi juga nilai data. Ruang storan setiap halaman adalah terhad Jika data data adalah besar, bilangan kunci yang boleh disimpan dalam setiap nod (iaitu satu halaman) akan menjadi sangat kecil Apabila jumlah data yang disimpan adalah besar, ia juga akan membawa ke B- Kedalaman Pokok lebih besar, yang meningkatkan bilangan I/O cakera semasa pertanyaan, sekali gus menjejaskan kecekapan pertanyaan. Dalam B Tree, semua nod rekod data disimpan pada nod daun pada lapisan yang sama mengikut urutan nilai kunci Hanya maklumat nilai kunci disimpan pada nod bukan daun Ini boleh meningkatkan bilangan nilai kunci yang disimpan dalam setiap nod Kurangkan ketinggian Pokok B. B Tree mempunyai beberapa perbezaan berbanding B-Tree: Nod bukan daun hanya menyimpan maklumat nilai utama.
Terdapat penuding pautan antara semua nod daun.
Rekod data disimpan dalam nod daun.
Optimumkan B-Tree dalam bahagian sebelumnya Memandangkan nod bukan daun bagi B Tree hanya menyimpan maklumat nilai utama, dengan mengandaikan bahawa setiap blok cakera boleh menyimpan 4 nilai utama dan maklumat penunjuk, ia akan menjadi struktur bagi. Pokok B. Seperti yang ditunjukkan dalam rajah di bawah:
Indeks B Tree dalam pangkalan data boleh dibahagikan kepada indeks berkelompok dan indeks sekunder. Contoh rajah B Tree di atas dilaksanakan dalam pangkalan data sebagai indeks berkelompok Nod daun dalam B Tree indeks berkelompok menyimpan data rekod baris keseluruhan jadual. Perbezaan antara indeks tambahan dan indeks berkelompok ialah nod daun indeks tambahan tidak mengandungi semua data rekod baris, tetapi kunci indeks berkelompok yang menyimpan data baris yang sepadan, iaitu kunci utama. Apabila menanyakan data melalui indeks tambahan, enjin storan InnoDB akan merentasi indeks tambahan untuk mencari kunci utama, dan kemudian mencari data rekod baris lengkap dalam indeks berkelompok melalui kunci utama.
(1) Menggunakan semua kekunci indeks indeks bersama boleh mencetuskan indeks bersama
(2) Menggunakan semua kekunci indeks daripada indeks bersama, tetapi bersambung dengan atau , indeks sendi
(3) Apabila medan pertama di sebelah kiri indeks sendi digunakan secara bersendirian, indeks sendi
(4) Apabila menggunakan medan lain indeks sendi sahaja, indeks sendi tidak boleh dicetuskan 6. Menganalisis rancangan pelaksanaan sql---terangkanTerangkan boleh mensimulasikan pengoptimuman SQL dan melaksanakan pernyataan SQL. 1. Pengenalan kepada menggunakan explan (1) Jadual pengguna (2) Jadual jabatan (3) Indeks tidak dicetuskan (4) Indeks tercetus (5 ) Analisis keputusanJadual yang muncul dalam baris pertama explain ialah jadual pemacu.
index_merge: Jenis gabungan ini menunjukkan bahawa kaedah pengoptimuman gabungan indeks digunakan. Dalam kes ini, lajur kunci mengandungi senarai indeks yang digunakan dan key_len mengandungi elemen kunci terpanjang bagi indeks yang digunakan.
unique_subquery: Jenis ini menggantikan ref subquery IN dalam bentuk berikut: nilai IN (SELECT primary_key FROM single_table WHERE some_expr unique_subquery ialah fungsi carian indeks yang boleh menggantikan sepenuhnya subquery, 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: hanya ambil julat yang diberikan Baris , gunakan indeks untuk memilih baris. Lajur utama menunjukkan indeks yang digunakan. key_len mengandungi elemen kunci terpanjang bagi indeks yang digunakan. Lajur rujukan ialah NULL dalam jenis ini. Apabila menggunakan operator =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN atau IN untuk membandingkan lajur utama dengan pemalar, anda boleh menggunakan julat
indeks: Jenis cantuman ini adalah sama seperti SEMUA, kecuali pepohon indeks sahaja 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. Ini biasanya tidak baik jika jadual adalah yang pertama tidak ditanda const, dan biasanya buruk dalam kes itu. Ia biasanya mungkin untuk menambah lebih banyak indeks tanpa menggunakan SEMUA supaya baris boleh diambil berdasarkan nilai malar atau nilai lajur dalam jadual sebelumnya.
(5) possible_keys: Lajur possible_keys menunjukkan indeks yang MySQL boleh gunakan untuk mencari baris dalam jadual. Ambil perhatian bahawa lajur ini adalah bebas sepenuhnya daripada susunan jadual yang ditunjukkan dalam output EXPLAIN. Ini bermakna bahawa beberapa kunci dalam possible_keys sebenarnya tidak boleh digunakan dalam susunan jadual yang dijana.
(6) kunci: Lajur kunci memaparkan kunci (indeks) yang MySQL benar-benar memutuskan untuk digunakan. Jika tiada indeks dipilih, kuncinya adalah NULL. Untuk memaksa MySQL menggunakan atau mengabaikan indeks pada lajur possible_keys, gunakan FORCE INDEX, USE INDEX atau ABAIKAN INDEX dalam pertanyaan.
(7) key_len: Lajur key_len memaparkan panjang kunci yang MySQL memutuskan untuk digunakan. Jika kuncinya NULL, panjangnya ialah NULL. Ambil perhatian bahawa dengan menggunakan nilai key_len kita boleh menentukan bahagian mana kata kunci berbilang bahagian yang sebenarnya akan digunakan oleh MySQL.
(8) rujukan: Lajur rujukan menunjukkan lajur atau pemalar yang digunakan dengan kunci untuk memilih baris daripada jadual.
(9) baris: Lajur baris menunjukkan bilangan baris yang MySQL fikir ia mesti semak semasa melaksanakan pertanyaan.
(10) Tambahan: Lajur ini mengandungi butiran pertanyaan yang diselesaikan oleh MySQL.
Berbeza: Selepas MySQL menemui baris pertama yang sepadan, ia berhenti mencari lebih banyak baris untuk gabungan baris semasa.
Tidak wujud: MySQL boleh melakukan pengoptimuman LEFT JOIN pada pertanyaan Selepas mencari baris yang sepadan dengan standard LEFT JOIN, ia tidak akan menyemak lagi dalam jadual untuk gabungan baris sebelumnya. OK.
julat disemak untuk setiap rekod (peta indeks: #): MySQL tidak menemui indeks yang baik untuk digunakan, tetapi mendapati bahawa adalah mungkin untuk mengindeks sebahagiannya jika nilai lajur dari jadual sebelumnya diketahui Boleh digunakan. Untuk setiap gabungan baris daripada jadual sebelumnya, MySQL menyemak sama ada baris itu boleh diambil menggunakan kaedah capaian julat atau index_merge.
Menggunakan failsort: MySQL memerlukan pas tambahan untuk memikirkan cara mendapatkan semula baris dalam susunan yang disusun. Pengisihan dicapai dengan menyemak imbas semua baris berdasarkan jenis gabungan dan menyimpan kekunci isihan dan penuding ke baris untuk semua baris yang sepadan dengan klausa WHERE. Kekunci kemudian diisih dan baris diambil dalam susunan yang disusun.
Menggunakan indeks: Dapatkan maklumat lajur daripada jadual dengan membaca baris sebenar menggunakan hanya maklumat dalam pepohon indeks tanpa mencari lebih lanjut. Strategi ini boleh digunakan apabila pertanyaan hanya menggunakan lajur yang merupakan sebahagian daripada indeks tunggal.
Menggunakan sementara: Untuk menyelesaikan pertanyaan, MySQL perlu mencipta jadual sementara untuk menyimpan keputusan. Situasi biasa ialah apabila pertanyaan mengandungi klausa GROUP BY dan ORDER BY yang boleh menyenaraikan lajur mengikut situasi yang berbeza.
Menggunakan where: klausa WHERE digunakan untuk mengehadkan baris mana yang sepadan dengan jadual seterusnya atau dihantar kepada pelanggan. Melainkan anda secara khusus meminta atau menyemak semua baris daripada jadual, pertanyaan mungkin mempunyai beberapa ralat jika nilai Tambahan tidak Menggunakan tempat dan jenis gabungan jadual ialah SEMUA atau indeks.
Menggunakan sort_union(...), Menggunakan union(...), Menggunakan intersect(...): Fungsi ini menggambarkan cara untuk menggabungkan imbasan indeks untuk jenis gabungan index_merge.
Menggunakan indeks untuk kumpulan mengikut: Sama seperti Menggunakan kaedah indeks untuk mengakses jadual, Menggunakan indeks untuk kumpulan mengikut bermakna MySQL telah menemui indeks yang boleh digunakan untuk membuat pertanyaan GROUP BY atau DISTINCT pertanyaan semua lajur tanpa perlu mencari cakera keras tambahan untuk mengakses jadual sebenar. Juga, gunakan indeks dengan cara yang paling cekap supaya untuk setiap kumpulan, hanya beberapa entri indeks dibaca.
Dengan mendarab semua nilai dalam lajur baris keluaran EXPLAIN, anda boleh mendapat petunjuk tentang betapa bagusnya gabungan. Ini sepatutnya memberitahu anda berapa banyak baris yang perlu diperiksa oleh MySQL untuk melaksanakan pertanyaan. Produk ini juga digunakan untuk menentukan pernyataan SELECT berbilang jadual yang hendak dilaksanakan apabila anda menggunakan pembolehubah max_join_size untuk mengehadkan pertanyaan.
Pembelajaran yang disyorkan: tutorial video mysql
Atas ialah kandungan terperinci Kuasai sepenuhnya kemahiran pengindeksan mysql (perkongsian ringkasan). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!