Introduction to MySQL 5.5Storage engine 02/28 Update SLTechnology News&Howtos

Introduction to MySQL 5.5Storage engine

2025-02-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

The storage engine is a MySQL component that handles SQL operations for different types of tables.

InnoDB storage engine

Default and the most widely used storage engine. Support transactions, with crash-recovery features; support row-level locks; support primary keys and foreign keys.

InnoDB is a general storage engine with reliability and high performance in MySQL.

Advantages:

DML operations follow the ACID model (atomicity, consistency, isolation, and persistence), support transactions, and support crash-recovery features (when MySQL fails to restart, for InnoDB tables, uncompleted transactions will be redone through the redo log, and data that has been committed but not written to the data file will be rebuilt from doublewrite buffer) to protect the data.

InnoDB buffer pool caches accessed table and index information, and frequently used data is read directly from memory.

Inserts,update,deletes operations are optimized by a mechanism called change buffering. Not only does InnoDB allow multiple concurrent reads and writes to the same table, it also caches changed data and optimizes disk Imando O.

When a database runs long queries on large tables and repeatedly accesses peers of the same table, a feature called Adaptive Hash Index makes these queries faster, just as data is queried from a hash table.

Tables and related indexes can be compressed.

Indexes can be created and deleted with little impact on performance and availability.

You can quickly TRUNCATE off an file_per_table tablespace, freeing up disk space for use by the operating system without freeing up system tablespaces that can only be reused by InnoDB.

Support row-level locks and consistent reads to improve multi-user concurrency and performance.

Support primary keys to improve query performance.

To maintain the integrity of the data, InnoDB also supports foreign keys.

You can freely combine InnoDB tables with tables from other MySQL storage engines. For example, in a SQL, you can associate an InnoDB table with a memory table.

When dealing with a large amount of data, InnoDB engine can effectively play CPU efficiency and improve performance.

MyISAM storage engine

Table-level locks limit read-write performance, so this storage engine is commonly used in read-only or read-based Web site data and data warehouse configurations.

MyISAM tables have the following characteristics:

The values of all data are first stored in low bytes, which makes the machine storing the data independent of the operating system.

All numeric key values are first stored in high bytes, which makes the index more compressed.

Large files (63-bit file length) on the file system and operating system are supported.

The limit for the number of rows in the MyISAM table is (232) 2 (1.844E+19).

Each MyISAM table can create up to 64 indexes, and federated indexes support up to 16 fields.

The maximum key length is 1000 bytes, which can be recompiled from the source code. If you want a key to be longer than 250 bytes, you need to use a key block that is greater than the default 1024 bytes.

When rows are inserted sequentially into the MyISAM table, for example, if you use the AUTO_INCREMENT field, the index tree will be split and the high node will contain only one key value, which will improve the utilization of index tree space.

Internal processing of AUTO INCREMENT fields of each table is supported. MyISAM automatically updates the insert and update operations of this field. This makes the processing of AUTO INCREMENT field sequences faster (at least 10%). When the sequence is deleted, the highest value of the sequence is not reused.

When the delete operation and update and update operations of the MYISAM table exist at the same time, fragmentation will occur in the table, and dynamically allocating the size of rows can effectively reduce fragmentation. This is automatically achieved by the database by merging deleted adjacent rows together and extending deleted blocks.

The MyISAM table supports concurrent inserts. If a table has no free data blocks, you can insert new rows into the table while other threads are reading the table.

You can put data files and index files on different physical devices, which can improve the speed of reading and writing to the table.

-- specify different partition locations of partition tables under different paths

Mysql > create table t_partition (id int,name varchar (30), adate date) engine=myisam

-> partition by list (year (adate))

-> (

PARTITION p1999 VALUES IN (1995, 1999, 2003)

-> DATA DIRECTORY ='/ appdata/95/data'

-> INDEX DIRECTORY ='/ appdata/95/idx'

-> PARTITION p2000 VALUES IN (1996, 2000, 2004)

-> DATA DIRECTORY ='/ appdata/96/data'

-> INDEX DIRECTORY ='/ appdata/96/idx'

-> PARTITION p2001 VALUES IN (1997, 2001, 2005)

-> DATA DIRECTORY ='/ appdata/97/data'

-> INDEX DIRECTORY ='/ appdata/97/idx'

PARTITION p2002 VALUES IN (1998, 2002, 2006)

-> DATA DIRECTORY ='/ appdata/98/data'

-> INDEX DIRECTORY ='/ appdata/98/idx'

->)

Query OK, 0 rows affected (0.11 sec)

Mysql > insert into t_partition values (100th date date 2016-04-12')

ERROR 1526 (HY000): Table has no partition for value 2016

Mysql > insert into t_partition values (100th date date 1995-04-12')

Query OK, 1 row affected (0.00 sec)

Mysql > insert into t_partition values (200nd tomatoes date date 1997-04-12')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from t_partition

+-+

+-+

| | 100 | Neo | 1995-04-12 |

| | 200 | Tom | 1997-04-12 |

+-+

2 rows in set (0.06 sec)

[root@localhost data] # ls / appdata/95/data/

T_partition#P#p1999.MYD

[root@localhost data] # ls / appdata/95/idx/

T_partition#P#p1999.MYI

[root@localhost data] # ls / appdata/97/data/

T_partition#P#p2001.MYD

[root@localhost data] # ls / appdata/97/idx/

T_partition#P#p2001.MYI

[root@localhost data] # ls / appdata/98/idx/

T_partition#P#p2002.MYI

[root@localhost data] # ls / appdata/98/data

T_partition#P#p2002.MYD

Indexes can be created for BLOB and TEXT.

Index fields can include null values, with each key occupying 0 to 1 byte.

Each character field can use a different character set.

There is an identity in the MyISAM index file that determines whether the table is closed correctly. If mysqld starts the service with the-myisam-recover-options parameter, the MyISAM table will be checked automatically when the database is opened and repaired automatically when the MyISAM table is not closed correctly.

You can check the MyISAM table through the myisamchk tool.

You can compress the BLOB and VARCHAR fields through the myisampack tool.

True VARCHAR type is supported, and a VARCHAR field can store one or two bytes.

A table with a VARCHAR field can have a fixed or dynamic length.

-- create a test table

Mysql > create table t_myd5 (id int,v1 char (10)) engine=myisam

Query OK, 0 rows affected (0.83 sec)

Mysql > show table status like'tresume myd5'\ G

* * 1. Row *

Name: t_myd5

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 9851624184872959

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2016-04-13 06:03:53

Update_time: 2016-04-13 06:03:53

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.08 sec)

Mysql > create table t_myd6 (id int,v1 char (10)) row_format=fixed engine=myisam

Query OK, 0 rows affected (0.01 sec)

Mysql > show table status like'tresume myd6'\ G

* * 1. Row *

Name: t_myd6

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 9851624184872959

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2016-04-13 06:05:47

Update_time: 2016-04-13 06:05:47

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options: row_format=FIXED

Comment:

1 row in set (0.02 sec)

Mysql > create table t_myd7 (id int,v1 char (10)) row_format=dynamic engine=myisam

Query OK, 0 rows affected (0.06 sec)

Mysql > show table status like'tresume myd7'\ G

* * 1. Row *

Name: t_myd7

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2016-04-13 06:08:13

Update_time: 2016-04-13 06:08:13

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options: row_format=DYNAMIC

Comment:

1 row in set (0.00 sec)

In a table, the total length of the VARCHAR or CHAR field is 64K.

-- create a test table

Mysql > create table t_myd2 (id int) engine=myisam

Query OK, 0 rows affected (0.07 sec)

-- View the created data files, .frm files store table structure files, .MYI files store index files, and .MYD files store data files

[root@localhost fire] # ls-trl

Total 656

-rw-rw----. 1 mysql mysql 8556 Apr 12 00:28 t_myd2.frm

-rw-rw----. 1 mysql mysql 1024 Apr 12 00:28 t_myd2.MYI

-rw-rw----. 1 mysql mysql 0 Apr 12 00:28 t_myd2.MYD

MEMORY storage engine

Put all the data in memory (RAM), the application scenario: non-critical data to achieve fast access. This storage engine was originally called the HEAP engine. Its applications are decreasing, because InnoDB can keep most of the data in memory through buffer pool memory and is more reliable and secure; at the same time, NDBCLUSTER provides fast query based on key values for large data sets.

This storage engine is suitable for storing short, non-core data, and the data in memory will be lost when the MySQL server is stopped or restarted.

It can achieve fast data access and low latency, and can completely load the data into memory without causing the exchange of virtual memory pages of the operating system.

It is suitable for read-only or read-based data access mode (data updates are rare).

By default, it uses a hash index instead of a B+ tree index.

The maximum capacity of the memory table cannot exceed the system parameter max_heap_table_size. The default value is 16MB.

Although the Memory storage engine is very fast, there are still some limitations in its use. For example, it only supports table locks, has poor concurrency performance, and does not support TEXT and BLOB column types. Most importantly, variable length fields (varchar) are stored as fields (char), so memory is wasted. In addition, it is often overlooked that MySQL databases use the Memory storage engine as a temporary table to hold the intermediate result set (intermediate result) of the query. If the intermediate result set is larger than the capacity setting of the Memory storage engine table, or if the intermediate result contains TEXT or BLOB column type fields, the MySQL database converts it to the MyISAM storage engine table and stores it to disk. MyISAM does not cache data files, so the performance of the resulting temporary tables loses performance for queries.

CSV storage engine

The tables that use this storage engine are actually comma-separated text files. The CSV table lets you import and export data in CSV format. CSV tables cannot be indexed. You can use InnoDB tables during normal operations, and only use CSV tables during the export and export data phases.

-- create a test table

Mysql > create table t_csv1 (id int not null default 0meme v1 varchar (20) not null default'') engine=csv

Query OK, 0 rows affected (0.03 sec)

Mysql > insert into t_csv1 values (1)

Query OK, 1 row affected (0.01sec)

Mysql > insert into t_csv1 values (2)

Query OK, 1 row affected (0.00 sec)

-- View table structure files (.frm), metadata files (.CSM) and table data files (.CSV) generated in data files

[root@localhost firedb] # ls-trl

Total 172

-rw-rw----. 1 mysql mysql 8582 Apr 9 19:09 t_csv1.frm

-rw-rw----. 1 mysql mysql 35 Apr 9 19:11 t_csv1.CSM

-rw-rw----. 1 mysql mysql 12 Apr 9 19:11 t_csv1.CSV

You can check the validity of the data in the CSV table through the check statement, which scans the field delimiters to determine the correct fields and misformatted data.

Mysql > check table t_csv1

+-+

+-+

+-+

1 row in set (0.06 sec)

Corrupted CSV tables can be repaired with REPAIR statements. This operation will repair the limited data in the table, and the corrupted data in the table will be lost.

Mysql > repair table t_csv1

+-+

+-+

+-+

1 row in set (0.01 sec)

Archive storage engine

Tables using this storage engine have compact data arrangements that cannot be indexed for storing and querying historical, archived, or security audit information with large amounts of data.

Archive storage engine supports INSERT, REPLACE, and SELECT operations, does not support DELETE and UPDATE operations, and does not support sorting, BLOB fields.

The Archive storage engine uses row-level locks. The data in the inserted Archive table is compressed, and the Archive storage engine uses the zlib data compression method.

Create test tables with the same data in both tables, using 517696 bytes for tables using the Myisam engine and 68904 bytes for tables using the Archive engine

Mysql > create table t_mi1 engine=myisam as select * from information_schema.columns

Query OK, 509 rows affected (0.12 sec)

Records: 509 Duplicates: 0 Warnings: 0

Mysql > insert into t_mi1 select * from t_mi1

Query OK, 509 rows affected (0.00 sec)

Records: 509 Duplicates: 0 Warnings: 0

Mysql > insert into t_mi1 select * from t_mi1

Query OK, 1018 rows affected (0.01sec)

Records: 1018 Duplicates: 0 Warnings: 0

Mysql > insert into t_mi1 select * from t_mi1

Query OK, 2036 rows affected (0.01sec)

Records: 2036 Duplicates: 0 Warnings: 0

Mysql > show table status like 'tabilmi1'\ G

* * 1. Row *

Name: t_mi1

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 4072

Avg_row_length: 127

Data_length: 517696

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2016-04-11 23:55:41

Update_time: 2016-04-11 23:55:54

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.11 sec)

Mysql > create table t_arc1 engine=archive as select * from t_mi1

Query OK, 4072 rows affected (0.21 sec)

Records: 4072 Duplicates: 0 Warnings: 0

Mysql > show table status like 'tymarc1'\ G

* * 1. Row *

Name: t_arc1

Engine: ARCHIVE

Version: 10

Row_format: Compressed

Rows: 4072

Avg_row_length: 16

Data_length: 68904

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: NULL

Update_time: 2016-04-12 00:05:26

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.20 sec)

-- View the created table file. The definition file of the table is .frm file, and the file that actually stores data is .ARZ file.

[root@localhost fire] # ls-trl

Total 640

-rw-rw----. 1 mysql mysql 13552 Apr 12 00:05 t_arc1.frm

-rw-rw----. 1 mysql mysql 68904 Apr 12 00:05 t_arc1.ARZ

Blackhole storage engine

This storage engine accepts data insertion but does not store data, similar to / dev/null devices under Unix. Queries against the Blackhole table usually return empty values. The Blackhole table can be used in replicated configurations, and when DML statements are sent to the standby server, the primary server does not keep its own copy of the data.

The Blackhole storage engine supports a variety of indexes. The data inserted into the Blackhole table will not exist in this table, but if the database opens binary logging, the related SQL statements will be recorded and copied to the standby server. This feature is useful when using a MySQL database as a repeater or filter.

-- create a test table

Mysql > create table t_bl1 (I int,c char (10)) engine=blackhole

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into t_bl1 values (1 recording record one'), (2 recording recording two')

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > select * from t_bl1

Empty set (0.00 sec)

-- there are only definition files for tables in the data file

[root@localhost firedb] # ls-trl

Total 728

-rw-rw----. 1 mysql mysql 8578 Apr 10 06:01 t_bl1.frm

Merge storage engine

This storage engine, also known as the MRG_MyISAM storage engine, logically combines a series of MyISAM tables with the same columns and indexes into a single data object, which is useful for data warehouse environments. The Merge storage engine cannot be used when the order of the columns in the table to be combined is inconsistent. Corresponding to the Merge table is the partitioned table, which stores the data from a single table in different files.

-- create two Mysiam tables with the same structure

Mysql > create table t_mg1 (id int not null auto_increment primary key,v1 varchar (20)) engine=myisam

Query OK, 0 rows affected (0.06 sec)

Mysql > create table t_mg2 (id int not null auto_increment primary key,v1 varchar (20)) engine=myisam

Query OK, 0 rows affected (0.03 sec)

Mysql > insert into t_mg1 (v1) values ('This'), (' ls'), ('mysl')

Query OK, 3 rows affected (0.09 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > insert into t_mg1 (v1) values ('This'), (' ls'), ('mys2')

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > select * from t_mg1

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mysl |

| | 4 | This |

| | 5 | ls |

| | 6 | mys2 |

+-+ +

6 rows in set (0.00 sec)

Mysql > insert into t_mg2 (v1) values ('This'), (' ls'), ('mys3')

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > insert into t_mg2 (v1) values ('This'), (' ls'), ('mys4')

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > select * from t_mg1

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mysl |

| | 4 | This |

| | 5 | ls |

| | 6 | mys2 |

+-+ +

6 rows in set (0.00 sec)

Mysql > select * from t_mg2

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mys3 |

| | 4 | This |

| | 5 | ls |

| | 6 | mys4 |

+-+ +

6 rows in set (0.00 sec)

-- create a MERGE table and merge the two tables created previously

Mysql > create table t_mer1 (id int not null auto_increment primary key,v1 varchar (20)) engine=merge union= (tweemg1 and tweemg2)

Query OK, 0 rows affected (0.06 sec)

Mysql > select * from t_mer1

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mysl |

| | 4 | This |

| | 5 | ls |

| | 6 | mys2 |

| | 1 | This |

| | 2 | ls |

| | 3 | mys3 |

| | 4 | This |

| | 5 | ls |

| | 6 | mys4 |

+-+ +

12 rows in set (0.00 sec)

-- View the created build file

The table structure information is stored in the .frm, and which tables are stored in the .MRG. In fact, the data in the created MERGE table is still the data of the source table.

[root@localhost firedb] # ls-trl

Total 804

-rw-rw----. 1 mysql mysql 8582 Apr 10 07:10 t_mg1.frm

-rw-rw----. 1 mysql mysql 8582 Apr 10 07:10 t_mg2.frm

-rw-rw----. 1 mysql mysql 2048 Apr 10 07:11 t_mg1.MYI

-rw-rw----. 1 mysql mysql 120 Apr 10 07:11 t_mg1.MYD

-rw-rw----. 1 mysql mysql 2048 Apr 10 07:13 t_mg2.MYI

-rw-rw----. 1 mysql mysql 120 Apr 10 07:13 t_mg2.MYD

-rw-rw----. 1 mysql mysql 8582 Apr 10 07:15 t_mer1.frm

-rw-rw----. 1 mysql mysql 12 Apr 10 07:15 t_mer1.MRG

[root@localhost firedb] # cat t_mer1.MRG

T_mg1

T_mg2

Insert two records into the source table t_mg1, and the data will appear directly in the MERGE table t_mer1

Mysql > insert into t_mg1 values (8)

Query OK, 1 row affected (0.02 sec)

Mysql > insert into t_mg1 (v1) values ('car2')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from t_mg1

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mysl |

| | 4 | This |

| | 5 | ls |

| | 6 | mys2 |

| | 8 | car |

| | 9 | car2 |

+-+ +

8 rows in set (0.00 sec)

Mysql > select * from t_mer1

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mysl |

| | 4 | This |

| | 5 | ls |

| | 6 | mys2 |

| | 8 | car |

| | 9 | car2 |

| | 1 | This |

| | 2 | ls |

| | 3 | mys3 |

| | 4 | This |

| | 5 | ls |

| | 6 | mys4 |

+-+ +

14 rows in set (0.06 sec)

You can insert data into the MERGE table and use the insert_method attribute to determine which table of the source table to insert data into. Insert_method last represents the last source table.

Mysql > alter table t_mer1 insert_method last

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > select * from t_mg2

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mys3 |

| | 4 | This |

| | 5 | ls |

| | 6 | mys4 |

+-+ +

6 rows in set (0.00 sec)

Mysql > insert into t_mer1 (v1) values ('car5')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from t_mg2

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mys3 |

| | 4 | This |

| | 5 | ls |

| | 6 | mys4 |

| | 10 | car5 |

+-+ +

7 rows in set (0.00 sec)

Mysql > select * from t_mer1

+-+ +

| | id | v1 | |

+-+ +

| | 1 | This |

| | 2 | ls |

| | 3 | mysl |

| | 4 | This |

| | 5 | ls |

| | 6 | mys2 |

| | 8 | car |

| | 9 | car2 |

| | 1 | This |

| | 2 | ls |

| | 3 | mys3 |

| | 4 | This |

| | 5 | ls |

| | 6 | mys4 |

| | 10 | car5 |

+-+ +

15 rows in set (0.00 sec)

MariaDB [test] > create table payment_2006 (

-> country_id smallint

-> payment_date datetime

-> amount decimal (15jue 2)

-> key idx_fk_country_id (country_id))

-> engine=myisam

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > create table payment_2007 (

-> country_id smallint

-> payment_date datetime

-> amount decimal (15jue 2)

-> key idx__fk_country_id (country_id))

-> engine=myisam

Query OK, 0 rows affected (0.01 sec)

MariaDB [test] > create table payment_all (

-> country_id smallint

-> payment_date datetime

-> amount decimal (15jue 2)

-> index (country_id))

-> engine=merge union= (payment_2006,payment_2007) insert_method=last

Query OK, 0 rows affected (0.09 sec)

MariaDB [test] > insert into payment_2006 values (100000 for 2006-05-01), 150000 for 2006-08-15)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test] > insert into payment_2007 values (1 minus 2007-02-20), (2meme 2007-07-15)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test] > select * from payment_2006

+-+

+-+

| | 1 | 2006-05-01 00:00:00 | 100000.00 |

| | 2 | 2006-08-1500: 00:00 | 150000.00 | |

+-+

2 rows in set (0.00 sec)

MariaDB [test] > select * from payment_2007

+-+

+-+

| | 1 | 2007-02-20 00:00:00 | 35000.00 |

| | 2 | 2007-07-15 00:00:00 | 220000.00 | |

+-+

2 rows in set (0.00 sec)

MariaDB [test] > select * from payment_all

+-+

+-+

| | 1 | 2006-05-01 00:00:00 | 100000.00 |

| | 2 | 2006-08-1500: 00:00 | 150000.00 | |

| | 1 | 2007-02-20 00:00:00 | 35000.00 |

| | 2 | 2007-07-15 00:00:00 | 220000.00 | |

+-+

4 rows in set (0.00 sec)

Because the LAST method is used, inserting data into the MERGE table will insert data into the last table when the table is created.

MariaDB [test] > insert into payment_all values (3meme 2006-03-31)

Query OK, 1 row affected (0.00 sec)

MariaDB [test] > select * from payment_all

+-+

+-+

| | 1 | 2006-05-01 00:00:00 | 100000.00 |

| | 2 | 2006-08-1500: 00:00 | 150000.00 | |

| | 1 | 2007-02-20 00:00:00 | 35000.00 |

| | 2 | 2007-07-15 00:00:00 | 220000.00 | |

| | 3 | 2006-03-31 00:00:00 | 112200.00 |

+-+

5 rows in set (0.00 sec)

MariaDB [test] > select * from payment_2006

+-+

+-+

| | 1 | 2006-05-01 00:00:00 | 100000.00 |

| | 2 | 2006-08-1500: 00:00 | 150000.00 | |

+-+

2 rows in set (0.00 sec)

MariaDB [test] > select * from payment_2007

+-+

+-+

| | 1 | 2007-02-20 00:00:00 | 35000.00 |

| | 2 | 2007-07-15 00:00:00 | 220000.00 | |

| | 3 | 2006-03-31 00:00:00 | 112200.00 |

+-+

3 rows in set (0.00 sec)

MariaDB [test] > show keys from payment_2006

+- -- +

+- -- +

+- -- +

1 row in set (0.00 sec)

MariaDB [test] > show keys from payment_2007

+ -+

+ -+

+ -+

1 row in set (0.00 sec)

MariaDB [test] > show keys from payment_all

+ -- +

+ -- +

+ -- +

1 row in set (0.00 sec)

Federated storage engine

This storage engine provides the ability to connect to other MySQL servers to create logical databases, which is useful for distributed databases or data marts.

-- create a table on the target side

Mysql > create table poll_vote (id int (10) not null auto_increment,parents_id int (10) not null

-> vote_count mediumint (10) not null default 0 not null default 0 voteware monthcount mediumint (10)

-> vote_month mediumint (10) not null,primary key (id), unique key ind_poll_vote_baike (parents_id,vote_month)

-> engine=innodb auto_increment=26020 default charset=latin1

Query OK, 0 rows affected (0.03 sec)

Mysql > insert into poll_vote (parents_id,vote_count,vote_month_count,vote_month) values (10100100100)

Query OK, 1 row affected (0.07 sec)

-- create a table on the source side, and the structure is the same as the table structure on the target side

Mysql > create table poll_vote (id int (10) not null auto_increment,parents_id int (10) not null

-> vote_count mediumint (10) not null default 0 not null default 0 voteware monthcount mediumint (10)

-> vote_month mediumint (10) not null,primary key (id), unique key ind_poll_vote_baike (parents_id,vote_month)

-> engine=federated auto_increment=26020 default charset=latin1

-> connection='mysql://test:System#2013@192.168.78.137/fire/poll_vote'

Query OK, 0 rows affected (0.08 sec)

Mysql > select * from poll_vote

+-+

+-+

| | 26020 | 10 | 100 | 100 | 100 |

+-+

1 row in set (2.01 sec)

When you create a FEDERATED table, the table definition file (.frm file) exists locally, and the actual data file of the table is stored on the remote database server.

-- View the created table

[root@localhost fire] # ls-trl

Total 28

-rw-rw----. 1 mysql mysql 61 Apr 11 07:06 db.opt

-rw-rw----. 1 mysql mysql 8736 Apr 11 19:39 poll_vote.frm

Example of connection

Connection='mysql://username:password@hostname:port/database/tablename'

Connection='mysql://username@hostname/database/tablename'

Connection='mysql://username:password@hostname/database/tablename'

Create a table in a different way

If you create multiple FEDERATED tables on the same server, or if you want to simplify the process of creating FEDERATED tables, you can use the CREATE SERVER statement to define the server parameters to connect to.

Mysql > create server db_01 foreign data wrapper mysql

-> options (user 'test',password' System#2013', host '192.168.78.137, port 3306 database' fire')

Query OK, 1 row affected (0.07 sec)

Mysql > select * from mysql.servers\ G

* * 1. Row *

Server_name: db_01

Host: 192.168.78.137

Db: fire

Username: test

Password: System#2013

Port: 3306

Socket:

Wrapper: mysql

Owner:

1 row in set (0.00 sec)

Mysql > create table poll_vote_2 (id int (10) not null auto_increment,parents_id int (10) not null

-> vote_count mediumint (10) not null default 0 not null default 0 voteware monthcount mediumint (10)

-> vote_month mediumint (10) not null,primary key (id), unique key ind_poll_vote_baike (parents_id,vote_month)

-> engine=federated auto_increment=26020 default charset=latin1

-> connection='db_01/poll_vote'

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from poll_vote_2

+-+

+-+

| | 26020 | 10 | 100 | 100 | 100 |

+-+

1 row in set (0.08 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.