Table of Contents
Storage engine concept
The main function
Mysql storage engine classification
Analyze and compare MYISAM and INNODB
Features of MYISAM
Supported storage formats
Key points INNODB
There are a few points to note
Features of INNODB
scenes to be used
INNODB three files
The relationship between Innodb row locks and indexes, as well as table locks, exclusive locks, and deadlocks
How to avoid deadlocks as much as possible
| In mysql, the database uses different Technologies are stored in files. Each technology uses different storage engine mechanisms, indexing techniques, locking levels, and ultimately provides different functions and capabilities. These are what we call storage engines |
Main Function
| 1 | MySQL is a way and format to store data in the file system |
| 2 | The storage engine is responsible for performing actual data I/O operations |
| 3 | The storage engine is between the data and the file system. The data will be saved to the storage engine first and then Save to the file system according to the format of the storage engine |
Mysql’s storage engine classification
| INNODB | The default storage engine of mysql after version 5.5, also called transactional shorthand engine (with better writing performance), supports ACID (four transactions) Big feature), supports row locking and table locking. |
| MYISAM | The default storage engine before 5.5, insert The data performance is high and the query speed is excellent, but it does not support transactions, which is why it was eliminated |
| Memor | All data is stored in the memory storage engine. Inserting data, updating data, and querying data are very fast, but it takes up a large amount of memory space, which is equivalent to the amount of data. Proportional memory space, and once mysql is restarted, the content will be lost soon |
| Csv | A storage engine that separates data by commas. It will create a .CSV file for each data table in the database subdirectory. It is an ordinary text file, and each data occupies one text line. But csv does not support indexing |
| Archive | Very suitable for storage A large number of independent and historical data engines, because it does not need to be read frequently and the insertion speed is very fast, but the query efficiency is relatively low |
| Blackhole | Black hole engine, any data written will disappear |
Analysis and comparison between MYISAM and INNODB
| MYISAM |
It does not support transactions or foreign keys. It only supports full-text indexing. The data files and index files are separated, and the access speed is fast. It is suitable for applications that focus on query and insertion. |
|
.frm: stored table structure .MYD: stored data file .MYI: index file |
Features of MYISAM
| 1 | Table-level locking, when updating data, the entire thing will be locked |
| 2 | Databases block each other during the reading and writing process (that is, you cannot read and write to a table at the same time) |
Supported storage format
| Static table | Fixed length table, static table is the default storage format of mysisam, and the fields in the static table are non-variable field, so each record is of fixed length |
| Advantages | Data storage is fast, easy to cache, and easy to recover in case of failure |
| Disadvantages | Take up a lot of space |
| Dynamic tables | can contain variable fields, and the length of the records is not fixed |
| Advantages | Occupies less space |
| Disadvantages | Frequently updating data and deleting records will generate fragments and require regular cleaning (myisamchk -r: clear command), once a failure occurs, recovery is more troublesome. |
| Compressed table | Created by the myisamchk tool, it occupies very little space and each record is compressed individually |
Key INNODB
| 1 | Supports transactions, supports 4 isolation levels, and is the default storage engine of mysql after 5.5 |
| 2 | Read and write blocking is related to isolation level |
| 3 | Support efficient cache index and cached data |
| 4 | The table stores BTREE in cluster mode on the primary key |
| 5 | Supports foreign key constraints, and after 5.5 INNODB can also support full-text index |
| 6 | Higher requirements for hardware resources |
| 7 | Supports row locking and table locking (full table scan) |
There are several points to note
| 1 | If you use like fuzzy query, a full table scan will be performed and the entire table will be locked |
| 2 | Add, delete, and modify fields that have not been indexed. Full table scans and locks will also be performed. For the entire table, queries can |
| 3 | using the index. Query, it is row-level locking |
Features of INNODB
| 1 | Does not save the number of rows in the table, counts the number of rows in the table, and scans the entire table to calculate how many rows there are |
| 2 | Self-growing index, INNODB must contain an index with only this field |
| 3 | Delete an empty table, which is row by row Delete, speed comparison truncate |
Usage Scenario
| 1 | Business needs the support of things |
| 2 | Forums, Weibo, scenarios with relatively high data consistency |
| 3 | In scenarios where the number of visits and concurrency is relatively high, INNODB supports caching, which can reduce the pressure on the backend server |
INNODB three files
| 1 | Table name.frm table structure file |
| 2 | Table name.ibd is both a data file and an index file |
| 3 | db.opt table property file |
Innodb row The relationship between locks and indexes, as well as table locks, exclusive locks, and deadlocks
| Row lock |
If the column name field is an ordinary index, the index row will be locked, and the corresponding primary key will also be locked. In fact, it is a row lock. If the ID used is the primary key, InnoDB uses a clustered index on the primary key to lock the entire row of records. |
| Lock table | To a Operate on non-index keys. When a transaction operates on non-index columns, because full table scanning and filtering are required, the entire table will be locked, and another transaction can only query |
| Exclusive lock | A transaction is operating, and the operation of another transaction cannot be executed. It can only be checked. Exclusive lock can only Add a |
| Deadlock |
Transactions wait for each other’s resources, and finally form a loop. Using for update exclusive lock can form a deadlock. When a deadlock occurs, the database will automatically select a transaction as the victim, then unlock the deadlock first, and then roll back the transaction. MySQL’s default deadlock mechanism will select a transaction as the victim of the deadlock and directly terminate one of the transactions, but it will not automatically roll back. |
How to avoid deadlocks as much as possible
| 1 | The business logic must be reasonable, and tables and rows should be accessed in a fixed order |
| 2 | If the type of transaction is relatively complex and needs to be split, large transactions can be divided into Split into smaller pieces and executed in batches |
| 3 | In the same In a transaction, lock all required resources at once as much as possible to reduce the probability of deadlock |
| 4 | Isolation level, if you want to avoid deadlock, you can use read commit to avoid indexing |
| 5 | Adding reasonable indexes can reduce the probability of deadlock |
Optimistic locking and pessimistic locking
| Optimistic lock |
There will be no prompt, but the data cannot be written. When the data is submitted for update, it will be verified. If a conflict occurs, the data will not be valid at most. There will be no other errors or lags. Generally speaking, we will configure a version field in the table, which can be auto-incremented, and use auto-increment verification to check whether the data conflicts. Timestamp method can also be used |
| Pessimistic lock |
- The storage engine can only be innodb
- Mysql default isolation level is enough
How to view and modify storage engines
show engines\G; #ViewEngine alter table member engine=myisam #Modify the storage engine in the database vim /etc/my.cnf #Change the configuration file to modify the storage engine, which is only valid for newly created tables create table jl ( id int(4), name int(5), )engine=myisam; #Modify the storage engine when creating a table
Core content
Relationship between indexes and row locks
| If it is an index, it will only lock the row , non-index columns will lock the table |
Non-indexed lock tables and deadlocks
| Non-index | Non-index columns will scan the entire table for scan and filtering, and the entire table will be locked |
| Deadlock | The system will automatically select a victim as the victim to end the deadlock |
Exclusive lock (pessimistic lock)
| One transaction is operating, another The transaction cannot be operated and can only be queried |
The mechanism of Innodb and the format of storing files
|
Format for saving files 1.Table name.frm: table structure 2. Table name.idb: data file, also an index file 3.db.opt: table attributes The mechanism of innodb Locking mechanism |
The article has been included in the official knowledge archive MySQL entry skill treeDatabase compositionStorage engine 77559 people are learning the system