Sejarah satu optimasi MySQL

Ini akan tentang pengoptimalan dalam database MySQL.

Ini terjadi ketika kami membuat sistem untuk buletin email. Sistem kami seharusnya mengirim puluhan juta surat sehari. Mengirim surat bukanlah tugas yang mudah, meskipun semuanya terlihat sangat primitif:

1. Kumpulkan surat dari kreatif html, gantikan data yang dipersonalisasi.
2. Tambahkan piksel yang melihat pesan, ganti semua tautan dalam pesan dengan milik Anda - untuk melacak klik.
3. Periksa sebelum mengirim bahwa email tidak ada dalam daftar hitam.
4. Kirim email ke kumpulan khusus.

Saya akan memberi tahu Anda lebih banyak tentang paragraf kedua:
Pembuat surat Microservice sedang mempersiapkan surat untuk dikirim:

• menemukan semua tautan dalam surat;
• uuid 32-karakter unik dihasilkan untuk setiap tautan;
• mengganti tautan asli dengan yang baru dan menyimpan data dalam database.

Dengan demikian, semua tautan sumber akan diganti dengan uuid, dan domain akan diubah menjadi milik kami. Saat Anda mendapatkan permintaan GET menggunakan tautan ini, kami proksi gambar asli atau mengalihkan ke tautan asli. Penghematan terjadi di database MySQL, kami menyimpan uuid yang dihasilkan bersama dengan tautan asli dan dengan beberapa informasi meta (email pengguna, id surat dan data lainnya). Denormalisasi membantu kita dalam satu permintaan untuk mendapatkan semua data yang diperlukan untuk menyimpan statistik, atau memulai semacam rantai pemicu.

Masalah nomor 1

Generasi uuid di dalam kita bergantung pada stempel waktu.

Karena pengiriman biasanya terjadi dalam periode waktu tertentu dan banyak contoh layanan microser untuk merakit surat diluncurkan, ternyata beberapa uuids sangat mirip. Ini memberi selektivitas rendah. UPD: karena datanya mirip, bekerja dengan bi-tree tidak terlalu efektif.

Kami memecahkan masalah ini menggunakan modul uuid di python, di mana tidak ada ketergantungan waktu.
Hal tersirat seperti itu mengurangi kecepatan indeks.

Bagaimana cara penyimpanan?

Struktur tabel adalah sebagai berikut:

CREATE TABLE IF NOT EXISTS `Messages` ( `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`UUID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Pada saat penciptaan, semuanya tampak logis:
UUID adalah kunci utama, dan juga merupakan indeks berkerumun. Ketika kami membuat pilihan pada bidang ini, kami cukup memilih catatan, karena semua nilai disimpan di sana. Ini adalah keputusan yang disengaja. Pelajari lebih lanjut tentang indeks berkerumun.

Semuanya bagus sampai meja tumbuh.

Masalah nomor 2

Jika Anda membaca lebih lanjut tentang indeks klaster, Anda dapat mengetahui tentang nuansa ini:

Saat menambahkan baris baru ke tabel, itu ditambahkan bukan ke akhir file, bukan ke akhir daftar datar, tetapi ke cabang yang diinginkan dari struktur pohon yang sesuai dengannya dengan menyortir.

Dengan demikian, dengan meningkatnya beban, waktu penyisipan meningkat.

Solusinya adalah menggunakan struktur tabel yang berbeda.

 CREATE TABLE IF NOT EXISTS `Messages` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UUID` (`UUID`, `Inserted`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Karena kunci primer sekarang bertambah secara otomatis, dan mysql menyimpan cache dari tempat penyisipan terakhir, sekarang penyisipan selalu terjadi di akhir, mis. Innodb dioptimalkan untuk menulis nilai yang meningkat secara berurutan.

Saya menemukan rincian optimasi ini dalam kode sumber postgres. Mysql mengimplementasikan optimasi yang sangat mirip.
Tentu saja, saya harus menambahkan kunci yang unik sehingga tidak ada konflik, tetapi kami meningkatkan kecepatan penyisipan.

Dengan basis yang semakin bertambah, kami berpikir untuk menghapus data lama. Menggunakan DELETE pada bidang yang Dimasukkan sama sekali tidak optimal - ini adalah waktu yang sangat lama, dan tempat itu tidak akan dibebaskan sampai kita menjalankan perintah tabel optimalisasi . Omong-omong, operasi ini benar-benar memblokir tabel - ini tidak cocok untuk kita sama sekali.

Oleh karena itu, kami memutuskan untuk membagi tabel kami menjadi partisi.
1 hari - 1 partisi, yang lama turun secara otomatis ketika saatnya tiba.

Masalah nomor 3

Kami mendapat kesempatan untuk menghapus data lama, tetapi kami tidak mendapatkan kesempatan untuk memilih dari partisi yang diinginkan, karena dengan select`e kami hanya menentukan uuid, mysql tidak tahu di partisi mana kita harus mencarinya dan mencari semuanya.

Solusinya lahir dari Masalah # 1 - tambahkan timestamp ke uuid yang dihasilkan. Hanya saja kali ini kami melakukan sedikit berbeda: kami memasukkan stempel waktu di tempat acak di garis, bukan di awal atau di akhir; sebelum dan sesudahnya mereka menambahkan simbol tanda hubung sehingga dapat diperoleh dengan ekspresi reguler.

Dengan pengoptimalan ini, kami dapat memperoleh tanggal saat uuid dihasilkan dan telah melakukan pemilihan yang menunjukkan nilai spesifik dari bidang yang Dimasukkan. Sekarang kita membaca data segera dari partisi yang kita butuhkan.

Juga, berkat hal-hal seperti ROW_FORMAT = COMPRESSED dan mengubah pengodean ke latin1 , kami menghemat lebih banyak ruang pada hard drive.