Mysql database engine, mysql

Mysql database engine, mysql

I. Database Engine

　　Database EngineIs a core service used to store, process, and protect data. The database engine can be used to control access permissions and quickly process transactions, so as to meet the requirements of most applications in the enterprise that need to process a large amount of data. Use the database engine to create a relational database for online transaction processing or online analysis and data processing. This includes creating tables for data storage and database objects (such as indexes, views, and stored procedures) for viewing, managing, and securing data ).

2. Database Engine tasks

In the database engine documentationSequenceFollow the main sequence of tasks used to store data using the database engine.

• Design and create a database to save the relationships or XML documents required by the system
• Implement the system to access and change the data stored in the database. This includes websites or applications that use data. It also includes the process of generating SQL Server tools and utilities to use data.
• System deployed for the Organization or customer
• Provides routine management support to optimize database performance

Iii. MySQL database engine category

The database engine you can use depends on how mysql is compiled during installation. To add a new engine, you must recompile MYSQL. By default, MYSQL supports three engines:ISAM, MYISAM, and HEAP. Two other typesINNODBAndBERKLEY(BDB.

ISAM is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, ISAM performs read operations quickly without occupying a large amount of memory and storage resources. ISAM WeaknessesIs, it Transaction processing not supported, Also Fault Tolerance: If your hard disk crashes, the data file cannot be recovered. If you are using ISAM in a key task application, you must always back up all your real-time data. With its copy feature, MYSQL can support such backup applications. MYISAM is the ISAM extension format of MYSQL and Default Database Engine. Besides ProvideWhat is not in ISAM Index and Field ManagementFunction, MYISAM also uses Table Locking Mechanism, Come Optimize multiple concurrent read/write operations. The cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MYISAM also has some useful extensions, such as the MYISAMCHK tool used to fix database files and the MYISAMPACK tool used to restore wasted space. MYISAM emphasizes fast read operations, which may be the main reason why MYSQL is so favored by WEB development: In WEB development, a large number of data operations you perform are read operations. Therefore, most VM providers and INTERNET platform providers only allow MYISAM format. HEAP Allow temporary tables that only reside in the memory. The HEAP is faster than ISAM and MYISAM in the memory, The managed data is unstable.And if it is not saved before shutdown, all data will be lost. When a row is deleted, HEAP does not waste much space. HEAP tables are useful when you need to use SELECT expressions to SELECT and manipulate data. Remember to delete the table after the table is used up. INNODB and BERKLEYDB (BDB) database engines are both direct products that create MYSQL flexibility technology. This technology is MYSQL ++ API. When using MYSQL, almost every challenge you face comes from the fact that the ISAM and MYISAM database engines do not support transaction processing or foreign keys. Although it is much slower than ISAM and MYISAM engines, INNODB and BDB include Support for transaction processing and Foreign keys, Both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.

Iv. mysql Data Engine replacement method

1. view the engines supported by the current database and the default database engine:

show engines;

My query results are as follows:

2. Change the Database Engine

2.1 Change Method 1: modify the configuration file my. ini

Save the my-small.ini as my. ini, add default-storage-engine = InnoDB after [mysqld], restart the service, change the default engine of the database to InnoDB

2.2 Change Method 2: specified when creating a table

When creating a table, specify:

create table mytbl(       id int primary key,       name varchar(50)   )type=MyISAM;

2.3 change method 3: change after table Creation

Alter table mytbl2 type = InnoDB;

3. view the Modification result

Method 1:

show table status from mytest; 

Method 2:

show create table table_name

V. Explanation of MyIASM and Innodb Engines

Innodb Engine

The Innodb engine provides ACIDTransactionsAnd implements the four isolation levels of the SQL standard. For information about database transactions and their isolation levels, see this article on database transactions and their isolation levels. The engine also provides row-level locks and foreign key constraints. It is designed to handle large-capacity database systems. It is actually a complete database system based on the MySQL background, when MySQL is running, Innodb creates a buffer pool in the memory to buffer data and indexes. However, this engine does not support FULLTEXT indexes and does not store the number of rows in the TABLE. When select count (*) from table, you need to scan the entire TABLE. This engine is of course the first choice when you need to use database transactions. Because the lock granularity is smaller, write operations do not lock the entire table, so when the concurrency is high, Innodb engine will improve efficiency. However, row-level locks are not absolute. If MySQL cannot determine the scan range when executing an SQL statement, the InnoDB table will also lock the entire table.

Glossary:

ACID

• ATransactionAtomicity(Atomicity ):Indicates that a transaction is either executed in full or not.. That is to say, it is impossible for a transaction to stop after half of the execution. for example, if you withdraw money from a cash machine, this transaction can be divided into two steps: 1 card, 2 money. it's impossible to drag the card, but the money is not out. these two steps must be completed at the same time. or not.
• CTransactionConsistencyConsistency: transactions do not change the Consistency of data in the database. For example, if the integrity constraint is a + B = 10 and a transaction changes a, then B should change accordingly.
• I Independence(Isolation): the independence of a transaction is also called Isolation. It means that two or more transactions will not be in a staggered state, because this may lead to data inconsistency.
• D Durability(Durability): the transaction Persistence means that after the transaction is successfully executed, the changes made by the transaction to the database are permanently stored in the database and will not be rolled back for no reason.

MyIASM Engine

MyIASM is the default MySQL engine, but it does not support database transactions, and does not support row-level locks and Foreign keys. Therefore, when INSERT or UPDATE) when the data is written, the entire table needs to be locked, and the efficiency will be lower. However, unlike Innodb, MyIASM stores the number of rows in the TABLE. Therefore, when you select count (*) from table, you only need to read the saved values directly without scanning the entire TABLE. MyIASM is also a good choice if the number of read operations for a table is much higher than that for write operations without the support of database transactions.

Two Engines

A large dataset tends to be an InnoDB Engine because it supports transaction processing and fault recovery. The size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. Primary Key query will be quite fast in the InnoDB engine, but it should be noted that if the primary key is too long, it will also cause performance problems. I will discuss this issue below. A large number of INSERT statements (write multiple rows in each INSERT statement and INSERT multiple rows in batches) are faster in MyISAM, but the UPDATE statement is faster in InnoDB, especially when the concurrency is large.

Index -- Index

　　Index(Index) isHelp MySQL efficiently obtain data structures. Both MyIASM and Innodb use the tree data structure as the index. Next I will talk about the index structures used by these two engines. Here, we should first talk about B-Tree and B + Tree.

Index Structure of MyIASM Engine

The index structure of the MyISAM engine isB + TreeWhere B + TreeThe content stored in the data domain is the actual data address.That is to say, its index is separated from the actual data, but the index points to the actual data. This index is calledNon-clustered Index. As shown in:

Here, a table has three columns. Suppose we use Col1 as the Primary key, it is the Primary index (Primary key) of a MyISAM table. It can be seen that the index file of MyISAM only stores the address of the data record. In MyISAM, the primary index and Secondary index (Secondary key) have no difference in structure, but the primary index requires that the key is unique, and the Secondary index key can be repeated. If we create a secondary index on Col2, the index structure is shown in:

It is also a B + Tree that stores data records in the data field. Therefore, the index search algorithm in MyISAM first searches for indexes based on the B + Tree search algorithm. If the specified Key exists, the value of its data field is obtained, then, read the corresponding data records using the data domain value as the address.

Index Structure of Innodb Engine

The index structure of the MyISAM engine is also B + Tree, but the index file of Innodb is a data file, that isB + Tree data domains store actual dataThis index isClustered Index. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index.

Different from MyISAMThe secondary index data domain stores the primary key values of corresponding records.Instead of the address, when secondary index search is used, the primary key is first found based on the secondary index, and then the actual data is found based on the primary key index. Therefore, Innodb does not recommend that you use too long primary keys. Otherwise, the secondary index will become too large. We recommend that you use an auto-increment field as the primary key. In this way, each node of the B + Tree will be filled in order without frequent split and adjustment, which will effectively improve the efficiency of data insertion.

Differences:
The first major difference is that the InnoDB data file itself is an index file. As mentioned above, the MyISAM index file is separated from the data file, and the index file only stores the data record address. In InnoDB, the table data file itself is an index structure organized by B + Tree. The leaf node data field of this Tree stores complete data records. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index. It is an InnoDB primary index (also a data file). We can see that the leaf node contains a complete data record. This index is called a clustered index. Because the data files in InnoDB need to be clustered by the primary key, InnoDB requires that the table have a primary key (MyISAM may not). If it is not explicitly specified, mySQL automatically selects a column that uniquely identifies a data record as the primary key. If this column does not exist, MySQL automatically generates an implicit field for the InnoDB table as the primary key, this field is 6 bytes in length and its type is long integer. The second difference from the MyISAM index is that InnoDB's secondary index data domain stores the value of the primary key of the corresponding record rather than the address. In other words, all secondary indexes of InnoDB reference the primary key as the data domain. For example, to define a secondary index on Col3: here the ASCII code of English characters is used as a comparison criterion. Clustered index makes the search by primary key very efficient, but secondary index search requires two indexes: first, retrieve the secondary index to obtain the primary key, then, use the primary key to search for the record in the primary index.
Understanding the index implementation methods of different storage engines is very helpful for correct use and optimization of indexes. For example, after knowing the index Implementation of InnoDB, it is easy to understand why it is not recommended to use too long fields as the primary key, because all secondary indexes reference the primary index, too long primary index will make the secondary index too large. For another example, it is not a good idea to use a non-Monotonic (which may be referred to as "non-incrementing") field as the primary key in InnoDB, because the InnoDB data file itself is a B + Tree, A non-monotonic primary key (which may be referred to as "non-incrementing") will cause frequent split and adjustment of data files to maintain the features of B + Tree during the insertion of new records, which is very inefficient, using an auto-increment field as the primary key is a good choice.

　　Thank you: Thank you for reading this article!