Bagaimana untuk menyelesaikan masalah bahawa operasi memadam mysql sebenarnya adalah pemadaman palsu

Operasi pemadaman mysql sebenarnya adalah pemadaman palsu

Dalam InnoDB, operasi pemadaman anda sebenarnya tidak hanya menandakan data yang dipadamkan sebagai dipadamkan data dalam jadual, ruang yang diduduki oleh fail jadual pada cakera tidak akan menjadi lebih kecil Kami memanggilnya pemadaman palsu buat masa ini.

Kita boleh mengesahkannya dengan contoh

Mari kita ikuti contoh dalam artikel sebelumnya Buat prosedur tersimpan, masukkan 100,000 keping data, dan kemudian lihat berapa banyak daripada 100,000 keping. data menempati ruang.

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

#定义分割符号，mysql 默认分割符为分号;，这里定义为 //
#分隔符的作用主要是告诉mysql遇到下一个 // 符号即执行上面这一整段sql语句
delimiter //
 
#创建一个存储过程，并命名为 testData
create procedure testData() 
 
#下面这段就是表示循环往表里插入10w条数据
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end //  #这里遇到//符号，即执行上面一整段sql语句
 
delimiter ; #恢复mysql分隔符为;
 
call testData(); #调用存储过程

#下面这两条命令可以查看表文件所占空间大小
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 3.52M                                           |
+-------------------------------------------------+
1 row in set (0.04 sec)

Anda dapat melihat bahawa 100,000 keping data menduduki 3.52M ruang dalam mysql, jadi mari kita laksanakan perintah padam padam dari t dan lihat.

#先删除表所有数据，再重新查看表文件大小
mysql> delete from t;
Query OK, 100000 rows affected (0.46 sec)
 
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 3.52M                                           |
+-------------------------------------------------+
1 row in set (0.00 sec)

Daripada keputusan, kita dapati bahawa selepas data jadual dikosongkan, ruang yang diduduki oleh jadual tidak berubah Ini mengesahkan kesimpulan di atas tidak benar-benar memadam data, dan ruang meja tidak dilepaskan.

Baris rekod yang dipadam ini hanya ditandakan untuk pemadaman dan boleh digunakan semula pada kali seterusnya rekod yang memenuhi syarat boleh dimasukkan terus ke kedudukan yang ditanda ini.

Contohnya, jika kita memadamkan rekod dengan id=500 antara rekod dengan id antara 300-600, rekod ini akan ditandakan sebagai dipadamkan jika terdapat rekod dengan id=400 yang akan dimasukkan kali berikutnya Masuk, kemudian anda boleh menggunakan semula lokasi yang ditandakan untuk pemadaman dengan id=500 Situasi ini dipanggil penggunaan semula rekod baris .

Situasi lain ialah penggunaan semula halaman data, yang bermaksud bahawa keseluruhan halaman data telah ditanda dan dipadamkan, jadi keseluruhan halaman data boleh digunakan semula, bersama-sama dengan rekod baris penggunaan semula halaman data hampir tidak mempunyai sekatan pada data yang akan dimasukkan.

Mengambil sisipan di atas sebagai contoh, jika rekod yang akan dimasukkan ialah id=1000, maka kedudukan id=500 tidak boleh digunakan semula, tetapi jika terdapat keseluruhan halaman data yang boleh digunakan semula, maka tidak kira daripada id Sebarang nilai boleh digunakan semula pada halaman ini.

Rekod ini yang telah ditandakan untuk dipadamkan sebenarnya adalah satu lubang. Mereka berasa seperti menduduki lubang tanpa mengambil apa-apa sahaja, ia juga akan menjejaskan kecekapan pertanyaan.

Kerana anda perlu tahu bahawa mysql menyimpan dan membaca data dalam unit halaman data di lapisan bawah Setiap kali anda membaca data dari cakera, anda membaca halaman data halaman, ia sepadan dengan halaman data operasi IO Cakera, cakera IO agak perlahan berbanding dengan kelajuan akses memori.

Jadi jika anda fikirkan, jika terdapat sejumlah besar lubang data dalam jadual, data yang asalnya hanya memerlukan satu halaman data untuk disimpan perlu disimpan dengan menambah halaman data lain kerana ruang yang diduduki oleh banyak lubang Data, oleh itu, apabila mysql menanyakan data yang sama, ia perlu meningkatkan operasi IO cakera, sekali gus menjejaskan kelajuan pertanyaan.

Malah, bukan sahaja operasi pemadaman akan menyebabkan lubang data, tetapi sisipan dan kemas kini juga akan menyebabkan lubang.

Oleh itu, selepas jadual data mengalami sejumlah besar penambahan, pemadaman dan pengubahsuaian yang kerap, tidak dapat dielakkan bahawa lubang data akan berlaku, membuang ruang dan menjejaskan kecekapan pertanyaan Biasanya dalam persekitaran pengeluaran, ini akan secara langsung nyatakan dirinya kerana pertanyaan asalnya pantas akan menjadi Lebih perlahan dan perlahan.

Dalam kes ini, biasanya kita boleh menggunakan arahan berikut untuk menyelesaikan masalah lubang data.

optimize table t

Prinsip arahan ini adalah untuk membina semula jadual, iaitu mencipta jadual sementara B, kemudian menanyakan semua data dalam jadual A (jadual dengan lubang data), dan kemudian memasukkan semula semua data ke dalam jadual sementara B , dan akhirnya menggantikan jadual A dengan jadual sementara B. Ini adalah proses membina semula jadual.

Jom cuba lagi.

Lihat kesannya

mysql> optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.39 sec)
 
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 0.02M                                           |
+-------------------------------------------------+
1 row in set (0.00 sec)

Anda dapat melihat bahawa saiz fail jadual telah menjadi 0.02M, menunjukkan bahawa ruang jadual telah dikeluarkan 0.02M ini sepatutnya saiz fail itu mentakrifkan struktur jadual.

Selain itu, arahan berikut juga boleh digunakan untuk membina semula jadual, yang boleh mencapai kesan yang sama seperti di atas. Anda boleh mencubanya.

alter table t engine=InnoDB

Perhatikan bahawa kandungan artikel ini adalah berdasarkan enjin InnoDB, dan mungkin terdapat beberapa perbezaan untuk enjin lain.

Atas ialah kandungan terperinci Bagaimana untuk menyelesaikan masalah bahawa operasi memadam mysql sebenarnya adalah pemadaman palsu. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!