mysql索引学习案例

简单的学习一下mysql普通索引

这是一个小的案例

一、创建表SQL

CREATE TABLE `group_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 KEY `unique_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;

    -- Disable autocommit for the duration of the procedure
    SET autocommit = 0;

    -- Start the transaction
    START TRANSACTION;
	-- 800000 就是80w条数据，这里自己选择创建多少，数据太少也展现不出来索引的作用
    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 FLOOR(RAND() * 365) DAY,
            NOW(), NOW()
        );

        SET i = i + 1;
    END WHILE;

    -- Commit the transaction
    COMMIT;

    -- Re-enable autocommit
    SET autocommit = 1;
END //

DELIMITER ;

-- 调用存储过程以生成数据
CALL generate_group_order_data();

三、GO

---

3.1 查询数据总条数

SELECT COUNT(1) FROM group_order;

这里数据是101w

---

3.2 使用唯一索引查询数据

SELECT * FROM group_order 
WHERE group_seq ='d6b9ebf0-8427-11ee-9600-988fe06b9059'
order by update_time desc limit 0,20;

可以看到查询速度在0.018s，速度还可以

---

3.3 不使用索引查询数据

EXPLAIN SELECT * FROM group_order 
WHERE group_status = 41
order by update_time desc limit 0,20;	

---

这里时间为1.938s，客户都等崩溃了

---

3.4 使用两个条件查询数据

一个条件有索引，一个条件没索引

3.4.1 索引条件在后

这里无索引条件在前，有索引条件在后

EXPLAIN SELECT * FROM group_order 
WHERE group_status = 41  
AND group_seq='d6b9ebf0-8427-11ee-9600-988fe06b9059' 
order by update_time desc limit 0,20;			

3.4.2 索引条件在前

这里索引条件在前，无索引条件在后

EXPLAIN SELECT * FROM group_order 
WHERE group_seq='d6b9ebf0-8427-11ee-9600-988fe06b9059'  
AND group_status = 41
order by update_time desc limit 0,20;					

---

3.5 给group_status添加普通索引

-- 添加group_status字段的普通索引
CREATE INDEX idx_group_status ON group_order(group_status);

---

3.5 查看表中的索引

-- 查询指定表中的索引
SHOW INDEX FROM group_order;

---

3.6 使用刚刚创建的索引字段进行查询

EXPLAIN SELECT * FROM group_order 
WHERE group_status = 41 
order by update_time desc limit 0,20;		

这里因为数据量比较大，在第一次查询时使用了0.55s，其余结果均为0.025s左右

四、释义

1. 这里选择使用了 group_status 来当索引，只是为了演示，一般状态字段不建议做索引
2. 图片中Extra：Using index condition

Using index condition: 这表示MySQL正在利用索引中的一部分条件来执行查询。具体来说，这通常发生在使用了覆盖索引（covering index）的情况，即查询的所有列都包含在索引中，而无需进一步查找实际的数据行。

3. 图片中Extra：Using filesort

Using filesort: 这表示MySQL需要对结果进行排序，并且这个排序操作是通过创建一个临时文件来完成的。这通常发生在无法使用索引满足ORDER BY的排序顺序时，或者在MySQL版本较旧的情况下。

---

PS 感谢观看

世上只有一种英雄主义，就是在认清生活真相之后依然热爱生活。— 罗曼罗兰