[数据库] 使用索引(1): MySQL

索引 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)