前缀索引的使用和注意事项
- 前缀索引
当字段里有很长字符串的列(TEXT,长的VARCHER等…),在前几个字符串里加索引,这就是前缀索引。
前缀索引语法:
alter table table_name add key(column_name(length));
优点:索引很长的字符列,这会让索引变得大且慢前缀索引提高索引效率。
缺点:降低索引的选择性。
选择性:不重复的索引值(基数,cardinality)与表的记录总数(count(*))的比值。一个索引的选择性越接近于1,这个索引的效率就越高。唯一索引(主键索引)的选择性是1,这是最好的索引选择性,性能也是最好的。
eg:如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是 1980/2000=0.99。
user表 字段id 字段name 给name字段 添加前缀索引 如何判断加具体几个字符最合适?
1.求user表字段name的选择性比值 即用 name索引列有多少个不同的值 count(distinct name)
除以user表的记录总数(count(*))
select count(distinct name)/count(*) from user
比如 得出的值是0.533
- 再在name字段加上若干个具体的前几个字符的值,得出的值谁接近0.533就用第几个值作为索引值。
select count(distinct left(name,3))/count(*) as car3,
count(distinct left(name,4))/count(*) as car4,
count(distinct left(name,5))/count(*) as car5,
count(distinct left(name,6))/count(*) as car6,
from user;
即 car3,car4,car5,car6 谁的值接近0.533 就用那个参数值。
这里的参数值length指的是3,4,5,6不是算出的值别弄混淆了…
注意:1.不能在order by 和group by 里使用 2.不能使用覆盖索引