索引是一个排序的列表，列表中存储索引值，值所在行的物理地址
使用索引后可以不用扫描全表来定位某行数据，而是通过索引表找到行的数据进行直接/间接访问（非聚簇索引：地址访问，聚簇索引：实际数据访问），或者通过记录主键值再查询主键索引访问（回表）
不同的DBMS有着不同的实现，这里探究MySQL索引的使用与实现
语法
对于经常查询的数据，我们可以为其建立索引
CREATE TABLE user (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100),
age INT
);
CREATE INDEX idx_user_email ON user(email);
底层实现
InnoDB使用B+树索引实现，把“无序查找”变成了“有序查找”，
B+树结构如下：
[M]
/ \
[F] [T]
/ \ / \
[A~F] [G~M] [N~T] [U~Z]
如果我们需要搜索邮箱email = "tom@qq.com"，MySQL 不需要从第一行开始找，而是会先判断大概在那个范围，然后再逐层深入
B+树是B树的一个变种，B树属于多叉树又名平衡多路查找树
主键索引 vs 普通索引
CREATE TABLE user (
id BIGINT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);
CREATE INDEX idx_user_username ON user(username);
主键索引是：id -> 整行数据
普通索引 idx_user_username 是：username -> id
SELECT * FROM user WHERE id = 10; # 可以直接通过主键索引找到整行数据。
SELECT * FROM user WHERE username = 'xiaoyi'; # 1. 先查 username 索引，找到 username = 'xiaoyi' 对应的 id2. 再拿 id 去主键索引找完整数据
这就是为什么有时候即使走了索引，也不一定特别快，因为可能还要回表。
使用场景
什么情况下索引特别有用？
索引最适合用在经常出现在这些位置的字段上：
- WHERE
- ORDER BY
- GROUP BY
- JOIN ON
什么情况建索引没啥用？
- 字段内容上，对于只有像性别这样区分度低的就不适合建索引
- 从后往前找的没啥用，例如
SELECT * FROM user WHERE username LIKE '%yi'; - 对字段使用函数的没啥用，例如
SELECT * FROM user WHERE LOWER(username) = 'xiaoyi';，由于需要先对每一行的username做LOWER计算，索引原本的有序性用不上 - 对于连表查询不利用B+树特性加快效率的没啥用，索引：加快查询速率`
比如：
SELECT * FROM user WHERE email = 'abc@qq.com'; # email 适合建索引。
SELECT * FROM article ORDER BY created_at DESC; # created_at 可能适合建索引。
SELECT * FROM orders WHERE user_id = 1001; # user_id 很适合建索引，因为订单表通常会按用户查订单。
说了这么多，那么代价是什么？
索引作为额外的数据结构，过多使用会占用空间，降低增删改速度。（就像线程不是越多越好，太多反而会增加线程上下文切换开销）
总结
MySQL 索引是一种帮助数据库快速查找数据的数据结构。
InnoDB 中常见的索引结构是 B+ 树。没有索引时，查询可能需要全表扫描；有索引后，可以通过 B+ 树从根节点逐层定位到叶子节点，减少扫描的数据量和磁盘 IO，所以查询速度更快。
InnoDB 的主键索引叶子节点存放整行数据，普通索引叶子节点一般存放主键值，如果查询的字段不在索引里，可能还需要通过主键回表查询。
评论（0）
暂无评论