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

This article mainly explains "how to understand the MySQL storage engine". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn how to understand the MySQL storage engine.

Today I found a magical parameter:-site:xxxx.net

1. Selection of storage engine (table type) 1. Introduction of storage engine

The difference from most relational databases is that MySQL has the concept of a storage engine, and you can choose the most appropriate storage engine for different storage needs. The plug-in storage engine in MySQL is one of its major features. Users can choose how to store, whether to index, and whether to use transactions according to the needs of the application. Hey, you can also adapt the storage engine that best suits your business according to the business environment.

Oracle sensed a business opportunity, acquired MySQL, and has since had an enterprise version (business support). The community version can still be downloaded for free. Another great charm is also because of open source, the community is highly active and everyone can contribute. Next, we introduce several storage engines that are more used. There are no advantages and disadvantages of storage engines, but only who is more suitable for the corresponding production business environment.

The storage engines supported in MySQL5.0 are FEDERATED, MRG_MYISAM, MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, NDB Cluster, BDB, EXAMPLE, InnoDB (the default storage engine after MySQL5.5 and MariaDB10.2) and PERFORMANCE_SCHEMA (unconventional storage data engine). By comparing MySQL with the memory engine supported by MariaDB, you can see that Aria engine has been added to MariaDB:

View Storage Engin

Enter show engines\ G through the character interface included in MySQL login, or use the query-supported engines such as SQLyog, phpMyAdmin, MySQL workbench and other tools that support MySQL query. Only some of them are shown here:

[test@cnwangk ~] $mysql-uroot-pEnter password: mysql > show engines\ G * * 2. Row * Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO** 3. Row * * Engine: MyISAM Support: YES Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO** 6. Row * * Engine: MEMORY Support: YES Comment: Hash based Stored in memory, useful for temporary tablesTransactions: NO XA: NO Savepoints: NO** 8. Row * * Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES9 rows in set (0.00 sec)

Function description:

Engine: engine name (description)

Support: whether the storage engine is supported in the current version of the database, YES: yes, NO: no; Supports transactions, row-level locking, and foreign keys, literally translate this paragraph: support transactions, row-level locks and foreign keys

Comment: a detailed description of the storage engine, such as whether the engine supports transactions and foreign keys

Transactions: description of whether the storage engine supports transactions, YES: supported, NO: not supported

XA: whether it meets the XA specification. The XA specification is an open group specification for distributed transaction processing (DTP). YES: supported, NO: not supported

Savepoints: literally means save point, whether the control of things is supported, YES: yes, NO: not supported.

Whisper beep, if you can read some official English documents, this will help you to further understand the MySQL storage engine and develop the ability to read source code or documentation.

By the way, I would like to mention MySQL's sister MariaDB. The new built-in engine Aria used before MariaDB, the replica version of MySQL, and the default storage engine used after MariaDB10.2 is InnoDB, which is enough to see the excellence of the InnoDB storage engine. MariaDB's API and protocol are compatible with MySQL, and additional features have been added to support local non-blocking operations and progress reporting. This means that all connectors, libraries, and applications that use MySQL will also work under MariaDB. On this basis, due to concerns about a more closed software project of Oracle MySQL, Linux distributions such as Fedora have replaced MySQL with MariaDB in the latest version, and Wikimedia Foundation servers have also replaced MySQL with MariaDB.

There are several storage engines you need to understand:

MyISAM

InnoDB

MEMORY

MERGE

The following will focus on several commonly used storage engines that I have learned from recent books, comparing the differences between different storage engines to help us understand how different storage engines are used. For more details, please refer to the official documentation of MySQL.

2. Features of partial storage engine Storage engine / support feature Storage limit transaction security locking mechanism B-tree index hash index full-text index cluster index data cache data cache data compressible space use memory bulk insert speed foreign keys support MyISAM have

Table lock support

Support

Support low, low and high

InnoDB64TB support row lock support

Support (5.6) support

High or low support MEMORY has

Table lock support

Support

Nabao A medium height

MERGE didn't.

Table lock support

Support

Low, low and high

NDB has

Row lock support

Support

Low, high and high

The InnoDB storage engine began to support full-text indexing in the MySQL5.6 version. Virtual columns are introduced in MySQL5.7, and functional indexing support is added to MySQL8.0 's new features.

2.1.The MyISAM storage engine

MyISAM was previously the default storage engine for MySQL5.5. MyISAM does not support transactions and foreign keys. The advantage lies in the fast access speed, no special requirements for transaction integrity, or applications based on select and insert can basically use MyISAM as a storage engine to create tables. Let's start with an example to demonstrate that we have prepared a table with tens of millions of data in advance to take a look at the features of this storage engine:

I have created the database as test and created two tables test and tolove in test. When the test table is created, the default storage engine is specified for the MyISAM,tolove table that the storage engine is InnoDB.

Use the table tolove created by the MyISAM storage engine to query the table tolove that stores 1kw data.

Tips: you can use use test to switch to the test database, so you don't have to query the tolove table to specify the test database like I did!

MySQL [(none)] > select count (*) from test.tolove;+-+ | count (*) | +-+ | 10000000 | +-+ 1 row in set (0.000 sec)

Let's take a look at the demonstration of the table test created by the InnoDB storage engine, and also for the demonstration, 1kw pieces of data are randomly generated in advance.

MySQL [(none)] > select count (*) from test.test;+-+ | count (*) | +-+ | 10000000 | +-+ 1 row in set (3.080 sec)

Compared with the tables that also store 1kw data, the query speed of using MyISAM as storage engine is 1 row in set (0.000 sec), while the query speed of using InnoDB storage engine is slightly lower than 1 row in set (3.080 sec).

Files that MyISAM stores on disk:

Each MyISAM is stored as 3 files on disk, with the same file name and table name, with the following extensions:

.frm: store table definition

.MYD: MYData, storing data

.MYI: MYindex, which stores the index.

Data files and index files can be stored in different directories, evenly distributed IO, to achieve faster speed and improve performance. The storage path of index file and data file needs to be specified. When creating a table, it is specified by DATA DIRECTORY and INDEX DIRECTORY parameters, indicating that the index file and data file of different MyISAM tables can be stored in different paths. Of course, you need to give access to the path.

MyISAM damage handling:

Tables of type MyISAM can be corrupted for a variety of reasons. The corrupted table may not be accessible, and it will prompt you to fix it or return an error result after access. For tables of type MyISAM, you can use the repair tool provided to execute CHECK TABLE statements to check the health of MyISAM tables and use REPAIR TABLE statements to repair a corrupted table. Table corruption may cause an abnormal restart of the database, which needs to be repaired and determined as soon as possible in order to deal with it.

Tables that use the MyISAM storage engine support three different storage formats, as follows:

Static table, fixed length

Dynamic table

Compression table

Static table is the default storage format of MyISAM storage engine. The length of field is fixed and the length of record is fixed. The advantage is that the storage is fast, easy to cache, easy to recover in the event of failure, and the disadvantage is the relative consumption of storage space. It should be noted that if you want to save the space after the content, the space after the result will be removed by default.

Dynamic tables contain variable-length fields, records are not of fixed length, and storage advantages: they take up relatively small space, but frequent deletions and updates of records result in fragments. At this point, optimize table statements or myisamchk-r commands need to be executed periodically to improve performance, and it is relatively difficult to recover from failures.

The compression table is created by the mysiampack tool and takes up very little disk space. Because each record is compressed separately, the access starts very small.

Sort out the main points of the MyISAM storage engine, as shown in figure 1-2-2-1 below:

By the way, some time ago, I found that WPS can also make exquisite mind maps and support one-click import into doc files. The average user can store up to 150 files. I've used XMind, processon, gitmind, and so on before, but now it's more convenient to use WPS.

2.2, InnoDB storage engine

Pros and cons: the InnoDB storage engine provides transaction security with commit (commit), rollback (rollback), and crash recovery. However, compared with the MyISAM storage engine, InnoDB writes are relatively inefficient and take up more disk space to retain data and indexes. The following figure shows the table in which I store 1kw pieces of data, using the InnoDB storage engine. The student01 table also uses the InnoDB storage engine, which stores 100w pieces of data. You can see from the following figure that the index of the stored data is in the .ibd file and the table structure is in the .frm file.

2.2.1, automatic growth column

The auto-growing column of the InnoDB table can be inserted manually, but if the inserted value is empty or 0, the auto-growing value will actually be inserted.

I wanted to continue to use the bols table as a demonstration, but think about it a little more seriously. To demonstrate, I added a new table named autoincre_test, indicating that id is set as the primary key and self-growing, and the storage engine chooses InnoDB. Then three pieces of data are inserted for demonstration. Query the record usage value of the last data inserted by the current thread:

MySQL [test] > create table autoincre_test (id int not null auto_increment,name varchar (16), primary key (id)) engine=innodb;Query OK, 0 rows affected (0.018 sec) MySQL [test] > insert into autoincre_test values (1 meme 1'), (0 rows affected 2'), (null,'3'); Query OK, 3 rows affected (0.007 sec) Records: 3 Duplicates: 0 Warnings: 0MySQL [test] > select * from autoincre_test +-id | id | name | 1 | 1 | 2 | 2 | 3 | 3 | + 3 rows in set (0.000 sec) select last_insert_id (); MySQL [test] > select last_insert_id () +-+ | last_insert_id () | +-+ | 2 | +-+ 1 row in set (0.000 sec)

Tips: you can forcibly set the initial value of the auto-growing column through the alter table table_name=n; statement, starting from 1 by default, but the mandatory default value is retained in memory. If the database is restarted before using this value, the forced default value will be lost and needs to be reset. After all, the memory is not loaded to disk.

Through the above demonstration, you will find that when the insert record is 0 or empty, the actual value will be automatically increased. You can query the record usage value of the last data inserted by the current thread through the last_insert_id () function. If you insert more than one record at a time, the automatic growth value used by the first record is returned, and inserting multiple pieces of data is not demonstrated here. Keep in mind that you can use last_insert_id () to query the id record value.

For InnoDB tables, the auto-growing column must be an index. If it is a composite index, it must also be the first column of the composite index. For MyISAM tables, however, auto-growing columns can be other columns of a composite index. After the record is inserted in this way, the auto-growing column is incremented by sorting the first few columns of the composite index. You can create a table to specify the MyISAM storage engine, and then test and verify the two-column field combination index.

2.2.2, foreign key constraint

In MySQL, currently the only storage engine that supports foreign key constraints is InnoDB. When creating a foreign key, the parent table must have a corresponding index. When a child table creates a foreign key, the corresponding index is also created automatically. The following will be explained by an example. You can download the sample databases world and sakila from the MySQL official website for reference.

City table, FOREIGN KEY (CountryCode) REFERENCES country (Code)

Country table

Countrylanguage table, FOREIGN KEY (CountryCode) REFERENCES country (Code)

Through MySQL workbench or Navicat reverse generation of the physical model for reference, more intuitive. By the way, there is also a sakila database on MySQL's official website about actor movies, and it also provides a picture of sakila's ERR physical model, which is hyperlinked and can be accessed directly. Give the physical model of my previous reverse-generated world database:

When you create an index, you can specify that when you delete and update the parent table, the corresponding actions on the child table include:

Restrict

Cascade

Set null and no action

Restrict is the same as no action. Restrict restricts that the parent table cannot be updated when the child table has associated records; cascade means that the corresponding records of the child table are cascaded or deleted when the parent table is updated or deleted; set null means that the corresponding fields of the child table are set null when the parent table is updated or deleted. You need to be careful when choosing cascade and set null, which may result in data loss.

When importing data from multiple tables, you can temporarily turn off foreign key checking if you ignore the import order before the table; similarly, you can also temporarily turn off foreign key checking to speed up processing and improve efficiency when executing load data and alter table. The command to turn off foreign key checking is:

Set foreign_key_checks=0

After you have finished importing data or modifying the table, change it back by opening the foreign key check command:

Set foreign_key_checks=1

For tables of type InnoDB, foreign key information can be viewed through show create table or show table status. For example, look up the city table in the world database:

MySQL [sakila] > show table status like 'city'\ G

That's all about foreign key constraints, without demonstrating creation and deletion, because too many SQL statements take up too much space. You can download world and sakila databases from MySQL's official website for testing.

2.2.3, storage mode

InnoDB stores tables and indexes in two ways:

Shared tablespace storage

Multiple tablespace storage

Using shared tablespace storage, the table structure of tables created in this way is saved in .frm files, and the data and indexes are stored in tablespaces defined by innodb_data_home_dir and innodb_data_file_path, which can be multiple files. The file storage location was also mentioned at the beginning of the introduction to the InnoDB storage engine.

Using multiple tablespace storage, the table structure of tables created in this way is still saved in .frm files, but the data and indexes for each table are saved separately in .ibd files. If it is a partition table, each partition corresponds to a separate .ibd file named table name + partition name. When you create a partition, you can specify the data file location of each partition, so as to distribute the IO of the disk evenly and achieve the purpose of alleviating the pressure on the disk. The following is the file that stores a large amount of data using InnoDB under Windows:

To use multi-table space storage, you need to set the parameter innodb_file_per_table to restart the database server to take effect. When the parameter of multiple tablespaces takes effect, it only takes effect on the newly created table. There is no size limit for multi-tablespace data files, there is no need to set the initial size, and there is no need to set parameters such as the maximum limit and extension size of the file. The advantage of using multiple tablespace storage is to facilitate single table backup and restore operations. Although you cannot directly copy .frm and .ibd files, you can use the following command:

Alter table table_name discard tablespace;alter table table_name import tablespace

Restore the backup to the database, single table backup, can only be restored to the original database, can not be restored to other databases. If you need to restore a single table to another target database, you need to do so through mysqldump and mysqlimport.

Note: even though multi-table storage has more advantages, sharing table storage space is still necessary, and InnoDB stores internal data dictionaries and online redo logs in this file.

Sort out the main points of the InnoDB storage engine, as shown in figure 1-2-2-2 below:

This is the end of the introduction to the InnoDB storage engine. More details can be found in the official documentation of MySQL. Did you find a mind map that I only summarized in the MyISAM and InnoDB storage engines? Yes, only these two, because these two are the most commonly used. As for why the pink background, because the old man has a girl heart!

2.3.The MEMORY storage engine

The MEMORY storage engine uses existing and in-memory content to create tables. Each MEMORY table corresponds to only one disk file in .frm format. MEMORY type of table access speed is extremely fast, stored in memory of course fast. Is that why Redis is so fast? Not only small? Can it last? To get back to the point, MEMORY is stored in memory and hash indexes are used by default. Once the service is turned off, the data in the table will be lost. Create a table named GIRLS and specify the storage engine as MEMORY. Note that under the UNIX and Linux operating systems, it is write-sensitive to the size of fields and table names, and keywords do not affect.

CREATE TABLE GIRLS (ID int NOT NULL,GIRE_NAME varchar 64) NOT NULL,GIRL_AGE varchar (10) NOT NULL, CUP_SIZE varchar (2) NOT NULL,PRIMARY KEY (ID)) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Remember when you introduced the table made by the storage engine? it was introduced that MEMORY supports B TREE indexes. Although the default index used by MEMORY is the hash index, you can specify the index type manually. For example, the keyword USING HASH is manually specified by default:

-- create an index and specify the index type as hash. Create index mem_hash USING HASH on GIRLS (ID);-- query the index type, which is simplified to show only some parameters. Mysql > SHOW TABLE STATUS LIKE 'GIRLS'\ gateway * 1. Row * * Name: GIRLS Engine: MEMORY Version: 10 Row_format: Fixed1 row in set (0.00 sec)

Although MEMORY is prone to data loss, when starting the MySQL service, we can use the * *-init-file option to insert into... Statements such as select or load data infile** are stored in this specified file so that the table can be loaded from a persistent data source when the service starts.

The server needs to provide enough memory to maintain all MEMORY tables used at the same time, freeing up memory used by the MEMORY table when the contents of the MEMORY table are not needed. Think carefully about how terrible it would be if memory was used and not freed. At this point, you can execute delete form or truncate table or delete the entire table completely, using drop table. One point here is that truncate is also supported in Oracle, and the advantage of using truncate is that you don't have to think about rollback and are more efficient. Truncate needs to be used in command mode and may not be supported by other client tools.

The amount of data stored in each MEMORY table is constrained by the max_heap_table_size system variable, and the initial value is 16MB, which can be adjusted according to the demand. You can specify the maximum number of rows for a table through max_rows.

The most important feature of MEMORY storage engine is fast, which is mainly used for code tables with infrequent content changes, or intermediate tables provided for statistics, which is more efficient. Be careful when using MEMORY, in case you forget to restart the data, it will be embarrassing. So when using it, consider how to get the data after restarting the server.

On the MEMORY storage engine is introduced here, most of it is some theoretical knowledge, more need to practice their own testing.

2.4.The MERGE storage engine

MERGE storage engine is a combination of a group of MyISAM tables, these MyISAM tables must have the same result, the MERGE table itself has no data, and the MERGE type table can be queried, updated and deleted, which is actually operated on the internal MyISAM table. For the insert operation of a MERGE type table, the insert defined by the insert_ method clause can have three different values, starting with the last table using the first or last insert operation. If this clause is not defined, or defined as NO, it means that the MERGE table cannot be manipulated.

The DROP operation on the MERGE table only deletes the definition of MERGE and has no effect on the internal table. Keep two files on the MERGE table, starting with the name of the table:

.frm file storage table definition

The .mrg file contains information about the combined table, including the composition of the table and the basis for inserting data.

You can modify the table by modifying the .mrg file, but you need to refresh it using flush tables. The test can first create two tables with the storage engine MyISAM, and then create a table with the storage engine MERGE storage engine. Create demo as follows to specify the engine MERGE,demo01 and demo02 as sub-tables for the summary table:

CREATE TABLE `INT (11) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR (16) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`ID`) ENGINE=MERGE UNION= (merge_demo01,merge_demo02) INSERT_METHOD=LAST DEFAULT CHARSET=utf8 COLLATE=utf8_bin CREATE TABLE `merge_ demo01` (`ID`INT (11) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR (16) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`ID`) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin CREATE TABLE `merge_ demo02` (`ID`INT (11) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR (16) COLLATE utf8_bin NOT NULL PRIMARY KEY (`ID`) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

By inserting data to verify that MERGE is indeed a combination of MyISAM, it is so magical. Only demo01 and demo02 are inserted, as shown below:

INSERT INTO study. `merge _ demo01` VALUES; INSERT INTO study. `merge _ demo02` VALUES; mysql [study] > select * demotion | ID | NAME | +-+ | 1 | demo01 | 1 | demo02 | +-+-- + 2 rows in set (0.000 sec)

After inserting the data, check that there is only one piece of data in demo01 and demo02 respectively, and you can see all the data in the two sub-tables in the summary table. The key is to specify the insert_method=last. The difference between a MERGE table and a partitioned table is that MERGE cannot intelligently insert records into the corresponding table, but partitioned tables can. Usually we use MERGE table to query and update multiple tables transparently. You can insert data in the following test table, see the situation of the sub-table, I will not paste the code here.

This is the end of the introduction of several commonly used storage engines that come with MySQL. If you are interested, you can test and verify them in private. For more information, please go to the official website to obtain API or DOC documents.

In addition to some of the storage engines that come with MySQL, a common excellent third-party storage engine is TokuDB, an open source high-performance storage engine for MySQL and MariaDB. More details can be found on the TokuDB website.

2.5. Modify the storage engine of the table

When creating a new table, if you do not specify a storage engine, the system uses the default storage engine. Before MySQL5.5, the default storage engine is MyISAM, and after MySQL5.5 the default storage engine is InnoDB. If you want to modify the default storage engine, you can specify the parameters of default-table-type through the configuration file. With regard to the view of the storage engine, it has been explained when the storage engine is introduced above.

Method 1: to create a table is to specify the storage engine of the current table

Specify the storage engine when creating the tolove table, for example, specify the storage engine as MyISAM, and the default encoding is utf8:

-- Create TableCREATE TABLE `tolove` (`ID` int (11) NOT NULL AUTO_INCREMENT, `GIRL_ NAME` varchar (64) COLLATE utf8_bin DEFAULT NULL, `GIRL_ AGE` varchar (64) COLLATE utf8_bin DEFAULT NULL, `CUP_ SIZE` varchar (10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID`) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

The amount of data generated by the test is relatively large, with 10 million pieces of data randomly generated. There are relatively many select services, so the default storage engine MyISAM is specified when the table is created, and count is very efficient. With my scum computer, using the INNODB storage engine, it takes about 2 to 3 seconds at a time. When we talked about MYISAM above, we have already compared the query time.

Method 2: use alter table to modify the storage engine of the current table

Modify the created tolove table to test for the MYISAM engine.

-- modify the created tolove table to test ALTER TABLE test.`tolove` ENGINE=MYISAM for MYISAM engine

Modify the storage engine of the test table to INNODB for testing.

-- modify the storage engine of the table to test ALTER TABLE test.`test`ENGINE=INNODB for INNODB

SHOW CREATE TABLE query table storage engine, respectively query test table and tolove table, in the storage engine for MyISAM, there is a demonstration!

SHOW CREATE TABLE test.`test`; SHOW CREATE TABLE test.`tolove`

If you can't see it all in the tool, you can export it to xml, csv, html and other queries. The storage engine I set when I created the table is InnoDB:

The SQL statement storage engine that shows the test table I created is InnoDBCREATE TABLE `test` (`ID` int (11) NOT NULL AUTO_INCREMENT, `STU_ NAME` varchar (50) NOT NULL, `SCORE` int (11) NOT NULL, `CREATETIME` timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), PRIMARY KEY (`ID`) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8-- shows the SQL statement of the tolove table I created. The storage engine is MyISAMCREATE TABLE `tolove` (`ID` int (11) NOT NULL AUTO_INCREMENT, `GIRL_ NAME` varchar (64) COLLATE utf8_bin DEFAULT NULL, `GIRL_ AGE` varchar (64) COLLATE utf8_bin DEFAULT NULL, `CUP_ SIZE` varchar (10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`ID`) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

There are so many changes to the storage engine. I saw that my self-growing column (AUTO_INCREMENT) ID has reached 20000001. I have randomly generated a 1kw bar before! Part of the explanation is that I wrote it in a block of code and looked more comfortable.

3. The choice of storage engine

When selecting the appropriate storage engine, the appropriate storage engine should be selected according to the application characteristics. For complex applications, you can choose a variety of storage engines to meet the needs of different application scenarios. How to choose the right storage engine? Is the choice of storage engine really important?

You should really think about it. In a non-complex application scenario, the MyISAM storage engine may be able to meet the daily overhead. Perhaps in another scenario, InnoDB is the best choice, which has better overall performance and meets more needs.

MyISAM is the default plug-in storage engine for MySQL and the default storage engine for MySQL prior to 5.5. If most of the applications are read and insert operations, there are only a few update and delete operations, and there is no high demand for transaction integrity and concurrency, so the MyISAM storage engine is preferred. One of the most commonly used storage engines in web and data warehouses.

InnoDB is used in transactional applications and supports foreign keys. It is the default storage engine of MySQL after 5.5. it is also the default storage engine of MariaDB after 10.2.It shows the excellence of InnoDB. If the application has high requirements for transaction integrity, it requires a high degree of data consistency in the case of concurrency. In addition to inserting and querying, data also includes a lot of update and delete operations, so InnoDB should be a more appropriate storage engine choice. In addition to effectively reducing locks caused by deletions and updates, InnoDB can also ensure full commit (commit) and rollback (rollback) of transactions. InnoDB is also a suitable choice for systems that require high data accuracy, such as billing systems or financial systems. Beside the point, I also used Oracle database in my work for a period of time. The affairs of Oracle are indeed very powerful, and there is a lot of pressure to deal with big data.

The MEMORY storage engine stores all the data in RAM, providing extremely fast access in environments that need to quickly locate records and other similar data. The defect of MEMORY is that there is a limit on the size of the table, the table is too large to be cached in memory, the second is to ensure that the data of the table can be recovered, and the data of the table can be recovered after an abnormal restart of the database. MEMORY tables are usually used for small tables that are updated less frequently to quickly locate access results.

MERGE is used to logically group a set of equivalent MyISAM storage engine tables together and apply them as an object. The advantage of MERGE table is that it can break through the restriction on the size of a single MyISAM table and improve the access efficiency of MERGE table by distributing different tables on multiple disks. It is suitable for VLDB environment such as data bureau warehouse.

Finally, the choice of storage engine is summed up based on the actual experience of others. It does not necessarily fit your application scenario, and ultimately requires users to test their applications and get the most appropriate results through testing. As I began to enumerate the example, the amount of data is very large, query and insert business is more frequent, I began to test the MyISAM storage engine, which is really in line with my application scenario.

With regard to the selection of storage engine, the summary is simplified as follows:

4. Table optimization (defragmentation)

When I first introduced the storage MyISAM and InnoDB storage engines, I also showed the disk space used to store large amounts of data. Use OPTIMIZE TABLE to optimize the test table under the test database, before optimization, this table occupies about 824m of disk space; after optimization, there is a significant improvement, the system reclaims unused space, the disk space consumed by test table is significantly reduced, only 456m after optimization. No screenshots of the space occupied by the disk will be posted here.

OPTIMIZE TABLE test.`test`

After optimization, the efficiency of count data has also been improved, probably around 2.5sec:

Mysql [test] > select count (*) from test;-using innodb storage engine test +-+ | count (*) | +-+ | 10000000 | +-+ 1 row in set (2.468 sec)

Before optimization, the statistics were about 3.080 sec. By comparison, the improvement in efficiency is considerable.

You can also use the explain execution plan to optimize the query. The knowledge of MySQL optimization is not the focus of this article, so it will not be described too much.

Index design and use 1. Introduction to the index

In the interview involving MySQL, the leftmost prefix index was mentioned.

All column types of MySQL can be indexed, and reasonable use of indexes on related columns is the best way to improve the performance of query (select) operations. According to the maximum number of indexes and the maximum index length per table can be defined by the engine, each storage engine of MySQL (MyISAM, InnoDB, etc.) supports at least 16 indexes per table, with a total index length of at least 256bytes. Most storage engines have higher limits.

The tables created by the MyISAM and InnoDB storage engines are BTREE indexes by default. Before MySQL8.0, not only functional indexes were supported, MySQL5.7 introduced the virtual column function, and functional indexes were supported in MySQL8.0, which is also one of the new features of version 8.0.

MySQL supports prefix indexes, creating indexes on the first N characters of the index field, and the prefix index length is related to the storage engine. Many people often ask, does MySQL support full-text indexing? My answer is: yes. Before MySQL5.6, the MyISAM storage engine supported full-text indexing (FULLTEXT). After 5.6, InnoDB began to support full-text indexing.

Create a 10-byte prefix index for the test table. The syntax for creating the index is as follows:

CREATE INDEX girl_name ON table_name (test (10))

You can also use the alter table statement to add an index to the field girl_name of the girl table:

ALTER TABLE test.`room`ADD INDEX idx_girlname (girl_name)

Validations that use indexes can be judged using the explain execution plan. So much for a brief introduction to the index, and more basic knowledge can be found in official documents or authoritative books.

2. The principle of designing index

The design of the index can follow some existing principles, which should be considered as much as possible when creating the index. It helps to improve the efficiency of the index.

The index column of the search is not necessarily the column to be selected. The most appropriate index column is often the column that appears in the where clause, or the column specified in the join clause, rather than the column that appears in the select list after select.

Use a unique index. Considering the distribution of values in a column, the larger the cardinality of the index column, the better the indexing effect.

Use short indexes. If you index a string column, you should specify a prefix length. For example, char (100), think about the question of repetition. Is it faster for all indexes, or is it better to index some characters?

Use the leftmost prefix. When you create an index with N columns, you actually create N indexes that are available to MySQL. A multi-column index can act as several indexes, using the leftmost list in the index to match rows. Such a column set is called the leftmost prefix. The interview involving MySQL is getting messed up, .

Be careful not to overuse the index. Don't think that using indexes has many benefits, just use indexes on all columns, and excessive use of indexes will be counterproductive. Each additional index takes up disk space and consumes disk write performance. When refactoring, the index has to be updated, resulting in an unnecessary waste of time.

InnoDB stores the table of the engine. For tables that use the InnoDB storage engine, records are saved in a certain order by default. There are the following situations:

If there is a clearly defined primary key, save it in the primary key order

If there is no primary key, but there is a unique index, it will be saved in the order of the unique index.

If there is neither a primary key nor a unique index, an internal column is automatically generated in the table and saved in the order of that column.

The above is a brief introduction to the principles of index design.

3. B-TREE and HASH index

When using these indexes, you should consider whether the index is valid under the current use conditions! You can choose to use HASH indexes or B-TREE indexes in tables that use the MEMORY storage engine, and the two different indexes have their own scope of application.

HASH index. The optimizer cannot use HASH indexes to speed up order by operations only for this type of relational operators: =,. MySQL is not sure how many rows there are between the two values.

B-TREE index. For B-TREE indexes, use >, =, show character set | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf32 | | | UTF-32 Unicode | utf32_general_ci | 4 | |

Or you can use DESC information_schema.CHARACTER_SETS to view all character sets and default proofreading rules for character sets.

To view the relevant character set proofing rules, you can use SHOW COLLATION with LIKE fuzzy search gbk character set.

SHOW COLLATION LIKE 'gbk%'

MySQL character set setting: the character-set-server parameter can be set through the configuration file by default.

Installation in Linux distributions is generally configured in my.cnf

Configure in my.ini file under Windows

[mysqld] character-set-server=utf-8character-set-server=gbk

In addition, to determine the bytes of the character set, you can use the function LENGTH ():

SELECT LENGTH ('medium')

If you are using UTF-8 encoding, the default Chinese character occupies 3 bytes, while using GBK takes 2 bytes. This is the end of character coding.

5. MySQL sample database sakila

Views, stored procedures, functions, triggers. Here are the functions and stored procedures that I use to randomly generate large amounts of data.

1. Function

Create functions, declare using DELIMITER, create functions using CREATE FUNCTION, the creation of the tolove table has been shown in the process of introducing the storage engine.

/ * * create a function to generate the student number * * / DELIMITER $CREATE FUNCTION rand_number () RETURNS INTBEGIN DECLARE i INT DEFAULT 0; SET I = FLOOR (1+RAND () * 100); RETURN I into end $DELIMITER $

Create function: used to generate random strings of names

/ * * create a function to generate a random string of names * * / DELIMITER $CREATE FUNCTION rand_name (n INT) RETURNS VARCHAR (255) BEGIN DECLARE chars_str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR (255) DEFAULT''; DECLARE i INT DEFAULT 0; WHILE I < n DO SET return_str = CONCAT (chars_str,FLOOR (1+RAND () * 52), 1); SET I = iTun1 END WHILE; RETURN return_str; END $DELIMITER $2, stored procedures

Create a stored procedure, using CREATE PROCEDURE to create:

/ * * create stored procedures * * / DELIMITER $CREATE PROCEDURE insert_tolove (IN max_num INT (10)) BEGIN DECLARE i INT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION; WHILE i < max_num DO INSERT INTO test.`tolove` (ID,GIRL_NAME,GIRL_AGE,CUP_SIZE) VALUES (NULL,rand_name (5), rand_number (), NULL); SET I = I + 1; END WHILE;COMMIT END $DELIMITER $

Use CALL to call stored procedures and randomly generate millions of data:

/ * * call stored procedure * * / CALL insert_tolove (100,10000)

Delete a function or stored procedure, using the DROP keyword

-- delete function rand_nameDROP FUNCTION rand_name;-- delete stored procedure insert_toloveDROP PROCEDURE insert_tolove;3, trigger

CREATE TRIGGER is used to create triggers, and the sakila database instance is referenced here. If it exists, the judgment statement IF EXISTS is used, and then the trigger that DROP TRIGGER already exists is deleted.

DELIMITER $$USE `sakila` $$DROP TRIGGER / *! 50032 IF EXISTS * / `customer_create_ date` $$CREATE / *! 50017 DEFINER = 'root'@'%' * / TRIGGER `customer_create_ date`BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW (); $DELIMITER; 4. Sakila database

In this article, I repeatedly mentioned MySQL's sample database sakila, which is a good example of a complete learning MySQL. Contains views, stored procedures, functions, and triggers. You can go to MySQL's official website to get the SQL script.

Leave a mysterious parameter at the end of the article, which can filter the content you don't want to see in this way! It can take effect either on the mobile phone or on the PC, and the personal test is available.

Xxxx (content of search)-site:xxxn.net-- or xxxx (content of search)-site:xxshu.com thank you for reading, this is the content of "how to understand MySQL storage engine". After the study of this article, I believe you have a deeper understanding of how to understand the MySQL storage engine, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.