使用AI工具优化MySQL索引：从全表扫描到毫秒响应

-- 典型的多条件查询
SELECT 
    order_id, 
    user_id, 
    order_date, 
    total_amount, 
    status,
    product_category
FROM orders 
WHERE 
    user_id = 12345 
    AND status IN ('shipped', 'delivered')
    AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
    AND product_category = 'electronics'
ORDER BY order_date DESC 
LIMIT 50;

-- 表结构
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'shipped', 'delivered', 'cancelled') NOT NULL,
    product_category VARCHAR(50) NOT NULL,
    -- 其他字段...
    KEY idx_user_id (user_id),
    KEY idx_order_date (order_date)
) ENGINE=InnoDB;

-- 临时启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 捕获超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 特定查询分析
pt-query-digest --filter '$event->{fingerprint} =~ /SELECT.*orders/' slow.log

-- 删除效率低下的单列索引
DROP INDEX idx_user_id ON orders;
DROP INDEX idx_order_date ON orders;

-- 创建针对查询模式的复合索引
ALTER TABLE orders ADD INDEX idx_orders_optimized (
    user_id,              -- 高选择性字段放在前面
    product_category,     -- 中等选择性字段
    status,               -- 低选择性字段
    order_date            -- 排序字段放在最后
);

-- 创建覆盖索引避免回表
ALTER TABLE orders ADD INDEX idx_orders_covering (
    user_id,
    product_category,
    status,
    order_date,
    total_amount          -- 包含在索引中避免回表
);

-- 分析各条件的基数分布
SELECT 
    status, 
    COUNT(*) as count 
FROM orders 
GROUP BY status;

SELECT 
    product_category, 
    COUNT(*) as count 
FROM orders 
GROUP BY product_category;

-- 分析user_id的分布
SELECT 
    COUNT(DISTINCT user_id) as distinct_users,
    COUNT(*) as total_orders
FROM orders;

EXPLAIN SELECT ... -- 显示type: ALL, rows: 5000000

EXPLAIN SELECT ... -- 显示type: ref, rows: 50, Extra: Using index

-- 测试查询性能
SET PROFILING = 1;
SELECT ...; -- 原始查询
SHOW PROFILE;

-- 比较执行时间
SELECT 
    (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 1) as original_time,
    (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = 2) as optimized_time;

-- 监控索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME = 'orders';

# 使用pt-online-schema-change进行无锁索引变更
pt-online-schema-change \
    --alter "ADD INDEX idx_new_optimized (user_id, product_category, status)" \
    D=database,t=orders \
    --execute