索引是一种用于快速查询行的数据结构,是SQL优化最简单也最重要的方式。
1.B-Tree 索引
最常见的一种索引,InnoDB,MyISAM,Memory这三种引擎都支持B-Tree索引。
索引的本质是一种数据结构。B-Tree索引使用的数据结构是B+树,这种数据结构父节点不存储数据,只存储数值(数据)的范围,而在叶子节点存储数据和指针。
1.1 聚簇索引(主键索引)
InnoDB引擎的表都有一个特殊的索引,叫聚簇索引,聚簇索引并不是一个单独的索引类型,而是B-Tree索引的一种数据存储方式。
这种存储方式表中,表中所有的数据都存放在叶子节点中,一个表对应一个聚簇索引。
InnoDB表中聚簇索引的索引列就是主键,所以聚簇索引也叫主键索引,如果表中没有主键,InnoDB找非空唯一键,如果也没有,InnoDB会创建一个唯一的隐藏的列作为主键。
1.2 非聚簇索引
在MyISAM引擎下,非聚簇索引的B-Tree树上的叶子节点存储的是索引列值和对应的行数据在磁盘中的地址,索引和行数据分开存储。而聚簇索引,索引列值和对应的行数据都在B-Tree树上。
1.3 二级索引(我们自己添加的索引)
在InnoDB引擎中,表一旦创建完成就会有一个默认的聚簇索引,表中的所有数据都会存储在聚簇索引对应的B-Tree上。很多的时候,仅适用主键索引不能满足需求,需要新添加索引。这些新添的非主键索引就称之为二级索引。二级索引的数量不限。
和聚簇索引不同的是,二级索引叶子节点存储的是索引列值和这一列对应的主键值。
因为表中所有的数据都存储在聚簇索引的B-Tree树上,所以二级索引根据索引进行查询的时候,先在二级索引的B-Tree树上找到主键值,然后根据主键值在聚簇索引的B-Tree树上查找到具体的行数据。
1.3.1 组合索引
组合索引是我们自定义的索引(多个列作为索引),因此也属于二级索引。组合索引在查询的时候,需要遵循 最左前缀原理。左边的索引列在查询中必须出现,否则不走索引。例如,以name + age + sex这三个列作为组合索引,在where作为查询条件可以使用的列为:name, name + age, name + age + sex。不能单独使用age或者sex或者name和sex组合作为查询条件,否则全表扫描,不走索引。
// 走索引的情况
select * from table where name = "lisi"
select * from table where name = "lisi" and age = 13
select * from table where name = "lisi" and age = 13 and sex="男"
//不走索引的情况
select * from table where age = 13
select * from table where sex="男"
select * from table where age = 13 and sex="男"
创建组合索引时应该将最常用(频率)作限制条件的列放在最左边,依次递减。
总结:
在InnoDB引擎中,表一旦创建完成就会生成聚簇索引,聚簇索引的B-Tree树的叶子节点存储的是主键值和对应的行数据(表中所有的数据都会在这个B-Tree树上)。我们自己新建的索引称之为二级索引,二级索引的B-Tree树的叶子节点存储索引列值和对应行数据的主键。使用二级索引查询的时候,先根据索引列值得到行数据的主键,然后使用得到的主键在聚簇索引的B-Tree树上查询对应的行数据。
2.哈希索引
哈希索引只有Memory引擎支持。
哈希索引的索引树上存储的是索引列的hash值。
优点:
- 查询速度特别快
缺点:
- 哈希索引不是按照索引顺序存储的,无法用于排序。
- 不支持部分索引列匹配查找。
- 不支持范围查找。
3.其他索引
- R-Tree 索引(空间索引):空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型。
- Full-text (全文索引):全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。全文索引是一种特殊类型的索引,通过建立倒排索引,快速匹配文档的方式。它查找的是文本中的关键字。
4.索引使用原则
较频繁的作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
更新非常频繁的字段不适合创建索引
5.其他
expain关键字:expain是查看sql执行计划的命令,使用该命令,可以查看sql语句查询时有没有使用索引。
参考链接