如何为MySQL数据库建立合适的索引？

在mysql中，通过使用create index命令建立合适的索引，可以优化查询速度。

在MySQL中，索引是提高查询性能的关键工具，通过合理的设计和使用索引，可以显著提升数据库的检索速度和效率，本文将通过一个案例详细讲解如何在MySQL中建立合适的索引，以及不同类型索引的使用场景和效果。

一、案例背景

假设我们有一个名为group_order的表，该表用于记录拼单信息，表结构如下：

CREATE TABLEgroup_order (id int(11) NOT NULL AUTO_INCREMENT,group_seq varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '拼单号',group_status int(8) NOT NULL COMMENT '100 待提货, 200 已提货, 300 已取消',purchase_user_id varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '下单商家',user_id varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '下单人',product_id varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '商品id',saler_user_id varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '商品所属人',payable_money bigint(16) NOT NULL COMMENT '应付金额',piece_amount int(16) NOT NULL COMMENT '件数',pick_code varchar(16) COLLATE utf8mb4_bin NOT NULL COMMENT '自提码',verify_status tinyint(1) NOT NULL DEFAULT '0' COMMENT '核销状态  0失效, 1 生效',remark varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',order_time datetime NOT NULL COMMENT '下单时间',create_time datetime NOT NULL,update_time datetime NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEYunique_group_seq (group_seq)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPACT COMMENT='拼单';

二、数据准备

为了测试索引的效果，我们需要向表中插入大量数据，以下是生成数据的存储过程：

DROP PROCEDURE IF EXISTS generate_group_order_data;
DELIMITER //
CREATE PROCEDURE generate_group_order_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    -禁用自动提交以加快数据插入速度
    SET autocommit = 0;
    START TRANSACTION;
    WHILE i < 800000 DO
        INSERT INTO group_order (group_seq, group_status, purchase_user_id, user_id, product_id, saler_user_id, payable_money, piece_amount, pick_code, verify_status, remark, order_time, create_time, update_time)
        VALUES (UUID(), FLOOR(1 + RAND() * 300), UUID(), UUID(), UUID(), UUID(), FLOOR(100 + RAND() * 9900), FLOOR(1 + RAND() * 100), LPAD(FLOOR(RAND() * 10000), 4, '0'), FLOOR(RAND() * 2), NULL, NOW() INTERVAL FAND(RAND() * 365) DAY, NOW(), NOW());
        SET i = i + 1;
    END WHILE;
    COMMIT;
    SET autocommit = 1;
END //
DELIMITER ;
CALL generate_group_order_data();

三、查询优化前后对比

1. 查询数据总条数

SELECT COUNT(*) FROM group_order;

结果：101w条数据。

2. 使用唯一索引查询数据

SELECT * FROM group_order WHERE group_seq ='d6b9ebf0-8427-11ee-9600-988fe06b9059' ORDER BY update_time DESC LIMIT 0, 20;

查询速度：0.018s，速度较快。

3. 不使用索引查询数据

EXPLAIN SELECT * FROM group_order WHERE group_status = 41 ORDER BY update_time DESC LIMIT 0, 20;

查询时间：1.938s，客户等待时间较长。

四、创建索引进行优化

根据需求，我们可以对group_status和update_time列创建组合索引：

CREATE INDEX idx_group_status_update_time ON group_order(group_status, update_time);

再次执行查询计划：

EXPLAIN SELECT * FROM group_order WHERE group_status = 41 ORDER BY update_time DESC LIMIT 0, 20;

结果显示，查询使用了新创建的索引，并且type变为range，解决了全表扫描的问题，但是Extra中仍然有Using filesort问题，这是因为update_time是一个范围条件导致的。

解决方案：删除不必要的字段索引

ALTER TABLE group_order DROP INDEX idx_group_status_update_time;
CREATE INDEX idx_group_status ON group_order(group_status);

再次查看执行计划，发现不仅解决了Using filesort的问题，而且type也变成了ref级别，查询速度更快。

1. 选择性原则

选择有区分度的列进行索引，高选择性的列意味着该列的不同值很多，这样查询时能够更快地定位到所需的数据。

2. 适度原则

不要过度使用索引，虽然索引可以提高查询速度，但过多的索引会增加数据插入和更新的开销，对于经常需要查询的列和有高选择性的列应该创建索引。

3. 监控与调优

定期监控数据库的性能，并根据实际情况调整索引策略，可以使用慢查询日志来发现哪些查询需要优化。

4. 定期重建和优化索引

随着数据的增加和删除，原有的索引可能会变得不再高效，定期重建和优化索引可以帮助数据库保持最佳性能。

六、FAQs

Q1: 什么时候使用组合索引？

A1: 当查询条件中有多个列，并且这些列的组合具有较高的选择性时，可以使用组合索引，组合索引的顺序很重要，应该按照查询中出现的顺序创建。

Q2: 如何确定某个列是否需要创建索引？

A2: 如果某列经常出现在查询条件中（如WHERE子句），或者经常用于排序（如ORDER BY子句），那么可以考虑为该列创建索引，可以使用EXPLAIN命令查看查询计划，判断是否使用了索引以及是否存在性能瓶颈。

以上内容就是解答有关“mysql建立索引_案例：建立合适的索引”的详细内容了，我相信这篇文章可以为您解决一些疑惑，有任何问题欢迎留言反馈，谢谢阅读。