In MySQL, data is stored in files using a variety of different technologies, each of which uses different storage mechanisms, import techniques, locking levels, and ultimately provides different functions and capabilities. These are storage engines.
Function of storage engine
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 (hard disk) according to the format of the storage engine.
MySQL storage engine classification
1. INNODB:Transactional shorthand engine. Supports ACID transactions, rows, and table locks. Writing and query performance are relatively good. The default storage engine of MySQL after version 5.5.
2. MYSAM: The performance of inserting data is high, and the query speed is also excellent, but it does not support transactions. MySQL default storage engine before version 5.5.
3. Memory: A storage engine in which all data is stored in memory. Once the service is restarted everything is lost. Inserting data, updating data, and querying data are faster. But it takes up a lot of memory space. It occupies memory space proportional to the amount of data. Once MySQL is restarted, the content will be lost.
4. csv: storage engine separated by commas. Itwill create a .csv file for each data table in the database subdirectory. It’s just an ordinary text file. Each data line occupies one text line (A piece of data is one line in the text). .csvdoes not support indexing.
5. Archive: An engine suitable for storing large amounts of independent and historical data. Does not need to be read frequently. Insertion is fast. However, the query efficiency is low.
6. blackhole: black hole engine. Any data written will be lost.
Common storage engines: INNODB, MYSAM, csv, Archive
Analysis and comparison of MYSAM and INNODB
MYSAM
MYSAM: Does not support transactions or foreign keys. Only full-text indexing is supported. Data files and index files are separate. Access is fast.
Applicable scenarios for MYSAM: applications that focus on querying and inserting data.
There are three files on the disk: the file name and table name are the same, but the extensions are different
1. .frm: storage table structure
2. .myd: storage data file
3. .MYI: storage index file
Features of MYSAM
1. Table-level locking. When updating data, the entire table will be locked.
2. Databases block each other during reading and writing.
Supported storage formats
1. Supports static table and fixed length table. Static tables are the default storage format of MYSAM. Fields in static tables are all non-variable fields. Each record is of fixed length. Fast storage and easy caching. It is easy to recover from failures. The disadvantage is that it takes up more space.
2. Dynamic tables can contain variable fields. The recordlength is not fixed. It takes up less space. The disadvantage is that frequently updating data and deleting records will cause fragmentation. Regular cleaning is required. Recovery is more difficult in the event of a failure. #mvisamchk -r: Cleanup command
3. The compressed table is created by the myisamchk tool and takes up very little space. Each record is compressed individually
INNODB
INNODB:Supports transactions. Supports 4 transaction isolation levels. After 5.5, it is the default storage engine of MySQL.
Read and write blocking is related to isolation level. If it is uncommitted read and committed read, only the final submitted data will take effect, and the intermediate operations will have no impact.
INNODBsupports efficient caching indexes as well as cached data. Tables and primary keys are stored in clusters BETREE
Supports foreign key constraints. After 5.5, INNODBcan also support full-text index.
The requirements for hardware resources are relatively high.
Supports row locking. Full table scans also support table locking.
1. If you use like fuzzy query, the whole table will be scanned, and then the entire table will be locked.
2. If you add, delete, or modify fields that have not been indexed, the entire table will also be locked. During this period, the entire table is locked and no operations can be performed on the table.
3. If you use an index to add, delete, or modify, it is row-level locking.
Features of INNODB
1. The number of rows in the table is not saved, but the number of rows in the table is counted. The entire table is scanned to calculate how many rows there are.
2. Self-increasing fields. INNODB must contain an index with only this field.
3. Delete deletes the table row by row, which is relatively slow. It is recommended to use truncate
Usage scenarios
1. Business needs transaction support.
2. Forums, Weibo, and scenarios with relatively high data consistency
3. Scenarios with relatively high visits and concurrency. BecauseINNODB can support caching, it can reduce the pressure on the backend server.
INNODB storage file format
Three files:
Table name.frm: table structure file
Table name.idb: both a data file and an index file
dp.opt: table properties file
The relationship between INNODB row locks and indexes, as well as table locks, exclusive locks, and deadlocks
Row lock
When the terminal opens the transaction to the external test, it is not submitted.
Externally opened transaction discovery cannot be run.
If the name field is a normal index, the index row will be locked, and the corresponding primary key will also be locked. In fact, it is a row lock
Open a transaction internally but do not commit it
Execute another transaction externally to check the results.
If the id field used is the primary key, innodb will use a clustered index on the primary key. Lock an entire row of records
Operation on index columns. When you specify a primary key or a normal index, if the previous transaction is not committed, other transactions cannot operate on this row. But it has no effect on other rows, only the index row is locked.
Lock table
Locking the table requires an operation on a non-index key
When a transaction operates on a non-indexed column, because a full table scan and filtering is required, the entire table will be locked, and another transaction can only query.
Exclusive lock
for update: exclusive lock
Only after one transaction ends, other transactions can operate.
Deadlock
Infinite loop. The two sides are stuck with each other forming a closed loop.
Transactions wait for each other’s resources, eventually forming a loop.
Internal device starts a transaction but does not commit
Open another transaction externally
Internal tasks will get stuck, and so will external affairs. A deadlock occurs at this time
1. When a deadlock occurs, the database will automatically select a transaction as the victim, then release the transaction first, and then roll back the transaction.
2. MySQL’s default deadlock mechanism will select a transaction as the victim of thinking. One of the transactions will be terminated directly, but it will not be automatically rolled back.
Lock Summary
Row lock: The index will only lock the index row
Lock the table: non-index will directly lock the table. But it will not roll back automatically.
Exclusive lock: only one can be added. While one transaction is operating, the operation of another transaction cannot be executed and can only be queried. Exclusive locks can also be called pessimistic locks.
Optimistic locking will not give any prompts, but the data cannot be written. Optimistic locking will not get stuck and will be verified when the data is submitted for update. If a conflict occurs, the data will not take effect, and there will be no other errors or freezes.
Deadlock: None can operate. A matter will be automatically selected as the victim of thinking. One of the transactions will be terminated directly, thus ending the deadlock but not automatically rolling back.
How to avoid deadlock as much as possible
1. The business logic must be reasonable. It is better to access tables and rows in a fixed order
2. If the type of transaction is relatively complex, it should be split when the business permits. Large transactions should be broken down into smaller pieces and executed in batches.
3. In the same 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. Deadlock can be avoided.
5. Add reasonable indexes. After adding a reasonable index, the lock will only lock rows. If you don’t add it, the table will be locked, and a deadlock may occur. Reasonable use of indexes can reduce the probability of indexing.
Modify storage engine
show engines\G; #View supported engines Method 1: alter table table name engine=storage engine name; #Modify the formula of the storage engine of the table **alter table test engine=myisam;** #Only modify the engine of the table Method 2: vim /etc/my.conf #Modify the configuration file to modify the storage engine Method 3: create table ky34 ( id int(4), namechar(10) )engine=myisam;
show create table ky32; #View the storage engine of the table The storage directory of the storage engine files is /usr/local/mysql/data/kgc
Core content
The relationship between indexes and row locks
Non-index lock table and deadlock
Exclusive lock
INNODB mechanism and storage file format
Exercise questions
Use the timestamp method. To verify whether the data conflicts.
Insert information after creating table
to internal operations
start TRANSACTION; #Open transaction update test3 set name = 'newnameC', date='2023-11-03 14:00:00' where id =1 and date='2023-11-03 02:28:31'2:28:31'; #Modify the contents of the table
Do not commit the transaction internally and start another transaction externally.
Externally modified content is stuck
Return to internal commit to commit the transaction
The external modification is modified successfully and then commit to view the information in the table.
The modification was successful but the name has not changed.
This is optimistic locking. Optimistic locking will not give any prompts, but the data cannot be written. Optimistic locking will not get stuck and will be verified when the data is submitted for update. If a conflict occurs, the data will not take effect, and there will be no other errors or freezes.
The article has been included in the official knowledge archive MySQL entry skill treeDatabase compositionStorage engine 77539 people are learning the system