limit语句的优化

准备工作

创建一张测试表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `role` varchar(255) DEFAULT NULL,
  `d_1` varchar(255) DEFAULT NULL,
  `d_2` varchar(255) DEFAULT NULL,
  `d_3` varchar(255) DEFAULT NULL,
  `d_4` varchar(255) DEFAULT NULL,
  `d_5` varchar(255) DEFAULT NULL,
  `d_6` varchar(255) DEFAULT NULL,
  `d_7` varchar(255) DEFAULT NULL,
  `d_8` varchar(255) DEFAULT NULL,
  `d_9` varchar(255) DEFAULT NULL,
  `d_10` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_name` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3761001 DEFAULT CHARSET=utf8mb3;

SET FOREIGN_KEY_CHECKS = 1;

结构如下

往表里面插入3760000条数据

public class BatchInsertDb {
    public static void main(String[] args) throws Exception {
        //插入数据库
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/test_mysql?characterEncoding=utf8&serverTimezone=UTC&useSSL=false";
        String username="root";
        String password="wl990922";
        Connection conn = DriverManager.getConnection(url, username, password);

        String sql = "insert into user(username,password,address,role,d_1,d_2,d_3,d_4,d_5,d_6,d_7,d_8,d_9,d_10)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        for (int i = 0; i < 1000; i++) {
            pstmt.setString(1, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(2, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(3, "北京");
            pstmt.setString(4, "admin" + i);
            pstmt.setString(5, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(6, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(7, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(8, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(9, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(10, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(11, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(12, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(13, UUID.randomUUID().toString().replace("-", ""));
            pstmt.setString(14, UUID.randomUUID().toString().replace("-", ""));
            pstmt.addBatch();

        }
        pstmt.executeBatch();
        pstmt.close();
        conn.close();
        System.out.println("insert to db");
    }
}

limit优化

问题

在我们准备好表和数据以后，我们可以使用sql进行分页查询。

以下是两条查询语句，都是取40条数据，但性能就相差很大。

#查询结果是0.001秒
select * from user limit 0,40
#查询结果是2.9秒左右(不同电脑的性能不一样)
select * from  user limit 3740000,40

探究

上面的第二条sql执行的步骤如下：

• 从表中读取第N条数据添加到数据集中
• 重复第一步直到N=3740000+40
• 根据偏移量抛弃前面的3740000条数据,返回剩余的40条数据

​ 可以看到导致性能下降的原因是第二步，这前面的3740000条数据对本地查询没有任何意义，并且占用了大部分时间！

​ 首先我们要了解数据库并不是顺序的存储数据，一方面是因为计算机存储本身就是随机读写，另一方面是因为数据操作有很大的随机性，经过增删改查数据也会变得乱。

第一次优化（数据库中有自增的主键）

本次优化必须数据库中有自增的主键

select * from user where id > 3740000 limit 40

发现只用了0.002秒,这条sql语句是告诉数据库，我就要 3740000 条数据以后的40条数据。

第二次优化

对于没有自增主键的，只能以下sql优化了,username为索引列。

select * from user INNER JOIN (select username from user  LIMIT 3740000,40)b using (username)

以上是先用索引列来查询 limit防止全表扫描，然后左边查询右边的结果