Penyelidikan untuk seni bina MySQL dan prestasi Optimumkan

Ringkasan

MySQL ialah sistem pengurusan pangkalan data perhubungan yang dibangunkan pada bahasa pengaturcaraan C dan C++, dicipta oleh MySQL AB, yang diasaskan oleh David Axmark, Allan Larsson dan Michael Widenius. Versi terbaharu ialah MySQL 9.0.

MySQL ialah projek sumber terbuka dengan lesen GNU.

MySQL ialah sistem pengurusan pangkalan data yang paling popular dan berguna sekarang kerana ia adalah sumber terbuka, mempunyai ciri penuh dan mempunyai prestasi tinggi.

Seni bina

• Rajah seni bina MySQL: Sumber

Lapisan Pelanggan

• Lapisan pertama seni bina sistem MySQL.
• Perkhidmatan penting lapisan ini ialah Pengendalian Sambungan, Pengesahan dan Keselamatan.
  • Pengendalian Sambungan: Mengurus sambungan daripada pelanggan. Apabila pelanggan ingin menyambung ke pelayan MySQL, benang baharu dicipta untuk sambungan itu. Pelayan cache benang apabila sambungan baharu dibuat.
  • Pengesahan: Apabila pelanggan menyambung ke pelayan, ia mesti memberikan maklumat pengesahan untuk mewujudkan sambungan. Biasanya, pelanggan membuat pengesahan menggunakan nama pengguna dan kata laluan.
  • Keselamatan: MySQL menyediakan banyak ciri untuk melindungi pangkalan data dan mengesahkan kebenaran pengguna selepas pengesahan.

Lapisan Pelayan

• Lapisan MySQL seterusnya bertanggungjawab untuk memproses pernyataan pertanyaan dan menyediakan banyak utiliti.
• Ciri utama:
  • Parser: Apabila pelanggan meminta pernyataan pertanyaan, pelayan menganalisisnya untuk membuat pepohon pertanyaan, menulis semula pertanyaan dan menentukan susunan serta indeks yang akan digunakan untuk melaksanakan pernyataan pertanyaan.
  • Optimizer: Ia mengoptimumkan pernyataan pertanyaan sebelum pelaksanaan. Pengoptimum tidak peduli enjin apa yang digunakan, tetapi enjin storan boleh memberi kesan kepada pengoptimuman untuk pertanyaan tertentu.
  • Cache pertanyaan: Cache pertanyaan menyimpan hasil pernyataan pertanyaan. Jika pelanggan meminta pertanyaan yang sama sekali lagi, pelayan mengembalikan hasil cache, memintas langkah penghuraian dan pengoptimuman. Ciri ini telah ditamatkan dalam versi 8.0 atau lebih tinggi kerana masalah kesesakannya.
  • Perkhidmatan & Utiliti:
    • Sandarkan & Pulihkan
    • Keselamatan: pengguna pembekal dan sistem kebenaran
    • Replikasi: Ini ialah proses untuk menyalin dan menyegerakkan data daripada pelayan utama kepada banyak pelayan kanak-kanak.
    • Kluster
    • Pembahagian: Bahagikan jadual kepada banyak bahagian menggunakan logik tertentu.
    • Meja Kerja: Ia ialah alat visual yang direka untuk berinteraksi dengan dan menggunakan MySQL.

Lapisan Storan

• Ini adalah lapisan tanggungjawab untuk cara kami menyimpan data dalam pangkalan data.
• Secara lalai, MySQL menggunakan enjin storan InnoDB.
• MySQL menyokong banyak enjin storan yang berbeza:
  • InnoDB (lalai)
  • MyISAM
  • INGATAN
  • CSV
  • ARKIB
  • LUBANG HITAM
  • GABUK
  • BERSEKUTU

Optimumkan Prestasi untuk MySQL

Optimumkan pernyataan SQL

Jelaskan Pernyataan

• Ramalan pertanyaan tanpa pelaksanaan

explain sql_statememnt

• Terangkan selepas pertanyaan pelaksanaan

explain analyze sql_statement

Teknik Pengindeksan

• Ia meningkatkan prestasi semasa membuat pertanyaan dan mencari.
• Apabila membuat indeks untuk berbilang lajur, lajur pertama adalah sangat penting. Ia menentukan prestasi pernyataan pertanyaan. Contohnya, jika anda membuat indeks pada (nama, hari lahir), pertanyaan menggunakan lajur 'nama' akan menggunakan indeks ini, tetapi pertanyaan menggunakan lajur 'hari jadi' tidak akan.
• Sintaks untuk mencipta indeks baharu:

create index idx_birthday on customers(birthday);

• Arahan akan menyebabkan indeks yang ditentukan tidak boleh diakses oleh pengoptimum pertanyaan, sekali gus menghalangnya daripada dipertimbangkan untuk rancangan pelaksanaan pertanyaan:

alter table customers alter index idx_birthday invisible;

Pembahagian

• Apabila saiz pangkalan data lebih besar daripada 2GB (atau lebih tinggi daripada 10 juta rekod), anda harus menggunakan pembahagian untuk pangkalan data tersebut.
• Apabila menggunakan pembahagian, prestasi akan bertambah baik.
• Pelan partition hendaklah berdasarkan lajur yang kerap digunakan dalam klausa WHERE.

Optimumkan parameter pangkalan data

Buffer Cache Hit

• Ia adalah peratusan data yang dimuatkan oleh sistem daripada cache penimbal dan bukannya daripada storan fizikal.
• Jika peratusannya lebih rendah daripada 90%, maka anda mesti mengoptimumkannya.
• Bergantung pada 2 parameter:
  • Innodb_buffer_pool_read_requests: Jumlah permintaan dihantar ke Buffer Pool. Perintah: tunjukkan status global seperti '%Innodb_buffer_pool_read_requests%
  • Innodb_buffer_pool_reads: Jumlah permintaan yang tidak ditemui dalam kumpulan penimbal dalam memori mesti dibaca daripada cakera. Perintah: tunjukkan status global seperti '%Innodb_buffer_pool_reads%
• Formula pengiraan:

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

Jadual Cache Hit

• MySQL akan cache jadual untuk menanyakannya dengan pantas dalam ingatan.
• Jika peratusannya lebih rendah daripada 80%, maka anda mesti mengoptimumkannya.
• Bergantung pada 2 parameter:
  • Open_tables: Jumlah jadual dibuka dalam cache. Perintah: tunjukkan status global seperti 'Open_tables
  • Opened_tables: Jumlah jadual dibuka. Perintah: tunjukkan status global seperti 'Opened_tables
• Formula pengiraan: Open_tables/Opened_tables

Hit Cache Definisi Jadual

• Apabila melaksanakan pertanyaan, MySQL memerlukan maklumat tentang jadual, seperti namanya, bilangan lajur dan bilangan baris. Maklumat ini dirujuk sebagai definisi jadual.
• Jika peratusannya lebih rendah daripada 80%, maka anda mesti mengoptimumkannya.
• Bergantung pada 2 parameter:
  • Open_table_definitions: Jumlah jadual definisi berada dalam cache . Perintah: tunjukkan status global seperti 'Open_table_definitions'
  • Opened_tables: Jumlah jadual definisi diminta. Perintah: tunjukkan status global seperti 'Opened_tables'
• Formula pengiraan: Open_table_definitions/Opened_table_definitions

Jadual Sementara dalam ingatan

• Banyak pernyataan SQL seperti Order, Group By, ... mesti menggunakan jadual sementara.
• Jika peratusannya lebih rendah daripada 80%, maka anda mesti mengoptimumkannya.
• Bergantung pada 2 parameter:
  • Created_tmp_disk_tables: Jumlah jadual temp dibuat pada cakera. Perintah: tunjukkan status global seperti '%Created_tmp_disk_tables%
  • Created_tmp_tables: Jumlah jadual temp dibuat. Perintah: tunjukkan status global seperti '%Created_tmp_tables%
• Formula pengiraan: (Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables

Kesimpulan

Blog ini menawarkan pengetahuan berasaskan penyelidikan tentang MySQL. MySQL ialah sistem pengurusan pangkalan data hubungan yang terkenal dengan kos rendah, utiliti yang luas dan prestasi tinggi, menjadikannya sesuai untuk pelbagai projek. Walaupun blog ini merangkumi banyak ciri dan aspek MySQL, terdapat banyak lagi yang perlu diterokai. Saya akan mendalami ciri dan pengetahuan tambahan dalam blog akan datang.

Terima kasih kerana membaca, dan jumpa lagi di blog saya yang seterusnya.

Dokumen rujukan

• Dokumentasi MySQL
• Kursus MySQL - Tran Quoc Huy
• Wikipedia - MySQL

Atas ialah kandungan terperinci Penyelidikan untuk seni bina MySQL dan prestasi Optimumkan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!