MySQL 有哪些索引类型?

可以从三个角度来描述…

1. 数据结构角度上可以分为B+Tree 索引(MySQL默认)、hash 索引、fulltext 索引(InnoDB(MySQL5.6.4+)、MyISAM都支持)

2. 存储角度上可以分为聚集索引和非聚集索引(普通索引):

聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个;
聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节点仍然是索引节点,但它有一个指向最终数据的指针;
聚集索引的数据按照索引的顺序存储,检索效率比非聚集索引高,但对数据更新影响较大。

3. 逻辑角度上可以分以下几种:

单列索引:主键索引(唯一且不能为空)、普通索引、唯一索引(唯一但允许为空)、全文索引(长文章搜索);
复合索引:又称联合索引,多个列加入到一个索引中,索引遵循最左前缀原则;
覆盖索引:如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度非常快,称为“索引覆盖”;
哈希索引:采用哈希算法把键值换算成新的哈希值,需要注意的是哈希索引只能进行等值查询,不能进行排序、模糊查找、范围查询等。检索时也不需要像B+Tree那样从根节点到叶子节点逐级查找,只需要一次哈希算法即可立刻定位到相应位置,查询速度非常快。
————————————————————————————-
索引的优点:

提高数据检索效率;
提高聚合函数效率;
提高排序效率;
使用覆盖索引可以避免回表。

索引的缺点:

降低了INSERT、UPDATE、DELETE的速度,因为在插入、修改、删除数据时,还要同时操作一下索引文件;
会占用一定的磁盘空间。

创建索引时注意事项:

选择性低的字段不要创建索引,比如性别、状态;
很少查询的列不要创建索引(项目初期就要确定好);
大数据类型字段不要创建索引;
尽量避免使用NULL,应该指定列为NOT NULL(在MySQL中,含有空值的列很难进行查询优化,它们会使得索引、索引的统计信息及比较运算更加复杂。可以使用空字符串代替空值)。

索引失效的场景:

通过索引扫描的行记录数超过了全表的30%,优化器就不会走索引,而变成全表扫描;

复合索引中,第一个查询条件不是最左索引列;

复合索引中,第一个索引列使用范围查询,只能使用到部分索引,有ICP出现(范围查询是指 <、=、<=、BETWEEN and);

复合索引中,第一个查询条件不是最左前缀列;

模糊查询条件列最左以通配符 % 开始(可以考虑放到子查询里面);

两个单列索引,一个用于检索,一个用于排序。这种情况下只能使用到一个索引,因为查询语句中最多只能使用一个索引,考虑建立复合索引;

查询字段上面有索引,但是使用了函数运算。

Leave comment

Your email address will not be published. Required fields are marked with *.

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据