转载

Android数据库一些源码分析

对于批量数据插入这种最常见的情况来说，我们来看两种实现方式（两种都用了事务）。

下面这种应该是最多人使用的插入数据的方法：

 1 public long addByExec(List<Person> persons) {  2   3         long start = System.currentTimeMillis();  4         db.beginTransaction();  5   6         for (Person person : persons) {  7             db.execSQL(" INSERT INTO person(name,age,info) VALUES(?, ?, ?) ",  8                     new Object[] { person.name, person.age, person.info });  9         } 10  11         db.setTransactionSuccessful(); 12         long end = System.currentTimeMillis(); 13         db.endTransaction(); 14         return end - start; 15  16     }

再看一种比较少用的插入方法

 1 public long addByStatement(List<Person> persons) {  2         long start = System.currentTimeMillis();  3         db.beginTransaction();  4         SQLiteStatement sqLiteStatement = db.compileStatement(sql);  5   6         for (Person person : persons) {  7             sqLiteStatement.bindString(1, person.name);  8             sqLiteStatement.bindString(2, person.age);  9             sqLiteStatement.bindString(3, person.info); 10             sqLiteStatement.executeInsert(); 11         } 12         db.setTransactionSuccessful(); 13         long end = System.currentTimeMillis(); 14         db.endTransaction(); 15         return end - start; 16     }

然后我们分别用这两个方法来向数据库里面插入一万条数据看看耗时多少。为了演示效果更加突出一点，我录制了一个GIF，同时，

这2个方法我也没有用子线程来操作他，直接在ui线程上操作所以看起来效果会比较突出一些（但是自己写代码的时候千万别这么写小心ANR）。

Android数据库一些源码分析

可以看出来后者耗时几乎只有前者的一半（所以以后大家在做大批量数据插入的时候可以考虑后者的实现方式）。我们来看看源代码为啥会这样。

首先看前者的实现方法源码

 1 public void execSQL(String sql, Object[] bindArgs) throws SQLException {  2         if (bindArgs == null) {  3             throw new IllegalArgumentException("Empty bindArgs");  4         }  5         executeSql(sql, bindArgs);  6     }  7   8     private int executeSql(String sql, Object[] bindArgs) throws SQLException {  9         if (DatabaseUtils.getSqlStatementType(sql) == DatabaseUtils.STATEMENT_ATTACH) { 10             disableWriteAheadLogging(); 11             mHasAttachedDbs = true; 12         } 13         SQLiteStatement statement = new SQLiteStatement(this, sql, bindArgs); 14         try { 15             return statement.executeUpdateDelete(); 16         } catch (SQLiteDatabaseCorruptException e) { 17             onCorruption(); 18             throw e; 19         } finally { 20             statement.close(); 21         } 22     }

我们发现前者的实现实际上最后也是通过SQLiteStatement 这个类是操作的。

而后者不过是

1  public SQLiteStatement compileStatement(String sql) throws SQLException { 2         verifyDbIsOpen(); 3         return new SQLiteStatement(this, sql, null); 4     }

所以实际上前者之所以比后者耗时应该是下面这段代码的原因：

1 if (DatabaseUtils.getSqlStatementType(sql) == DatabaseUtils.STATEMENT_ATTACH) { 2             disableWriteAheadLogging(); 3             mHasAttachedDbs = true; 4         }

 1  public static int getSqlStatementType(String sql) {  2         sql = sql.trim();  3         if (sql.length() < 3) {  4             return STATEMENT_OTHER;  5         }  6         String prefixSql = sql.substring(0, 3).toUpperCase();  7         if (prefixSql.equals("SEL")) {  8             return STATEMENT_SELECT;  9         } else if (prefixSql.equals("INS") || 10                 prefixSql.equals("UPD") || 11                 prefixSql.equals("REP") || 12                 prefixSql.equals("DEL")) { 13             return STATEMENT_UPDATE; 14         } else if (prefixSql.equals("ATT")) { 15             return STATEMENT_ATTACH; 16         } else if (prefixSql.equals("COM")) { 17             return STATEMENT_COMMIT; 18         } else if (prefixSql.equals("END")) { 19             return STATEMENT_COMMIT; 20         } else if (prefixSql.equals("ROL")) { 21             return STATEMENT_ABORT; 22         } else if (prefixSql.equals("BEG")) { 23             return STATEMENT_BEGIN; 24         } else if (prefixSql.equals("PRA")) { 25             return STATEMENT_PRAGMA; 26         } else if (prefixSql.equals("CRE") || prefixSql.equals("DRO") || 27                 prefixSql.equals("ALT")) { 28             return STATEMENT_DDL; 29         } else if (prefixSql.equals("ANA") || prefixSql.equals("DET")) { 30             return STATEMENT_UNPREPARED; 31         } 32         return STATEMENT_OTHER; 33     }

实际上就是多了一个字符串处理的函数。这就是为什么前者耗时要比后者多。因为实际上直接调用executeSql的时候

他里面是先做字符串处理然后再调用SQLiteStatement来执行，这个过程当然是比我们直接调用SQLiteStatement

来执行速度慢的。

我们首先来看一下下面这个函数

 1 public Cursor queryTest1() {  2         long start1 = System.currentTimeMillis();  3         Cursor c = db.rawQuery("select * from t1,t3 where t1.num>t3.num", null);  4         long end1 = System.currentTimeMillis();  5         Log.v("DBManager", "time1 need " + (end1 - start1));  6         long start2 = System.currentTimeMillis();  7         c.moveToNext();  8         long end2 = System.currentTimeMillis();  9         Log.v("DBManager", "time2 need" + (end2 - start2)); 10         long start3 = System.currentTimeMillis(); 11         c.moveToNext(); 12         long end3 = System.currentTimeMillis(); 13         Log.v("DBManager", "time3 need" + (end3 - start3)); 14         return c; 15     }

一个很常见的，多表查询的函数，有些人可能会奇怪为啥在这个地方我要加那么多日志。实际上如果你t1和t3的数据都很多的话，这个查询是可以预料到的会非常耗时。

很多人都会以为这个耗时是在下面这条语句做的：

1         Cursor c = db.rawQuery("select * from t1,t3 where t1.num>t3.num", null);

但是实际上这个查询耗时是在你第一调用

1         c.moveToNext();

来做的，有兴趣的同学可以自己试一下，我们这里就不帮大家来演示这个效果了，但是可以帮助大家分析一下源代码为什么会是这样奇怪的结果？

我们首先来分析一下rawQuery 这个函数

 1 public Cursor rawQuery(String sql, String[] selectionArgs) {  2         return rawQueryWithFactory(null, sql, selectionArgs, null);  3     }  4   5     /**  6      * Runs the provided SQL and returns a cursor over the result set.  7      *  8      * @param cursorFactory the cursor factory to use, or null for the default factory  9      * @param sql the SQL query. The SQL string must not be ; terminated 10      * @param selectionArgs You may include ?s in where clause in the query, 11      *     which will be replaced by the values from selectionArgs. The 12      *     values will be bound as Strings. 13      * @param editTable the name of the first table, which is editable 14      * @return A {@link Cursor} object, which is positioned before the first entry. Note that 15      * {@link Cursor}s are not synchronized, see the documentation for more details. 16      */ 17     public Cursor rawQueryWithFactory( 18             CursorFactory cursorFactory, String sql, String[] selectionArgs, 19             String editTable) { 20         verifyDbIsOpen(); 21         BlockGuard.getThreadPolicy().onReadFromDisk(); 22  23         SQLiteDatabase db = getDbConnection(sql); 24         SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(db, sql, editTable); 25  26         Cursor cursor = null; 27         try { 28             cursor = driver.query( 29                     cursorFactory != null ? cursorFactory : mFactory, 30                     selectionArgs); 31         } finally { 32             releaseDbConnection(db); 33         } 34         return cursor; 35     }

看一下24行，发现是构造了一个driver对象然后调用这个driver对象的query方法

我们继续跟踪源代码

 1  public SQLiteDirectCursorDriver(SQLiteDatabase db, String sql, String editTable,  2             CancellationSignal cancellationSignal) {  3         mDatabase = db;  4         mEditTable = editTable;  5         mSql = sql;  6         mCancellationSignal = cancellationSignal;  7     }  8   9     public Cursor query(CursorFactory factory, String[] selectionArgs) { 10         final SQLiteQuery query = new SQLiteQuery(mDatabase, mSql, mCancellationSignal); 11         final Cursor cursor; 12         try { 13             query.bindAllArgsAsStrings(selectionArgs); 14  15             if (factory == null) { 16                 cursor = new SQLiteCursor(this, mEditTable, query); 17             } else { 18                 cursor = factory.newCursor(mDatabase, this, mEditTable, query); 19             } 20         } catch (RuntimeException ex) { 21             query.close(); 22             throw ex; 23         } 24  25         mQuery = query; 26         return cursor; 27     }

发现这个返回的cursor实际上就是直接new出来的一个对象

 1  public SQLiteCursor(SQLiteCursorDriver driver, String editTable, SQLiteQuery query) {  2         if (query == null) {  3             throw new IllegalArgumentException("query object cannot be null");  4         }  5         if (query.mDatabase == null) {  6             throw new IllegalArgumentException("query.mDatabase cannot be null");  7         }  8         mStackTrace = new DatabaseObjectNotClosedException().fillInStackTrace();  9         mDriver = driver; 10         mEditTable = editTable; 11         mColumnNameMap = null; 12         mQuery = query; 13  14         query.mDatabase.lock(query.mSql); 15         try { 16             // Setup the list of columns 17             int columnCount = mQuery.columnCountLocked(); 18             mColumns = new String[columnCount]; 19  20             // Read in all column names 21             for (int i = 0; i < columnCount; i++) { 22                 String columnName = mQuery.columnNameLocked(i); 23                 mColumns[i] = columnName; 24                 if (false) { 25                     Log.v("DatabaseWindow", "mColumns[" + i + "] is " 26                             + mColumns[i]); 27                 } 28      29                 // Make note of the row ID column index for quick access to it 30                 if ("_id".equals(columnName)) { 31                     mRowIdColumnIndex = i; 32                 } 33             } 34         } finally { 35             query.mDatabase.unlock(); 36         } 37     }

所以看到这里我们就能确定的是rawquery这个方法返回的cursor实际上就是一个对象，并没有任何真正调用sql的地方。

然后我们来看看我们怀疑的moveToNext这个方法因为从日志上看耗时的地方在第一次调用他的时候，所以我们怀疑真正调用查询sql的地方

在这个函数里面被触发。

1   public final boolean moveToNext() { 2         return moveToPosition(mPos + 1); 3     }

 1 public final boolean moveToPosition(int position) {  2         // Make sure position isn't past the end of the cursor  3         final int count = getCount();  4         if (position >= count) {  5             mPos = count;  6             return false;  7         }  8   9         // Make sure position isn't before the beginning of the cursor 10         if (position < 0) { 11             mPos = -1; 12             return false; 13         } 14  15         // Check for no-op moves, and skip the rest of the work for them 16         if (position == mPos) { 17             return true; 18         } 19  20         boolean result = onMove(mPos, position); 21         if (result == false) { 22             mPos = -1; 23         } else { 24             mPos = position; 25             if (mRowIdColumnIndex != -1) { 26                 mCurrentRowID = Long.valueOf(getLong(mRowIdColumnIndex)); 27             } 28         } 29  30         return result; 31     }

看一下那个getcount方法

 1 @Override  2     public int getCount() {  3         if (mCount == NO_COUNT) {  4             fillWindow(0);  5         }  6         return mCount;  7     }  8   9     private void fillWindow(int startPos) { 10         clearOrCreateLocalWindow(getDatabase().getPath()); 11         mWindow.setStartPosition(startPos); 12         int count = getQuery().fillWindow(mWindow); 13         if (startPos == 0) { // fillWindow returns count(*) only for startPos = 0 14             if (Log.isLoggable(TAG, Log.DEBUG)) { 15                 Log.d(TAG, "received count(*) from native_fill_window: " + count); 16             } 17             mCount = count; 18         } else if (mCount <= 0) { 19             throw new IllegalStateException("Row count should never be zero or negative " 20                     + "when the start position is non-zero"); 21         } 22     }

发现如果满足某个条件的话就调用fillwindow这个方法，我们来看看是什么条件

1 /** The number of rows in the cursor */ 2     private volatile int mCount = NO_COUNT; 3     static final int NO_COUNT = -1;

看到这就明白了，如果你默认的mCount为-1的话就代表你这个cursor里面还没有查过吗，所以必须要调用fillwindow方法

1  private synchronized SQLiteQuery getQuery() { 2         return mQuery; 3     }

我们来看看这个query是什么

1  /** The query object for the cursor */ 2     private SQLiteQuery mQuery;

看看他的fillwindow方法

 1     /**  2      * Reads rows into a buffer. This method acquires the database lock.  3      *  4      * @param window The window to fill into  5      * @return number of total rows in the query  6      */  7     /* package */ int fillWindow(CursorWindow window) {  8         mDatabase.lock(mSql);  9         long timeStart = SystemClock.uptimeMillis(); 10         try { 11             acquireReference(); 12             try { 13                 window.acquireReference(); 14                 int startPos = window.getStartPosition(); 15                 int numRows = nativeFillWindow(nHandle, nStatement, window.mWindowPtr, 16                         startPos, mOffsetIndex); 17                 if (SQLiteDebug.DEBUG_LOG_SLOW_QUERIES) { 18                     long elapsed = SystemClock.uptimeMillis() - timeStart; 19                     if (SQLiteDebug.shouldLogSlowQuery(elapsed)) { 20                         Log.d(TAG, "fillWindow took " + elapsed 21                                 + " ms: window=/"" + window 22                                 + "/", startPos=" + startPos 23                                 + ", offset=" + mOffsetIndex 24                                 + ", filledRows=" + window.getNumRows() 25                                 + ", countedRows=" + numRows 26                                 + ", query=/"" + mSql + "/"" 27                                 + ", args=[" + (mBindArgs != null ? 28                                         TextUtils.join(", ", mBindArgs.values()) : "") 29                                 + "]"); 30                     } 31                 } 32                 mDatabase.logTimeStat(mSql, timeStart); 33                 return numRows; 34             } catch (IllegalStateException e){ 35                 // simply ignore it 36                 return 0; 37             } catch (SQLiteDatabaseCorruptException e) { 38                 mDatabase.onCorruption(); 39                 throw e; 40             } catch (SQLiteException e) { 41                 Log.e(TAG, "exception: " + e.getMessage() + "; query: " + mSql); 42                 throw e; 43             } finally { 44                 window.releaseReference(); 45             } 46         } finally { 47             releaseReference(); 48             mDatabase.unlock(); 49         } 50     }