actually performs access to database data.Currently MySQL uses the InnoDB engine by default.Compared with using the MyISAM engine in the past, it has the following advantages:
MySQL databases use the following two data structures to store and look up data:
index name|index type|field type|remarks -|-|- PRIMARY KEY|primary index|primary key|field value cannotRepeat, nor can it be empty.INDEX|Common Index|Custom Field|None, low efficiency.UNIQUE|Unique Index|Custom Field|Field values cannot be repeated, and the efficiency is high.FULLTEXT|Text Index|Custom Field|None, for text retrieval.
In the InnoDB storage engine, the data file itself is the main index (clusteredindex): Data is stored in a B+ tree, sorted according to the primary key value.
We can build auxiliary indexes (non-clustered indexes) for other fields to improve the query speed of the fields, but at the same time reduce the table update speed.Record the primary key value in the secondary index instead of the field address: After searching according to the secondary index, you still need to query the data in the primary index according to the primary key value.
The index can contain multiple fields, the combined index of N fieldsN indexes are actually created.
The combined index of the three fields a/b/c will actually be searched first in the a index, then in the a/b index, and finally in the a/b/c indexFind in.
The view is a virtual table and does not actually store data.Its content is obtained by querying other tables, and is dynamically generated when the view is referenced.
The slave table is related to the primary key of the main table through the foreign key to establish the relationship between the data tables.
When performing UPDATE/DELETE operations on the data of the master table, it will affect the associated slave tables.
Log database operations when database data changes.When an error or conflict occurs, a rollback can be performed.Ensure data consistency.
bin log archive log
In the beginning, MySQL did not work with the InnoDB engine, and other storage engines only had the general bin log for archiving (at the server layer).
The InnoDB engine completes the main memory data update and submits it to the executor, and the bin log records the operation.If the main memory data is updated and the database crashes when the bin log is not written, the original data will be lost during subsequent machine backups.This results in no safe recovery of data: once the database restarts abnormally, the previously committed records will be lost.
redolog redo log
MySQL comes with redo log after introducing InnoDB engine.It is used to restore system records when the database restarts abnormally.
If the database crashes when the bin log is not written, the data will be restored according to the redo log in subsequent machine backups.
The database crashes if the bin log has been written but the redo log is still in the prepare state.MySQL will judge whether the redo log is complete, and submit it immediately if it is complete.Otherwise, judge whether the bin log is complete. If it is complete, submit the redo log, and if it is incomplete, roll back the transaction.This solves the problem of data consistency.