Mysql中千万数据查询浅析

最新推荐文章于 2024-09-03 18:01:14 发布

ldcaws

最新推荐文章于 2024-09-03 18:01:14 发布

阅读量2.7k

点赞数 2

分类专栏： mysql 文章标签： mysql 数据库千万条数据

本文链接：https://blog.csdn.net/leijie0322/article/details/130816079

版权

mysql 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

假如mysql数据库中有一千万数据，如何进行查询，查询效率如何，下面进行简单的分析。

1、准备数据

也许有些人没遇见过上千万数据量的表，没关系，下面通过sql脚本准备一下数据，环境为：mysql5.7.+

1.1、创建表脚本

CREATE TABLE `b_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;

1.2、创建数据脚本

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
 set @execSql = 'INSERT INTO `test`.`b_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 ;

采用批量插入，效率会快很多，而且每1000条数就commit；数据量太大，会导致批量插入效率慢。本次完成数据插入花费时间: 2757.472s，大概46min，占用硬盘12GB大小，以供参考。

2、数据测试

执行存储过程添加数据挺耗时间的，大家可以观察磁盘大小的变化和所用时间。
统计表中数据记录数

SELECT count(1) FROM `b_log`

返回结果为：
在这里插入图片描述

三次查询时间分别为：15.384s，16.050s，15.413s

2.1、普通分页查询

MySQL 支持 LIMIT 语句来选取指定的条数数据，MySQL分页查询语法如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

第一个参数指定第一个返回记录行的偏移量（可选，非必填），第二个参数指定返回记录行的最大数目；
下面我们开始测试查询结果：

SELECT * FROM `b_log` LIMIT 10000, 10

查询3次时间分别为：78ms，30ms，31ms
这样看起来速度还行，不过是本地数据库，速度自然快点。

2.2、相同偏移量，不同数据量

SELECT * FROM `b_log` LIMIT 10000, 10
SELECT * FROM `b_log` LIMIT 10000, 100
SELECT * FROM `b_log` LIMIT 10000, 1000
SELECT * FROM `b_log` LIMIT 10000, 10000
SELECT * FROM `b_log` LIMIT 10000, 100000
SELECT * FROM `b_log` LIMIT 10000, 1000000

查询时间如下：
28ms，30ms，27ms
31ms，29ms，30ms
40ms，36ms，34ms
101ms，81ms，83ms
508ms，696ms，680ms
5041ms，6556ms，7135ms
从上面结果可以看出，数据量越大，花费时间越长。

2.3、相同数据量，不同偏移量

SELECT * FROM `b_log` LIMIT 100, 100
SELECT * FROM `b_log` LIMIT 1000, 100
SELECT * FROM `b_log` LIMIT 10000, 100
SELECT * FROM `b_log` LIMIT 100000, 100
SELECT * FROM `b_log` LIMIT 1000000, 100

查询时间如下：
22ms，20ms，22ms
21ms，22ms，22ms
30ms，31ms，30ms
245ms，237ms，221ms
1757ms，1793ms，1809ms
从上面结果可以看出，偏移量越大，花费时间越长。

3、查询优化

针对偏移量大、数据量大两种情况分析其优化方式。

3.1、偏移量大优化

采用子查询方式，可以先定位偏移位置的id，然后再查询数据

# 第一条
SELECT * FROM `b_log` LIMIT 1000000, 10
# 第二条
SELECT id FROM `b_log` LIMIT 1000000, 10
# 第三条
SELECT * FROM `b_log` WHERE id >= (SELECT id FROM `b_log` LIMIT 1000000, 1) LIMIT 10

查询结果分别如下：1808ms，1417ms，1492ms

从上面结果可以看出，第一条花费时间最长，第三条比第一条花费时间短。

采用id限定方式，要求id必须是连续递增，而且计算id的范围，使用between，如下

# 第一条
SELECT * FROM `b_log` WHERE id between 1000000 AND 1000100 LIMIT 100
# 第二条
SELECT * FROM `b_log` WHERE id >= 1000000 LIMIT 100

查询结果分别如下：21ms，20ms

从上面结果可以看出，查询变快了。

3.2、数据量大优化

# 第一条
SELECT * FROM `b_log` LIMIT 1, 1000000
# 第二条
SELECT id FROM `b_log` LIMIT 1, 1000000
# 第三条
SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `b_log` LIMIT 1, 1000000

查询结果分别如下：4861ms，3403ms，4975ms
从上面结果可以看出，减少不需要的列，查询时间明显变少了；第一条和第三条查询时间差不多，是因为mysql服务器和mysql客户端在同一台机器上，所以查询时间差不多，若服务器和客户端分开就会不一样。

4、SELECT *

为什么要禁止 SELECT *，主要有2点原因：

用 "SELECT * " 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担；
增大网络开销，* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。特别是MySQL和应用程序不在同一台机器，这种开销非常明显；

感兴趣的小伙伴可以试试~