mysql多条件查询 索引_通过添加索引提高多条件查询mysql单表大量数据的测试

今天线上mysql告警日志针对BBS点赞记录表发出了多次通知,提示没有添加索引。单表数据20w+,查询结果有做缓存处理,每查一次DB进行一次全表扫描,数据越多的情况下,查询效率越慢,超过了0.1m。添加索引之后,效率明显提升。之前对这块没过多关注, 想想就用实际的数据来测试一下吧。

线上的慢sql为

SELECT `cid` FROM `bbs_test` WHERE `origin` = 1 AND `user_id` = ? AND `cid` IN (?);

一. 建立表结构 — 用户点赞记录表

CREATE TABLE `bbs_test` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键，自动增长',

`user_id` varchar(32) DEFAULT NULL COMMENT '用户UID',

`origin` tinyint(4) DEFAULT '0' COMMENT '1帖子 2回复',

`cid` int(11) DEFAULT NULL COMMENT '帖子ID 评论ID',

`count` int(11) DEFAULT '0' COMMENT '计数',

`type` tinyint(4) DEFAULT NULL COMMENT '1点赞2分享3收藏',

`gmt_create` datetime DEFAULT NULL COMMENT '添加时间',

`version` tinyint(4) DEFAULT '0' COMMENT '版本号，默认0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=458603 DEFAULT CHARSET=utf8mb4 COMMENT='用户收藏/分享行为表'

批量插入了36w多条记录

select count(*) num from bbs_test;

362496

二、进行分类测试

1、一个查询条件 建立索引

-- 给user_id 加索引

create index bbs_test_user_id on bbs_test(user_id);

-- 创建索引需要维护索引文件，花费时间如下

create index bbs_test_user_id on bbs_test(user_id)OK, Time: 3.67sec

-- 删除索引

alter table bbs_test drop index bbs_test_user_id;

-- 三次查询的时间(总记录数 只有一条 )

select * from bbs_test where user_id = '368E8DA7A52C144BE050A00ACC3C6111';

加索引前 加索引后

1. 0.17秒 0.01秒

2. 0.33秒 0.01秒

3. 0.32秒 0.01秒

-- 三次查询的时间(总记录 33792条)

select * from bbs_test where user_id = '6B95182C82AA3272E050A00ACC3C431F';

加索引前 加索引后

1. 17.81秒 17.14秒

2. 17.16秒 17.56秒

3. 17.08秒 17.43秒

加索引后, 查看explain执行计划都走了bbs_test_user_id索引,针对少量数据,优化效果是明显的，

如果数据量很大,效果忽略。这种查询结构应该是业务需求上的设计缺陷。

2. 两个查询条件 建立组合索引

-- 给user_id,origin 加索引

create index bbs_test_user_origin_id on bbs_test(user_id,origin);

-- 删除索引

alter table bbs_test drop index bbs_test_user_origin_id;

-- 三次查询的时间(总记录数 只有一条 )

select * from bbs_test where user_id = '368E8DA7A52C144BE050A00ACC3C6111' and origin = 1 ;

加索引前 加索引后

1. 0.32秒 0.01秒

2. 0.18秒 0.02秒

3. 0.19秒 0.01秒

-- 三次查询的时间(总记录 5120条)

select * from bbs_test where user_id = '6B95182C82AA3272E050A00ACC3C431F' and origin = 1;

加索引前 加索引后

1. 2.07秒 1.87秒

2. 2.19秒 1.92秒

3. 2.09秒 1.96秒

加索引后, 查看explain执行计划都走了bbs_test_user_origin_id索引，对于数据多的效果不明显

3、三个查询条件， 多种索引尝试

-- 创建不同索引尝试

create index bbs_test_one on bbs_test(user_id); -- 单索引

create index bbs_test_two on bbs_test(user_id,origin); --组合索引

create index bbs_test_three on bbs_test(user_id, origin , cid); --组合索引

create index bbs_test_four on bbs_test(cid); -- 单索引

-- 删除索引

alter table bbs_test drop index bbs_test_one;

alter table bbs_test drop index bbs_test_two;

alter table bbs_test drop index bbs_test_three;

alter table bbs_test drop index bbs_test_four;

-- 三次查询的时间(总记录数 只有一条 )

select * from bbs_test where user_id = '368E8DA7A52C144BE050A00ACC3C6111' and origin = 1 and cid in(21) ;

加索引前 加one索引后 加two索引后 加three索引后 加four索引后

扫描all 扫描1行 扫描1行 扫描1行 扫描1024

1. 0.18秒 0.01秒 0.01秒 0.06秒 0.02秒

2. 0.16秒 0.01秒 0.01秒 0.01秒 0.01秒

3. 0.24秒 0.01秒 0.01秒 0.01秒 0.02秒

-- 三次查询的时间(总记录 1024条)

select * from bbs_test where user_id = '6B95182C82AA3272E050A00ACC3C431F' and origin = 1 and cid in(26541,26543);

加索引前 加one索引后 加two索引后 加three索引后 加four索引后

扫描all 扫描1024行 扫描1024行 扫描1024行 扫描1024行

1. 0.16秒 0.07秒 0.06秒 0.05秒 0.48秒

2. 0.18秒 0.07秒 0.06秒 0.08秒 0.54秒

3. 0.18秒 0.06秒 0.08秒 0.07秒 0.32秒

-- 三次查询的时间(总记录 2048条)

select * from bbs_test where user_id = '6B95182C82AA3272E050A00ACC3C431F' and origin = 1 and cid in(26541,26543);

加索引前 加one索引后 加two索引后 加three索引后 加four索引后

扫描all 扫描2048行 扫描2048行 扫描2048行 扫描2048(explain的type为range,效率低于ref)

1. 0.27秒 0.18秒 0.37秒 0.31秒 0.35秒

2. 0.51秒 0.31秒 0.33秒 0.28秒 0.49秒

3. 0.17秒 0.37秒 0.33秒 0.56秒 0.35秒

由于测试环境的稳定性|测试方法的逻辑|数据问题，可能对测试结果有一定影响。

当然测试条件还有很多种,就不一一列举了。

通过以上测试，可以明确几点

0. 当查询数据时，条件越精确，查询速度越快。如一列具有唯一性,作为查询条件。如果没有,根据业务进行精确查询

1. 当某一列值具有唯一属性,作为查询条件。创建索引之后,效果非常明显。如:主键ID(会创建默认索引,无需在建)

2. 当查询条件有多个,且每列都没有唯一属性。这种情况,可以增加组合索引,也可以直接以排除记录数最多的字段作为索引,减少扫描的行数。

3. 存在in条件时,索引字段为数字类型。 in中值为1个时,执行计划的索引类型为ref;in中的值多个时,执行计划的索引类型为range。 ref的执行效率高于range

4. 针对不同查询条件，要去分析执行计划, explain sql 。根据数据的结构、特点进行索引的创建。

5. 多了解下mysql的索引结构，知道根据索引查询的过程，对索引的创建也是有好处的。

希望所有的sql查询数据非常快,不要拖死库…