索引帮助数据库高效获取数据的数据结构,索引是为了加快查询速度,减少IO。
磁盘IO与预读
数据库数据是保存在磁盘上的,但为了提升性能,会把部分数据读取到内存进行计算。访问磁盘的成本是访问内存成本的十万倍左右。
计算机操作系统对磁盘读取数据进行了优化,当一次IO时,不仅会把当前磁盘地址的数据读取到,而是把相邻的数据也读取到内存缓冲区中。(局部预读性原理:当计算机访问一个地址的数据时,与其相邻的数据也会很快被访问到)。每次IO读取的数据我们称之为一页(page),具体一页有多大数据和操作系统有关,一般为4k或8k,也就是说当我们读取一页数据时,实际只发生了一次IO。
索引分类
从存储结构分类
- Btree(B+tree, B-tree)
- 哈希索引
- full text全文索引
- Rtree
从应用层次划分
- 普通索引:一个索引只包含单列,一个表可以有多个单列索引;
- 唯一索引:索引的值必须唯一,但允许有空值;
- 复合索引:一个索引包含多个列。
- 短索引:指定长度的字符串类型的索引(col_name[length])。
从表记录的排列顺序和索引的排列顺序是否一致划分
- 聚集索引:表记录的排列顺序和索引的排列顺序一致;
- 非聚集索引:表记录的排列顺序和索引的排列顺序不一致。
聚集索引和非聚集索引
聚集索引:以主键创建的索引;
表记录顺序和索引的排列顺序一致,所以查询速度快,因为只要找到第一个索引值记录,其余的连续性记录也会在物理表中连续存放,一起就会查到。
聚集索引的叶子节点存储的是主键和表中的数据。
缺点:新增比较慢,为保证表记录顺序和索引排列顺序一致,在记录插入时,会对数据页重新排序。
非聚集索引:以非主键创建的索引(也叫二级索引)。
索引的逻辑顺序与磁盘上行的物理存储顺序不同,非聚集索引在叶子节点存储的是索引列和主键,查询时,需要拿到叶子节点的主键再去查询想要的数据,也称为回表。
添加索引
# 主键索引
ALTER TABLE table_name ADD PRIMARY KEY (`column`);
# 唯一索引
ALTER TABLE table_name ADD UNIQUE (`column`);
# 普通索引
ALTER TABLE table_name ADD INDEX index_name (`column`);
# 全文索引
ALTER TABLE table_name ADD FULLTEXT index_name (`column`);
# 多列索引
ALTER TABLE table_name ADD INDEX index_name (`column1`, `column2`, `column3`);
# 短索引
ALTER TABLE table_name ADD INDEX idx_name(`column1`(2));
索引数据结构
哈希索引
将键