mysql索引学习案例

太过爱你忘了你带给我的痛 2024-02-18 04:35 9阅读 0赞

### 简单的学习一下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;

![在这里插入图片描述][dbf26e6d124d4690af04263df020f7a7.png]

> 这里数据是101w

--------------------

##### 3.2 使用唯一索引查询数据 #####

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

![在这里插入图片描述][b848880154654a8aad7bb4c8980353e7.png]  
![在这里插入图片描述][2df08afb26e34b4b871c5da76a906921.png]

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

--------------------

##### 3.3 不使用索引查询数据 #####

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

![在这里插入图片描述][aae4276abc324611a8939fba034bc4dd.png]

--------------------

![在这里插入图片描述][31ceba53e9d64df1902229d4e6edfa2c.png]

> 这里时间为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;

![在这里插入图片描述][97884d34ebb74aafb80b4a9f3b360a54.png]  
![在这里插入图片描述][463e7d9cd92f4d86afa1b8dcae7c35db.png]

###### 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;

![在这里插入图片描述][c666ac37b50e40378248c1cc4c77f7ee.png]  
![在这里插入图片描述][23da78ef676b4a85b3a8e5661b3fad93.png]

--------------------

##### 3.5 给group\_status添加普通索引 #####

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

--------------------

##### 3.5 查看表中的索引 #####

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

![在这里插入图片描述][c57e12b2e5fd4b889cc80a4471996171.png]

--------------------

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

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

![在这里插入图片描述][e9ef4bab00d141b0ab84602ded278df6.png]  
![在这里插入图片描述][c2f81fa31b264d69b7100f369605f3dc.png]

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

#### 四、释义 ####

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

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

1.  图片中Extra：Using filesort

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

--------------------

#### PS 感谢观看 ####

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

[dbf26e6d124d4690af04263df020f7a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/1b6193cd4e5442708898ee599b7793a5.png
[b848880154654a8aad7bb4c8980353e7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/ba4a0e980c324f8580147ee7793785e1.png
[2df08afb26e34b4b871c5da76a906921.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/dc3381c062a948a4bb3700b55e28deec.png
[aae4276abc324611a8939fba034bc4dd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/201b7428c00b43ae8a99e7dd26df528d.png
[31ceba53e9d64df1902229d4e6edfa2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/2a2c4efd8a3a40b080326d3325d3eed7.png
[97884d34ebb74aafb80b4a9f3b360a54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/dee131284a9c46f083dbc1fd0bd30a51.png
[463e7d9cd92f4d86afa1b8dcae7c35db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/b9cbcb8373914c2790fb16cf3ca3956b.png
[c666ac37b50e40378248c1cc4c77f7ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/3a81c1c68141443f83e28449dade53f6.png
[23da78ef676b4a85b3a8e5661b3fad93.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/7fe675c7394940cc8b280059df155b05.png
[c57e12b2e5fd4b889cc80a4471996171.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/ed5e276c005245d997ccbda046fc508e.png
[e9ef4bab00d141b0ab84602ded278df6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/ef8044b24f6448d68b4006cacc6f43ed.png
[c2f81fa31b264d69b7100f369605f3dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/08cd6f076bd14739b30639ba9e38fa10.png