-- 創(chuàng)建一個示例表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_age_created (age, created_at)
);

-- 沒有索引的查詢（全表掃描）
SELECT * FROM users WHERE username = 'john_doe';
-- 有索引的查詢（索引查找）
-- 執(zhí)行計劃會顯示使用 idx_username 索引
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

-- 創(chuàng)建表時定義主鍵
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2)
);
-- 為已存在的表添加主鍵
ALTER TABLE products ADD PRIMARY KEY (product_id);

-- 創(chuàng)建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);
-- 或者在創(chuàng)建表時定義
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 創(chuàng)建普通索引
CREATE INDEX idx_username ON users (username);
CREATE INDEX idx_age ON users (age);
-- 查看索引使用情況
EXPLAIN SELECT * FROM users WHERE username = 'alice' AND age > 25;

-- 創(chuàng)建復合索引
CREATE INDEX idx_name_age_city ON users (username, age, city);
-- 以下查詢可以使用該索引
SELECT * FROM users WHERE username = 'bob';
SELECT * FROM users WHERE username = 'bob' AND age = 30;
SELECT * FROM users WHERE username = 'bob' AND age = 30 AND city = 'Beijing';
-- 以下查詢無法使用該索引
SELECT * FROM users WHERE age = 30;  -- 跳過了最左列
SELECT * FROM users WHERE city = 'Beijing';  -- 跳過了最左列

-- 創(chuàng)建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
);
-- 使用全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 索引優(yōu)化' IN NATURAL LANGUAGE MODE);

-- 演示 B+ 樹索引的范圍查詢優(yōu)勢
SELECT * FROM users WHERE age BETWEEN 25 AND 35 ORDER BY age;
-- 由于 B+ 樹的有序性，這個查詢非常高效

-- 創(chuàng)建測試表演示聚簇索引和非聚簇索引
CREATE TABLE orders (
    order_id INT PRIMARY KEY,  -- 聚簇索引
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    INDEX idx_customer (customer_id),  -- 非聚簇索引
    INDEX idx_date (order_date)        -- 非聚簇索引
);
-- 通過主鍵查詢（使用聚簇索引）
SELECT * FROM orders WHERE order_id = 1001;
-- 通過非聚簇索引查詢（需要回表）
SELECT * FROM orders WHERE customer_id = 123;

-- 查看表的索引信息
SHOW INDEX FROM users;
-- 查看索引的存儲統(tǒng)計信息
SELECT 
    table_name,
    index_name,
    stat_name,
    stat_value
FROM mysql.innodb_index_stats 
WHERE table_name = 'users';

-- 基本語法
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (column1, column2, ...);
-- 實際示例
CREATE INDEX idx_user_status ON users (status);
CREATE UNIQUE INDEX idx_user_phone ON users (phone_number);
CREATE INDEX idx_order_date_status ON orders (order_date, status);
-- 使用 ALTER TABLE 創(chuàng)建索引
ALTER TABLE users ADD INDEX idx_created_at (created_at);
ALTER TABLE users ADD UNIQUE INDEX idx_username_email (username, email);

-- 查看表的所有索引
SHOW INDEX FROM users;
-- 查看索引使用統(tǒng)計
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database' AND object_name = 'users';

-- 刪除索引
DROP INDEX idx_username ON users;
-- 使用 ALTER TABLE 刪除索引
ALTER TABLE users DROP INDEX idx_age;
-- 刪除主鍵（需要先刪除 AUTO_INCREMENT 屬性）
ALTER TABLE users MODIFY id INT;
ALTER TABLE users DROP PRIMARY KEY;

-- MySQL 不支持直接修改索引，需要先刪除再創(chuàng)建
DROP INDEX idx_old_name ON users;
CREATE INDEX idx_new_name ON users (username, email);
-- 或者使用 ALTER TABLE
ALTER TABLE users 
DROP INDEX idx_old_name,
ADD INDEX idx_new_name (username, email);

-- 計算列的選擇性
SELECT 
    COUNT(DISTINCT username) / COUNT(*) as username_selectivity,
    COUNT(DISTINCT email) / COUNT(*) as email_selectivity,
    COUNT(DISTINCT age) / COUNT(*) as age_selectivity
FROM users;
-- 分析最適合創(chuàng)建索引的列
SELECT 
    column_name,
    cardinality,
    cardinality / table_rows as selectivity
FROM information_schema.statistics s
JOIN information_schema.tables t ON s.table_name = t.table_name
WHERE s.table_schema = 'your_database' AND s.table_name = 'users';

-- 分析查詢執(zhí)行計劃
EXPLAIN SELECT * FROM users WHERE username = 'john' AND age > 25;
-- 詳細的執(zhí)行計劃分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'john' AND age > 25;
-- 實際執(zhí)行統(tǒng)計
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john' AND age > 25;

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_user_cover ON users (username, email, age);
-- 以下查詢可以使用覆蓋索引，避免回表
SELECT username, email, age FROM users WHERE username = 'alice';
-- 查看是否使用了覆蓋索引
EXPLAIN SELECT username, email, age FROM users WHERE username = 'alice';
-- Extra 列會顯示 "Using index"

-- 索引失效的常見情況
-- 1. 使用函數(shù)或表達式
-- 錯誤：索引失效
SELECT * FROM users WHERE UPPER(username) = 'JOHN';
-- 正確：使用索引
SELECT * FROM users WHERE username = 'john';
-- 2. 使用 LIKE 以通配符開頭
-- 錯誤：索引失效
SELECT * FROM users WHERE username LIKE '%john%';
-- 正確：使用索引
SELECT * FROM users WHERE username LIKE 'john%';
-- 3. 使用 OR 連接不同列
-- 錯誤：可能索引失效
SELECT * FROM users WHERE username = 'john' OR age = 25;
-- 正確：使用 UNION
SELECT * FROM users WHERE username = 'john'
UNION
SELECT * FROM users WHERE age = 25;
-- 4. 數(shù)據(jù)類型不匹配
-- 錯誤：索引失效
SELECT * FROM users WHERE age = '25';  -- age 是 INT 類型
-- 正確：使用索引
SELECT * FROM users WHERE age = 25;

-- 好的索引設計示例
CREATE TABLE user_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled'),
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    -- 基于查詢模式設計的復合索引
    INDEX idx_user_status_date (user_id, order_status, order_date),
    -- 覆蓋索引，避免回表
    INDEX idx_status_date_amount (order_status, order_date, total_amount)
);

-- 陷阱1：過多的單列索引
-- 錯誤做法
CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_status ON orders (order_status);
CREATE INDEX idx_date ON orders (order_date);
-- 正確做法：根據(jù)查詢模式創(chuàng)建復合索引
CREATE INDEX idx_user_status_date ON orders (user_id, order_status, order_date);
-- 陷阱2：重復索引
-- 錯誤：創(chuàng)建了重復的索引
CREATE INDEX idx_username ON users (username);
CREATE INDEX idx_username_duplicate ON users (username);  -- 重復索引
-- 陷阱3：無用的索引
-- 檢查從未使用的索引
SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE count_read = 0 AND count_write = 0 AND count_fetch = 0;

-- 定期分析表和索引統(tǒng)計信息
ANALYZE TABLE users;
-- 檢查索引碎片
SELECT 
    table_schema,
    table_name,
    data_length,
    index_length,
    data_free,
    (data_free / (data_length + index_length)) * 100 as fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'your_database';
-- 重建索引（減少碎片）
ALTER TABLE users ENGINE=InnoDB;
-- 監(jiān)控索引使用情況
SELECT 
    object_name,
    index_name,
    count_read,
    count_write,
    count_fetch / count_read as fetch_ratio
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_read DESC;