国外优秀门户网站,抚州建设公司网站,知春路网站建设公司,怎么做网站标题优化查询背景有一个表tmp_test_course大概有10万条记录#xff0c;然后有个json字段叫outline#xff0c;存了一对多关系(保存了多个编码#xff0c;例如jy1577683381775)我们需要在这10万条数据中检索特定类型的数据#xff0c;目标总数据量#xff1a;条2931SELECT COUNT(*)…查询背景有一个表tmp_test_course大概有10万条记录然后有个json字段叫outline存了一对多关系(保存了多个编码例如jy1577683381775)我们需要在这10万条数据中检索特定类型的数据目标总数据量条2931SELECT COUNT(*) FROM tmp_test_course WHERE type5 AND del2 AND is_leaf1我们在限定为上面类型的同时还得包含下面任意一个编码(也就是OR查询)jy1577683381775jy1577683380808jy1577683379178jy1577683378676jy1577683377617jy1577683376672jy1577683375903jy1578385720787jy1499916986208jy1499917112460jy1499917093400jy1499917335579jy1499917334770jy1499917333339jy1499917331557jy1499917330833jy1499917329615jy1499917328496jy1576922006950jy1499916993558jy1499916992308jy1499917003454jy1499917002952下面分别列出4种方式查询outline字段给出相应的查询时间和扫描行数一、like查询耗时248毫秒SELECT * FROM tmp_test_course WHERE type5 AND del2 AND is_leaf1 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函数查询使用函数JSON_SEARCH更多函数请查看MySQL官方文档可以看到查询耗时196毫秒速度稍微快了一点SELECT * FROM tmp_test_course WHERE type5 AND del2 AND is_leaf1AND (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的联合索引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字段定义改一下ALTER TABLE tmp_test_course MODIFY outline VARCHAR(1024) NOT NULL DEFAULT []ALTER TABLE tmp_test_course ADD FULLTEXT INDEX outline (outline);现在再来用全文索引进行检索SELECT * FROM tmp_test_course WHERE type5 AND del2 AND is_leaf1AND MATCH(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种情况的执行结果结论全文索引 联合索引 json函数查询 like查询数据量越大全文索引速度越明显就10万的量查询速度大概比直接查询快了20倍左右如果是百万或千万级别的表提升差距会更加大所以有条件还是老老实实用全文索引吧
