商家在后臺(tái)系統(tǒng)中根據(jù)商品名稱檢索商品，在商品數(shù)量有限時(shí)，可以使用模糊查找方式實(shí)現(xiàn)簡(jiǎn)單地檢索。如使用 WHERE columName like '%inputName%' 實(shí)現(xiàn)模糊查找。

然而有一種場(chǎng)景正在濫用模糊查找。

在優(yōu)惠券配置后臺(tái)中，需要具備優(yōu)惠券模版檢索能力，其中模板檢索條件是根據(jù)商品 ID，檢索哪些優(yōu)惠券模版關(guān)聯(lián)了指定商品。 一個(gè)優(yōu)惠券模版可以在多個(gè)商品上使用，兩者關(guān)聯(lián)關(guān)系為 1:N；

業(yè)界通常的實(shí)現(xiàn)方案有兩種：

• 1）使用 ElasticSerach實(shí)現(xiàn)全文檢索。
• 2）使用 MySQL 檢索。

當(dāng)數(shù)據(jù)量較少，檢索條件有限時(shí)，可優(yōu)先先選擇方案更加簡(jiǎn)單地MySQL方案。

如何實(shí)現(xiàn) 指定商品 ID 檢索關(guān)聯(lián)的優(yōu)惠券模版 功能呢？MySQL 有兩種方案

方案 1：模糊查找

通過模糊查找實(shí)現(xiàn)檢索，如 coupon_template 中 relatedProductIds 屬性記錄了 關(guān)聯(lián)的商品 Id 列表。

WHERE related_product_Ids like  '%${inputProductId}%'

這種方式并不準(zhǔn)確，例如模版 A 的related_product_ids = 110,111,112, 當(dāng)用戶輸入 11 時(shí)，模板 A 會(huì)被檢索出來。

如何優(yōu)化呢？可以將related_product_ids 存儲(chǔ)為 **"110,111,112,"**，增加 1 個(gè)逗號(hào)。  SQL語(yǔ)句調(diào)整為

WHERE related_product_Ids like  '%${inputProductId},%'

此種方式就萬無一失了嗎？不然，當(dāng)用戶輸入12 時(shí)，依然會(huì)錯(cuò)誤地檢索到模版 A。

再次優(yōu)化后，related_product_ids 存儲(chǔ)為 ",110,111,112," 即前后均添加逗號(hào)，SQL 語(yǔ)句調(diào)整為

WHERE related_product_Ids like  '%,${inputProductId},%' 前后添加逗號(hào)，用來截?cái)唷?/span>

方案 2：多值索引查找

MySQL 8.0版本以后，可以建立多值索引，應(yīng)用此種查詢場(chǎng)景。相比模糊查找方案，多值索引的檢索性能更好，方案更加簡(jiǎn)單。

什么是多值索引

對(duì)普通的索引來說，每一條記錄僅對(duì)應(yīng)一條索引記錄，對(duì)多值索引而言，一條記錄可以對(duì)應(yīng)多條索引記錄。

普通索引可以對(duì) user_id、order_id 字段建立索引。多值索引可以對(duì) JSON 字段建立索引，例如 related_product_Ids 字段在數(shù)據(jù)庫(kù)中是 JSON 類型，值為 [110, 111, 112]，當(dāng)對(duì)該字段建立多值索引時(shí)，可以建立三條索引記錄，110，111，112 ，因此單獨(dú)查詢?nèi)我簧唐稩d，均可以索引到 記錄 A。

接下來查看下如何使用多值索引

如何使用多值索引

1）創(chuàng)建JSON 類型的字段

alter table coupon_template add column related_prodcut_ids JSON default NULL;

2）創(chuàng)建一條 JSON 記錄

insert into  coupon_template(related_product_ids) values ('[110,111,112]');

3）對(duì) JSON 類型創(chuàng)建多值索引

alter table coupon_template add INDEX `relatedProductIdsIndex`((cast(json_extract(`related_product_ids`, '$[*]') as unsigned array)));

創(chuàng)建多值索引部分，需要指定表達(dá)式。范式是 ((cast(json_extract(columnName, 'expression') as unsigned array)))

因?yàn)?product_ids是 Json 數(shù)組，所以表達(dá)式部分 是 $[*]。 如果是一個(gè) Json Object，例如如下的 JSON

{
  "name": "xxxx",
  "ids": [110,111,112]
}

表達(dá)式應(yīng)該為 $.ids

例如下面的代碼示例，創(chuàng)建了 ids_ext JSON 屬性，對(duì) json 中的 ids屬性建立二級(jí)索引。注意 表達(dá)式變成了，ids_ext-> '$.ids'，這是創(chuàng)建二級(jí)索引的另一種簡(jiǎn)潔寫法，無需嵌套 json_extract 方法了。

alter table coupon_template add INDEX ids_ext_index ((CAST(ids_ext-> '$.ids' AS UNSIGNED ARRAY)));

如何查詢多值索引

使用 MEMBER OF (表達(dá)式) 可查詢多值索引，例如以下示例

圖片

select * from coupon_template WHERE 110 MEMBER OF (ids_ext-> '$.ids');

使用 explain 查看執(zhí)行計(jì)劃，如下截圖所示，確實(shí)可以使用到 ids_ext_index 索引。

圖片

最后

MySQL 8.0 在 2018 年發(fā)布，目前已經(jīng)成熟。該版本引入了一系列新特性如 JSON類型、窗口函數(shù)、多值索引和OnlineDDL Instant瞬時(shí)改表等。

此外官方已經(jīng)在 2023 年底停止 MySQL 5.7版本，所以還是要盡快遷移到 8.0 版本。