1
SELECT * FROM orders WHERE user_id = 42;

1
-- InnoDB 中，主键就是聚簇索引
2
CREATE TABLE users (
3
    id BIGINT PRIMARY KEY,       -- 聚簇索引
4
    email VARCHAR(255),
5
    name VARCHAR(100),
6
    age INT,
7
    INDEX idx_email (email)      -- 二级索引，叶子存 id 值
8
);
9

10
-- 通过二级索引查找需要回表
11
SELECT * FROM users WHERE email = 'alice@example.com';
12
-- 步骤：idx_email 找到 id=5，再回聚簇索引找 id=5 的完整行

1
-- 联合索引
2
CREATE INDEX idx_status_created ON orders (status, created_at);
3

4
-- 可以使用索引（最左前缀匹配）
5
SELECT * FROM orders WHERE status = 'shipped';
6
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2026-01-01';
7

8
-- 无法使用索引（跳过了最左列 status）
9
SELECT * FROM orders WHERE created_at > '2026-01-01';
10

11
-- 部分使用索引（仅使用 status 列，created_at 无法用于过滤）
12
SELECT * FROM orders WHERE status = 'shipped' AND amount > 100;

1
-- 计算列的选择性
2
SELECT COUNT(DISTINCT status) / COUNT(*) AS selectivity FROM orders;

1
-- 查看表的索引基数（不同值的数量）
2
SHOW INDEX FROM orders;
3
-- 关注 Cardinality 列，它反映优化器对该索引区分度的估算

1
-- 查看自适应哈希索引状态
2
SHOW ENGINE INNODB STATUS\G
3
-- 关注 "INSERT BUFFER AND ADAPTIVE HASH INDEX" 段
4

5
-- 开启/关闭自适应哈希索引
6
SET GLOBAL innodb_adaptive_hash_index = ON;

1
-- MySQL 全文索引（中文需要 ngram 分词器）
2
CREATE TABLE articles (
3
    id INT PRIMARY KEY,
4
    title VARCHAR(200),
5
    content TEXT,
6
    FULLTEXT INDEX ft_content (title, content) WITH PARSER ngram
7
);
8

9
-- 全文搜索
10
SELECT title,
11
    MATCH(title, content) AGAINST('数据库 索引' IN NATURAL LANGUAGE MODE) AS score
12
FROM articles
13
WHERE MATCH(title, content) AGAINST('数据库 索引' IN NATURAL LANGUAGE MODE)
14
ORDER BY score DESC;

1
-- MySQL 空间索引
2
CREATE TABLE locations (
3
    id INT PRIMARY KEY,
4
    name VARCHAR(100),
5
    point GEOMETRY NOT NULL SRID 4326,
6
    SPATIAL INDEX idx_point (point)
7
);
8

9
-- 空间范围查询
10
SELECT name, ST_Distance_Sphere(point, ST_GeomFromText('POINT(31.23 121.47)', 4326)) AS dist
11
FROM locations
12
WHERE ST_Contains(
13
    ST_Buffer(ST_GeomFromText('POINT(31.23 121.47)', 4326), 0.03),
14
    point
15
);

1
-- 联合索引
2
CREATE INDEX idx_user_email_name ON users (email, name);
3

4
-- 覆盖索引：查询只需要 email 和 name，索引中都有
5
SELECT email, name FROM users WHERE email = 'alice@example.com';
6

7
-- 非覆盖索引：还需要 age，必须回表
8
SELECT email, name, age FROM users WHERE email = 'alice@example.com';

1
-- MySQL EXPLAIN 验证覆盖索引
2
EXPLAIN SELECT email, name FROM users WHERE email = 'alice@example.com';
3
-- Extra: Using index 表示覆盖索引生效，无需回表

1
-- 联合索引 (email, name)
2
-- 查询条件使用了 email（索引第一列）和 name（索引第二列）
3
SELECT * FROM users WHERE email LIKE 'ali%' AND name LIKE '%ce';

1
-- 查看 ICP 是否生效
2
EXPLAIN SELECT * FROM users WHERE email LIKE 'ali%' AND name LIKE '%ce';
3
-- Extra: Using index condition 表示索引下推生效

1
-- 前缀索引：只取 URL 前 20 个字符
2
CREATE INDEX idx_url_prefix ON pages (url(20));
3

4
-- 选择前缀长度的原则：选择性接近完整列即可
5
SELECT
6
    COUNT(DISTINCT url) / COUNT(*) AS full_selectivity,
7
    COUNT(DISTINCT LEFT(url, 10)) / COUNT(*) AS prefix_10,
8
    COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS prefix_20,
9
    COUNT(DISTINCT LEFT(url, 30)) / COUNT(*) AS prefix_30
10
FROM pages;
11
-- 选择使选择性接近 full_selectivity 的最短前缀

1
-- 判断该不该加索引的检查清单
2
-- 1. 看选择性
3
SELECT COUNT(DISTINCT trace_id) / COUNT(*) AS selectivity FROM access_log;
4
-- 0.95，选择性很高
5

6
-- 2. 看这张表的读写情况（sys.schema_index_statistics 或慢查询日志）
7
-- 如果 access_log 每天写入千万级、查询个位数 → 不加
8
-- 如果查询频繁、每次按 trace_id 精确查一条 → 加
9

10
-- 3. 表规模
11
SELECT table_rows FROM information_schema.tables
12
WHERE table_name = 'access_log';
13
-- 千万行级别 → 值得为高频精确查询加索引

1
-- 场景：订单表有 status、user_id、created_at 三列
2
-- 查询模式 1：WHERE user_id = ? AND status = ?（高频）
3
-- 查询模式 2：WHERE user_id = ? AND created_at > ?（中频）
4
-- 查询模式 3：WHERE status = ? AND created_at > ?（低频）
5

6
-- 最优索引：user_id 在前（等值+高频），status 在中（等值），created_at 在后（范围）
7
CREATE INDEX idx_uid_status_created ON orders (user_id, status, created_at);
8

9
-- 这个索引可以覆盖查询模式 1 和 2
10
-- 查询模式 3 需要单独的索引
11
CREATE INDEX idx_status_created ON orders (status, created_at);

1
-- 高频查询：只取 email 和 name
2
SELECT email, name FROM users WHERE email = 'alice@example.com';
3

4
-- 覆盖索引：把查询列都纳入索引
5
CREATE INDEX idx_email_name ON users (email, name);
6
-- Extra: Using index，无需回表

1
-- 错误：对 created_at 使用函数
2
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
3

4
-- 正确：使用范围查询
5
SELECT * FROM orders
6
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
7

8
-- 错误：对 user_id 进行算术运算
9
SELECT * FROM orders WHERE user_id + 1 = 43;
10

11
-- 正确：将运算移到等号另一侧
12
SELECT * FROM orders WHERE user_id = 42;

1
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
2
-- type: ALL          （全表扫描）
3
-- possible_keys: NULL
4
-- key: NULL          （未使用索引）
5
-- Extra: Using where
6

7
EXPLAIN SELECT * FROM orders
8
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
9
-- type: range         （范围扫描）
10
-- key: idx_created_at （使用索引）
11
-- Extra: Using index condition

1
-- MySQL 8.0+ 函数索引
2
CREATE INDEX idx_year_created ON orders ((YEAR(created_at)));

1
-- 假设 user_id 是 VARCHAR 类型
2
-- 错误：传入整数，MySQL 将 user_id 隐式转为数字
3
SELECT * FROM users WHERE user_id = 123;
4

5
-- 正确：传入字符串
6
SELECT * FROM users WHERE user_id = '123';
7

8
-- 假设 phone 是 VARCHAR 类型
9
-- 错误：传入数字
10
SELECT * FROM users WHERE phone = 13800138000;
11

12
-- 正确：传入字符串
13
SELECT * FROM users WHERE phone = '13800138000';

1
-- user_id 为 VARCHAR，传入整数
2
EXPLAIN SELECT * FROM users WHERE user_id = 123;
3
-- type: ALL          （全表扫描，索引失效）
4
-- key: NULL
5

6
-- 传入字符串
7
EXPLAIN SELECT * FROM users WHERE user_id = '123';
8
-- type: ref          （索引查找）
9
-- key: idx_user_id

1
-- 索引：idx_status_created (status, created_at)
2

3
-- 错误：跳过 status，直接查 created_at
4
SELECT * FROM orders WHERE created_at > '2026-01-01';
5

6
-- 正确：包含最左列 status
7
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2026-01-01';
8

9
-- 错误：status 用范围查询，created_at 无法利用索引排序
10
SELECT * FROM orders WHERE status > 'pending' ORDER BY created_at;
11

12
-- 正确：status 用等值查询，created_at 可利用索引排序
13
SELECT * FROM orders WHERE status = 'shipped' ORDER BY created_at;

1
-- 跳过最左列
2
EXPLAIN SELECT * FROM orders WHERE created_at > '2026-01-01';
3
-- type: ALL
4
-- key: NULL          （未使用联合索引）
5

6
-- 包含最左列
7
EXPLAIN SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2026-01-01';
8
-- type: range
9
-- key: idx_status_created
10
-- Extra: Using index condition

1
-- 假设 email 有索引，name 没有索引
2
-- 错误：name 无索引导致整个 OR 无法使用索引
3
SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';
4

5
-- 正确方案 1：为 name 也建索引
6
CREATE INDEX idx_name ON users (name);
7
SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';
8
-- 优化器可能使用索引合并（Index Merge）
9

10
-- 正确方案 2：用 UNION 替代 OR
11
SELECT * FROM users WHERE email = 'alice@example.com'
12
UNION
13
SELECT * FROM users WHERE name = 'Alice';

1
-- name 无索引时
2
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';
3
-- type: ALL          （全表扫描）
4
-- key: NULL
5

6
-- name 建索引后
7
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com' OR name = 'Alice';
8
-- type: index_merge   （索引合并）
9
-- key: idx_email,idx_name
10
-- Extra: Using union(idx_email,idx_name); Using where

1
-- 错误：前缀通配符，无法使用索引
2
SELECT * FROM users WHERE name LIKE '%lice';
3

4
-- 正确：前缀匹配，可以使用索引
5
SELECT * FROM users WHERE name LIKE 'ali%';
6

7
-- 如需全文搜索，使用全文索引
8
SELECT * FROM users WHERE MATCH(name) AGAINST('lice');

1
-- 左模糊
2
EXPLAIN SELECT * FROM users WHERE name LIKE '%lice';
3
-- type: ALL
4
-- key: NULL          （索引失效）
5

6
-- 前缀匹配
7
EXPLAIN SELECT * FROM users WHERE name LIKE 'ali%';
8
-- type: range
9
-- key: idx_name      （索引生效）
10
-- Extra: Using index condition

1
-- 假设 status 有 3 个值：pending、shipped、cancelled
2
-- pending 占 80%，shipped 占 15%，cancelled 占 5%
3

4
-- 错误：!= 过滤掉的行太少，优化器倾向全表扫描
5
SELECT * FROM orders WHERE status != 'pending';
6

7
-- 正确：用 IN 列举需要的值，优化器能更精确评估
8
SELECT * FROM orders WHERE status IN ('shipped', 'cancelled');

1
EXPLAIN SELECT * FROM orders WHERE status != 'pending';
2
-- type: ALL          （选择率低，优化器放弃索引）
3
-- key: NULL
4
-- rows: 1000000      （估算扫描全表）
5

6
EXPLAIN SELECT * FROM orders WHERE status IN ('shipped', 'cancelled');
7
-- type: range        （选择率高，索引生效）
8
-- key: idx_status
9
-- rows: 200000       （估算扫描 20%）

1
-- 假设 orders 表 1000 万行，status = 'shipped' 占 60%（600 万行）
2
CREATE INDEX idx_status ON orders (status);
3

4
-- 优化器可能放弃索引：600 万次回表的随机 I/O 远超全表扫描
5
SELECT * FROM orders WHERE status = 'shipped';
6

7
-- 强制使用索引（不推荐，仅用于诊断）
8
SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 'shipped';

1
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
2
-- type: ALL          （优化器评估回表代价过高，放弃索引）
3
-- key: NULL
4
-- rows: 6000000      （估算匹配 600 万行）
5

6
-- 强制索引
7
EXPLAIN SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 'shipped';
8
-- type: ref
9
-- key: idx_status
10
-- rows: 6000000
11
-- Extra: Using index condition
12
-- 实际执行可能比全表扫描更慢，因为大量随机回表

1
-- 查看表的所有索引
2
SHOW INDEX FROM orders;
3
-- 关注字段：
4
--   Cardinality：索引基数（不同值数量估算）
5
--   Non_unique：0=唯一索引，1=非唯一
6
--   Seq_in_index：联合索引中的列顺序

1
-- 更新统计信息（MySQL 8.0 采样，不锁表）
2
ANALYZE TABLE orders;
3

4
-- 持久化统计信息（MySQL 8.0+）
5
SET GLOBAL innodb_stats_persistent = ON;
6
ALTER TABLE orders STATS_PERSISTENT = 1;

1
-- 开启慢查询日志
2
SET GLOBAL slow_query_log = ON;
3
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录
4

5
-- 查看慢查询日志位置
6
SHOW VARIABLES LIKE 'slow_query_log_file';

1
-- 查看表的索引统计信息
2
SELECT *
3
FROM mysql.innodb_index_stats
4
WHERE database_name = 'testdb' AND table_name = 'orders';
5
-- 关注：
6
--   stat_name = 'n_leaf_pages'：索引叶子页数
7
--   stat_name = 'size'：索引总页数
8
--   stat_name = 'n_diff_pfxNN'：联合索引前缀的基数估算

1
-- 调整采样页数
2
-- 持久化统计（innodb_stats_persistent 开启时生效，默认采样 20 页）
3
SET GLOBAL innodb_stats_persistent_sample_pages = 100;
4
-- 非持久化统计（innodb_stats_persistent 关闭时生效，默认采样 8 页）
5
SET GLOBAL innodb_stats_transient_sample_pages = 100;
6
-- 采样页数越多，统计越准，但 ANALYZE TABLE 越慢
7

8
-- 查看采样设置
9
SHOW VARIABLES LIKE 'innodb_stats%_sample_pages';

1
-- MySQL 8.0 查询冗余索引
2
SELECT * FROM sys.schema_redundant_indexes
3
WHERE table_schema = 'testdb';
4

5
-- 查询从未使用的索引
6
SELECT * FROM sys.schema_unused_indexes
7
WHERE object_schema = 'testdb';