数据库版本：
[root@mysqltest ~]# mysql -u root -p123456
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 389805
Server version: 5.1.73-community MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cacti |
| centreon |
| centreon_status |
| centreon_storage |
| mysql |
| syslog |
| test |
+--------------------+
8 rows in set (0.01 sec)
1 .创建实验使用的a b表
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table a (a1 char(10), a2 char(10), a3 char(10));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into a values ('1', '2', '3');
Query OK, 1 row affected (0.00 sec)
mysql> insert into a select * from a;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
。
。
。
。
mysql> insert into a select * from a;
Query OK, 4194304 rows affected (7.85 sec)
Records: 4194304 Duplicates: 0 Warnings: 0
mysql> insert into a select * from a;
Query OK, 8388608 rows affected (27.81 sec)
Records: 8388608 Duplicates: 0 Warnings: 0
2 创建b表：
mysql> create table b (b1 char (10), b2 char(10), b3 char(10));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into b select * from a;
Query OK, 16777216 rows affected (1 min 6.18 sec)
Records: 16777216 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> select * from b limit 10;
+------+------+------+
| b1 | b2 | b3 |
+------+------+------+
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
+------+------+------+
10 rows in set (0.04 sec)
3 加入b表不同样的数据
mysql> insert into b values ('4','5','6');
Query OK, 1 row affected (0.01 sec)
mysql> insert into b values ('4','5','6');
Query OK, 1 row affected (0.00 sec)
mysql> insert into b values ('4','5','6');
Query OK, 1 row affected (0.00 sec)
mysql> insert into b values ('4','5','6');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into b values ('4','5','6');
Query OK, 1 row affected (0.00 sec)
mysql> commit
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
4 查看a b表数据行数
mysql> select count(1) from b;
+----------+
| count(1) |
+----------+
| 16777224 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from a;
+----------+
| count(1) |
+----------+
| 16777216 |
+----------+
1 row in set (0.00 sec)
5 创建c表
mysql> create table c (c1 char (10), c2 char(10), c3 char(10));
Query OK, 0 rows affected (0.31 sec)
6 创建临时temp表
mysql> create table temp select * from c where 1=2;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from temp;
Empty set (0.00 sec)
mysql> desc temp;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | char(10) | YES | | NULL | |
| c3 | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)
7 为b表创建索引
mysql> create index ind_b_b1 on b(b1);
Query OK, 16777224 rows affected (2 min 9.14 sec)
Records: 16777224 Duplicates: 0 Warnings: 0
mysql> desc b;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| b1 | char(10) | YES | MUL | NULL | |
| b2 | char(10) | YES | | NULL | |
| b3 | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)
8 把a b表数据插入temp表中
mysql> insert into temp select * from a;
Query OK, 16777216 rows affected (29.84 sec)
Records: 16777216 Duplicates: 0 Warnings: 0
mysql> insert into temp select * from b;
Query OK, 16777224 rows affected (59.79 sec)
Records: 16777224 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(1) from temp;
+----------+
| count(1) |
+----------+
| 33554440 |
+----------+
1 row in set (0.00 sec)
9 创建联合索引 强制索引去掉重复数据
mysql> create index ind_temp_c123 on temp(c1, c2, c3);
Query OK, 33554440 rows affected (6 min 57.80 sec)
Records: 33554440 Duplicates: 0 Warnings: 0
mysql>
mysql>
mysql> explain select c1, c2, max(c3) from temp force index (ind_temp_c123) group by c1, c2;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | temp | range | NULL | ind_temp_c123 | 22 | NULL | 3 | Using index for group-by |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from temp;
+----------+
| count(*) |
+----------+
| 33554440 |
+----------+
1 row in set (0.00 sec)
mysql> explain select c1, c2, c3 from temp force index (ind_temp_c123) group by c1, c2;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | temp | range | NULL | ind_temp_c123 | 22 | NULL | 3 | Using index for group-by |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> insert into c select c1, c2, max(c3) from temp force index (ind_temp_c123) group by c1, c2;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
10 去重复后c表的数据
mysql> select * from c;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
+------+------+------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from temp order by c1 desc limit 10;
+------+------+------+
| c1 | c2 | c3 |
+------+------+------+
| 4 | 5 | 6 |
| 4 | 5 | 6 |
| 4 | 5 | 6 |
| 4 | 5 | 6 |
| 4 | 5 | 6 |
| 4 | 5 | 6 |
| 4 | 5 | 6 |
| 4 | 5 | 6 |
| 1 | 2 | 3 |
| 1 | 2 | 3 |
+------+------+------+
10 rows in set (0.00 sec)
mysql> Ctrl-C -- exit!
Aborted
11 删除表 temp
mysql> drop table temp;
Query OK, 0 rows affected (1.59 sec)
mysql> drop table a;
Query OK, 0 rows affected (0.55 sec)
mysql> drop table b;
Query OK, 0 rows affected (0.73 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| c |
+----------------+
1 row in set (0.00 sec)
mysql>
- 本文来自：Linux学习教程网