索引学习

一、key和index--容易混淆的概念

index和key虽然经常混为一谈，但还是有区别的 index:物理结构，是数据库层面的实现;用于提高查询性能
key :逻辑约束，是数据关系层面的概念；用于确保数据完整性、建立表关系

所有 KEY 都是 INDEX，但不是所有 INDEX 都是 KEY

2.约束不等于索引（但经常依赖索引）
UNIQUE/PRIMARY 基本一定对应索引结构
FOREIGN KEY 是约束，本身不是索引，但常需要索引配合

二、联合索引-最左前缀原则

1.联合索引 (a,b) 能支持：
WHERE a = ...
WHERE a = ... AND b = ...
WHERE a = ... ORDER BY b（常见可利用索引顺序）
但 通常不支持/很难利用：
WHERE b = ...（缺少最左列 a，索引顺序没法直接定位）

2. 区别
   idx(a,b) 适合：你的常见查询是 a 单独查、或 a+b 一起查、或 a 过滤后按 b 排序。
   两个单列索引适合：a 和 b 经常独立使用，而且很少同时出现。

三、普通索引与联合索引

只用于加速查询，不保证值唯一，同一个值可以出现多次
(UNIQUE INDEX）：额外要求索引列（或列组合）取值必须唯一；允许多个 NULL

2.创建示例
索引名，表名，字段名

CREATE INDEX idx_a ON t(a);
-- 
CREATE UNIQUE INDEX uk_a ON t(a);

-- 联合唯一索引：要求 (a,b) 组合唯一
CREATE UNIQUE INDEX uk_ab ON t(a,b);

四、mul / uni 与列属性标记

mul：multiple（可重复/多重匹配）；uni （unique）
列属性/机制标记: auto_increment; 还有INVISIBLE等

五、聚簇索引和非聚簇索引（主键索引和二级索引）具体例子

1.主键（PRIMARY KEY）在 InnoDB：聚簇索引
数据行本身就存放在主键 B+ 树的叶子节点

2.普通索引 / 唯一索引：二级索引
但叶子节点存的是：索引列值 + 主键值
通过二级索引找到主键后，通常还要再去主键树取整行：这一步叫 回表

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,      -- 聚簇索引键
    email VARCHAR(100),
    name VARCHAR(50),
    INDEX idx_email (email)                 -- 二级索引
);

-- 插入数据
INSERT INTO users VALUES 
(1, 'bob@x.com', 'Bob'),
(2, 'alice@x.com', 'Alice'),
(3, 'carol@x.com', 'Carol');

1. 聚簇索引（数据文件）：
   页1: [id=1, email='bob@x.com', name='Bob']
   [id=2, email='alice@x.com', name='Alice']
   页2: [id=3, email='carol@x.com', name='Carol']
   （按id顺序物理存储）

2. 二级索引 idx_email：
   叶子节点内容：

   email值	主键id值
   alice@x.com	2
   bob@x.com	1
   carol@x.com	3

   （按email字母顺序排序）

若执行SELECT * FROM users WHERE email = 'alice@x.com';

1.在二级索引idx_email中查找

在B+树中找到 email='alice@x.com'
得到对应的 主键id=2
；若改为select (id或者是email)，都无需回表，这个过程叫覆盖索引

2.回表查询（Bookmark Lookup）
用 id=2 到聚簇索引中查找
在聚簇索引B+树中查找id=2
找到对应的数据行，返回完整数据

3.无需回表

SELECT email FROM users WHERE email = 'alice@x.com ;//无需回表
SELECT name FROM users WHERE email = 'alice@x.com ；//需要回表