Ringkasan super terperinci tentang kemahiran praktikal pengoptimuman mysql

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql terutamanya meringkaskan dua puluh satu kemahiran praktikal untuk pengoptimuman mysql ini.

Pembelajaran yang disyorkan: tutorial video mysql

Hari ini, operasi pangkalan data semakin menjadi prestasi keseluruhan aplikasi kesesakan, yang amat ketara untuk aplikasi Web. Mengenai prestasi pangkalan data, ini bukan sahaja perkara yang perlu dibimbangkan oleh DBA, tetapi ini adalah sesuatu yang perlu diberi perhatian oleh pengaturcara. Apabila kita mereka bentuk struktur jadual pangkalan data dan mengendalikan pangkalan data (terutamanya pernyataan SQL apabila mencari jadual), kita perlu memberi perhatian kepada prestasi operasi data. Di sini, kami tidak akan bercakap terlalu banyak tentang pengoptimuman pernyataan SQL, tetapi hanya untuk MySQL, pangkalan data yang paling biasa digunakan di Web. Saya harap petua pengoptimuman berikut berguna kepada anda

1 Optimumkan pertanyaan anda untuk cache pertanyaan

Kebanyakan pelayan MySQL telah mendayakan caching pertanyaan. Ini adalah salah satu cara paling berkesan untuk meningkatkan prestasi, dan ia dikendalikan oleh enjin pangkalan data MySQL. Apabila banyak pertanyaan yang sama dilaksanakan berbilang kali, hasil pertanyaan akan diletakkan dalam cache, supaya pertanyaan serupa berikutnya tidak perlu mengendalikan jadual tetapi mengakses terus hasil cache.
Masalah utama di sini ialah bagi pengaturcara, perkara ini mudah terlepas pandang. Kerana beberapa pernyataan pertanyaan kami akan menyebabkan MySQL tidak menggunakan cache . Sila lihat contoh berikut:

Perbezaan antara dua pernyataan SQL di atas ialah CURDATE(), cache pertanyaan MySQL tidak berfungsi untuk fungsi ini. Oleh itu, fungsi SQL seperti NOW() dan RAND() atau fungsi lain seperti itu tidak akan mendayakan caching pertanyaan, kerana pulangan fungsi ini tidak menentu. Jadi, apa yang anda perlukan ialah menggantikan fungsi MySQL dengan pembolehubah untuk membolehkan caching.

2. JELASKAN pertanyaan PILIH anda

Menggunakan kata kunci EXPLAIN boleh memberitahu anda bagaimana MySQL memproses pernyataan SQL anda . Ini boleh membantu anda menganalisis kesesakan prestasi penyata pertanyaan atau struktur jadual anda. Hasil pertanyaan
EXPLAIN juga akan memberitahu anda cara kunci utama indeks anda digunakan, cara jadual data anda dicari dan diisih...dsb., dsb.
Pilih salah satu daripada SELECT pernyataan anda (disyorkan untuk memilih yang paling kompleks dengan gabungan berbilang jadual) dan tambahkan kata kunci EXPLAIN ke hadapan. Anda boleh menggunakan phpmyadmin untuk melakukan ini. Kemudian, anda akan melihat satu borang. Dalam contoh berikut, kami terlupa untuk menambah indeks group_id, dan terdapat gabungan jadual:

Selepas kami menambah indeks pada medan group_id:

Kita dapat melihat bahawa hasil sebelumnya menunjukkan bahawa 7883 baris telah dicari, manakala yang terakhir hanya mencari 9 dan 16 baris daripada dua jadual. Melihat pada lajur baris membolehkan kami mencari isu prestasi yang berpotensi.

3. Gunakan LIMIT 1 1 apabila terdapat hanya satu baris data

Apabila anda menanyakan jadual, anda sudah tahu bahawa hanya akan ada satu hasil, tetapi kerana anda mungkin perlu fetch kursor, atau anda mungkin menyemak bilangan rekod yang dikembalikan.
Dalam kes ini, menambah LIMIT 1 boleh meningkatkan prestasi. Dengan cara ini, enjin pangkalan data MySQL akan berhenti mencari selepas mencari sekeping data, dan bukannya terus mencari sekeping data seterusnya yang sepadan dengan rekod.
Contoh berikut hanyalah untuk mencari sama ada terdapat pengguna "China" Jelas sekali, yang terakhir akan lebih cekap daripada yang pertama. (Sila ambil perhatian bahawa yang pertama ialah Select * dan yang kedua ialah Select 1)

Indeks medan carian

Indeks tidak semestinya untuk kunci utama atau satu-satunya medan. Jika terdapat medan dalam jadual anda yang anda akan sentiasa gunakan untuk carian, sila indekskannya

Daripada gambar di atas, anda boleh melihat rentetan carian “last_name LIKE ‘a%’”. Selain itu, anda juga perlu mengetahui jenis carian yang tidak boleh menggunakan pengindeksan biasa. Contohnya, apabila anda perlu mencari perkataan dalam artikel besar, seperti: "'", indeks mungkin tidak bermakna. Anda mungkin perlu menggunakan indeks teks penuh MySQL atau membuat indeks sendiri (contohnya: cari kata kunci atau teg)
WHERE post_content LIKE ‘%apple%

5 Gunakan jenis contoh yang sama semasa menyertai jadual

Jika aplikasi anda mempunyai banyak pertanyaan JOIN, anda harus memastikan bahawa medan Sertai dalam kedua-dua jadual diindeks. Dengan cara ini, MySQL akan memulakan mekanisme secara dalaman untuk mengoptimumkan pernyataan Join SQL untuk anda.

Selain itu, medan yang digunakan untuk Sertai ini hendaklah daripada jenis yang sama. Contohnya: jika anda meletakkan medan

dengan medan INT
bersama-sama, MySQL tidak boleh menggunakan indeksnya. Untuk jenis DECIMAL tersebut, mereka juga perlu mempunyai set aksara yang sama. (Set aksara kedua-dua jadual mungkin berbeza)JoinSTRING

6 Jangan sekali-kali ORDER OLEH RAND()

Berebut yang dikembalikan. baris data? Pilih sekeping data secara rawak? Saya benar-benar tidak tahu siapa yang mencipta penggunaan ini, tetapi ramai orang baru suka menggunakannya dengan cara ini. Tetapi anda benar-benar tidak memahami masalah prestasi yang teruk ini.

Jika anda benar-benar mahu mengocok baris data yang dikembalikan, anda mempunyai N cara untuk mencapai ini. Menggunakan ini hanya akan menyebabkan prestasi pangkalan data anda menurun secara eksponen. Masalahnya di sini ialah:

MySQL perlu melaksanakan fungsi
(yang menggunakan masa CPU), dan ini adalah untuk merekodkan baris bagi setiap baris rekod, dan kemudian mengisihnya. Walaupun anda menggunakan , ia tidak akan membantu (kerana anda perlu mengisih) RAND()Limit 1

Contoh berikut memilih rekod secara rawak:

7. Elakkan PILIH *

Semakin banyak data dibaca daripada pangkalan data, semakin perlahan pertanyaan itu. Selain itu, jika pelayan pangkalan data dan pelayan WEB anda adalah dua pelayan bebas, ini juga akan meningkatkan beban penghantaran rangkaian. Oleh itu, anda harus membina tabiat yang baik untuk

mengambil apa sahaja yang anda perlukan

.

8 Sentiasa tetapkan ID untuk setiap jadual

Kita harus menetapkan ID untuk setiap jadual dalam pangkalan data sebagai kunci utamanya. dan yang terbaik ialah jenis

(

disyorkan), dan ditetapkan dengan bendera INT ditambah secara automatik. UNSIGNED Walaupun jadual pengguna anda mempunyai medan dengan kunci utama yang dipanggil "AUTO_INCREMENT", jangan jadikan ia sebagai kunci utama. Menggunakan jenis
sebagai kunci utama akan merendahkan prestasi. Selain itu, dalam program anda, anda harus menggunakan ID jadual untuk membina struktur data anda. email Selain itu, di bawah enjin data MySQL, terdapat beberapa operasi yang memerlukan penggunaan kunci utama Dalam kes ini, VARCHARPrestasi dan tetapan kunci utama menjadi sangat penting, seperti kelompok, sekatan... <.>
Di sini, hanya terdapat satu pengecualian, iaitu "kunci asing" bagi "jadual bersekutu". Maksudnya, kunci utama jadual ini terdiri daripada kunci utama beberapa jadual individu. Kami memanggil keadaan ini sebagai "kunci asing". Contohnya: terdapat "jadual pelajar" dengan ID pelajar, dan "jadual kurikulum" dengan ID kursus Kemudian, "jadual gred" ialah "jadual persatuan", yang mengaitkan jadual pelajar dan jadual kursus jadual, ID pelajar dan ID kursus dipanggil "kunci asing" dan bersama-sama ia membentuk kunci utama.
9. Gunakan ENUM dan bukannya VARCHAR

Jenis ENUM sangat pantas dan padat. Malah, ia menjimatkan

, tetapi ia muncul sebagai rentetan. Dengan cara ini, ia menjadi agak sempurna untuk menggunakan medan ini untuk membuat beberapa senarai pilihan.

Jika anda mempunyai bidang, seperti "jantina", "negara", "etnik", "status" atau "jabatan", dan anda tahu bahawa nilai medan ini terhad dan tetap, maka anda harus gunakan

bukannya TINYINT.
MySQL juga mempunyai "cadangan" (lihat item 10) untuk memberitahu anda cara menyusun semula struktur jadual anda. Apabila anda mempunyai medan ENUM, cadangan ini akan memberitahu anda untuk menukarnya kepada jenis VARCHAR. Menggunakan
anda boleh mendapatkan cadangan yang berkaitan VARCHAR

10 Dapatkan cadangan daripada PROCEDURE ANALYSE()

PROCEDURE ANALYSE() akan membenarkan MySQL membantu anda menganalisis medan anda dan data sebenar mereka, dan memberi anda beberapa cadangan berguna. Cadangan ini hanya akan berguna jika terdapat data sebenar dalam jadual, kerana membuat beberapa keputusan besar memerlukan data sebagai asas.
Contohnya, jika anda mencipta medan INT sebagai kunci utama anda, tetapi tidak banyak data, maka PROCEDURE ANALYSE() akan mencadangkan anda menukar jenis medan ini kepada MEDIUMINT . Atau jika anda menggunakan medan VARCHAR, kerana data tidak banyak, anda mungkin mendapat cadangan untuk menukarnya daripada
kepada ENUM. Cadangan ini semuanya mungkin kerana data tidak mencukupi, jadi pembuatan keputusan tidak cukup tepat.
Dalam phpmyadmin, anda boleh mengklik “Propose table structure” untuk melihat cadangan ini semasa melihat jadual. . Cadangan ini hanya akan menjadi tepat kerana jadual anda mengandungi lebih banyak data. Pastikan anda ingat,

Anda adalah orang yang membuat keputusan muktamad

11 Gunakan NOT NULL sebanyak mungkin

Melainkan anda mempunyai yang sangat istimewa. sebab Untuk menggunakan nilai , anda harus sentiasa menyimpan medan anda

. Ini mungkin kelihatan agak kontroversi, sila baca.

Mula-mula, tanya diri anda apakah perbezaan antara "

" dan "

" (jika

, itu NULL0 dan NULLNOT NULL) jika anda rasa ada perbezaan antara mereka Tiada bezanya, maka anda tidak menggunakan
. (Tahukah anda? Dalam Oracle, rentetan EmptyNULL dan INT adalah sama !) NULL Jangan fikir tidak memerlukan ruang, ia memerlukan ruang tambahan NULL, dan apabila anda membuat perbandingan, program anda akan menjadi lebih kompleks. Sudah tentu, ini tidak bermakna anda tidak boleh menggunakan Empty Realitinya sangat rumit, dan masih terdapat situasi di mana anda perlu menggunakan nilai NULL.
12.Penyata DisediakanNULLNULL

adalah seperti prosedur tersimpan Ia adalah koleksi pernyataan SQL yang berjalan di latar belakang daripada menggunakan

Faedah, sama ada

isu prestasi atau isu keselamatan

.

Anda boleh menyemak beberapa pembolehubah yang telah anda terikat, yang boleh Prepared Statementsmelindungi program anda daripada serangan "SQL injection"prepared statements. Sudah tentu, anda juga boleh menyemak pembolehubah anda secara manual Walau bagaimanapun, semakan manual terdedah kepada masalah dan sering dilupakan oleh pengaturcara. Masalah ini akan menjadi lebih baik apabila kita menggunakan beberapa atau .
Dari segi prestasi, apabila pertanyaan yang sama digunakan beberapa kali, ini akan membawa anda kelebihan prestasi yang besar. Anda boleh menentukan beberapa parameter untuk Prepared Statements ini dan MySQL hanya akan menghuraikannya sekali. Walaupun versi terbaru MySQL menggunakan bentuk binariframework semasa menghantar ORM, jadi ini akan
menjadikan penghantaran rangkaian sangat cekapPrepared Statements.
Sudah tentu, terdapat beberapa kes di mana kita perlu mengelak daripada menggunakan Prepared Statements kerana ia tidak menyokong caching pertanyaan . Tetapi ia dikatakan akan disokong selepas versi 5.1. Untuk menggunakan pernyataan yang disediakan dalam PHP, anda boleh menyemak manualnya: sambungan mysql atau gunakan lapisan abstraksi pangkalan data, seperti: PDO.
Prepared Statements
13 . Pertanyaan yang tidak ditimbal

Dalam keadaan biasa, apabila anda melaksanakan pernyataan SQL dalam skrip anda, program anda akan berhenti di sana sehingga pernyataan SQL dikembalikan Kemudian program anda terus dilaksanakan. Anda boleh menggunakan pertanyaan tidak buffer untuk mengubah tingkah laku ini.

menghantar pernyataan SQL ke MySQL tanpa

secara automatik dan menyimpan hasil carian seperti yang dilakukan

secara automatik. Ini akan menjimatkan banyak memori, terutamanya untuk pertanyaan yang menjana sejumlah besar hasil, dan anda tidak perlu menunggu semua keputusan dikembalikan Anda hanya perlu mengembalikan baris pertama data dan anda boleh mula berfungsi serta-merta.

Walau bagaimanapun, ini disertakan dengan beberapa had. Kerana anda sama ada perlu membaca semua baris, atau anda perlu memanggil

untuk mengosongkan keputusan sebelum pertanyaan seterusnya. Selain itu,
tidak akan berfungsi. Oleh itu, anda perlu mempertimbangkan dengan teliti sama ada hendak menggunakan pertanyaan tidak buffer. mysql_unbuffered_query()

14. Simpan alamat IP sebagai UNSIGNED INT

Ramai pengaturcara akan mencipta medan VARCHAR(15) untuk menyimpan IP dalam bentuk rentetan dan bukannya IP integer. Jika anda menggunakan integer untuk menyimpannya, ia hanya mengambil masa 4 bait dan anda boleh mempunyai medan panjang tetap. Selain itu, ini akan memberi anda kelebihan pertanyaan, terutamanya apabila anda perlu menggunakan WHERE keadaan seperti ini: IP between ip1 and ip2.
Kita mesti menggunakan UNSIGNED INT, kerana alamat IP menggunakan keseluruhan integer tidak bertanda 32-bit.
Untuk pertanyaan anda, anda boleh menggunakan INET_ATON() untuk menukar IP rentetan kepada integer dan gunakan INET_NTOA() untuk menukar integer kepada IP rentetan. Dalam PHP, terdapat juga fungsi sedemikian ip2long() 和 long2ip().

15 Jadual panjang tetap akan menjadi lebih pantas

Jika semua medan dalam jadual adalah "panjang tetap", keseluruhan jadual akan Dianggap sebagai "static" atau "fixed-length". Contohnya, tiada medan jenis berikut dalam jadual: VARCHAR，TEXT. Selagi anda memasukkan salah satu medan ini, jadual itu bukan lagi "jadual statik panjang tetap" dan enjin MySQL akan memprosesnya dengan cara lain.
Jadual panjang tetap akan meningkatkan prestasi kerana MySQL akan mencari dengan lebih pantas Kerana panjang tetap ini memudahkan pengiraan mengimbangi data seterusnya, bacaan secara semula jadi akan menjadi lebih pantas. Dan jika medan itu bukan panjang tetap, maka setiap kali anda ingin mencari yang seterusnya, program perlu mencari kunci utama.
Selain itu, jadual panjang tetap lebih mudah untuk dicache dan dibina semula. Walau bagaimanapun, satu-satunya kesan sampingan ialah medan panjang tetap akan membazirkan sedikit ruang , kerana medan panjang tetap akan memperuntukkan begitu banyak ruang sama ada anda menggunakannya atau tidak.

Menggunakan teknologi "vertical split" (lihat item seterusnya), anda boleh membahagikan jadual anda kepada dua, satu dengan panjang tetap dan satu dengan panjang berubah daripada.

16. Pembahagian menegak

"Pecahan menegak" ialah kaedah menukar jadual dalam pangkalan data kepada beberapa jadual mengikut lajur, yang boleh mengurangkan kerumitan dan medan nombor jadual , supaya mencapai tujuan pengoptimuman. (Saya pernah membuat projek di bank dan melihat jadual dengan lebih daripada 100 medan, yang menakutkan)

Contoh 1: Terdapat medan dalam jadual Pengguna iaitu alamat rumah . Ini Medan adalah medan pilihan, sebagai perbandingan, dan kecuali untuk maklumat peribadi apabila anda beroperasi dalam pangkalan data, anda tidak perlu membaca atau menulis semula medan ini dengan kerap. Jadi, mengapa tidak meletakkannya dalam jadual lain? Ini akan menjadikan jadual anda mempunyai prestasi yang lebih baik, sering kali, untuk jadual pengguna, saya hanya mempunyai ID pengguna, nama pengguna dan kata laluan. dan lain-lain akan digunakan dengan kerap. Jam tangan yang lebih kecil akan sentiasa mempunyai prestasi yang lebih baik
.

Contoh 2: Anda mempunyai medan yang dipanggil "last_login" yang akan dikemas kini setiap kali pengguna log masuk. Walau bagaimanapun, setiap kemas kini akan menyebabkan cache pertanyaan jadual dikosongkan. Oleh itu, anda boleh meletakkan medan ini dalam jadual lain, supaya ia tidak menjejaskan pembacaan berterusan ID pengguna, nama pengguna dan peranan pengguna anda, kerana cache pertanyaan akan membantu anda meningkatkan banyak prestasi.

Selain itu, anda perlu memberi perhatian kepada fakta bahawa anda tidak akan kerap menyertai jadual yang dibentuk oleh medan yang dipisahkan ini, jika tidak, prestasi akan menjadi lebih teruk daripada tanpa pemisahan ia akan turun secara eksponen

17 Pisahkan penyataan PADAM atau INSERT yang besar

Jika anda perlu melaksanakan penyataan besar pada tapak web dalam talian DELETE atau INSERT pertanyaan, anda. perlu berhati-hati untuk mengelakkan operasi anda menyebabkan seluruh tapak web anda berhenti bertindak balas. Kerana kedua-dua operasi ini akan mengunci jadual, setelah jadual dikunci, tiada operasi lain boleh masuk.
Apache akan mempunyai banyak proses atau utas anak. Oleh itu, ia berfungsi dengan agak cekap, dan pelayan kami tidak mahu mempunyai terlalu banyak proses kanak-kanak, utas dan pautan pangkalan data Ini memerlukan banyak sumber pelayan, terutamanya memori.
Jika anda mengunci jadual anda untuk satu tempoh masa, seperti 30 saat, maka untuk tapak dengan volum trafik yang tinggi, bilangan proses/benang akses, pautan pangkalan data dan fail terbuka yang terkumpul dalam 30 saat ini, ia mungkin bukan sahaja menyebabkan anda menghentikan perkhidmatan WEB Crash, tetapi juga boleh menyebabkan seluruh pelayan anda ditutup serta-merta.
Jadi, jika anda mempunyai proses yang besar dan anda pasti akan membahagikannya, menggunakan syarat LIMIT adalah cara yang baik untuk melakukannya. Berikut ialah contoh:

18. Lajur yang lebih kecil akan menjadi lebih pantas

Bagi kebanyakan enjin pangkalan data, operasi cakera keras mungkin merupakan kesesakan yang paling ketara. Jadi, menjadikan data anda padat boleh sangat membantu dalam situasi ini kerana ia mengurangkan akses kepada cakera keras.
Lihat dokumentasi MySQL Storage Requirements untuk melihat semua jenis data.
Jika jadual hanya mempunyai beberapa lajur (seperti jadual kamus, jadual konfigurasi), maka kami tidak mempunyai sebab untuk menggunakan INT sebagai kunci utama Ia akan menjadi lebih menjimatkan untuk menggunakan MEDIUMINT, SMALLINT atau TINYINT yang lebih kecil . Jika anda tidak perlu menjejaki masa, lebih baik menggunakan DATE berbanding DATETIME.
Sudah tentu, anda juga perlu meninggalkan ruang yang cukup untuk pengembangan Jika tidak, jika anda melakukan ini pada masa hadapan, anda akan mati dengan buruk. Lihat Slashdot untuk contoh (6 November 2009), ALTER TABLE yang mudah. 🎜> Kenyataan itu mengambil masa lebih 3 jam kerana terdapat 16 juta keping data di dalamnya.

19 Pilih enjin storan yang betul

Terdapat dua enjin storan dalam MySQL, MyISAM dan InnoDB, dan setiap enjin mempunyai kebaikan dan keburukan. Artikel Cool Shell sebelum ini "MySQL: InnoDB atau MyISAM membincangkan perkara ini?"
MyISAM sesuai untuk sesetengah aplikasi yang memerlukan bilangan pertanyaan yang banyak, tetapi ia tidak begitu baik untuk operasi tulis yang banyak. Walaupun anda hanya perlu mengemas kini medan, keseluruhan jadual akan dikunci dan proses lain, malah proses membaca, tidak boleh beroperasi sehingga operasi membaca selesai. Selain itu, MyISAM sangat pantas untuk pengiraan seperti SELECT COUNT(*).
Trend InnoDB akan menjadi enjin storan yang sangat kompleks Untuk beberapa aplikasi kecil, ia akan menjadi lebih perlahan daripada MyISAM. Sebab lain ialah ia menyokong "penguncian baris", jadi ia akan menjadi lebih baik apabila terdapat lebih banyak operasi tulis. Selain itu, ia juga menyokong aplikasi yang lebih maju, seperti transaksi.

20 Gunakan Pemeta Perkaitan Objek

Menggunakan ORM (Object Relational Mapper), anda boleh mendapatkan keuntungan prestasi yang boleh dipercayai. Semua yang boleh dilakukan oleh ORM juga boleh ditulis secara manual. Walau bagaimanapun, ini memerlukan pakar peringkat tinggi.
Perkara yang paling penting tentang ORM ialah "Lazy Loading", iaitu, ia hanya akan melakukannya apabila perlu untuk mendapatkan nilai. Tetapi anda juga perlu berhati-hati tentang kesan sampingan mekanisme ini, kerana ia berkemungkinan mengurangkan prestasi dengan mencipta banyak, banyak pertanyaan kecil.
ORM juga boleh membungkus penyata SQL anda ke dalam transaksi, yang jauh lebih pantas daripada melaksanakannya secara individu.
Pada masa ini, ORM PHP kegemaran peribadi saya ialah: Doctrine

21 Berhati-hati dengan "pautan kekal"

Tujuan "pautan kekal" ialah. untuk digunakan Untuk mengurangkan bilangan penciptaan semula sambungan MySQL. Apabila pautan dibuat, ia kekal bersambung selama-lamanya, walaupun selepas operasi pangkalan data telah tamat. Selain itu, sejak Apache kami mula menggunakan semula proses anak - iaitu, permintaan HTTP seterusnya akan menggunakan semula proses anak Apache dan menggunakan semula sambungan MySQL yang sama.
Secara teori, ini kedengaran hebat. Tetapi dari pengalaman peribadi (dan kebanyakan orang), ciri ini menimbulkan lebih banyak masalah. Kerana, anda hanya mempunyai bilangan pautan yang terhad, isu memori, pemegang fail, dsb.
Selain itu, Apache berjalan dalam persekitaran yang sangat selari dan akan mencipta banyak, banyak proses. Inilah sebabnya mekanisme "pautan kekal" ini tidak berfungsi dengan baik. Sebelum anda membuat keputusan untuk menggunakan "pautan kekal", anda perlu berhati-hati mempertimbangkan seni bina keseluruhan sistem anda

22 pengoptimuman indeks pengoptimuman SQL

Lajur bebas

Apabila membuat pertanyaan, lajur indeks tidak boleh menjadi sebahagian daripada ungkapan atau parameter fungsi, jika tidak, indeks tidak boleh digunakan.
Contohnya, pertanyaan berikut tidak boleh menggunakan indeks lajur actor_id:

#这是错误的SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

Kaedah pengoptimuman: Ungkapan dan operasi fungsi boleh dialihkan ke sebelah kanan tanda sama. Seperti berikut:

SELECT actor_id FROM sakila.actor WHERE actor_id  = 5 - 1;

2. Indeks berbilang lajur

Apabila berbilang lajur perlu digunakan sebagai syarat untuk pertanyaan, menggunakan indeks berbilang lajur mempunyai prestasi yang lebih baik daripada menggunakan berbilang lajur tunggal indeks.
Sebagai contoh, dalam pernyataan berikut, sebaiknya tetapkan actor_id dan film_id sebagai indeks berbilang lajur. Yuanfudao ada soalan, lihat pautan untuk butiran, yang boleh membantu anda memahami dengan lebih mendalam.

SELECT film_id, actor_ id FROM sakila.film_actorWHERE actor_id = 1 AND film_id = 1;

3. Susunan lajur indeks

Biar lajur indeks yang paling terpilih diletakkan dahulu.
Pemilihan indeks merujuk kepada nisbah nilai indeks unik kepada jumlah rekod. Nilai maksimum ialah 1, di mana setiap rekod mempunyai indeks unik yang sepadan dengannya. Semakin tinggi selektiviti, semakin tinggi diskriminasi setiap rekod dan semakin tinggi kecekapan pertanyaan.

Sebagai contoh, dalam hasil yang ditunjukkan di bawah, customer_id lebih selektif daripada staff_id, jadi sebaiknya letakkan lajur customer_id di hadapan indeks berbilang lajur.

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

#结果如下
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

4. Indeks awalan

Untuk lajur jenis BLOB、TEXT 和 VARCHAR, indeks awalan mesti digunakan untuk mengindeks aksara permulaan sahaja.

Pemilihan panjang awalan perlu ditentukan berdasarkan pemilihan indeks.

5.覆盖索引

索引包含所有需要查询的字段的值。具有以下优点：

1.索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
2.一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
3.对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

6.优先使用索引，避免全表扫描

mysql在使用like进行模糊查询的时候把%放后面，避免开头模糊查询
因为mysql在使用like查询的时候只有使用后面的%时，才会使用到索引。

如：’%ptd_’ 和 ‘%ptd_%’ 都没有用到索引；而 ‘ptd_%’ 使用了索引。

#进行全表查询，没有用到索引
EXPLAIN SELECT * FROM `user` WHERE username LIKE '%ptd_%';
EXPLAIN SELECT * FROM `user` WHERE username LIKE '%ptd_';

#有用到索引
EXPLAIN SELECT * FROM `user` WHERE username LIKE 'ptd_%';

再比如：经常用到的查询数据库中姓张的所有人：

SELECT * FROM `user` WHERE username LIKE '张%';

7.尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。

比如：

SELECT * FROM t WHERE id IN (2,3)SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

8.尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描

如：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1UNIONSELECT * FROM t WHERE id = 3

9.尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

10.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

同第1个，单独的列；

SELECT * FROM t2 WHERE score/10 = 9SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9SELECT * FROM t2 WHERE username LIKE 'li%'

11.当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描

SELECT * FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。
索引的好处：建立索引后，查询时不会扫描全表，而会查询索引表锁定结果。
索引的缺点：在数据库进行DML操作的时候，除了维护数据表之外，还需要维护索引表，运维成本增加。
应用场景：数据量比较大，查询字段较多的情况。

索引规则：

1.选用选择性高的字段作为索引，一般unique的选择性最高；
2.复合索引：选择性越高的排在越前面。（左前缀原则）；
3.如果查询条件中两个条件都是选择性高的，最好都建索引；

23.SQL优化之查询优化

1.使用Explain进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有：

select_type : 查询类型，有简单查询、联合查询、子查询等；
key : 使用的索引；
rows : 扫描的行数；

2.优化数据访问

1.减少请求的数据量

只返回必要的列：最好不要使用 SELECT * 语句。
只返回必要的行：使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2.减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

3.重构查询方式

1.切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

2.分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

Jadikan caching lebih cekap: Untuk pertanyaan penyertaan, jika salah satu jadual berubah, keseluruhan cache pertanyaan tidak boleh digunakan. Untuk berbilang pertanyaan selepas penguraian, walaupun satu jadual berubah, cache pertanyaan untuk jadual lain masih boleh digunakan.
Uraikannya kepada berbilang pertanyaan jadual tunggal Hasil cache bagi pertanyaan jadual tunggal ini lebih berkemungkinan digunakan oleh pertanyaan lain, dengan itu mengurangkan pertanyaan rekod berlebihan.
Kurangkan pertikaian kunci;
Menyambung pada lapisan aplikasi menjadikannya lebih mudah untuk memisahkan pangkalan data, menjadikannya lebih mudah untuk mencapai prestasi tinggi dan berskala.
Kecekapan pertanyaan itu sendiri juga boleh dipertingkatkan. Contohnya, dalam contoh berikut, menggunakan IN() dan bukannya pertanyaan sertai membolehkan MySQL membuat pertanyaan dalam tertib ID, yang mungkin lebih cekap daripada gabungan rawak.

SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

24.分析查询语句

通过对查询语句的分析，可以了解查询语句执行的情况，找出查询语句执行的瓶颈，从而优化查询语句。mysql中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。
EXPLAIN语句的基本语法如下：
EXPLAIN [EXTENDED] SELECT select_options;
使用EXTENED关键字,EXPLAIN语句将产生附加信息。select_options是select语句的查询选项，包括from where子句等等。
执行该语句，可以分析EXPLAIN后面的select语句的执行情况，并且能够分析出所查询的表的一些特征。
例如：EXPLAIN SELECT * FROM user;
查询结果进行解释说明：
a、id：select识别符，这是select的查询序列号。
b、select_type：标识select语句的类型。
它可以是以下几种取值：
b1、SIMPLE（simple）表示简单查询，其中不包括连接查询和子查询。
b2、PRIMARY（primary）表示主查询，或者是最外层的查询语句。
b3、UNION（union）表示连接查询的第2个或者后面的查询语句。
b4、DEPENDENT UNION（dependent union）连接查询中的第2个或者后面的select语句。取决于外面的查询。
b5、UNION RESULT（union result）连接查询的结果。
b6、SUBQUERY（subquery）子查询的第1个select语句。
b7、DEPENDENT SUBQUERY（dependent subquery）子查询的第1个select,取决于外面的查询。
b8、DERIVED（derived）导出表的SELECT(FROM子句的子查询)。
c、table：表示查询的表。
d、type：表示表的连接类型。
下面按照从最佳类型到最差类型的顺序给出各种连接类型。
d1、system，该表是仅有一行的系统表。这是const连接类型的一个特例。
d2、const，数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const表查询速度很快，因为它们只读一次。const用于使用常数值比较primary key或者unique索引的所有部分的场合。
例如：EXPLAIN SELECT * FROM user WHERE id=1;
d3、eq_ref，对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE或者PRIMARY KEY时候，即可使用这种类型。eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或者一个在该表前面所读取的表的列的表达式。
例如：EXPLAIN SELECT * FROM user,db_company WHERE user.company_id = db_company.id;
d4、ref对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于所以既不是UNION也不是primaey key的情况，或者查询中使用了索引列的左子集，即索引中左边的部分组合。ref可以用于使用=或者操作符的带索引的列。
d5、ref_or_null，该连接类型如果ref，但是如果添加了mysql可以专门搜索包含null值的行，在解决子查询中经常使用该连接类型的优化。
d6、index_merge，该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
d7、unique_subquery，该类型替换了下面形式的in子查询的ref。是一个索引查询函数，可以完全替代子查询，效率更高。
d8、index_subquery，该连接类型类似于unique_subquery，可以替换in子查询，但是只适合下列形式的子查询中非唯一索引。
d9、range，只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。key_len包含所使用索引的最长关键元素。当使用=，,>,>=,，between或者in操作符，用常量比较关键字列时，类型为range。
d10、index,该连接类型与all相同，除了只扫描索引树。着通常比all快，引文索引问价通常比数据文件小。
d11、all，对于前面的表的任意行组合，进行完整的表扫描。如果表是第一个没有标记const的表，这样不好，并且在其他情况下很差。通常可以增加更多的索引来避免使用all连接。
e. possible_keys: Lajur possible_keys menunjukkan indeks mysql yang boleh digunakan untuk mencari baris dalam jadual. Jika lajur adalah batal, tiada indeks yang berkaitan. Dalam kes ini, anda boleh meningkatkan prestasi pertanyaan dengan menyemak klausa where untuk melihat sama ada ia menyebabkan lajur atau lajur tertentu yang sesuai untuk pengindeksan. Jika ya, anda boleh mencipta indeks yang sesuai untuk meningkatkan prestasi pertanyaan.
f, key: Menunjukkan indeks yang sebenarnya digunakan dalam pertanyaan Jika tiada indeks dipilih, nilai lajur ini adalah batal Untuk memaksa mysql menggunakan atau mengabaikan indeks dalam lajur possible_key, gunakan force index、use index或者ignore index dalam pertanyaan.
g, key_len: Menunjukkan panjang medan indeks pilihan MySQL yang dikira dalam bait Jika kuncinya nol, panjangnya adalah null. Ambil perhatian bahawa nilai key_len menentukan bilangan medan dalam indeks berbilang lajur mysql sebenarnya akan digunakan.
h, ref: Menunjukkan lajur, pemalar atau indeks yang hendak digunakan untuk menanyakan rekod.
i, rows: Memaparkan bilangan baris yang MySQL mesti semak semasa membuat pertanyaan dalam jadual.
j, Extra: Maklumat terperinci lajur ini apabila mysql memproses pertanyaan.

Pembelajaran yang disyorkan: tutorial video mysql

Atas ialah kandungan terperinci Ringkasan super terperinci tentang kemahiran praktikal pengoptimuman mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!