Meskipun MySQL belum ada, sekarang dimungkinkan untuk mencakup kasus penggunaan yang signifikan. menyimpan kolom yang didenormalisasi (atau array secara umum), dan mengaksesnya melalui index
Pada artikel ini saya akan memberikan beberapa konteks tentang data dan indeks yang didenormalisasi, termasuk solusi untuk fungsionalitas tersebut di MySQL 5. 7, dan jelaskan bagaimana ini (agak) dilakukan dengan bersih di MySQL 8. 0
Terminologi
Meskipun B-tree secara teknis adalah indeks terbalik, dalam konteks ini saya akan menggunakan istilah "indeks terbalik" untuk mendeskripsikan indeks berorientasi dokumen, seperti GIN PostgreSQL atau indeks teks lengkap InnoDB, dan saya akan merujuk ke B-tree dengan namanya
Selain itu, saya tidak akan membuat perbedaan apa pun antara B-tree dan B+trees, hanya menggunakan istilah "B-tree".
Menyimpan dan mengindeks array di MySQL 5. 7. pendekatan, dan masalah
MySQL tidak memiliki tipe data array. Ini adalah masalah mendasar dalam arsitektur di mana menyimpan baris yang didenormalisasi adalah persyaratan, misalnya, di mana MySQL (juga) digunakan untuk pergudangan data
Penyimpanan dan akses adalah dua sisi dari mata uang yang sama. hilangnya struktur data penyimpanan optimal untuk kelas data tertentu hampir pasti menyiratkan kurangnya algoritme terkait yang optimal;
Menyimpan array bukanlah masalah besar. dengan asumsi tipe data sederhana, seperti bilangan bulat, kita dapat dengan mudah mengadopsi solusi menggunakan kolom VARCHAR/TEXT untuk menyimpan nilai dengan pemisah sewenang-wenang (spasi adalah yang paling nyaman), namun, MySQL tidak dirancang untuk mengindeks skenario ini
Sekali lagi, kita dapat mengadopsi solusi lain. indeks teks lengkap. Kami dapat menyetel ke 1, tetapi ini memiliki kelemahan menjadi pengaturan global, atau menambahkan nilai, yang berfungsi, meskipun kurang optimal dalam hal penyimpanan
Ini adalah solusi yang berfungsi, jika benar-benar perlu. itu memiliki kelemahan dari dukungan indeks teks lengkap InnoDB, yang tidak sedikit, tetapi cukup baik
MySQL8. 0 implementasi. tipe data dan indeks
MySQL dapat menyimpan array sejak v5. 7, melalui tipe data JSON
-- Note how we're using the v8.0.19's new `ROW()` construct for inserting multiple rows. -- CREATE TEMPORARY TABLE t_json_arrays( id INT PRIMARY KEY AUTO_INCREMENT, c_array JSON NOT NULL ) SELECT * FROM ( VALUES ROW("[1, 2, 3]"), ROW(JSON_ARRAY(4, 5, 6)) ) v (c_array); SELECT * FROM t_json_arrays; -- +----+-----------+ -- | id | c_array | -- +----+-----------+ -- | 1 | [1, 2, 3] | -- | 2 | [4, 5, 6] | -- +----+-----------+
Kita dapat menyisipkan dokumen JSON (array) baik sebagai string, atau menggunakan fungsi JSON_ARRAY
Beberapa operator tersedia untuk mengakses data yang disimpan dalam dokumen JSON, mis. g. ->
-- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _
Namun, pengindeksan hanya diperkenalkan dengan v8. 0. 17, bersama dengan fungsi pencarian baru
-- This is a functional index. -- ALTER TABLE t_json_arrays ADD KEY ( (CAST(c_array -> '$' AS UNSIGNED ARRAY)) ); SELECT * FROM t_json_arrays WHERE 3 MEMBER OF (c_array); -- +----+-----------+ -- | id | c_array | -- +----+-----------+ -- | 1 | [1, 2, 3] | -- +----+-----------+ EXPLAIN FORMAT=TREE SELECT * FROM t_json_arrays WHERE 3 MEMBER OF (c_array -> '$'); -- -> Filter: json'3' member of (cast(json_extract(t_json_arrays.c_array,_utf8mb4'$') as unsigned array)) (cost=1.10 rows=1) -- -> Index lookup on t_json_arrays using functional_index (cast(json_extract(t_json_arrays.c_array,_utf8mb4'$') as unsigned array)=json'3') (cost=1.10 rows=1)
Perhatikan bagaimana kondisi WHERE harus mereplikasi persis bagian kunci fungsional (dalam hal ini, c_array -> '$')
Ekspektasi kinerja
Menurut , indeksnya adalah B-tree yang sedikit dimodifikasi
Secara umum, indeks multi-nilai adalah indeks fungsional biasa, dengan pengecualian bahwa indeks ini memerlukan penanganan tambahan di balik layar INSERT/UPDATE untuk bagian kunci multi-nilai
SHOW INDEXES FROM t_json_arrays WHERE Key_name NOT LIKE 'PRIMARY'\G -- *************************** 1. row *************************** -- Table: t_json_arrays -- Key_name: functional_index -- Index_type: BTREE -- [...]
Menggunakan B-tree sederhana untuk tujuan ini memiliki kelebihan dan kekurangan kebalikan dari indeks terbalik, perbedaan penting adalah bahwa biaya operasi meningkat secara linier dengan ukuran array yang disimpan.
Ini karena B-tree tidak memiliki pengoptimalan untuk penyisipan besar/batch (indeks terbalik berorientasi pada dokumen, jadi diharapkan penyisipan berukuran besar);
Di sisi lain, biaya DML adalah konstan; .
Mengapa beberapa array tidak dapat diindeks
Poin yang menarik adalah itu
Hanya satu bagian kunci bernilai banyak yang diperbolehkan per indeks, untuk menghindari ledakan eksponensial. e. g jika akan ada dua bagian kunci multi-nilai, dan server akan memberikan 10 nilai untuk masing-masing, SE harus menyimpan 100 catatan indeks
Mengapa demikian?
Karena tidak ada struktur data yang nyaman untuk mengoptimalkan kasus tersebut
Dengan struktur data saat ini, tuple [1, 2], [4, 5] akan menghasilkan kunci indeks
- -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _0,
- -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _1,
- -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _2,
- -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _3
Misalkan kita mengatasi masalah dengan mereduksi kunci ke komposisi setiap nilai larik pertama dengan larik kedua
- -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _4,
- -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _5
kami tidak dapat mencari secara efisien di kedua larik, karena indeks hanya pada elemen pertama;
- -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _6
hanya dapat mencari -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ _7 entri, bukan untuk -- Functionality for accessing JSON data -- SELECT id, c_array -> "$[1]" `array_entry_1` FROM t_json_arrays; -- +----+---------------+ -- | id | array_entry_1 | -- +----+---------------+ -- | 1 | 2 | -- | 2 | 5 | -- +----+---------------+ 8 entri
Kedengarannya akrab?
Array dari setiap tuple masih dapat diindeks secara independen;
Bagaimana cara mendeklarasikan kolom ARRAY UNSIGNED?
Kami telah bermain dengan penyimpanan dan pengindeksan array;
CREATE TEMPORARY TABLE t_json_arrays( id INT PRIMARY KEY AUTO_INCREMENT, c_array UNSIGNED ARRAY NOT NULL ); -- ERROR 1064 (42000): You have an error in your SQL syntax [...] near 'UNSIGNED ARRAY NOT NULL
Aduh. Saat ini tidak ada tipe data seperti itu. Secara internal, semuanya dilakukan melalui json;
[...] server membuat kolom yang dihasilkan secara virtual menggunakan bidang array yang diketik (bukan bidang biasa) untuk fungsi yang metode is_returns_array() mengembalikan nilai true. WL ini menambahkan satu fungsi seperti itu - CAST(… AS … ARRAY)
Bidang array yang diketik (kelas Field_typed_array) pada dasarnya adalah bidang JSON, turunan dari Field_json, tetapi melaporkan dirinya sebagai bidang biasa yang jenisnya adalah tipe elemen array yang diketik. […]
Menambahkan tipe data baru akan membutuhkan banyak pekerjaan;
Kesimpulan
Kami sangat senang dengan pengenalan tipe data ini, dan kami sedang dalam proses memigrasikan indeks teks lengkap yang digunakan untuk pseudo-array, ke kolom/indeks array berbasis JSON;
Catatan kaki
¹. Biaya penyisipan dalam B-tree tidak konstan, namun biaya pemeliharaan (penyeimbangan ulang) dapat diabaikan dalam konteks ini