查詢背景
有一個表tmp_test_course大概有10萬條記錄，然后有個json字段叫outline，存了一對多關(guān)系(保存了多個編碼，例如jy1577683381775)
我們需要在這10萬條數(shù)據(jù)中檢索特定類型的數(shù)據(jù)，目標(biāo)總數(shù)據(jù)量：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字段，給出相應(yīng)的查詢時間和掃描行數(shù)
一、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分析結(jié)果如下，全表掃描
二、json函數(shù)查詢
耗時196毫秒，速度稍微快了一點(diǎn)
|
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分析結(jié)果如下，還是全表掃描
三、聯(lián)合索引查詢
下面為該表建立一個聯(lián)合索引(本來想建一個type-del-is_leaf-outline的索引，但是outline字段太長限制，所以只加type-del-is_leaf的聯(lián)合索引
|
1
|
alter table tmp_test_course add key `type-del-is_leaf` (`type`,`del`,`is_leaf`) |
加入索引后再執(zhí)行l(wèi)ike和json查詢，明顯提速。
like執(zhí)行用了136毫秒，json查詢用了82.6毫秒，由此可見針對json類型使用json函數(shù)查詢比like快
explain分析結(jié)果如下，兩者查詢掃描的行數(shù)都限定在了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); |
現(xiàn)在再來用全文索引進(jìn)行檢索
|
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分析結(jié)果如下，顯示只掃描了一行
結(jié)論
以下是4種情況的執(zhí)行結(jié)果
全文索引: 11.6ms
聯(lián)合索引：82.6ms(json)、136ms(like)
json函數(shù)查詢：196ms
like查詢: 248ms
結(jié)論：全文索引 > 聯(lián)合索引 > json函數(shù)查詢 > like查詢
數(shù)據(jù)量越大，全文索引速度越明顯，就10萬的量，查詢速度大概比直接查詢快了20倍左右，如果是百萬或千萬級別的表，提升差距會更加大，所以有條件還是老老實實用全文索引吧
到此這篇關(guān)于mysql全文索引、聯(lián)合索引、like查詢、json查詢速度哪個快的文章就介紹到這了,更多相關(guān)mysql 全文索引 聯(lián)合索引 like查詢 json查詢內(nèi)容請搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！
原文鏈接：https://www.cnblogs.com/chenqionghe/p/12367268.html
