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

作者: admin 分类: 学习札记,解惑记录 发布时间: 2019-01-19 00:21  阅读: 38 views
今天线上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查询数据非常快,不要拖死库…

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注

更多阅读