测试千万级别数据量、使用Explain解释器及索引类型分析

最新推荐文章于 2024-06-18 23:03:55 发布

忧然叶落

最新推荐文章于 2024-06-18 23:03:55 发布

阅读量804

点赞数

分类专栏：数据篇文章标签： java

本文链接：https://blog.csdn.net/qq_43846090/article/details/123648530

版权

数据篇专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前言

最近买了一个阿里云RDS实例，瞅着怎么去压榨一下它的性能，就想着直接导入几百上千万的数据量，然后去做一些测试、看下sql的查询性能、以及如何去优化；
正好马上要离职准备下一份工作了，也趁此机会复习一下mysql相关知识；目前在保险工作做后天，那表设计是真的复杂，都是百千万级别，每个表都是二三十个字段起步的；
关于mysql相关的好多总结知识，后续再慢慢发出来：存储引擎、底层索引数据结构、锁、事务等等

看下本小节要掌握的知识点

请添加图片描述

下面准备开搞、先创建测试表结构

CREATE TABLE `user_operation_log` (
`id` int(11) NOT NULL AUTO\_INCREMENT,
`user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
`attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4\_general\_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO\_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4\_general\_ci ROW\_FORMAT = Dynamic;

然后通过存储过程批量导入测试数据

DELIMITER ;;
CREATE PROCEDURE batch\_insert\_log()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE userId INT DEFAULT 10000000;
set @execSql = 'INSERT INTO `user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
set @execData = '';
WHILE i<=10000000 DO
set @attr = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";
set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
if i % 1000 = 0
then
set @stmtSql = concat(@execSql, @execData,";");
prepare stmt from @stmtSql;
execute stmt;
DEALLOCATE prepare stmt;
commit;
set @execData = "";
else
set @execData = concat(@execData, ",");
end if;
SET i=i+1;
END WHILE;

END;;
DELIMITER ;

测试正式开始：使用explain调试索引类型

-- 注意：id列是主键索引  user_id是普通索引

-- ALL走全表扫描：
explain select * from user_operation_log a where a.ip = '10.131.11';
explain select id from user_operation_log a where a.ip = '10.131.11'; -- 即便查询字段是索引字段，但是条件是非索引字段，还是走全表扫描


-- const：根据主键/普通唯一索引列等值匹配查询（is null除外），这种查询是很快的，查询速率认为是常数级别的，定义为const
explain select * from user_operation_log a where id = 100;


-- ref：根据普通索引等值匹配，或is null。（前面说的普通唯一索引列查询时 is null也是这种场景）。这种方式需要先根据普通索引匹配到多个主键，然后根据主键进行回表。
explain select * from user_operation_log a where id is null;
explain select * from user_operation_log a where user_id = '100';  
-- 有查询非索引字段，就需要回表了，无法索引覆盖，注意字符串不加单引号，就会索引失效的；一个索引一棵树（除了联合索引），所以需要根据普通索引等值配置拿到主键，再回表根据主键拿到字段值


-- range：根据主键索引或普通索引（包含唯一索引）进行范围查找
explain select * from user_operation_log a where id > 100 and id <200;


-- index：索引覆盖，你查询的列刚好是索引列，即使查询条件是联合索引的非最左索引列，查询的条件是联合索引中的列，也可能会走索引覆盖；
#如果我们select的字段／数据，可以通过索引树直接找到数据，不需要再次去聚集索引的叶子节点找数据，这时我们就叫他覆盖索引
explain select id,user_id from user_operation_log a where user_id = '4000100';  #走的是ref形式，但是是using index的，也是索引覆盖


-- index merge：除此之外，还会有index merge（索引合并），针对一些and、or的操作，单纯的回表可能速度会慢一些，如果先将使用到的索引先进行求 交集、并集之后在进行回表，会更加高效
explain select * from user_operation_log a where id = 200 or user_id = '100';

拓展一个点！千万数据分页查询性能如何优化？

-- 导入时间还是用得很久，毕竟阿里云RDS的性能一般，1核1G,20G的磁盘空间；耗费了五十多分钟（导入期间CPU使用率基本都是满的，也算利用到位了）
Procedure executed successfully
时间: 5262.607s

-- 发现数据库被锁了，因此空间超出了，
-- 发现阿里云锁了之后，只能扩容， 或者删除整个表数据，不能删除部分数据，所以只能重来了；也就是说delete不能解锁，因为delete会继续产生日志数据
SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC

-- truncate之后表结构还在，继续导数据。drop之后表都没了
-- truncate table user_operation_log;


-- 看下总数据量 10674000 小服务器根本受不了这么大的数据量，查了372s ，所以占了18G，干掉500万数据腾出点空间，也不影响效果
delete from user_operation_log a where a.id > 5000000;


-- 1、相同偏移量，不同数据量  															-- 索引前   索引后
SELECT * FROM `user_operation_log` LIMIT 10000, 10        -- 0.02s
SELECT * FROM `user_operation_log` LIMIT 10000, 100       -- 0.04s
SELECT * FROM `user_operation_log` LIMIT 10000, 1000      -- 0.04s
SELECT * FROM `user_operation_log` LIMIT 10000, 10000     -- 0.278s  0.256s
SELECT * FROM `user_operation_log` LIMIT 10000, 100000    -- 3.2s    2.437s
SELECT * FROM `user_operation_log` LIMIT 10000, 1000000   -- 33s
-- 这个没的啥解释的，数据越多，肯定耗时越长，优化操作就是对常用的查询字段、条件字段建立索引


-- 2、我们直接测试相同的数据量，不同的偏移量
SELECT * FROM `user_operation_log` LIMIT 100, 100;     -- 0.012s
SELECT * FROM `user_operation_log` LIMIT 1000, 100;     -- 0.012s
SELECT * FROM `user_operation_log` LIMIT 10000, 100;		 -- 0.019s
SELECT * FROM `user_operation_log` LIMIT 100000, 100;   -- 0.085s
SELECT * FROM `user_operation_log` LIMIT 1000000, 100;  --  28.524s  27.819s

-- 优化方案
-- 不用select *  这个比较你让你知道查询索引字段有多强
SELECT user_id FROM `user_operation_log` LIMIT 1000000, 100;  -- 26.397s   0.019s

-- 然后再使用子查询代替偏移量 先定位偏移位置的 id，然后再查询数据
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10  -- 27s  0.2s