Berbagi teknologi

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

---

1. Infrastruktur: Bagaimana pernyataan query SQL dieksekusi?

Ringkasan

Secara umum, MySQL dapat dibagi menjadi dua lapisan

• Lapisan server
  Mencakup sebagian besar fungsi layanan inti MySQL
  • Penyambung
  • Cache kueri
  • penganalisa
  • pengoptimal
  • Aktuator
  • Semua fungsi bawaan (seperti tanggal, waktu, fungsi matematika dan kriptografi, dll.)
  • Kemampuan di seluruh mesin penyimpanan
    • prosedur tersimpan
    • pemicu
    • melihat
    • ……
• lapisan mesin penyimpanan
  Arsitektur plug-in, bertanggung jawab atas penyimpanan dan pengambilan data
  • Bahasa Indonesia
  • MyISAM
  • Penyimpanan

Penyambung

mysql -h$ip -P$port -u$user -p
1

Mysql dalam perintah koneksi adalah alat klien yang digunakan untuk membuat koneksi dengan server.Setelah menyelesaikan jabat tangan TCP klasik, konektor
Ini akan mulai mengautentikasi identitas Anda. Saat ini, nama pengguna dan kata sandi yang Anda masukkan akan digunakan.

• Jika nama pengguna atau kata sandi salah, Anda akan menerima kesalahan "Akses ditolak untuk pengguna", dan kemudian program klien
  Akhiri eksekusi.
• Jika otentikasi nama pengguna dan kata sandi lolos, konektor akan lolosTabel izin Cari tahu izin apa yang Anda miliki di sana.Selanjutnya, dalam hubungan ini
  Logika penilaian izin akan bergantung pada izin yang dibaca saat ini.

Jika klien tidak aktif terlalu lama, konektor akan memutuskan sambungannya secara otomatis. Kali ini dikontrol oleh parameter wait_timeout, dan nilai defaultnya adalah 8 jam.

Jika klien mengirimkan permintaan lagi setelah koneksi terputus, klien akan menerima pengingat kesalahan: Lost connection to MySQL server during query . Jika Anda ingin melanjutkan saat ini, Anda perlu menyambungkan kembali dan kemudian menjalankan permintaan.

Dalam database, koneksi yang panjang berarti setelah koneksi berhasil, jika klien terus melakukan permintaan, koneksi yang sama akan selalu digunakan. Sambungan pendek berarti sambungan terputus setelah beberapa kueri dijalankan, dan sambungan baru dibuat kembali untuk kueri berikutnya.

Proses membuat koneksi biasanya rumit, jadi saya sarankan Anda mencoba meminimalkan tindakan membuat koneksi saat digunakan, yaitu coba gunakan koneksi yang panjang.

Namun setelah semua koneksi lama digunakan, Anda mungkin menemukan bahwa terkadang memori yang digunakan oleh MySQL meningkat dengan sangat cepatMemori yang digunakan sementara oleh MySQL selama eksekusi dikelola di objek koneksi. . Sumber daya ini akan dilepaskan ketika koneksi terputus.Jadi jikaAkumulasi koneksi yang lama dapat menyebabkan penggunaan memori yang berlebihan., dibunuh secara paksa oleh sistem (OOM). Dilihat dari fenomena tersebut, MySQL restart secara tidak normal.

Bagaimana cara mengatasi masalah ini? Anda dapat mempertimbangkan dua opsi berikut.

• Putuskan sambungan lama secara berkala . Setelah menggunakannya untuk jangka waktu tertentu, atau setelah program menentukan bahwa kueri besar yang memakan memori telah dijalankan, sambungan terputus, lalu kueri tersebut diperlukan dan kemudian disambungkan kembali.
• Jika Anda menggunakan MySQL 5.7 atau lebih baru, Anda dapat menjalankannya mysql_reset_connection untuk menginisialisasi ulang sumber daya koneksi. Proses ini tidak memerlukan koneksi ulang dan verifikasi izin, namun akan mengembalikan koneksi ke keadaan saat baru saja dibuat.

Cache kueri

Setelah MySQL mendapat permintaan kueri, ia akan masuk ke cache kueri terlebih dahulu untuk melihat apakah pernyataan ini telah dieksekusi sebelumnya. Pernyataan yang dieksekusi sebelumnya dan hasilnya dapat disimpan dalam cache langsung di memori dalam bentuk pasangan nilai kunci. Kuncinya adalah pernyataan kueri, dan nilainya adalah hasil kueri. Jika kueri Anda dapat menemukan kunci secara langsung di cache ini, maka nilainya akan dikembalikan langsung ke klien.

Jika pernyataan tidak ada dalam cache kueri, tahap eksekusi dilanjutkan. Setelah eksekusi selesai, hasil eksekusi akan disimpan dalam cache query. Anda dapat melihat bahwa jika kueri mencapai cache, MySQL dapat langsung mengembalikan hasilnya tanpa melakukan operasi rumit selanjutnya, yang sangat efisien.

Namun seringkali saya akan melakukannyaDisarankan agar Anda tidak menggunakan cache kueri ,Mengapa? Karena cache kueri sering kali lebih banyak merugikan daripada menguntungkan.

Cache kueri sangat sering tidak valid. Selama ada pembaruan pada tabel, semua cache kueri pada tabel ini akan dihapus. Jadi mungkin saja Anda bersusah payah menyimpan hasilnya, dan bahkan sebelum Anda menggunakannya, hasilnya telah terhapus oleh pembaruan. Untuk database dengan tekanan pembaruan yang tinggi, tingkat hit cache kueri akan sangat rendah. Kecuali jika bisnis Anda memiliki tabel statis yang hanya akan diperbarui sekali dalam jangka waktu yang lama. Misalnya, jika tabel konfigurasi sistem, maka kueri pada tabel ini cocok untuk cache kueri.

Untungnya, MySQL juga menyediakan metode "use on demand" ini. Anda dapat mengatur parameter query_cache_type ke DEMAND sehingga cache kueri tidak digunakan untuk pernyataan SQL default. Untuk pernyataan yang Anda yakin ingin menggunakan cache kueri, Anda dapat menggunakan SQL_CACHE untuk menentukannya secara eksplisit, seperti pernyataan berikut:

select SQL_CACHE * from T where ID=10；
1

yang perlu diwaspadai adalah,Versi MySQL 8.0 langsung menghapus seluruh fungsi cache kueri, yang berarti fungsi ini tidak lagi tersedia mulai versi 8.0.

penganalisa

Jika cache kueri tidak tercapai, eksekusi pernyataan yang sebenarnya akan dimulai. Pertama, MySQL perlu mengetahui apa yang ingin Anda lakukan, sehingga perlu mengurai pernyataan SQL.

pengoptimal

Aktuator

2. Sistem logging: Bagaimana pernyataan pembaruan SQL dijalankan?

buat ulang log

Entah apakah Anda masih ingat artikel "Kong Yiji". Manajer hotel memiliki papan berwarna merah muda yang khusus digunakan untuk mencatat catatan kredit para tamu. Jika tidak banyak orang yang membayar secara kredit, maka ia dapat menuliskan nama dan rekening nasabahnya di papan tulis. Namun jika terlalu banyak orang yang memiliki rekening kredit, akan selalu ada saatnya fan board tidak dapat melacaknya. Pada saat ini, penjaga toko harus memiliki buku besar yang khusus untuk mencatat rekening kredit.

Jika seseorang ingin melunasi kredit atau melunasi hutang, pemilik toko umumnya mempunyai dua pilihan:

• Salah satu caranya adalah dengan langsung membuka buku besar dan menambah atau mengurangi rekening kredit;
• Pendekatan lain adalahKali ini tulis dulu rekeningnya di papan merah muda, lalu keluarkan buku rekening setelah waktu tutup dan hitung.。

Saat bisnis sedang booming dan konter sibuk, pemilik toko pasti akan memilihyang terakhir , karena operasi sebelumnya terlalu merepotkan. Pertama, Anda harus mencari catatan total rekening kredit orang tersebut. Coba pikirkan, ada puluhan halaman yang padat. Untuk menemukan namanya, penjaga toko mungkin harus memakai kacamata baca dan mencari perlahan. Setelah menemukannya, dia akan mengeluarkan sempoa untuk menghitung, dan akhirnya menulis kembali hasilnya buku besar.

Seluruh proses ini sulit untuk dipikirkan. Sebaliknya, lebih mudah untuk menuliskannya di papan berwarna merah muda terlebih dahulu. Coba pikirkan, jika penjaga toko tidak mendapat bantuan papan merah muda, dia harus membalik buku besar setiap kali dia mencatat akun.

Demikian pula, masalah ini juga terjadi di MySQL. Jika setiap operasi pembaruan perlu ditulis ke disk, dan disk juga harus menemukan catatan yang sesuai sebelum memperbarui, biaya IO dan biaya pencarian seluruh proses akan sangat tinggi. Untuk mengatasi masalah ini, para perancang MySQL menggunakan ide yang mirip dengan papan merah muda penjaga toko hotel untuk meningkatkan efisiensi pembaruan.

Seluruh proses kerjasama antara pink board dan ledger sebenarnya adalah apa yang sering disebutkan di MySQL. WAL teknologi,WAL Nama lengkapnya adalahWrite-Ahead Logging, poin kuncinya adalahTulis log terlebih dahulu, lalu tulis ke diskArtinya, tulislah papan merah jambu terlebih dahulu, kemudian tulislah buku rekening bila tidak sedang sibuk.

Khususnya, ketika suatu catatan perlu diperbarui, mesin InnoDB pertama-tama akan menulis catatan tersebut ke log pengulangan (papan merah muda) dan memperbarui memori. Pada saat ini, pembaruan telah selesai. Pada saat yang sama, mesin InnoDB akan memperbarui catatan operasi ke disk pada waktu yang tepat, dan pembaruan ini sering kali dilakukan ketika sistem relatif menganggur, seperti yang dilakukan penjaga toko setelah penutupan.

Jika rekening kredit hari ini tidak banyak, pemilik toko dapat menunggu hingga waktu tutup untuk memilah barang. Namun apa yang harus kita lakukan jika pada hari tertentu rekening pulsa banyak dan papan merah mudanya penuh? Pada saat ini, penjaga toko harus meletakkan pekerjaannya, memperbarui beberapa catatan kredit di papan merah muda ke buku besar, dan kemudian menghapus catatan ini dari papan merah muda untuk memberi ruang bagi rekening baru.

Demikian pula, log ulang InnoDB memiliki ukuran tetap. Misalnya, dapat dikonfigurasi sebagai satu set 4 file, setiap file berukuran 1GB. Kemudian "papan merah muda" ini dapat merekam total operasi 4GB. Mulailah menulis dari awal, lalu kembali ke awal untuk menulis secara berulang-ulang, seperti terlihat pada gambar di bawah ini.

write pos adalah posisi record saat ini, bergerak mundur saat menulis. Setelah menulis ke akhir file No. 3, ia kembali ke awal file No. 0. Pos pemeriksaan adalah posisi saat ini yang akan dihapus, dan juga bergerak maju dan berulang. Sebelum menghapus catatan, catatan harus diperbarui ke file data.

Jarak antara pos tulis dan pos pemeriksaan adalah bagian kosong dari "papan merah muda" yang dapat digunakan untuk mencatat operasi baru. Jika pos tulis berhasil mencapai pos pemeriksaan, itu berarti "papan merah muda" sudah penuh, dan tidak ada pembaruan baru yang dapat dilakukan saat ini. Anda harus berhenti dan menghapus beberapa catatan terlebih dahulu untuk memajukan pos pemeriksaan.

Dengan redo log, InnoDB dapat memastikan bahwa meskipun database dimulai ulang secara tidak normal, catatan yang dikirimkan sebelumnya tidak akan hilangcrash-safe。

Untuk memahami konsep aman dari kecelakaan, pikirkan contoh catatan kredit kita sebelumnya. Selama catatan kredit dicatat di papan merah muda atau ditulis di buku besar, meskipun kemudian pemilik toko lupa, seperti tiba-tiba menghentikan usahanya selama beberapa hari, dia tetap dapat mengklarifikasi rekening kredit tersebut melalui data di buku besar dan papan merah muda setelah melanjutkan bisnis.

binlog

Seperti yang kami katakan sebelumnya, MySQL secara keseluruhan sebenarnya memiliki dua bagian: satu adalah lapisan server, yang terutama melakukan hal-hal pada tingkat fungsional MySQL; yang lainnya adalah lapisan mesin, yang bertanggung jawab atas hal-hal spesifik yang berkaitan dengan penyimpanan.Papan merah muda yang kita bicarakan di atasredo log adalah log unik untuk mesin InnoDB,Dan Lapisan Server juga memiliki lognya sendiri, yang disebut binlog (log arsip)。

Saya rasa Anda akan bertanya, mengapa ada dua log?

Karena pada awalnya tidak ada mesin InnoDB di MySQL. Mesin MySQL sendiri adalah MyISAM, tetapi MyISAM tidak memiliki kemampuan anti-crash, dan log binlog hanya dapat digunakan untuk pengarsipan. InnoDB diperkenalkan ke MySQL dalam bentuk plug-in oleh perusahaan lain. Karena hanya mengandalkan binlog tidak memiliki kemampuan anti-crash, InnoDB menggunakan sistem log lain, yaitu redo log, untuk mencapai kemampuan anti-crash.

Kedua log ini memiliki tiga perbedaan berikut.

1. Log redo unik untuk mesin InnoDB; binlog diimplementasikan oleh lapisan server MySQL dan dapat digunakan oleh semua mesin.
2. redo log adalah log fisik, mencatat "modifikasi apa yang dilakukan pada halaman data tertentu";binlog adalah log logis, yang dicatat adalah logika asli dari pernyataan ini, seperti "tambahkan 1 ke bidang c pada baris dengan ID=2".
3. redo log ditulis dalam satu lingkaran, ruang akan habis;binlog dapat ditulis tambahan . "Tambahkan tulisan" berarti setelah file binlog mencapai ukuran tertentu, file tersebut akan beralih ke file berikutnya dan tidak akan menimpa log sebelumnya.

Dengan pemahaman konseptual tentang kedua log ini, mari kita lihat proses internal eksekutor dan mesin InnoDB saat menjalankan pernyataan pembaruan sederhana ini.

1. Pelaksana terlebih dahulu mencari mesin untuk mendapatkan baris ID=2. ID adalah kunci utama, dan mesin langsung menggunakan pencarian pohon untuk menemukan baris ini. Jika halaman data tempat baris dengan ID=2 berada sudah ada di memori, maka akan dikembalikan langsung ke pelaksana; jika tidak, perlu dibaca ke dalam memori dari disk terlebih dahulu dan kemudian dikembalikan.
2. Pelaksana mendapatkan data baris yang diberikan oleh mesin, menambahkan 1 ke nilai ini, misalnya dulu N, tetapi sekarang menjadi N+1, mendapatkan baris data baru, lalu memanggil antarmuka mesin untuk menulis ini baris data baru.
3. Mesin memperbarui baris data baru ini ke dalam memori dan mencatat operasi pembaruan di log pengulangan buat ulang log di dalammempersiapkan negara. Kemudian informasikan kepada pelaksana bahwa eksekusi telah selesai dan transaksi dapat diserahkan kapan saja.
4. Pelaksana membuat binlog dari operasi ini dan menempatkannya binlog ditulis ke disk。
5. Pelaksana memanggil antarmuka transaksi komit mesin, dan mesin menulis buat ulang log Ubah untuk mengirimkan (melakukan) status, pembaruan selesai.

Di sini saya memberikan diagram alur eksekusi pernyataan pembaruan ini. Kotak terang pada gambar menunjukkan bahwa itu dijalankan di dalam InnoDB, dan kotak gelap menunjukkan bahwa itu dijalankan di eksekutor.

perbarui proses eksekusi pernyataan

Anda mungkin telah memperhatikan bahwa tiga langkah terakhir tampak agak "melingkar". Penulisan log ulang dibagi menjadi dua langkah: persiapan dan penerapan.

komitmen dua fase

Mengapa perlu adanya “penyerahan dua tahap”?Hal ini untuk memungkinkan perbedaan antara kedua logkonsisten secara logis . Untuk menjelaskan masalah ini, kita harus mulai dengan pertanyaan di awal artikel: Bagaimana cara mengembalikan database ke keadaan setiap detik dalam waktu setengah bulan?

Seperti yang kami katakan sebelumnya, binlog akan mencatat semua operasi logis dan mengadopsi bentuk "tambahkan tulisan". Jika DBA Anda menjanjikan dapat dipulihkan dalam waktu setengah bulan, maka sistem pencadangan pasti akan menyimpan semua binlog dalam setengah bulan terakhir, dan sistem akan melakukan pencadangan rutin seluruh database. Yang "reguler" di sini tergantung pada pentingnya sistem, bisa sehari sekali atau seminggu sekali.

Ketika Anda perlu memulihkan ke detik tertentu, misalnya, pada pukul dua siang suatu hari, Anda menemukan bahwa sebuah tabel tidak sengaja terhapus pada siang hari, dan Anda perlu mengambil datanya, Anda dapat melakukan ini:

• Pertama, temukan cadangan lengkap terbaru. Jika Anda beruntung, itu mungkin cadangan dari tadi malam, dan pulihkan dari cadangan ini ke database sementara;
• Kemudian, mulai dari titik waktu pencadangan, binlog cadangan diambil secara berurutan, dan diputar ulang ke waktu sebelum tabel terhapus secara tidak sengaja pada siang hari.
  Dengan cara ini, database sementara Anda akan sama dengan database online sebelum Anda tidak sengaja menghapusnya. Kemudian Anda dapat mengeluarkan data tabel dari database sementara dan mengembalikannya ke database online sesuai kebutuhan.

Oke, setelah membahas proses pemulihan data, mari kita kembali dan membahas mengapa log memerlukan "komit dua fase". Di sini kita sebaiknya menggunakan bukti dengan kontradiksi untuk menjelaskan.

Karena redo log dan binlog adalah dua logika independen, jika penerapan dua fase tidak digunakan, maka redo log harus ditulis terlebih dahulu lalu binlog harus ditulis, atau urutan sebaliknya harus digunakan. Mari kita lihat masalah apa saja yang ada pada kedua metode ini.

Masih menggunakan pernyataan update sebelumnya sebagai contoh. Asumsikan bahwa nilai bidang c pada baris saat ini dengan ID=2 adalah 0, dan anggaplah bahwa selama eksekusi pernyataan pembaruan, kerusakan terjadi setelah log pertama ditulis tetapi sebelum log kedua ditulis. Apa yang akan terjadi?

• Tulis redo log terlebih dahulu lalu binlog.
  Misalkan proses MySQL dimulai ulang secara tidak normal ketika log pengulangan ditulis tetapi sebelum binlog ditulis. Seperti yang kami katakan sebelumnya, setelah log pengulangan ditulis, meskipun sistem crash, data masih dapat dipulihkan, jadi nilai c pada baris ini setelah pemulihan adalah 1. Namun, karena binlog mogok sebelum selesai, pernyataan ini tidak dicatat di binlog saat ini. Oleh karena itu, ketika log dicadangkan nanti, pernyataan ini tidak akan disertakan dalam binlog yang disimpan. Kemudian Anda akan menemukan bahwa jika Anda perlu menggunakan binlog ini untuk memulihkan perpustakaan sementara, karena binlog dari pernyataan ini hilang, perpustakaan sementara tidak akan diperbarui kali ini. Nilai c pada baris yang dipulihkan adalah 0, yaitu sama dengan nilai perpustakaan aslinya berbeda.
• Tulis binlog terlebih dahulu lalu ulangi log.
  Jika terjadi crash setelah binlog ditulis, karena redo log belum ditulis, maka transaksi akan menjadi tidak valid setelah crash recovery, sehingga nilai c pada baris ini adalah 0. Namun log "Ubah c dari 0 ke 1" telah tercatat di binlog. Oleh karena itu, ketika binlog digunakan untuk memulihkan nanti, akan keluar satu transaksi lagi. Nilai c pada baris yang dipulihkan adalah 1, berbeda dengan nilai pada database asli.
  Dapat dilihat bahwa jika "komitmen dua fase" tidak digunakan, status database mungkin tidak konsisten dengan status perpustakaan yang dipulihkan menggunakan lognya.

Anda mungkin berkata, apakah kemungkinan ini sangat rendah? Tidak ada situasi di mana perpustakaan sementara perlu dipulihkan kapan saja.

Sebenarnya tidak, proses ini tidak hanya diperlukan untuk memulihkan data setelah kesalahan pengoperasian. Ketika Anda perlu memperluas kapasitas, yaitu ketika Anda perlu membangun lebih banyak database cadangan untuk meningkatkan kapasitas baca sistem, praktik umum sekarang adalah menggunakan cadangan penuh dan menerapkan binlog untuk mencapai hal ini adalah ketidakkonsistenan antara database master dan slave online.

Sederhananya, redo log dan binlog dapat digunakan untuk mewakili status penerapan suatu transaksi, danPengajuan dua fase bertujuan untuk menjaga kedua negara tetap konsisten secara logis.。