sqlite3-索引优化

vb
705 阅读4分钟

背景

熟悉数据库的同学应该了解 EXPLAIN 这个命令，用于检查sql语句索引是否合理。但我在尝试对sqlite进行索引优化时，发现sqlite3和mysql有很大的区别。
本文主要是尝试整理下mysql/sqlite3索引优化的过程和区别。

索引优化流程

一般来说，使用mysql的索引优化步骤如下：
1、DBA通过监控mysql慢日志反馈给开发人员;
2、开发优化业务逻辑/sql优化等(本文忽略);
3、开发检查索引是否合理;
4、根据3检查后可能进行索引优化;
我尝试根据这个步骤进行sqlite3的索引优化，但遇到一些问题。

慢查询日志(slow query log)

一、mysql, 提供了慢日志选项。开启步骤如下:

// my.conf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/vb.log
long_query_time = 1

二、slqite, 本身没有提供慢日志的功能，而我使用的typeORM框架也没有提供(跟进issue)。

所以在使用sqlite3时，我们无法通过慢日志来反馈哪些sql语句可能会有问题。

EXPLAIN

由于因为没有慢日志，我尝试在开发环境改造一下，做一个全量的 EXPLAIN 语句覆盖，用于检查是否有不合理的sql索引。
在开始之前，先讲一下我遇到的一个坑, 如果直接使用 EXPLAIN 语句会得到跟mysql不一样的结果， e.g. image.png 刚看到的时候一脸懵，怎么分析？关于这些字段解释的文章基本没有，只有官网上有解释，但太繁琐(The SQLite Bytecode Engine)，但同时官网有推荐另外一个语句 EXPLAIN QUERY PLAN(下面有详细的分析过程)。

其实实现代码很简单，但需要对目前代码做一个简单的改造(sql执行时，将query记录下来，转化为原生sql，加上 EXPLAIN QUERY PLAN 语句):

// 实现
async function wrapExplain(queryBulder, connection, sql) {
    if(global.ENV === 'prod') {
        return
    }
    let result

    if(sql) {
        result = await connection.query(`EXPLAIN QUERY PLAN ${sql}`)
    }else {
        const [query, queryParameters] = await queryBulder.getQueryAndParameters()

        result = await connection.query(`EXPLAIN QUERY PLAN ${query}`, queryParameters)
    }
    logServices.debugLog(`explain result: ${JSON.stringify(result)}`)
}

// 调用时
const query = getConnection()
    .getRepository('message')
    .createQueryBuilder()
    .where({
      ...condition
    })
    .orderBy('createTimestamp', 'DESC')

wrapExplain(query, connectionManage.getConnection())

return query.getMany()

改造完所有的sql调用后，我们就可以拿到结果进行分析， e.g. image.png

备注:
1、其实有类似的typeORM插件实现了改功能，typeorm-explain,该插件目前不支持sqlite3，需要添加下。
2、我其实有尝试通过typeORM提供的 subscriber 处理(这样可以不需要改造现有逻辑)，但尝试后发现 subscriber 并没有提供查询原生sql的返回值。

索引优化

我们进行索引优化的前提是没有索引或者索引不正确，导致全表扫描或者扫描的行太多。所以我们需要结合 EXPLAIN 结果来进行优化。先看一下如何判断索引是否应该优化:
一、mysql,使用 EXPLAIN 结果参数如下:
image.png 这是一个简单的 user 表，下面列一下主要需要分析一下我常用的几个字段:

type: system->const->eq_ref->ref->range->index->ALL(性能从好到差排序);  
key: 用到哪个索引的字段;  
Extra: 这个字段用的比较多，列一下常遇到的几种情况说一下(不能单纯只看Extra字段，需要结合type字段来看，如果是range及之前的的消耗一般不用优化);  
1、NULL; 用到索引，但索引没有覆盖所有字段,需要回表查询； 
2、Using index; 查询的列被索引覆盖;   
3、Using where; 查询的列未被索引覆盖;  
4、Using where; Using index; 查询的列没有被索引覆盖，需要回表查询；

更详细的字段解析参考EXPLAIN Output Format

二、sqlite3,使用 EXPLAIN QUERY PLAN 结果参数如下:
image.png 这里需要分析的字段是 detail , 因为主要是讲sqlite3，我按照截图的几个例子进行分析下:
1、第一个日志空数组是插入语句，可以忽略;
2、第二、三、五处日志 SEARCH 关键字表示使用了索引，但没有做到索引覆盖(如果做到索引覆盖会出现 USING COVERING INDEX)，但这个请求涉及的字段比较多，无法做到索引覆盖,暂时不进行优化;
3、第四、六处日志出现了两个结果，是因为获取到数据后的临时表进行排序(如果是联表也会出现多条结果)。但这里第一个查询结果关键字是 SCAN 表示全表扫描，没有用到索引，所以需要将搜索关键字加上索引。加上后变成:

explain result: [{"id":4,"parent":0,"notused":0,"detail":"SEARCH message_t2 USING INDEX conversationId (conversationId=?)"},{"id":45,"parent":0,"notused":0,"detail":"USE TEMP B-TREE FOR ORDER BY"}]

但这里还有一个结果是: USE TEMP B-TREE FOR ORDER BY,这个表示使用了临时表进行排序，这个官方是建议给排序字段加上索引的，加上索引可以避免创建临时表:

explain result: [{"id":4,"parent":0,"notused":0,"detail":"SCAN TABLE message_t2 USING INDEX createTimestamp"}]

4、最后再说一个索引覆盖的例子, e.g.

explain result: [{"id":6,"parent":0,"notused":0,"detail":"SEARCH TABLE user USING COVERING INDEX name (name=?)"}]

关键字是 USING COVERING INDEX，表示可以做到索引覆盖。
更详细的字段解析请参考EXPLAIN QUERY PLAN

参考文档

1、Tracking query time for slow queries and statsd timers: github.com/typeorm/typ…
2、The SQLite Bytecode Engine: www.sqlite.org/opcode.html
3、XPLAIN Output Format： dev.mysql.com/doc/refman/…
4、EXPLAIN QUERY PLAN： man.hubwiz.com/docset/SQLi…

avatar
文章
阅读
粉丝