MySQL 8.0导入样例数据库employees

1、下载

https://launchpad.net/test-db/employees-db-1/1.0.6

2、导入

[root@node1 app]# cd employees_db/
[root@node1 employees_db]# ll
总用量 164492
-rw-r--r-- 1 root root       752 11月 11 12:59 Changelog
-rw-r--r-- 1 root root      6460 11月 11 12:59 employees_partitioned2.sql
-rw-r--r-- 1 root root      7624 11月 11 12:59 employees_partitioned3.sql
-rw-r--r-- 1 root root      5660 11月 11 12:59 employees_partitioned.sql
-rw-r--r-- 1 root root      3861 11月 11 12:59 employees.sql
-rw-r--r-- 1 root root       241 11月 11 12:59 load_departments.dump
-rw-r--r-- 1 root root  13828291 11月 11 12:59 load_dept_emp.dump
-rw-r--r-- 1 root root      1043 11月 11 12:59 load_dept_manager.dump
-rw-r--r-- 1 root root  17422825 11月 11 12:59 load_employees.dump
-rw-r--r-- 1 root root 115848997 11月 11 12:59 load_salaries.dump
-rw-r--r-- 1 root root  21265449 11月 11 12:59 load_titles.dump
-rw-r--r-- 1 root root      3889 11月 11 12:59 objects.sql
-rw-r--r-- 1 root root      2211 11月 11 12:59 README
-rw-r--r-- 1 root root      4455 11月 11 12:59 test_employees_md5.sql
-rw-r--r-- 1 root root      4450 11月 11 12:59 test_employees_sha.sql
[root@node1 employees_db]# vi employees.sql 

set default_storage_engine = InnoDB;
-- set storage_engine = MyISAM;
-- set storage_engine = Falcon;
-- set storage_engine = PBXT;
-- set storage_engine = Maria;

select CONCAT('storage engine: ', @@default_storage_engine) as INFO;

[root@node1 employees_db]# mysql -t -uroot -p <employees.sql
Enter password: 
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+
[root@node1 employees_db]# 

mysql> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)

mysql> 

mysql> show create table titles \G
*************************** 1. row ***************************
       Table: titles
Create Table: CREATE TABLE `titles` (
  `emp_no` int NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  KEY `emp_no` (`emp_no`),
  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>

mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+
1 row in set (0.23 sec)

mysql> 

3、导入分区

从MySQL 5.1开始支持分区

[root@node1 employees_db]# mysql -t -uroot -p <employees_partitioned.sql
Enter password: 
+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+
[root@node1 employees_db]# 

mysql> show create table titles \G
*************************** 1. row ***************************
       Table: titles
Create Table: CREATE TABLE `titles` (
  `emp_no` int NOT NULL,
  `title` varchar(50) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date DEFAULT NULL,
  PRIMARY KEY (`emp_no`,`title`,`from_date`),
  KEY `emp_no` (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`from_date`))
(PARTITION p01 VALUES LESS THAN (1985) ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN (1986) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (1987) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (1988) ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN (1989) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN (1991) ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN (1992) ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN (1993) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (1994) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (1996) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (1997) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (1998) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (1999) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (2001) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN (2002) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> 

注意：

• 分区表查询时，需要按照分区键+条件进行过滤，才能发挥分区表的特性。
• 如果按照非分区键的索引查询，非分区表的性能要好于分区表
• 结合业务特性建立分区表，一定要结合实际情况