MySQL 海量数据优化（理论+实战） 吊打面试官

一、准备表数据

咱们建一张用户表，表中的字段有用户ID、用户名、地址、记录创建时间，如图所示

OK，接下来准备写一个存储过程插入一百万条数据

CREATE TABLE `t_user` (
  `id` int NOT NULL,
  `user_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DELIMITER ;;
CREATE PROCEDURE user_insert()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<1000000
DO
INSERT INTO t_user(id, user_name, address,  create_time) VALUES (i, CONCAT('mayun',i), '浙江杭州', now());
SET i=i+1;
END WHILE ;
commit;
END;;
CALL user_insert();

插入完后咱们看看数据条数

二、优化方式

1.分页查询优化

OK，咱们看下分页limit到一定值时的耗时是多少

• limit 1000时

• limit 10000时

• limit 100000时

• limit 1000000时

可以看到limit值越大，耗时越长，这还只是一百万数据，要是千万级、亿级呢？

OK不废话，咱们马上进行分页优化

• 子查询优化

-

可以看到比起之前 limit 1000000时的0.218s 效率提高了很多

• 使用JOIN分页

-

可以看到比起之前 limit 1000000时的0.218s 效率也同样提高了很多

• 使用前一次查询的最大ID

可以看到这种方法效率最高，但依赖于需要知道最大ID，这种适合点击下一页查询（类似于滚动加载数据）的场景

• 通过伪列对ID进行分页

然后可以开启多个线程去进行最高效率查询语句的批量查询操作 0~10000，10001-20000.... 这样子的话可以快速把全量数据查询出来同步至缓存中。

分页优化总结：使用前一次查询的最大ID进行查询优化是效率最高的方法，但这种方法只适用于下一页点击的这种操作，对于同步全量数据来说建议的方式使用伪列对ID进行分页，然后开启多个线程同时查询，把全量数据加载到缓存，以后面试官问你如何  快速获取海量数据并加载到缓存 你该知道怎么回答了吧。

2.普通索引优化