Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the common storage engines of MySQL are, which can be used for reference by interested friends. I hope you can learn a lot after reading this article.

MyISAM of common storage engines in MySQL

Properties:

1. Concurrency and lock level

2. Repair of table damage

Check table tablename

Repair table tablename

3. Types of indexes supported by the MyISAM table

①, full-text index

②, prefix index

4. MyISAM table supports data compression.

Myisampack

Restrictions:

Version

256TB is supported by default in MySQL5.0.

Applicable scenarios:

1. Non-transactional applications

2. Read-only applications

3. Spatial applications

Innodb of common storage engines in MySQL

Characteristics of Innodb storage engine

1. Innodb is a transactional storage engine

2. Fully support the ACID features of transactions

3. Redo Log and Undo Log

4. Innodb supports row-level locks

Innodb uses tablespaces for data storage

Create a separate tablespace store for each table

Innodb_file_per_table

ON: independent tablespace: tablename.ibd

OFF: system tablespace: ibdataX (X is a number, starting with 1)

How to choose system tablespace and independent tablespace

Compare:

Shrinking file size that cannot be picked up in the system tablespace

The independent table spacebar can shrink system files through the optimize table command

System tablespaces cause IO bottlenecks

Independent tablespaces can refresh data to multiple files at the same time

Steps for table transfer

Steps:

1. Use mysqldump for all database table data everywhere

2. Stop the MySQL service, modify the parameters, and delete the Innodb related files

3. Restart MySQL service and rebuild the tablespace of Innodb system.

4. Re-import data

CSV of MySQL's common storage engine

Storage characteristics of file system

1. Data is stored in the text as text

2. .csv file stores table contents

3. The .csm file stores the metadata of the table, such as table status and amount of data.

4. .frm file stores table structure information

5. Store in csv format

6. All columns must not be Null

7. Indexing is not supported

Applicable scenarios:

Suitable as an intermediate table for data exchange (spreadsheet-> csv file-> MySQL database directory)

Archive of common storage engines in MySQL

Storage characteristics of file system

1. Use zlib to compress the table data, and the disk Imax O is less.

2. The data is stored in a file with the suffix of ARZ

Features of Archive storage engine

1. Only insert and select operations are supported

2. Only indexes are allowed on self-incrementing ID columns

Applicable scenarios:

Log and data acquisition applications

Memory of common storage engines in MySQL

Storage characteristics of file system

1. It also becomes the HEAP storage engine, so the data is stored in memory.

Features:

1. Support HASH index and Btree index

2. All fields have a fixed length varchar (10) = char (10).

3. Large fields such as BLOG and TEXT are not supported.

4. Memory storage engine uses table-level locks

5. The maximum size is determined by the max_heap_table_size parameter

Applicable scenarios:

1. Use to find or map tables, such as zip codes and regions

2. It is used to save the intermediate table generated during the data distraction.

3. The result table used to cache periodically aggregated data

Federated of common storage engines in MySQL

Features:

1. Provides a method to access the table on the remote MySQL server

2. No data is stored locally, and all the data is placed on the remote server.

3. The table structure and the connection information of the remote server need to be saved locally.

How to use

Default is still. Enabling requires increasing the federated parameter at startup.

Mysql://user_name [: password] @ host_name [: port] / db_name/table_name

Applicable scenarios:

Occasional statistical analysis and manual inquiry

How to choose the right storage engine

Reference condition

1. Whether to support transactions

2. Back up regularly

3. Crash recovery

4. Unique characteristics of storage engine

Introduction to server parameters of Mysql

MySQL gets configuration information path

1. Command line parameters

Mysqld_safe-datadir=/data/sql_data

2. Configuration file

The command to view the configuration file:

[root@localhost ~] # mysqld-- help-- verbose | egrep-A 1 'Default options'

Valid path to the configuration file

/ etc/my.cnf / etc/mysql/my.cnf / usr/etc/my.cnf ~ / .my.cnf

Scope of MySQL configuration parameters

1. Global parameters

Set global parameter name = parameter valu

Set @ @ global. Parameter name: = parameter value

2. Session parameters

Set [session] parameter name = parameter valu

Set @ @ session. Parameter name: = parameter value

Parameters related to memory configuration

1. Determine the upper limit of memory that can be used

2. Determine the memory used for each connection to the MySQL

Sort_buffer_size

Join_buffer_size

Read_buffer_size

Read_rnd_buffer_size

3. Determine how much memory needs to be reserved for the operating system

4. How to allocate memory for the cache pool

Innodb_buffer_pool_size

Note: the criteria for setting the size of the cache pool are: total memory-(number of memory * connections required for each programming)-memory reserved by the system

Key_buffer_size

Select sum (index_length) from information_schema.tables where engines='myisam'

Configuration parameters related to Icano

Configuration related to Innodo I PUBO

Innodb_log_file_size the size of a single transaction log

Innodb_log_files_in_group controls the number of file days

Total transaction log size = Innodb_log_files_in_group * Innodb_log_file_size

Innodb_log_buffer_size = (32m or 128m)

Innodb_flush_log_at_trx_commint

0: write log to cache once per second and flush log to disk

1 [default]: log writes cache and flush log to disk every time a transaction is committed

2 [suggestion]: every time a transaction commits, log data is written to cache, and flush log is executed to disk once per second.

Innodb_flush_method=O_DIRECT

Innodb_file_per_table = 1

Innodb_doublewrite = 1

Configuration related to MyISAM I PUBO

Delay_key_write

OFF: refresh the dirty blocks in the key cache to disk after each write operation

ON: use delayed refresh only for tables where the delay_key_write option is specified when the table is keyed

ALL: use deferred build writes for all MyISAM tables

Security-related configuration parameters

Expire_logs_days specifies the number of days to automatically clean up binlog

Max_allowed_packet controls the size of packets that MySQL can connect to. It is recommended to set it to 32m. If master-slave replication is used, the parameters should be set to consistent.

Skip_name_resolve disables DNS lookup

Sysdate_is_now ensures that sysdate () returns a guaranteed date

Read_only forbids users with non-super permissions to write. Note: it is recommended to enable this function in the slave library in master-slave replication. To ensure that operations in the slave library cannot be modified and can only be synchronized from the master library

Skip_slave_start disables Salve automatic recovery (used from settings in the library)

Sql_mode sets the SQL mode used by MySQL (caution may cause MySQL to fail to execute)

If the data given by ① strict_trans_tables cannot be inserted into the database, it will operate at the end of the transaction engine and has no effect on the non-transaction engine.

When ② no_engine_subitiution specifies engines in create table, if the engine is not available, the default engine will not be used to build the table

③ no_zero_date cannot insert the date of 0th of April 2000 into the table

④ no_zero_in_date does not accept a partial date of 0

⑤ noly_full_group_by

Other commonly used configuration parameters

Sync_binlog controls how MySQL refreshes binlog to disk

Tmp_table_size and max_heap_table_size control temporary memory table size (should not be set too large to avoid memory overflow)

Max_connections controls the maximum number of connections allowed (default is 100, which is a little small, resize appropriately according to your own business)

What affects performance

The influence of Database Design on performance

1. Excessive denormalization of tables to create too many columns

2. Excessive normalization results in too many table associations (the associated tables are controlled within 10 as many as possible)

3. Use non-forward-blocking partition tables in OLTP environment

4. Use foreign keys to ensure the integrity of data

Thank you for reading this article carefully. I hope the article "what are the common storage engines of MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.