-- 示例表結(jié)構(gòu)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    created_at DATETIME,
    INDEX idx_age_city_name (age, city, name)  -- 復合索引
);
-- 情況1：需要回表
SELECT * FROM users WHERE age = 25;
-- 雖然用到了索引，但SELECT *需要回表獲取所有字段
-- 情況2：覆蓋索引（無需回表）
SELECT age, city, name FROM users WHERE age = 25;
-- 所有需要的字段都在索引中，無需訪問數(shù)據(jù)行

1. 通過索引找到符合條件的行主鍵
2. 使用主鍵回表查詢數(shù)據(jù)行
3. 從數(shù)據(jù)行中取出需要的字段

1. 通過索引找到符合條件的索引記錄
2. 直接從索引記錄中取出需要的字段
3. 無需回表！

EXPLAIN SELECT age, city FROM users WHERE age = 25;

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_covering ON orders(user_id, status, amount, created_at);
-- 查詢：所有需要的字段都在索引中
SELECT user_id, status, amount 
FROM orders 
WHERE user_id = 100 AND status = 'paid';
-- ? 完全覆蓋，Extra: Using index

-- 假設索引：idx_name_age_city (name, age, city)
SELECT name, age FROM users WHERE name LIKE '張%';
-- ? 覆蓋索引：只查詢索引包含的字段
SELECT name, age, email FROM users WHERE name LIKE '張%';
-- ? 需要回表：email不在索引中

-- 沒有覆蓋索引：0.5秒
SELECT user_id, product_name 
FROM orders 
WHERE user_id = 1000;
-- 添加覆蓋索引后：0.02秒
CREATE INDEX idx_user_product ON orders(user_id, product_name);
SELECT user_id, product_name 
FROM orders 
WHERE user_id = 1000;  -- Extra: Using index

-- 索引：idx_a_b_c (a, b, c)
-- 覆蓋索引：?
SELECT a, b FROM users WHERE a = 1;
-- 覆蓋索引：?（c在WHERE中）
SELECT a, b FROM users WHERE a = 1 AND c = 2;
-- 覆蓋索引：?（缺少a，索引失效）
SELECT b, c FROM users WHERE b = 2;

-- 對于TEXT/BLOB字段，即使包含在索引中也可能需要回表
CREATE INDEX idx_content ON articles(title, content(100));
SELECT title, content FROM articles WHERE title = 'MySQL';
-- 可能無法完全覆蓋，取決于存儲引擎和配置

-- InnoDB二級索引自動包含主鍵
CREATE INDEX idx_age ON users(age);
-- 實際上存儲的是: (age, id)
-- 這些查詢可以使用覆蓋索引：
SELECT id FROM users WHERE age = 25;            -- ?
SELECT age, id FROM users WHERE age = 25;       -- ?
SELECT age FROM users WHERE age = 25;           -- ?

-- 根據(jù)查詢模式設計索引
-- 常見查詢：
SELECT user_id, order_date, total 
FROM orders 
WHERE user_id = ? AND status = 'completed' 
ORDER BY order_date DESC;
-- 最佳索引：
CREATE INDEX idx_user_status_date_total 
ON orders(user_id, status, order_date DESC, total);
-- 完全覆蓋查詢需求

-- 低效的分頁（需要回表）
SELECT * FROM users ORDER BY created_at LIMIT 100000, 20;
-- 高效的分頁（使用覆蓋索引）
SELECT id FROM users ORDER BY created_at LIMIT 100000, 20;
-- 先通過覆蓋索引獲取id，再關(guān)聯(lián)查詢
SELECT u.* FROM users u
JOIN (
    SELECT id FROM users ORDER BY created_at LIMIT 100000, 20
) AS tmp ON u.id = tmp.id;

-- 查看索引使用統(tǒng)計
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    ROWS_READ,
    ROWS_INSERTED,
    ROWS_UPDATED,
    ROWS_DELETED
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database';

-- 測試覆蓋索引效果
SET profiling = 1;
-- 查詢1：需要回表
SELECT * FROM large_table WHERE category = 'electronics';
-- 查詢2：使用覆蓋索引
SELECT id, category, name FROM large_table WHERE category = 'electronics';
SHOW PROFILES;
-- 通常覆蓋索引查詢速度快2-10倍