Storage engine concept
Data in MySQL is stored in files using a variety of different technologies, each of which uses different storage mechanisms, indexing technologies, locking levels, and ultimately provides different functions and capabilities. These are what we call storage engines.
Storage engine function
1. A way and format for mysql 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 saved to the file system (i.e. hard disk) according to the format of the storage engine.
Mysql storage engine classification
1. InnoDB: MySQL’s default storage engine after 5.5 (transactional shorthand engine, with better writing and query performance) supports ACID transactions, row locks, and table locks.
2. MyISAM: The default storage engine before 5.5. The performance of inserting data is high, and the query speed is also excellent, but transactions are not supported.
3. MEMORY: A storage engine in which all data is stored in memory. Inserting, updating, and querying data are faster, but they occupy a larger amount of memory space, which is proportional to the amount of data. Once MySQL is restarted, the content will be lost.
4. CSV: A storage engine that separates data by commas will create a .csv file (an ordinary text file) for each data table in the database subdirectory. Each line represents a data record. CSV does not support indexing. .
5. ARCHIVE: It is very suitable for engines that store large amounts of independent, historical data. It does not need to be read frequently. The insertion speed is very fast, but the query efficiency is relatively low.
6. BLACKHOLE: Black hole engine, any data written will disappear.
Introduction to MyISAM
MyISAM: 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.
MylSAM is stored in three files on the disk. The file names and table names are the same, but the extensions are:
.frm file storage table structure definition
The data file extension is .MYD
The extension of the index file is .MYI
MyISAM Features:
1. Table-level locking. When updating data, the entire table will be locked.
2. Databases block each other during reading and writing.
Applicable scenarios:
Applications focused on querying and inserting data
Storage formats supported by Myisam:
1. Static table, that is, fixed-length table. Static table is the default storage format of MyISAM. The fields in static tables are non-variable fields, and each record is of fixed length.
Advantages: fast storage, convenient caching, easy recovery in case of failure
Disadvantages: takes up a lot of space
2. Dynamic tables. Dynamic tables can contain variable fields, and the length of records is not fixed.
Advantages: takes up less space
Disadvantages: Frequently updating data and deleting records will generate fragments, which need to be cleaned regularly, making recovery and price comparison difficult in case of failure.
3. Compression table. The compression table is created by the tool myisam. It occupies very little space and each record is compressed individually.
InnoDB introduction
InnoDB:
Supports transactions, supports 4 transaction isolation levels, and is the default storage engine of mysql after 5.5
Read and write blocking is related to isolation level
Supports efficient cache indexing and cached data
Tables and primary keys are stored in clusters
Supports foreign key constraints, and after 5.5 innodb also supports full-text indexing
Hardware resource requirements are relatively high
Supports row locking and table locking
Points to note:
1. Using like fuzzy query will scan the entire table and lock the entire table.
2. Querying the fields for which no index has been created will also perform a full table scan and lock the entire table.
3. If you use an index to query, it is row-level locking.
Features of innodb:
1. The number of rows in the table is not saved. If the number of rows in the table is counted, the entire table will be scanned to calculate how many rows there are.
2. For self-increasing fields, innodb must contain an index with only this field.
3. When delete clears the table, it deletes rows one by one, which is relatively slow. It is recommended to use truncate.
Applicable scenarios:
1. Business needs support from affairs
2. Scenarios that require high data consistency, such as forums and Weibo
3. In scenarios with relatively high visits and concurrency, innodb supports caching to reduce the pressure on the backend server.
Innodb storage file format:
There is only one property file for the dp.opt table, and no more will be generated when creating a table.
tablename.frm table structure file
Table name.idb is both a data file and an index file
The difference between MyISAM and InnoDB
MyISAM: does not support transactions and foreign key constraints, takes up less resources, has fast access speed, table-level locking, and supports full-text indexing.
Suitable for application scenarios that do not require transaction processing, separate writing or querying.
Storage format: Table name.frm (table structure file) Table name.MYD (data file) Table name.MYI (index file)
InnoDB: supports transaction processing, foreign key constraints, good caching capabilities, supports row-level locking, and has good read and write concurrency capabilities.
After version 5.5, full-text indexing is supported, which is suitable for application scenarios with high consistency requirements and frequent data updates.
Table name.frm (table structure file) Table name.idb (table data file/index file) db.opt (table attribute file)
View the storage engines supported by the system
show engines;
View the storage engine used by the table
Method 1: show table status from library name where name='table name'\G Method Two: use library name; show create table table name;
Modify storage engine
Method 1: alter table table name engine=use engine Method Two: vim /etc/mysql.cnf --Revise-- default-storage-engine=Use engine
Specify the engine when creating the table
create table table name( ... ) engine=use engine;
The relationship between InnoDB row locks and indexes
Row Lock:
InnoDB row locks are implemented by locking index entries. If there is no index, InnoDB will lock records through a hidden clustered index.
Before a transaction ends, if the field where the data is modified is an ordinary index, the index row will be locked, and the corresponding primary key will also be locked. In fact, the row lock can only be modified after commit.
If the id field used is the primary key, InnoDB uses a clustered index on the primary key to lock the entire row of records.
create table if not exists student( id int(5) primary key, namechar(8), age int(3), sex char(2), index name_index(name) );
Lock table: When a transaction operates on a non-index column, the entire table will be locked because it requires full table scan and filtering. Another transaction can only query show index from test;
for update: exclusive lock
Deadlock: Things wait for each other’s resources, and finally form a loop combined with exclusive locks 1. When a deadlock occurs, the database will automatically select a transaction as the victim, then first explain the deadlock, and then roll back the transaction 2. mysql The 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
Exclusive lock, when one transaction is operating, the operation of another transaction cannot be executed and can only be checked
Deadlock will automatically select a transaction as the victim to end the deadlock. 1. The storage engine can only be innodb. 2. The default isolation level of mysql is enough.
How to avoid deadlock as much as possible?
1. The business logic must be reasonable, and tables and rows must be accessed in a fixed order.
2. If the type of transaction is relatively complex, it needs to be split. If the business allows, split the large transaction into smaller ones.
3. In the same transaction, lock all required resources at once as much as possible to reduce the probability of deadlock.
4. Isolation level, read commit, can avoid deadlock. 5. Adding reasonable indexes can reduce the probability of deadlock.
Optimistic locking: There will be no prompt, but the data cannot be written. When the data is submitted for update, verification is performed, and a conflict occurs. The data does not take effect and there are no other errors or freezes. Generally speaking, we will configure a version field in the table, which can be auto-incremented. We can use auto-increment verification to check whether the data is Conflict timestamp
The article has been included in the official knowledge archive MySQL entry skill treeDatabase compositionStorage engine 77649 people are learning the system