#索引优化策略 - MySQL与PostgreSQL索引设计与优化指南

-- 普通索引
CREATE INDEX idx_name ON employees(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

-- 主键索引（自动创建）
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 复合索引
CREATE INDEX idx_name_dept_salary ON employees(name, department_id, salary);

-- 创建全文索引（仅InnoDB和MyISAM支持）
CREATE FULLTEXT INDEX idx_fulltext_desc ON products(description);

-- 使用全文搜索
SELECT * FROM products 
WHERE MATCH(description) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

-- 布尔模式搜索
SELECT * FROM products 
WHERE MATCH(description) AGAINST('+database -mysql +optimization' IN BOOLEAN MODE);

-- 精确短语搜索
SELECT * FROM products 
WHERE MATCH(description) AGAINST('"database optimization"' IN BOOLEAN MODE);

-- 空间数据类型和索引（仅MyISAM支持，MySQL 5.7.5+ InnoDB也支持）
CREATE TABLE locations (
    id INT PRIMARY KEY,
    location GEOMETRY NOT NULL,
    SPATIAL INDEX(location)
);

-- 插入空间数据
INSERT INTO locations VALUES (
    1, 
    GeomFromText('POINT(10 20)')
);

-- 空间查询
SELECT * FROM locations 
WHERE MBRContains(
    GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), 
    location
);

-- 复合索引创建
CREATE INDEX idx_composite ON orders(customer_id, order_date, status);

-- 有效的查询（遵循最左前缀原则）
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01';
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2024-01-01' AND status = 'completed';

-- 无效的查询（不遵循最左前缀原则）
-- SELECT * FROM orders WHERE order_date = '2024-01-01'; -- 不会使用索引
-- SELECT * FROM orders WHERE status = 'completed'; -- 不会使用索引

-- 优化复合索引顺序
-- 将选择性高的列放在前面
CREATE INDEX idx_optimized ON employees(department_id, hire_date, salary);
-- department_id选择性高，放在最前面

-- 创建不可见索引
CREATE INDEX idx_invisible ON employees(hire_date) INVISIBLE;

-- 临时启用不可见索引
ALTER INDEX idx_invisible ON employees VISIBLE;
-- 临时禁用索引
ALTER INDEX idx_invisible ON employees INVISIBLE;

-- 查看索引可见性
SELECT index_name, is_visible 
FROM information_schema.statistics 
WHERE table_name = 'employees';

-- 基于表达式的索引
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 使用函数索引的查询
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';
-- 会使用idx_lower_email索引

-- 日期函数索引
CREATE INDEX idx_year_created ON orders((YEAR(order_date)));
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 普通索引
CREATE INDEX idx_name ON employees(name);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

-- 复合索引
CREATE INDEX idx_name_dept_salary ON employees(name, department_id, salary);

-- 创建Hash索引（适用于等值查询）
CREATE INDEX idx_hash_employee_id ON employees USING hash(employee_id);

-- Hash索引适用于等值查询
SELECT * FROM employees WHERE employee_id = 123;
-- 会使用Hash索引，查询速度快

-- GiST索引适用于范围查询和几何数据
CREATE INDEX idx_gist_range ON reservations USING gist(room_range);

-- 适用于几何数据
CREATE INDEX idx_gist_geom ON locations USING gist(location);

-- 适用于全文搜索
CREATE INDEX idx_gist_tsv ON documents USING gist(search_vector);

-- GIN索引适用于数组和JSONB数据
CREATE INDEX idx_gin_array ON products USING gin(tags);

-- GIN索引适用于JSONB
CREATE INDEX idx_gin_jsonb ON user_profiles USING gin(profile_data);

-- 使用示例
SELECT * FROM products WHERE tags @> ARRAY['electronics', 'mobile'];
SELECT * FROM user_profiles WHERE profile_data @> '{"preferences": {"theme": "dark"}}';

-- BRIN索引适用于大表，占用空间少
CREATE INDEX idx_brin_date ON large_table USING brin(created_date);

-- BRIN索引适用于有序数据的大表
-- 适用于时间戳、序列号等递增数据

-- 只为特定条件创建索引
CREATE INDEX idx_active_employees ON employees(employee_id) 
WHERE status = 'active';

-- 只为高薪员工创建索引
CREATE INDEX idx_high_salary ON employees(salary) 
WHERE salary > 100000;

-- 使用部分索引的查询
SELECT * FROM employees WHERE status = 'active' AND employee_id = 123;
-- 会使用idx_active_employees索引

-- 基于表达式的索引
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- 日期表达式索引
CREATE INDEX idx_order_year ON orders(EXTRACT(year FROM order_date));

-- 使用表达式索引
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';
SELECT * FROM orders WHERE EXTRACT(year FROM order_date) = 2024;

-- PostgreSQL 11+的覆盖索引功能
CREATE INDEX idx_covering ON employees(department_id, salary) 
INCLUDE (first_name, last_name);

-- 查询可以完全从索引中获取数据，无需回表
SELECT first_name, last_name FROM employees 
WHERE department_id = 1 AND salary > 50000;

-- 选择性分析
-- 高选择性列（推荐建立索引）
SELECT 
    COUNT(DISTINCT email) * 100.0 / COUNT(*) AS email_selectivity,
    COUNT(DISTINCT department_id) * 100.0 / COUNT(*) AS dept_selectivity,
    COUNT(DISTINCT status) * 100.0 / COUNT(*) AS status_selectivity
FROM employees;

-- 一般认为选择性 > 10% 的列适合建立索引
-- email: 100% (适合)
-- department_id: 5% (不适合，除非查询频繁)
-- status: 2% (不适合，除非查询频繁)

-- 频繁查询的列
-- 在WHERE子句中频繁出现的列
CREATE INDEX idx_frequently_queried ON orders(status, customer_id);

-- 在JOIN条件中使用的列
CREATE INDEX idx_join_columns ON orders(customer_id);
CREATE INDEX idx_join_columns_customers ON customers(id);

-- 正确的复合索引顺序
-- 按选择性高低排序
CREATE INDEX idx_composite_optimal ON orders(
    customer_id,    -- 高选择性
    order_date,     -- 中等选择性
    status          -- 低选择性
);

-- 频繁查询模式
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2024-01-01';
SELECT * FROM orders WHERE customer_id = 123 AND status = 'completed';
SELECT * FROM orders WHERE customer_id = 123;

-- 错误的顺序示例
CREATE INDEX idx_bad_order ON orders(status, order_date, customer_id);
-- 只有当WHERE条件包含status时才会使用此索引

-- 排序字段考虑
CREATE INDEX idx_for_ordering ON sales(salesperson_id, sale_date DESC);
SELECT * FROM sales WHERE salesperson_id = 1 ORDER BY sale_date DESC;

-- MySQL索引维护
-- 分析表统计信息
ANALYZE TABLE employees;

-- 检查索引使用情况
SHOW INDEX FROM employees;

-- 重建索引（碎片整理）
OPTIMIZE TABLE employees;

-- MySQL 8.0+ 查看索引使用统计
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE object_name = 'employees';

-- PostgreSQL索引维护
-- 更新统计信息
ANALYZE employees;

-- 查看表和索引统计
SELECT schemaname, tablename, attname, inherited, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'employees';

-- 重建索引
REINDEX INDEX idx_name;

-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'employees';

-- 查看表扫描统计
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE tablename = 'employees';

-- 基本EXPLAIN
EXPLAIN SELECT * FROM employees WHERE department_id = 1;

-- 详细分析（MySQL 5.6+）
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department_id = 1;

-- 性能分析（MySQL 5.6+）
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;

-- EXPLAIN输出解读
-- id: 查询序列号
-- select_type: 查询类型
-- table: 表名
-- type: 连接类型（system > const > eq_ref > ref > range > index > ALL）
-- possible_keys: 可能使用的索引
-- key: 实际使用的索引
-- key_len: 使用的索引长度
-- ref: 索引比较的列
-- rows: 扫描的行数
-- Extra: 额外信息

-- 基本EXPLAIN
EXPLAIN SELECT * FROM employees WHERE department_id = 1;

-- 详细分析
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;

-- 包含缓冲区信息
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employees WHERE department_id = 1;

-- 包含详细成本信息
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM employees WHERE department_id = 1;

-- EXPLAIN输出解读
-- Seq Scan: 顺序扫描
-- Index Scan: 索引扫描
-- Index Only Scan: 索引仅扫描（无需回表）
-- Bitmap Heap Scan: 位图堆扫描
-- Nested Loop: 嵌套循环连接
-- Hash Join: 哈希连接
-- Merge Join: 归并连接

-- 查看未使用的索引
SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME,
    s.SEQ_IN_INDEX
FROM information_schema.STATISTICS s
LEFT JOIN information_schema.INNODB_INDEX_STATS iis 
    ON s.INDEX_NAME = iis.index_name 
    AND s.TABLE_NAME = iis.table_name
WHERE s.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND (iis.stat_name IS NULL OR iis.stat_value = 0)
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME;

-- 查看索引大小
SELECT 
    table_name,
    index_name,
    stat_value * @@innodb_page_size / 1024 / 1024 AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
AND table_name = 'employees';

-- 查看未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND idx_tup_read = 0
ORDER BY schemaname, tablename;

-- 查看索引大小
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查看索引膨胀
SELECT 
    schemaname,
    tablename,
    indexname,
    (pg_relation_size(indexrelid)::float / 
     (COALESCE(pgstattuple(indexrelid).dead_tuple_percent, 0) + 1)) AS efficiency_ratio
FROM pg_stat_user_indexes
JOIN pg_stattuple(indexrelid) ON true;

-- MySQL高并发优化
-- 使用合适的事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ_COMMITTED;

-- 批量操作优化
-- 使用批量插入减少索引维护开销
INSERT INTO large_table (col1, col2, col3) 
SELECT col1, col2, col3 FROM staging_table;

-- 临时禁用索引（仅适用于MyISAM）
ALTER TABLE myisam_table DISABLE KEYS;
-- 执行大量插入操作
ALTER TABLE myisam_table ENABLE KEYS;

-- PostgreSQL高并发优化
-- 使用批量操作
COPY large_table FROM '/path/to/data.csv';

-- 调整维护工作
SET maintenance_work_mem = '256MB';
SET work_mem = '64MB';

-- 并发创建索引（不会阻塞DML操作）
CREATE INDEX CONCURRENTLY idx_concurrent ON large_table(column_name);

-- MySQL分区表索引策略
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(50)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 在分区表上创建索引
CREATE INDEX idx_sale_date ON sales(sale_date);  -- 本地索引
CREATE INDEX idx_region ON sales(region);        -- 本地索引

-- PostgreSQL分区表索引策略
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    region VARCHAR(50)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 创建索引（会自动传播到所有分区）
CREATE INDEX idx_sales_date ON sales (sale_date);
CREATE INDEX idx_sales_region ON sales (region);

-- MySQL时间序列索引
CREATE INDEX idx_timestamp ON events(event_timestamp);
CREATE INDEX idx_date_partition ON events(YEAR(event_timestamp), MONTH(event_timestamp));

-- 使用范围查询
SELECT * FROM events 
WHERE event_timestamp >= '2024-01-01' 
AND event_timestamp < '2024-02-01';

-- PostgreSQL时间序列索引
CREATE INDEX idx_events_time ON events USING btree(event_timestamp);
CREATE INDEX idx_events_time_brin ON events USING brin(event_timestamp);

-- 适用于时间序列的BRIN索引
-- 占用空间小，适合大表

-- 使用Performance Schema监控
SELECT 
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_read DESC;

-- 查看慢查询中涉及的表
SELECT * FROM mysql.slow_log 
WHERE sql_text LIKE '%your_table%';

-- 使用pg_stat_statements
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 查找缺失的索引
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    seq_tup_read / seq_scan AS avg_tups
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;

-- MySQL优化技巧
-- 1. 使用覆盖索引避免回表
CREATE INDEX idx_covering ON orders(customer_id, order_date, status, total_amount);

-- 2. 为范围查询优化索引顺序
CREATE INDEX idx_range_opt ON orders(status, order_date, customer_id);
-- 当status选择性不高但经常用于WHERE条件时

-- 3. 使用前缀索引节省空间
CREATE INDEX idx_prefix_name ON users(name(10));  -- 只索引前10个字符

-- PostgreSQL优化技巧
-- 1. 使用部分索引
CREATE INDEX idx_recent_active ON orders(id) 
WHERE status = 'active' AND order_date > CURRENT_DATE - INTERVAL '30 days';

-- 2. 使用表达式索引优化特定查询
CREATE INDEX idx_email_domain ON users(SUBSTRING(email FROM POSITION('@' IN email) + 1));

-- 3. 使用操作符类优化特定数据类型
CREATE INDEX idx_text_pattern ON products USING btree(product_name varchar_pattern_ops);

-- 陷阱1: 过度索引
-- 错误：为每个列都创建索引
CREATE INDEX idx_col1 ON table1(col1);
CREATE INDEX idx_col2 ON table1(col2);
CREATE INDEX idx_col3 ON table1(col3);
-- 结果：写性能严重下降，存储空间浪费

-- 正确：根据查询模式创建索引
CREATE INDEX idx_common_query ON table1(col1, col2);
CREATE UNIQUE INDEX idx_identifier ON table1(col3);

-- 陷阱2: 忽略复合索引顺序
-- 错误：将低选择性列放在前面
CREATE INDEX idx_bad ON orders(status, customer_id, order_date);
-- 当查询不包含status时无法使用索引

-- 正确：按选择性高低排序
CREATE INDEX idx_good ON orders(customer_id, order_date, status);

-- 陷阱3: 在WHERE子句中使用函数
-- 错误：索引无法使用
SELECT * FROM users WHERE YEAR(created_date) = 2024;

-- 正确：使用范围查询
SELECT * FROM users 
WHERE created_date >= '2024-01-01' 
AND created_date < '2025-01-01';

-- MySQL索引失效场景
-- 1. 使用LIKE '%pattern%'
SELECT * FROM users WHERE name LIKE '%john%';  -- 无法使用索引

-- 2. 在索引列上使用函数
SELECT * FROM users WHERE UPPER(name) = 'JOHN';  -- 无法使用name索引

-- 3. 使用OR连接不同列的条件
SELECT * FROM users WHERE name = 'john' OR email = 'john@example.com';  -- 可能无法使用索引

-- 4. 类型隐式转换
SELECT * FROM users WHERE user_id = '123';  -- user_id是INT类型，可能导致索引失效

-- PostgreSQL索引失效场景
-- 类似的情况也会导致PostgreSQL索引失效
-- 但PostgreSQL的查询优化器通常更智能

-- MySQL定期维护脚本
-- 1. 更新表统计信息
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND table_rows > 10000;

-- 2. 检查碎片
SELECT 
    table_schema,
    table_name,
    round(((data_length + index_length) / 1024 / 1024), 2) AS 'Size_MB',
    round((data_free / 1024 / 1024), 2) AS 'Free_MB'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND data_free > 0;

-- PostgreSQL定期维护脚本
-- 1. VACUUM和ANALYZE
VACUUM ANALYZE employees;

-- 2. 检查表膨胀
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_relation_size(schemaname||'.'||tablename) DESC;

-- 3. 重建碎片化索引
SELECT 'REINDEX INDEX ' || indexname || ';' 
FROM pg_stat_user_indexes 
WHERE idx_scan > 1000 
AND (pg_relation_size(indexrelid) / (idx_tup_read + 1)) > 100;

-- 关键监控指标
-- MySQL:
-- - Key_read_requests / Key_reads (索引缓存命中率)
-- - Handler_read_* 操作次数
-- - 慢查询数量

-- PostgreSQL:
-- - buffer cache命中率
-- - 索引扫描vs顺序扫描比例
-- - 表膨胀程度

-- 创建监控视图
-- MySQL
CREATE VIEW index_monitoring AS
SELECT 
    table_schema,
    table_name,
    index_name,
    stat_value as usage_count
FROM mysql.innodb_index_stats
WHERE stat_name = 'n_diff_pfx';

-- PostgreSQL
CREATE VIEW index_efficiency AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    CASE 
        WHEN idx_tup_read = 0 THEN 0
        ELSE (idx_tup_fetch::float / idx_tup_read) * 100
    END AS efficiency_pct
FROM pg_stat_user_indexes;