Mysql千万级数据测试

1、数据准备

1.1创建一个表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` smallint(6) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000011 DEFAULT CHARSET=utf8;

1.2插入数据

import java.sql.*;

public class InsertTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String name = "com.mysql.jdbc.Driver";
        final String url = "jdbc:mysql://127.0.0.1/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT";
        String user = "root";
        String password = "123456";
        Connection conn = null ;
        Class.forName(name); //指定连接类型
        conn = DriverManager.getConnection(url, user, password);
        if(conn!=null){
            System.out.println("数据库连接成功");
            insert(conn);
        }else{
            System.out.println("数据库连接失败");
        }
    }

    public static void insert(Connection conn){
        String prefix = "INSERT INTO user (name,age,address,create_date,remark) VALUES " ;
        StringBuffer suffix = new StringBuffer();
        PreparedStatement preparedStatement =null;
        long start = System.currentTimeMillis();
        try {
            conn.setAutoCommit(false);
            for(int i=1;i<=10000;i++){
                suffix = new StringBuffer();                 // 第j次提交步长
                for(int j=1;j<=1000;j++){
                    // 构建SQL后缀
                    suffix.append("('"+i*j+"','"+i+"','"+(j*i+j)+"','"+new Date(System.currentTimeMillis())+"','备注"+i+"'),");
                }
                String sql = prefix + suffix.substring( 0 , suffix.length() - 1 );
//                System.out.println(sql);
                preparedStatement = (PreparedStatement)conn.prepareStatement(sql);
                preparedStatement.addBatch();
                preparedStatement.executeBatch();
                conn.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("共计用时：" +  (end - start));
    }
}

此处根据不同的电脑配置可能插入的时间不一样，我本机电脑用时95秒。

1.3 检查数据是否正确

2、索引的创建

2.1 主键索引

​ mysql默认给我们创建了一个主键索引

select * from user where id =11;

此处查询非常快。

2.2 条件查询

未创建索引

select * from where name='11';
#未创建索引时本机大概需要10秒。

创建索引

ALTER TABLE USER ADD INDEX index_user_name(name);

再次查询，毫秒级查询。

2.3 联合索引（多列索引）

ALTER TABLE USER ADD INDEX index_user_name_age(name,age);

联合索引注意查询条件的一些顺序，最左匹配原则。

2.4 分页查询

mysql的分页是使用limit来实现，1000万数据的查询，每页10条，需要100万页，在查询的数据越靠后，时间越久。

select id from user 
limit 9989999,10
#本机测试10秒左右

优化1：

select * from user u
join(
select id from user 
limit 9000000,10 ) a
on u.id = a.id
#本机测试5秒

explain查看分析结果

优化2：

select * from user 
where id >=(select id from user limit 9989999,1) limit 10

explain查看分析结果

2中方式查询时间相差不大。

后记：后面会记录一下explain的特点和索引创建的一些注意事项。