欢迎光临商丘市灵车网
详情描述

一、三种添加索引的方法

1. CREATE INDEX语句

-- 创建普通索引
CREATE INDEX idx_name ON table_name(column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_name ON table_name(column_name);

-- 创建前缀索引(针对文本字段)
CREATE INDEX idx_name ON table_name(column_name(10));

-- 创建多列组合索引
CREATE INDEX idx_name ON table_name(col1, col2, col3);

2. ALTER TABLE语句

-- 添加普通索引
ALTER TABLE table_name ADD INDEX idx_name(column_name);

-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE idx_name(column_name);

-- 添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY(column_name);

-- 添加全文索引(仅MyISAM和InnoDB支持)
ALTER TABLE table_name ADD FULLTEXT idx_name(column_name);

3. 建表时创建索引

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- 主键索引
    PRIMARY KEY (id),

    -- 唯一索引
    UNIQUE KEY uk_username (username),

    -- 普通索引
    KEY idx_email (email),

    -- 组合索引
    KEY idx_created_email (created_at, email(20)),

    -- 全文索引
    FULLTEXT idx_content (content)
) ENGINE=InnoDB;

二、索引类型详解

1. 主键索引 (PRIMARY KEY)

  • 每个表只能有一个
  • 值不能为NULL且必须唯一

2. 唯一索引 (UNIQUE)

  • 确保列值的唯一性
  • 允许NULL值(但只能有一个NULL)

3. 普通索引 (INDEX/KEY)

  • 最基本的索引类型
  • 仅用于加速查询

4. 组合索引 (Composite Index)

-- 正确的组合索引顺序应考虑查询条件和选择性
CREATE INDEX idx_user_date ON orders(user_id, order_date);

5. 全文索引 (FULLTEXT)

  • 用于文本内容的全文搜索
  • 仅支持CHAR、VARCHAR、TEXT类型

三、使用注意事项

1. 选择合适的列创建索引

✅ 适合创建索引的列:

  • WHERE子句中的列
  • JOIN连接条件的列
  • ORDER BY、GROUP BY涉及的列
  • 选择性高的列(不同值多)

❌ 不适合创建索引的列:

  • 数据量小的表(< 1000行)
  • 频繁更新的列(索引维护成本高)
  • 选择性低的列(如性别、状态标志)
  • BLOB/TEXT大字段(考虑前缀索引)

2. 组合索引的最佳实践

-- 最佳实践:遵循最左前缀原则
CREATE INDEX idx_a_b_c ON table(a, b, c);

-- 有效使用索引的查询:
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;
SELECT * FROM table WHERE a = 1 ORDER BY b, c;

-- 无法使用索引的查询:
SELECT * FROM table WHERE b = 2;  -- 缺少最左列a
SELECT * FROM table WHERE a = 1 ORDER BY c;  -- 跳过了b

3. 避免索引失效的场景

-- 1. 对索引列进行运算或函数处理
SELECT * FROM users WHERE YEAR(created_at) = 2023;  -- ❌
SELECT * FROM users WHERE created_at >= '2023-01-01';  -- ✅

-- 2. 使用NOT、!=、<>操作符
SELECT * FROM users WHERE status != 'active';  -- ❌

-- 3. 使用OR条件连接(除非所有列都有索引)
SELECT * FROM users WHERE id = 1 OR username = 'john';  -- ❌

-- 4. LIKE以通配符开头
SELECT * FROM users WHERE username LIKE '%john%';  -- ❌
SELECT * FROM users WHERE username LIKE 'john%';   -- ✅

-- 5. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- ❌ phone是varchar
SELECT * FROM users WHERE phone = '13800138000';  -- ✅

4. 索引维护和管理

-- 查看表索引
SHOW INDEX FROM table_name;

-- 查看索引使用情况(需要开启性能模式)
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 删除索引
DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;

-- 重建索引(优化索引碎片)
ALTER TABLE table_name ENGINE=InnoDB;
OPTIMIZE TABLE table_name;

5. 性能监控

-- 查看索引使用统计
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'your_database';

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

四、实际应用建议

案例:电商订单表优化

-- 原始表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    product_id BIGINT,
    status TINYINT,
    amount DECIMAL(10,2),
    created_at DATETIME
);

-- 优化后的索引策略
CREATE INDEX idx_user_status ON orders(user_id, status);
CREATE INDEX idx_created_user ON orders(created_at, user_id);
CREATE INDEX idx_product_status ON orders(product_id, status);

-- 分区表(针对超大表)
ALTER TABLE orders 
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

五、常见错误

过度索引:每个索引都会增加插入、更新、删除操作的开销 盲目添加索引:未分析查询模式就添加索引 忽略索引选择性:在性别字段上创建索引效果甚微 组合索引顺序错误:未考虑查询频率和选择性 忘记监控和调整:索引需要根据业务变化调整

六、最佳实践总结

先分析后创建:使用EXPLAIN分析查询执行计划 测试验证:在生产环境前在测试环境验证性能提升 定期审查:定期检查未使用或低效的索引 考虑业务负载:读写比例影响索引策略 监控磁盘空间:索引会占用额外的磁盘空间

通过合理使用索引,可以显著提升MySQL查询性能,但需要根据实际业务场景和数据特征进行精心设计和持续优化。

相关帖子
用房子抵押贷款还网贷:以贷养贷的甜蜜陷阱与债务重组正确操作姿势
用房子抵押贷款还网贷:以贷养贷的甜蜜陷阱与债务重组正确操作姿势
商丘市手机app开发#网站设计与开发,收费透明
商丘市手机app开发#网站设计与开发,收费透明
商丘市丧葬灵棚策划#殡葬服务一条龙,白事丧事服务
商丘市丧葬灵棚策划#殡葬服务一条龙,白事丧事服务
商丘市苹果手机app开发&网站开发公司,专业建站公司
商丘市苹果手机app开发&网站开发公司,专业建站公司
残保金征收现在是否实行了分档征收政策,具体分档标准是什么?
残保金征收现在是否实行了分档征收政策,具体分档标准是什么?
员工存在违章操作行为导致受伤,是否会影响工伤认定?
员工存在违章操作行为导致受伤,是否会影响工伤认定?
盐城市安卓系统app开发#网站开发服务公司,小程序开发
盐城市安卓系统app开发#网站开发服务公司,小程序开发
晋城市公司丧事服务#殡葬服务一条龙办理,丧葬一站式服务
晋城市公司丧事服务#殡葬服务一条龙办理,丧葬一站式服务
小区房子抵押贷款征信要求各地有什么区别?不同城市征信对比
小区房子抵押贷款征信要求各地有什么区别?不同城市征信对比
企业经营贷款结清证明流程,还款结清证明开具
企业经营贷款结清证明流程,还款结清证明开具
邯郸市办理丧葬服务|租冰棺,价格公道
邯郸市办理丧葬服务|租冰棺,价格公道
九江市客户关系管理系统开发&网站设计正规公司,优秀设计团队
九江市客户关系管理系统开发&网站设计正规公司,优秀设计团队
成都市葬礼拍摄#白事殡葬一条龙服务,丧事一条龙服务
成都市葬礼拍摄#白事殡葬一条龙服务,丧事一条龙服务
苏州市手机app开发#企业网站建设公司,小程序开发
苏州市手机app开发#企业网站建设公司,小程序开发
太原市灵堂守灵#殡葬服务正规公司,白事一条龙服务
太原市灵堂守灵#殡葬服务正规公司,白事一条龙服务
上海市网站推广&模版网站开发,专业开发团队
上海市网站推广&模版网站开发,专业开发团队
常州市正规殡葬公司电话|办理丧事,价格公道,快速上门
常州市正规殡葬公司电话|办理丧事,价格公道,快速上门
写字楼门店房抵押贷款负债高怎么办?降低负债率提高通过率
写字楼门店房抵押贷款负债高怎么办?降低负债率提高通过率
包头市crm系统开发#专业网站开发建设,专业建站
包头市crm系统开发#专业网站开发建设,专业建站
企业经营贷款对财务负责人?财务人员稳定性条件
企业经营贷款对财务负责人?财务人员稳定性条件
未来是否可能出现无法被现有技术破解的“终极”人脸信息保护方案?
未来是否可能出现无法被现有技术破解的“终极”人脸信息保护方案?