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

Familiar with mysql database friends, will certainly like mysql's powerful plug-in storage engine, can support too many storage engines, when the current storage engine can not meet your needs, you can choose the appropriate engine according to your needs, copy the relevant files to the relevant path, or even do not need to restart the database to be used. It's really powerful.

1 common storage engines

Memory storage engine

The table structure information is stored on the hard disk in .frm format, and the data is stored in memory.

Formats such as blob text are not supported

Create a table structure

Table locks are supported

Support for B-tree index and hash indexing

Support for data caching

Fast insertion speed

The memory allocated to the memory engine table will not be released, it will be held by the table, and the deleted data will not be recycled and will be used by newly inserted data.

CSV storage engine

All columns must be defined as Not NULL

CSV engine does not support indexing and partitioning

File format .frm table structure information

CSV is the data file is the actual data.

.CSM error table status and data in the table

You can change the .csv file to change the data directly, and check table checks the repair table (Note: you can use repair table to load the data after manually changing the .csv file)

For example:

# create table structure storage engine for CSV create table csv2 (id int not null, name char (20) not null default "ZN") engine=csv charset utf8; # check table structure: mysql > desc csv1 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | int ( 11) | NO | | NULL | | name | char (20) | NO | | ZN | | +-+-+ 2 rows in set (0.02 sec) # insert data mysql > insert into csv1 values (3) 'linux'), (20, "MYSQL") Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > insert into csv1 values (8, "MYSQL"); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from csv1 +-manually change the file vim / var/lib/mysql/test/csv1.CSV # (other paths for RPM package installation depends on your own installation) 8, "MYSQL" 9, "linux" 99, "docker" 200 "baidu" 44, "openstack" 155,121 "facebook", "ansible" # checklist mysql > check table csv1 +-+ | Table | Op | Msg_type | Msg_text | +-+ | test.csv1 | check | error | Corrupt | +- -- + 1 row in set (0.03 sec) # repair table mysql > repair table csv1 +-+ | Table | Op | Msg_type | Msg_text | +-+ | test.csv1 | repair | status | OK | +-- -+ 1 row in set (0.05sec) # check repair mysql > check table csv1 +-+ | Table | Op | Msg_type | Msg_text | +-+ | test.csv1 | check | status | OK | +- -+ 1 row in set (0.03 sec) # check that data changes take effect mysql > select * from csv1 +-+-+ | id | name | +-+-+ | 9 | linux | | 99 | docker | | 200 | baidu | 44 | openstack | | 155 | facebook | | 121 | ansible | +-+ |

Note: check statement will check the CSV file delimiter is correct, the data column and the definition of the table structure is the same, found that illegal rows will throw an exception, in the use of repair, will try to copy legal data from the current CSV file, clear illegal data, but need to pay attention to repair found in the file has corrupted rows of records, then all the subsequent data is lost, regardless of whether it is legal or not.

ARCHIVE storage engine

Suitable for scene archiving

Columns that support large data compression inserts are compressed, and the ARCHIVE engine uses the Zlib lossless data compression algorithm

You can also use optimze table to parse tables and package them into smaller formats

Only insert and update statements are supported, but statements such as delete replace update truncate are not supported. Order by operation blob column types are supported.

Row-level locks are supported but indexes are not supported

The data file for the archive engine table file .frm definition file .arz, and a .arn file with an extension may also appear when performing the optimization operation.

Simple test:

First create a myisam storage engine table, insert data, and then create an ARCHIVE storage engine table insert data to check the size of its storage space.

# create test table and related data mysql > create table archive2 engine=myisam as select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.columns;Query OK, 3362 rows affected (0.10 sec) Records: 3362 Duplicates: 0 Warnings: 0mysql > select count (*) from archive2 +-+ | count (*) | +-+ | 3362 | +-+ 1 row in set (0.00 sec) # continue inserting data (execute multiple times) mysql > insert into archive2 select * from archive2;Query OK, 107584 rows affected (0.23 sec) # check data quantity mysql > select count (*) from archive2 +-+ | count (*) | +-+ | 860672 | +-+ 1 row in set (0.00 sec) # check data size mysql > show table status like "archive2"\ G * * 1. Row * * Name: archive2 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 860672 Avg_row_length: 53 Data_length: 45790208Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2017-05-16 13:35:26 Update_time: 2017-05-16 13:38:14 Check_time: NULL Collation: gbk_chinese_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) ERROR: No query specified# data size 45790208 newly created storage engine for archive type tables mysql > create table archive3 engine=archive as select * from archive2 Query OK, 860672 rows affected Records: 860672 Duplicates: 0 Warnings: 0mysql > select count (*) from archive3;+-+ | count (*) | +-+ | 860672 | +-+ 1 row in set (0.11 sec) check size mysql > show table status like "archive3"\ G * * 1. Row * Name: archive3 Engine: ARCHIVE Version: 10 Row_format: Compressed Rows: 860672 Avg_row_length: 6 Data_length: 5801647Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: 2017-05-16 13:42:35 Check_time: NULL Collation: gbk_chinese_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) size: 5801647

Compared with the result of 8 times difference in stored value, the gap is still very large.

BLACKGOLE storage engine

Is a relatively special storage engine, just write, but regardless of storage, although it can accept data like other storage engines, all data will not be saved. The BLACKGOLE storage engine is always empty, somewhat similar to / dev/null under Linux.

# try mysql > create table black engine=blackhole as select * from archive2;Query OK, 860672 rows affected (0.65 sec) Records: 860672 Duplicates: 0 Warnings: 0mysql > select * from black; Empty set (0.00 sec) mysql > insert into black select * from archive2;Query OK, 860672 rows affected (0.62 sec) Records: 860672 Duplicates: 0 Warnings: 0mysql > select * from black Empty set (0.00 sec) many tests found that it was so amazing that everything was inserted successfully, but the data could not be found, so it was a magical storage engine, right? Take a look

Many tests, the results are so magical, the insertion is successful, but can not find the data, this storage engine magical bar, see what is the use of this magical storage engine?

1. Although the BLACKHOLE storage engine does not save data, if binlog is enabled, the executed SQL statements are actually recorded, that is, they can be copied to the SLAVE side. As shown below:

Combined with the replicete-do-* or reolicate-ignore-* rules in the replication feature, the log can be filtered. Through this ingenious design, the same write can be achieved, but the data between the master and slave is inconsistent.

The insert trigger in the BLACKHOLE object fires according to the standard, but because the BLACKHOLE object is empty, it is absolutely impossible for UPdate and delete to fire, and there is no effect on the FOR EACH RAW statement in the trigger.

Other application scenarios:

Other applications:

Verify dump file syntax

Evaluate the impact of binary logs on the load by comparing the performance of disabling binary log files at the first level.

The BLACKHOLE storage engine supports transactions, commit transactions are written to binary logs, but rollbacks are not

BLACKHOLE Storage engine and self-incrementing column

The BLACKHOLE engine is a no-op no-action engine, and all operations on BLACKHOLE objects are ineffective. It takes so long to consider the behavior of self-incrementing columns. The engine does not automatically increase the self-incrementing values, nor does it actually save the state of the self-incrementing fields, which is important for replication.

Consider the following replication scenarios

1. The BLACKHOLE table on Master side has a self-increasing primary key column.

2. The Slave table storage engine is Myisam.

3. The insert operation of the Masterside to the table object does not clearly know the column value of the self-incrementing column.

In this scenario, the duplicate key error of the primary key column will occur on the Slave side. In the SBR mode of the given statement, the INSERT_ID of each event inserted is the same, so the replication will trigger the error of inserting the duplicate key.

In row-based replication mode, the column values returned by the engine are always the same, so there will be an error trying to insert the same value on the Slave side.

MySQL's plug-in storage engine is very functional, and it is also suitable for unused application scenarios. When you have an in-depth understanding of its principle, you can give play to the better performance of MySQL.

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.