前缀索引
创建前缀索引
alter table t add index idx_email(email);
alter table t add index idx_email(email(6));
使用前缀索引,定义好长度,可以做到即节省空间,又不用额外增加太多查询成本。
区分度
建立索引时,区分度(不重复的值)越高越好。
select count(distance email) from t;
select
count(distance left(email, 4)) as L4,
count(distance left(email, 5)) as L5,
count(distance left(email, 6)) as L6,
count(distance left(email, 7)) as L7,
from t;
前缀索引对覆盖索引的影响
使用前缀索引下面语句就无法使用覆盖索引
selec id, email from t where email = 'xxx';
前缀区分度不够好
倒序存储
select * from t where id_card = reverse('xxxx')
使用 hash 字段
-- 新增字段,新增索引
-- crc32()
alter table t add id_card_crc varchar(18), add index(idx_id_card_crc);
select * from t where id_card_crc = crc32('xxxx') and id_card = 'xxxx';
倒序存储和hash字段的索引不支持范围查询,只支持等值查询。
标签:count,前缀,email,索引,MySQL,id,card From: https://www.cnblogs.com/liaozibo/p/18530920