Dapatkah saya mengindeks kolom json mysql?

Database relasional seperti MySQL, Postgres, atau MSSQL dirancang untuk menyimpan data dua dimensi, bukan multidimensi. Jika data kami memiliki beberapa hubungan, maka kami memiliki 2 opsi. duplikasi dan/atau normalisasi. Juga; . (Ngomong-ngomong, yang favorit saya sejauh ini adalah Postgres. )

Mari kita lihat MySQL

Dukungan JSON di MySQL tidak bagus tetapi lebih baik daripada tidak sama sekali. Pada dasarnya; . Jika mau, Anda bisa mendapatkan (memilih) hanya beberapa bidang JSON ini, gunakan bidang ini dalam klausa WHERE untuk memfilter hasil, dll. Tetapi jika menyangkut kumpulan data besar, kinerjanya menjadi buruk. Sayangnya, MySQL tidak mendukung indeks pada bidang JSON, jadi kami tidak dapat menambahkan indeks ke bidang JSON secara langsung. Apa yang bisa kita lakukan untuk mendapatkan kinerja adalah membuat kolom yang dibuat dan menambahkan indeks ke dalamnya

Jadi, Jika Anda senang dengan MySQL dan satu-satunya kebutuhan Anda adalah menjalankan beberapa kueri dengan bidang JSON lebih cepat, menggunakan kolom yang dihasilkan MySQL dan mengindeksnya mungkin merupakan solusi yang baik untuk situasi ini

Izinkan saya mengucapkan kata-kata terakhir saya di awal; . Pendapat saya tentang memutuskan apakah akan menggunakan teknik ini

• Jika bisa, buat kolom lain dengan indeks dan simpan data di sana
• Jika Anda memiliki banyak bidang JSON pada tabel yang sama atau berbeda, MySQL mungkin merupakan pilihan yang salah untuk tumpukan Anda. Pertimbangkan untuk menggunakan Postgres atau alternatif basis data multidimensi seperti MongoDB
• Jika yang Anda butuhkan hanyalah menjalankan beberapa kueri yang berisi bidang JSON dengan cepat dan saat ini Anda tidak ingin bermigrasi ke database lain, teknik ini mungkin akan membantu Anda

Apa itu Kolom yang Dihasilkan MySQL?

MySQL menambahkan fitur yang disebut Generated Column di versi 5. 7. Dinamakan

{
    "country": "Turkey",
    "ip_address": "78.173.68.165",
    "device": "desktop"
}

Untuk skenario ini;

ADD COLUMN `full_name` varchar(255) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name))

Setelah menjalankan kueri, tabel akan bertindak seperti memiliki kolom nama_lengkap.

{
    "country": "Turkey",
    "ip_address": "78.173.68.165",
    "device": "desktop"
}

{
    "country": "Turkey",
    "ip_address": "78.173.68.165",
    "device": "desktop"
}

Menggunakan Kolom & Pengindeksan Virtual Untuk Kolom MySQL JSON

Sebagai contoh; . Jadi data yang akan kita simpan adalah sebagai berikut

{
    "country": "Turkey",
    "ip_address": "78.173.68.165",
    "device": "desktop"
}

Anggaplah kebutuhan kita adalah menemukan hitungan hit dari Turki. Kami dapat mengirim kueri ke MySQL sebagai berikut;

SELECT count(*) FROM hits WHERE user_data->>"$.country" = "Turkey"

Jika jumlah baris tidak banyak, kueri ini berfungsi dengan baik

Namun menambah jumlah baris akan membuat kueri ini sangat lambat. Dalam lingkungan pengujian saya, saya mengunggulkan MySQL dengan 10 juta baris dan waktu permintaan rata-rata adalah 5. 7 detik. (Saya menjalankan kueri berkali-kali, nilai ini adalah nilai rata-rata)

Sekarang, mari kita buat kolom virtual dan beri nama

{
    "country": "Turkey",
    "ip_address": "78.173.68.165",
    "device": "desktop"
}

ALTER TABLE hits ADD COLUMN `country_virtual` VARCHAR(40) GENERATED ALWAYS AS (`user_data` ->> '$.country') VIRTUAL;

Jadi, kami memiliki kolom country_virtual. Mari jalankan kueri berikut untuk menemukan jumlah klik dari Turki

SELECT count(*) FROM hits WHERE country_virtual = "Turkey"

Kabar baiknya adalah kueri berfungsi. Berita buruknya adalah membutuhkan waktu lebih lama dari kueri sebelumnya yang kami gunakan bidang JSON secara langsung, 6. 9 detik. Jadi, mari kita lakukan keajaiban dengan menambahkan index

CREATE INDEX `country_virtual_index` ON `hits`(`country_virtual`);  

Sekarang, kita dapat menemukan jumlah klik dari Turki dengan kueri berikut

SELECT count(*) FROM hits WHERE country_virtual = "Turkey"

Di lingkungan pengujian saya; . 7 detik

(Saya melakukan tes pada MySQL. 8 kontainer buruh pelabuhan. Lingkungan pengujian saya tidak dapat diandalkan; . ). Sebagai contoh; . 6 detik -> 24 md. Jika Anda berencana menggunakan teknik ini dalam produksi, saya sarankan Anda mengujinya di lingkungan seperti produksi yang memiliki data seperti produksi. )

Implementasi Di Laravel

Membuat kolom virtual dan menambahkan indeks ke dalamnya mudah dilakukan dengan migrasi Laravel. Sebagai contoh kita

// creating json column
$table->json("user_data");

// generating virtual column and adding index
$table->string('country_virtual')
    ->virtualAs("(`user_data` ->> '$.country')")
    ->index();

Karena itu;

$hits = App\Models\Hit::where("country_virtual", "Turkey")->count();

Karena saya sudah mengatakan kata-kata terakhir saya di awal (tentang pengambilan keputusan), saya selesai di sini. Semoga ini bisa membantu. 👋🏻

Bisakah kolom JSON diindeks secara langsung?

Bagaimana cara mengindeks data JSON di MySQL?

Bisakah JSON diindeks?

Bagaimana cara menanyakan kolom JSON di MySQL?