1. 索引创建的核心原则解析在数据库优化领域索引就像图书馆的目录系统。想象一下在百万册藏书中寻找特定书籍时没有分类标签和检索编号会是怎样的灾难。作为从业十余年的数据库架构师我处理过太多因索引不当导致的性能事故。以下是经过实战验证的索引创建黄金法则重要提示索引不是越多越好每个额外索引都会增加写入开销。根据统计超过5个索引的表在OLTP系统中写入性能下降可达40%1.1 选择性优先原则高选择性字段应该作为索引的首选列。计算选择性的公式如下选择性 不重复值数量(DISTINCT) / 总行数(COUNT)当选择性 0.3 时索引效果显著。例如用户表的手机号字段-- 计算字段选择性示例 SELECT COUNT(DISTINCT mobile) / COUNT(*) AS selectivity FROM users;避坑经验避免对性别、状态码等低选择性字段建单列索引多列组合索引应将高选择性字段放在左侧使用EXPLAIN验证索引是否被实际使用1.2 最左前缀匹配原则这是组合索引设计的核心法则。假设有联合索引idx_a_b_c-- 能使用索引的情况 WHERE a 1 WHERE a 1 AND b 2 WHERE a 1 AND b 2 AND c 3 -- 不能使用索引的情况 WHERE b 2 WHERE c 3 WHERE b 2 AND c 3实战技巧将等值查询()字段放在索引左侧范围查询(,)字段放在右侧常用排序字段可加入索引末尾1.3 覆盖索引优化当索引包含查询所需全部字段时性能提升最显著。比较以下两个查询-- 需要回表查询 SELECT * FROM orders WHERE user_id 100; -- 覆盖索引查询推荐 ALTER TABLE orders ADD INDEX idx_cover(user_id, amount, create_time); SELECT user_id, amount, create_time FROM orders WHERE user_id 100;性能对比查询类型执行时间(ms)扫描行数回表查询12010,000覆盖索引151002. 索引类型选型策略2.1 B-Tree索引的适用场景作为最普遍的索引类型B-Tree适合等值查询, IN范围查询, , BETWEEN前缀匹配LIKE abc%特殊场景处理超长字段使用前缀索引INDEX(column(10))JSON字段使用虚拟列索引ALTER TABLE products ADD COLUMN price_val DECIMAL(10,2) GENERATED ALWAYS AS (JSON_EXTRACT(attributes, $.price)) STORED; CREATE INDEX idx_price ON products(price_val);2.2 哈希索引的取舍Memory引擎默认使用哈希索引特点O(1)时间复杂度查找仅支持等值比较不保证排序顺序使用建议临时表、内存表适合哈希索引字段值较长时可考虑CRC32函数索引ALTER TABLE urls ADD COLUMN url_crc INT UNSIGNED GENERATED ALWAYS AS (CRC32(url)) VIRTUAL; CREATE INDEX idx_crc ON urls(url_crc);3. 索引维护与监控3.1 索引碎片整理随着数据修改索引碎片率超过30%时应重建-- 检查碎片率 SELECT table_name, index_name, ROUND(data_free/(data_lengthindex_length)*100,2) AS frag_ratio FROM information_schema.tables WHERE table_schema your_db; -- 重建索引 ALTER TABLE orders ENGINEInnoDB;维护窗口建议业务低峰期执行大表采用pt-online-schema-change工具配合ALGORITHMINPLACE减少锁时间3.2 索引使用率监控通过performance_schema发现无用索引SELECT object_schema, object_name, index_name, rows_selected, rows_inserted FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL ORDER BY rows_selected DESC;清理标准超过3个月未使用的索引选择率1000次/天的索引与主键存在重复的二级索引4. 特殊场景索引方案4.1 时序数据索引优化针对时间序列数据如日志表的特殊处理-- 按时间分片索引 CREATE TABLE logs_2023 ( id BIGINT, create_time DATETIME, INDEX idx_time (create_time) ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS(2023-02-01)), PARTITION p202302 VALUES LESS THAN (TO_DAYS(2023-03-01)) ); -- 定期归档旧数据 ALTER TABLE logs_2023 DROP PARTITION p202301;4.2 全文检索实现方案相比LIKE查询全文索引性能提升百倍-- 创建全文索引 ALTER TABLE articles ADD FULLTEXT INDEX ft_idx(title,content); -- 使用布尔搜索模式 SELECT * FROM articles WHERE MATCH(title,content) AGAINST(mysql -oracle IN BOOLEAN MODE);中文分词建议使用ngram解析器WITH PARSER ngram配合Elasticsearch实现更复杂搜索5. 索引设计反模式5.1 过度索引问题每个额外索引带来的成本写入性能下降约5-10%存储空间增加20-30%优化器选择负担加重精简索引的方法合并功能重复的索引将单列索引升级为组合索引使用索引合并(Index Merge)替代多个索引5.2 隐式类型转换陷阱常见导致索引失效的情况-- 字段是varchar但传入数字索引失效 SELECT * FROM users WHERE mobile 13800138000; -- 解决方案保证类型一致 SELECT * FROM users WHERE mobile 13800138000;类型转换黑名单字符串与数字比较字符集不一致函数处理索引列如DATE(create_time)在最近一次系统优化中通过对300张表的索引重构我们成功将平均查询延迟从230ms降至47ms。关键是将原有的1874个索引精简到921个同时通过组合索引设计使索引命中率从58%提升到89%。这再次验证了合理索引设计的重要性——不是越多越好而是越精准越好。
