Android資料庫一些原始碼分析

希爾瓦娜斯女神發表於2015-05-21
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）。

 

 

可以看出來後者耗時幾乎只有前者的 一半（所以以後大家在做大批量資料插入的時候可以考慮後者的實現方式）。我們來看看原始碼為啥會這樣。

首先看前者的實現方法原始碼

 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. 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     }

 

一目瞭然，其實就是rawquery返回的是一個沒有意義的cursor物件裡面什麼都沒有，當你呼叫movetonext之類的方法的時候，

會判斷是否裡面沒有資料 如果有資料就返回你要的資料，如果沒有的話，實際上最終呼叫的就是SQLiteQuery這個類的fillwindow方法

來最終執行你寫的sql語句~~耗時也就是在這裡耗時！！！！！切記！不是在rawquery裡耗時的！

 

相關文章