sqlite 查询速度比较

最新推荐文章于 2025-11-06 19:20:44 发布

原创最新推荐文章于 2025-11-06 19:20:44 发布 · 7.7k 阅读

2 ·

CC 4.0 BY-SA版权

android.java 专栏收录该内容

23 篇文章

订阅专栏

本文通过在macOS上使用SQLProforSQLiteReadOnly版，对多种SQL查询语句进行了性能对比，包括不同条件筛选、排序及限制结果数量等场景，展示了在不同查询条件下的执行时间，为优化数据库查询效率提供了参考。

以下是在 macOS 上使用 SQLPro for SQLite Read Only 版进行的查询速度对比。

select word from words_table where word >= '小' and word < '少' order by wordfrq desc 0.4s

select word,wordfrq from words_table where word >= '小' and word < '少' 未按 wordfrq 排序，0.5s

select word from words_table where word like '小%' 未按 wordfrq 排序，0.48s

select word from words_table where word like '小%' order by wordfrq desc 0.67s

select word,wordfrq from words_table where word like '小%' 0.67s

select word,wordfrq from words_table where word like '小%' limit 100 0.52s

select word,wordfrq from words_table where word like '小%' limit 20 有按 wordfrq 排序 0.52s

select word from words_table where word >= '小' and word < '少' order by wordfrq desc limit 100 0.3s

select word from words_table where word >= '小' and word < '少' order by wordfrq desc limit 20 0.2s

select word,wordfrq from words_table where word glob '小*' limit 20 未按 wordfrq 排序 0.1s

select word from words_table where word glob '小*' order by wordfrq desc limit 20 0.2s

select word from words_table where stroke like '31%y1z32%' 0.86s

select word from words_table where stroke like '31%y1z32%' order by wordfrq desc 0.85s

select word from words_table where stroke like '31%y1z32%' order by wordfrq desc limit 100 0.84s

select word from words_table where stroke like '31%y1z32%' and wordlength == '2' order by wordfrq desc limit 20 0.84s

select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') 0.41s

select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') order by wordfrq desc 0.408s

select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') order by wordfrq desc limit 100 0.405s

select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') order by wordfrq desc limit 20 0.405s

select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%') and wordlength == '2' order by wordfrq desc limit 20 0.405s

select word from words_table where stroke like '%y1z32%' and stroke in (select stroke from words_table where stroke like '31%' and wordlength == '2') order by wordfrq desc limit 20 0.38s

select word from words_table where stroke like '31%y1z32%' and stroke in (select stroke from words_table where stroke like '31%' and wordlength == '2') order by wordfrq desc limit 20 0.174s

select word from words_table where stroke >= '31%y1z32' and stroke in (select stroke from words_table where stroke >= '31' and wordlength == '2') order by wordfrq desc limit 20 0.485s

select word from words_table where stroke glob '31*y1z32*' and stroke in (select stroke from words_table where stroke glob '31*' and wordlength == '2') order by wordfrq desc limit 20 0.156s