表结构如下
符合索引如下
查询语句
SELECT
phone,lan_id,region_id
FROM
test_user2
WHERE
phone = 15799795683
AND lan_id = 5
AND region_id = 28
explain结果
就结果来看, 虽然用到了 id_ph&lan®
索引,但依然扫了400多万行的数据, 很明显索引失效了.
原因:
phone是varchar类型,但是查询语句中phone的值确实数字类型.
将查询语句改为:
SELECT
phone,lan_id,region_id
FROM
test_user2
WHERE
phone = '15799795683'
AND lan_id = 289
AND region_id = 28