66 soalan temu bual untuk membantu anda menyusun mata pengetahuan MySQL!

Sebagai Budak SQL, tidak akan ada sesiapa yang tidak tahu asasnya, bukan? Tidak banyak soalan dalam temuduga Kawan yang mempunyai pengetahuan asas yang baik boleh skip bahagian ini. Sudah tentu, anda boleh menulis beberapa pernyataan SQL di tempat kejadian boleh dipraktikkan melalui laman web seperti Niuke, LeetCode dan LintCode.

1. Apakah cantuman dalam, cantuman luar, cantuman silang dan produk Cartesian?

• Penyertaan dalaman: Dapatkan rekod daripada dua jadual yang memenuhi perhubungan padanan sambungan.
• Caburan luar: Bukan sahaja memperoleh rekod dalam dua jadual yang memenuhi perhubungan pemadanan sambungan, tetapi juga termasuk rekod dalam jadual tertentu (atau dua jadual) yang tidak memenuhi perhubungan padanan.
• Cross join: Memaparkan satu-dengan-satu surat-menyurat antara semua rekod dalam dua jadual. Ia adalah pelaksanaan produk Cartesian dalam SQL Jika jadual A mempunyai m baris dan jadual B mempunyai n garis, maka hasil sambungan silang A dan B mempunyai garis m*n.
• Produk Cartesian: Ia adalah konsep dalam matematik Contohnya, set A={a,b}, set B={1,2,3}, kemudian A✖️B={,,,,,}.

2. Apakah perbezaan antara sambung dalam MySQL, sambung kiri dan sambung kanan?

Sambungan MySQL terbahagi terutamanya kepada sambungan dalam dan sambungan luar yang biasa digunakan termasuk sambung kiri dan sambung kanan.

• cantuman dalaman cantuman dalaman, apabila dua jadual disambungkan untuk pertanyaan, hanya set hasil yang sepadan sepenuhnya dalam kedua-dua jadual itu dikekalkan
• sisa sertai Apabila dua jadual disambungkan untuk pertanyaan, semua baris dari jadual kiri akan dikembalikan, walaupun tiada rekod yang sepadan dalam jadual kanan.
• cantuman kanan akan mengembalikan semua baris dalam jadual kanan apabila melakukan pertanyaan sambungan antara dua jadual, walaupun tiada rekod yang sepadan dalam jadual kiri.

3. Bercakap tentang tiga paradigma utama pangkalan data?

• Bentuk biasa pertama: Setiap lajur (setiap medan) dalam jadual data tidak boleh dipisahkan. Sebagai contoh, dalam jadual pengguna, alamat pengguna juga boleh dibahagikan kepada negara, wilayah dan bandar, supaya ia mematuhi paradigma pertama.
• Bentuk normal kedua: Berdasarkan bentuk normal pertama, lajur bukan kunci utama bergantung sepenuhnya pada kunci primer dan tidak boleh menjadi sebahagian daripada kunci primer. Contohnya, dalam jadual pesanan, maklumat produk (harga produk, jenis produk) disimpan, jadi ID produk dan ID pesanan perlu digunakan sebagai kunci utama bersama untuk memenuhi paradigma kedua.
• Bentuk normal ketiga: Atas dasar memenuhi bentuk normal kedua, kunci bukan utama dalam jadual hanya bergantung pada kunci primer dan tidak bergantung pada kunci bukan utama yang lain. Sebagai contoh, jadual pesanan tidak boleh menyimpan maklumat pengguna (nama, alamat).

Peranan tiga paradigma utama adalah untuk mengawal redundansi pangkalan data dan menjimatkan ruang Malah, reka bentuk syarikat Internet am adalah anti-paradigma , dengan melebihkan beberapa data, elakkan jadual silang dan pangkalan data silang, gunakan ruang untuk masa dan tingkatkan prestasi.

4. Apakah perbezaan antara varchar dan char?

char:

• char mewakili rentetan panjang tetap, panjangnya tetap; > Jika panjang data yang dimasukkan kurang daripada panjang tetap char, ia diisi dengan ruang
• Kerana panjangnya tetap, kelajuan akses jauh lebih cepat daripada varchar, malah 50% lebih cepat, tetapi kerana panjangnya yang tetap, Oleh itu, ia akan menduduki ruang tambahan, yang merupakan cara menukar ruang dengan masa
• Untuk char, bilangan maksimum aksara yang boleh disimpan ialah 255, yang tiada kaitan; dengan pengekodan

varchar

:

varchar mewakili rentetan panjang berubah dan panjangnya berubah

• Yang disisipkan data akan disimpan selagi ia; , yang merupakan cara menukar masa untuk ruang;
• Untuk varchar Said, bilangan maksimum aksara yang boleh disimpan ialah 65532
• Dalam reka bentuk harian, untuk rentetan yang agak tetap panjang, char boleh digunakan Untuk rentetan dengan panjang yang tidak pasti, varchar adalah lebih sesuai.
• 5. Apakah perbezaan antara gumpalan dan teks?

blob digunakan untuk menyimpan data binari, manakala teks digunakan untuk menyimpan rentetan besar.

Blob tidak mempunyai set aksara, teks mempunyai set aksara, dan nilai diisih dan dibandingkan mengikut peraturan pengumpulan set aksara

• 6 adakah persamaan dan perbezaan antara DATETIME dan TIMESTAMP?

Mata yang sama

:

Kedua-dua jenis data menyimpan masa dalam format yang sama. Kedua-duanya ialah

Kedua-dua jenis data mengandungi bahagian "tarikh" dan "masa".
0. YYYY-MM-DD HH:MM:SSKedua-dua jenis data boleh menyimpan saat pecahan dalam mikrosaat (6 saat perpuluhan selepas saat)
2. Perbezaan
:

• Julat tarikh: Julat tarikh DATETIME ialah 1000-01-01 00:00:00.000000 hingga 9999-12-31 23:59:59.999999 julat masa TIMESTAMP ialah 1970-01-01 00:00:01.000000 UTC到 ``2038-01-09 03:14:07.999999 UTC

• Ruang storan: Ruang storan DATETIME ialah 8 bait ialah 4 bait

Nilai lalai : Nilai lalai DATETIME adalah batal; medan TIMESTAMP tidak kosong secara lalai (bukan null), dan nilai lalai ialah masa semasa (CURRENT_TIMESTAMP)

• 7 dalam dan wujud dalam MySQL Perbezaannya? Penyataan dalam dalam MySQL melakukan sambungan cincang antara jadual luaran dan jadual dalaman, manakala penyataan wujud melakukan gelung pada jadual luaran dan menanyakan jadual dalaman setiap kali gelung gelung. Kita mungkin berpendapat bahawa wujud adalah lebih cekap daripada dalam pernyataan Pernyataan ini sebenarnya tidak tepat Kita perlu membezakan antara senario:

Jika dua jadual yang ditanya mempunyai saiz yang sama, maka gunakan. dalam dan wujud tidak jauh berbeza.

Jika satu daripada dua jadual lebih kecil dan satu lagi adalah jadual besar, penggunaan wujud untuk jadual subkueri yang lebih besar dan dalam untuk jadual subkueri yang lebih kecil.
tidak masuk dan tidak wujud: Jika pernyataan pertanyaan menggunakan not in, maka imbasan jadual penuh akan dilakukan pada kedua-dua jadual dalam dan luar, dan tiada indeks akan digunakan; subquery bukan extsts masih boleh digunakan Indeks pada jadual. Jadi tidak kira jadual mana yang besar, menggunakan tidak wujud lebih cepat daripada tidak masuk.
8. Apakah jenis medan yang lebih baik untuk merekod mata wang dalam MySQL?

• Mata wang biasanya diwakili oleh jenis Perpuluhan dan Numrik dalam pangkalan data MySQL, dan kedua-dua jenis ini dilaksanakan sebagai jenis yang sama oleh MySQL. Ia digunakan untuk menyimpan data berkaitan mata wang.

Sebagai contoh, gaji PERPULUHAN(9,2), 9 (ketepatan) mewakili jumlah bilangan tempat perpuluhan yang akan digunakan untuk menyimpan nilai, dan 2 (skala) mewakili bilangan digit selepas perpuluhan titik yang akan digunakan untuk menyimpan nilai. Julat nilai yang disimpan dalam lajur gaji adalah dari -9999999.99 hingga 9999999.99.

Nilai PERPULUHAN dan ANGKA disimpan sebagai rentetan dan bukannya sebagai apungan binari untuk mengekalkan ketepatan perpuluhan bagi nilai tersebut.

Sebab mengapa float atau double tidak digunakan: Kerana float dan double disimpan dalam binari, terdapat ralat tertentu.

9. Bagaimanakah MySQL menyimpan emoji?

MySQL boleh terus menggunakan rentetan untuk menyimpan emoji.

Tetapi harus diingat bahawa pengekodan utf8 tidak boleh digunakan dalam MySQL ialah versi utf8 yang dikebiri Ia hanya menggunakan sehingga 3 bait untuk menyimpan aksara, jadi ia tidak boleh menyimpan ekspresi. Apa yang perlu dilakukan?

Memerlukan pengekodan utf8mb4.

10. Apakah perbezaan antara drop, delete dan truncate?

Ketiga-tiga min pemadaman, tetapi terdapat beberapa perbezaan antara ketiga-tiga:

Oleh itu, apabila jadual tidak diperlukan lagi, gunakan drop apabila anda ingin memadam beberapa baris data, gunakan padam apabila mengekalkan jadual tetapi memadam semua data, gunakan truncate.

11. Apakah perbezaan antara UNION dan UNION ALL?

• Jika UNION ALL digunakan, baris rekod pendua tidak akan digabungkan
• UNION lebih cekap daripada UNION ALL

12.count(1) , count Apakah perbezaan antara (*) dan count(nama lajur)?

Kesan pelaksanaan:

• count(*) termasuk semua lajur, bersamaan dengan bilangan baris, dalam statistik Apabila mengira hasil, nilai lajur yang NULL
• count(1) termasuk mengabaikan semua lajur, menggunakan 1 untuk mewakili baris kod Apabila mengira hasil, nilai lajur yang NULL

Kelajuan pelaksanaan:

Lajur ialah kunci utama, kiraan (nama lajur) akan lebih cepat daripada kiraan(1)
Jika nama lajur bukan kunci utama, kiraan(1) akan lebih cepat daripada kiraan(nama lajur)
• Jika jadual mempunyai berbilang lajur dan tiada kunci utama, kecekapan pelaksanaan kiraan(1 ) adalah lebih baik daripada count(*)
Jika terdapat kunci utama, kecekapan pelaksanaan kiraan pilih (kunci utama) adalah optimum
• Jika jadual hanya mempunyai satu medan, kemudian pilih kiraan (*) adalah optimum.

13. Apakah susunan pelaksanaan pernyataan pertanyaan SQL?

• DARI: Jalankan seruling di atas meja kiri DARI klausa Cartesian product (Cartesian product), menjana jadual maya VT1

• ON: Gunakan penapisan ON pada jadual maya VT1, hanya baris yang memenuhi

• SERTAI: Jika OUTER JOIN (seperti LEFT OUTER JOIN, KANAN OUTER JOIN) ditentukan, maka baris yang tidak sepadan dalam jadual dikekalkan apabila baris Luaran ditambahkan pada jadual maya VT2, menghasilkan jadual maya VT3. Jika klausa FROM mengandungi lebih daripada dua jadual, ulangi langkah 1) hingga 3) untuk jadual hasil VT3 yang dijana oleh sambungan sebelumnya dan jadual seterusnya sehingga semua jadual diproses

• WHERE: Gunakan syarat penapis WHERE pada jadual maya VT3 Hanya rekod yang memenuhi 🎜>: Kumpulkan rekod dalam VT4 mengikut lajur dalam klausa GROUP BY, menghasilkan VT5

• CUBE|ROLLUP: gandingkan jadual VT5 melakukan CUBE atau Operasi ROLLUP untuk menjana jadual VT6

• HAVING: Gunakan penapis HAVING pada jadual maya VT6 dan hanya rekod yang memenuhi .

• PILIH: Lakukan operasi SELECT untuk kali kedua, pilih lajur yang ditentukan dan masukkannya ke dalam jadual maya VT8

• BERBEZA: alih keluar data pendua dan jana jadual maya VT9

• PESANAN OLEH: proses rekod dalam jadual maya VT9 mengikut Operasi pengisihan menjana jadual maya VT10 11)

• LIMIT: Keluarkan rekod baris yang ditentukan, jana jadual maya VT11, dan kembalikan kepada pengguna pertanyaan

• Seni bina pangkalan data

  14 Bercakap tentang infrastruktur MySQL?

Seni bina logik MySQL. rajah terbahagi terutamanya kepada tiga lapisan:

• Klien: Perkhidmatan peringkat atas bukan unik untuk MySQL Kebanyakan alat atau perkhidmatan klien/pelayan berasaskan rangkaian mempunyai seni bina yang serupa. Seperti pemprosesan sambungan, pengesahan kebenaran, keselamatan, dsb.
• Lapisan pelayan: Kebanyakan fungsi perkhidmatan teras MySQL berada dalam lapisan ini, termasuk penghuraian pertanyaan, analisis, pengoptimuman, caching dan semua fungsi terbina dalam (seperti tarikh, masa, matematik dan fungsi penyulitan), semua fungsi enjin storan silang dilaksanakan dalam lapisan ini: prosedur tersimpan, pencetus, pandangan, dsb.
• Lapisan enjin storan: Lapisan ketiga mengandungi enjin storan. Enjin storan bertanggungjawab untuk penyimpanan dan mendapatkan semula data dalam MySQL. Lapisan Pelayan berkomunikasi dengan enjin storan melalui API. Antara muka ini melindungi perbezaan antara enjin storan yang berbeza, menjadikan perbezaan ini telus kepada proses pertanyaan lapisan atas.

15. Bagaimanakah pernyataan pertanyaan SQL dilaksanakan dalam MySQL?

• Semak kenyataan 是否有权限 terlebih dahulu Jika tiada kebenaran, mesej ralat akan dikembalikan secara terus Jika ada kebenaran, cache akan ditanya dahulu (sebelum versi MySQL8.0).
• Jika tiada cache, penganalisis akan 语法分析 mengekstrak elemen utama seperti pilih dalam pernyataan sql, dan kemudian tentukan sama ada pernyataan sql mempunyai ralat sintaks, seperti sama ada kata kunci itu betul, dsb.
• Selepas analisis sintaks, pelayan MySQL akan mengoptimumkan pernyataan pertanyaan dan menentukan pelan pelaksanaan.
• Selepas melengkapkan pengoptimuman pertanyaan, hasil pelaksanaan dikembalikan mengikut pelan pelaksanaan yang dihasilkan 调用数据库引擎接口.

Enjin Storan

16. Apakah enjin storan biasa untuk MySQL?

Enjin dan fungsi storan utama adalah seperti berikut:

功能	MylSAM	MEMORY	InnoDB
存储限制	256TB	RAM	64TB
支持事务	No	No	Yes
支持全文索引	Yes	No	Yes
支持树索引	Yes	Yes	Yes
支持哈希索引	No	Yes	Yes
支持数据缓存	No	N/A	Yes
支持外键	No	No	Yes

Sebelum MySQL 5.5, enjin storan lalai ialah MylSAM, dan selepas 5.5 ia menjadi InnoDB.

Indeks cincang yang disokong oleh InnoDB adalah adaptif secara automatik akan menjana indeks cincang untuk jadual berdasarkan penggunaan jadual.

InnoDB menyokong pengindeksan teks penuh bermula daripada MySQL 5.6.

17. Bagaimanakah anda harus memilih enjin storan?

Anda boleh memilih ini secara kasar:

• Dalam kebanyakan kes, menggunakan InnoDB lalai sudah memadai. Jika anda ingin menyediakan keupayaan keselamatan transaksi (keserasian ACID) untuk komit, rollback dan pemulihan, dan memerlukan kawalan serentak, InnoDB ialah pilihan pertama.
• Jika jadual data digunakan terutamanya untuk memasukkan dan menanya rekod, enjin MyISAM menyediakan kecekapan pemprosesan yang lebih tinggi.
• Jika anda hanya menyimpan data sementara, jumlah data tidak besar, dan keselamatan data yang tinggi tidak diperlukan, anda boleh memilih untuk menyimpan data dalam enjin MEMORY dalam memori Enjin ini digunakan dalam MySQL sebagai jadual sementara untuk menyimpan hasil perantaraan.

Enjin mana yang hendak digunakan boleh dipilih secara fleksibel mengikut keperluan, kerana enjin storan adalah berasaskan jadual, jadi berbilang jadual dalam pangkalan data boleh menggunakan enjin yang berbeza untuk memenuhi pelbagai prestasi dan keperluan sebenar. Menggunakan enjin storan yang sesuai akan meningkatkan prestasi keseluruhan pangkalan data.

18. Apakah perbezaan utama antara InnoDB dan MylSAM?

PS: MySQL8.0 perlahan-lahan menjadi popular Jika ia bukan temu bual, anda tidak perlu tahu banyak tentang MylSAM.

1. Struktur storan : Setiap MyISAM disimpan dalam tiga fail pada cakera; semua jadual InnoDB disimpan dalam fail data yang sama (Ia mungkin juga berbilang fail, atau fail ruang jadual bebas). Saiz jadual InnoDB hanya dihadkan oleh saiz fail sistem pengendalian, yang biasanya 2GB.

2. Sokongan transaksi : MyISAM tidak menyediakan sokongan transaksi, dengan keupayaan transaksi (komit), rollback (putar balik) dan ranap (keupayaan pemulihan ranap) ciri.

3 Butiran kunci minimum : MyISAM hanya menyokong kunci peringkat jadual Keseluruhan jadual akan dikunci semasa kemas kini, menyebabkan pertanyaan dan kemas kini lain disekat.

4. Jenis indeks : Indeks MyISAM ialah indeks berkelompok, dan struktur data ialah indeks InnoDB ialah indeks bukan berkelompok, dan struktur data ialah B -pokok.

5. Kunci utama diperlukan : MyISAM membenarkan jadual tanpa sebarang indeks dan kunci utama wujud jika InnoDB tidak menetapkan kunci utama atau indeks unik yang tidak kosong, secara automatik akan menjana 6 perkataan Kunci utama bahagian (tidak kelihatan kepada pengguna) , data adalah sebahagian daripada indeks utama dan indeks tambahan menyimpan nilai indeks utama.

6. Bilangan baris tertentu dalam jadual: MyISAM menyimpan jumlah bilangan baris dalam jadual Jika anda memilih count() daripada jadual;, nilai akan dikeluarkan secara langsung; InnoDB tidak menyimpan jadual Jumlah baris, jika anda menggunakan pilih count() daripada jadual; dengan cara yang sama.

7. Sokongan kunci asing: MyISAM tidak menyokong kunci asing; InnoDB menyokong kunci asing.

Log

19. Apakah fail log MySQL? Perkenalkan fungsi masing-masing?

Terdapat banyak fail log MySQL, termasuk:

• Log ralat (log ralat): Fail log ralat ialah sangat penting kepada MySQL Proses permulaan, operasi dan penutupan direkodkan, yang boleh membantu mengesan masalah MySQL.
• Log pertanyaan perlahan (log pertanyaan perlahan): Log pertanyaan perlahan digunakan untuk merekod pernyataan pertanyaan yang masa pelaksanaannya melebihi panjang yang ditentukan oleh pembolehubah long_query_time. Melalui log pertanyaan yang perlahan, anda boleh mengetahui pernyataan pertanyaan yang mempunyai kecekapan pelaksanaan yang rendah untuk pengoptimuman.
• Log pertanyaan umum (log umum): Log pertanyaan umum merekodkan semua maklumat yang diminta untuk pangkalan data MySQL, tidak kira sama ada permintaan itu dilaksanakan dengan betul.
• Log binari (log bin): Berkenaan log binari, ia merekodkan semua pernyataan DDL dan DML yang dilaksanakan oleh pangkalan data (kecuali pernyataan pertanyaan data pilih, tunjukkan, dsb.), direkodkan dalam bentuk peristiwa dan Disimpan dalam fail binari.

Terdapat juga dua fail log khusus enjin storan InnoDB:

• Buat semula log (buat semula log): Buat semula log adalah penting kerana mereka merekodkan log transaksi untuk enjin storan InnoDB.
• Log gulung balik (buat asal log): Log putar balik juga merupakan log yang disediakan oleh enjin InnoDB Seperti namanya, peranan log balik adalah untuk melancarkan semula data. Apabila urus niaga mengubah suai pangkalan data, enjin InnoDB bukan sahaja akan merekodkan log buat semula, tetapi juga menjana log buat asal yang sepadan jika pelaksanaan urus niaga gagal atau panggil balik, menyebabkan urus niaga ditarik balik, maklumat dalam log buat asal; boleh digunakan untuk memulihkan data Tatal ke cara ia kelihatan sebelum pengubahsuaian.

20. Apakah perbezaan antara binlog dan buat semula log?

• log bin akan merekodkan semua rekod log berkaitan pangkalan data, termasuk log enjin storan seperti InnoDB dan MyISAM, manakala log semula hanya merekodkan log enjin storan InnoDB.
• Kandungan yang direkodkan adalah berbeza Log bin merekodkan kandungan operasi khusus transaksi, iaitu log adalah log logik. Log buat semula merekodkan perubahan fizikal pada setiap halaman (Halaman).
• Masa menulis adalah berbeza Log bin hanya dihantar sebelum transaksi diserahkan, iaitu, ia hanya ditulis ke cakera sekali. Semasa urus niaga sedang berjalan, entri buat semula sentiasa ditulis pada log buat semula.
• Kaedah penulisan juga berbeza ialah Redo log ialah penulisan dan pemadaman kitaran, manakala log bin menambah tulisan dan tidak akan menimpa fail yang telah ditulis.

21 Adakah anda faham cara melaksanakan kenyataan kemas kini?

Pelaksanaan kenyataan kemas kini diselesaikan dengan kerjasama lapisan pelayan dan lapisan enjin Selain menulis data ke jadual, log yang sepadan juga mesti direkodkan.

• Pelaksana terlebih dahulu mencari enjin untuk mendapatkan ID talian=2. ID ialah kunci utama dan enjin storan mendapatkan semula data dan mencari baris ini. Jika halaman data di mana baris dengan ID=2 terletak sudah berada dalam ingatan, ia akan dikembalikan terus kepada pelaksana jika tidak, ia perlu dibaca ke dalam memori dari cakera terlebih dahulu dan kemudian dikembalikan.

• Pelaksana mendapat data baris yang diberikan oleh enjin, menambah 1 pada nilai ini, contohnya, dulu N, tetapi kini N 1, mendapat baris baru data, dan kemudian memanggil antara muka enjin untuk menulis Masukkan baris data baharu ini.

• Enjin mengemas kini baris data baharu ini ke dalam memori dan merekodkan operasi kemas kini ke dalam log buat semula Pada masa ini, log buat semula berada dalam keadaan sediakan. Kemudian maklumkan kepada pelaksana bahawa pelaksanaan telah selesai dan transaksi boleh diserahkan pada bila-bila masa.

• Pelaksana menjana binlog operasi ini dan menulis binlog ke cakera.

• Pelaksana memanggil antara muka transaksi komit enjin, dan enjin menukar log buat semula yang baru ditulis kepada keadaan komit, dan kemas kini selesai.

Seperti yang dapat dilihat daripada rajah di atas, apabila MySQL melaksanakan pernyataan kemas kini, ia menghuraikan dan melaksanakan pernyataan dalam lapisan perkhidmatan, mengekstrak dan menyimpan data dalam lapisan enjin; masa yang sama, dalam perkhidmatan Lapisan menulis binlog dan menulis log buat semula dalam InnoDB.

Bukan itu sahaja, terdapat dua peringkat penyerahan semasa menulis redo log Satu ialah penulisan keadaan prepare sebelum binlog ditulis, dan yang kedua ialah penulisan keadaan commit selepas. binlog ditulis.

22. Jadi kenapa ada penyerahan dua peringkat?

Mengapa penyerahan dua peringkat? Tidak bolehkah anda menyerahkannya terus?

Kita boleh mengandaikan bahawa daripada menggunakan kaedah komit dua peringkat, kita menggunakan komit "peringkat tunggal", iaitu, sama ada menulis log buat semula dahulu dan kemudian menulis binlog atau menulis binlog dahulu dan kemudian tulis semula log. Penyerahan dalam dua cara ini akan menyebabkan keadaan pangkalan data asal tidak konsisten dengan keadaan pangkalan data yang dipulihkan.

Tulis log buat semula dahulu, kemudian tulis binlog:

Selepas menulis log buat semula, data mempunyai keupayaan crash-safe pada masa ini, jadi sistem ranap dan data Ia akan dipulihkan kepada keadaan sebelum transaksi dimulakan. Walau bagaimanapun, jika sistem ranap apabila log buat semula selesai dan sebelum binlog ditulis, sistem ranap. Pada masa ini, binlog tidak menyimpan kenyataan kemas kini di atas, menyebabkan kenyataan kemas kini di atas hilang apabila binlog digunakan untuk membuat sandaran atau memulihkan pangkalan data. Akibatnya, data dalam baris id=2 tidak dikemas kini.

Tulis ke binlog dahulu, kemudian buat semula log:

Selepas menulis binlog, semua pernyataan disimpan, Oleh itu, data dalam baris id=2 dalam pangkalan data yang disalin atau dipulihkan melalui binlog akan dikemas kini kepada a=1. Walau bagaimanapun, jika sistem ranap sebelum log buat semula ditulis, transaksi yang direkodkan dalam log buat semula akan menjadi tidak sah, menyebabkan data dalam baris id=2 dalam pangkalan data sebenar tidak dikemas kini.

Ringkasnya, kedua-dua log buat semula dan binlog boleh digunakan untuk mewakili status komit transaksi, dan komit dua fasa adalah untuk memastikan kedua-dua keadaan konsisten secara logik.

23.redo log怎么刷入磁盘的知道吗？

redo log的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。

什么时候会刷入磁盘？

在如下的一些情况中，log buffer的数据会刷入磁盘：

• log buffer 空间不足时

log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

• 事务提交时

在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。

• 后台线程输入

有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。

• 正常关闭服务器时
• 触发checkpoint规则

重做日志缓存、重做日志文件都是以块（block） 的方式进行保存的，称之为重做日志块（redo log block） ,块的大小是固定的512字节。我们的redo log它是固定大小的，可以看作是一个逻辑上的 log group，由一定数量的log block 组成。

它的写入方式是从头到尾开始写，写到末尾又回到开头循环写。

其中有两个标记位置：

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到磁盘。

当write_pos追上checkpoint时，表示redo log日志已经写满。这时候就不能接着往里写数据了，需要执行checkpoint规则腾出可写空间。

所谓的checkpoint规则，就是checkpoint触发后，将buffer中日志页都刷到磁盘。

SQL 优化

24.慢SQL如何定位呢？

慢SQL的监控主要通过两个途径：

• 慢查询日志：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
• 服务监控：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

25.有哪些方式优化慢SQL？

慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。

避免不必要的列

这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像slect * 这种写法应该尽量避免。

分页优化

在数据量比较大，分页比较深的情况下，需要考虑分页的优化。

例如：

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案：

• 延迟关联

  先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行

  例如：

  select a.* from table a, 
   (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b
   where a.id = b.id

• 书签方式

  书签方式就是找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit

  例如：

  select * from table where id >
  (select * from table where type = 2 and level = 9 order by id asc limit 190

索引优化

合理地设计和使用索引，是优化慢SQL的利器。

利用覆盖索引

InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

低版本避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

避免使用 != 或者 操作符

SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

例如，把column’aaa’，改成column>’aaa’ or column，就可以使用索引了

适当使用前缀索引

适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

正确使用联合索引

使用联合索引的时候，注意最左匹配原则。

JOIN优化

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。

比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。

 select name from A left join B ;

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

避免使用JOIN关联太多的表

《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。

如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。

排序优化

利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION优化

条件下推

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引

最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。

26.怎么看执行计划（explain），如何理解其中各个字段的含义？

explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。

直接在 select 语句之前增加explain关键字，就会返回执行计划的信息。

Lajur

0. id: MySQL akan memberikan nilai id unik pada setiap pernyataan pilihan

1. select_type Lajur, jenis pertanyaan, dikelaskan mengikut perkaitan, kesatuan, subkueri, dsb. Jenis pertanyaan biasa termasuk MUDAH dan UTAMA.

2. jadual lajur: Menunjukkan jadual yang satu baris explain sedang diakses.

3. taip lajur: Salah satu lajur yang paling penting. Mewakili jenis perkaitan atau jenis akses yang MySQL tentukan cara mencari baris dalam jadual.

   Prestasi dari yang terbaik hingga yang paling teruk ialah: sistem >_ref > >

   • sistem

     : Apabila jadual hanya mempunyai satu baris rekod (jadual sistem), jumlah data adalah sangat kecil, cakera IO selalunya tidak diperlukan dan kelajuannya sangat pantas system

   • const

     : menunjukkan bahawa pertanyaan mencecah kunci utama const atau indeks unik primary key, atau yang disambungkan bahagian ialah nilai pemalar (unique). Pengimbasan jenis ini sangat cekap, mengembalikan sejumlah kecil data dan sangat pantas. const

   • eq_ref

     : Tekan kekunci utama eq_ref atau primary key indeks semasa pertanyaan, unique key ialah type. eq_ref

   • ref_or_null

     : Jenis cantuman ini serupa dengan ref, kecuali ref_or_null tambahan akan mencari baris yang mengandungi nilai MySQL. NULL

   • index_merge

     : Kaedah pengoptimuman gabungan indeks digunakan dan pertanyaan menggunakan lebih daripada dua indeks. <code>index_merge

   • unique_subquery

     : Gantikan unique_subquery subquery berikut, dan subquery mengembalikan set unik. IN

   • index_subquery

     : Berbeza daripada <code>index_subquery, ia digunakan untuk indeks bukan unik dan boleh mengembalikan nilai pendua. unique_subquery

   • julat

     : Pilih baris menggunakan indeks, ambil hanya baris dalam julat yang diberikan. Ringkasnya, ia adalah untuk mendapatkan semula data dalam julat tertentu untuk medan yang diindeks. Menggunakan range, where, bettween...and, , <code>> dan pertanyaan bersyarat lain dalam pernyataan <code>in semuanya type. range

   • indeks

     : <code>index dan Index sebenarnya membaca keseluruhan jadual Perbezaannya ialah ALL melintasi pokok indeks untuk membaca, manakala <code>index dibaca daripada cakera keras. ALL

   • SEMUA
     Tidak perlu dikatakan, imbasan meja penuh.

4. possible_keys lajur: Menunjukkan indeks mana yang boleh digunakan oleh pertanyaan untuk mencari, yang lebih penting apabila menggunakan indeks untuk mengoptimumkan SQL. Lajur

5. kunci: Lajur ini menunjukkan indeks yang mysql benar-benar gunakan untuk mengoptimumkan akses kepada jadual, dan lazimnya digunakan apabila menilai sama ada indeks itu tidak sah.

6. key_len lajur: menunjukkan perkara yang MySQL gunakan

7. ref lajur: lajur ref menunjukkan Ia ialah nilai yang sepadan dengan lajur indeks sebagai nilai yang sama ialah: const (constant), func, NULL, dan nama medan.

8. baris Lajur: Ini juga merupakan medan penting Berdasarkan maklumat statistik, pengoptimum pertanyaan MySQL menganggarkan baris data yang perlu diimbas oleh SQL untuk mencari hasilnya set. Nombor, nilai ini secara intuitif menunjukkan kecekapan SQL Pada dasarnya, lebih sedikit baris, lebih baik.

9. Tambahan lajur: memaparkan maklumat tambahan yang tidak sesuai dalam lajur lain Walaupun ia dipanggil tambahan, terdapat juga beberapa maklumat penting:

Menggunakan indeks: Menunjukkan bahawa MySQL akan menggunakan indeks penutup untuk mengelakkan jadual kembali
• Menggunakan tempat: Menunjukkan bahawa penapisan akan dilakukan selepas enjin storan mendapatkan semula
• Menggunakan sementara: Menunjukkan Jadual sementara digunakan semasa mengisih hasil pertanyaan.

Indeks

Indeks boleh dikatakan sebagai keutamaan utama dalam temu duga MySQL, dan ia mesti dimenangi sepenuhnya.

27 Bolehkah anda bercakap secara ringkas tentang klasifikasi indeks?

Indeks kategori daripada tiga dimensi berbeza:

Sebagai contoh, dari perspektif penggunaan asas:

Indeks kunci utama : Kunci utama InnoDB ialah indeks lalai Lajur data tidak dibenarkan diulang atau NULL.
• Indeks unik: Penduaan lajur data tidak dibenarkan, nilai NULL dibenarkan dan jadual membenarkan berbilang lajur mencipta indeks unik.
• Indeks biasa: Jenis indeks asas, tiada sekatan keunikan, nilai NULL dibenarkan.
• Indeks gabungan: Nilai lajur berbilang membentuk indeks untuk carian gabungan, yang lebih cekap daripada penggabungan indeks

28 Mengapakah menggunakan indeks mempercepatkan pertanyaan?

Kaedah pertanyaan tradisional merentasi jadual mengikut tertib Tidak kira berapa keping data yang ditanya, MySQL perlu merentasi data jadual dari awal hingga akhir.

Selepas kami menambah indeks, MySQL biasanya menjana fail indeks melalui algoritma BTREE Apabila menanyakan pangkalan data, cari fail indeks dan melintasinya, cari dalam data indeks yang agak kecil, dan kemudian petakannya ke data yang sepadan. yang boleh meningkatkan kecekapan Carian.

Ia sama seperti apabila kita mencari kandungan yang sepadan melalui jadual kandungan buku.

29 Apakah perkara yang perlu diperhatikan semasa membuat indeks?

Walaupun indeks ialah alat yang berkuasa untuk pengoptimuman prestasi sql, penyelenggaraan indeks juga memerlukan kos, jadi apabila membuat indeks, anda juga harus memberi perhatian kepada:

• indeks harus dibina dalam aplikasi pertanyaan Medan kerap

  Buat indeks pada medan (di) digunakan untuk tempat penghakiman, isihan pesanan dan sertai.

• Bilangan indeks hendaklah sesuai

  Indeks perlu menduduki ruang; ia juga perlu dikekalkan semasa kemas kini.

• Jangan bina indeks untuk medan dengan perbezaan yang rendah, seperti jantina.

  Untuk medan dengan serakan terlalu rendah, bilangan baris yang diimbas akan dihadkan.

• Jangan gunakan nilai yang kerap dikemas kini sebagai kunci utama atau indeks

  Ia memerlukan wang untuk menyelenggara fail indeks juga akan membawa kepada pemisahan halaman dan peningkatan masa IO .

• Indeks gabungan meletakkan nilai dengan pencincangan tinggi (perbezaan tinggi) di hadapan

  Untuk memenuhi prinsip pemadanan awalan paling kiri

• Buat indeks gabungan dan bukannya mengubah suai indeks lajur tunggal.

  Indeks gabungan menggantikan berbilang indeks lajur tunggal (untuk indeks lajur tunggal, MySQL pada asasnya hanya boleh menggunakan satu indeks, jadi lebih sesuai untuk menggunakan indeks gabungan apabila berbilang pertanyaan keadaan sering digunakan)

• Untuk medan yang terlalu panjang, gunakan indeks awalan. Apabila nilai medan agak panjang, pengindeksan akan menggunakan banyak ruang dan carian akan menjadi sangat perlahan. Kita boleh mencipta indeks dengan memintas bahagian medan sebelumnya, yang dipanggil indeks awalan.

• Tidak disyorkan untuk menggunakan nilai tidak tertib (seperti kad ID, UUID) sebagai indeks

  Apabila kunci utama tidak pasti, ia akan menyebabkan nod daun untuk berpecah dengan kerap dan cakera akan muncul pemecahan storan

30. Dalam keadaan apakah indeks akan gagal?

• Syarat pertanyaan mengandungi atau, yang boleh menyebabkan indeks gagal
• Jika jenis medan ialah rentetan, di mana mesti disertakan dalam petikan, jika tidak, indeks akan gagal disebabkan kepada penukaran jenis tersirat
• seperti kad bebas boleh menyebabkan kegagalan indeks.
• Indeks bersama, lajur keadaan semasa membuat pertanyaan bukanlah lajur pertama dalam indeks bersama dan indeks menjadi tidak sah.
• Apabila menggunakan fungsi terbina dalam MySQL pada lajur indeks, indeks menjadi tidak sah.
• Untuk operasi pada lajur indeks (seperti , -, *, /), indeks menjadi tidak sah.
• Apabila menggunakan (!= atau , bukan dalam) pada medan indeks, ia boleh menyebabkan kegagalan indeks.
• Menggunakan adalah batal atau tidak batal pada medan indeks boleh menyebabkan kegagalan indeks.
• Format pengekodan medan yang dikaitkan dengan pertanyaan penyertaan kiri atau pertanyaan penyertaan kanan adalah berbeza, yang boleh menyebabkan kegagalan indeks.
• Pengoptimum MySQL menganggarkan bahawa menggunakan imbasan jadual penuh adalah lebih pantas daripada menggunakan indeks, jadi indeks tidak digunakan.

31. Apakah senario yang tidak sesuai untuk indeks?

• Jadual dengan jumlah data yang agak kecil tidak sesuai untuk pengindeksan
• Medan yang kerap dikemas kini juga tidak sesuai untuk pengindeksan
• Medan dengan diskret yang rendah tidak sesuai untuk pengindeksan ( Seperti jantina)

32. Adakah lebih baik untuk membina lebih banyak indeks?

Sudah tentu tidak.

• Indeks akan menduduki ruang cakera
• Walaupun indeks akan meningkatkan kecekapan pertanyaan, ia akan mengurangkan kecekapan mengemas kini jadual. Sebagai contoh, setiap kali jadual ditambah, dipadam atau diubah suai, MySQL bukan sahaja mesti menyimpan data, tetapi juga menyimpan atau mengemas kini fail indeks yang sepadan.

33 Adakah anda tahu struktur data yang digunakan oleh indeks MySQL?

Enjin storan lalai MySQL ialah InnoDB, yang menggunakan indeks berstruktur B-tree.

• Pokok B: Hanya nod daun menyimpan data dan nod bukan daun hanya menyimpan nilai utama. Nod daun disambungkan menggunakan penunjuk dua hala, dan nod daun terendah membentuk senarai terpaut tersusun dua hala.

Dalam gambar ini, terdapat dua perkara penting:

• Blok paling luar, kami memanggilnya blok cakera Anda boleh melihat bahawa setiap blok cakera mengandungi beberapa item data (ditunjukkan dalam warna merah jambu) dan penunjuk (ditunjukkan dalam warna kuning/kelabu), seperti akar Cakera nod mengandungi. item data 17 dan 35, termasuk penunjuk P1, P2, dan P3 mewakili blok cakera kurang daripada 17, P2 mewakili blok cakera antara 17 dan 35, dan P3 mewakili blok cakera lebih daripada 35. Data sebenar wujud dalam nod daun iaitu 3, 4, 5..., 65. Nod bukan daun tidak menyimpan data sebenar, tetapi hanya item data yang membimbing arah carian Contohnya, 17 dan 35 sebenarnya tidak wujud dalam jadual data.
• Nod daun disambungkan menggunakan penunjuk dua hala Nod daun terendah membentuk senarai terpaut tertib dua hala, yang boleh ditanya julat.

34. Berapa keping data yang boleh disimpan oleh pokok B?

Anggap bahawa medan indeks adalah jenis bigint dan panjangnya 8 bait. Saiz penuding ditetapkan kepada 6 bait dalam kod sumber InnoDB, menjadikan jumlah keseluruhan 14 bait. Nod bukan daun (satu halaman) boleh menyimpan 16384/14=1170 unit tersebut (penunjuk nilai utama), yang bermaksud terdapat 1170 penunjuk.

Apabila kedalaman pokok ialah 2, terdapat 1170^2 nod daun, dan data yang boleh disimpan ialah 1170117016=21902400.

Apabila mencari data, satu carian halaman mewakili satu IO Dalam erti kata lain, untuk jadual kira-kira 20 juta, data pertanyaan memerlukan sehingga tiga akses cakera.

Jadi kedalaman B-tree dalam InnoDB biasanya 1-3 lapisan, yang boleh memenuhi berpuluh juta storan data.

35 Mengapa menggunakan B-tree berbanding pokok binari biasa?

Anda boleh melihat masalah ini dari beberapa dimensi, sama ada pertanyaan itu cukup pantas, sama ada kecekapannya stabil, berapa banyak data yang disimpan dan bilangan carian cakera.

Mengapa tidak menggunakan pokok binari biasa?

Pokok binari biasa merosot jika ia merosot menjadi senarai terpaut, ia bersamaan dengan imbasan jadual penuh. Berbanding dengan pokok carian binari, pokok binari seimbang mempunyai kecekapan carian yang lebih stabil dan kelajuan carian keseluruhan yang lebih pantas.

Mengapa tidak mengimbangkan pokok binari?

Apabila membaca data, ia dibaca dari cakera ke dalam memori. Jika struktur data seperti pokok digunakan sebagai indeks, setiap kali anda mencari data, anda perlu membaca nod daripada cakera, iaitu blok cakera, tetapi pokok binari yang seimbang hanya menyimpan satu nilai kunci dan data bagi setiap nod . Jika ia adalah B-tree , lebih banyak data nod boleh disimpan, dan ketinggian pokok juga akan dikurangkan, jadi bilangan bacaan cakera akan dikurangkan dan kecekapan pertanyaan akan menjadi lebih cepat.

36 Mengapa menggunakan B-tree dan bukannya B-tree?

B mempunyai kelebihan ini berbanding B-tree:

• Ia adalah varian B-Tree Ia boleh menyelesaikan semua masalah yang boleh diselesaikan oleh B-Tree.

  Dua masalah utama yang diselesaikan oleh B Tree: setiap nod menyimpan lebih banyak kata kunci; melakukan imbasan jadual penuh di atas meja, kita hanya perlu melintasi nod daun, dan tidak perlu melintasi keseluruhan B Tree untuk mendapatkan semua data.

• B Tree mempunyai keupayaan membaca dan menulis cakera yang lebih kuat daripada B Tree, dan mempunyai masa IO yang lebih sedikit

  Nod akar dan nod cawangan tidak menyimpan kawasan data, jadi satu nod Lebih banyak kata kunci boleh disimpan, lebih banyak kata kunci boleh dimuatkan ke dalam cakera pada satu masa, dan bilangan IO boleh dikurangkan.

• Keupayaan pengisihan lebih kuat

  Oleh kerana terdapat penunjuk ke kawasan data seterusnya pada nod daun, data membentuk senarai terpaut.

• Kecekapan lebih stabil

  B Tree sentiasa mendapat data pada nod daun, jadi bilangan IO adalah stabil.

• 37. Apakah perbezaan antara indeks Hash dan indeks B-tree?

  B-tree boleh melakukan pertanyaan julat, tetapi indeks Hash tidak boleh.

B-tree menyokong prinsip paling kiri indeks bersama, tetapi indeks Hash tidak menyokongnya.

B-tree menyokong pesanan dengan mengisih, tetapi indeks Hash tidak menyokongnya.

• Indeks cincang lebih cekap daripada B-tree untuk pertanyaan yang setara.
• Apabila B-tree menggunakan suka untuk pertanyaan kabur, perkataan selepas suka (seperti bermula dengan %) boleh memainkan peranan pengoptimuman dan indeks Hash tidak boleh melakukan pertanyaan kabur sama sekali.
• 38. Apakah perbezaan antara indeks berkelompok dan indeks tidak berkelompok?
• Mula-mula faham bahawa indeks berkelompok bukanlah indeks baharu, tetapi
• kaedah penyimpanan data
. Pengelompokan bermakna baris data dan nilai kunci bersebelahan disimpan padat bersama. Dua enjin storan yang kami kenali - MyISAM menggunakan indeks bukan berkelompok dan InnoDB menggunakan indeks berkelompok.

Anda boleh menyebut:

Struktur data indeks ialah pokok Indeks dan data indeks berkelompok disimpan dalam pokok data. Indeks Tidak Berkelompok Indeks dan data tidak berada dalam pokok yang sama.

• 一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。
• 聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
• 聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

39.回表了解吗？

在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如:select * from user where name = ‘张三’;



40.覆盖索引了解吗？

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。

比如，select name from user where name = ‘张三’;



41.什么是最左前缀原则/最左匹配原则？

注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。

最左匹配原则：在InnoDB的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。

根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引。

为什么不从最左开始查，就无法匹配呢？

比如有一个user表，我们给 name 和 age 建立了一个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，age 在右边)。

从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

这个时候我们使用where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name，就不知道下一步应该查哪个 节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。

42.什么是索引下推优化？

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。
• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：

不使用ICP，引擎层查找到name like '张%'的数据，再由Server层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。

但是，使用了索引下推优化，把where的条件放到了引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

锁

43.MySQL中有哪几种锁，列举一下？

如果按锁粒度划分，有以下3种：

• Kunci meja: overhed rendah, penguncian cepat; daya penguncian yang kuat, kebarangkalian konflik kunci yang tinggi, konkurensi terendah;
• Kunci baris: overhed tinggi, penguncian perlahan mungkin berlaku;
• Kunci halaman: Kos dan kelajuan mengunci adalah antara kunci meja dan kunci baris mungkin berlaku; >Dari segi keserasian, terdapat dua jenis,

Kunci kongsi (S Lock), juga dipanggil kunci baca (read lock), yang tidak menghalang satu sama lain.

Kunci eksklusif (Kunci X), juga dipanggil kunci tulis (kunci tulis), kunci eksklusif disekat dalam tempoh masa tertentu, hanya satu permintaan boleh melakukan penulisan dan menghalang kunci lain daripada membaca dan menulis data.
• 44. Bercakap tentang pelaksanaan kunci baris dalam InnoDB?

Kami menggunakan jadual pengguna sedemikian untuk mewakili kunci peringkat baris, di mana 4 baris data dimasukkan dan nilai kunci utama ialah 1,6,8,12, kini memudahkan struktur indeks berkelompoknya dan hanya mengekalkan rekod data.

Pelaksanaan utama kunci baris InnoDB adalah seperti berikut:

Kunci rekod Kunci Rekod

• Kunci rekod adalah untuk mengunci terus barisan rekod. Apabila kami menggunakan indeks unik (termasuk indeks unik dan indeks berkelompok) untuk melakukan pertanyaan yang setara dan memadankan rekod dengan tepat, rekod akan dikunci terus. Contohnya,
akan mengunci rekod

.

select * from t where id =6 for update;id=6

Gap Lock Gap Lock

• Jurang Gap Lock merujuk kepada dua Bahagian antara rekod yang belum lagi diisi secara logik dengan data ialah
ruang terbuka kiri dan buka kanan

.

Kunci celah adalah untuk mengunci julat jurang tertentu. Apabila kami menggunakan pertanyaan kesamaan atau pertanyaan julat dan tidak menekan sebarang , selang jurang yang sepadan akan dikunci. Contohnya,

atau

akan mengunci selang (1,6). recordselect * from t where id =3 for update;select * from t where id > 1 and id

Kunci kekunci seterusnya

• Kunci seterusnya merujuk kepada jurang serta rekod di sebelah kanannya
Terbuka kiri Selang tertutup kanan

. Contohnya, di atas (1,6], (6,8], dsb.

Kunci kekunci Lynx ialah gabungan kunci rekod (Kunci Rekod) dan kunci celah (Kunci Jurang) , iaitu, selain mengunci rekod itu sendiri, kita juga perlu mengunci jurang antara indeks Apabila kita menggunakan pertanyaan julat dan menekan beberapa rekod , selang kunci dikunci pada masa ini bahawa selang kunci dikunci. Julat langsung akan termasuk julat kunci segera di sebelah kanan rekod terakhir Contohnya,

akan mengunci (4,7], (7, ∞). Jenis kunci baris lalai MySQL ialah.

. Apabila menggunakan indeks unik, Apabila pertanyaan nilai yang sama sepadan dengan rekod, Kunci Kekunci Seterusnya akan merosot menjadi kunci rekod apabila tiada rekod dipadankan, Kunci Kekunci Seterusnya akan merosot menjadi kunci jurang dan record kedua-duanya digunakan untuk menyelesaikan masalah bacaan hantu Di bawah tahap pengasingan select * from t where id > 5 and id dan <code>临键锁(Next-Key Locks) akan gagal

Di atas ialah tiga algoritma pelaksanaan kunci baris Selain itu, terdapat kunci niat sisipan pada baris 间隙锁(Gap Locks)临键锁(Next-Key Locks)已提交读（READ COMMITTED）间隙锁(Gap Locks)Sisipkan Kunci Niat 临键锁(Next-Key Locks)

Satu transaksi menyisip satu baris, anda perlu melakukannya tentukan sama ada kedudukan sisipan dikunci oleh transaksi lain Jika ya, operasi sisipan perlu menunggu sehingga urus niaga yang memegang kunci jurang itu dilakukan Walau bagaimanapun, transaksi juga perlu menjana kunci niat dalam memori semasa menunggu bahawa transaksi ingin memasukkan rekod baharu dalam jurang tertentu, tetapi kini sedang menunggu jenis kunci ini dinamakan Kunci Niat Sisip, iaitu,

Jika kita mempunyai transaksi T1 , kunci niat mempunyai telah ditambahkan pada selang (1,6) Sekarang terdapat transaksi T2, yang ingin memasukkan sekeping data dengan id 4. Ia akan memperoleh kunci niat sisipan untuk selang (1,6), dan di sana. juga merupakan transaksi T3 , jika anda ingin memasukkan sekeping data dengan id 3, ia juga akan memperoleh kunci niat sisipan dalam julat (1,6) Walau bagaimanapun, kedua-dua kunci niat sisipan tidak akan saling eksklusif.

45. Adakah anda tahu apa itu kunci niat? kunci niat sisipan.

Kunci niat kelihatan menyokong kunci berbutir-butir InnoDB menyelesaikan masalah kewujudan bersama kunci meja dan kunci baris

.

Apabila kita perlu menambah kunci jadual pada jadual, kita perlu menilai sama ada terdapat sebarang baris data dalam jadual yang dikunci untuk menentukan sama ada penambahan itu boleh berjaya.

Jika tiada kunci niat, maka kita perlu melintasi semua baris data dalam jadual untuk menentukan sama ada terdapat kunci baris;

Dengan kunci niat, kunci peringkat meja , kita boleh menentukan secara langsung sekali. Ketahui sama ada mana-mana baris data dalam jadual dikunci.

Dengan kunci niat, sebelum transaksi A dilaksanakan menggunakan kunci baris (kunci tulis), pangkalan data secara automatik akan memohon kunci eksklusif niat di atas meja untuk transaksi A. Apabila transaksi B menggunakan kunci mutex di atas meja, ia akan gagal kerana terdapat kunci eksklusif yang disengajakan di atas meja dan transaksi B akan disekat apabila ia menggunakan kunci mutex di atas meja.

46 Adakah anda memahami penguncian optimistik dan penguncian pesimis MySQL?

• Kunci Pesimistik (Kawalan Keselarasan Pesimis):

Kunci Pesimistik percaya bahawa data yang dilindunginya amat tidak selamat, sepanjang masa. Semua mungkin diubah Selepas transaksi memperoleh kunci pesimis, tiada transaksi lain boleh mengubah suai data dan hanya boleh menunggu kunci dikeluarkan sebelum melaksanakannya.

Kunci baris, kunci meja, kunci baca dan kunci tulis dalam pangkalan data semuanya adalah kunci pesimis.

• Kawalan Serentak Optimis

Kunci optimis percaya bahawa data tidak akan berubah terlalu kerap.

Penguncian optimis biasanya dilaksanakan dengan menambahkan versi (versi) atau cap waktu (cap masa) pada jadual, antara versi yang paling biasa digunakan.

Apabila transaksi mengambil data daripada pangkalan data, ia juga akan mengeluarkan versi data (v1 Apabila transaksi melengkapkan perubahan pada data dan ingin mengemas kininya ke jadual, ia akan mengambil masa). keluar versi yang dikeluarkan sebelum ini. Membandingkan v1 dengan versi terkini v2 dalam data, jika v1=v2, ini bermakna semasa tempoh perubahan data, tiada urus niaga lain mengubah suai data pada masa ini dalam jadual, dan versi akan diubah semasa pengubahsuaian Tambah 1 untuk menunjukkan bahawa data telah diubah.

Jika v1 tidak sama dengan v2, ini bermakna data telah diubah suai oleh transaksi lain semasa tempoh perubahan data Pada masa ini, data tidak dibenarkan untuk dikemas kini ke dalam jadual untuk memberitahu pengguna dan membiarkan mereka beroperasi semula. Tidak seperti penguncian pesimis, penguncian optimistik biasanya dilaksanakan oleh pembangun.

47.Adakah MySQL pernah menghadapi masalah kebuntuan Bagaimana anda menyelesaikannya?

Langkah-langkah umum untuk menyelesaikan masalah kebuntuan adalah seperti berikut:

(1) Semak log kebuntuan menunjukkan status enjin innodb

(2) Ketahui kebuntuan sql

(3) Analisis situasi kunci sql

(4) Simulasi kejadian kebuntuan

(5) Analisis log kebuntuan

(6) Analisis hasil kunci kebuntuan

Sudah tentu, ini hanyalah penerangan proses yang mudah Malah, kebuntuan dalam pengeluaran adalah semua jenis pelik, dan ia tidak begitu mudah untuk diselesaikan dan diselesaikan.

Transaksi

48 Apakah empat ciri utama transaksi MySQL?

• Atomicity: Transaksi dilaksanakan secara keseluruhan, dan sama ada semua operasi pada pangkalan data yang terkandung di dalamnya dilaksanakan, atau tiada satu pun daripadanya dilaksanakan.
• Ketekalan: bermakna data tidak akan dimusnahkan sebelum urus niaga bermula dan selepas urus niaga tamat Jika akaun A memindahkan 10 yuan ke akaun B, jumlah keseluruhan A dan B akan kekal tidak berubah tanpa mengira kejayaan atau. kegagalan.
• Pengasingan: Apabila berbilang urus niaga mengakses secara serentak, urus niaga itu diasingkan antara satu sama lain, iaitu, satu transaksi tidak menjejaskan kesan berjalan transaksi lain. Ringkasnya, ia bermakna tidak ada konflik antara urusan.
• Kegigihan: Menunjukkan bahawa selepas transaksi selesai, perubahan operasi yang dibuat oleh transaksi kepada pangkalan data akan disimpan secara kekal dalam pangkalan data.

49 Jadi, apakah jaminan yang ASID harapkan?

• pengasingan transaksi dicapai melalui mekanisme kunci pangkalan data.
• Konsistensi urus niaga dijamin oleh log asal: log asal ialah log logik, yang merekodkan operasi sisipan, kemas kini dan deltete urus niaga tersebut operasi padam, kemas kini dan sisip bertentangan untuk memulihkan data. Ketahanan
urus niaga
• dan ketahanan dijamin oleh log buat semula: log buat semula dipanggil log semula, iaitu log fizikal apabila transaksi diserahkan, ia mesti pertama Tulis semua log urus niaga ke log buat semula untuk kegigihan, dan transaksi tidak selesai sehingga operasi komit.

50 Apakah tahap pengasingan urus niaga? Apakah tahap pengasingan lalai MySQL?

Read Uncommitted
• Read Commited
• Reatable Read )
• Serializable

Tahap pengasingan transaksi lalai MySQL ialah Bacaan Boleh Diulang.

51.Apakah bacaan hantu, bacaan kotor dan bacaan tidak boleh diulang?

• Transaksi A dan B dilaksanakan secara bergilir-gilir, dan transaksi A membaca data transaksi B yang tidak terikat. Ini bacaan kotor.
• Dalam skop transaksi, dua pertanyaan yang sama membaca rekod yang sama tetapi mengembalikan data yang berbeza. Ini adalah bacaan tidak boleh diulang.
• Transaksi A menanyakan set hasil julat, dan satu lagi transaksi serentak B memasukkan/memadamkan data ke dalam julat ini dan melaksanakannya secara senyap Kemudian transaksi A menanyakan julat yang sama sekali lagi dan membaca set hasil adalah berbeza, ini ialah bacaan hantu.

Tahap pengasingan yang berbeza, masalah yang mungkin berlaku di bawah transaksi serentak:

隔离级别	脏读	不可重复读	幻读
Read Uncommited 读取未提交	是	是	是
Read Commited 读取已提交	否	是	否
Repeatable Read 可重复读	否	否	是
Serialzable 可串行化	否	否	否

52 Bagaimanakah pelbagai tahap pengasingan transaksi dilaksanakan?

Baca tanpa komitmen

Baca tanpa komitmen, tidak perlu dikatakan, prinsip membaca tanpa mengunci diguna pakai.

• Bacaan transaksi tidak dikunci dan tidak menyekat pembacaan dan penulisan transaksi lain
• Penulisan transaksi menyekat penulisan transaksi lain, tetapi tidak menyekat bacaan transaksi lain;

Baca Komited & Bacaan Boleh Ulang

Baca Komited dan Tahap Bacaan Boleh Ulang mengambil kesempatan daripada ReadView dan MVCC, iaitu setiap transaksi hanya boleh membaca The versi yang boleh dilihatnya (ReadView).

• READ COMMITED: ReadView dijana setiap kali sebelum data dibaca
• REPEATABLE READ: ReadView dijana apabila data dibaca buat kali pertama

Siri

Pelaksanaan siri mengamalkan prinsip mengunci baik membaca dan menulis. Dalam kes siri

, untuk baris transaksi yang sama, 写 akan ditambah dengan 写锁 dan 读 akan ditambah dengan 读锁. Apabila konflik kunci baca-tulis berlaku, transaksi yang diakses kemudian mesti menunggu penyelesaian transaksi sebelumnya sebelum ia boleh terus dilaksanakan.

53.Adakah anda faham MVCC? Bagaimana ia dicapai?

MVCC (Multi Version Concurrency Control), nama Cina ialah kawalan penukaran berbilang versi Secara ringkasnya, ia menyelesaikan masalah ketekalan baca di bawah akses serentak dengan mengekalkan versi sejarah data. Berkenaan pelaksanaannya, kita mesti memahami beberapa perkara penting, medan tersirat, buat asal log, rantai versi, bacaan syot kilat & bacaan semasa dan Paparan Baca.

Rantai versi

Untuk enjin storan InnoDB, setiap baris rekod mempunyai dua lajur tersembunyi DB_TRX_ID, DB_ROLL_PTR

DB_TRX_IDDB_TRX_ID
• DB_ROLL_PTRAndaikan terdapat jadual
dengan hanya satu baris rekod dalam jadual, dan ID transaksi yang dimasukkan pada masa itu ialah 80. Pada masa ini, contoh rajah rekod ini adalah seperti berikut:

Seterusnya, terdapat dua user transaksi, masing-masing

dan

, untuk dilaksanakan operasi rekod ini, keseluruhan proses adalah seperti berikut:

DB_TRX_ID100200 Memandangkan setiap perubahan akan direkodkan dalam log update dahulu, dan gunakan

untuk menunjuk ke

alamat log. Oleh itu, boleh dianggap bahawa log pengubahsuaian untuk rekod ini digabungkan untuk membentuk

, dan nod kepala rantai versi ialah nilai terkini rekod semasa

. Seperti berikut: undoDB_ROLL_PTRundo版本链ReadView

Untuk tahap pengasingan

dan , bacaan diperlukan Rekod pengubahsuaian transaksi yang telah dilakukan, iaitu, jika pengubahsuaian versi tertentu dalam rantaian versi tidak diserahkan, maka rekod versi tersebut tidak boleh dibaca. Oleh itu, adalah perlu untuk menentukan versi dalam rantaian versi yang boleh dibaca oleh transaksi semasa di bawah tahap pengasingan dan

. Jadi konsep

diperkenalkan untuk menyelesaikan masalah ini.

Read CommittedRepeatable ReadPandangan Baca ialah paparan baca yang dijana apabila transaksi melaksanakan Read Committed syot kilat dibaca Repeatable Read, yang bersamaan dengan syot kilat yang direkodkan dalam jadual pada masa tertentu, kita boleh dapatkan: ReadView

m_ids: Mewakili senarai ID transaksi transaksi baca dan tulis aktif dalam sistem semasa apabila ReadView dijana.

min_trx_id: Menunjukkan id transaksi terkecil antara transaksi baca dan tulis aktif dalam sistem semasa apabila ReadView dijana, iaitu nilai terkecil dalam m_ids.

max_trx_id: Menunjukkan nilai id yang harus diberikan kepada transaksi seterusnya dalam sistem apabila menjana ReadView.

• creator_trx_id: Menunjukkan id transaksi transaksi yang menghasilkan ReadView
• Dengan ReadView ini, apabila mengakses rekod tertentu, anda hanya perlu mengikuti langkah di bawah untuk menentukan tertentu sebahagian daripada rekod Sama ada versi kelihatan:
• • Jika nilai atribut DB_TRX_ID versi yang diakses adalah sama dengan nilai creator_trx_id dalam ReadView, ini bermakna transaksi semasa mengakses rekodnya sendiri yang diubah suai, jadi versi ini boleh diakses oleh transaksi semasa.
  • Jika nilai atribut DB_TRX_ID versi yang diakses adalah kurang daripada nilai min_trx_id dalam ReadView, ini menunjukkan bahawa transaksi yang menjana versi ini telah dilakukan sebelum transaksi semasa menjana ReadView, jadi versi ini boleh diakses oleh transaksi semasa.
  • Jika nilai atribut DB_TRX_ID versi yang diakses adalah lebih besar daripada nilai max_trx_id dalam ReadView, ini bermakna transaksi yang menjana versi ini dibuka selepas transaksi semasa menghasilkan ReadView, jadi versi ini tidak boleh diakses oleh transaksi semasa.
  • Jika nilai atribut DB_TRX_ID versi yang diakses adalah antara min_trx_id dan max_trx_id ReadView, maka anda perlu menentukan sama ada nilai atribut trx_id berada dalam senarai m_ids Jika ya, ini bermakna versi ini transaksi dijana apabila ReadView dibuat Jika ia masih aktif, versi ini tidak boleh diakses, ini bermakna transaksi yang menjana versi ini apabila ReadView dibuat telah dilakukan, dan versi ini boleh diakses.

  Jika versi data tertentu tidak dapat dilihat oleh transaksi semasa, kemudian ikuti rantaian versi untuk mencari versi data seterusnya, teruskan ikut langkah di atas untuk menentukan keterlihatan dan seterusnya , sehingga versi Versi terakhir dalam rangkaian. Jika versi terakhir tidak kelihatan, ini bermakna rekod itu tidak dapat dilihat sepenuhnya kepada transaksi dan hasil pertanyaan tidak termasuk rekod.

  Dalam MySQL, perbezaan yang sangat besar antara tahap pengasingan READ COMMITTED dan REPEATABLE READ ialah mereka menjana ReadView pada masa yang berbeza.

  READ COMMITTED adalah hasilkan ReadView sebelum setiap kali membaca data, untuk memastikan anda boleh membaca data yang dihantar oleh transaksi lain setiap kali REPEATABLE READ adalah Apabila membaca data buat pertama kalinya, ReadView dijana, yang memastikan hasil bacaan seterusnya adalah konsisten sepenuhnya.

  Ketersediaan/prestasi tinggi

  54 Adakah anda faham pemisahan pangkalan data baca dan tulis?

  Prinsip asas pemisahan baca dan tulis adalah untuk menyebarkan operasi baca dan tulis pangkalan data ke nod yang berbeza Berikut ialah gambar rajah seni bina asas:

  

  Baca. dan write separation Pelaksanaan asas ialah:

  • Pelayan pangkalan data membina kluster tuan-hamba, sama ada satu tuan dan satu hamba, atau satu tuan dan berbilang hamba.
  • Hos pangkalan data bertanggungjawab untuk operasi baca dan tulis, dan hamba hanya bertanggungjawab untuk operasi baca.
  • Hos pangkalan data menyegerakkan data ke mesin hamba melalui replikasi, dan setiap pelayan pangkalan data menyimpan semua data perniagaan.
  • Pelayan perniagaan menghantar operasi tulis kepada hos pangkalan data dan membaca operasi kepada hamba pangkalan data.

  55. Bagaimana untuk merealisasikan peruntukan pengasingan baca dan tulis?

  Untuk memisahkan operasi baca dan tulis, dan kemudian mengakses pelayan pangkalan data yang berbeza, biasanya terdapat dua cara: enkapsulasi kod program dan enkapsulasi perisian tengah.

  1. Enkapsulasi kod program

  Pengenkapsulan kod program merujuk kepada pengabstrakan lapisan akses data dalam kod (jadi sesetengah artikel juga memanggil kaedah ini "pengenkapsulan lapisan tengah" " ) untuk merealisasikan pemisahan operasi baca dan tulis dan pengurusan sambungan pelayan pangkalan data. Contohnya, enkapsulasi ringkas berdasarkan Hibernate boleh mencapai pemisahan baca-tulis:

  

  Antara penyelesaian pelaksanaan sumber terbuka semasa, TDDL (Lapisan Data Teragih Taobao, nama panggilan: pengepala) Taobao. semuanya besar) agak terkenal.

  2. Enkapsulasi middleware

  Encapsulation middleware merujuk kepada sistem bebas yang merealisasikan pemisahan operasi baca dan tulis serta pengurusan sambungan pelayan pangkalan data. Perisian tengah menyediakan protokol yang serasi SQL kepada pelayan perniagaan, dan pelayan perniagaan tidak perlu memisahkan bacaan dan penulisan dengan sendirinya.

  Untuk pelayan perniagaan, tiada perbezaan antara mengakses middleware dan mengakses pangkalan data Malah, dari perspektif pelayan perniagaan, middleware adalah pelayan pangkalan data.

  Struktur asas ialah:

  

  56. Adakah anda faham prinsip replikasi tuan-hamba?

  • Data induk menulis, mengemas kini binlog
  • tuan mencipta benang dump untuk menolak binlog ke hamba
  • Apabila hamba bersambung ke master, ia akan mencipta benang IO ke terima binlog, dan rekodkannya dalam log geganti
  • hamba membuka utas sql untuk membaca peristiwa log geganti dan laksanakannya pada hamba untuk menyelesaikan penyegerakan
  • hamba merekodkan binglognya sendiri

  

  57. Bagaimana untuk menangani kelewatan penyegerakan tuan-hamba?

  Punca kelewatan penyegerakan tuan-hamba

  Pelayan membuka N pautan untuk disambungkan oleh pelanggan, jadi akan terdapat operasi kemas kini serentak yang besar, tetapi hanya terdapat satu urutan untuk membaca binlog daripada pelayan Apabila SQL tertentu dilaksanakan pada pelayan hamba Jika ia memerlukan a sedikit lebih lama atau kerana SQL tertentu perlu mengunci jadual, akan terdapat banyak tunggakan SQL pada pelayan induk dan ia tidak akan disegerakkan ke pelayan hamba. Ini membawa kepada ketidakkonsistenan tuan-hamba, iaitu kelewatan tuan-hamba.

  Penyelesaian kepada kelewatan penyegerakan tuan-hamba

  Terdapat beberapa kaedah biasa untuk menyelesaikan kelewatan replikasi tuan-hamba:

  • Tulis Operasi baca selepas operasi ditetapkan untuk dihantar ke pelayan utama pangkalan data

  Contohnya, selepas pendaftaran akaun selesai, operasi baca untuk membaca akaun semasa log masuk ialah juga dihantar ke pelayan utama pangkalan data. Kaedah ini sangat terikat dengan perniagaan dan mempunyai pencerobohan dan kesan yang lebih besar kepada perniagaan Jika pengaturcara baharu tidak tahu cara menulis kod dengan cara ini, ia akan menyebabkan pepijat.

  • Baca tuan sekali lagi selepas gagal membaca daripada hamba

  Inilah yang biasa dipanggil "bacaan sekunder", bacaan sekunder Ia adalah tidak terikat kepada perniagaan dan hanya perlu merangkumkan API yang diakses oleh pangkalan data asas Kos pelaksanaannya adalah kecil, jika terdapat banyak bacaan sekunder, ia akan meningkatkan tekanan operasi baca pada hos. Sebagai contoh, jika penggodam memecahkan akaun secara ganas, ia akan membawa kepada sejumlah besar operasi bacaan sekunder Hos mungkin tidak dapat menahan tekanan operasi membaca dan runtuh.

  • Semua operasi baca dan tulis perniagaan utama ditujukan kepada hos dan perniagaan tidak kritikal menggunakan pemisahan baca dan tulis

  Sebagai contoh, untuk sistem pengurusan pengguna, Semua operasi baca dan tulis pendaftaran dan log masuk perniagaan mengakses hos Pengenalan, cinta, tahap dan perkhidmatan lain pengguna boleh menggunakan pemisahan baca dan tulis, kerana walaupun pengguna menukar pengenalan diri, dia. akan melihat bahawa pengenalan diri masih sama apabila membuat pertanyaan Kesan perniagaan adalah jauh lebih kecil daripada tidak dapat log masuk, dan ia boleh diterima.

  58 Bagaimanakah anda biasanya membahagikan pangkalan data?

  • Pembahagian pangkalan data menegak: Berdasarkan jadual, jadual berbeza dibahagikan kepada pangkalan data berbeza mengikut pemilikan perniagaan yang berbeza.

  

  • Sub-pangkalan data mendatar: Berdasarkan medan dan mengikut strategi tertentu (cincang, julat, dll.), data dalam satu pangkalan data dibahagikan menjadi berbilang dalam perpustakaan.

  

  59 Jadi bagaimana anda membahagikan jadual?

  • Pemisahan jadual mendatar: Pisahkan data dalam satu jadual kepada berbilang jadual berdasarkan medan dan mengikut strategi tertentu (cincang, julat, dsb.).
  • Pemisahan jadual menegak: Berdasarkan medan dan mengikut aktiviti medan, medan dalam jadual dibahagikan kepada jadual yang berbeza (jadual utama dan jadual lanjutan).

  

  60. Apakah kaedah penghalaan untuk sharding mendatar?

  Apakah penghalaan? Itulah jadual mana data harus dibahagikan.

  Terdapat tiga kaedah penghalaan utama untuk pembahagian jadual mendatar:

  • Penghalaan julat : Pilih lajur data tersusun (contohnya, integer, cap waktu, dsb.) sebagai laluan Mengikut syarat, segmen berbeza bertaburan ke dalam jadual pangkalan data yang berbeza.

  Kami boleh memerhati beberapa sistem pembayaran dan mendapati kami hanya boleh menyemak rekod pembayaran dalam tempoh setahun Ini mungkin kerana syarikat pembayaran telah membahagikan jadual mengikut masa.

  

  Kerumitan reka bentuk penghalaan julat terutamanya ditunjukkan dalam pemilihan saiz segmen Jika segmen terlalu kecil, ia akan membawa kepada terlalu banyak sub-jadual selepas pembahagian dan meningkatkan kerumitan penyelenggaraan ; Segmen yang terlalu besar boleh menyebabkan masalah prestasi dalam satu jadual.

  Kelebihan penghalaan julat ialah jadual baharu boleh dikembangkan dengan lancar apabila data bertambah. Sebagai contoh, jika bilangan pengguna semasa ialah 1 juta, jika bilangannya meningkat kepada 10 juta, anda hanya perlu menambah jadual baharu, dan data asal tidak perlu diubah. Kelemahan penghalaan julat yang agak tersirat ialah pengedaran tidak sekata Jika jadual dibahagikan kepada 10 juta keping, ada kemungkinan jumlah sebenar data yang disimpan dalam segmen hanya 1,000, manakala jumlah sebenar data yang disimpan dalam segmen lain ialah 900. Sepuluh ribu.

  • Hash routing: Pilih nilai lajur tertentu (atau gabungan lajur tertentu) untuk melaksanakan operasi Hash, dan kemudian sebarkan ia ke jadual pangkalan data yang berbeza berdasarkan Hasil hash.

  Juga mengambil id pesanan sebagai contoh, jika kita merancang 4 jadual pangkalan data dari awal, algoritma penghalaan hanya boleh menggunakan nilai id % 4 untuk mewakili nombor jadual pangkalan data yang mana data milik, dan id ialah Urutan 12 diletakkan dalam jadual kecil bernombor 50, dan susunan dengan id 13 diletakkan dalam jadual kecil bernombor 61.

  

  Kerumitan reka bentuk penghalaan Hash terutamanya ditunjukkan dalam pemilihan bilangan jadual awal yang menyusahkan untuk diselenggara dan terlalu sedikit jadual boleh menyebabkan masalah prestasi dengan satu jadual. Selepas menggunakan penghalaan Hash, adalah sangat menyusahkan untuk menambah bilangan sub-jadual, dan semua data mesti diagihkan semula. Kebaikan dan keburukan penghalaan Hash pada asasnya adalah bertentangan dengan penghalaan julat Kelebihan penghalaan Hash ialah jadual diedarkan secara sekata Kelemahannya ialah menyusahkan untuk mengembangkan jadual baharu dan semua data mesti diagihkan semula.

  • Mengkonfigurasi penghalaan : Mengkonfigurasi penghalaan ialah jadual penghalaan, menggunakan jadual bebas untuk merekodkan maklumat penghalaan. Mengambil id pesanan sebagai contoh, kami menambah jadual order_router yang baharu Jadual ini mengandungi dua lajur: orderjd dan tablejd yang sepadan boleh ditanya berdasarkan orderjd.

  Mengkonfigurasi penghalaan adalah mudah dalam reka bentuk dan sangat fleksibel untuk digunakan, terutamanya apabila mengembangkan jadual Anda hanya perlu memindahkan data yang ditentukan dan kemudian mengubah suai jadual penghalaan.

  

  Kelemahan mengkonfigurasi penghalaan ialah ia mesti disoal sekali lagi, yang akan menjejaskan prestasi keseluruhan dan jika jadual penghalaan itu sendiri terlalu besar (contohnya, ratusan juta data), prestasi juga mungkin menjadi Bottleneck: Jika kita membahagikan jadual penghalaan kepada pangkalan data dan jadual sekali lagi, kita akan menghadapi masalah pemilihan algoritma penghalaan gelung yang tidak berkesudahan.

  61 Bagaimana untuk mencapai pengembangan kapasiti tanpa masa henti?

  Malah, pengembangan tanpa masa henti adalah operasi yang sangat menyusahkan dan berisiko Sudah tentu, temu bual itu lebih mudah untuk dijawab.

  • Peringkat pertama: penulisan berganda dalam talian, tanya pangkalan data lama

    • Tubuhkan struktur jadual pangkalan data baharu, Apabila data ditulis ke pangkalan data jangka panjang, ia juga ditulis ke pangkalan data baru berpecah

    • Penghijrahan data, gunakan program migrasi data untuk memindahkan data sejarah dalam pangkalan data lama ke pangkalan data baharu

    • Gunakan tugas berjadual untuk membandingkan data pangkalan data lama dan baharu untuk mengisi perbezaan

  

  • Peringkat kedua: penulisan dwi dalam talian, menanyakan pangkalan data baharu

    • Menyelesaikan penyegerakan dan pengesahan data sejarah

    • Tukar bacaan data kepada pustaka baharu

  

  • Peringkat ketiga: pangkalan data lama di luar talian

    • Pangkalan data lama tidak lagi menulis data baharu

    • Selepas tempoh masa, Selepas mengesahkan bahawa tiada permintaan untuk perpustakaan lama, anda boleh offline perpustakaan lama

  

  62. Sub-perpustakaan dan perisian tengah meja yang biasa digunakan Mana yang ada?

  • sharding-jdbc
  • Mycat

  63 Jadi apakah masalah yang anda fikir akan disebabkan oleh sub-pangkalan data dan sub-jadual?

  Dari perspektif sub-pangkalan data:

  • Isu transaksi

  Terdapat perbezaan besar apabila menggunakan pangkalan data hubungan Kerana ia menjamin integriti transaksi.

  Selepas pangkalan data dipecahkan, transaksi mesin tunggal tidak lagi diperlukan dan mesti diselesaikan menggunakan transaksi teragih.

  • Masalah JOIN merentas pangkalan data

  Apabila kita berada dalam satu pangkalan data, kita juga boleh menggunakan JOIN untuk menanyakan jadual, tetapi selepas melintasi pangkalan data , JOIN tidak boleh digunakan lagi.

  Penyelesaian pada masa ini adalah untuk melaksanakan perkaitan dalam kod perniagaan, iaitu, mula-mula semak data satu jadual, dan kemudian semak jadual lain melalui hasil yang diperoleh, dan kemudian Gunakan kod untuk mengaitkan untuk mendapatkan hasil akhir.

  Kaedah ini lebih rumit sedikit untuk dilaksanakan, tetapi ia boleh diterima.

  Terdapat juga beberapa medan berlebihan yang boleh sesuai. Sebagai contoh, jadual sebelumnya menyimpan ID korelasi, tetapi perniagaan sering memerlukan Nama yang sepadan atau medan lain untuk dikembalikan. Pada masa ini, medan ini boleh ditambah secara berlebihan pada jadual semasa untuk mengalih keluar operasi yang memerlukan perkaitan.

  Cara lain ialah keheterogenan data, yang menggunakan penyegerakan binlog dan kaedah lain untuk mengisomerikan data yang memerlukan gabungan pangkalan data silang ke dalam struktur storan seperti ES, dan menanyakannya melalui ES.

  Dari perspektif sub-jadual:

  • Kiraan nod silang, tertib mengikut, kumpulan mengikut dan isu fungsi agregat

  Ia hanya boleh dilaksanakan melalui kod perniagaan atau menggunakan perisian tengah untuk meringkaskan, mengisih, halaman dan mengembalikan data dalam setiap jadual.

  • Penghijrahan data, perancangan kapasiti, pengembangan dan isu-isu lain

  Penghijrahan data, cara merancang kapasiti, sama ada pengembangan mungkin diperlukan lagi dalam masa depan, dsb., adalah semua isu yang perlu dipertimbangkan.

  • Masalah ID

  Selepas jadual pangkalan data dipecahkan, kita tidak boleh lagi bergantung pada mekanisme penjanaan kunci utama pangkalan data itu sendiri, jadi beberapa cara diperlukan untuk memastikan keadaan keseluruhan Kunci utama adalah unik.

  • Ia masih meningkat sendiri, tetapi saiz langkah yang meningkat sendiri ditetapkan. Sebagai contoh, terdapat tiga jadual sekarang, saiz langkah ditetapkan kepada 3, dan nilai ID awal bagi tiga jadual ialah 1, 2, dan 3 masing-masing. Dengan cara ini, pertumbuhan ID jadual pertama ialah 1, 4, dan 7. Jadual kedua ialah 2, 5, 8. Jadual ketiga ialah 3, 6, 9, jadi tidak akan ada pertindihan.

  • UUID, ini adalah yang paling mudah, tetapi pemasukan kunci utama yang tidak berterusan akan menyebabkan pemisahan halaman yang serius dan prestasi yang lemah.

  • ID yang diedarkan, yang lebih terkenal ialah algoritma kepingan salji sonwflake sumber terbuka Twitter

  Operasi dan penyelenggaraan

  64. juta Bagaimana untuk memadam data di atas tahap?

  Mengenai indeks: Memandangkan indeks memerlukan kos penyelenggaraan tambahan, kerana fail indeks adalah fail yang berasingan, apabila kami menambah, mengubah suai atau memadam data, operasi tambahan pada fail indeks akan berlaku Operasi tambahan ini perlu dimakan, yang akan mengurangkan kecekapan pelaksanaan penambahan/pengubahsuaian/penghapusan.

  Jadi, apabila kami memadamkan berjuta-juta data dalam pangkalan data, kami merujuk manual rasmi MySQL dan mengetahui bahawa kelajuan pemadaman data adalah berkadar terus dengan bilangan indeks yang dicipta.

  • Jadi apabila kita ingin memadam berjuta-juta data, kita boleh memadamkan indeks dahulu

  • dan kemudian memadamkan data yang tidak berguna itu

  • Membuat semula indeks selepas pemadaman selesai juga sangat pantas

  65 Bagaimana untuk menambah medan pada jadual besar dengan berjuta-juta tahap?

  Apabila jumlah data pangkalan data dalam talian mencecah berjuta-juta atau berpuluh-puluh juta, menambah medan bukanlah semudah itu kerana jadual mungkin dikunci untuk masa yang lama.

  Untuk menambah medan pada jadual besar, biasanya terdapat kaedah ini:

  • Tukar masa lalu melalui jadual perantaraan

    Buat jadual baharu sementara dan tukar jadual lama Salin sepenuhnya struktur, tambah medan, salin data jadual lama, padam jadual lama, dan namakan jadual baharu nama jadual lama Kaedah ini mungkin kehilangan beberapa data.

  • Gunakan pt-online-schema-change

    pt-online-schema-change ialah alat yang dibangunkan oleh percona Ia boleh mengubah suai struktur jadual dalam talian permukaan tengah.

  • Tambahkannya ke pangkalan data hamba dahulu dan kemudian tukar antara tuan dan hamba

    Jika jadual mempunyai jumlah data yang besar dan merupakan jadual panas (membaca dan menulis adalah sangat kerap), anda boleh mempertimbangkan terlebih dahulu Tambahnya pada pangkalan data hamba, kemudian tukar antara induk dan hamba, dan kemudian tambah medan pada beberapa nod lain selepas suis.

  66. Bagaimana untuk menangani lonjakan CPU pangkalan data MySQL?

  Proses penyelesaian masalah:

  (1) Gunakan arahan atas untuk memerhati dan menentukan sama ada ia disebabkan oleh mysqld atau sebab lain.

  (2) Jika ia disebabkan oleh mysqld, tunjukkan senarai proses, semak status sesi, dan tentukan sama ada terdapat sebarang SQL yang memakan sumber berjalan.

  (3) Ketahui SQL dengan penggunaan tinggi dan lihat sama ada pelan pelaksanaan adalah tepat, sama ada indeks hilang dan sama ada jumlah data terlalu besar.

  Pemprosesan:

  (1) Matikan benang ini (sambil memerhati sama ada penggunaan CPU berkurangan),

  (2) Buat pelarasan yang sepadan (seperti menambah indeks, Tukar sql , tukar parameter memori)

  (3) Jalankan semula SQL ini.

  Situasi lain:

  Mungkin juga setiap pernyataan SQL tidak menggunakan banyak sumber, tetapi tiba-tiba, sejumlah besar sesi disambungkan, menyebabkan CPU melonjak. anda perlu berbincang dengan aplikasi Mari analisa mengapa bilangan sambungan meningkat, dan kemudian buat pelarasan yang sepadan, seperti mengehadkan bilangan sambungan, dsb.

  [Cadangan berkaitan: tutorial video mysql]