优化技巧 如何快速商品分页

创建测试表

-- auto-generated definition
create table test
(
    id  int          not null,
    var varchar(255) not null
);

程序生成1百万数据

    private static void generateTestSqlFile() {
        FileWriter fileWriter = null;
        BufferedWriter bufferedWriter = null;
        try {

            fileWriter = new FileWriter("/Users/xmc/batchTest.csv");
            bufferedWriter = new BufferedWriter(fileWriter);
            for (int i = 0; i < 10_000_000; i++) {
                String uuid = UUID.randomUUID().toString();
                //为什么采用CSV文件导入方式而不采用程序批量插入SQL
                // TODO: 因为程序插入带事务执行比文件慢
                bufferedWriter.write(i + "," + uuid + "\n");
            }


        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (fileWriter != null) {
                try {
                    bufferedWriter.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (bufferedWriter != null) {
                try {
                    fileWriter.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("执行完毕...");
        }
    }

navicat mysql 导入CSV数据

IDEA 导入CSV文件

为创建索引查询执行耗时

select * from test where id >=50 limit 100; #160ms
select * from test where id >=5000 limit 100;# 134ms
select * from test where id >=500000 limit 100;# 278ms
select * from test where id >=5000000 limit 100; # 2s 633ms
select * from test where id >=5000000 and id<= 5000000 +100 # 6s 201ms

优化办法1 为主键创建索引

为主键添加索引后的执行耗时

#创建索引
create index test_id_index on test (id);


select * from test where id >=50 limit 100; #160ms
select * from test where id >=5000 limit 100;# 134ms
select * from test where id >=500000 limit 100;# 150ms
select * from test where id >=5000000 limit 100; # 153ms
--- 主键连续优化方案
select * from test where id >=5000000 and id<= 5000000 +100 # 173ms

优化办法2 通过表连接提升查询效率

MySQL root@(none):demo> select * from test  limit 9000000 ,10;# 4s 759ms
+---------+--------------------------------------+
| id      | var                                  |
+---------+--------------------------------------+
| 9000000 | c6cf9c99-9596-44c0-a3c9-a8b3cd5b4e0d |
| 9000001 | 97c60251-1b01-4aeb-9f64-2c785059e486 |
| 9000002 | 0050bbb1-85a7-46c0-bae9-934eae68d46a |
| 9000003 | ae17397c-0dee-4c48-859e-d7c1a3a7bcc3 |
| 9000004 | 322d8c3f-f20b-4ffc-9927-49c4783a516e |
| 9000005 | 42cf1db7-c0b5-489f-9180-2d98903ae2a1 |
| 9000006 | 46ec0789-1e28-441e-99a4-5e138e1b0a99 |
| 9000007 | 9be7ebb8-da30-4790-aece-b5333f7fc9df |
| 9000008 | 7d2d4687-eb79-4edd-8088-ca7ef1b52d4d |
| 9000009 | 4fbf6776-0a02-4952-a86e-92953a4f0df6 |
+---------+--------------------------------------+
10 rows in set
Time: 5.101s
MySQL root@(none):demo>

提升0.6ms

MySQL root@(none):demo> select *
                     -> from test t
                     -> join
                     -> (select id from test  limit 9000000 ,10)tmp
                     -> on t.id = tmp.id # 4s 645ms
+---------+--------------------------------------+---------+
| id      | var                                  | id      |
+---------+--------------------------------------+---------+
| 9000000 | c6cf9c99-9596-44c0-a3c9-a8b3cd5b4e0d | 9000000 |
| 9000001 | 97c60251-1b01-4aeb-9f64-2c785059e486 | 9000001 |
| 9000002 | 0050bbb1-85a7-46c0-bae9-934eae68d46a | 9000002 |
| 9000003 | ae17397c-0dee-4c48-859e-d7c1a3a7bcc3 | 9000003 |
| 9000004 | 322d8c3f-f20b-4ffc-9927-49c4783a516e | 9000004 |
| 9000005 | 42cf1db7-c0b5-489f-9180-2d98903ae2a1 | 9000005 |
| 9000006 | 46ec0789-1e28-441e-99a4-5e138e1b0a99 | 9000006 |
| 9000007 | 9be7ebb8-da30-4790-aece-b5333f7fc9df | 9000007 |
| 9000008 | 7d2d4687-eb79-4edd-8088-ca7ef1b52d4d | 9000008 |
| 9000009 | 4fbf6776-0a02-4952-a86e-92953a4f0df6 | 9000009 |
+---------+--------------------------------------+---------+
10 rows in set
Time: 4.525s
MySQL root@(none):demo>

优化办法3 业务上限定不可以查询早期数据。