informasi kontak saya
Surat[email protected]
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
Isi log MySQL sangat penting dan sering ditanyakan saat wawancara. Pada saat yang sama, menguasai pengetahuan terkait log juga akan membantu kita memahami prinsip dasar MySQL dan membantu kita memecahkan masalah serta memecahkan masalah bila diperlukan.
Jenis log umum di MySQL terutama mencakup kategori berikut (untuk mesin penyimpanan InnoDB):
Log biner (binlog) dan log transaksi (redo log dan undo log) lebih penting dan memerlukan fokus kita.
Log kueri lambat mencatat semua pernyataan kueri yang waktu eksekusinya melebihi long_query_time (defaultnya adalah 10 detik, biasanya disetel ke 1 detik). Ini sering digunakan saat menyelesaikan masalah kueri lambat SQL (waktu eksekusi SQL terlalu lama).
Menemukan SQL yang lambat adalah langkah pertama untuk mengoptimalkan kinerja pernyataan SQL. Kemudian gunakan perintah EXPLAIN untuk menganalisis SQL yang lambat dan mendapatkan informasi tentang rencana eksekusi.
Anda dapat menggunakan variabel acara seperti perintah "slow_query_log";
Dapat diaktifkan dengan SET GLOBAL slow_query_log=ON
Parameter long_query_time menentukan berapa lama waktu yang dibutuhkan suatu kueri sebelum dapat didefinisikan sebagai kueri lambat. Defaultnya adalah 10 detik. Anda dapat melihatnya melalui perintah SHOW VARIABLES LIKE'%long_query_time%';
Itu juga dapat dimodifikasi: setel global long_query_time = 12
Dalam proyek sebenarnya, log kueri lambat mungkin relatif besar dan tidak nyaman untuk menganalisisnya secara langsung. Kita dapat menggunakan alat analisis dan penyetelan kueri lambat MySQL resmi. mysqldumps lambat . Blog saya juga cukup terhubung ke alat mysqldumpslow:
[MySQL] alat mysqldumpslow--meringkas file log kueri yang lambat-Blog CSDN
Ada variabel di MySQL yang mencatat jumlah pernyataan kueri lambat saat ini. Anda dapat menggunakan tampilkan status global seperti '%slo
w_queries%'; perintah untuk melihat.
MySQL memberi kitaMENJELASKANperintah untuk memperoleh informasi tentang rencana eksekusi.
Rencana eksekusi mengacu pada metode eksekusi spesifik dari pernyataan SQL setelah dioptimalkan oleh pengoptimal kueri MySQL. Rencana eksekusi biasanya digunakan dalam skenario seperti analisis dan pengoptimalan kinerja SQL. Melalui hasil EXPLAIN, Anda dapat mempelajari informasi seperti urutan kueri tabel data, jenis operasi operasi kueri data, indeks mana yang dapat dipukul, indeks mana yang sebenarnya akan dipukul, berapa baris record dalam setiap data tabel ditanyakan, dan informasi lainnya. Secara khusus, kita dapat mengoptimalkan SQL melalui beberapa metode umum di bawah ini:
1. Hindari penggunaan SELECT *
SELECT * mengkonsumsi lebih banyak CPU.
SELECT *Bidang yang tidak berguna meningkatkan konsumsi sumber daya bandwidth jaringan dan waktu transmisi data, terutama bidang yang besar (seperti varchar,
gumpalan、teks)
SELECT * tidak dapat menggunakan pengoptimal MySQL untuk mencakup pengoptimalan indeks (strategi "mencakup indeks" berdasarkan pengoptimal MySQL sangat cepat dan efisien, dan merupakan metode pengoptimalan kueri yang sangat direkomendasikan di industri)
SELECT <field list> dapat mengurangi dampak perubahan struktur tabel.
2. Optimasi paginasi
Paging biasa membutuhkan waktu yang relatif singkat ketika jumlah datanya sedikit.
Jika jumlah data menjadi besar, mencapai jutaan bahkan puluhan juta, paging biasa akan memakan waktu yang sangat lama.
Bagaimana cara mengoptimalkannya? Anda dapat mengubah pernyataan SQL di atas menjadi subquery.
Pertama-tama kita menanyakan nilai kunci utama yang sesuai dengan parameter batas pertama, lalu memfilter dan membatasi berdasarkan nilai kunci utama ini, sehingga efisiensinya akan lebih cepat. Namun, metode ini hanya berfungsi jika id berada dalam urutan positif.
Namun, hasil subkueri akan menghasilkan tabel baru, yang akan mempengaruhi kinerja. Penggunaan subkueri secara ekstensif harus dihindari. Selain itu, metode ini hanya berlaku jika ID berada dalam urutan positif. Dalam skenario paging yang kompleks, sering kali perlu memfilter ID yang memenuhi ketentuan melalui kondisi pemfilteran. Pada saat ini, ID bersifat terpisah dan terputus-putus.
3. Kurangi bergabung
Panduan Pengembangan Alibaba:
Anda dapat membaca diskusi di Zhihu:
https://www.zhihu.com/pertanyaan/68258877
https://www.zhihu.com/pertanyaan/682588774. Disarankan untuk tidak menggunakan kunci asing dan kaskade
Panduan Pengembangan Java Alibaba:
5. Pilih jenis bidang yang sesuai
6. Coba gunakan UNION ALL, bukan UNION
UNION akan memasukkan semua data dari dua kumpulan hasil ke dalam tabel sementara dan kemudian melakukan operasi deduplikasi, yang lebih memakan waktu dan menghabiskan lebih banyak sumber daya CPU.
UNION ALL tidak akan lagi menghapus duplikat kumpulan hasil, dan data yang diperoleh berisi item duplikat.
Namun, jika duplikat data tidak diperbolehkan dalam skenario bisnis sebenarnya, UNION masih dapat digunakan.
7. Operasi batch
Untuk pembaruan data dalam database, jika operasi batch dapat digunakan, gunakan sebanyak mungkin untuk mengurangi jumlah permintaan database dan meningkatkan kinerja.
8. Gunakan indeks dengan benar
Ada banyak konten di bagian ini, yang nanti akan diperkenalkan di blog terpisah.
binlog (log biner adalah file log biner) terutama mencatat semua operasi yang telah diubah pada database MSQL (semua pernyataan DDL dan DML dijalankan oleh database), termasuk perubahan struktur tabel (CREATE, ALTER, DROP TABLE.), data tabel modifikasi ( INSERT.UPDATE, DELETE..), tetapi tidak termasuk SELECT, SHOW dan operasi lain yang tidak menyebabkan perubahan pada database.
Anda dapat menggunakan perintah tampilkan log biner;
Ada 3 jenis metode pencatatan biner:
Dibandingkan dengan mode Baris, file log dalam mode pernyataan lebih kecil, tekanan IO disk juga lebih kecil, dan kinerja lebih baik. Namun, akurasinya lebih buruk dibandingkan mode Baris.
Sebelum MySQL 5.1.5, format binlog hanya STATEMENT. 5.1.5 mulai mendukung binlog dalam format ROW. Mulai dari versi 5.1.8, MySQL mulai mendukung binlog dalam format MIXED. Sebelum MySQL 5.7.7, mode Pernyataan digunakan secara default. MySQL5.7.7 menggunakan mode Baris secara default.
Anda dapat menggunakan variabel acara seperti'%binlog format%';
Skenario penerapan utama binlog adalah replikasi master-slave. Master-slave, master-master, dan master-slave semuanya tidak dapat dipisahkan dari binlog. Binlog perlu diandalkan untuk menyinkronkan data dan memastikan konsistensi data.
Prinsip replikasi master-slave ditunjukkan pada gambar di bawah ini:
1. Perpustakaan utama menulis perubahan data dalam database ke binlog
2. Hubungkan perpustakaan budak ke perpustakaan utama
3. Perpustakaan budak akan membuat thread I0 untuk meminta binlog yang diperbarui dari perpustakaan utama.
4. Perpustakaan utama akan membuat thread dump binlog untuk mengirim binlog, dan thread I/0 di perpustakaan budak bertanggung jawab untuk menerima. 5. Thread I/0 dari perpustakaan budak akan menulis binlog yang diterima ke dalam relai catatan.
6. Baca log relai dari thread SQL perpustakaan dan sinkronkan data secara lokal (yaitu, jalankan SQL lagi)
Untuk mesin penyimpanan InnoDB, selama eksekusi transaksi, log pertama-tama akan ditulis ke binlogcache. Hanya ketika transaksi dikirimkan, log di binlogcache akan disimpan ke file binlog di disk. Menulis ke memori lebih cepat, dan ini juga dilakukan untuk alasan efisiensi.
Karena binlog suatu transaksi tidak dapat dipecah, sebesar apa pun transaksinya, transaksi tersebut harus ditulis satu kali, sehingga sistem akan mengalokasikan satu blok memori ke setiap thread sebagai cache binlog. Kita dapat mengontrol ukuran binlogcache dari satu thread melalui parameter binlog_cache_size. Jika konten penyimpanan melebihi parameter ini, maka harus disimpan sementara di disk (swap).
Jadi kapan binlog dipindahkan ke disk? Anda dapat mengontrol waktu pembilasan biglog melalui parameter sync_binlog.
·0: Tidak ada persyaratan wajib, sistem akan memutuskan kapan akan menulis ke disk.
·1: Setiap kali transaksi dikirimkan, binlog harus ditulis ke disk:
·N: Binlog akan ditulis ke disk setiap N transaksi.Resiko kerugian
Sebelum MySQL5.7, nilai default sync_binlog adalah 0. Setelah MySQL5.7, nilai default sync_binlog adalah 1. Secara umum, tidak disarankan untuk menyetel nilai sync_binlog ke 0. Jika persyaratan kinerja relatif tinggi atau terjadi kemacetan IO disk, nilai sync_binlog dapat ditingkatkan secara tepat. Namun, hal ini akan meningkatkan risiko kehilangan data.
Ketika menghadapi tiga situasi berikut, MySQL akan membuat ulang file log baru, dan nomor seri file akan bertambah.
Kita tahu bahwa mesin penyimpanan InnoD8 mengatur ruang penyimpanan dalam satuan halaman. Data yang kita masukkan ke MySQL pada akhirnya ada di halaman. Untuk mengurangi overhead IO disk, ada juga area yang disebut Buffer Pool, yang ada di memori. Ketika halaman yang sesuai dengan data kita tidak ada di Buffer Pool, MSQL pertama-tama akan menyimpan halaman tersebut di disk ke dalam Buffer Pool, sehingga nanti kita langsung mengoperasikan halaman tersebut di Buffer Pool, yang sangat meningkatkan kinerja baca dan tulis. .
Setelah transaksi dilakukan, modifikasi kami pada halaman terkait di Buffer Pool mungkin tidak disimpan ke disk. Saat ini, jika MySQL tiba-tiba crash, apakah perubahan pada transaksi ini akan langsung hilang?
Jelas tidak, jika demikian jelas akan melanggar keberlangsungan transaksi.
Mesin MySQLInnoDB menggunakan redo log untuk memastikan ketahanan transaksi. Hal utama yang dilakukan redo log adalah mencatat modifikasi halaman, seperti berapa byte yang telah dimodifikasi pada offset tertentu pada halaman tertentu dan konten spesifik apa yang dimodifikasi. Setiap catatan dalam log pengulangan berisi nomor ruang tabel, nomor halaman data, offset, data spesifik yang dimodifikasi, dan bahkan mungkin mencatat panjang data yang diubah (tergantung pada jenis log pengulangan).
Ketika transaksi selesai, kami akan membuang log pengulangan ke disk sesuai dengan strategi pembilasan. Dengan cara ini, bahkan jika MySQL mogok, data yang gagal ditulis ke disk dapat dipulihkan setelah dimulai ulang, sehingga memastikan ketahanannya. dari transaksi tersebut. Dengan kata lain, redo log memberikan kemampuan pemulihan kerusakan MySQL.
Redo Log mencatat semua operasi modifikasi ke database. Ketika database melakukan operasi tulis (INSERT, UPDATE, DELETE), operasi ini pertama-tama akan dicatat di Redo Log dan kemudian diterapkan ke file data. Dengan cara ini, bahkan jika sistem gagal sebelum operasi modifikasi data ditulis sepenuhnya ke disk, Redo Log dapat memastikan bahwa data tidak hilang. Selama pemulihan, database akan mengulangi operasi modifikasi yang belum selesai ini dari Redo Log untuk memastikan konsistensi data.
Redo Log membantu database pulih ke keadaan konsisten setelah sistem crash atau pemadaman listrik yang tidak terduga. Selama proses pemulihan, database akan memeriksa catatan di Redo Log dan menerapkan kembali semua modifikasi data yang dikirimkan tetapi tidak bertahan pada file data untuk memulihkan data.
Untuk meningkatkan kinerja operasi penulisan, database sering kali menggunakan mekanisme caching (seperti kumpulan buffer) untuk menyimpan sementara operasi modifikasi di memori daripada langsung menuliskannya ke disk. Adanya Redo Log memungkinkan mekanisme caching ini, karena selama Redo Log dipastikan masih ada, maka tidak ada risiko kehilangan data meskipun data di cache belum ditulis ke disk.
Saat melakukan transaksi, log pengulangan di buffer log akan dipindahkan ke disk
melakukan kontrol parameter. Kita harus memperhatikan pengaturan kebijakan flush yang benar innodb_flush_log_at_trx_commit. Tergantung pada strategi flush yang dikonfigurasi di MySQL, mungkin ada sedikit masalah kehilangan data setelah MySQL mati.
innodb_flush_log_at_trx_commit Ini adalah parameter konfigurasi penting dalam mesin penyimpanan MySQL InnoDB. Ini menentukan strategi flush (flush) dan menulis (write) log ketika transaksi dikirimkan, sehingga mempengaruhi ketahanan dan kinerja data. Terdapat tiga nilai yaitu 0, 1 dan 2. Setiap nilai mewakili strategi menyikat gigi yang berbeda.
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
Ringkasan strategi kuas
1. Log redo ditulis ke buffer log tetapi belum ditulis ke cache halaman. Pada saat ini, database crash dan terjadi kehilangan data (kehilangan data ini dapat terjadi ketika nilai kebijakan flush innodb_flush log_at trx_commit adalah 0);
2. Log redo telah ditulis ke cache halaman tetapi belum ditulis ke disk. Sistem operasi akan crash dan kehilangan data dapat terjadi (kehilangan data ini dapat terjadi ketika nilai kebijakan flush innodb2 flush log_at trx_commit adalah 2).
Undo Log (rollback log) adalah log yang digunakan dalam sistem database untuk mencatat operasi modifikasi data. Ini mencatat operasi kebalikan (yaitu operasi pembatalan) dari semua operasi modifikasi pada data selama eksekusi transaksi. Undo Log memainkan peran penting dalam rollback transaksi. Melalui Undo Log, data dapat dikembalikan ke keadaan sebelum transaksi dimulai, sehingga memastikan atomisitas transaksi.
Atomisitas suatu transaksi berarti bahwa semua operasi transaksi dieksekusi semua atau tidak ada yang dieksekusi. Undo Log memastikan atomisitas transaksi melalui mekanisme berikut:
Rekam pembatalan operasi Selama pelaksanaan transaksi, setiap operasi modifikasi pada data akan mencatat operasi pembatalan terkait di Log Pembatalan sebelum modifikasi sebenarnya. Misalnya, jika suatu transaksi memperbarui nilai dari sebaris catatan, nilai lama akan dicatat dalam Undo Log sebelum diperbarui.
Transaksi pengembalian Jika transaksi gagal karena alasan tertentu (seperti kesalahan atau rollback eksplisit), sistem database membaca Undo Log dan mengembalikan data ke keadaan sebelum transaksi dimulai sesuai dengan operasi pembatalan yang tercatat. Dengan cara ini, Anda dapat memastikan bahwa transaksi yang gagal tidak berdampak pada database, sehingga menjamin atomisitas transaksi.
Mengutip:
Penjelasan rinci tentang pemisahan baca-tulis dan sub-database serta tabel |
Ia telah mengabdikan dirinya untuk meneliti teknologi selama lebih dari 30 tahun, dan mahir dalam berbagai bahasa seperti java, linux, javascript, php, css, dll. Ia telah memberikan banyak kontribusi di bidang open source stasiun dokumentasi pengembang untuk berbagi beberapa masalah dalam pengembangan teknologi untuk referensi di masa mendatang. Semua orang memeriksanya
Surat[email protected]