Bagaimana untuk memampatkan storan teks yang besar dalam MySQL

Seperti yang dinyatakan sebelum ini, kandungan syot kilat projek dokumen awan kami disimpan terus dalam db, iaitu storan teks yang besar Kebanyakan medan kandungan syot kilat dokumen adalah tahap kb. dan beberapa Malah ke peringkat MB. Pada masa ini, pengoptimuman caching CDN telah dijalankan untuk membaca data ( alat caching sumber statik - CDN masih perlu dioptimumkan jika ia boleh dilakukan dalam teks besar melalui beberapa algoritma pemampatan Storan termampat boleh menjimatkan ruang storan DB pada tahap yang besar dan melegakan tekanan I/O DB.

Analisis data inventori

select
  table_name as '表名',
  table_rows as '记录数',
  truncate(data_length/1024/1024, 2) as '数据容量(MB)',
  truncate(index_length/1024/1024, 2) as '索引容量(MB)',
  truncate(DATA_FREE/1024/1024, 2) as '碎片占用(MB)'
from
  information_schema.tables
where
  table_schema=${数据库名}
order by
  data_length desc, index_length desc;

Pengenalan kandungan berkaitan

data halaman enjin innodb melebihi Apa yang perlu dilakukan dengan 16kb?

Kita semua tahu bahawa saiz blok halaman lalai innodb ialah 16k Jika panjang baris data dalam jadual melebihi 16k, limpahan baris akan berlaku dan baris yang melimpah disimpan di tempat lain (nyahmampatkan. halaman gumpalan). Memandangkan innodb menggunakan indeks berkelompok untuk menyimpan data, iaitu struktur B+Tree, terdapat sekurang-kurangnya dua baris data dalam setiap blok halaman, jika tidak, makna B+Tree akan hilang, jadi had panjang maksimum bagi satu baris data diperolehi. Ia adalah 8k (medan besar akan menyimpan 768 bait data dalam halaman data, dan data yang selebihnya melimpah ke halaman lain. Halaman data juga mempunyai 20 bait untuk merekodkan alamat halaman limpahan)

• Ya Untuk format dinamik, jika saiz data yang disimpan dalam medan objek besar (teks/blob) adalah kurang daripada 40 bait, kesemuanya diletakkan pada halaman data dalam senario yang tinggal. halaman data hanya mengekalkan penunjuk 20 bait yang menunjuk ke halaman limpahan. Dalam senario ini, jika data yang disimpan dalam setiap medan objek besar adalah kurang daripada 40 bait, ia akan mempunyai kesan yang sama seperti varchar(40).
• innodb-row-format-dynamic: dev.mysql.com/doc/refman/…

Fail & Lubang Jarang Linux

• Fail Jarang: Fail Jarang pada asasnya sama dengan fail biasa yang lain Perbezaannya ialah sesetengah data dalam fail semuanya 0, dan bahagian data ini tidak menempati ruang cakera
• Lubang fail. : anjakan fail Jumlah boleh lebih besar daripada panjang sebenar fail (bait yang ada dalam fail tetapi belum ditulis ditetapkan kepada 0 sama ada lubang menempati ruang cakera ditentukan oleh sistem pengendalian
  • ).

Bahagian lubang fail tidak menempati ruang cakera dan ruang cakera yang diduduki oleh fail masih berterusan

Skim mampatan disediakan oleh innodb

Mampatan halaman

Senario yang berkenaan: Senario di mana jumlah data terlalu besar, ruang cakera tidak mencukupi, beban ditunjukkan terutamanya dalam IO, dan CPU pelayan mempunyai saiz yang agak besar margin.

1) KOMPRESKAN pemampatan halaman

Dokumen berkaitan: dev.mysql.com/doc/refman/…

• Fungsi pemampatan halaman yang disediakan sebelum MySQL versi 5.7, nyatakan ROW_FORMAT = COMPRESS semasa membuat jadual, dan tetapkan saiz halaman yang dimampatkan melalui KEY_BLOCK_SIZE
• Terdapat kelemahan reka bentuk, mungkin Ia akan membawa kepada kemerosotan prestasi yang jelas, dan reka bentuk asalnya adalah untuk meningkatkan prestasi, memperkenalkan konsep "log ialah data"
  • Untuk pengubahsuaian data halaman termampat, halaman itu sendiri tidak akan diubah suai secara langsung, tetapi log akan diubah suai. Disimpan dalam halaman ini, ini memang lebih mesra kepada perubahan data Tidak perlu dimampatkan/nyahmampat setiap kali diubah suai
    • 
  <. 🎜>Untuk bacaan data , data yang dimampatkan tidak boleh dibaca secara langsung, jadi algoritma ini akan mengekalkan halaman 16K yang dinyahmampat dalam memori untuk bacaan data
  • • 
  Ini menghasilkan halaman yang mungkin mempunyai dua versi (versi termampat dan versi tidak dimampatkan) dalam kumpulan penimbal, menyebabkan masalah yang sangat serius, iaitu bilangan halaman yang boleh dicache dalam kumpulan penimbal dikurangkan dengan banyak, yang mungkin menyebabkan penurunan hebat dalam prestasi pangkalan data

2) TPC (Mampatan Halaman Lutsinar)

Dokumen berkaitan:

mysql.com/doc/ refman/…

Prinsip kerja: Apabila menulis halaman, gunakan algoritma pemampatan yang ditentukan untuk memampatkan halaman, dan tuliskannya pada cakera selepas pemampatan, melalui mekanisme penebuk lubang Lepaskan nol daripada hujung halaman (memerlukan sistem pengendalian untuk menyokong ciri
• ) 空洞
• Fungsi pemampatan halaman TPC boleh didayakan, tetapi ini hanya memampatkan data tambahan berikutnya jika anda menjangkakan untuk memampatkan keseluruhan jadual, Anda perlu melaksanakan proses pelaksanaan ALTER TABLE xxx COMPRESSION = ZLIBOPTIMIZE TABLE xxx
• : halaman yang dimampatkan ialah halaman yang tidak dimampatkan 16K dalam kumpulan penimbal Hanya apabila data disiram, ia akan dimampatkan sekali, dan kandungan yang tinggal selepas pemampatan akan menjadi Ruang akan diisi dengan 0x00, dan tebuk lubang sistem fail digunakan untuk memangkas fail untuk melepaskan ruang yang jarang diduduki oleh 0x00

Walaupun TPC bagus, ia bergantung pada ciri Hole Punch sistem pengendalian, dan saiz fail yang dipangkas perlu diselaraskan dengan saiz blok sistem fail (4K). Iaitu, jika saiz halaman yang dimampatkan ialah 9K, maka ruang yang diduduki sebenar ialah 12K

Mampatan Lajur

MySQL pada masa ini tidak mempunyai penyelesaian langsung untuk pemampatan lajur, tetapi terdapat cara melengkung untuk menyelamatkan negara , iaitu menggunakan fungsi pemampatan dan penyahmampatan yang disediakan oleh MySQL pada lapisan perniagaan untuk melaksanakan operasi pemampatan dan penyahmampatan pada lajur. Iaitu, jika anda perlu memampatkan lajur tertentu, panggil fungsi

untuk memampatkan kandungan lajur itu semasa menulis dan gunakan fungsi COMPRESS untuk menyahmampat data termampat semasa membaca. UNCOMPRESS

Senario penggunaan: Untuk situasi di mana panjang data beberapa lajur dalam jadual adalah agak besar, biasanya varchar, text, blob, json dan jenis data lain
Fungsi berkaitan:
• Fungsi mampatan:
  • COMPRESS()
  Fungsi penyahmampatan:
  • UNCOMPRESS()
  Fungsi panjang rentetan:
  • LENGTH()
  Fungsi panjang rentetan tidak dimampatkan:
  • UNCOMPRESSED_LENGTH()
Ujian:
• Sisipkan data:
  • insert into xxx (content) values (compress('xxx....'))
  • Baca data termampat:

    select c_id, uncompressed_length(c_content) uncompress_len, length(c_content) compress_len from xxx

为什么innodb提供的都是基于页面的压缩技术？

• 记录压缩：每次读写记录的时候，都要进行压缩或解压，过度依赖CPU的计算能力，性能相对会比较差
• 表空间压缩：压缩效率高，但要求表空间文件是静态不增长的，这对于我们大部分的场景都是不适用的
• 页面压缩：既能提升效率，又能在性能中取得一定的平衡

总结

• 对于一些性能不敏感的业务表，如日志表、监控表、告警表等，这些表只期望对存储空间进行优化，对性能的影响不是很关注，可以使用COMPRESS页压缩
• 对于一些比较核心的表，则比较推荐使用TPC压缩
• 列压缩过度依赖CPU，性能方面会稍差，且对业务有一定的改造成本，不够灵活，需要评估影响范围，做好切换的方案。好处是可以由业务端决定哪些数据需要压缩，并控制解压操作
• 对页面进行压缩，在业务侧不用进行什么改动，对线上完全透明，压缩方案也非常成熟

为什么要进行数据压缩？

• 由于处理器和高速缓存存储器的速度提高超过了磁盘存储设备，因此很多时候工作负载都是受限于磁盘I/O。数据压缩可以使数据占用更小的空间，可以节省磁盘I/O、减少网络I/O从而提高吞吐量，虽然会牺牲部分CPU资源作为代价
• 对于OLTP系统，经常进行update、delete、insert等操作，通过压缩表能够减少存储占用和IO消耗
• 压缩其实是一种平衡，并不一定是为了提升数据库的性能，这种平衡取决于解压缩带来的收益和开销之间的一种权衡，但压缩对存储空间来说，收益无疑是很大的

简单测试

innodb透明页压缩（TPC）

参考：dev.mysql.com/doc/refman/…

测试数据

1）创建表

• create table table_origin ( ...... ) comment '测试原表';
• create table table_compression_zlib ( ...... ) comment '测试压缩表_zlib' compression = 'zlib';
• create table table_compression_lz4 ( ...... ) comment '测试压缩表_lz4' compression = 'lz4';

2）往表中写入10w行测试数据

压缩率

SELECT NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE
FROM information_schema.INNODB_TABLESPACES WHERE NAME like 'test_compress%';

• FS_BLOCK_SIZE：文件系统块大小，也就是打孔使用的单位大小
• FILE_SIZE：文件的表观大小，表示文件的最大大小，未压缩
• ALLOCATED_SIZE：文件的实际大小，即磁盘上分配的空间量

压缩率：

• zlib：1320636416/3489660928 = 37.8%
• lz4：1566949376/3489660928 = 45%

耗时

• 循环插入10w条记录
  • 原表：918275 ms
  • zlib：878540 ms
  • lz4：875259 ms
• 循环查询10w条记录
  • 原表：332519 ms
  • zlib：373387 ms
  • lz4：343501 ms

【相关推荐：mysql视频教程】

Atas ialah kandungan terperinci Bagaimana untuk memampatkan storan teks yang besar dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!