-- 創(chuàng)建索引(name, age, city)
CREATE INDEX idx_user_name_age_city ON user(name, age, city);

-- 以下查詢無法充分利用索引
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';  -- name列缺失，只能全表掃描
SELECT * FROM user WHERE name = 'Tom' AND city = 'Beijing';  -- 中間age列缺失，city無法使用索引

-- 假設(shè)選擇性：city < name < age
CREATE INDEX idx_user_name_age_city ON user(name, age, city);

-- 充分利用索引的查詢
SELECT * FROM user WHERE name = 'Tom' AND age = 25;
SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';

-- 創(chuàng)建普通索引
CREATE INDEX idx_user_name ON user(name);

-- 需要回表查詢
SELECT id, name, age, city FROM user WHERE name = 'Tom';

-- 創(chuàng)建包含所需字段的索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);

-- 使用覆蓋索引，無需回表
SELECT name, age, city FROM user WHERE name = 'Tom';

-- 假設(shè)product_desc是較長(zhǎng)的產(chǎn)品描述文本
CREATE INDEX idx_product_desc ON product(product_desc(50));

-- 計(jì)算不同前綴長(zhǎng)度的選擇性
SELECT 
    COUNT(DISTINCT LEFT(product_desc, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LEFT(product_desc, 20)) / COUNT(*) AS sel_20,
    COUNT(DISTINCT LEFT(product_desc, 30)) / COUNT(*) AS sel_30,
    COUNT(DISTINCT LEFT(product_desc, 40)) / COUNT(*) AS sel_40,
    COUNT(DISTINCT LEFT(product_desc, 50)) / COUNT(*) AS sel_50,
    COUNT(DISTINCT product_desc) / COUNT(*) AS sel_full
FROM product;

-- 單獨(dú)創(chuàng)建兩個(gè)索引
CREATE INDEX idx_user_age ON user(age);
CREATE INDEX idx_user_city ON user(city);

-- MySQL通常只會(huì)選擇一個(gè)索引
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';

-- 創(chuàng)建一個(gè)復(fù)合索引
CREATE INDEX idx_user_age_city ON user(age, city);

-- 可以同時(shí)使用age和city條件
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';

-- 可以使用索引
SELECT * FROM products WHERE product_name LIKE 'iphone%';

-- 無法使用索引
SELECT * FROM products WHERE product_name LIKE '%iphone%';

-- 添加一個(gè)反轉(zhuǎn)字段
ALTER TABLE products ADD product_name_reversed VARCHAR(255);

-- 觸發(fā)器維護(hù)反轉(zhuǎn)值, 此處為了簡(jiǎn)單表示整體實(shí)現(xiàn)思路, 實(shí)際通常在代碼中進(jìn)行反轉(zhuǎn)值賦值
DELIMITER //
CREATE TRIGGER product_insert BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SET NEW.product_name_reversed = REVERSE(NEW.product_name);
END; //
DELIMITER ;

-- 創(chuàng)建反轉(zhuǎn)字段的索引
CREATE INDEX idx_product_name_rev ON products(product_name_reversed);

-- 搜索以'phone'結(jié)尾的產(chǎn)品
SELECT * FROM products 
WHERE product_name_reversed LIKE CONCAT(REVERSE('phone'), '%');

-- 索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

-- 可以使用索引
SELECT * FROM orders 
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

-- user_id是varchar類型，但使用了整數(shù)條件
CREATE INDEX idx_user_id ON users(user_id);
SELECT * FROM users WHERE user_id = 12345;  -- 索引可能失效

-- 確保條件值類型與字段類型一致
SELECT * FROM users WHERE user_id = '12345';  -- 使用字符串類型

-- 優(yōu)化前：無法充分利用索引
SELECT * FROM products WHERE category_id != 5;

-- 優(yōu)化后：可以使用索引
SELECT * FROM products WHERE category_id < 5 OR category_id > 5;

-- 性能較差的分頁(yè)查詢
SELECT * FROM products ORDER BY id LIMIT 100000, 10;

-- 先獲取ID，再關(guān)聯(lián)查詢完整數(shù)據(jù)
SELECT p.* FROM products p
JOIN (
    SELECT id FROM products ORDER BY id LIMIT 100000, 10
) tmp ON p.id = tmp.id;

-- 假設(shè)已知上一頁(yè)的最大ID是100233
SELECT * FROM products WHERE id > 100233 ORDER BY id LIMIT 10;

-- 可能導(dǎo)致不必要的開銷
SELECT * FROM users WHERE name = 'Tom';

-- 只返回需要的列，可能利用覆蓋索引
SELECT id, name, email FROM users WHERE name = 'Tom';

EXPLAIN SELECT * FROM users WHERE name = 'Tom' AND age > 20;

-- WHERE和ORDER BY使用不同的列，可能導(dǎo)致文件排序
CREATE INDEX idx_user_name ON users(name);
SELECT * FROM users WHERE name = 'Tom' ORDER BY age;

-- 創(chuàng)建聯(lián)合索引同時(shí)包含WHERE和ORDER BY的列
CREATE INDEX idx_user_name_age ON users(name, age);
SELECT * FROM users WHERE name = 'Tom' ORDER BY age;

-- 在低峰期執(zhí)行索引創(chuàng)建
CREATE INDEX idx_order_status ON orders(status);

-- 使用ALGORITHM和LOCK選項(xiàng)
CREATE INDEX idx_order_status ON orders(status)
ALGORITHM=INPLACE, LOCK=NONE;

pt-online-schema-change --alter "ADD INDEX idx_order_status (status)" \
--host=localhost --user=root --ask-pass --database=mydb --table=orders \
--execute

-- 添加虛擬列存儲(chǔ)計(jì)算結(jié)果
ALTER TABLE products 
ADD total_value DECIMAL(10,2) AS (price * quantity) VIRTUAL;

-- 在虛擬列上創(chuàng)建索引
CREATE INDEX idx_total_value ON products(total_value);

-- 使用計(jì)算列進(jìn)行查詢
SELECT * FROM products WHERE total_value > 10000;

-- 添加哈希列
ALTER TABLE users ADD name_hash INT UNSIGNED 
GENERATED ALWAYS AS (crc32(name)) STORED;

-- 在哈希列上創(chuàng)建索引
CREATE INDEX idx_name_hash ON users(name_hash);

-- 使用哈希索引查詢
SELECT * FROM users 
WHERE name_hash = crc32('Tom') AND name = 'Tom';

-- 分析表
ANALYZE TABLE orders;

-- 優(yōu)化表
OPTIMIZE TABLE orders;

-- 或者重建索引
ALTER TABLE orders DROP INDEX idx_status, ADD INDEX idx_status(status);

-- 查找未使用的索引
SELECT * FROM schema_unused_indexes;  -- Performance Schema

-- 查找重復(fù)的索引
SELECT * FROM sys.schema_redundant_indexes;  -- Sys Schema

-- 創(chuàng)建混合排序方向的索引(MySQL 8.0+)
CREATE INDEX idx_user_age_score ON users(age ASC, score DESC);

-- 可以高效執(zhí)行的查詢
SELECT * FROM users ORDER BY age ASC, score DESC;

-- 只為活躍用戶創(chuàng)建索引(MySQL 8.0+)
CREATE INDEX idx_active_users ON users(name, email) 
WHERE status = 'active';

-- 查看表的統(tǒng)計(jì)信息
SHOW TABLE STATUS LIKE 'users';

-- 查看索引的基數(shù)
SHOW INDEX FROM users;

-- 刷新統(tǒng)計(jì)信息
ANALYZE TABLE users;

-- 強(qiáng)制使用特定索引
SELECT * FROM users FORCE INDEX(idx_name_age) 
WHERE name = 'Tom' AND age > 20;

-- 忽略特定索引
SELECT * FROM users IGNORE INDEX(idx_status) 
WHERE status = 'active' AND age > 20;