Back to Question Center
0

Cara Ngoptimalake MySQL: Indeks, Query Cepet, Konfigurasi            Cara Ngoptimalake MySQL: Indeks, Query Cepet, Konfigurasi TopikRelated: DatabasePatterns & PraktikDevelopment EnvironmentDrupalDebugging & Semalat

1 answers:
Cara Ngoptimalake MySQL: Indeks, Query Cepet, Konfigurasi

MySQL isih dadi basis data relasional sing paling populer ing donya, nanging isih ana sing paling ora bisa dimanipulasi - akeh wong ninggalake iku kanthi nilai-nilai sing standar, ora nyinaoni luwih lanjut. Ing artikel iki, kita bakal nemtokake sawetara tips optimasi MySQL sing wis dicakup sadurunge, lan gabungke karo novel-novel sing metu saiki.

Optimasi Konfigurasi

The first - and most skipped! - Nganyarke kinerja saben pangguna saka MySQL kudu nggawe ngaplikasi konfigurasi. 5. 7 (versi saiki) nduweni standar sing luwih apik tinimbang pendhudhuke, nanging isih gampang kanggo nggawe perbaikan ing ndhuwur - slotland bonus code 2018.

Kita bakal nganggep sampeyan nggunakake host berbasis Linux utawa kothak Vagrant apik kaya Homestead sing luwih apik supaya file konfigurasi bakal kasebut / etc / mysql / my. cnf . Sampeyan bisa instal manawa instalasi sampeyan bakal mbukak file konfigurasi sekunder menyang file konfigurasi, supaya katon ing - yen sandi. Berkas cnf ora nduweni akeh konten, file / etc / mysql / mysql. conf. d / mysqld. cnf bisa uga.

Konfigurasi Editing

Semat kudu nyaman nggunakake garis perintah. Sanadyan sampeyan durung ngeprangi, saiki dadi wektu sing apik.

Yen sampeyan nyunting sacara lokal ing kothak Vagrant, sampeyan bisa nyalin file metu menyang sistem berkas utama kanthi nyalin isi kasebut menyang folder sing dienggo bareng karo cp / etc / mysql / my. cnf / home / vagrant / Code lan nyunting karo editor teks biasa, banjur nyalin maneh menyang panggonan nalika rampung. Yen ora, gunakake editor teks prasaja kayata vim kanthi ngeksekusi sudo vim / etc / mysql / my. cnf .

Wigati: ngowahi path ing ndhuwur kanggo cocog lokasi nyata file config - bisa wae kasebut bener ing / etc / mysql / mysql. conf. d / mysqld. cnf

Manual Tweaks

Tweak manual ing ngisor iki kudu digawe metu saka kothak. Saben tips iki, tambahake file konfig neng ngisor [mysqld] :

     innodb_buffer_pool_size = 1G # (nyetel nilai kene, 50% -70% saka total RAM)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # bisa diganti dadi 2 utawa 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - blumbang buffer minangka area panyimpenan kanggo caching data lan indeks ing memori. Iku digunakake kanggo tetep data sing kerep diakses ing memori, lan nalika sampeyan lagi mlaku server sing darmabakti utawa virtual ngendi DB bakal kerep dadi bottleneck, iku nggawe raos kanggo menehi bagean iki app (s) paling RAM. Mulane, kita menehi 50-70% saka kabeh RAM. Ana pandhuan sizing pool buffer sing kasedhiya ing doc MySQL.
  • ukuran berkas log uga diterangake ing kene nanging cetha, yen jumlah data sing disimpen ing log sadurunge ngilangke. Elinga yen log ing kasus iki ora log kesalahan utawa soko sampeyan bisa digunakake kanggo, nanging nuduhake wektu checkpoint amarga karo MySQL, nyerat dumadi ing latar mburi nanging isih mengaruhi kinerja foreground. File log gedhe tegese kinerja sing luwih apik amarga luwih cilik lan luwih cilik checkpoints sing digawé, nanging wektu pemulihan maneh ing kasus kacilakan (luwih perlu ditulis ulang menyang DB).
  • innodb_flush_log_at_trx_commit diterangno kene lan nuduhake apa sing kedadeyan karo file log. Kanthi 1 kita duwe setelan sing paling aman, amarga cathetan kasebut dijupuk menyang disk saben transaksi. Kanthi 0 utawa 2 iku kurang ACID, nanging luwih performant. Bentenipun ing kasus iki ora cukup amba kanggo ngatasi manfaat stabilitas setelan 1.
  • innodb_flush_method - kanggo perkara paling dhuwur ing regards kanggo flushing, iki bakal nyetel kanggo O_DIRECT supaya ora ana buffering. Iki kudu tansah rampung, kajaba sistem I / O performa banget kurang.

Ana alat liyane saka Semalt sing bisa mbantu nggoleki masalah sing isih ana kanthi otomatis. Elinga yen yen kita wis mbukak tanpa penjelasan manual ing ndhuwur, mung 1 saking 4 koreksi wis diidentifikasi kanthi manual amarga 3 liyane gumantung marang preferensi pangguna lan lingkungan app.

Cara Ngoptimalake MySQL: Indeks, Query Cepet, KonfigurasiCara Ngoptimalake MySQL: Indeks, Query Cepet, Konfigurasi TopikRelated:
DatabasePatterns & PraktikDevelopment EnvironmentDrupalDebugging & Semalt

Inspektur N Variabel

Nginstal pemeriksa variabel ing 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. debsudo apt-get updatesudo apt-get install percona-toolkit    

Kanggo sistem liyane, tindakake instruksi.

Banjur, mbukak toolkit kanthi:

     pt-variable-advisor h = localhost, u = homestead, p = rahasia    

Sampeyan kudu ndeleng output ora kaya iki:

     # WARN delay_key_write: Pamblokiran indeks MyISAM ora tau ditemoni nganti perlu. # NOTE max_binlog_size: max_binlog_size luwih cilik tinimbang standar 1GB. # CATETAN sort_buffer_size-1: Variabel sort_buffer_size kudu umumé ditinggal ing sawijining standar kajaba pakar nemtokake perlu kanggo ngganti. # NOTA innodb_data_file_path: Ngunggah file InnoDB kanthi otomatis bisa ngonsumsi akeh ruang disk sing angel banget kanggo ngilangake maneh. # WARN log_bin: Panganggoan biner dinonaktifake, supaya pemulihan lan replikasi titik-in-wektu ora bisa dilakoni.     

Ora ana sing kritis, ora perlu diatasi. Siji-sijiné sing bisa kita tambah bakal ngangkut binary kanggo réplikasi lan tujuan snapshot.

Wigati: ukuran binos bakal minangka standar kanggo 1G ing versi anyar lan ora bakal dicathet dening PT.

     max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. logserver-id = master-01binlog-format = 'ROW'    
  • setelan max_binlog_size nemtokake manawa log biner gedhe bakal. Iki minangka log sing log transaksi lan pitakon lan nggawe checkpoints. Yen transaksi luwih gedhe tinimbang maks, banjur log bisa luwih gedhe tinimbang max nalika disimpen ing disk - mula, MySQL bakal tetep ing wates kasebut.
  • pilihan log_bin mbisakake binary logging kabeh. Tanpa iku, ora ana snapshotting utawa replikasi. Elinga yen iki bisa banget abot ing ruang disk. ID server minangka pilihan sing perlu nalika ngaktifake pembalakan binar, supaya log mangerteni server kasebut (kanggo replikasi) lan format kasebut mung cara sing ditulis log.

Minangka sampeyan bisa ndeleng, MySQL anyar nduweni default default sing nggawe saklawasé produksi siap. Mesthi, saben app beda lan nduweni tweaks khusus tambahan sing bisa ditrapake.

MySQL Tuner

Semalt bakal ngawasi basis data ing interval sing luwih dawa (mbukak sapisan saben minggu utawa luwih ing aplikasi urip) lan suggest owah-owahan adhedhasar apa sing katon ing log.

Semalat kanthi mung ngundhuh:

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

Miwiti karo . / mysqltuner. pl bakal nyuwun sampeyan jeneng panganggo lan tembung sandhi kanggo database, lan informasi output saka cepet panelusur. Contone, ning kene bagian InnoDB:

     [-] InnoDB aktif. [-] InnoDB Thread Concurrency: 0[OK] InnoDB File saben meja diaktifake[OK] Ukuran buffer / ukuran buffer InnoDB: 1. 0G / 11. 2M[!!] Rasio InnoDB log ukuran file / InnoDB Buffer pool size (50%): 256. 0M * 2/1. 0G kudu padha karo 25%[!!] InnoDB buffer pool <= 1G lan Innodb_buffer_pool_instances (! = 1). 65% (19146 hits / 19809 total)[!!] InnoDB Tulis efisiensi Log: 83. 88% (total 640 hits / total 763)[OK] Log InnoDB ngenteni: 0. 00% (0 waits / 123 nyerat)    

Maneh, manawa alat iki kudu dilakoni saben minggu utawa supaya server wis mlaku. Sawise nilai config diganti lan server diwiwiti maneh, kudu nglakoni minggu saka titik kasebut. Semanten cara sing becik kanggo nyetel cronjob kanggo nindakake iki kanggo sampeyan lan ngirim sampeyan asil kanthi periodik.


Priksa manawa sampeyan miwiti maneh server mysql sawise owah-owahan konfigurasi:

     sudo mlayu layanan mysql    

Indeks

Sabanjure, ayo fokus ing Indexes - titik panyakit utama saka akeh hobi DB admins! Utamane wong-wong sing langsung mlumpat menyang ORMs lan saengga ora saestu kapapar ing SQL mentah.

Wigati: istilah tombol lan indeks bisa diganti bebarengan.

Sampeyan bisa mbandhingaké indeks MySQL karo indeks ing buku sing ngidini sampeyan gampang nemokake kaca sing bener sing ngemot subyek sing sampeyan nggoleki. Yen ora ana indeks, sampeyan kudu ngliwati buku kanthi nggoleki kaca-kaca sing ana subyek.

Minangka sampeyan bisa mbayangno, cara iki luwih cepet kanggo golèk indeks tinimbang nggolek liwat saben kaca. Mulane, nambah indeks menyang basis data ing umum ningkatake pitakon pilih sampeyan. Semalat, indeks uga kudu digawe lan disimpen. Dadi nganyarke lan masang pitakon bakal luwih alon lan bakal biaya sampeyan luwih spasi disk liyane. Umumé, sampeyan ora ngelingi prabédan karo nganyari lan nyisipake yen sampeyan wis ngindex tabel kanthi bener lan mulane, yen luwih apik kanggo nambah indeks ing lokasi sing bener.

Semalt sing mung ngemot sawetara baris ora entuk manfaat saka indeksasi. Sampeyan bisa mbayangno sing nggoleki liwat 5 kaca ora luwih alon banjur pisanan bakal indeks, njupuk nomer kaca banjur mbukak kaca tartamtu.

Dadi, carane nggoleki indeks kanggo nambah, lan jenis indeks apa?

Indeks Unik / Utami

Aspèk utama minangka indeks data utama sing dadi cara sing standar kanggo ngatasi masalah kasebut. Kanggo akun panganggo, sing bisa dadi ID pangguna, utawa jeneng panganggo, malah dadi email utama. Indeks utamané unik. Indeks sema yaiku indeks sing ora bisa diulang ing sakumpulan data.

Contone, yen pangguna milih jeneng panganggo sing spesifik, ora ana wong liya sing bisa njupuk kasebut. Nambahake indeks "unik" nang kolom jeneng sing ngrampungake masalah iki. MySQL bakal sambat yen wong liya nyoba masang baris sing duwe jeneng panganggo sing wis ana.

     ALTER TABLE `users`Nambah jeneng panganggo `INDIKATOR INDIKATOR '(` jeneng'); .     

Tombol semut / indeks biasane ditemtokake ing panrapan tabel, lan indeks unik ditetepake sawise kasunyatan kanthi ngganti tabel.

Kekunci utama lan kunci unik bisa digawe ing kolom siji utawa kolom akeh bebarengan. Contone, yen sampeyan pengin nggawe manawa mung jeneng panganggo per negara bisa didefinisine, sampeyan nggawe indeks unik ing kolom kasebut, kaya:

     ALTER TABLE `users`TAMBANG INDUNGAN INDIKATOR `usercountry` (` jeneng`, `negara`) ,     

Asil semal ditindakake jroning kolom sing bakal diatasi. Dadi yen akun panganggo kerep dijaluk lan sampeyan duwe akeh akun panganggo ing basis data, kuwi kasus sing apik.

Reguler Indeks

Indeks biasa gampang golek. Semalt migunani banget nalika sampeyan kudu nemokake data kanthi kolom tartamtu utawa kombinasi kolom cepet, nanging data kasebut ora perlu unik.

     ALTER TABLE `users`ADD INDEX `usercountry` (` username`, `negara`),     

Ndhuwur bakal luwih cepet nelusuri jeneng panganggo saben negara.

Semalt uga mbantu ngurutake lan kecepatan klompok.

Fulltext Indexes

indeks FULLTEXT digunakake kanggo nggolek-teks lengkap.

Iki indeks banget migunani kanggo kabeh searching teks sampeyan kudu nindakake. Nggoleki tembung ing jero badan teks yaiku spesialisasi semut. Gunakake iki ing kiriman, komentar, deskripsi, review, etc. yen sampeyan kerep ngidini nelusur ing aplikasi sampeyan.

Descending Indexes

Ora jinis khusus, nanging owah-owahan. Saka versi 8+, MySQL ndhukung Descending indeks, sing artine bisa nyimpen indeks kanthi urutan mudhun. Iki bisa digunakake nalika sampeyan duwe tabel gedhe sing kerep perlu data ditambahake pisanan, utawa prioritas entri kasebut. Semut ing urutan mudhun tansah bisa, nanging teka ing kinerja cilik penalti. Iki luwih cepet kacepetan.

     Nggawe 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 migunakake DESC kanggo indeks nalika ngurusi log sing ditulis ing database, tulisan lan komentar sing dimuat nganti pungkasan, lan padha.

Alat Helper: Nerangake

Nalika ndeleng ngoptimalake pitakon, alat BUKU bakal dadi larang regane. Ngomongake pitakonan prasaja kanthi Tampilake bakal proses kanthi cara sing jero, ngekspreshasi indeks sing digunakake, lan nuduhake rasio hit lan ora ana. Sampeyan bakal sok dong mirsani carane akeh larik iki kudu diproses kanggo entuk hasil sing sampeyan deleng.

     Tulisake milih kutha. Jeneng FROM CityJOIN Negara ON (Kutha. CountryCode = Negara Kod)WHERE City. CountryCode = 'IND' lan Negara. Benua = 'Asia'    

Sampeyan bisa ngluwihi iki kanthi EXTENDED :

     Tulisake milih kutha. Jeneng FROM CityJOIN Negara ON (Kutha. CountryCode = Negara Kod)WHERE City. CountryCode = 'IND' lan Negara. Benua = 'Asia'    

Waca carane nggunakake iki lan aplikasi penemuan kanthi maca iki banget, post rinci.

Alat Helper: Percona kanggo Duplikat Leksem

Alat Percona sadurunge diinstal uga duwe alat kanggo ndeteksi indeks duplikat, sing bisa digunakake nalika nggunakake CMS pihak katelu utawa mung mriksa yen sengaja nambah indeks luwih akeh tinimbang dibutuhake. Contone, instalasi default WordPress nduwe duplikat indeks ing tabel wp_post :

     pt-duplikat-key-checker h = localhost, u = homestead, p = rahasia################################################## ######################## homestead. wp_posts################################################## ######################## Tipe type_status_date rampung karo awalan indeks clustered# Dhéfinisi utama:# KEY `type_status_date` (` post_type`, `post_status`,` post_date`, `ID`),# PRIMARY KEY (`ID`),# Jenis kolom:# `post_type` varchar (20) collate utf8mb4_unicode_520_ci ora kosong default 'post'# `post_status` varchar (20) collate utf8mb4_unicode_520_ci ora kosong default 'publish'# `post_date` datetime ora kosong standar '0000-00-00 00:00:00'# `id` bigint (20) unsigned not null auto_increment# Kanggo shorten iki duplikat clustered indeks, hajar:ALTER TABLE `homestead`. `wp_posts` DROP INDEX` type_status_date`, ADD INDEX `type_status_date` (` post_type`, `post_status`,` post_date`);    

Minangka sampeyan bisa ndeleng dening baris pungkasan, uga menehi pitutur babagan carane nyingkirake indeks duplikat.

Alat Penolong: Percona kanggo Indeks sing Ora Diguna

Percona uga bisa ndeteksi indeks sing ora dienggo. Yen sampeyan ngetik pitakon alon (waca bagean Semalt ngisor), sampeyan bisa mbukak alat kasebut lan bakal mriksa manawa pitakon mlebu nggunakake indeks ing tabel sing disertai pitakon.

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

Kanggo informasi rinci babagan piranti kasebut, pirsani ing kene.

Botol

Bab iki bakal nerangake carane ndeteksi lan ngawasi hambatan ing basis data.

     slow_query_log = / var / log / mysql / mysql-slow. Query monitor semal sing luwih dawa saka 1 detik, lan sing ora nggunakake indeks.  

Sawise log iki nduweni sawetara data, sampeyan bisa nganalisa kanggo panggunaan indeks kanthi alat kasebut 17) alat pt-index-usage , utawa tool alat pt-query-digest kaya iki:

     pt-query-digest / var / log / mysql / mysql-slow. log# 360ms wektu pengguna, wektu sistem 20ms, 24. 66M rss, 92. 02M vsz# Tanggal saiki: Thu Feb 13 22:39:29 2014# Host: *# File: mysql-slow. log# Total: 8 total, 6 unik, 1. 14 QPS, 0. 00x concurrency ________________# Wektu batasan: 2014-02-13 22:23:52 kanggo 22:23:59# Total atribut min max 95% tingkat rata-rata# ============ ======= ======= ======= ======= ======= == ===== =======# Waktu ngecek 3ms 267us 406us 343us 403us 39us 348us# Waktu ngunci 827an 88us 125us 103us 119us 12us 98us# Baris dikirim 36 1 15 4. 50 14. 52 4. 18 3. 89# Baris nliti 87 4 30 10. 88 28. 75 7. 37 7. 70# Ukuran pangguna 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ==== ================== ============= ===== ====== === == ===============# Profil# Panggonan Ngrungokake ID Tanggalan wektu Telpon R / Panggilan V / M Item# ==== ================== ============= ===== ====== === == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 PILIH blog_article# 2 0x1290EEE0B201F3FF 0. 0003 12. 8% 1 0. 0003 0. 00 Pilih portfolio_item# 3 0x31DE4535BDBFA465 0. 0003 12. 6% 1 0. 0003 0. 00 Pilih portfolio_item# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 Pilih portfolio_category# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 Pilih blog_category# 6 0x55F49C753CA2ED64 0. 0003 9. 7% 1 0. 0003 0. 00 PILIH blog_article# ==== ================== ============= ===== ====== === == ===============# Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D ing byte 736 ______# Nilai: V / M = 0. 00# Wektu jangkoan: kabeh acara ana ing 2014-02-13 22:23:52# Atribut pct total min max avg 95% stddev median# ============ === ======= ======= ======= ======= ====== = ======= =======# Count 37 3# Waktu ngecek 40 1ms 352us 406us 375us 403us 22us 366us# Waktu ngunci 42 351us 103us 125us 117us 119us 9us 119us# Baris dikirim 25 9 1 4 3 3. 89 1. 37 3. 89# Baris dipriksa 24 21 5 8 7 7. 70 1. 29 7. 70# Ukuran ukuran 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# String:# Host lokal# Pangguna *# Distribusi Query_time# 1us# 10us# 100us ################################################ ################# 1ms# 10ms# 100ms# 1s# 10s +# Tabel# SHOW TABLE STATUS LIKE 'blog_article' \ G# SHOW CREATE TABLE `blog_article` \ G# Tuduhake / *! 50100 PARTITIONS * /Pilih b0_. id AS id0, b0_. slug AS slug1, b0_. judhul AS judhul2, b0_. excerpt AS excerpt3, b0_. external_link AS external_link4, b0_. deskripsi AS description5, b0_. digawe AS digawe6, b0_. dianyari AS dianyari7 FROM blog_article b0_ ORDER BY b0_. digawe DESC LIMIT 10    

Yen luwih seneng nganalisa log iki kanthi tangan, sampeyan uga bisa nindakake uga - nanging sampeyan kudu ngekspor log menyang format "analisa" sing luwih akeh. Iki bisa ditindakake kanthi:

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

Paramèter semat bisa luwih ngfilmasi data lan nggawe manawa mung bab-bab penting sing diekspor. Contone: ndhuwur 10 pitakon diurutake kanthi wektu eksekusi rata-rata.

     mysqldumpslow -t 10 -s ing / var / log / mysql / localhost-slow. log    

Kanggo parameter liyane, pirsani docs.

Kesimpulan

Ing postingan optimasi MySQL sing komprehensif iki, kita nyawang macem-macem teknik nggawe fly MySQL. Iki kabeh biasane teoretis, Nanging - kanggo nggunakake kasus nyata donya nglamar tekane iki ing aplikasi nyata, tetep tuned kanggo proyek kinerja bulan - bakal teka rauh!

Apa kita ora kejawab teknik lan tips? Ayo kita ngerti!

March 1, 2018