MySQL Database engine

First, the database engine

　　The Database engine is the core service for storing, processing, and securing data . Use the database engine to control access and quickly process transactions to meet the requirements of most applications that need to process large amounts of data within the enterprise. Use the database engine to create a relational database for online transaction processing or online analytics processing data. This includes creating tables for storing data and database objects such as indexes, views, and stored procedures that are used to view, manage, and secure data.

Ii. Database Engine Tasks

In the Database engine documentation, the order of the topics follows the primary order of the tasks used to implement the system that uses the database engine for data storage.

• Design and create a database to hold the relationships or XML documents required by the system
• Implement the system to access and change the data stored in the database. Includes applications that implement a Web site or work with data, and also include the process of building the use of SQL Server tools and utilities to work with data.
• Deploy implemented systems for units or customers
• Provides daily management support to optimize database performance

III. MySQL Database engine category

The database engine you can use depends on how MySQL was compiled when it was installed. To add a new engine, you must recompile MySQL. by default , MySQL supports three engines:ISAM, MyISAM, and heap. Two other types of INNODB and BERKLEY(BDB) are also often available.

The ISAM ISAM is a well-defined and time-tested method of data table management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. ISAM's two main the shortcomingsIs that it does not support transactional processing, also cannot be fault tolerant: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application. MYISAM MYISAM is the ISAM extended format for MySQL and the default database engine。 Apart from provideNot in the ISAM. indexed and field-managedfunction, MyISAM also uses a mechanism for table lockingTo optimize multiple concurrent read and write operations。 The cost is that you need to run the Optimize Table command frequently to restore the space wasted by the updated mechanism.　　MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space. MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most web hosting providers and internet platform providers only allow the use of the MyISAM format. Heap Heap allow temporary tables that reside only in memory。 Residing in memory makes the heap faster than ISAM and MyISAM, but it The managed data is unstable, and if you do not save before shutting down, all data will be lost. The heap does not waste a lot of space when data rows are deleted. The heap table is useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using the table. The InnoDB and Berkleydb InnoDB and Berkleydb (BDB) database engines are direct products of the technology that makes MySQL flexible, the technology is the mysql++ API. Every challenge you face when using MySQL comes from the ISAM and the MyISAM database engine does not support transactional processing or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB and BDB included support for transaction processing and foreign keys, these two points are not the first two engines. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter 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. Changing the Database engine

2.1, Change Mode 1: Modify the configuration file My.ini

Save My-small.ini as My.ini, add Default-storage-engine=innodb after [mysqld], restart Service, database default engine modified to InnoDB

2.2, Change Mode 2: In the construction of the table when the designation

Specify when the table is built:

Create Table mytbl (       intprimarykey,       varchar (  )   ) type=MyISAM;

2.3. Change Mode 3: Change after building table

ALTER TABLE MYTBL2 type = InnoDB;

3. View the results of the changes

Mode 1:

Table  from

Mode 2:

Create Table table_name

Five, myiasm and InnoDB engine detailed

InnoDB engine

The InnoDB engine provides support for database ACID transactions , and implements four isolation levels for the SQL standard, with information about database transactions and their isolation levels in the article, database transactions and their isolation levels. The engine also provides row-level and foreign-key constraints, which are designed to handle a large-capacity database system, which is itself a complete database system based on the MySQL backend, and the MySQL runtime InnoDB creates buffer pools in memory for buffering data and indexes. However, the engine does not support an index of type Fulltext, and it does not save the number of rows in the table, and the full table needs to be scanned when SELECT COUNT (*) from table . The engine is of course preferred when it is necessary to use a database transaction. Because the lock granularity is smaller, the write operation does not lock the full table, so using the INNODB engine increases efficiency when concurrency is high. However, using row-level locks is not absolute, and if MySQL does not determine the scope to scan when executing an SQL statement, the INNODB table will also lock the full table.

Noun Analysis:

ACID

• atomicity of a transaction (atomicity): means that a transaction is either executed or not executed . That is, a transaction cannot be executed in half. For example, if you withdraw money from a cash machine, the transaction can be divided into two steps. : 1 Card, 2 money. It is impossible to draw the card, but the cash does not come out. These two steps must be completed at the same time. or not done.
• C Transactional Consistency (consistency): The operation of a transaction does not alter the consistency of the data in the database. For example, integrity constrains a+b=10, a transaction changes A, and B should change as well.
• I Independence (Isolation): The independence of a transaction is also known as isolation, which means that more than two transactions do not have a state of interleaving. This can cause inconsistent data.
• D Persistence (Durability): The persistence of a transaction means that the transaction's changes to the database are persisted in the database and will not be rolled back for no reason.

Myiasm engine

Myiasm is the default engine for MySQL, but it does not provide support for database transactions or row-level and foreign keys , so it is less efficient to write operations that require the entire table to be locked when insert (insert) or update (updated) data . Unlike InnoDB, however, the number of rows in the table is stored in myiasm, so the SELECT COUNT (*) from table only needs to read the saved values directly and does not require a full table scan. Myiasm is also a good choice if the table reads much more than writes and does not require support for database transactions.

Choice of two engines

Large datasets tend to select the InnoDB engine because it supports transactional processing and failback. The size of the database determines how long the recovery takes, and InnoDB can use the transaction log for data recovery, which is faster. Primary key queries can also be pretty fast under the InnoDB engine, but it's important to note that if the primary key is too long it can cause performance problems, as I'll see later in this question. A large number of INSERT statements (write multiple lines in each INSERT statement, BULK INSERT) will be faster under MyISAM, but the UPDATE statement will be faster under InnoDB, especially if the concurrency is large.

index--Index

　　An index is a data structure that helps MySQL to efficiently get the information . Both myiasm and InnoDB use tree-like data structures as indexes. Here I go on the index structure used by these two engines, and here we should first talk about B-tree and B+tree.

Index structure of the MYIASM engine

The index structure of the MyISAM engine is b+tree, where B+tree's data field is stored as the actual data address , that is, its index is separate from the actual data , it's just that the index points to the actual data, which is called a nonclustered index . As shown in the following:

There are three columns in the table, assuming that we have Col1 as the primary key, it is a MyISAM table's main index (Primary key) schematic. You can see that the index file of MyISAM only stores the address of the data record. In MyISAM, the primary index and secondary index (secondary key) are structurally indistinguishable, except that the primary index requires that key be unique, and the secondary index key can be duplicated. If we establish a secondary index on Col2 , the structure of this index is as follows:

It is also the address of a b+tree,data field that holds data records. Therefore, the algorithm of index retrieval in MyISAM is to search the index according to the B+tree search algorithm first, if the specified key exists, the value of its data field is fetched, then the data record is read with the value of the data field.

Index structure of the INNODB engine

The index structure of the MyISAM engine is also b+tree, but the InnoDB index file itself is the data file, which is the actual data stored in the B+tree data field , which is the clustered 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.

And unlike MyISAM, the Secondary index data field of InnoDB stores the value of the corresponding record primary key instead of the address, so when looking with a secondary index, the primary key is first found based on the secondary index. The actual data is then found based on the primary key index . Therefore, InnoDB does not recommend using a long primary key, otherwise the secondary index becomes too large. It is recommended to use the self-increment field as the primary key so that each node of the b+tree is filled in order, without frequent splitting adjustments, which effectively increases the efficiency of inserting data.

The difference between the two:
The first major difference is that the InnoDB data file itself is the index file. As you know above, the MyISAM index file and the data file are detached, and the index file only holds the address of the data record. In InnoDB, the table data file itself is an index structure organized by B+tree, and the tree's leaf node data field holds the complete data record.　　The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index. Is the InnoDB primary index (also the data file), you can see that the leaf node contains the complete data record. This index is called a clustered index. Because the InnoDB data file itself is clustered by the primary key,InnoDB requires that the table must have a primary key(MyISAM can not), if not explicitly specified, the MySQL system automatically selects a column that uniquely identifies the data record as the primary key, and if no such column exists, MySQL automatically generates an implicit field for the InnoDB table as the primary key, which is 6 bytes long and has a length of type. The second difference from the MyISAM index is that the secondary index of the InnoDB data field stores the value of the corresponding record primary key instead of the address. In other words, all secondary indexes of InnoDB refer to the primary key as the data domain. For example, to define a secondary index on a Col3: here the ASCII code for the English character is used as the comparison criterion. Clustered index This implementation makes search by primary key efficient, but secondary index search needs to retrieve two times index: first retrieves the secondary index to obtain the primary key, and then retrieves the record with the primary key to the primary index.
Understanding how index implementations of different storage engines can be useful for proper use and optimization of indexes, such as knowing the InnoDB index implementation, makes it easy to understand why it is not recommended to use too-long fields as primary keys, because all secondary indexes refer to the primary index, and the long primary index makes the secondary index too large. For example, it is not a good idea to use a field that is non-monotonic (which may mean "non-incrementing") as the primary key in InnoDB because the InnoDB data file itself is a b+tree, non-monotonic (possibly "non-incrementing") primary key that causes the data file to be inserted in order to maintain B + The tree features frequent split adjustments that are very inefficient, and using the self-increment field as the primary key is a good choice.

　　 Thanks: Thank you for reading!

MySQL Database engine