轉載注明出處:https://blog.csdn.net/skysukai
在Android系統中,聯系人資料庫是一個比較大的資料庫。一次在浏覽contact2.db的時候發現,phone_lookup表裡的資料特别簡單:
data_id | raw_contact_id | normalized_number | min_match |
---|---|---|---|
data表_id的外鍵 | raw_contact表_id的外鍵 | 标準化的電話号碼 | 标準化電話号碼的倒序 |
看完了這個表我的心裡一大堆疑問,phone_lookup表如果是通過電話号碼查詢姓名,怎麼隻有号碼沒有姓名?而且如果光是為了查詢姓名直接在data表裡不就可以了嗎,這張表存在的意義又是什麼?
帶着這些問題讀了ContactProvider,期望從代碼中尋找答案。應用上層通過以下代碼來查詢姓名:
Uri uri = Uri.withAppendedPath(PhoneLookup.CONTENT_FILTER_URI, Uri.encode(phoneNumber));
resolver.query(uri, new String[]{PhoneLookup.DISPLAY_NAME,...
那直接從ContactProvider的query()方法開始,找到query
PHONE_LOOKUP
的地方,簡略版代碼:
……
String number = uri.getPathSegments().size() > 1 ? uri.getLastPathSegment() : "";
String numberE164 = PhoneNumberUtils.formatNumberToE164(number, mDbHelper.get().getCurrentCountryIso());
String normalizedNumber = PhoneNumberUtils.normalizeNumber(number);
//設定要查詢的表
mDbHelper.get().buildPhoneLookupAndContactQuery(qb, normalizedNumber, numberE164);
//設定project
qb.setProjectionMap(sPhoneLookupProjectionMap);
……
final Cursor fallbackCursor = doQuery(db, qb, projectionWithNumber,
selection, selectionArgs, sortOrder, groupBy, having, limit,
cancellationSignal);
……
跟蹤到
doQuery()
方法裡邊去,得到最終的SQL查詢語句:
SELECT data1 AS number,
contacts_view._id AS contact_id,
contacts_view.photo_uri AS photo_uri,
contacts_view.send_to_voicemail AS send_to_voicemail,
data_id AS data_id,
contacts_view.lookup AS lookup,
contacts_view.display_name AS display_name,
contacts_view.last_time_contacted AS last_time_contacted,
contacts_view.has_phone_number AS has_phone_number,
contacts_view.in_visible_group AS in_visible_group,
contacts_view.photo_file_id AS photo_file_id,
data3 AS label,
contacts_view.starred AS starred,
data4 AS normalized_number,
contacts_view.photo_thumb_uri AS photo_thumb_uri,
contacts_view.in_default_directory AS in_default_directory,
contacts_view.photo_id AS photo_id,
contacts_view.custom_ringtone AS custom_ringtone,
contacts_view._id AS _id,
data2 AS type,
contacts_view.times_contacted AS times_contacted
FROM raw_contacts
JOIN
view_contacts contacts_view ON (contacts_view._id = raw_contacts.contact_id),
(
SELECT data_id,
normalized_number,
length(normalized_number) AS len
FROM phone_lookup
WHERE (phone_lookup.min_match = '.......')
)
AS lookup,
data
WHERE (lookup.data_id = data._id AND
data.raw_contact_id = raw_contacts._id AND
(lookup.normalized_number = '+86........' OR
lookup.len <= 11 AND
substr('.........', 11 - lookup.len + 1) = lookup.normalized_number OR
(lookup.len > 11 AND
substr(lookup.normalized_number, lookup.len + 1 - 11) = '........') ) )
ORDER BY length(lookup.normalized_number) DESC
可以看到,最終
phone_lookup
的資料是由
raw_contact
、
view_contacts
、
phone_lookup
、
data
這四張表組合查詢得到,最終傳回的資料中包含了
number
、
display_name
、
starred
、
photo_thumb_uri
等一系列會在來電界面上顯示的關鍵字段。
回到最開始的問題,
phone_lookup
表存在的意義是什麼,上面那些資料不需要
phone_lookup
表也可以直接查詢得到啊。
上面那段查詢語句稍顯複雜,我們分解來看。首先是phone_lookup表的分解:
SELECT data_id,
normalized_number,
min_match,
len
FROM (
SELECT data_id,
normalized_number,
min_match,
length(normalized_number) AS len
FROM phone_lookup
)
AS lookup
WHERE (lookup.normalized_number = '+86........' OR
lookup.len <= 11 AND
substr('........', 11 - lookup.len + 1) = lookup.normalized_number OR
(lookup.len > 11 AND
substr(lookup.normalized_number, lookup.len + 1 - 11) = '........') );
得到資料庫表結構:
data_id | normalized_number | min_match | len |
---|---|---|---|
标準化電話号後的長度 |
大意就是從
phone_lookup
表中篩選出來電的那個電話号碼,至于為什麼寫這麼複雜,隻能猜測各個國家的電話号碼可能都不一樣,這裡做了統一處理。
繼續回到第一個query語句,WHERE之後的篩選條件:
WHERE (lookup.data_id = data._id AND
data.raw_contact_id = raw_contacts._id
……
檢視
data
表及
phone_lookup
表的index:
名稱 | 字段 |
---|---|
data_raw_contact_id | raw_contact_id |
data_mimetype_data1_index | mimetype_id, data1 |
data_hash_id_index | hash_id |
名稱 | 字段 |
---|---|
phone_lookup_index | normalized_number, raw_contact_id, data_id |
phone_lookup_min_match_index | min_match, raw_contact_id, data_id |
phone_lookup_data_id_min_match_index | data_id, min_match |
可以看到,在第一個篩選條件
lookup.data_id = data._id
和第二個篩選條件
data.raw_contact_id = raw_contacts._id
都用到了索引來提高查詢效率。在
phone_lookup
這張表上建了三個複合索引,基本覆寫了這張表的字段,有關索引是怎麼提高查詢效率的,可以參考這篇文章(傳送門)。
可以肯定一點,
phone_lookup
這張表是通過設定索引來提高查詢效率,它存在的意義就是為了在來電時快速顯示聯系人姓名。在聯系人數目較少時,查詢性能可能不是特别明顯,當聯系人上幾十萬時,這種優化才能顯現出來。是以,谷歌在PhoneLookup的開發者文檔簡介中說的
“A table that represents the result of looking up a phone number, for example for caller ID. To perform a lookup you must append the number you want to find to CONTENT_FILTER_URI.
This query is highly optimized.
”這裡的“高度優化”其實是指資料庫層面的優化,而不是代碼層面的優化了。
順便說一下,
phone_lookup
的字段
min_match
号碼倒序也是為了提高查詢速度。試想一下,如果存了幾十萬個聯系人,号碼都是以13\15\17這種數字開頭,不如将号碼倒序并對這個字段設立索引,不失為一種提高查詢速度的手段。
參考:https://developer.android.com/reference/android/provider/ContactsContract.PhoneLookup
參考:https://www.cnblogs.com/hyd1213126/p/5828937.html
參考:https://www.cnblogs.com/wuchanming/p/6886020.html
參考:https://www.cnblogs.com/aspwebchh/p/6652855.html