Apakah yang perlu saya lakukan jika indeks Mysql gagal? Analisis ringkas sebab kegagalan

Artikel ini akan merekodkan untuk semua orang kegagalan indeks Mysql dan menganalisis sebab kegagalan indeks Mysql saya harap ia akan membantu semua orang.

Artikel ini mengandungi proses pelaksanaan pertanyaan syarat Mysql's Where, pertanyaan julat untuk menghentikan indeks bersama daripada padanan, analisis operasi pulangan jadual, senario kegagalan indeks biasa, Analisis tambahan dan pengetahuan lain . [Cadangan berkaitan: tutorial video mysql]

Latar Belakang

Pertanyaan penuh muncul dalam jadual data dengan 60 juta data dan sql itu diterbitkan semula Pertanyaan penemuan pernyataan tidak menggunakan indeks tetapi menggunakan keseluruhan pertanyaan jadual untuk mengetahui punca kegagalan indeks.

# sql语句
EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>&#39;1628442000&#39; and start_date<&#39;1631120399&#39; and station_id=&#39;1809&#39; and status=&#39;2&#39;;

Jadual_penerima_lanjutkan_tab mempunyai 60 juta data Medan pertanyaan pertanyaan lambat termasuk tarikh_mula, id_tesen, status reka bentuk indeks tetapi sebenarnya gagal Ya:

driver_id

Indeks Kesatuan

Medan 1

Medan 2<.>

Field 3

联合索引	字段1	字段2	字段3
idx_date_station_driver	start_date	station_id	driver_id

idx_date_station_driver

start_date

station_id

Di mana proses pelaksanaan pertanyaan syarat

Memahami cara Mysql melaksanakan di mana pertanyaan keadaan boleh memberikan cerapan yang lebih pantas dan jelas tentang sebab kegagalan indeks. Indeks dengan tahap padanan yang tinggi dalam pertanyaan perlahan ini ialah idx_date_station_driver Analisis proses pelaksanaan pertanyaan keadaan di mana dalam pertanyaan perlahan ini.

Mysql di mana peraturan pengekstrakan keadaan boleh diringkaskan terutamanya kepada tiga kategori: Kunci Indeks (Kunci Pertama & Kunci Terakhir), Penapis Indeks, Penapis Jadual .

Kunci Indeks

Kunci Indeks digunakan untuk menentukan julat pertanyaan sql ini pada pepohon indeks. Julat termasuk permulaan dan akhir Kunci Pertama Indeks digunakan untuk mencari julat permulaan pertanyaan indeks, dan Kunci Akhir Indeks digunakan untuk mencari julat akhir pertanyaan indeks.

• Kunci Pertama Indeks

  Peraturan pengekstrakan: Bermula dari medan pertama indeks, semak sama ada medan itu wujud dalam keadaan dimana, jika wujud dan keadaannya =, >=, kemudian tambahkan syarat yang sepadan dengan Kunci Pertama Indeks, dan teruskan membaca medan indeks seterusnya jika ia wujud dan syaratnya ialah >, tambahkan syarat yang sepadan dengan Kunci Pertama Indeks, dan kemudian tamatkan Indeks Pertama; Kunci.

• Kunci Terakhir Indeks

  hanyalah bertentangan dengan Kunci Pertama Indeks Peraturan pengekstrakan ialah: bermula dari medan pertama indeks, semak sama ada ia wujud dalam di mana keadaan. Jika Jika wujud dan syaratnya ialah =,

Menurut peraturan pengekstrakan Kunci Indeks, Kunci Terakhir Indeks yang diekstrak dalam pertanyaan perlahan ini ialah: tarikh_mula>'1628442000', dan Kunci Terakhir Indeks ialah: tarikh_mula

Kunci Pertama Indeks hanya digunakan untuk mencari julat permulaan indeks Menggunakan keadaan Kunci Pertama Indeks, bermula dari nod akar pokok indeks B, gunakan kaedah carian binari untuk mengindeks dengan cepat kepada yang betul. kedudukan nod daun. Semasa proses pertanyaan Where, Indeks Pertama Kunci hanya dinilai sekali.

Kunci Terakhir Indeks digunakan untuk mencari julat pengakhiran indeks Oleh itu, untuk setiap rekod indeks yang dibaca selepas julat permulaan, adalah perlu untuk menentukan sama ada ia telah melebihi julat Kunci Terakhir Indeks melebihi, semasa Pertanyaan tamat.

Penapis Indeks

Dalam julat indeks yang ditentukan oleh Kunci Indeks, tidak semua rekod indeks memenuhi syarat pertanyaan. Sebagai contoh, dalam julat Kunci Terakhir Indeks dan Kunci Terakhir Indeks, tidak semua rekod indeks memenuhi station_id = '1809'. Pada masa ini anda perlu menggunakan Penapis Indeks.

Penapis Indeks, juga dikenali sebagai tekan bawah indeks, digunakan untuk menapis rekod dalam julat pertanyaan indeks yang tidak memenuhi syarat pertanyaan . Bagi setiap rekod dalam julat indeks, ia perlu dibandingkan dengan Penapis Indeks Jika ia tidak memenuhi Penapis Indeks, ia akan dibuang terus dan terus membaca rekod seterusnya dalam indeks.

Peraturan pengekstrakan Penapis Indeks: bermula dari medan pertama indeks, semak sama ada ia wujud dalam keadaan di mana Jika ia wujud dan syaratnya hanya =, langkau medan pertama dan terus semak yang seterusnya medan indeks , lajur indeks seterusnya menggunakan peraturan pengekstrakan yang sama (penjelasan: medan dengan syarat = telah ditapis keluar dalam Kunci Indeks jika wujud dan syaratnya ialah >=, >,

Mengikut peraturan pengekstrakan Penapis Indeks, Penapis Indeks yang diekstrak dalam pertanyaan perlahan ini ialah: station_id='1809'. Dalam julat pertanyaan indeks yang ditentukan oleh Kunci Indeks, station_id='1809' perlu dibandingkan semasa merentasi rekod indeks Jika syarat ini tidak dipenuhi, ia akan hilang secara langsung dan rekod seterusnya dalam indeks akan terus dibaca .

Penapis Jadual

Penapis Jadual digunakan untuk menapis data yang tidak boleh ditapis oleh indeks. Selepas keseluruhan baris rekod disoal dalam indeks sekunder melalui kunci utama dan dikembalikan ke jadual , ia dinilai sama ada rekod itu memenuhi syarat Penapis Jadual Jika ia tidak memenuhi syarat, ia akan hilang dan rekod seterusnya akan terus dinilai.

Peraturan pengekstrakan adalah sangat mudah: semua syarat pertanyaan yang tidak tergolong dalam medan indeks diklasifikasikan ke dalam Penapis Jadual. Menurut peraturan pengekstrakan Penapis Jadual, Penapis Jadual dalam pertanyaan ini ialah: status=‘2’.

Ringkasan dan tambahan

Kunci Indeks digunakan untuk menentukan skop imbasan indeks digunakan untuk menapis dalam indeks Penapis Jadual perlu dikembalikan ke jadual pada pelayan Mysql untuk ditapis.

Kunci Indeks dan Penapis Indeks berlaku pada lapisan storan InnoDB, dan Penapis Jadual berlaku pada lapisan Pelayan Mysql.

Sebelum MySQL5.6, tiada perbezaan antara Penapis Indeks dan Penapis Jadual Semua rekod indeks dalam julat Kunci Pertama Indeks dan Kunci Terakhir Indeks dikembalikan ke jadual untuk membaca rekod lengkap, dan kemudian dikembalikan. ke lapisan Pelayan MySQL untuk diproses.

Dalam MySQL 5.6 dan lebih baru, Penapis Indeks dipisahkan daripada Penapis Jadual Penapis Indeks jatuh ke lapisan enjin storan InnoDB untuk penapisan, yang mengurangkan overhed interaksi jadual pemulangan dan mengembalikan rekod ke lapisan Pelayan MySQL. dan meningkatkan kecekapan pelaksanaan SQL.

Analisis punca kegagalan indeks

Yang pertama ialah count( Pada masa ini, wildcard * tidak akan mengembangkan semua lajur selepas pengoptimuman, dan sebenarnya akan mengabaikan semua Lajur mengira bilangan baris secara langsung. Jadi jika anda hanya ingin mengumpul bilangan baris, sebaiknya gunakan count().

Seterusnya, analisa pernyataan where. Andaikan bahawa pertanyaan perlahan ini akan menggunakan indeks sekunder idx_date_station_driver Mengikut proses pelaksanaan pertanyaan keadaan where di atas, Kunci Indeks Pertama pertanyaan perlahan ialah start_date>'1628442000', dan Kunci Terakhir Indeks ialah: start_date

Selepas mengekstrak Kunci Pertama Indeks, mencari julat permulaan indeks pada pepohon indeks B ialah proses pemadanan indeks Gunakan kaedah carian binari pada pepohon indeks B untuk mencari dengan cepat julat permulaan yang memenuhi syarat pertanyaan. Melalui proses pelaksanaan pertanyaan keadaan Where di atas, kita tahu bahawa keadaan where bagi pertanyaan perlahan （start_date>'1628442000' and start_date hanya sepadan dengan medan pertama indeks <code>idx_date_station_driver(start_date, station_id, driver_id), iaitu hanya idx_date_station_driver(start_date) yang dipadankan ' disoal dengan tepat dan Ia tidak menjejaskan indeks yang sepadan, tetapi memainkan peranan dalam Penapis Indeks, iaitu, proses tolak ke bawah indeks. Apa yang sebenarnya berlaku di sini ialah pertanyaan julat menyebabkan indeks kesatuan berhenti memadankan .

Pertanyaan julat menyebabkan indeks kesatuan berhenti padanan

Mengapa pertanyaan julat menyebabkan indeks kesatuan berhenti padanan? Ini melibatkan prinsip padanan awalan paling kiri. Dengan mengandaikan bahawa indeks indeks bersama (a, b) ditubuhkan, a akan diisih dahulu, dan jika a adalah sama, b akan diisih, seperti yang ditunjukkan dalam rajah di bawah. Pada pokok indeks ini, a dipesan secara global, manakala b berada dalam keadaan tidak tertib global dan tertib tempatan. Dari perspektif global, nilai b ialah 1, 2, 1, 4, 1, 2, dan hanya b=2 syarat pertanyaan tidak boleh menggunakan indeks ini secara langsung dari perspektif setempat, apabila nilai a ditentukan, b ialah dalam status pesanan, a=2 && b=4 boleh menggunakan indeks ini. Oleh itu, sebab asas mengapa pertanyaan julat menyebabkan indeks bersama berhenti padanan ialah keadaan tertib medan bukan pertama pada pokok indeks bergantung pada kesamaan medan sebelumnya dan pertanyaan julat memusnahkan setempat. keadaan terperintah medan indeks seterusnya Menyebabkan indeks berhenti padanan.

Pertanyaan julat menghentikan indeks bersama daripada padanan dan tidak boleh menapis keluar data yang station_idnya tidak sama dengan '1809' apabila indeks sepadan, menghasilkan julat pengimbasan MySQL pada indeks. Kunci Pertama Indeks dan Kunci Terakhir Indeks ditentukan sepenuhnya mengikut masa mula_masa_tarikh. Pertanyaan julat start_timestamp_of_date boleh menapis keluar 73% daripada volum data, manakala pertanyaan tepat station_id='1809' boleh menapis keluar 99% daripada volum data.

查询条件	数据量	占比
所有数据	6367万	100%
start_timestamp_of_date>'1628442000' and start_timestamp_of_date	1742万	27.35%
station_id='1809'	8万	0.16%

Overhed operasi pemulangan jadual

Memandangkan medan status tiada pada medan indeks idx_date_station_driver, adalah perlu untuk mengembalikan jadual untuk menanyakan data yang ditapis oleh indeks, dan tentukan sama ada data memenuhi pertanyaan pada keadaan lapisan perkhidmatan Mysql.

Pengoptimum MySQL akan terlebih dahulu menganggarkan kos pengindeksan dengan tahap padanan yang tinggi apabila melaksanakan penyata sql Jika kos pengindeksan lebih besar daripada mencari keseluruhan jadual, maka Mysql akan memilih imbasan jadual penuh. Kesimpulan ini mungkin berlawanan dengan intuitif Dalam tanggapan kami, indeks digunakan untuk meningkatkan kecekapan pertanyaan. Terdapat terutamanya dua faktor yang terlibat di sini:

• Apabila keadaan pertanyaan atau medan yang dicari tidak berada pada medan indeks sekunder, operasi pemulangan jadual akan dilakukan dan perkara berikut langkah akan diambil: indeks sekunder indeks kunci utama.

• Prestasi I/O rawak cakera adalah lebih rendah daripada I/O berjujukan. Pertanyaan pemulangan jadual ialah I/O rawak pada indeks kunci utama, dan imbasan jadual penuh ialah I/O berurutan pada indeks kunci utama.

Adakah percubaan untuk menganalisis sama ada kos operasi pemulangan jadual adalah punca langsung kegagalan indeks?

Alih keluar keadaan pertanyaan status='0' dan terangkan untuk melihat sama ada pertanyaan menggunakan indeks idx_date_station_driver. Hasilnya adalah seperti yang ditunjukkan dalam rajah di bawah Overhed operasi pemulangan jadual dikurangkan, dan indeks tidak menjadi tidak sah.

Ringkasan

Digabungkan dengan analisis di atas, sebab kegagalan indeks diringkaskan: pertanyaan julat menyebabkan indeks bersama untuk menghentikan pemadanan, dan indeks sepadan dengan data yang ditapis Tidak mencukupi, menyebabkan pengoptimum MySQL menganggarkan bahawa kos operasi pulangan jadual bagi Penapis Jadual adalah lebih besar daripada pertanyaan jadual penuh, jadi pertanyaan jadual penuh dipilih. Pertanyaan julat yang menyebabkan indeks bersama berhenti padanan adalah punca kegagalan indeks, dan kos operasi pemulangan jadual adalah punca langsung kegagalan indeks.

Indeks Optimumkan

Penyebab kegagalan indeks pertanyaan perlahan ialah pertanyaan julat menyebabkan indeks bersama berhenti padanan Anda hanya perlu melaraskan medan pertanyaan julat ke medan pertanyaan yang tepat Kemudian,

indeks bersama idx_date_station_driver(start_date, station_id, driver_id) akan diubah suai kepada idx_station_date_driver(station_id_id)_driver(date_id_driver)_ . Keputusan yang dioptimumkan ditunjukkan dalam rajah di bawah.

Peluasan

Senario biasa kegagalan indeks

• Melanggar prinsip padanan awalan paling kiri. Sebagai contoh, terdapat indeks indeks(a,b), tetapi syarat pertanyaan hanya mempunyai medan b.

• Lakukan sebarang operasi pada lajur indeks, termasuk pengiraan, fungsi, penukaran jenis, dsb.

• Pertanyaan julat menyebabkan indeks kesatuan berhenti padanan.

• Kurangkan penggunaan pilih*. Untuk mengelakkan overhed operasi pemulangan jadual yang tidak perlu, cuba gunakan indeks penutup.

• Gunakan tidak sama dengan (!=, ), penggunaan atau operasi.

• Indeks rentetan tanpa petikan tunggal adalah tidak sah.

• suka bermula dengan aksara kad bebas '�c'. Ambil perhatian bahawa seperti 'abc%' boleh diindeks.

• perintah mengikut melanggar prinsip padanan paling kiri dan termasuk pengisihan medan bukan indeks, yang akan mengakibatkan pengisihan fail.

• kumpulan mengikut melanggar prinsip padanan paling kiri dan mengandungi kumpulan medan bukan indeks, yang akan menghasilkan penjanaan jadual sementara.

Jelaskan analisis

Analisis pertanyaan lambat tidak dapat dipisahkan daripada penyataan explain mysql yang tertumpu terutamanya pada dua medan Jenis dan Tambahan.

Jenis mewakili cara untuk mengakses data, dan Tambahan mewakili cara untuk menapis dan menyusun data. Disenaraikan di sini untuk carian mudah.

Type		Extra
ALL	全表扫描	Using index	使用覆盖索引，不需要回表，不需要Mysql服务层过滤
index	索引树全扫描	Using where	从存储引擎层获取数据，在Mysql服务层用where查询条件过滤数据。
range	索引树范围扫描	Using where; Using index	索引范围扫描。索引扫描和全表扫描类似，只是发生的层面不一样。
ref	非唯一性索引扫描，比如非唯一索引和唯一索引的非唯一前缀	Using index condition	使用索引下推，在存储引擎层充分利用查询索引字段过滤数据
eq_ref	唯一性索引扫描，比如唯一索引、主键索引	Using temporary	临时表存储结果，用于排序和分组查询
const	将查询转化成常量	Using filesort	文件排序，用于排序
NULL	不用访问表或索引	NULL	回表

Untuk lebih banyak pengetahuan berkaitan pengaturcaraan, sila lawati: Pengenalan kepada Pengaturcaraan! !

Atas ialah kandungan terperinci Apakah yang perlu saya lakukan jika indeks Mysql gagal? Analisis ringkas sebab kegagalan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!