MariaDB索引 #

索引类比
├── 书籍目录：快速定位内容
├── 图书馆索引：快速找到书籍
└── 数据库索引：快速找到数据

索引特点
├── 提高查询速度
├── 降低磁盘IO
├── 占用存储空间
└── 降低写入速度

MariaDB索引类型
├── 按结构分类
│   ├── B+Tree索引（默认）
│   ├── Hash索引（Memory引擎）
│   └── 全文索引（FULLTEXT）
├── 按功能分类
│   ├── 普通索引（INDEX）
│   ├── 唯一索引（UNIQUE）
│   ├── 主键索引（PRIMARY KEY）
│   └── 全文索引（FULLTEXT）
└── 按列数分类
    ├── 单列索引
    └── 复合索引

-- 创建主键索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE,  -- 唯一索引
    INDEX idx_name (name)       -- 普通索引
);

-- 创建复合索引
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    created_at DATETIME,
    INDEX idx_user_status (user_id, status)
);

-- 创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content)
);

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

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

-- 创建复合索引
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 创建前缀索引
CREATE INDEX idx_name_prefix ON users(name(10));

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

-- 添加索引
ALTER TABLE users ADD INDEX idx_name (name);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

-- 主键索引特点：
-- 1. 唯一且非空
-- 2. 每个表只能有一个
-- 3. InnoDB的聚簇索引

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 唯一索引特点：
-- 1. 列值必须唯一
-- 2. 允许NULL值（可以有多个NULL）

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 或
CREATE UNIQUE INDEX idx_email ON users(email);

-- 复合唯一索引
CREATE UNIQUE INDEX idx_user_product ON user_favorites(user_id, product_id);

-- 普通索引特点：
-- 1. 最基本的索引类型
-- 2. 无唯一性约束

CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_created_at ON orders(created_at);

-- 复合索引特点：
-- 1. 多列组成
-- 2. 遵循最左前缀原则

CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 有效使用：
SELECT * FROM orders WHERE user_id = 1;                    -- 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid'; -- 使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01'; -- 使用索引

-- 无效使用：
SELECT * FROM orders WHERE status = 'paid';                -- 不使用索引
SELECT * FROM orders WHERE created_at > '2024-01-01';      -- 不使用索引
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2024-01-01'; -- 不使用索引

-- 前缀索引：只索引列的前N个字符
-- 适用于长字符串列

CREATE INDEX idx_email_prefix ON users(email(10));

-- 选择前缀长度
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS selectivity_5,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS selectivity_10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS selectivity_15,
    COUNT(DISTINCT email) / COUNT(*) AS full_selectivity
FROM users;

-- 选择接近完整选择性的最短前缀

-- 全文索引：用于文本搜索
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
);

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database');

-- 布尔模式
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+database +mysql' IN BOOLEAN MODE);

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

-- 查询扩展
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

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

-- 结果说明
-- Table: 表名
-- Non_unique: 是否非唯一（0=唯一，1=非唯一）
-- Key_name: 索引名
-- Seq_in_index: 索引中列的顺序
-- Column_name: 列名
-- Collation: 排序方式（A=升序）
-- Cardinality: 基数（估计值）
-- Sub_part: 前缀长度
-- Null: 是否允许NULL
-- Index_type: 索引类型

-- 从information_schema查询
SELECT 
    index_name,
    column_name,
    non_unique,
    seq_in_index,
    index_type
FROM information_schema.statistics
WHERE table_name = 'users'
ORDER BY index_name, seq_in_index;

-- 删除索引
DROP INDEX idx_name ON users;

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

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_user;

-- 删除索引
ALTER TABLE users DROP INDEX idx_name;

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

索引设计原则
├── 选择性高的列
│   └── 基数/总行数 接近1
├── WHERE条件列
│   └── 经常用于过滤的列
├── JOIN连接列
│   └── 外键列
├── ORDER BY/GROUP BY列
│   └── 排序和分组列
├── 复合索引顺序
│   └── 高选择性列在前
└── 避免过度索引
    └── 索引有维护成本

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = 'John';

-- 关键字段说明
-- id: 查询标识符
-- select_type: 查询类型
-- table: 表名
-- type: 访问类型（从好到差）
--   system > const > eq_ref > ref > range > index > ALL
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 使用的索引长度
-- ref: 索引比较的列
-- rows: 预估扫描行数
-- Extra: 额外信息
--   Using index: 覆盖索引
--   Using where: WHERE过滤
--   Using filesort: 文件排序
--   Using temporary: 临时表

-- 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. 复合索引不满足最左前缀
-- 索引: (a, b, c)
SELECT * FROM t WHERE b = 1;  -- 索引失效
SELECT * FROM t WHERE a = 1;  -- 使用索引

-- 6. 使用!=或<>
SELECT * FROM users WHERE status != 1;  -- 可能索引失效

-- 7. IS NULL / IS NOT NULL
SELECT * FROM users WHERE name IS NULL;  -- 可能索引失效

-- 覆盖索引：查询的列都在索引中
-- 不需要回表，性能更好

-- 创建索引
CREATE INDEX idx_user_status_name ON users(status, name);

-- 使用覆盖索引
SELECT name FROM users WHERE status = 1;  -- Using index

-- 不使用覆盖索引
SELECT name, email FROM users WHERE status = 1;  -- 需要回表

-- 索引下推（Index Condition Pushdown, ICP）
-- MariaDB 5.3+ 支持

-- 索引: (name, age)
SELECT * FROM users WHERE name LIKE 'J%' AND age > 25;

-- 无ICP：存储引擎只根据name过滤，再由服务器层过滤age
-- 有ICP：存储引擎直接根据name和age过滤

-- 分析表（更新统计信息）
ANALYZE TABLE users;

-- 优化表（重建表和索引）
OPTIMIZE TABLE users;

-- 使用ALTER TABLE重建
ALTER TABLE users ENGINE=InnoDB;

-- 查看索引使用统计
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
ORDER BY count_read DESC;

-- 查找未使用的索引
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';

-- MariaDB 10.3+ 支持函数索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    created_at DATETIME,
    INDEX idx_year ((YEAR(created_at)))
);

-- 使用函数索引
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- MariaDB 10.2+ 虚拟列索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    INDEX idx_full_name (full_name)
);

-- 使用虚拟列索引
SELECT * FROM users WHERE full_name = 'John Doe';

-- 查看索引基数
SHOW INDEX FROM users;

-- 更新统计信息
ANALYZE TABLE users;

-- 查看表统计信息
SELECT 
    table_name,
    table_rows,
    data_length,
    index_length
FROM information_schema.tables
WHERE table_schema = 'mydb';

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 分析慢查询
-- 查看慢查询日志
-- 使用mysqldumpslow分析

索引创建检查
├── 列选择性是否足够高？
├── 是否用于WHERE/JOIN/ORDER BY？
├── 复合索引顺序是否合理？
├── 是否存在冗余索引？
├── 索引长度是否合适？
└── 是否考虑了写入性能影响？