Mysql: Remove heavy data with forced index

Source: Internet
Author: User

Database version:

[Email protected]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) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names 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. Create a B table used in the experiment

mysql> use test;

Database changed

Mysql> Show tables;

Empty Set (0.00 sec)

Mysql> CREATE Table A (A1 char (TEN), A2 char (TEN), 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 CREATE TABLE B:

Mysql> CREATE TABLE B (B1 char (Ten), B2 char (Ten), 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 |

+------+------+------+

Rows in Set (0.04 sec)

3 Join B table not the same data

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 View a B table number of data rows

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 Creating a C table

Mysql> CREATE TABLE C (C1 char (TEN), C2 char (Ten), C3 char (10));

Query OK, 0 rows affected (0.31 sec)

6 Creating a temporary temp table

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 Creating an index for B table

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 Inserting a B table data into the temp table

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 Creating a federated index forcing the index to remove duplicate data

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 go to repeat after C table of data

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 |

+------+------+------+

Rows in Set (0.00 sec)

Mysql> Ctrl-c--exit!

Aborted

11 Delete Table 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>

    • This article is from: Linux Learning Tutorial Network

Mysql: Remove heavy data with forced index

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.