数据库优化|性能提升：mysql添加索引语句及MySQL添加索引方法解析

🚀 数据库优化 | 性能提升：MySQL添加索引语句及方法解析

场景引入：
某天，程序员小张发现公司后台系统越来越慢，用户投诉页面加载要等10秒以上 😫，排查后发现，核心的订单查询SQL居然要扫描全表200万条数据！这时候，DBA老王拍了拍他肩膀："小伙子，该加索引了！"

🔍 为什么需要索引？

想象一下,一本没有目录的百科全书 📚，要查某个知识点只能一页页翻——这就是没有索引的数据库表，而索引就像书的目录，能让你快速定位数据，特别是当表数据量超过10万行时，效果立竿见影！

索引的典型收益：

• 查询速度提升10~100倍 ⚡
• 减少服务器CPU和内存消耗 💾
• 避免全表扫描导致的磁盘IO瓶颈 📉

📌 MySQL添加索引的5种方法

方法1：CREATE INDEX 基础版

-- 单列索引（最常用）
CREATE INDEX idx_user_name ON users(name);  
-- 多列联合索引（注意顺序影响查询效率）
CREATE INDEX idx_order_status_date ON orders(status, create_time);  

💡 适用场景：已存在的表需要新增索引，不影响业务运行。

方法2：ALTER TABLE 追加式

-- 添加普通索引  
ALTER TABLE products ADD INDEX idx_price(price);  
-- 添加唯一索引（防止重复值）  
ALTER TABLE employees ADD UNIQUE idx_email(email);  
-- 添加主键索引（如果表没有主键）  
ALTER TABLE logs ADD PRIMARY KEY(id);  

🌟 优势：一条语句可同时添加多个索引，适合初始化表结构时使用。

方法3：建表时直接定义（DDL语句）

CREATE TABLE articles (
    id INT AUTO_INCREMENT,  VARCHAR(100),  
    content TEXT,  
    author_id INT,  
    PRIMARY KEY (id),  -- 主键索引  
    INDEX idx_title (title),  -- 普通索引  
    UNIQUE INDEX idx_author_title (author_id, title)  -- 联合唯一索引  
);  

🛠️ 最佳实践：新项目建表时就应该规划好索引。

方法4：创建全文索引（FULLTEXT）

-- 支持文本内容的模糊搜索优化  
ALTER TABLE news ADD FULLTEXT INDEX ft_content(content);  
-- 使用MATCH AGAINST查询  
SELECT * FROM news WHERE MATCH(content) AGAINST('数据库优化');  

📝 注意：仅适用于MyISAM和InnoDB引擎（MySQL 5.6+）。

方法5：空间索引（GIS数据）

-- 为地理坐标数据创建索引  
ALTER TABLE locations ADD SPATIAL INDEX pt_index(coordinates);  

🌍 特殊用途：处理地图、经纬度等空间数据。

⚠️ 索引使用避坑指南

1. 不要过度索引

   • 每个索引会占用磁盘空间,增删改操作需要维护索引
   • 建议单表索引不超过5~7个

2. 最左前缀原则

   

   -- 联合索引 (A,B,C) 生效场景：
   WHERE A=1 AND B=2  -- ✅ 生效  
   WHERE B=2 AND C=3  -- ❌ 不生效  

3. 索引失效的常见情况

   • 使用LIKE '%关键字%'（前导通配符）
   • 对索引列进行函数计算：WHERE YEAR(create_time)=2025
   • 类型不匹配：字符串列用数字查询

🛠️ 索引优化实战技巧

技巧1：EXPLAIN分析工具

EXPLAIN SELECT * FROM users WHERE age > 25;  

查看结果中的 type 和 key 字段，确认是否命中索引。

技巧2：慢查询日志定位

-- 开启慢查询日志（2秒以上查询）  
SET GLOBAL slow_query_log = ON;  
SET GLOBAL long_query_time = 2;  

技巧3：索引选择性评估

-- 计算字段的选择性（越接近1越好）  
SELECT COUNT(DISTINCT city)/COUNT(*) FROM customers;  

选择性 > 0.1 的字段才适合建索引

📅 2025年MySQL索引新动向

根据2025年MySQL 8.3的更新：

• 倒序索引性能提升30% ↓

  CREATE INDEX idx_desc ON accounts(balance DESC);  

• 隐藏索引（可临时禁用索引而不删除）

  ALTER INDEX idx_test INVISIBLE;  

索引是数据库的"加速器"，但需要科学使用：

1. 高频查询字段优先加索引
2. 联合索引注意字段顺序
3. 定期用ANALYZE TABLE更新统计信息

小张听完后,给订单表加上了(user_id, status)联合索引，查询时间从8.2秒降到了0.03秒，老板终于露出了笑容 😎。

（完）