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

See what storage engines are supported by the current mysql database:

Mysql > show engines

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 170700

Current database: * * NONE * *

Note:

1. The status of support is default,yes,no,disabled. No indicates that the engine is not available, and disabled indicates that the engine is supported, but is currently disabled.

2. Transactions indicates whether transactions are supported.

3. XA indicates whether distributed transactions are supported.

4. Savepoints indicates whether SavePoint is supported.

The more common storage engines included in mysql are: MyISAM,Innodb,NDB Cluster,Memory,Archive,Merge,Federated and so on. Among them, the most famous and extensive one is MyISAM,Innodb.

1) MEMORY engine

The table of the MEMORY storage engine only has a separate disk file with the extension .frm, which is used to store the definition of the table structure and does not contain data.

The data of the MEMORY engine table is stored in memory, and the mysql server needs to allocate the memory area to it separately. The memory assigned to it will not be released under normal conditions and will always be held. The associated memory is not freed unless the entire MEMORY table is deleted or rebuilt.

If you delete only the data in the MEMORY engine table, the freed memory will be used by the newly inserted data in the table and cannot be used by other objects or threads.

When the mysql service is turned off, the data in the MEMORY engine table is lost and the table structure is preserved.

The maximum memory consumed by the MEMORY engine table is determined by the parameter max_heap_table_size system variable. The value is 16m by default. You can set different maximum memory for different MEMORY engine tables. However, when the mysql service is restarted, the maximum memory of all MEMORY engine tables will be overwritten by the value of the system variable.

-set the maximum memory to 1m.

Set max_heap_table_size=1024*1024

Create tabele test1 (user varchar (10)) engine=memory

-set the maximum memory to 2MB.

Set max_heap_table_size=1024*1024*2

Create tabele test1 (user varchar (10)) engine=memory

In addition, you can limit the memory used by the MEMORY engine table by specifying the value of the max_rows key when you create the MEMORY engine table.

2) csv storage engine

The csv storage engine stores data based on csv format files.

All columns of the csv storage engine table must be forced to specify NOT NULL, and the csv storage engine table does not support indexes and partitions.

The csv storage engine table contains a table structure definition file with a .frm extension, a data file with a .CSV extension, and a meta-information file with the same name, with a .CSM extension, to hold the table state and the amount of data stored in the table.

The data file of .CSV is a flat text file in CSV format and can be opened with excel.

If the contents of the .CSV file are corrupted, you can use the CHECK TABLE or REPAIR TABLE command to check and recover.

The CHECK TABLE command is used to check whether each line in the file is legal, and if an illegal line is found, an exception is thrown.

The REPAIR TABLE command is used to repair the file and remove the corrupted data from the file. Note that it clears all rows of records after the occurrence of corrupted rows, regardless of whether or not subsequent rows have legitimate data.

3) ARCHIVE storage engine

Objects based on the ARCHIVE storage engine that can compress and store large amounts of data, using the zlib lossless data compression algorithm, and can also be packaged into a smaller format using OPTIMIZE TABLE parsing tables.

The ARCHIVE storage engine is characterized by high insertion efficiency and small space for data storage.

Currently, the ARCHIVE storage engine only supports insert and select statements, not update,delete,replace statements. General columns such as order by operations and BLOB columns are supported. Row-level locking can be used, but indexes are not supported.

The ARCHIVE storage engine has .frm structure definition files, as well as data files with the .arz extension. A file with the .arn extension may also appear when performing the optimization operation.

4) BLACKHOLE engine

Although the BLACKHOLE engine can accept data like other engines, the data is not saved. The BLACKHOLE engine table is always empty. However, the engine table supports various indexes.

Although there is no data in the BLACKHOLE engine table, if binlog is enabled, the sql statements executed are actually logged.

The BLACKHOLE engine table has only one file in .frm format that is used to save the table definition.

5) MRG_MYISAM storage engine

Also known as the MERGE storage engine, it aggregates a set of MyISAM tables together and uses them like a table to simplify query operations.

MRG_MYISAM stores the engine table, which requires the base table to have the same column and index information, and the column definition and order, and the order of the index must be exactly the same. Otherwise, it will not be able to come together.

MRG_MYISAM storage engine table itself does not store data, only a summary function, but it not only supports query operations, but also supports insert, modify, delete operations. It operates in the same way as a normal MyISAM table.

Just when inserting, you need some configuration to indicate which MyISAM table to insert data into. This configuration is the INSERT_METHOD option when creating the MRG_MYISAM storage engine table.

The INSERT_METHOD option has three values, which are NO,FIRST,LAST. NO indicates that insertion is not allowed and is the default value. FIRST is the first table inserted. LAST is the last table inserted.

6) FEDERATED storage engine

Similar to database link in oracle. The default mysql installation does not install the FEDERATED storage engine. If you need to use this engine, add the parameter-DWITH_FEDERATED_STORAGE_ENGINE when compiling and installing. And when the mysql service is started

Additional parameter-FEDERATED. Or modify the my.cnf configuration file, add the FEDERATED line, and restart the mysql service.

FEDERATED stores the engine table, only the table structure, and its physical data comes from the remote mysql server. Each FEDERATED storage engine table contains two elements: a remote mysql database table and a local database table.

When the FEDERATED storage engine table is created, the connection information needs to be specified through the CONNECTION option. The CONNECTION option can be written in two ways: one through a string and the other through a CREATE SERVER statement. The server information created by the CREATE SERVER statement is saved in the mysql.servers table.

7) MyISAM storage engine

Each MyISAM table object consists of three separate files with .frm, .MYD, and .MYI extensions. The .frm file stores the structure of the table object, the .MYD stores the table data, and the .MYI stores the index information of the table.

No matter how many CHAR/VARCHAR type columns are included in the MyISAM engine table, the total length of these columns cannot exceed 65535B, and the maximum length of a single table character column cannot exceed 65532B.

The main advantages of MyISAM engine are fast, fast query and fast write. However, the disadvantages are obvious: transactions are not supported, and the lock granularity is too coarse (table-level locks), which is not suitable in OLTP scenarios.

When storing data, the MyISAM engine table supports three storage formats: FIXED (fixed length or static), DYNAMIC (dynamic), and COMPRESSED (compression).

A static format table is a table that does not contain columns of variable length, such as varchar/varbinary/blob/text, etc., and each column defined holds a fixed number of bytes. Or specify ROW_FORMAT=FIXED when you create it. Advantages: simple, the fastest to find data, it is best to recover after a crash. Disadvantages: waste of space.

A dynamic format table is the opposite of a static format table, or ROW_FORMAT=DYNAMIC is specified when it is created. Except for columns whose string length is less than 4, the length of other character columns is dynamic. Advantages: save space. Disadvantages: the query is relatively slow, and there will be more storage space debris. It is troublesome to recover after a crash.

(the myisamchk tool: the OPTIMIZE TABLE or myisamchk-r commands are used to remove fragments; myisamchk-ei is used to query table statistics; and the myisamchk-ed command is used to query the number of links to table objects. )

Compressed format table: creation can only be created with myisampack, decompress with the myisamchk command. Compressed tables can only be read, and records cannot be added or modified.

8) InnoDB storage engine

InnoDB has its own independent cache pool, and the corresponding parameter is the innodb_buffer_pool_size system variable, similar to SGA_TARGET in the oracle database, where commonly used data, including indexes, are cached.

Lock granularity is at the row level, providing consistent reads. Support transactions. Foreign key constraints are supported. Data encryption is supported. Support query, data, index caching. Backup / restore to point in time is supported.

The InnoDB storage engine table must create a primary key. It is generally chosen to use the columns that often need to be queried as the primary key, and if there are no suitable columns, the primary key is created to automatically grow columns.

InnoDB can handle multiple sessions and read and write the same object.

InnoDB has two tablespace modes: system tablespace and multiple tablespaces.

The physical files corresponding to the InnoDB system tablespace are set by the system variable innodb_data_file_path:

Mysql > show variables like "innodb_data%"

+-+

| | Variable_name | Value |

+-+

| | innodb_data_file_path | ibdata1:12M:autoextend |

| | innodb_data_home_dir |

+-+

2 rows in set (0.00 sec)

By default, InnoDB data files are stored in the data directory of mysql. If you want to change the directory, you can set it through the system variable innodb_data_home_dir.

Start the innodb_file_per_table option to enable multiple tablespaces so that the data and indexes in the table are saved in separate files rather than in the system tablespace.

When set to multiple tablespaces, tables whose truncate storage engine is InnoDB are very fast, and the free space can be used by the operating system.

In mysql5.6, the innodb_file_per_table option is enabled by default.

Mysql > show variables like "innodb_file%"

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_file_format | Antelope |

| | innodb_file_format_check | ON |

| | innodb_file_format_max | Antelope |

| | innodb_file_per_table | ON |

+-+ +

4 rows in set (0.00 sec)

In addition to the concept of table space, InnoDB also has its own dedicated log file, that is, REDOLOG logs.

By default, the InnoDB engine creates two sets of log files, both 5m in size, named ib_logfile0 and ib_logfile1.

The system parameters for InnoDB engine logs are as follows: the save path of innodb_log_group_home_dir redo log. The default is under the path specified by the datadir variable.

Innodb_log_file_size. The size of each redo log cannot exceed 512G.

Innodb_log_files_in_group specifies the number of log filegroups. The default is two, and the maximum is 100.

Adjusting the InnoDB engine log is complex because these parameters are static and depend on the parameter innodb_fast_shutdown.

The innodb_fast_shutdown parameter is used to control the shutdown mode of InnoDB, which has three values:

The 0 0 mode is similar to oracle's shutdown normal.

The 1 / 1 mode is similar to oracle's shutdown immediate, with a default value of 1

22 mode is similar to oracle's shutdown abort

The InnoDB engine will definitely need a rollback segment because it supports transactions. For the rollback segment of the InnoDB engine, there are two parameters: innodb_undo_directory specifies the physical file location of the UNDO log

Innodb_undo_tablespaces specifies the number of UNDO tablespaces, each of which is a separate .idb file

Innodb_undo_logs specifies the number of rollback segments in the undo tablespace

Once a undo tablespace is created, it cannot be deleted. So once the undo tablespace is used, the version of mysql cannot be lowered from 5.6.

By default, clients connected to the MYSQL service are in autocommit mode, that is, each DML execution commits.

How do I start transaction support for the InnoDB engine? There are two ways:

1. Disable the automatic commit of transactions:

Set the autocommit system variable to 0 or OFF. Later, it is up to the user to decide whether to commit or roll back the transaction.

Autocommit is a session-level variable that is only for the current session and cannot take effect globally.

Mysql > show variables like "autocommit%"

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | ON |

+-+ +

1 row in set (0.00 sec)

2. Explicitly declare the transaction, through the statement start transaction statement.

The tables in the mysql library are MYISAM engine tables by default, and the engine cannot be changed.

For the InnoDB engine table, the state information such as the number of recorded rows and row length returned by show table status is not accurate, it is just an estimate. It is best to use the count function for statistics.

The logical storage structure of InnoDB engine table is divided into page (pages) / block, extend / area, segment (segments), table space from small to large.

Page (pages) / block: basic unit. The default size is 16K, which can be set by parameter innodb_page_size variable. Available values are 4k, 8k, and 16k.

Extend / extent: each zone has a fixed size of 1m and is made up of pages.

Segments: in InnoDB, a segment actually refers to the data file corresponding to a separate tablespace.

Tablespace:

When the InnoDB engine saves data to the database, it automatically generates three internal columns: DB_TRX_IDG marks the identifier of the transaction, DB_ROLL_PRT is the rollback flag, and DB_ROW_ID is the row ID

The UNDO logs in the rollback segment are divided into insert UNDO logs and update UNDO logs.

Insert UNDO log: required only when the transaction is rolled back and is discarded after the transaction is committed

Update UNDO log: used to construct consistent reads.

The UNDO log record size in the rollback segment is generally smaller than the actual record size inserted or modified.

Purge thread: when a delete record is recorded, the record is not physically deleted, and the associated column and index records are physically removed only when InnoDB discards the update UNDO log. This removal operation is called purge.

Controlling purge thread latency can be set using the system variable innodb_max_purge_lag.

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.