字首索引
建立字首索引
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欄位的索引不支援範圍查詢,只支援等值查詢。