Bagaimana untuk mencipta indeks untuk medan JSON dalam MySQL

Pengenalan kepada Indeks Berbilang Nilai

Bermula daripada MySQL 8.0.17, InnoDB menyokong penciptaan indeks berbilang nilai ( Indeks Berbilang Nilai) , indeks ini ialah indeks kedua yang ditakrifkan pada lajur tatasusunan nilai storan JSON Terdapat beberapa rekod indeks untuk satu rekod data. Takrifan sintaks khusus untuk jenis indeks ini:

CAST(ungkapan SEBAGAI jenis ARRAY), seperti CAST(data->'$.zipcode' AS UNSIGNED ARRAY). Seperti indeks biasa, ia juga boleh dilihat dalam EXPLAIN.

Buat indeks berbilang nilai

Seperti indeks lain, indeks berbilang nilai boleh ditambah semasa membuat jadual, atau dibuat melalui ALTER TABLE atau CREATE INDEX.

Indeks Medan Objek JSON

Sintaks

ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array ) ) );

Nota: Terdapat dua lapisan kurungan tunggal di luar sintaks CAST di sini! , jika anda kurang menulis satu, ralat akan dilaporkan!

Kes Ujian

PS: Kes dalam artikel merujuk kepada kes dalam dokumen rasmi dan hanya digunakan sebagai ujian, jadi penamaan tidak begitu standard dan mesti ketat dalam proses pembangunan sebenar Ikuti spesifikasi pembangunan pasukan syarikat dan jangan malas!

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '{"key":94582,"value":"asdf"}' ),
	( NULL, NOW(), '{"key":94568,"value":"gjgasdasdf"}' ),
	( NULL, NOW(), '{"key":94477,"value":"ghasdfsdf"}' ),
	( NULL, NOW(), '{"key":94536,"value":"hagsdfgdf"}' ),
	( NULL, NOW(), '{"key":94507,"value":"wasfgjdf"}' );
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$.key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$.key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$.key',
	CAST( '[94582]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$.key',
	CAST( '[94477]' AS JSON ));

Lihat pelan pelaksanaan dan ketahui bahawa indeks boleh digunakan:

Jika anda perlu mencipta indeks berbilang nilai untuk jenis aksara, ia mestilah set aksara utf8mb4 Dan peraturan pengisihan ialah utf8mb4_0900_as_cs, jika tidak, ralat akan dilaporkan versi ini tidak menyokongnya:

Jika anda ingin mencipta indeks berbilang nilai untuk binari. rentetan binari, peraturan pengisihan mestilah binari, jika tidak ralat akan dilaporkan dan ia tidak disokong.

Selepas mengubah suai peraturan pengisihan, indeks boleh ditambah dengan jayanya:

Indeks objek tatasusunan JSON

Sintaks

ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array ) ) );

Nota: Terdapat dua lapisan kurungan tunggal di luar sintaks CAST! Jika anda kurang menulis satu, ralat akan dilaporkan!

Kes ujian

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, NOW(), '[{"key":94536}]'),
	( NULL, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
ALTER TABLE customers ADD INDEX idx_mv_custinfo_list( ( CAST( custinfo -> '$[*].key' AS UNSIGNED array)) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94507 MEMBER OF ( custinfo -> '$[*].key' );
/*测试 JSON_CONTAINS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_CONTAINS(
		custinfo -> '$[*].key',
	CAST( '[94582, 94507]' AS JSON ));
/*测试 JSON_OVERLAPS 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	JSON_OVERLAPS (
		custinfo -> '$[*].key',
	CAST( '[94477, 94582]' AS JSON ));

Lihat pelan pelaksanaan dan ketahui bahawa indeks boleh digunakan:

Dibuat dalam indeks gabungan sintaks indeks berbilang nilai

sintaks

adalah serupa dengan indeks gabungan biasa, dan juga mengikut prinsip padanan paling kiri:

ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified
( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), modified );

Nota: kurungan perlu untuk digunakan di luar sintaks CAST di sini berdiri!

Kes ujian

DROP TABLE IF EXISTS `customers`;
/*建表语句*/
CREATE TABLE customers ( 
	id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	age tinyint(4) not null,
	modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	custinfo JSON NOT NULL 
);
/*插入写测试数据*/
INSERT INTO customers
VALUES
	( NULL, 21, NOW(), '[{"key":94582},{"key":94536}]'),
	( NULL, 22, NOW(), '[{"key":94568},{"key":94507},{"key":94582}]'),
	( NULL, 23, NOW(), '[{"key":94477},{"key":94507}]'),
	( NULL, 24, NOW(), '[{"key":94536}]'),
	( NULL, 25, NOW(), '[{"key":94507},{"key":94582}]');
/*添加多值索引*/
alter table customers DROP INDEX idx_age_custinfo$list_modified ;
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )),modified );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ((CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )), age,modified  );
ALTER TABLE customers ADD INDEX idx_age_custinfo$list_modified ( age,modified, (CAST( custinfo -> '$[*].key' AS UNSIGNED ARRAY )) );
/*测试 MEMBER OF 语法*/
SELECT
	* 
FROM
	customers 
WHERE
	94536 MEMBER OF ( custinfo -> '$[*].key' ) and modified = '2021-08-05 10:36:34' and age = 21;

Lihat pelan pelaksanaan dan ketahui bahawa indeks boleh digunakan:

Penghadan indeks berbilang nilai

• Indeks berbilang nilai hanya dibenarkan mengandungi nilai satu atribut

• Indeks ini pada masa ini hanya menyokong tiga Sintaks

Pada masa ini, hanya tiga sintaks: MEMBER OF, JSON_CONTAINS(), dan JSON_OVERLAB() boleh menggunakan indeks berbilang nilai.

• Nilai indeks mesti ditukar kepada tatasusunan

( CAST( custinfo -> '$.key ' AS UNSIGNED array)), tatasusunan dalam sintaks boleh ditinggalkan Sebab mengapa ia terpaksa ditambah adalah kerana jika ia tidak ditambah, ia bukan struktur tatasusunan, di atas tiga sintaks tidak boleh digunakan secara langsung Ia perlu ditukar melalui JSON_ARRAY() dan kaedah lain Ia hanya boleh digunakan kemudian, yang akan menyebabkan indeks menjadi tidak sah. Oleh itu, tidak kira sama ada medan yang akan diindeks ialah medan nilai tunggal atau medan tatasusunan, kata kunci tatasusunan mesti ditambah.

• Indeks ini tidak disokong untuk perkaitan jadual

• Tidak boleh digabungkan dengan indeks awalan

• Tidak menyokong penciptaan indeks berbilang nilai dalam talian

Ayat ini bermaksud operasi menggunakan ALGORITHM=COPY , iaitu, melalui Buat struktur jadual baharu dan kemudian salin data untuk mencipta indeks. Oleh itu operasi DML tidak dibenarkan semasa proses ini.

• Indeks berbilang nilai mempunyai keperluan yang jelas untuk medan jenis set aksara

Peraturan pengumpulan set aksara binari mestilah Pengumpulan perduaan

set aksara utf8mb4 mestilah utf8mb4_0900_as_cs

Sebarang set aksara atau himpunan lain tidak boleh mencipta indeks berbilang nilai dan ralat akan dilaporkan semasa menciptanya versi tidak menyokongnya.

Senario Aplikasi

Senario aplikasi indeks berbilang nilai adalah sangat luas! Dengan dia, banyak jadual perhubungan tidak boleh digunakan lagi! Mari kita ambil contoh mudah: teg pengguna Dalam banyak senario, pengguna akan diberi pelbagai tag, seperti 1 tinggi, 2 kaya, 3 tampan Untuk membuat statistik atau pertanyaan penapisan berikutnya dengan lebih cekap, kami tidak boleh menggunakan teg ini secara langsung Storan, kerana kecekapan pertanyaan tidak tinggi tanpa indeks, jadual perkaitan sering digunakan untuk menyimpan perhubungan tag pengguna. Tetapi kini dengan indeks berbilang nilai, kami boleh menyimpan teg sebagai medan!

Ini hanyalah satu daripada adegan kecil Terdapat banyak adegan yang serupa. Pengguna boleh menukarnya kepada apa-apa sahaja, dan label itu juga boleh ditukar kepada mana-mana atribut lain hubungan banyak-ke-banyak Kemudian jika atribut ini tidak perlu dikaitkan dengan jadual lain), anda boleh menggunakan indeks berbilang nilai! Indeks berbilang nilai tidak menyokong perkaitan jadual, jadi adalah tidak sesuai jika anda perlu menggunakan medan ini untuk perkaitan jadual.

Atas ialah kandungan terperinci Bagaimana untuk mencipta indeks untuk medan JSON dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!