Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan kandungan yang berkaitan tentang prinsip seni bina Pelayan MySQL boleh dibahagikan secara kasar kepada lapisan sambungan rangkaian dan lapisan perkhidmatan dari atas ke bawah. lapisan enjin storan dan lapisan fail sistem, mari kita lihat bersama-sama, saya harap ia akan membantu semua orang.
Pembelajaran yang disyorkan: tutorial video mysql
Prinsip seni bina MySQL
1 🎜>
Seni bina MySQL Server boleh dibahagikan secara kasar kepada lapisan sambungan rangkaian, lapisan perkhidmatan, lapisan enjin storan dan lapisan fail sistem dari atas ke bawah.
Lapisan sambungan rangkaian
Penyambung Pelanggan: Menyediakan sokongan untuk mewujudkan dengan pelayan MySQL. Pada masa ini, ia menyokong hampir semua teknologi pengaturcaraan sisi pelayan arus perdana, seperti Java biasa, C, Python, .NET, dll., yang mewujudkan sambungan dengan MySQL melalui teknologi API masing-masing. -
Lapisan perkhidmatan (MySQL Server)
Lapisan perkhidmatan ialah teras Pelayan MySQL dan terutamanya termasuk pengurusan sistem dan alat kawalan, kumpulan sambungan, antara muka SQL, penghurai, pengoptimum pertanyaan dan Cache enam bahagian.
- Kolam Sambungan: Bertanggungjawab untuk menyimpan dan mengurus sambungan antara pelanggan dan pangkalan data. Satu urutan bertanggungjawab untuk menguruskan satu sambungan.
- Alat pengurusan dan kawalan sistem (Perkhidmatan & Utiliti Pengurusan): seperti sandaran dan pemulihan, pengurusan keselamatan, pengurusan kluster, dsb.
- Antara muka SQL (SQL Interface): digunakan untuk menerima pelbagai arahan SQL yang dihantar oleh klien dan mengembalikan hasil yang perlu ditanya oleh pengguna. Seperti DML, DDL, prosedur tersimpan, paparan, pencetus, dsb.
- Parser: Bertanggungjawab menghuraikan SQL yang diminta untuk menjana "pohon parse". Kemudian semak lagi sama ada pokok parse itu sah mengikut beberapa peraturan MySQL.
- Pengoptimum pertanyaan (Pengoptimum): Apabila "pokok parse" melepasi semakan sintaks parser, ia akan diserahkan kepada pengoptimum untuk menukarnya menjadi pelan pelaksanaan, dan kemudian berinteraksi dengan enjin simpanan.
pilih uid, nama daripada pengguna di mana jantina = 1;Pilih--"Unjuran--"Sertai strategi
Pilih dahulu berdasarkan tempat pernyataan Memilih tidak bermakna menanyakan semua data dan kemudian menapis; - pertanyaan pilih melakukan unjuran atribut berdasarkan uid dan nama, tetapi tidak mengalih keluar semua medan
- menggabungkan pemilihan dan unjuran sebelumnya akhirnya menjana hasil pertanyaan;
-
- Cache (Cache&Buffer): Mekanisme caching terdiri daripada siri cache kecil. Contohnya, cache jadual, cache rekod, cache kebenaran, cache enjin, dsb. Jika cache pertanyaan mempunyai hasil pertanyaan hit, pernyataan pertanyaan boleh terus mengambil data daripada cache pertanyaan.
Lapisan Enjin Storan (Enjin Storan Boleh Pasang)
Enjin storan bertanggungjawab untuk penyimpanan dan pengambilan data dalam MySQL, dan berinteraksi dengan sistem asas fail. Enjin storan MySQL adalah plug-in Enjin pelaksanaan pertanyaan dalam pelayan berkomunikasi dengan enjin storan melalui antara muka yang melindungi perbezaan antara enjin storan yang berbeza. Terdapat banyak enjin storan sekarang, masing-masing mempunyai ciri tersendiri Yang paling biasa ialah MyISAM dan InnoDB. -
Sistem Fail
Lapisan ini bertanggungjawab untuk menyimpan data pangkalan data dan log pada sistem fail dan melengkapkan interaksi dengan enjin storan Ia adalah lapisan fizikal fail lapisan. Terutamanya termasuk fail log, fail data, fail konfigurasi, fail pid, fail soket, dsb.
- Fail log
- Log ralat (Log ralat)
- Didayakan secara lalai, tunjukkan pembolehubah seperti '%log_error%';
- Log pertanyaan umum (Log pertanyaan umum)
- Rekodkan pernyataan pertanyaan umum, tunjukkan pembolehubah seperti '%general%';
- Log binari (log binari)
- Merakam operasi perubahan yang dilakukan pada pangkalan data MySQL, dan merekodkan masa kejadian dan masa pelaksanaan pernyataan itu bagaimanapun, ia tidak merekodkan pilih, tunjukkan, dsb. SQL yang tidak mengubah suai pangkalan data. Terutamanya digunakan untuk pemulihan pangkalan data dan replikasi tuan-hamba.
- tunjukkan pembolehubah seperti '%log_bin%'; //Sama ada untuk mendayakan
- tunjukkan pembolehubah seperti '%binlog%'; //Paparan parameter
- tunjukkan log binari;/ / Lihat fail log
- Log pertanyaan perlahan (Log pertanyaan perlahan)
- Merakam semua SQL pertanyaan yang tamat masa pelaksanaannya ialah 10 saat.
- tunjukkan pembolehubah seperti '%slow_query%'; //Sama ada untuk mendayakan
- tunjukkan pembolehubah seperti '%long_query_time%'; //Duration
- Fail konfigurasi
- digunakan untuk menyimpan semua fail maklumat konfigurasi MySQL, seperti my.cnf, my.ini, dsb.
- Fail data
- fail db.opt: merekod set aksara lalai dan peraturan pengesahan yang digunakan oleh pustaka ini.
- fail frm: menyimpan maklumat metadata (meta) yang berkaitan dengan jadual, termasuk maklumat definisi struktur jadual, dsb. Setiap jadual akan mempunyai fail frm.
- Fail MYD: Ia dikhususkan untuk enjin storan MyISAM dan menyimpan data jadual MyISAM Setiap jadual akan mempunyai fail .MYD.
- Fail MYI: khusus untuk enjin storan MyISAM, yang menyimpan maklumat berkaitan indeks jadual MyISAM Setiap jadual MyISAM sepadan dengan fail .MYI.
- fail ibd dan fail IBDATA: simpan fail data InnoDB (termasuk indeks). Enjin storan InnoDB mempunyai dua mod ruang meja: ruang meja eksklusif dan ruang meja kongsi. Ruang jadual eksklusif menggunakan fail .ibd untuk menyimpan data dan setiap jadual InnoDB sepadan dengan satu fail .ibd. Ruang jadual kongsi menggunakan fail .ibdata dan semua jadual menggunakan satu (atau berbilang, dikonfigurasikan sendiri) fail .ibdata.
- fail ibdata1: fail data ruang jadual sistem, yang menyimpan metadata jadual, Buat asal log, dsb.
- fail ib_logfile0, ib_logfile1: Buat semula fail log log.
- fail pid
- fail pid ialah fail proses aplikasi mysqld dalam persekitaran Unix/Linux Seperti banyak program pelayan Unix/Linux yang lain, ia disimpan bersamanya id proses sendiri.
- fail soket
- fail soket juga tersedia dalam persekitaran Unix/Linux Pengguna boleh menyambungkan pelanggan dalam persekitaran Unix/Linux tanpa melalui rangkaian TCP/IP. gunakan Unix Socket terus untuk menyambung ke MySQL.
2. Mekanisme pengendalian MySQL
- Tubuhkan sambungan (Connectors&Connection Pool) untuk mewujudkan sambungan dengan MySQL melalui protokol komunikasi klien/pelayan. Kaedah komunikasi antara klien dan pelayan MySQL ialah "half-duplex". Untuk setiap sambungan MySQL, terdapat status benang pada setiap masa untuk mengenal pasti perkara yang dilakukan oleh sambungan itu.
- Mekanisme komunikasi:
- Full-duplex: boleh menghantar dan menerima data pada masa yang sama, seperti membuat panggilan telefon.
- Separuh dupleks: merujuk kepada detik tertentu, sama ada menghantar data atau menerima data, bukan pada masa yang sama. Contohnya, walkie-talkie awal
- Simplex: hanya boleh menghantar data atau hanya boleh menerima data. Contohnya, jalan sehala;
- Status utas: tunjukkan senarai proses; //Lihat maklumat utas yang pengguna jalankan, pengguna akar boleh melihat semua utas, pengguna lain hanya boleh lihat mereka sendiri;
- id: ID utas, anda boleh menggunakan kill xx
- pengguna: pengguna yang memulakan utas ini
- Hos: nombor IP dan port bagi klien yang menghantar permintaan
- db : Di perpustakaan mana arahan semasa dilaksanakan
- Arahan: Perintah operasi sedang dilaksanakan oleh urutan ini
- Buat DB: Operasi perpustakaan ialah sedang dibuat
- Lepaskan DB: Operasi perpustakaan sedang dipadamkan
- Laksanakan: Melaksanakan PreparedStatement
- Close Stmt: Menutup PreparedStatement
- Query: Melaksanakan a kenyataan
- Tidur: Menunggu pelanggan menghantar penyata
- Keluar: Keluar
- Tutup: Menutup pelayan
- Masa: Menunjukkan masa benang berada dalam keadaan semasa, dalam saat
- Nyatakan: Status benang
- Mengemaskini: Mencari rekod yang sepadan dan membuat pengubahsuaian
- Tidur: Menunggu pelanggan untuk menghantar permintaan baharu
- Bermula: Pemprosesan permintaan sedang dilakukan
- Menyemak jadual: Menyemak jadual data
- Menutup jadual: Menyegarkan semula data dalam jadual ke cakera
- Dikunci: Rekod dikunci oleh pertanyaan lain
- Menghantar Data: Memproses pertanyaan Pilih dan menghantar keputusan kepada pelanggan pada masa yang sama
- Maklumat: Secara amnya merekodkan pernyataan yang dilaksanakan oleh utas dan memaparkan 100 aksara pertama secara lalai. Jika anda ingin melihat senarai proses yang lengkap, gunakan show full processlist; Pernyataan SQL yang sama tidak dipersoalkan, penghurai akan melakukan analisis sintaksis dan semantik dan menjana "Pokok Parse".
Cache keputusan pertanyaan Pilih dan pernyataan SQL
Apabila melaksanakan pertanyaan Pilih, tanya cache terlebih dahulu untuk menentukan sama ada terdapat set rekod yang tersedia dan sama ada keperluan adalah betul-betul sama (termasuk nilai parameter), supaya Akan memadankan hit data yang dicache;
- Walaupun cache pertanyaan dihidupkan, SQL berikut tidak boleh dicache:
- Pernyataan pertanyaan menggunakan SQL_NO_CACHE
- Hasil pertanyaan lebih besar daripada tetapan query_cache_limit
- Terdapat beberapa parameter yang tidak pasti dalam pertanyaan, seperti now()
-
- tunjukkan pembolehubah seperti '%query_cache%'; //Semak sama ada cache pertanyaan didayakan, saiz ruang, sekatan, dsb.
- tunjukkan status seperti 'Qcache%' //Lihat parameter cache yang lebih terperinci, ruang cache yang tersedia, cache blok, saiz cache, dsb.
- Penghuraikan akan SQL yang dihantar oleh pelanggan menjalankan analisis sintaks dan menjana "pokok parse". Prapemproses selanjutnya menyemak sama ada "pohon parse" adalah sah berdasarkan beberapa peraturan MySQL Contohnya, ia akan menyemak sama ada jadual data dan lajur data wujud, dan juga menghuraikan nama dan alias untuk melihat sama ada ia samar-samar, dan akhirnya menghasilkan a. "pokok parse" baharu .
- Pengoptimum pertanyaan (Pengoptimum) menjana pelan pelaksanaan optimum berdasarkan "pohon parse". MySQL menggunakan banyak strategi pengoptimuman untuk menjana pelan pelaksanaan yang optimum, yang boleh dibahagikan kepada dua kategori: pengoptimuman statik (pengoptimuman masa kompilasi) dan pengoptimuman dinamik (pengoptimuman masa jalan).
- Strategi transformasi setara
- 5=5 dan a>5 ditukar kepada > 5
- a 5 dan a =5
- Berdasarkan indeks sendi, laraskan kedudukan keadaan, dsb.
- Optimumkan kiraan, min, maks dan fungsi lain
- Min enjin InnoDB fungsi hanya perlu mencari indeks Paling kiri
- Fungsi max enjin InnoDB hanya perlu mencari indeks paling kanan
- kiraan enjin MyISAM(*), tiada pengiraan diperlukan dan ia kembali terus
- terlebih dahulu Tamatkan pertanyaan
- Gunakan pertanyaan had untuk mendapatkan data yang diperlukan mengikut had tanpa terus melintasi data berikutnya
- Pengoptimuman daripada dalam
- MySQL untuk dalam Pertanyaan akan diisih dahulu, dan kemudian kaedah binari akan digunakan untuk mencari data. Sebagai contoh, di mana id dalam (2,1,3) menjadi dalam (1,2,3); penyata. Enjin pelaksanaan pertanyaan akan memperoleh hasil pertanyaan dan mengembalikannya kepada klien berdasarkan jenis enjin storan jadual dalam pernyataan SQL dan interaksi antara antara muka API yang sepadan dan cache enjin storan atau fail fizikal. Jika cache pertanyaan didayakan, pernyataan SQL dan keputusan akan disimpan sepenuhnya dalam cache pertanyaan (Cache&Buffffer Jika pernyataan SQL yang sama dilaksanakan pada masa hadapan, hasilnya akan dikembalikan secara langsung.
Jika caching pertanyaan didayakan, cache hasil pertanyaan dahulu
Terlalu banyak hasil yang dikembalikan, gunakan mod tambahan untuk kembali
- Pastikan anda membuat pertimbangan sebelum memulakan pelaksanaan Lakukan anda mempunyai kebenaran untuk melaksanakan pertanyaan pada jadual ini T? Jika tidak, ralat tiada kebenaran akan dikembalikan (Jika cache pertanyaan dipukul, pengesahan kebenaran akan dilakukan apabila cache pertanyaan mengembalikan keputusan. Pertanyaan juga akan dipanggil. sebelum pengoptimum prasemak untuk mengesahkan kebenaran).
- Jika anda mempunyai kebenaran, buka jadual dan teruskan pelaksanaan. Apabila jadual dibuka, pelaksana akan menggunakan antara muka yang disediakan oleh enjin berdasarkan definisi enjin jadual. Aliran pelaksanaan pelaksana adalah seperti berikut:
- pilih * daripada ujian di mana umur >
- Panggil antara muka enjin InnoDB untuk mendapatkan baris pertama jadual ini dan tentukan sama ada umur nilai ialah 10. Jika tidak, langkaunya Jika ya, simpan baris ini dalam set hasil
- Panggil antara muka enjin untuk mendapatkan "baris seterusnya" dan ulangi logik penghakiman yang sama sehingga baris terakhir ini. meja diambil.
- Pelaksana mengembalikan set rekod yang terdiri daripada semua baris yang memenuhi syarat semasa proses traversal di atas sebagai hasil yang ditetapkan kepada klien.
-
-
- 3. Enjin storan Mysql
Enjin storan terletak pada lapisan ketiga dalam seni bina MySQL dan berada bertanggungjawab untuk MySQL Penyimpanan dan pengambilan data dalam adalah subsistem yang berkaitan dengan fail Ia adalah mekanisme capaian fail yang disesuaikan berdasarkan antara muka abstrak lapisan capaian yang disediakan oleh MySQL Mekanisme ini dipanggil enjin penyimpanan.
Gunakan perintah
show engines
untuk melihat maklumat enjin yang disokong oleh pangkalan data semasa.
Enjin storan MyISAM telah digunakan secara lalai sebelum versi 5.5, dan enjin storan InnoDB telah digunakan bermula dari 5.5.
- InnoDB: menyokong urus niaga, mempunyai komit, rollback dan keupayaan pemulihan ranap, keselamatan transaksi; Gunakan memori untuk mencipta jadual, kelajuan akses sangat pantas, kerana data berada dalam ingatan, dan indeks Hash digunakan secara lalai, tetapi apabila ia ditutup, data akan hilang
- Arkib: Enjin jenis arkib, hanya menyokong penyata sisipan dan pilih ;
- Csv: Gunakan fail CSV untuk penyimpanan data Disebabkan pengehadan fail, semua lajur mesti dipaksa untuk menyatakan bukan nol indeks dan partition, jadi ia sesuai untuk jadual perantaraan untuk pertukaran data;
- Bersekutu: Anda boleh mengakses jadual dalam pangkalan data MySQL jauh. Jadual setempat tidak menyimpan data dan mengakses kandungan jadual jauh.
- MRG_MyISAM: Gabungan sekumpulan jadual MyISAM ini mesti mempunyai struktur yang sama. Operasi Gabungan boleh beroperasi pada kumpulan jadual MyISAM 🎜>
InnoDB Berbanding dengan MyISAM-
- Transaksi dan kunci asing
- InnoDB menyokong transaksi dan kunci asing, mempunyai keselamatan dan integriti, dan sesuai untuk sejumlah besar sisipan atau operasi kemas kini
MyISAM tidak Menyokong transaksi dan kunci asing, ia menyediakan storan dan pengambilan berkelajuan tinggi, sesuai untuk sejumlah besar operasi pertanyaan terpilih
- Mekanisme kunci
- InnoDB menyokong kunci peringkat baris untuk mengunci rekod yang ditentukan. Penguncian dilaksanakan berdasarkan indeks.
- MyISAM menyokong penguncian peringkat meja, mengunci keseluruhan meja.
- Struktur indeks
- InnoDB menggunakan indeks berkelompok (indeks berkelompok Indeks dan rekod disimpan bersama, meng-cache kedua-dua indeks dan rekod).
- MyISAM menggunakan indeks bukan berkelompok (indeks bukan berkelompok), dan indeks dan rekod diasingkan.
- Keupayaan pemprosesan serentak
- MyISAM menggunakan kunci meja, yang akan membawa kepada kadar operasi tulis serentak yang rendah, tiada penyekatan antara bacaan dan penyekatan bacaan dan tulis.
- Sekatan baca dan tulis InnoDB boleh dikaitkan dengan tahap pengasingan dan kawalan konkurensi berbilang versi (MVCC) boleh digunakan untuk menyokong konkurensi tinggi
- Fail storan
- InnoDB Jadual sepadan dengan dua fail, fail struktur jadual .frm dan fail data .ibd. Jadual InnoDB menyokong sehingga 64TB;
- Jadual MyISAM sepadan dengan tiga fail, fail struktur jadual .frm, fail data jadual MYD dan fail indeks .MYI. Bermula daripada
MySQL 5.0, had lalai ialah 256TB.
-
Senario yang berkenaan- MyISAM
-
Tiada sokongan transaksi diperlukan (tidak disokong)
Konkurensi agak rendah (masalah mekanisme penguncian )
- Pengubahsuaian data agak kecil, terutamanya membaca
- Keperluan konsistensi data tidak tinggi
-
- InnoDB
- Memerlukan sokongan transaksi ( Mempunyai ciri urus niaga yang baik)
- Penguncian peringkat baris mempunyai kebolehsesuaian yang baik kepada konkurensi tinggi
Senario dengan kemas kini data yang kerap
- Keperluan konsistensi data tinggi
- Peranti perkakasan mempunyai memori yang besar, dan keupayaan caching InnoDB yang lebih baik boleh digunakan untuk meningkatkan penggunaan memori dan mengurangkan IO cakera
-
-
- Ringkasan
- Cara memilih antara dua enjin?
Adakah anda memerlukan transaksi? Ya, adakah InnoDB
mempunyai pengubahsuaian serentak? Ya, adakah InnoDB
- meneruskan pertanyaan pantas dan sedikit pengubahsuaian data? Ya, MyISAM
- Dalam kebanyakan kes, disyorkan untuk menggunakan InnoDBBermula dari MySQL versi 5.5, InnoDB digunakan sebagai enjin secara lalai Ia pandai memproses transaksi dan mempunyai pemulihan ranap automatik. Berikut ialah gambar rajah seni bina enjin InnoDB rasmi, yang terbahagi terutamanya kepada dua bahagian: struktur memori dan struktur cakera.
Struktur memori InnoDB
Struktur memori terutamanya merangkumi empat komponen utama: Buffer Pool, Change Buffer, Adaptive Hash Index dan Log Penampan.
- Kolam Penampan: Kolam Penampan, dirujuk sebagai BP. BP adalah berdasarkan Halaman, dengan saiz lalai 16K Lapisan bawah BP menggunakan struktur data senarai terpaut untuk mengurus Halaman. Apabila InnoDB mengakses rekod jadual dan indeks, mereka akan dicache dalam halaman Halaman Penggunaan kemudian boleh mengurangkan operasi cakera IO dan meningkatkan kecekapan.
- Mekanisme pengurusan halaman
- Halaman boleh dibahagikan kepada tiga jenis mengikut status:
- halaman percuma: halaman terbiar, tidak digunakan
- halaman bersih: digunakan Gunakan halaman, data belum diubah suai
- halaman kotor: Halaman kotor, gunakan halaman, data telah diubah suai, data dalam halaman dan data pada cakera tidak konsisten
- Untuk tiga jenis halaman di atas, InnoDB menyelenggara dan mengurusnya melalui tiga struktur senarai terpaut:
- senarai percuma: mewakili penimbal percuma, mengurus halaman percuma
- senarai siram: mewakili keperluan untuk disiram ke cakera Penampan menguruskan halaman kotor, dan halaman dalaman diisih mengikut masa pengubahsuaian. Halaman kotor wujud dalam kedua-dua senarai pautan siram dan senarai pautan LRU, tetapi ia tidak menjejaskan satu sama lain Senarai terpaut LRU bertanggungjawab untuk mengurus ketersediaan dan penyimpanan halaman, manakala senarai pautan siram bertanggungjawab untuk mengurus operasi curahan. daripada halaman yang kotor.
- Senarai lru: Mewakili penimbal sedang digunakan, mengurus halaman bersih dan halaman kotor Penimbal adalah berdasarkan titik tengah Senarai terpaut hadapan dipanggil kawasan senarai baharu, yang menyimpan data yang kerap diakses, menyumbang 63%. ; yang terakhir Senarai terpaut dipanggil kawasan senarai lama, yang menyimpan data yang kurang digunakan, menyumbang 37%.
-
Penyelenggaraan algoritma LRU yang dipertingkatkan
- LRU biasa: kaedah penyingkiran tamat, data baharu ditambah daripada ketua senarai terpaut, dan dihapuskan dari penghujung apabila ruang dikeluarkan
- LRU yang diubah suai: senarai terpaut dibahagikan kepada dua bahagian, baru dan lama, apabila menambah elemen Ia tidak dimasukkan dari kepala jadual, tetapi dari kedudukan titik tengah Jika data diakses tidak lama lagi, halaman akan berpindah ke kepala senarai baru tidak diakses, ia akan beransur-ansur beralih ke penghujung senarai lama, menunggu penyingkiran.
- Setiap kali data halaman baharu dibaca ke dalam kumpulan penimbal, enjin InnoDb akan menentukan sama ada terdapat halaman percuma dan sama ada halaman tersebut mencukupi Jika terdapat halaman percuma, halaman percuma akan dipadamkan daripada senarai percuma dan diletakkan dalam senarai LRU. Jika tiada halaman percuma, halaman lalai senarai terpaut LRU akan dihapuskan mengikut algoritma LRU, dan ruang memori akan dikeluarkan dan diperuntukkan kepada halaman baharu.
- Parameter konfigurasi Buffer Pool
- tunjukkan pembolehubah seperti '%innodb_page_size%'; //Lihat saiz halaman
- tunjukkan pembolehubah seperti '%innodb_old% '; ; //Lihat parameter senarai lama dalam senarai lru
- tunjukkan pembolehubah seperti '%innodb_buffer%'; //Lihat parameter kumpulan penimbal
- Cadangan: Tetapkan innodb_buffer_pool_size kepada 60% daripada jumlah saiz memori - 80%, innodb_buffer_pool_instances boleh ditetapkan kepada berbilang untuk mengelakkan perbalahan cache.
- Tukar Penampan: Tulis penimbal, dirujuk sebagai CB. Apabila menjalankan operasi DML, jika BP tidak mempunyai data Halaman yang sepadan, halaman cakera tidak akan dimuatkan ke dalam kumpulan penimbal serta-merta, sebaliknya, perubahan penimbal akan direkodkan dalam CB, dan apabila data masa hadapan dibaca, data tersebut akan digabungkan dan dipulihkan kepada BP.
- ChangeBuffer menduduki ruang BufferPool lalai ialah 25% dan maksimum yang dibenarkan ialah 50%. Parameter innodb_change_buffer_max_size;
- Apabila rekod dikemas kini, rekod wujud dalam BufferPool dan diubah suai terus dalam BufferPool, operasi memori. Jika rekod tidak wujud dalam BufferPool (tiada hit), operasi memori akan dilakukan terus dalam ChangeBuffer tanpa perlu menanyakan cakera untuk data dan mengelakkan IO cakera. Apabila menanyakan rekod kali seterusnya, ia akan dibaca daripada cakera terlebih dahulu, kemudian maklumat akan dibaca daripada ChangeBuffer dan digabungkan, dan akhirnya dimuatkan ke dalam BufferPool.
- Tulis penimbal, hanya terpakai pada halaman indeks biasa bukan unik
- Jika keunikan ditetapkan dalam indeks, InnoDB mesti melakukan pengesahan keunikan semasa membuat pengubahsuaian, jadi cakera mesti disoal operasi IO. Rekod itu akan ditanya terus ke dalam BufferPool dan kemudian diubah suai dalam kumpulan penimbal. Ia tidak akan dikendalikan dalam ChangeBuffer.
- Indeks Adaptive Hash: Indeks Hash Adaptive, digunakan untuk mengoptimumkan pertanyaan untuk data BP. Enjin storan InnoDB akan memantau carian untuk indeks jadual Jika diperhatikan bahawa membina indeks cincang boleh meningkatkan kelajuan, ia akan membina indeks cincang, jadi ia dipanggil penyesuaian. Enjin storan InnoDB secara automatik mencipta indeks cincang untuk halaman tertentu berdasarkan kekerapan dan corak akses.
- Penimbal Log: Penimbal log digunakan untuk menyimpan data yang akan ditulis pada fail log (Buat Semula/Buat Asal) pada cakera Kandungan penimbal log sentiasa dimuat semula ke fail log cakera. Apabila penimbal log penuh, ia akan dipadamkan secara automatik ke cakera Apabila menghadapi operasi transaksi besar seperti kemas kini BLOB atau berbilang baris, meningkatkan penimbal log boleh menjimatkan I/O cakera.
- LogBuffer digunakan terutamanya untuk merekodkan log enjin InnoDB dan Buat Buat asal akan dijana semasa operasi DML;
- Apabila ruang LogBuffer penuh, ia akan ditulis secara automatik ke cakera.Anda boleh mengurangkan kekerapan IO cakera dengan meningkatkan parameter innodb_log_buffer_size; parameter
- innodb_flush_log_at_trx_commit mengawal kelakuan muat semula log, lalainya ialah 1
- 0: tulis fail log dan operasi siram cakera setiap 1 saat (tulis fail Log LogBuffer --> OS cache, flush OScache --> fail cakera), data akan hilang sehingga 1 saat
- 1: Transaksi dilakukan, fail log ditulis serta-merta dan cakera disiram, data tidak hilang, tetapi operasi IO yang kerap akan berlaku
- 2: Urus niaga diserahkan, fail log ditulis serta-merta dan operasi siram cakera dilakukan setiap 1 saat
Struktur cakera InnoDB
Cakera InnoDB terutamanya termasuk Tablespaces, Kamus Data InnoDB, Penimbal Doublewrite, Redo Log dan Undo Log.
-
Ruang meja: digunakan untuk menyimpan struktur jadual dan data. Ruang jadual dibahagikan kepada ruang jadual sistem, ruang jadual bebas, ruang jadual umum, ruang jadual sementara, Buat asal ruang jadual dan jenis lain
-
Kamus Data (Kamus Data InnoDB)
- Kamus Data InnoDB terdiri daripada dalaman jadual sistem Terdiri daripada jadual yang mengandungi metadata untuk objek seperti jadual carian, indeks dan medan jadual. Metadata terletak secara fizikal dalam ruang jadual sistem InnoDB. Atas sebab sejarah, metadata kamus data bertindih pada tahap tertentu dengan maklumat yang disimpan dalam fail metadata jadual InnoDB (fail.frm).
-
Doublewrite Buffer
- terletak dalam ruang jadual sistem dan merupakan kawasan storan. Sebelum halaman BufferPage dialihkan ke lokasi sebenar pada cakera, data akan disimpan dalam penimbal Doublewrite. Jika sistem pengendalian, subsistem storan atau proses mysqld ranap semasa halaman sedang ditulis, InnoDB boleh mencari sandaran halaman yang baik daripada penimbal Doublewrite semasa pemulihan ranap sistem. Dalam kebanyakan kes, penimbal doublewrite didayakan secara lalai Untuk melumpuhkan penimbal Doublewrite, anda boleh menetapkan innodb_doublewrite kepada 0. Adalah disyorkan untuk menetapkan innodb_flush_method kepada O_DIRECT apabila menggunakan penimbal Doublewrite.
- Parameter innodb_flush_method MySQL mengawal mod pembukaan dan curahan fail data innodb dan buat semula log. Terdapat tiga nilai: fdatasync (lalai), O_DSYNC, O_DIRECT. Menetapkan O_DIRECT bermakna operasi penulisan fail data akan memberitahu sistem pengendalian supaya tidak cache data, jangan gunakan prabacaan dan tulis terus dari InnodbBuffer ke fail cakera.
- Fdatasync lalai bermaksud menulis kepada cache sistem pengendalian dahulu, dan kemudian memanggil fungsi fsync() untuk mengepam maklumat cache fail data dan buat semula log secara tak segerak.
-
Buat Semula Log
- Log buat semula ialah struktur data berasaskan cakera yang digunakan untuk membetulkan data yang ditulis oleh transaksi yang tidak lengkap semasa pemulihan ranap sistem. MySQL menulis fail log buat semula secara bulat dan merekodkan semua pengubahsuaian kepada Buffer Pool dalam InnoDB. Apabila kegagalan kejadian berlaku (seperti gangguan bekalan elektrik) dan data gagal dikemas kini kepada fail data, pangkalan data mesti dibuat semula apabila pangkalan data dimulakan semula untuk mengemas kini data kepada fail data semula. Semasa pelaksanaan transaksi baca dan tulis, log buat semula akan terus dijana. Secara lalai, log buat semula secara fizikal diwakili pada cakera oleh dua fail bernama ib_logfile0 dan ib_logfile1.
-
Buat asal Log
- Log buat asal ialah sandaran data yang diubah suai yang disimpan sebelum transaksi bermula, digunakan untuk pengecualian Gulung semula transaksi. Log buat asal ialah log logik dan direkodkan berdasarkan setiap baris. Buat asal log wujud dalam ruang jadual sistem, buat asal ruang jadual dan ruang jadual sementara.
Evolusi struktur versi baharu
- Versi MySQL 5.7
- Buat asal jadual log ruang diasingkan daripada fail ibdata ruang jadual kongsi, dan saiz dan kuantiti fail boleh ditentukan oleh pengguna semasa memasang MySQL.
- Menambah ruang jadual sementara, yang menyimpan data jadual sementara atau set hasil pertanyaan sementara.
- Saiz Buffer Pool boleh diubah suai secara dinamik tanpa memulakan semula contoh pangkalan data.
- MySQL 8.0 version
- mengasingkan sepenuhnya kamus data dan Buat asal jadual InnoDB daripada ibdata ruang jadual kongsi Pada masa lalu, kamus data dan jadual bebas masuk ibdata diperlukan Kamus data dalam fail ibd ruang mestilah konsisten, yang tidak diperlukan dalam versi 8.0.
- Ruang meja sementara sementara juga boleh dikonfigurasikan dengan berbilang fail fizikal, dan semuanya adalah enjin storan InnoDB dan boleh mencipta indeks, yang mempercepatkan pemprosesan.
- Pengguna boleh menyediakan beberapa ruang jadual seperti pangkalan data Oracle Setiap ruang jadual sepadan dengan berbilang fail fizikal Setiap ruang jadual boleh digunakan oleh berbilang jadual, tetapi jadual hanya boleh disimpan dalam satu ruang jadual.
- Penimbal Doublewrite juga diasingkan daripada ibdata ruang jadual kongsi.
Model Benang InnoDB
- Benang IO
- banyak digunakan dalam InnoDB AIO (Async IO) digunakan untuk membaca dan menulis, yang boleh meningkatkan prestasi pangkalan data. Dalam
InnoDB mempunyai sejumlah 10 IO Thread, iaitu 4 writes, 4 reads, 1 insert buffer dan 1 log thread.
- benang baca: Bertanggungjawab untuk operasi membaca dan memuatkan data dari cakera ke halaman cache. 4
- tulis benang: bertanggungjawab untuk operasi menulis dan membuang halaman kotor yang dicache ke cakera. 4
- benang log: bertanggungjawab untuk mengepam kandungan penimbal log ke cakera. 1
- masukkan benang penimbal: Bertanggungjawab untuk mengepam kandungan penimbal tulis ke cakera. 1
- Purge Thread
- Selepas transaksi dilakukan, log asal yang digunakannya tidak lagi diperlukan, jadi Purge Thread perlu mengitar semula halaman buat asal yang diperuntukkan.
- tunjukkan pembolehubah seperti '%innodb_purge_threads%';
- Benang Pembersih Halaman
- Fungsinya adalah untuk mengepam data kotor ke cakera data disiram Log buat semula yang sepadan juga boleh ditimpa, yang boleh menyegerakkan data dan mencapai tujuan kitar semula log semula. Pemprosesan benang tulis akan dipanggil.
tunjukkan pembolehubah seperti '%innodb_page_cleaners%';-
Benang IndukBenang induk ialah utas utama InnoDB, bertanggungjawab untuk menjadualkan utas lain, keutamaan Peringkat tertinggi. Fungsinya adalah untuk menyegarkan semula data dalam kumpulan penimbal ke cakera secara tidak segerak untuk memastikan ketekalan data. Termasuk: muat semula halaman kotor (benang pembersih halaman), buat asal kitar semula halaman (benang bersih), buat semula muat semula log (benang log), penimbal tulis digabungkan, dsb. Terdapat dua proses utama di dalamnya, satu setiap 1 saat dan satu setiap 10 saat. - Operasi setiap 1 saat:
Muat semula penimbal log, siram ke cakera- Gabungkan data penimbal tulis, tentukan sama ada untuk beroperasi berdasarkan tekanan baca dan tulis IO
-
Muat semula data halaman kotor ke cakera, dan beroperasi hanya apabila nisbah halaman kotor mencapai 75% (innodb_max_dirty_pages_pct, - innodb_io_capacity)
- 🎜 >Muat semula data halaman kotor ke cakera
- Gabungkan data penimbal tulis
- Muat semula penimbal log
- Padam halaman buat asal yang tidak berguna
-
Struktur storan fail InnoDB
-
InnoDB文件存储格式
-
File文件格式（File-Format）
- 在早期的InnoDB版本中，文件格式只有一种，随着InnoDB引擎的发展，出现了新文件格式，用于支持新的功能。目前InnoDB只支持两种文件格式：Antelope 和 Barracuda。
- Antelope: 先前未命名的，最原始的InnoDB文件格式，它支持两种行格式：COMPACT和REDUNDANT，MySQL 5.6及其以前版本默认格式为Antelope。
- Barracuda: 新的文件格式。它支持InnoDB的所有行格式，包括新的行格式：COMPRESSED和 DYNAMIC。
- 通过innodb_file_format 配置参数可以设置InnoDB文件格式，之前默认值为Antelope，5.7版本开始改为Barracuda。
Row行格式（Row_format）
-
表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和DML操作的性能。如果在单个page页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的I/O更少。
InnoDB存储引擎支持四种行格式：REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。
DYNAMIC和COMPRESSED新格式引入的功能有：数据压缩、增强型长列数据的页外存储和大索引前缀。
每个表的数据分成若干页来存储，每个页中采用B树结构存储；
-
如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页，该字段被称为页外列。
- REDUNDANT 行格式
- 使用REDUNDANT行格式，表会将变长列值的前768字节存储在B树节点的索引记录中，其余
的存储在溢出页上。对于大于等于786字节的固定长度字段InnoDB会转换为变长字段，以便
能够在页外存储。
- COMPACT 行格式
- 与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但代价是增加了
某些操作的CPU使用量。如果系统负载是受缓存命中率和磁盘速度限制，那么COMPACT格式
可能更快。如果系统负载受到CPU速度的限制，那么COMPACT格式可能会慢一些。
- DYNAMIC 行格式
- 使用DYNAMIC行格式，InnoDB会将表中长可变长度的列值完全存储在页外，而索引记录只包含指向溢出页的20字节指针。大于或等于768字节的固定长度字段编码为可变长度字段。DYNAMIC行格式支持大索引前缀，最多可以为3072字节，可通过innodb_large_prefix参数控制。
- COMPRESSED 行格式
- COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引
数据压缩的支持。
-
在创建表和索引时，文件格式都被用于每个InnoDB表数据文件（其名称与*.ibd匹配）。修改文件格式的方法是重新创建表及其索引，最简单方法是对要修改的每个表使用以下命令：
ALTER TABLE 表名 ROW_FORMAT=格式类型;
Salin selepas log masuk
Undo Log
Undo Log介绍
Undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。
Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。
Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记录一个insert；执行一个update，undolog会记录一个相反的update。
Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。
#相关参数命令
show variables like '%innodb_undo%';
Salin selepas log masuk
Undo Log作用
- 实现事务的原子性
- Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。
-
实现多版本并发控制（MVCC）
Redo Log 日志
-
Redo Log 介绍
- Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。
- Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。
- Redo Log 的生成和释放：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。
Redo Log工作原理
- Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表
的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘
数据进行持久化这一特性。
- write pos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；
- checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；
- write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint推进一下。
-
Redo Log相关配置参数
- 0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次操作。
- 1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。
- 2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OS cache -> flush cache to disk 的操作。
- 一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数
据。
Binlog日志
-
Binlog 记录模式
- Redo Log 是属于InnoDB引擎所特有的日志，而MySQL Server也有自己的日志，即 Binary log（二进制日志），简称Binlog。Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog日志是以事件形式记录，还包含语句所执行的消耗时间。开启Binlog日志有以下两个最重要的使用场景。
- 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
- 数据恢复：通过mysqlbinlog工具来恢复数据。
- Binlog文件名默认为“主机名_binlog-序列号”格式，例如oak_binlog-000001，也可以在配置文件中指定名称。文件记录模式有STATEMENT、ROW和MIXED三种，具体含义如下。
- ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
- 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。
- 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
- STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到master的Binlog中，slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。
- 优点：日志量小，减少磁盘IO，提升存储和恢复速度
- 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。
- MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。
-
Binlog 文件结构
-
Binlog写入机制
- 根据记录模式和操作触发event事件生成log event（事件触发执行机制）
- 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
- 事务在提交阶段会将产生的log event写入到外部binlog文件中。
- 不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。
-
Binlog文件操作
- 根据记录模式和操作触发event事件生成log event（事件触发执行机制）
- 将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区
- Log Event保存在一个binlog_cache_mngr数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
- 事务在提交阶段会将产生的log event写入到外部binlog文件中。
- 不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在
binlog文件中是连续的，中间不会插入其他事务的log event。
-
Binlog文件操作
-
Binlog状态查看
show variables like 'log_bin';
Salin selepas log masuk
-
开启Binlog功能
-
使用show binlog events命令
show binary logs; //等价于show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';
Salin selepas log masuk
-
使用 mysqlbinlog 命令
mysqlbinlog "文件名"
mysqlbinlog "文件名" > "test.sql"
Salin selepas log masuk
-
使用 binlog 恢复数据
-
删除Binlog文件
-
Redo Log和 Binlog区别
- Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
- Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
- Redo Log日志是循环写，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
- Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。
推荐学习：mysql视频教程
Atas ialah kandungan terperinci Penjelasan grafik terperinci tentang prinsip seni bina mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
