-- 查看字段選擇性
SELECT 
  COUNT(DISTINCT gender)/COUNT(*) AS gender_selectivity,
  COUNT(DISTINCT phone)/COUNT(*) AS phone_selectivity
FROM users;

-- 好的索引字段
ALTER TABLE products ADD INDEX idx_category_id (category_id);  -- 整型
ALTER TABLE users ADD INDEX idx_phone (phone);  -- 定長字符串

-- 較差的索引選擇
ALTER TABLE logs ADD INDEX idx_content (content(255));  -- 長文本前綴索引

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 有效使用索引
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

-- 部分失效（只用到了user_id）
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

-- 完全失效（跳過了user_id）
EXPLAIN SELECT * FROM orders WHERE status = 1;

-- 索引失效
EXPLAIN SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';
EXPLAIN SELECT * FROM products WHERE price + 100 > 500;

-- 優(yōu)化方案
EXPLAIN SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

-- phone字段是varchar類型
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;  -- 失效
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';  -- 有效

-- 枚舉值比較
EXPLAIN SELECT * FROM orders WHERE status = '1';  -- 可能失效
EXPLAIN SELECT * FROM orders WHERE status = 1;    -- 有效

-- 索引失效
EXPLAIN SELECT * FROM users WHERE age != 30;

-- 優(yōu)化方案（范圍查詢+union）
EXPLAIN SELECT * FROM users WHERE age < 30 
UNION ALL 
SELECT * FROM users WHERE age > 30;

-- 索引失效
EXPLAIN SELECT * FROM users WHERE name LIKE '%張%';

-- 索引有效（前綴匹配）
EXPLAIN SELECT * FROM users WHERE name LIKE '張%';

-- 特殊優(yōu)化方案（全文索引）
ALTER TABLE users ADD FULLTEXT INDEX ft_idx_name (name);
EXPLAIN SELECT * FROM users WHERE MATCH(name) AGAINST('張*' IN BOOLEAN MODE);

-- 索引失效（其中一個(gè)條件無索引）
EXPLAIN SELECT * FROM users WHERE user_id = 1001 OR register_ip = '192.168.1.1';

-- 優(yōu)化方案
EXPLAIN SELECT * FROM users WHERE user_id = 1001
UNION ALL
SELECT * FROM users WHERE register_ip = '192.168.1.1' AND user_id != 1001;

-- 當(dāng)status=1占90%數(shù)據(jù)時(shí)
EXPLAIN SELECT * FROM orders WHERE status = 1;  -- 可能全表掃描

-- 查看數(shù)據(jù)分布
SELECT status, COUNT(*) FROM orders GROUP BY status;

-- MySQL 5.7+可以走索引
EXPLAIN SELECT * FROM users WHERE phone IS NULL;

-- 通常需要結(jié)合其他條件
EXPLAIN SELECT * FROM users WHERE phone IS NULL AND create_time > '2023-01-01';

-- 可能不如預(yù)期高效
EXPLAIN SELECT * FROM users 
WHERE username = 'admin' OR email = 'admin@example.com';

-- 優(yōu)化方案
CREATE INDEX idx_username_email ON users(username, email);

-- 只有user_id和status能用索引，age失效
EXPLAIN SELECT * FROM orders 
WHERE user_id = 1001 AND status > 0 AND age = 30;

-- 優(yōu)化索引順序
ALTER TABLE orders ADD INDEX idx_user_age_status (user_id, age, status);

-- 不同字符集比較導(dǎo)致失效
EXPLAIN SELECT * FROM users u JOIN logs l 
ON u.username = l.operator 
WHERE u.charset = 'utf8mb4' AND l.charset = 'latin1';

-- 原始查詢
SELECT product_id, product_name, price 
FROM products 
WHERE category_id = 5 
AND status = 1 
AND stock > 0 
ORDER BY sales_volume DESC 
LIMIT 20;

-- 優(yōu)化方案
ALTER TABLE products ADD INDEX idx_cat_status_stock_sales 
(category_id, status, stock, sales_volume DESC);

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 索引統(tǒng)計(jì)信息
SHOW INDEX FROM products;

-- 查詢性能分析
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

ALTER TABLE orders ADD INDEX idx_create_time (create_time DESC);

-- 測試刪除索引的影響
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

-- 對JSON字段建立索引
ALTER TABLE products ADD INDEX idx_price_data ((CAST(price_data->'$.price' AS DECIMAL(10,2))));