MySQL索引 #

没有索引：
┌─────────────────────────────────────┐
│ 数据页 → 数据页 → 数据页 → 数据页  │
│   ↓       ↓       ↓       ↓        │
│  扫描所有数据页查找目标            │
└─────────────────────────────────────┘

有索引：
┌─────────────────────────────────────┐
│        索引树                       │
│       /      \                      │
│     节点     节点                   │
│     / \      / \                    │
│   数据 数据 数据 数据               │
│   快速定位目标数据                  │
└─────────────────────────────────────┘

-- MySQL主要使用B+树索引
-- B+树特点：
-- 1. 非叶子节点只存储键值
-- 2. 叶子节点存储所有数据和指针
-- 3. 叶子节点通过指针连接，便于范围查询

-- InnoDB存储结构
-- 聚簇索引：叶子节点存储完整行数据
-- 二级索引：叶子节点存储主键值

-- 主键自动创建索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 或
ALTER TABLE users ADD PRIMARY KEY (id);

-- 创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 创建唯一索引
CREATE UNIQUE INDEX uk_email ON users(email);

-- 或
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 或
ALTER TABLE users ADD INDEX idx_name (name);

-- 创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

-- 创建组合索引（多列索引）
CREATE INDEX idx_name_age ON users(name, age);

-- 最左前缀原则
-- 索引可用于以下查询：
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE name = 'John' AND age = 25;
SELECT * FROM users WHERE name = 'John' ORDER BY age;

-- 索引不可用于以下查询：
SELECT * FROM users WHERE age = 25;  -- 不满足最左前缀

-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- 使用全文索引
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL database');

-- 自然语言模式
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL database' IN NATURAL LANGUAGE MODE);

-- 布尔模式
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 创建空间索引（需要空间数据类型）
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    point POINT,
    SPATIAL INDEX idx_point (point)
);

-- 使用空间函数查询
SELECT * FROM locations 
WHERE ST_Distance(point, POINT(0, 0)) < 100;

-- 方式1：CREATE INDEX
CREATE INDEX idx_name ON users(name);

-- 方式2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_name (name);

-- 方式3：创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

-- 查看表索引
SHOW INDEX FROM users;

-- 查看索引信息
SELECT 
    index_name,
    column_name,
    non_unique,
    seq_in_index
FROM information_schema.statistics
WHERE table_name = 'users';

-- 方式1：DROP INDEX
DROP INDEX idx_name ON users;

-- 方式2：ALTER TABLE
ALTER TABLE users DROP INDEX idx_name;

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

-- 重建表（包括索引）
ALTER TABLE users ENGINE=InnoDB;

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

-- 1. WHERE条件经常使用的列
SELECT * FROM users WHERE email = 'john@example.com';
-- 为email创建索引
CREATE INDEX idx_email ON users(email);

-- 2. JOIN连接的列
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- 为user_id创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 3. ORDER BY排序的列
SELECT * FROM users ORDER BY created_at DESC;
-- 为created_at创建索引
CREATE INDEX idx_created ON users(created_at);

-- 4. GROUP BY分组的列
SELECT status, COUNT(*) FROM users GROUP BY status;
-- 为status创建索引
CREATE INDEX idx_status ON users(status);

-- 5. DISTINCT去重的列
SELECT DISTINCT name FROM users;
-- 为name创建索引
CREATE INDEX idx_name ON users(name);

-- 1. 数据量小的表
-- 小表全表扫描更快

-- 2. 频繁更新的列
-- 索引维护成本高

-- 3. 区分度低的列
SELECT * FROM users WHERE gender = 'male';
-- gender只有两个值，区分度低

-- 4. 很少使用的列
-- 浪费存储空间

-- 组合索引设计原则
-- 1. 最常用的列放在最左边
-- 2. 区分度高的列放在左边
-- 3. 范围查询的列放在最后

-- 示例：用户查询场景
-- 查询1：WHERE status = 1 AND age > 20
-- 查询2：WHERE status = 1 AND name = 'John'

-- 推荐索引
CREATE INDEX idx_status_name_age ON users(status, name, age);
-- status放在最左边（最常用）
-- name放在中间（等值查询）
-- age放在最后（范围查询）

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 重要字段说明
-- id：查询标识符
-- select_type：查询类型
-- table：表名
-- type：访问类型（重要）
-- possible_keys：可能使用的索引
-- key：实际使用的索引
-- key_len：使用的索引长度
-- rows：预估扫描行数
-- Extra：额外信息

-- type类型（从好到差）
-- system > const > eq_ref > ref > range > index > ALL

-- 查看索引使用统计
SELECT 
    index_name,
    rows_read,
    rows_inserted,
    rows_updated,
    rows_deleted
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND object_name = 'users';

-- 查找未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
    AND count_star = 0
    AND object_schema = 'mydb';

-- 1. 使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 优化
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone是VARCHAR
-- 优化
SELECT * FROM users WHERE phone = '13800138000';

-- 3. LIKE以%开头
SELECT * FROM users WHERE name LIKE '%John%';
-- 优化
SELECT * FROM users WHERE name LIKE 'John%';

-- 4. OR条件
SELECT * FROM users WHERE name = 'John' OR age = 25;
-- 优化：使用UNION
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE age = 25;

-- 5. 不等于操作
SELECT * FROM users WHERE status != 1;
-- 可能导致全表扫描

-- 6. IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
-- 可能导致全表扫描

-- 7. 组合索引不满足最左前缀
-- 索引：idx_name_age(name, age)
SELECT * FROM users WHERE age = 25;  -- 不使用索引
SELECT * FROM users WHERE name = 'John';  -- 使用索引

-- 强制使用索引
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'John';

-- 忽略索引
SELECT * FROM users IGNORE INDEX (idx_name) WHERE name = 'John';

-- 使用索引
SELECT * FROM users USE INDEX (idx_name) WHERE name = 'John';

-- 查看表索引大小
SELECT 
    table_name,
    index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb'
    AND stat_name = 'size'
ORDER BY size_mb DESC;

-- 查看索引基数（区分度）
SHOW INDEX FROM users;

-- Cardinality越高，区分度越好
-- 可以通过ANALYZE TABLE更新统计信息
ANALYZE TABLE users;

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

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

-- 分析慢查询
-- 使用mysqldumpslow工具
-- mysqldumpslow -s t /var/log/mysql/slow.log

-- 主键索引：pk_表名
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- 唯一索引：uk_表名_字段名
CREATE UNIQUE INDEX uk_users_email ON users(email);

-- 普通索引：idx_表名_字段名
CREATE INDEX idx_users_name ON users(name);

-- 组合索引：idx_表名_字段1_字段2
CREATE INDEX idx_users_name_age ON users(name, age);

-- 全文索引：ft_表名_字段名
CREATE FULLTEXT INDEX ft_articles_content ON articles(content);

-- 定期分析表
ANALYZE TABLE users;

-- 定期优化表
OPTIMIZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 重建表
ALTER TABLE users ENGINE=InnoDB;