时间戳

首页 > 技术文章 > 百万数据的curd测试

百万数据的curd测试

xuduan 2021-07-23 14:09 原文

经常使用mysql的我们都知道，一旦数据量大起来，还是用以前的老方法查询，卡是必然的。那么怎么优化和合理的使用索引，写出高效的sql语句。下面俺来测试一波~

准备工作

这里有位大佬的导入一百万条数据的方法：点我打开
导入1w条数据约16s，导入100w条数据约1600s,不想等那么长时间导入数据的，可以直接看本人的测试结果

表结构和测试数据

表结构

CREATE TABLE `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int(9) NOT NULL COMMENT '本地网',
  `region_id` int(9) NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1020001 DEFAULT CHARSET=utf8mb4 COMMENT='普通表';

数据demo

mysql> select * from `test_user` where `id` = 1\G;
*************************** 1. row ***************************
         id: 1
    user_id: 8488b3da-eabd-11eb-8437-0242ac120005
  user_name: mrXhmNKdEuhN0t9cpi5p
      phone: 13636237617
     lan_id: 255
  region_id: 6
create_time: 2021-07-22 15:22:05
1 row in set (0.00 sec)

查询简单和复杂字段对比

ps:
id = 123123 等的整型，数据相对是最简单的了，全是位数不大的数字
phone = '13636237617' 等的字符串，数据比id复杂一点
user_id = '0000170a-eac2-11eb-8666-0242ac120005' 等的字符串,数据比phone又复杂一点

select id from test_user;       -- 查询简单字段(全盘扫描)： 1.8s
select phone from test_user;    -- 查询复杂字段(全盘扫描)： 3.6s
select user_id from test_user;  -- 查询复杂字段(全盘扫描)： 5s

查询少量和大量字段

select id from test_user; -- 查询少量字段：2.43s
select * from test_user; -- 查询大量字段：9s

curd索引有效和失效的对比

-- 查询少量数据（结果集较少的）：
select * from test_user where user_id like '%8488b3da-eabd-11eb-8437-0242ac12%' -- like查询(索引失效) : 1.3s 
select * from test_user where user_id like '8488b3da-eabd-11eb-8437-0242ac12%'; -- like查询(索引有效) : 0.003s 
select * from test_user where id = 1000; 	   -- 等值查询(索引有效) : 0.003s 
select * from test_user where abs(id) = 5; 	   -- 运算符号绝对值(索引失效)  ： 0.9s
select * from test_user where id=5 or id = -5; -- 运算符号替代方案(索引有效) ：0.007s

-- 查询大量数据（结果集较多的）：
select * from test_user where id > 100000; -- 查询90w数据出来(索引有效)：9s
select * from test_user where id < 100000; -- 查询10w数据出来(索引有效)：0.9s
select * from test_user where id > 100000 limit 100000; -- 查询10w数据出来(索引有效)：0.9s


-- 查询的数据类型出现隐式转化：
select * from test_user where user_id = 8437;   -- 出现隐式字符转整型(索引无效)：6s
select * from test_user where user_id = '8437'; -- 正确的数据格式查询(索引有效)：0.004s

-- 增
INSERT INTO `test_data`.`test_user`(`id`, `user_id`, `user_name`, `phone`, `lan_id`,`region_id`, `create_time`) 
 VALUES (11, '843712', 'qsQFFaFGid2myxQszlS8', '18792592447', 683, 2, '2021-07-22 15:22:05');  -- 0.013s

-- 删
delete from test_user  where user_id = '843712';    -- 使用索引：0.012s
delete from test_user  where phone = '18792592447'; -- 未使用索引：3.69s

-- 改
update test_user set user_id='84371'   where user_id = '8437'      -- 使用索引：0.05s
update test_user set user_id='843712'  where phone = '18792592447' -- 未使用索引：5.12s

结论

1 百万级别的单条数据查询，有无索引速度差别在50-100倍；
2 更新：只要用到了where条件的都受到索引的影响；
3 新增：速度最快,不存在查询所以百万数据的表中新增速度不受影响；
4 删除：无论有无索引，都比修改数据要快；
（ps:有文章说or查询条件的左右都必须是索引字段，否则索引会失效，本人亲测暂时没有看出差别)

索引失效分析

1、like 以%开头，索引无效。（仅后缀有%时，索引还是有效的）
2、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引失效，产生全表扫描。
3、对索引字段使用函数或者计算，如abs或+-运算等，将导致索引失效。
4、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

另外还有以下几个有兴趣可以去测试下，用例本文没有一一列举了：
1、组合索引，查询条件中没有在第一个字段使用组合的第一个字段，索引失效。 （//todo 还没有测试）
2、在索引字段上使用not，<>，!=等操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 （测试后感觉差别不大）
3、or语句前后没有同时使用索引。只有当or左右查询字段均为索引时，才会生效,否则索引失效。 （测试后感觉差别不大）

优化建议

1 尽量控制查询的字段个数
2 尽量控制查询的条数
3 尽量查询简单的字段
4 即使建立了索引，也必须注意合理使用，尽量避免出现导致索引失效的语句

推荐阅读