MYSQL进阶03

索引概述

所有的MySQL列类型都可以被索引，对相关列使用索引是提高select操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引和最大索引长度，没中存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，大多数存储引擎有着而更高的限制。
MyISAM和InnoDB存储引擎默认创建的都是BTREE索引。

设计索引的原则

索引设计可以遵循一些已有的原则

1. 搜索索引不一定是索要选择的列，换句话说，最适合索引的列是出现在where字句中的列，或者连接件子句中指定的列，而不是select关键字中后选择列表中的列。
2. 使用唯一索引。考虑某列中值的分布。索引列的基数越大，索引效果越好
3. 使用短索引，如果对字符串进行索引，应该指定一个前缀长度
4. 利用最左前缀。在创建一个n列的索引时，实际时创建了MySQL可以用的n个索引。多列索引可起几个索引的作用，因为可以利用索引最左边的列集来进行匹配
5. 针对频繁作为查询条件的字段建索引。这可以加速查询速度。
6. 针对数据量较大的表建索引。因为索引本身也占空间,如果表数据量很小,索引效果不明显,反而会占用额外空间。
7. 选择区分度高的列作为索引列。区分度高意味着有更多的不同值,这可以使索引的选择性更高,查询速度更快。
8. 选择较长的列作为索引列。因为索引值会完整存储,较短的列意味着索引数据的重复率会更高,查询选择性会更低。
9. 组合多列数据类型不同的列建立联合索引。这可以在一定程度上破除索引列类型限制,提高查询效率。
10. 为频繁更新的字段建立索引要谨慎。因为每次更新会导致索引变更,会降低更新速度。
11. 不要过度索引。建立索引也需要消耗存储空间和时间,如果建立太多无用索引会导致维护成本增高,查询优化器的选择困难等。

Btree索引和Hash索引

1. 数据顺序:

• B树索引会对数据进行排序,数据由小到大或由大到小排列。
• 哈希索引是无序的,数据按照哈希函数计算后的散列结果存储。

2. 查询效率:

• B树索引支持范围查询,时间复杂度O(logN)。
• 哈希索引只支持精确查询,时间复杂度O(1)。

3. 数据分布:

• B树索引数据聚集,相近的数据存储在一起。
• 哈希索引数据分散,数据位置由哈希函数决定,相近的数据可能存储在任意位置。

4. 索引更新:

• B树索引更新代价较高,插入和删除后需要重新排序和调整树形结构。
• 哈希索引的更新代价较低,只需要计算新值的哈希结果并插入相应位置。

5. 冗余:

• B树索引重复的键只会存储一次,没有冗余数据。
• 哈希索引可能存在多个相同键散列到同一个 slot 的情况,需要链地址处理,会产生一定冗余。
  所以综上:
  如果需要范围查询或需要数据有序,选择B树索引。
  如果主要是精确查询,并且更新操作较频繁,可以选择哈希索引。
  如果两者之间无特定需求,B树索引作为一种更加普遍和完备的索引结构,是传统的首选方案。但在具体应用中,还是需要根据实际数据和查询模式选择最优的索引机制

有很多博客文章已经迁移到我的个人博客网站，欢迎大家访问是小靳的~