Apakah sebab mengapa jadual sementara MySQL boleh mempunyai nama pendua?

Hari ini kita akan mulakan dengan soalan ini: Apakah ciri-ciri jadual sementara dan senario mana yang sesuai untuknya?

Di sini, saya perlu membantu anda menjelaskan isu yang mudah disalahfahamkan terlebih dahulu: Sesetengah orang mungkin berpendapat bahawa jadual sementara ialah jadual memori. Namun, kedua-dua konsep ini berbeza sama sekali.

• Jadual memori merujuk kepada jadual menggunakan enjin Memori Sintaks penciptaan jadual ialah buat jadual …engine=memory . **Data jadual jenis ini disimpan dalam memori dan akan dikosongkan apabila sistem dimulakan semula, tetapi struktur jadual kekal. **Kecuali dua ciri yang kelihatan "pelik" ini, daripada ciri lain, ia adalah jadual biasa.

• Meja sementara, boleh guna pelbagai jenis enjin. Jika anda menggunakan enjin InnoDB atau jadual sementara enjin MyISAM, data ditulis pada cakera semasa menulis. Sudah tentu, jadual sementara juga boleh menggunakan enjin Memori.

Selepas menjelaskan perbezaan antara jadual memori dan jadual sementara, mari kita lihat ciri jadual sementara.

Ciri-ciri jadual sementara

Untuk memudahkan pemahaman, mari kita lihat urutan operasi berikut:

Seboleh-bolehnya lihat, jadual sementara Ia mempunyai ciri berikut yang digunakan:

• Sintaks untuk mencipta jadual ialah buat jadual sementara ….

• Urutan lain tidak boleh mengakses jadual sementara yang dibuat oleh sesi dan ia hanya kelihatan kepada sesi itu. Oleh itu, jadual t sementara yang dibuat oleh sesi A dalam rajah tidak dapat dilihat oleh sesi B.

• Jadual sementara boleh mempunyai nama yang sama seperti jadual biasa.

• Apabila terdapat jadual sementara dan jadual biasa dengan nama yang sama dalam sesi A, kenyataan showcreate dan tambah, padam, ubah suai dan penyata pertanyaan mengakses jadual sementara.

• Arahan jadual tayang tidak memaparkan jadual sementara.

Memandangkan jadual sementara hanya boleh diakses oleh sesi yang menciptanya, jadual sementara akan dipadamkan secara automatik apabila sesi tamat.

Senario pengoptimuman gabungan dalam artikel sebelumnya amat sesuai untuk menggunakan jadual sementara kerana jadual sementara mempunyai ciri ini. kenapa? Sebabnya termasuk dua aspek berikut:

• Jadual sementara dalam sesi berbeza boleh mempunyai nama yang sama Jika terdapat berbilang sesi yang melakukan pengoptimuman gabungan pada masa yang sama , tidak perlu bimbang Penduaan nama jadual menyebabkan kegagalan penciptaan jadual.

• Tidak perlu risau tentang pemadaman data . Jika jadual biasa digunakan, jika pelanggan terputus sambungan secara tidak normal semasa pelaksanaan proses, atau pangkalan data dimulakan semula secara tidak normal, jadual data yang dijana semasa proses perantaraan perlu dibersihkan secara khusus. Memandangkan jadual sementara akan dikitar semula secara automatik, operasi tambahan ini tidak diperlukan.

Aplikasi jadual sementara

Memandangkan tidak perlu risau tentang konflik nama pendua antara benang, jadual sementara sering digunakan dalam proses pengoptimuman kompleks pertanyaan. Antaranya, pertanyaan silang pangkalan data sub-pangkalan data dan sistem sub-jadual ialah senario penggunaan biasa.

Secara amnya, senario pemecahan pangkalan data dan pemecahan jadual adalah untuk mengedarkan jadual besar secara logik kepada contoh pangkalan data yang berbeza. contohnya. Untuk medan f tertentu, bahagikan jadual besar ht kepada 1024 sub-jadual dan edarkan sub-jadual ini kepada 32 contoh pangkalan data. Seperti yang ditunjukkan dalam rajah di bawah:

Secara amnya, sistem sub-pangkalan data dan sub-jadual jenis ini mempunyai proksi perantaraan. Walau bagaimanapun, terdapat juga beberapa penyelesaian yang membolehkan pelanggan menyambung terus ke pangkalan data, iaitu, tiada lapisan proksi.

Dalam seni bina ini, pemilihan kekunci partition adalah berdasarkan prinsip "mengurangkan operasi pangkalan data silang dan jadual silang". Jika kebanyakan pernyataan akan mengandungi keadaan setara f, maka f harus digunakan sebagai kunci partition. Proksi yang telah menghuraikan pernyataan SQL akan memutuskan jadual mana yang hendak dihalakan untuk pertanyaan.

Sebagai contoh, pernyataan berikut:

select v from ht where f=N;

Pada masa ini, kita boleh menggunakan peraturan subjadual (contohnya, N%1024) untuk mengesahkan sub-jadual mana data yang diperlukan diletakkan pada. Pernyataan jenis ini hanya perlu mengakses satu sub-jadual, dan merupakan bentuk pernyataan yang paling popular dalam sub-pangkalan data dan skema sub-jadual.

Walau bagaimanapun, jika terdapat indeks k lain pada jadual ini, dan pernyataan pertanyaan adalah seperti ini:

select v from ht where k >= M order by t_modified desc limit 100;

Pada masa ini, memandangkan medan partition f tidak digunakan dalam keadaan pertanyaan, anda hanya boleh pergi ke Cari semua baris yang memenuhi syarat dalam semua partition, dan kemudian lakukan susunan mengikut operasi secara seragam. Dalam kes ini, terdapat dua idea yang biasa digunakan.

Idea pertama ialah untuk melaksanakan pengisihan dalam kod proses lapisan proksi. Kelebihan kaedah ini ialah kelajuan pemprosesan adalah pantas Selepas mendapat data daripada sub-pangkalan data, ia boleh terus mengambil bahagian dalam pengiraan dalam memori. Walau bagaimanapun, kelemahan penyelesaian ini juga jelas:

• memerlukan jumlah kerja pembangunan yang agak besar. Pernyataan yang kami berikan sebagai contoh adalah agak mudah Jika ia melibatkan operasi yang kompleks, seperti kumpulan oleh atau pun bergabung, keupayaan pembangunan lapisan tengah akan menjadi agak tinggi

• 对proxy端的压力比较大，尤其是很容易出现内存不够用和CPU瓶颈的问题。

另一种思路就是，把各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作。

比如上面这条语句，执行流程可以类似这样：

• 在汇总库上创建一个临时表temp_ht，表里包含三个字段v、k、t_modified；

• 在各个分库上执行select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

• 把分库执行的结果插入到temp_ht表中；

• 执行select v from temp_ht order by t_modified desc limit 100;

得到结果。 这个过程对应的流程图如下所示：

在实践中，我们往往会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库中的某一个上。

为什么临时表可以重名？

你可能会问，不同线程可以创建同名的临时表，这是怎么做到的呢？

我们在执行

create temporary table temp_t(id int primary key)engine=innodb;

这个语句的时候，MySQL要给这个InnoDB表创建一个frm文件保存表结构定义，还要有地方保存表数据。

这个frm文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程id}_ {线程id}_ 序列号”。

从文件名的前缀规则，我们可以看到，其实创建一个叫作t1的InnoDB临时表，MySQL在存储上认为我们创建的表名跟普通表t1是不同的，因此同一个库下面已经有普通表t1的情况下，还是可以再创建一个临时表t1的。

先来举一个例子。

进程号为1234的进程，它的线程id分别为4和5，分别属于会话A和会话B。因此，可以看出，session A和session B创建的临时表在磁盘上的文件名不会冲突。

MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。

• 一个普通表的table_def_key的值是由“库名+表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。

• 而对于临时表，table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”。

也就是说，session A和session B创建的两个临时表t1，它们的table_def_key不同，磁盘文件名也不同，因此可以并存。

在实现上，每个线程都维护了自己的临时表链表。这样每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在session结束的时候，对链表里的每个临时表，执行 “DROPTEMPORARY TABLE +表名”操作。

你会注意到，在binlog中也有DROP TEMPORARY TABLE命令的记录。你一定会觉得奇怪，临时表只在线程内自己可以访问，为什么需要写到binlog里面？这，就需要说到主备复制了。

临时表和主备复制

既然写binlog，就意味着备库需要。 你可以设想一下，在主库上执行下面这个语句序列：

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录，那么在备库就只有create table t_normal表和insert intot_normal select * fromtemp_t这两个语句的binlog日志，备库在执行到insert into t_normal的时候，就会报错“表temp_t不存在”。

你可能会说，如果把binlog设置为row格式就好了吧？因为binlog是row格式时，在记录insert intot_normal的binlog时，记录的是这个操作的数据，即：write_rowevent里面记录的逻辑是“插入一行数据（1,1)”。

确实是这样。如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog里。也就是说，只在binlog_format=statment/mixed的时候，binlog中才会记录临时表的操作。

在这种情况下，执行创建临时表语句的操作会被传递到备用数据库进行处理，从而触发备用数据库的同步线程创建相应的临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。因此，我们需要在主数据库中再运行一个DROP TEMPORARY TABLE命令以便备用数据库执行。

Tidak kira sama ada urutan berlainan pada pangkalan data utama mencipta jadual sementara dengan nama yang sama, tetapi bagaimana untuk mengendalikan pemindahan ke pangkalan data siap sedia untuk dilaksanakan?

Sekarang, izinkan saya memberi anda contoh Dalam urutan berikut, contoh S ialah pangkalan data siap sedia bagi M.

Dua sesi pada pangkalan data utama M telah mencipta jadual sementara t1 dengan nama yang sama Kedua-dua penyataan t1 jadual sementara ini akan dihantar ke pangkalan data siap sedia S.

Walau bagaimanapun, urutan log aplikasi pangkalan data siap sedia dikongsi, yang bermaksud bahawa kenyataan cipta mesti dilaksanakan dua kali dalam urutan aplikasi. Walaupun replikasi berbilang benang, masih boleh diberikan kepada pekerja yang sama dari perpustakaan hamba untuk dilaksanakan. Jadi, adakah ini akan menyebabkan urutan penyegerakan melaporkan ralat?

Jelas sekali tidak, jika tidak jadual sementara akan menjadi pepijat. Dalam erti kata lain, benang sandaran perlu menganggap kedua-dua jadual t1 sebagai jadual sementara bebas untuk diproses semasa pelaksanaan. Bagaimana ini dicapai? Apabila MySQL merekodkan binlog, ia akan menulis ID utas perpustakaan utama untuk melaksanakan pernyataan ini ke dalam binlog. Dengan cara ini, utas aplikasi dalam pangkalan data siap sedia boleh mengetahui ID utas pangkalan data utama yang melaksanakan setiap pernyataan dan menggunakan ID utas ini untuk membina table_def_key jadual sementara:

• Sementara jadual sesi A t1, table_def_key dalam pangkalan data siap sedia ialah: nama perpustakaan + t1 + "M's serverid" + "session A's thread_id"; dalam pangkalan data siap sedia Iaitu: nama perpustakaan + t1 + "M's serverid" + "session B's thread_id".

• Memandangkan table_def_key berbeza, kedua-dua jadual ini tidak akan bercanggah dalam urutan aplikasi pangkalan data siap sedia.

Atas ialah kandungan terperinci Apakah sebab mengapa jadual sementara MySQL boleh mempunyai nama pendua?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!