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;
c语言入门自学多久-- 三次查询的时间(总记录数 只有⼀条 )
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); --组合索引
数百个亚马逊rds泄露用户信息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秒
mysql语句多表查询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查询数据⾮常快，不要拖死库…linux进入图形化界面
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论