查詢背景
有一個表tmp_test_course大概有10萬條記錄，然后有個json字段叫outline，存了一對多關系(保存了多個編碼，例如jy1577683381775)
我們需要在這10萬條數據中檢索特定類型的數據，目標總數據量：2931條
|
1
|
select count(*) from tmp_test_course where `type`=5 and del=2 and is_leaf=1 |
我們在限定為上面類型的同時，還得包含下面任意一個編碼（也就是or查詢）
jy1577683381775
jy1577683380808
jy1577683379178
jy1577683378676
jy1577683377617
jy1577683376672
jy1577683375903
jy1578385720787
jy1499916986208
jy1499917112460
jy1499917093400
jy1499917335579
jy1499917334770
jy1499917333339
jy1499917331557
jy1499917330833
jy1499917329615
jy1499917328496
jy1576922006950
jy1499916993558
jy1499916992308
jy1499917003454
jy1499917002952
下面分別列出4種方式查詢outline字段，給出相應的查詢時間和掃描行數
一、like查詢
耗時248毫秒
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
select * from tmp_test_course where `type`=5 and del=2 and is_leaf=1 and (outline like '%jy1577683381775%'or outline like '%jy1577683380808%'or outline like '%jy1577683379178%'or outline like '%jy1577683378676%'or outline like '%jy1577683377617%'or outline like '%jy1577683376672%'or outline like '%jy1577683375903%'or outline like '%jy1578385720787%'or outline like '%jy1499916986208%'or outline like '%jy1499917112460%'or outline like '%jy1499917093400%'or outline like '%jy1499917335579%'or outline like '%jy1499917334770%'or outline like '%jy1499917333339%'or outline like '%jy1499917331557%'or outline like '%jy1499917330833%'or outline like '%jy1499917329615%'or outline like '%jy1499917328496%'or outline like '%jy1576922006950%'or outline like '%jy1499916993558%'or outline like '%jy1499916992308%'or outline like '%jy1499917003454%'or outline like '%jy1499917002952%') |
explain分析結果如下，全表掃描
二、json函數查詢
耗時196毫秒，速度稍微快了一點
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
select * from tmp_test_course where `type`=5 and del=2 and is_leaf=1and(json_search(outline, 'one', 'jy1577683381775') is not null orjson_search(outline, 'one', 'jy1577683380808') is not null orjson_search(outline, 'one', 'jy1577683379178') is not null orjson_search(outline, 'one', 'jy1577683378676') is not null orjson_search(outline, 'one', 'jy1577683377617') is not null orjson_search(outline, 'one', 'jy1577683376672') is not null orjson_search(outline, 'one', 'jy1577683375903') is not null orjson_search(outline, 'one', 'jy1578385720787') is not null orjson_search(outline, 'one', 'jy1499916986208') is not null orjson_search(outline, 'one', 'jy1499917112460') is not null orjson_search(outline, 'one', 'jy1499917093400') is not null orjson_search(outline, 'one', 'jy1499917335579') is not null orjson_search(outline, 'one', 'jy1499917334770') is not null orjson_search(outline, 'one', 'jy1499917333339') is not null orjson_search(outline, 'one', 'jy1499917331557') is not null orjson_search(outline, 'one', 'jy1499917330833') is not null orjson_search(outline, 'one', 'jy1499917329615') is not null orjson_search(outline, 'one', 'jy1499917328496') is not null orjson_search(outline, 'one', 'jy1576922006950') is not null orjson_search(outline, 'one', 'jy1499916993558') is not null orjson_search(outline, 'one', 'jy1499916992308') is not null orjson_search(outline, 'one', 'jy1499917003454') is not null orjson_search(outline, 'one', 'jy1499917002952') is not null) |
explain分析結果如下，還是全表掃描
三、聯合索引查詢
下面為該表建立一個聯合索引(本來想建一個type-del-is_leaf-outline的索引，但是outline字段太長限制，所以只加type-del-is_leaf的聯合索引
|
1
|
alter table tmp_test_course add key `type-del-is_leaf` (`type`,`del`,`is_leaf`) |
加入索引后再執行like和json查詢，明顯提速。
like執行用了136毫秒，json查詢用了82.6毫秒，由此可見針對json類型使用json函數查詢比like快
explain分析結果如下，兩者查詢掃描的行數都限定在了2931行
四、全文索引查詢
因為全文索引只支持char、varchar和text，我們需要把json字段定義改一下
|
1
|
alter table tmp_test_course modify `outline` varchar(1024) not null default '[]' |
添加全文索引
|
1
|
alter table tmp_test_course add fulltext index outline (outline); |
現在再來用全文索引進行檢索
|
1
2
3
4
|
select * from tmp_test_course where `type`=5 and del=2 and is_leaf=1andmatch(outline) against ('jy1577683381775 jy1577683380808 jy1577683379178 jy1577683378676 jy1577683377617 jy1577683376672 jy1577683375903 jy1578385720787 jy1499916986208 jy1499917112460 jy1499917093400 jy1499917335579 jy1499917334770 jy1499917333339 jy1499917331557 jy1499917330833 jy1499917329615 jy1499917328496 jy1576922006950 jy1499916993558 jy1499916992308 jy1499917003454 jy1499917002952') |
耗時11.6毫秒,速度提升極其明顯，可見全文索引的牛逼。
explain分析結果如下，顯示只掃描了一行
結論
以下是4種情況的執行結果
全文索引: 11.6ms
聯合索引：82.6ms(json)、136ms(like)
json函數查詢：196ms
like查詢: 248ms
結論：全文索引 > 聯合索引 > json函數查詢 > like查詢
數據量越大，全文索引速度越明顯，就10萬的量，查詢速度大概比直接查詢快了20倍左右，如果是百萬或千萬級別的表，提升差距會更加大，所以有條件還是老老實實用全文索引吧
到此這篇關于mysql全文索引、聯合索引、like查詢、json查詢速度哪個快的文章就介紹到這了,更多相關mysql 全文索引 聯合索引 like查詢 json查詢內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://www.cnblogs.com/chenqionghe/p/12367268.html
