目录
前缀索引
并不是在所有的查询条件中出现的列都需要添加索引,对于什么时候添加B+树索引,一般情况下,在访问表中很少的一部分数据时使用B+树索引才有意义。例如对于性别字段、地区字段、类型字段,它们可取值的范围很小,为低选择性。如果某个字段的取值范围比较广,几乎没有重复,即属于高选择性,则使用索引比较合适。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。索引的选择性越高则查询效率越高,因为选择性高的索引可以过滤更多的行。
索引的选择性是指:不重复的索引值(也称为基数,cardinality)和数据表的记录总数(#T)的比值,范围从[1 / #T, 1]。所以,选择性就可以理解为目录的多少,目录越多就越方便定位,这就是高选择性,所以如果索引是高选择性的话,那就可以非常快查询到数据了。反之,则是低选择性。
有时候需要索引很长的字符字段,如 BLOB、TEXT 或者很长的 VARCHAR 类型的字段,这会让索引变得很大,就要考虑占用空间和效率问题,导致查询很慢。对于这种情况,我们可以使用前缀索引来索引数据最开始的部分字符,这样可以大大的节约索引空间,从而提高索引效率,但这样也会降低索引的选择性,因为部分字符作为索引的话,会出现多条数据都有同样的前缀,选择性就低了。
先定义一张表,基于这张表做的演示,那么就定义一张最常见的用户表吧。
CREATE TABLE `user` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
`pwd` VARCHAR(255) COLLATE UTF8MB4_UNICODE_CI NOT NULL,
`created_at` INT(8) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_created_at` (`created_at`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE = UTF8MB4_UNICODE_CI;
插入几条数据,方便之后快乐的玩耍
INSERT INTO `user` (`id`, `name`, `pwd`, `created_at`) VALUES ('1', '牛A',