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.
Sambungan MySQL terbahagi terutamanya kepada sambungan dalam dan sambungan luar yang biasa digunakan termasuk sambung kiri dan sambung kanan.
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.
char:
:
varchar mewakili rentetan panjang berubah dan panjangnya berubahKedua-dua jenis data menyimpan masa dalam format yang sama. Kedua-duanya ialah
YYYY-MM-DD HH:MM:SSKedua-dua jenis data boleh menyimpan saat pecahan dalam mikrosaat (6 saat perpuluhan selepas saat) 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:
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.
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?
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.
Kesan pelaksanaan:
Kelajuan pelaksanaan:
DARI: Jalankan seruling di atas meja kiri
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
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
14 Bercakap tentang infrastruktur MySQL?
是否有权限 terlebih dahulu Jika tiada kebenaran, mesej ralat akan dikembalikan secara terus Jika ada kebenaran, cache akan ditanya dahulu (sebelum versi MySQL8.0). 语法分析 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. 调用数据库引擎接口. 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.
Anda boleh memilih ini secara kasar:
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.
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.
Terdapat banyak fail log MySQL, termasuk:
Terdapat juga dua fail log khusus enjin storan InnoDB:
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.
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.
redo log的写入不是直接落到磁盘，而是在内存中设置了一片称之为redo log buffer的连续内存空间，也就是redo 日志缓冲区。
什么时候会刷入磁盘？
在如下的一些情况中，log buffer的数据会刷入磁盘：
log buffer 的大小是有限的，如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。如果当前写入 log buffer 的redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
在事务提交时，为了保证持久性，会把log buffer中的日志全部刷到磁盘。注意，这时候，除了本事务的，可能还会刷入其它事务的日志。
有一个后台线程，大约每秒都会刷新一次log buffer中的redo log到磁盘。
重做日志缓存、重做日志文件都是以块（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的监控主要通过两个途径：
慢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 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大
小表驱动大表
关联查询的时候要拿小表去驱动大表，因为关联的时候，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子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序
条件下推
MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引
最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化
此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。
explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。
直接在 select 语句之前增加explain关键字，就会返回执行计划的信息。
id: MySQL akan memberikan nilai id unik pada setiap pernyataan pilihan
select_type Lajur, jenis pertanyaan, dikelaskan mengikut perkaitan, kesatuan, subkueri, dsb. Jenis pertanyaan biasa termasuk MUDAH dan UTAMA.
jadual lajur: Menunjukkan jadual yang satu baris explain sedang diakses.
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 > >
: Apabila jadual hanya mempunyai satu baris rekod (jadual sistem), jumlah data adalah sangat kecil, cakera IO selalunya tidak diperlukan dan kelajuannya sangat pantas system
: 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
: Tekan kekunci utama eq_ref atau primary key indeks semasa pertanyaan, unique key ialah type. eq_ref
: Jenis cantuman ini serupa dengan ref, kecuali ref_or_null tambahan akan mencari baris yang mengandungi nilai MySQL. NULL
: Kaedah pengoptimuman gabungan indeks digunakan dan pertanyaan menggunakan lebih daripada dua indeks. <code>index_merge
: Gantikan unique_subquery subquery berikut, dan subquery mengembalikan set unik. IN
: Berbeza daripada <code>index_subquery, ia digunakan untuk indeks bukan unik dan boleh mengembalikan nilai pendua. unique_subquery
: 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
: <code>index dan Index sebenarnya membaca keseluruhan jadual Perbezaannya ialah ALL melintasi pokok indeks untuk membaca, manakala <code>index dibaca daripada cakera keras. ALL
possible_keys lajur: Menunjukkan indeks mana yang boleh digunakan oleh pertanyaan untuk mencari, yang lebih penting apabila menggunakan indeks untuk mengoptimumkan SQL. Lajur
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.
key_len lajur: menunjukkan perkara yang MySQL gunakan
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.
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.
Tambahan lajur: memaparkan maklumat tambahan yang tidak sesuai dalam lajur lain Walaupun ia dipanggil tambahan, terdapat juga beberapa maklumat penting:
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.
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
Sudah tentu tidak.
Enjin storan lalai MySQL ialah InnoDB, yang menggunakan indeks berstruktur B-tree.
Dalam gambar ini, terdapat dua perkara penting:
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.
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.
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.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.
在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
例如:select * from user where name = ‘张三’;
在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。
比如，select name from user where name = ‘张三’;
注意：最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。
最左匹配原则：在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 是第一个比较因子，所以就没用上索引。
索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。
例如一张表，建了一个联合索引（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服务器从存储引擎接收数据的次数。
如果按锁粒度划分，有以下3种：
Kunci kongsi (S Lock), juga dipanggil kunci baca (read lock), yang tidak menghalang satu sama lain.
Pelaksanaan utama kunci baris InnoDB adalah seperti berikut:
select * from t where id =6 for update;id=6
Kunci celah adalah untuk mengunci julat jurang tertentu. Apabila kami menggunakan pertanyaan kesamaan atau pertanyaan julat dan tidak menekan sebarang 
akan mengunci selang (1,6). recordselect * from t where id =3 for update;select * from t where id > 1 and id
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,
. 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,
.
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.
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.
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.
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.
Tahap pengasingan yang berbeza, masalah yang mungkin berlaku di bawah transaksi serentak:
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read Uncommited 读取未提交 | 是 | 是 | 是 |
| Read Commited 读取已提交 | 否 | 是 | 否 |
| Repeatable Read 可重复读 | 否 | 否 | 是 |
| Serialzable 可串行化 | 否 | 否 | 否 |
Baca tanpa komitmen
Baca tanpa komitmen, tidak perlu dikatakan, prinsip membaca tanpa mengunci diguna pakai.
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).
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.
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_IDDB_ROLL_PTRAndaikan terdapat jadual Seterusnya, terdapat dua user transaksi, masing-masing
, untuk dilaksanakan operasi 
DB_TRX_ID100200 Memandangkan setiap perubahan akan direkodkan dalam log update dahulu, dan gunakan
alamat log. Oleh itu, boleh dianggap bahawa log pengubahsuaian 
. Seperti berikut: undoDB_ROLL_PTRundo版本链ReadView
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 konsepdiperkenalkan untuk menyelesaikan masalah ini.
Read CommittedRepeatable ReadPandangan Baca ialah paparan baca yang dijana apabila transaksi melaksanakanRead Committedsyot kilat dibacaRepeatable 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.
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.
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:
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:
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.
Apakah penghalaan? Itulah jadual mana data harus dibahagikan.
Terdapat tiga kaedah penghalaan utama untuk pembahagian jadual mendatar:
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.
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 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.
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
Dari perspektif sub-pangkalan data:
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.
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:
Ia hanya boleh dilaksanakan melalui kod perniagaan atau menggunakan perisian tengah untuk meringkaskan, mengisih, halaman dan mengembalikan data dalam setiap jadual.
Penghijrahan data, cara merancang kapasiti, sama ada pengembangan mungkin diperlukan lagi dalam masa depan, dsb., adalah semua isu yang perlu dipertimbangkan.
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
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
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.
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]
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
