数据库中千万数据常见问题以及如何解决(超详细,附导入数据教程)

public static void main(String[] args) {
		//procedure_partition_test2_188||100188||test_模块||test_模块_方法||SEARCH||SUCCESS||2020-10-27 19:38:54||2020-10-27 19:38:54
		String sdf = "yyyy-MM-dd HH:mm:ss";
		Date date = new Date();
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);

		SimpleDateFormat dateFormat = new SimpleDateFormat(sdf);
		File file = new File("C:\\Users\\12192\\Desktop\\load_data.txt");
		String center = "||100188||test_模块||test_模块_方法||SEARCH||SUCCESS||";
		long start = System.currentTimeMillis();
		System.out.println("start:[" + start + "]");
		try {
			PrintWriter pfp = new PrintWriter(file, "UTF-8");
			for (int j = 0; j < 30; j++) {
				String id_prefix = "procedure_partition_test" + j + "_";
				calendar.add(Calendar.DAY_OF_MONTH, -1);
				Date time = calendar.getTime();
				String yesterday = dateFormat.format(time);
				String start_datetime = yesterday;
				String end_datetime = yesterday;
				for (int i = 0; i < 300000; i++) {
					StringBuffer sb = new StringBuffer();
					sb.append(id_prefix).append(i).append(center).append(start_datetime).append("||").append(end_datetime);
					pfp.print(sb.toString() + "\n");
				}
			}
			pfp.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}

		long end = System.currentTimeMillis();
		System.out.println("end:[" + end + "]");
		System.out.println("耗时:[" + (end - start) / 1000 + "s]");

	}

set global local_infile=1;

show global variables like 'local_infile';

load data local infile 'C:\\Users\\12192\\Desktop\\load_data.txt' into table igs_sm_interface_access_log
CHARACTER SET utf8 -- 可选，避免中文乱码问题
FIELDS TERMINATED BY '||' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 \t
    OPTIONALLY ENCLOSED BY '' -- 文本限定符，每个字段被什么字符包围，默认是空字符
    ESCAPED BY '\\' -- 转义符，默认是 \
LINES TERMINATED BY '\n' -- 记录分隔符，如字段本身也含\n，那么应先去除，否则load data 会误将其视作另一行记录进行导入
(
    interface_request_seq,
    user_id,
    interface_access_func_name_cn,
    interface_access_method_name_cn,
    interface_access_method_type_name_en,
    interface_response_status_cd,
    begin_datetime,
    end_datetime
) -- 每一行文本按顺序对应的表字段，建议不要省略

select * from table where key > 1 and key < 100 and orther_key != 520;
//key是二级索引字段(非主键的索引),orther为非索引字段

select * from table where key > 1 and key < 100 and orther_key != 520 limt 100000, 100;
//key是二级索引字段(非主键的索引),orther为非索引字段