转载注明出处: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