索引 indexing
- 索引是用来提高查询效率的, 会将数据按照被设为索引的字段进行排序, 这样根据该字段查找的时候更快
- 数据库在没有索引的情况下, 默认进行全文搜索, 这意味着所有数据都会被遍历到
- 索引和数据文件一样存储在磁盘上, 因此过多的索引会占用空间; 索引和数据文件的关联性也导致维护所需的资源增加
MySQL的索引
https://developer.aliyun.com/article/831250#slide-0
分类
主键索引
数据库自动建立的索引, 以数据表的主键primary key为主
唯一索引
create unique index age_index on user(age);
索引的值必须唯一, 可以是Null值
复合索引composite indexing
create index composite_index on user(name, birth_year, year);
最左前缀原则
多个字段联合组成的索引, 遵循最左前缀原则, 即查询语句中的筛选条件中, 索引的第一个key必须要有且在第一个⬇️
例如创建一个复合索引(name, birth_year, year)
-- 这些可查询, 注意name在最左边
select * from stu where name = ?;
select * from stu where name = ? and birth_year = ?;
select * from stu where name = ? and year = ?;
select * from stu where name = ? and birth_year = ? and year = ?;
同时mysql会自动变换顺序, 如果索引内的字段都被提到的话
-- 这一条也可查询, 注意三个字段都在
select * from stu where birth_year = ? and year = ? and name = ?;
如果顺序不正确或者该有的字段没有的话, 是不可用的
- 全文索引, 空间索引, 前缀索引
存储方式
在mysql中, B+ tree被用来存储索引, 这是因为(1)不想二叉树一样一个节点只能分两个节点, (2) 数据都被存储在叶子节点中, 而这些节点之间由指针双向链接, 比B tree更适合快速查找
实例
这里用InnoDB引擎⬇️
首先创建一个表, 将id作为主索引, age作为一般(辅助)索引
CREATE TABLE 'user_innodb' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'username' varchar(20) DEFAULT NULL,
'age' int(11) DEFAULT NULL,
PRIMARY KEY ('id') USING BTREE,
KEY 'idx_age' ('age') USING BTREE
) ENGINE = InnoDB;
主键索引的叶子节点存储数据, 普通节点(辅助节点)的叶子节点存储的是主键值
这是主键索引
这是(主键索引以外的)辅助索引
查询的流程都是一样的, 就是通过箭头一直向下查找到叶子节点, 找到对应的数据; 辅助索引需要额外先查找到对应的主键(即id)
这是复合索引, 假设现在有一个复合索引(a, b, c), 查找索引是会根据复合索引中字段的顺序来依次比较, 然后决定去往哪一个子节点, 和辅助索引一样, 叶子节点存储的是主键
