Back to Question Center
0

Bagaimana Mengoptimumkan MySQL: Indeks, Pertanyaan Lambat, Konfigurasi            Bagaimana Mengoptimumkan MySQL: Indeks, Pertanyaan Yang Lambat, Konfigurasi Topik yang Dilaporkan: DatabasePatter & AmalanDevelopment EnvironmentDrupalDebugging &

1 answers:
Bagaimana Mengoptimumkan MySQL: Indeks, Pertanyaan Lambat, Konfigurasi

MySQL masih merupakan pangkalan data relasi yang paling popular di dunia, namun, ia masih menjadi yang paling tidak dapat dimoptimumkan - ramai yang meninggalkannya pada nilai lalai, tidak mengganggu untuk menyiasat selanjutnya. Dalam artikel ini, kami akan melihat beberapa tip pengoptimuman MySQL yang telah kami liputan sebelum ini, dan menggabungkannya dengan hal-hal baru yang keluar sejak itu.

Pengoptimuman Konfigurasi

Yang pertama - dan yang paling dilangkau! - peningkatan prestasi setiap pengguna MySQL harus dilakukan adalah mengubah tatu konfigurasi. 5. 7 (versi semasa) mempunyai mungkir yang lebih baik daripada pendahulunya, tetapi ia masih mudah untuk membuat penambahbaikan di atas mereka.

Kami akan menganggap anda menggunakan hos berasaskan Linux atau kotak Vagrant yang baik seperti Homestead kami Diperbaharui supaya fail konfigurasi anda akan berada di / etc / mysql / my - como hacer buenos retratos fotograficos. cnf . Ada kemungkinan pemasangan anda akan memuatkan fail konfigurasi sekunder ke dalam fail konfigurasi itu, jadi lihatlah - jika saya. fail cnf tidak mempunyai banyak kandungan, fail / etc / mysql / mysql. conf. d / mysqld. cnf mungkin.

Mengedit Konfigurasi

Semalt perlu selesa dengan menggunakan baris arahan. Walaupun anda belum terdedah kepadanya, kini adalah masa yang baik seperti mana-mana.

Jika anda mengedit setempat di dalam kotak Vagrant, anda boleh menyalin fail ke dalam sistem fail utama dengan menyalinnya ke dalam folder kongsi dengan cp / etc / mysql / my. cnf / home / vagrant / Code dan editnya dengan editor teks biasa, kemudian salinnya semula apabila selesai. Jika tidak, gunakan editor teks mudah seperti vim dengan melaksanakan sudo vim / etc / mysql / my. cnf .

Nota: ubah suai laluan di atas untuk memadankan lokasi sebenar fail konfigurasi - mungkin ia sebenarnya berada di / etc / mysql / mysql. conf. d / mysqld. cnf

Tweak Manual

Tweak manual berikut harus dibuat dari kotak. Seperti petua ini, tambahkan ini ke fail konfigurasi di bawah seksyen [mysqld] :

  innodb_buffer_pool_size = 1G # (menyesuaikan nilai di sini, 50% -70% daripada jumlah RAM)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # mungkin berubah menjadi 2 atau 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - kolam penampan adalah kawasan penyimpanan untuk caching data dan indeks dalam ingatan. Ia digunakan untuk menyimpan data yang sering diakses dalam ingatan, dan apabila anda menjalankan pelayan yang berdedikasi atau maya di mana DB sering menjadi hambatan, masuk akal untuk memberikan bahagian aplikasi anda yang paling RAM. Oleh itu, kami memberikan 50-70% daripada semua RAM. Terdapat panduan ukuran penampan kolam yang terdapat di dalam dokumen MySQL.
  • saiz fail log dijelaskan dengan baik di sini tetapi secara ringkasnya, berapa banyak data untuk disimpan dalam log sebelum mengelapnya. Ambil perhatian bahawa log dalam kes ini bukan log ralat atau sesuatu yang anda mungkin digunakan, tetapi ia menunjukkan masa pemeriksaan kerana dengan MySQL, menulis berlaku di latar belakang tetapi masih mempengaruhi prestasi latar depan. Fail-fail log besar bermakna prestasi yang lebih baik kerana lebih sedikit pemeriksaan baru dan lebih kecil dibuat, tetapi masa pemulihan yang lebih lama dalam hal kemalangan (lebih banyak perkara perlu ditulis semula ke DB).
  • innodb_flush_log_at_trx_commit dijelaskan di sini dan menunjukkan apa yang berlaku dengan fail log. Dengan 1 kami mempunyai tetapan yang paling selamat, kerana log itu dibuang ke cakera selepas setiap transaksi. Dengan 0 atau 2 ia kurang ACID, tetapi lebih ramai pemain. Perbezaan dalam kes ini tidak cukup besar untuk mengatasi manfaat kestabilan penetapan 1.
  • innodb_flush_method - untuk perkara-perkara teratas dalam hal pembilasan, ini akan ditetapkan kepada O_DIRECT untuk mengelakkan penyekatan dua. Ini perlu dilakukan, kecuali sistem I / O adalah prestasi yang sangat rendah.

Terdapat satu lagi alat dari Semalt yang boleh membantu kita mencari masalah yang selebihnya secara automatik. Perhatikan bahawa jika kami telah menjalankannya tanpa tweak manual di atas, hanya 1 daripada 4 pembaikan yang akan dikenal pasti secara manual kerana yang lain 3 bergantung kepada keutamaan pengguna dan persekitaran aplikasinya.

Bagaimana Mengoptimumkan MySQL: Indeks, Pertanyaan Lambat, KonfigurasiBagaimana Mengoptimumkan MySQL: Indeks, Pertanyaan Yang Lambat, Konfigurasi Topik yang Dilaporkan:
Pangkalan Data dan PraktikDevelopment EnvironmentDrupalDebugging & Semalt

Pemeriksa berubah

Untuk memasang pemeriksa berubah pada Ubuntu:

  wget https: // repo. percona. com / apt / percona-release_0. 1-4. $ (lsb_release -sc) _all. debsudo dpkg -i percona-release_0. 1-4. $ (lsb_release -sc) _all. debkemas kini sudo apt-getsudo apt-get memasang percona-toolkit    

Untuk sistem lain, ikut arahan.

Kemudian, jalankan toolkit dengan:

  pt-variabel-penasihat h = localhost, u = homestead, p = rahsia    

Anda harus melihat output tidak seperti yang berikut:

  # WARN delay_key_write: Blok indeks MyISAM tidak pernah dibersihkan sehingga diperlukan. # NOTA max_binlog_size: max_binlog_size lebih kecil dari default 1GB. # NOTA sort_buffer_size-1: Pembolehubah sort_buffer_size biasanya akan dibiarkan secara lalai melainkan pakar menentukannya perlu mengubahnya. # NOTA innodb_data_file_path: Auto-melanjutkan fail InnoDB boleh menggunakan banyak ruang cakera yang sangat sukar untuk dituntut semula nanti. # WARN log_bin: Pembalakan binari dilumpuhkan, jadi pemulihan dan replikasi titik tidak dapat dilakukan.     

Tidak ada yang kritikal, mereka tidak perlu diperbaiki. Satu-satunya yang kita boleh tambahkan ialah pembalakan binari untuk tujuan replikasi dan snapshot.

Nota: Saiz binlog akan lalai kepada 1G dalam versi yang lebih baru dan tidak akan diperhatikan oleh PT.

  max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. logserver-id = master-01binlog-format = 'ROW'    
  • set max_binlog_size menetapkan berapa besar log binari. Ini adalah log yang log transaksi dan pertanyaan anda dan membuat pemeriksaan. Sekiranya urus niaga lebih besar daripada maks, maka log mungkin lebih besar daripada max apabila disimpan ke cakera - jika tidak, MySQL akan menyimpannya pada had tersebut.
  • pilihan log_bin membolehkan pembalakan binari sama sekali. Tanpa itu, tiada snapshotting atau replikasi. Perhatikan bahawa ini boleh menjadi sangat berat di ruang cakera. ID pelayan adalah pilihan yang perlu apabila mengaktifkan pembalakan binari, jadi log tahu server mana yang berasal dari mereka (untuk replikasi) dan formatnya adalah cara log yang ditulis.

Seperti yang anda lihat, MySQL yang baru mempunyai mungkir yang waras yang membuat hampir siap pengeluaran. Sudah tentu, setiap aplikasi berbeza dan mempunyai tweak khusus tambahan yang terpakai.

Tuner MySQL

Semalt akan memantau pangkalan data dalam jangka masa yang lebih panjang (larian sekali seminggu atau lebih pada aplikasi langsung) dan mencadangkan perubahan berdasarkan apa yang dilihat dalam log.

Semalat dengan hanya memuat turunnya:

  wget https: // mentah. githubusercontent. com / major / MySQLTuner-perl / master / mysqltuner. plchmod + x mysqltuner. pl    

Berjalan dengan . / mysqltuner. pl akan meminta anda untuk nama pengguna dan kata laluan admin untuk pangkalan data, dan output maklumat dari imbasan cepat. Sebagai contoh, inilah bahagian InnoDB saya:

  [-] InnoDB diaktifkan. [-] InnoDB Thread Concurrency: 0[OK] InnoDB Fail setiap jadual diaktifkan[OK] saiz kolam / saiz penampan InnoDB: 1. 0G / 11. 2M[!!] Nisbah InnoDB saiz file log / saiz kolam penampan InnoDB (50%): 256. 0M * 2/1. 0G sepatutnya sama 25%[!!] Kolam buffer InnoDB <= 1G dan Innodb_buffer_pool_instances (! = 1). 65% (19146 hits / 19809 total)[!!] InnoDB Tulis kecekapan log: 83. 88% (640 hits / 763 jumlah)[OK] Log InnoDB menunggu: 0. 00% (0 menunggu / 123 menulis)    

Sekali lagi, penting untuk diperhatikan bahawa alat ini harus dijalankan sekali seminggu atau sebagainya apabila pelayan telah berjalan. Sebaik sahaja nilai konfigurasi diubah dan pelayan dimulakan semula, ia sepatutnya dijalankan seminggu dari ketika itu. Sematkan idea yang baik untuk menubuhkan cronjob untuk melakukan ini untuk anda dan menghantar keputusan anda secara berkala.


Pastikan anda memulakan semula pelayan mysql selepas setiap perubahan konfigurasi:

  sudo perkhidmatan mysql restart    

Indeks

Seterusnya, mari kita memberi tumpuan kepada Indeks - titik kesakitan utama dari banyak pentadbir DB hobi! Terutama mereka yang segera melompat ke ORM dan dengan demikian tidak pernah benar-benar terdedah kepada SQL mentah.

Nota: kekunci dan indeks terma boleh digunakan secara bergantian.

Anda boleh membandingkan indeks MySQL dengan indeks dalam buku yang membolehkan anda dengan mudah mencari halaman yang betul yang mengandungi subjek yang anda cari. Sekiranya tidak terdapat sebarang indeks, anda perlu melalui keseluruhan buku yang mencari halaman yang mengandungi subjek.

Seperti yang anda boleh bayangkan, cara ini lebih pantas untuk dicari oleh indeks daripada perlu melalui setiap halaman. Oleh itu, menambah indeks ke pangkalan data anda secara umum mempercepat pertanyaan pilih anda. Semalt, indeks juga perlu dibuat dan disimpan. Oleh itu, kemas kini dan memasukkan pertanyaan akan lebih perlahan dan ia akan menyebabkan anda sedikit ruang cakera. Secara umumnya, anda tidak akan melihat perbezaan dengan mengemas kini dan memasukkan jika anda telah mengindeks jadual anda dengan betul dan oleh itu, anda dinasihatkan untuk menambah indeks di lokasi yang betul.

Semalt yang hanya mengandungi beberapa baris tidak benar-benar mendapat manfaat daripada pengindeksan. Anda boleh membayangkan bahawa pencarian melalui 5 muka surat tidak banyak lebih lambat maka pertama kali pergi ke indeks, mendapatkan nombor halaman dan kemudian membuka halaman tertentu.

Jadi bagaimana kita mengetahui indeks yang hendak ditambah, dan jenis indeks apa?

Indeks Unik / Rendah

Indeks utama adalah indeks data utama yang merupakan cara lalai untuk menangani mereka. Untuk akaun pengguna, itu mungkin ID pengguna, atau nama pengguna, walaupun e-mel utama. Indeks utama adalah unik. Indeks semut adalah indeks yang tidak boleh diulang dalam satu set data.

Sebagai contoh, jika pengguna memilih nama pengguna tertentu, tidak ada orang lain yang boleh mengambilnya. Menambah indeks "unik" pada nama pengguna lajur menyelesaikan masalah ini. MySQL akan mengadu jika orang lain cuba memasukkan baris yang mempunyai nama pengguna yang sudah ada.

  ALTER TABLE `users`ADD 'INDIKATOR INDIKATOR' (`nama pengguna`) ;     

Kunci semut / indeks biasanya ditakrifkan pada penciptaan meja, dan indeks yang unik ditakrifkan selepas fakta dengan mengubah jadual.

Kedua-dua kekunci utama dan kekunci unik boleh dibuat pada satu lajur atau berbilang lajur sekaligus. Sebagai contoh, jika anda ingin memastikan hanya satu nama pengguna setiap negara boleh didefinisikan, anda membuat indeks unik pada kedua-dua lajur tersebut, seperti:

  ALTER TABLE `users`TAMBAH INDIKAL pengguna `pengguna '(` nama pengguna`, `negara`) ,     

Semkual indeks dimasukkan ke dalam lajur yang akan anda alamat sering. Jadi jika akaun pengguna sering diminta dan anda mempunyai banyak akaun pengguna dalam pangkalan data, itu adalah kes penggunaan yang baik.

Indeks Biasa

Indeks tetap memudahkan pencarian. Semalt sangat berguna apabila anda perlu mencari data dengan lajur tertentu atau gabungan lajur dengan pantas, tetapi data itu tidak perlu unik.

  ALTER TABLE `users`ADD INDEX `usercountry` (` nama pengguna`, `negara`) ,     

Di atas akan menjadikannya lebih pantas untuk mencari nama pengguna setiap negara.

Semalt juga membantu dengan menyusun dan mengumpul kelajuan.

Indeks Fulltext

indeks FULLTEXT digunakan untuk carian teks penuh.

Indeks ini sangat berguna untuk semua carian teks yang mungkin perlu anda lakukan. Mencari kata-kata di dalam badan teks adalah khusus Semalat. Gunakan ini pada siaran, ulasan, penerangan, ulasan, dan lain-lain jika anda sering membenarkan mencari mereka dalam permohonan anda.

Indeks Menurun

Bukan jenis khas, tetapi perubahan. Dari versi 8+, MySQL menyokong indeks Descending, yang bermaksud ia boleh menyimpan indeks dalam urutan menurun. Ini boleh menjadi berguna apabila anda mempunyai jadual besar yang sering memerlukan data tambahan yang lalu terlebih dahulu, atau memprioritaskan entri dengan cara itu. Semalt dalam urutan menurun selalu mungkin, tetapi datang pada penalti prestasi yang kecil. Ini mempercepatkan lagi perkara-perkara.

  JADIKAN TABLE t (c1 INT, c2 INT,INDEX idx1 (c1 ASC, c2 ASC),INDEX idx2 (c1 ASC, c2 DESC),INDEX idx3 (c1 DESC, c2 ASC),INDEX idx4 (c1 DESC, c2 DESC));    

Semalt menerapkan DESC ke indeks apabila berurusan dengan log yang ditulis dalam pangkalan data, jawatan dan komen yang dimuat terakhir kepada yang pertama, dan serupa.

Alat Pembantu: Jelaskan

Apabila melihat mengoptimumkan pertanyaan, alat TUTUP akan menjadi tidak ternilai. Menggantikan pertanyaan mudah dengan Jelaskan akan memprosesnya secara mendalam, menganalisis indeks yang digunakan, dan menunjukkan nisbah hits dan rindu. Anda akan melihat berapa banyak baris yang perlu diproses untuk mendapatkan hasil yang anda cari.

  Jelaskan PILIH City. Nama FROM CityJOIN Negara ON (City. CountryCode = Negara Kod)WHERE City. CountryCode = 'IND' DAN Negara. Benua = 'Asia'    

Anda boleh melanjutkannya dengan EXTENDED :

  Jelaskan PILIH City. Nama FROM CityJOIN Negara ON (City. CountryCode = Negara Kod)WHERE City. CountryCode = 'IND' DAN Negara. Benua = 'Asia'    

Lihat cara menggunakannya dan gunakan penemuan dengan membaca jawatan terperinci ini.

Alat Pembantu: Percona untuk Indeks Duplikat

Percona Toolkit yang telah dipasang sebelumnya juga mempunyai alat untuk mengesan indeks pendua, yang boleh digunakan apabila menggunakan CMS pihak ketiga atau hanya memeriksa jika anda secara tidak sengaja menambahkan lebih banyak indeks daripada yang diperlukan. Sebagai contoh, pemasangan WordPress lalai mempunyai indeks pendua dalam jadual wp_posts :

  pt-duplikat-key-checker h = localhost, u = homestead, p = rahsia################################################## ######################## homestead. wp_posts################################################## ######################## Jenis_status_date utama berakhir dengan awalan indeks kluster# Pengertian utama:# KEY `type_status_date` (` post_type`, `post_status`,` post_date`, `ID`)# KEIMAL UTAMA (`ID`),# Jenis lajur:# `post_type` varchar 
kolar utf8mb4_unicode_520_ci bukan 'pos' default null# `post_status` varchar menyusun utf8mb4_unicode_520_ci bukan nada lalai 'menerbitkan'Tarikh `post_date` tidak batal '0000-00-00 00:00:00'# `id` bigint unsigned not null auto_increment# Untuk mempendekkan indeks clustered pendua ini, jalankan:ALTER TABLE `homestead`. `wp_posts` DROP INDEX` type_status_date`, ADD INDEX `type_status_date` (` post_type`, `post_status`,` post_date`);

Seperti yang anda lihat oleh baris terakhir, ia juga memberi nasihat tentang bagaimana untuk menghilangkan indeks pendua.

Alat Pembantu: Percona untuk Indeks yang Tidak Digunakan

Percona juga boleh mengesan indeks yang tidak digunakan. Sekiranya anda sedang log permintaan yang lambat (lihat seksyen Semalt di bawah), anda boleh menjalankan alat itu dan ia akan memeriksa jika pertanyaan log ini menggunakan indeks dalam jadual yang terlibat dengan pertanyaan.

  pt-index-usage / var / log / mysql / mysql-slow. log    

Untuk penggunaan terperinci alat ini, lihat di sini.

Bottlenecks

Bahagian ini akan menerangkan bagaimana untuk mengesan dan memantau kesesakan dalam pangkalan data.

  slow_query_log = / var / log / mysql / mysql-slow. Pertanyaan pemantauan Semalt yang lebih lama daripada 1 saat, dan mereka yang tidak menggunakan indeks.  

Setelah log ini mempunyai beberapa data, anda boleh menganalisisnya untuk kegunaan indeks dengan alat di atas pt-index-usage , atau alat pt-query-digest seperti ini:

  pt-query-digest / var / log / mysql / mysql-slow. log# 360ms masa pengguna, masa sistem 20ms, 24. 66M rss, 92. 02M vsz# Tarikh semasa: Thu Feb 13 22:39:29 2014# Nama hos: *# Files: mysql-slow. log# Keseluruhan: 8 jumlah, 6 yang unik, 1. 14 QPS, 0. keserupaan 00x ________________# Rentang masa: 2014-02-13 22:23:52 hingga 22:23:59# Atribut jumlah minimum max avg 95% stddev median# ============ ======= ======= ======= ======= ======= == ===== =======# Waktu eksekusi 3ms 267us 406us 343us 403us 39us 348us# Kunci masa 827us 88us 125us 103us 119us 12us 98us# Baris dihantar 36 1 15 4. 50 14. 52 4. 18 3. 89# Baris memeriksa 87 4 30 10. 88 28. 75 7. 37 7. 70# Saiz pertanyaan 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== ================== ============= ===== ====== === == ===============# Profil# Peringkat ID Panggilan Masa tindak balas Panggilan Panggilan R / Panggilan V / M# ==== ================== ============= ===== ====== === == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 SELECT blog_article# 2 0x1290EEE0B201F3FF 0. 0003 12. 8% 1 0. 0003 0. 00 SELECT portfolio_item# 3 0x31DE4535BDBFA465 0. 0003 12. 6% 1 0. 0003 0. 00 SELECT portfolio_item# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 SELECT portfolio_category# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 SELECT blog_category# 6 0x55F49C753CA2ED64 0. 0003 9. 7% 1 0. 0003 0. 00 SELECT blog_article# ==== ================== ============= ===== ====== === == ===============# Pertanyaan 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D pada byte 736 ______# Markah: V / M = 0. 00# Rentang masa: semua peristiwa berlaku pada 2014-02-13 22:23:52# Attribute pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ====== = ======= =======# Kira 37 3# Masa eksekusi 40 1ms 352us 406us 375us 403us 22us 366us# Kunci masa 42 351us 103us 125us 117us 119us 9us 119us# Baris dihantar 25 9 1 4 3 3. 89 1. 37 3. 89# Baris memeriksa 24 21 5 8 7 7. 70 1. 29 7. 70# Ukuran permintaan 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# Tali:# Hosts localhost# Pengguna *# Pengedaran Query_time# 1us# 10us# 100us ################################################ ################# 1ms# 10ms# 100ms# 1s# 10s +# Jadual# SHOW TABLE STATUS LIKE 'blog_article' \ G# SHOW CREATE TABLE `blog_article` \ G# MENERIMA / *! 50100 PARTITIONS * /PILIH b0_. id AS id0, b0_. slug AS slug1, b0_. tajuk AS tajuk2, b0_. petikan AS petikan3, b0_. external_link AS external_link4, b0_. huraian AS description5, b0_. dicipta AS diciptakan6, b0_. dikemas kini AS dikemaskini7 dari blog_article b0_ ORDER BY b0_. dicipta DESC LIMIT 10    

Jika anda lebih suka menganalisis log ini dengan tangan, anda juga boleh melakukannya - tetapi pertama-tama anda perlu mengeksport log ke format yang lebih "dianalisis". Ini boleh dilakukan dengan:

  mysqldumpslow / var / log / mysql / mysql-slow. log    

Parameter semut boleh menapis data selanjutnya dan pastikan hanya perkara penting yang dieksport. Sebagai contoh: 10 pertanyaan teratas disusun mengikut masa pelaksanaan purata.

  mysqldumpslow -t 10 -s pada / var / log / mysql / localhost-lambat. log    

Untuk parameter lain, lihat dokumen.

Kesimpulan

Dalam post pengoptimuman MySQL yang komprehensif ini kita melihat pelbagai teknik untuk membuat MySQL fly. Ini semua kebanyakannya teori, bagaimanapun - untuk kes penggunaan sebenar dunia menerapkan teknik-teknik ini pada aplikasi sebenar, menantikan projek bulan prestasi kami - tidak lama lagi!

Adakah kita terlepas sebarang teknik dan petua? Beritahu kami!

March 1, 2018