目录
数据库索引
数据库索引是存储引擎用于快速查询的一种数据结构,通常使用B树和B+树来实现。因为索引所需的物理空间会随着数据量的增加而增大,建立和维护索引的成本也随之增大,所以,对于小型的表,全表搜索效率更高,对于中大型的表,索引更有效。
索引可以包含一个列或多个列的值,在创建索引时,考虑在哪些列上建立索引是十分重要的:
- 在经常需要搜索的列上,可以加快搜索速度;(where)
- 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;(主键约束 PRIMARY);
- 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;(外键?join时on的字段嘛?)
- 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
- 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
创建索引的方法
- 使用CREATE INDEX语句:CREATE INDEX indexName ON tableName(columnName);
- ALTER TABLE tableName ADD INDEX indexName ( `columnName` );
- 通过定义主键约束或者唯一性键约束,也可以间接创建索引;
注:主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。
索引的类型
MySQL支持的索引从数据结构角度分为:B-Tree索引(从技术上说是B+Tree)、哈希索引、FULLTEXT索引、R-Tree索引。
B-Tree索引:B-Tree索引中索引的值都是按顺序存储,并且每一个叶子点到根的距离相同。从索引的根节点开始搜索,根节点的槽中存放了指向子结点的指针,通过比较节点页的值和要查找的值,存储引擎根据指针向下层查找,叶子节点指向被索引的数据。B-Tree对索引是顺序组织存储的,如果索引包含多列,他会根据CREATE TABLE 语句中定义索引时列的顺序来排序。
B-Tree索引的限制:
- 如果不是按照索引的最左列开始查找,则无法使用索引。
- 不能跳过索引中的列。
- 如果索引中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。
哈希索引:基于哈希表实现,对于每一行数据,存储引擎都会对所有的索引列计算出一个哈希码,哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。
哈希索引的限制:
- 哈希索引数据并不是按照索引值顺序存储的,无法用于排序。
- 哈希索引不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值。
- 哈希索引只包含哈希值和行指针,不存储字段值,所以不能使用索引中的值来避免读取行。(访问内存中的行的速度很快,对性能影响不大)。
- 哈希索引只支持等值比较查询,不支持范围查询。
- 注意哈希冲突。