Kuasai sepenuhnya Mysql explain

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan isu-isu yang berkaitan dengan menerangkan dalam Mysql boleh dipanggil artifak analisis pengoptimuman prestasi Mysql pelaksanaan pernyataan SQL, saya harap ia akan membantu semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

Pengoptimuman prestasi pangkalan data ialah salah satu kemahiran asas yang mesti ada pada setiap pengaturcara back-end , dan terangkan dalam Mysql boleh dipanggil artifak analisis pengoptimuman prestasi Mysql Kita boleh menggunakannya untuk menganalisis bagaimana pelan pelaksanaan yang sepadan bagi pernyataan SQL dilaksanakan di bahagian bawah Mysql. Adalah berguna untuk kita menilai kecekapan pelaksanaan SQL dan tentukan prestasi Mysql Arah pengoptimuman adalah sangat penting. Walau bagaimanapun, ramai pelajar masih keliru tentang cara menjalankan analisis pelaksanaan yang mendalam terhadap SQL sedia ada berdasarkan explain.

Terangkan asas

Untuk setiap SQL, apabila ia dihantar ke pelayan Mysql oleh pelanggan, ia akan dianalisis oleh komponen pengoptimum Mysql, terutamanya termasuk beberapa pemprosesan khas, Urutan pelaksanaan ialah diubah untuk memastikan kecekapan pelaksanaan yang optimum, dan pelan pelaksanaan yang sepadan akhirnya dihasilkan. Pelan pelaksanaan yang dipanggil sebenarnya merujuk kepada cara mendapatkan data di peringkat enjin storan Sama ada untuk mendapatkan data melalui indeks atau imbasan jadual penuh, sama ada perlu mengembalikan jadual selepas mendapatkan data, dsb. Pemahaman mudah. ialah proses mendapatkan data dalam Mysql.

Seterusnya, mari kita lihat dengan lebih dekat apakah penjelasan ini dan sebab ia boleh membimbing kita dalam pengoptimuman prestasi. Apabila kita melaksanakan pernyataan berikut:

explain SELECT * FROM user_info where NAME='mufeng'

Selepas melaksanakan pernyataan explain, kita akan mendapat hasil pelaksanaan berikut 12 medan yang serupa dengan jadual pangkalan data ini sebenarnya merupakan penerangan terperinci tentang pelan pelaksanaan yang dilaksanakan oleh Mysql. . Mari kita lihat dengan lebih dekat apa maksud 12 medan ini Hanya dengan memahami maksudnya kita boleh memahami cara Mysql melaksanakan pertanyaan data.

1.

Malah, setiap pertanyaan pilihan akan sepadan dengan id. . Ia mewakili susunan pelaksanaan SQL Semakin besar nilai id, semakin tinggi keutamaan pelaksanaan pernyataan SQL yang sepadan. Beberapa pernyataan SQL pertanyaan kompleks selalunya mengandungi beberapa subquery, maka nombor siri id akan ditambah Jika terdapat pertanyaan bersarang, kita boleh mendapati bahawa pertanyaan paling dalam sepadan dengan id terbesar, jadi ia akan dilaksanakan terlebih dahulu.

Seperti yang ditunjukkan dalam rajah di atas, dalam pernyataan pertanyaan SQL, id pelan pelaksanaan pertama ialah 1, id pelan pelaksanaan kedua ialah 2, dan pelaksanaan dengan id 1 Jadual yang sepadan dengan pelan adalah susunan, dan jadual yang sepadan dengan pelan pelaksanaan dengan id 2 ialah user_info Digabungkan dengan pernyataan SQL, kita tahu bahawa id pilih subquery daripada user_info dilaksanakan terlebih dahulu, dan kemudian data. pertanyaan tentang susunan jadual dilaksanakan.

2. select_type

select_type menunjukkan jenis pertanyaan yang sepadan dengan jenis pertanyaan biasa terutamanya termasuk pertanyaan biasa, pertanyaan bersama dan subquery. SIMPLE (pernyataan pertanyaan ialah pertanyaan ringkas dan tidak mengandungi subkueri), PRIMER (apabila pernyataan pertanyaan mengandungi subkueri, ia sepadan dengan jenis pertanyaan paling luar), UNION (jenis pertanyaan sepadan dengan pernyataan pilih yang muncul selepas kesatuan akan menandakan jenis ini), SUBQUERY (subquery akan ditandakan sebagai jenis ini), DEPENDENT SUBQUERY (bergantung pada pertanyaan luar)

3, table

jadual mewakili nama jadual dan menunjukkan jadual yang hendak ditanya. Sudah tentu, ia tidak semestinya nama jadual sebenar, ia mungkin alias jadual atau jadual sementara.

4. partition

partition mewakili konsep partition, yang bermaksud bahawa apabila membuat pertanyaan, jika jadual yang sepadan akan dibahagikan, Maka maklumat partition tertentu akan dipaparkan di sini.

5. Jenis

Jenis ialah atribut teras yang perlu dikuasai. Ia menunjukkan kaedah semasa mengakses jadual pangkalan data.

(1) sistem

Jadual hanya mempunyai satu baris (bersamaan dengan jadual sistem), jumlah data adalah kecil, dan kelajuan pertanyaan sangat pantas Sistem adalah kes khas jenis const.

(2) const

Jika jenis adalah const, ini bermakna kunci utama atau indeks unik dipukul semasa pertanyaan data jenis ini adalah sangat pantas.

(3) eq_ref

Semasa proses pertanyaan data, jika pernyataan SQL boleh berdasarkan indeks berkelompok atau bukan nol dalam kes itu sambungan jadual Indeks unik nilai mengingati imbasan data, maka nilai yang sepadan dengan jenis akan dipaparkan sebagai eq_ref.

(4) ref

Semasa pertanyaan data, jika indeks hit ialah indeks sekunder dan bukan indeks unik, kelajuan pertanyaan ujian akan menjadi sangat pantas, tetapi jenisnya ialah ref. Di samping itu, jika ia ialah indeks sendi berbilang medan, maka mengikut prinsip padanan paling kiri, perbandingan kesamaan medan dalam berbilang lajur berturut-turut bermula dari bahagian paling kiri indeks sendi juga merupakan jenis rujukan.

(5) ref_or_null

Jenis sambungan ini serupa dengan ref, kecuali MySQL juga akan mencari baris yang mengandungi nilai NULL.

(7) unique_subquery

Set keadaan subquery tentang dalam dalam keadaan dimana

(8) index_subquery

berbeza daripada unique_subquery dan digunakan Indeks bukan unik, boleh mengembalikan nilai pendua.

Julat (9)

Gunakan indeks untuk mendapatkan semula data baris dan hanya mendapatkan data baris dalam julat yang ditentukan. Dalam erti kata lain, ia adalah untuk mendapatkan semula data dalam julat yang ditentukan untuk medan yang diindeks. Apabila menggunakan antara...dan, <, >, <=, dalam dan jenis pertanyaan bersyarat lain dalam pernyataan where, jenisnya ialah julat.

(10) indeks

Indeks dan SEMUA sebenarnya membaca keseluruhan jadual adalah indeks dibaca dengan merentasi pokok indeks, manakala SEMUA adalah baca dari cakera keras baca masuk.

(11) semua

merentasi seluruh jadual untuk pemadanan data Prestasi pertanyaan data pada masa ini adalah yang paling teruk.

6. possible_keys

menunjukkan indeks mana yang boleh dipilih oleh pengoptimum Mysql, iaitu calon indeks yang ada.

7. kekunci

Indeks sebenarnya dipilih dalam kekunci_mungkin

8 > menunjukkan panjang indeks, yang berkaitan dengan atribut medan sebenar dan sama ada ia adalah nol.

9 ref

Apabila menggunakan medan untuk pertanyaan setara yang berterusan, rujuk di sini ialah const, apabila digunakan dalam keadaan pertanyaan Jika ada tiada ungkapan atau fungsi, ref akan dipaparkan sebagai func, dan yang lain akan dipaparkan sebagai null.

10 baris

Lajur baris menunjukkan bilangan baris yang MySQL fikir ia mesti semak semasa melaksanakan pertanyaan. Semakin sedikit baris, semakin cekap!

11. ditapis

ditapis Ini ialah nilai peratusan, peratusan bilangan rekod dalam jadual yang memenuhi syarat. Ringkasnya, medan ini mewakili perkadaran baki rekod yang memenuhi syarat selepas menapis data yang dikembalikan oleh enjin storan.

12 tambahan

Maklumat tambahan tidak akan dipaparkan dalam lajur lain tetapi akan dipaparkan dalam lajur ini.

(1) Menggunakan indeks

Apabila melakukan pertanyaan data, pangkalan data menggunakan indeks penutup, iaitu, lajur pertanyaan diliputi oleh indeks Menggunakan pertanyaan indeks penutup akan menjadi sangat pantas . Daripada menggunakan pilih *, gunakan nombor_telefon pilih, yang akan menggunakan indeks penutup.

(2) Menggunakan where

Tiada indeks tersedia ditemui semasa pertanyaan, dan kemudian data yang diperlukan diperoleh melalui penapisan keadaan di mana, tetapi ia sepatutnya ambil perhatian bahawa Tidak semua pertanyaan dengan pernyataan where akan dipaparkan Menggunakan where.

(3) Menggunakan sementara

menunjukkan bahawa hasil pertanyaan perlu disimpan dalam jadual sementara, yang biasanya digunakan semasa mengisih atau mengumpulkan pertanyaan.

(4) Menggunakan filesort

Jenis ini bermakna indeks tidak boleh digunakan untuk melengkapkan operasi pengisihan yang ditentukan, iaitu medan ORDER BY sebenarnya tidak mempunyai indeks, jadi jenis ini SQL perlu dioptimumkan.

exlpain分析实战

上文中我们阐述了explain在分析SQL语句时，可以通过12个属性来分析SQL的大致执行过程，并以此来判断SQL存在的性能问题。那么接下来我们通过一个实际的例子，来具体看下如何结合explain来实现SQL的性能分析。

其实所谓的Mysql性能问题，大部分都指的是平台出现了慢查询问题。慢查询实际上是可以通过配置进行记录的，把执行时间超过某个设定的阈值的sql都记录下来，当出现问题的时候可以通过记录的慢查询日志进行问题的定位。但是有的时候，出现大量慢查询会导致数据库连接给占满，导致整个平台的出现异常。

实际上我们在产品评价表product_evaluation中是建立了索引的，正常来说应该是可以使用到对应的索引字段进行查询的。但是实际上查询耗时有几十秒的时间，远远超过我们的预期。那我们猜测是不是由于某种原因导致Mysql优化器没有选择对应的索引进行数据检索，最后造成慢查询的发生。到底执行计划是怎样的，还是得借助于explain来看下。

如上文所说，虽然explain有12个字段属性帮助我们进行执行计划的分析，但是实际上常用的核心字段也就几个。我们可以看的出来在possible_key中实际上包含了我们设置的索引的，但是实际上Mysql却选择了PRIMARY作为其实际使用的。那么问题来了，为什么明明设置了索引，但是实际并没有用上，呗Mysql吃了吗？另外为什么之前的业务中没有出现这个问题，而现在出现了？我们需要进行进一步的分析。

我们所建立的idx_evaluation_type实际上是一个二级索引（叶子节点是主键id），对于数千万一张的大表来说，实际上这个二级索引也是非常大的，而且这个字段本身的值就三个，变化不大。因此Mysql的优化器在分析这个SQL的时候发现，如果按照SQL中的索引来获取数据后再根据where条件进行筛选，筛选后的数据还需要回表到聚簇索引中获取实际的数据。

假如通过二级索引筛选出来的数据有几万条，而后还需要进行排序，这些操作都是基于临时磁盘我恩建进行的，Mysql判断这种方式的性能可能会很差，因此优化器放弃了原有的数据查询方式，直接通过主键id对应的聚簇索引来进行数据的获取，因为id本身就是有序的。

那么知道了查询慢的原因，我们应该怎么进行优化呢？实际上可以在SQL语句中增加force idnex，强制Mysql使用我们设置的二级索引。

SELECT * FROM product_evaluation force index(idx_product_id)WHERE product_id =1 and evaluation_type='GOOD'  ORDER BY id desc LIMIT 200

总结

通过上文对于explain使用的介绍，大家在遇到慢SQL问题的时候，可以先通过explain来进行初步的分析，主要明确SQL在Mysql中实际的执行过程是怎样的，如果查询字段没有索引则增加索引，如果有索引就要分析为什么没有用到索引。只要明确具体的执行过程，我们才能确定具体的查询优化方案。

推荐学习：mysql视频教程

Atas ialah kandungan terperinci Kuasai sepenuhnya Mysql explain. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!