Summary of MySQL knowledge points

PS: This article has been included in the github repository, which is used to share the core knowledge of Java, including Java basics, MySQL, SpringBoot, Mybatis, Redis, RabbitMQ, etc., which is necessary for interviews.

github address: https://github.com/Tyson0314/Java-learning
If github is not accessible, you can visit the gitee repository.
gitee address: https://gitee.com/tysondai/Java-learning

Transaction characteristics

Transaction characteristics: Atomicity, Consistency, Isolation, Durability.

• Atomicity means that all operations included in the transaction either all succeed or all fail and roll back.
• Consistency means that a transaction must be in a consistent state before and after execution. For example, accounts a and b have a total of 1,000 yuan. After the transfer between the two is successful or unsuccessful, the sum of their accounts is still 1,000.
• Isolation. Related to the isolation level, such as read committed, a transaction can only read the committed changes.
• Persistence means that once a transaction is committed, the changes to the data in the database are permanent, and the operation of committing the transaction will not be lost even if the database system encounters a failure.

Transaction isolation level

First understand the following concepts: dirty reading, non-repeatable reading, and phantom reading.

• Dirty read refers to the data in another uncommitted transaction that is read in the process of a transaction.
• Non-repeatable read means that for a row of records in the database, multiple queries within a transaction range have returned different data values. This is because during the query interval, another transaction modifies the data and commits it.
• A phantom read is when a transaction is reading a record in a certain range, and another transaction inserts a new record in the range. When the previous transaction reads the record in the range again, a phantom row will be generated. Just like hallucinations, this is where hallucinations take place.

The difference between non-repeatable reads and dirty reads is that a dirty read is a transaction that reads dirty data that was not committed by another transaction, while a non-repeatable read reads the data submitted by the previous transaction.
Both phantom read and non-repeatable read read another committed transaction. The difference is that the focus of non-repeatable read is modification, while the focus of phantom read is addition or deletion.

Transaction isolation is to solve the problems of dirty reads, non-repeatable reads, and phantom reads mentioned above.

MySQL database provides us with four isolation levels:

• Serializable: Solve the problem of phantom reading by forcing transaction ordering to make it impossible to conflict with each other.
• Repeatable read (repeatable read): MySQL's default transaction isolation level, it ensures that multiple instances of the same transaction will see the same data row when reading data concurrently, which solves the problem of non-repeatable read.
• Read committed: A transaction can only see the changes made by the committed transaction. Can avoid the occurrence of dirty reads.
• Read uncommitted: All transactions can see the execution results of other uncommitted transactions.

View the isolation level:

select @@transaction_isolation;

Set the isolation level:

set session transaction isolation level read uncommitted;

index

Index is a data structure used by the storage engine to improve the access speed of database tables.

Features: 1. Avoid scanning the entire database table. In most cases, you only need to scan fewer index pages and data pages; improve the execution efficiency of query statements, but reduce the speed of adding and deleting operations, and it will also take up Additional storage space.

The role of the index

The data is stored on the disk. When querying the data, if there is no index, all the data will be loaded into the memory, and the retrieval will be performed one by one. There are many times to read the disk. With an index, there is no need to load all the data, because the height of the B+ tree is generally 2-4 levels, and only needs to read the disk 2-4 times at most, which greatly improves the query speed.

Under what circumstances need to build an index:

1. Frequently used fields for query
2. Frequently used to connect fields (such as foreign keys) to create an index, can speed up the connection
3. The fields that often need to be sorted are indexed, because the index has been sorted, which can speed up the sorting query

Under what circumstances do you not build an index?

1. Fields not used in the where condition are not suitable for indexing
2. Less table records
3. Need to add and delete frequently
4. The columns participating in the column calculation are not suitable for indexing
5. Fields that are not highly differentiated are not suitable for indexing, gender, etc.

B+ tree

The B+ tree is implemented based on the B-tree and the sequential access pointers of the leaf nodes. It has the balance of the B-tree and improves the performance of the interval query by sequentially accessing the pointers.

In the B+ tree, the keys in the node are arranged in ascending order from left to right. If the left and right adjacent keys of a pointer are key _i and key _i+1 , then all the keys pointed to by the pointer are greater than or equal to key _i and less than or equal to key _i+1 .

When performing a search operation, first perform a binary search at the root node to find the pointer where the key is located, and then recursively search the node pointed to by the pointer. Until the leaf node is found, a binary search is performed on the leaf node to find the data item corresponding to the key.

The most commonly used index type in MySQL database is BTREE index, and the bottom layer is implemented based on the B+ tree data structure.

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

Index instance

Let's take a look at an example of an index:

As shown in the figure below, col1 is the primary key, and col2 and col3 are common fields.

The figure below is the B+ tree structure corresponding to the primary key index, and each node corresponds to a page of the disk.

Create a single-column index on col3, corresponding to the B+ tree structure:

Index classification

1. Primary key index: the only non-empty index named primary, no null values are allowed.

2. Unique index: The value in the index column must be unique, but null values are allowed.

   The difference between a unique index and a primary key index is: the column of the UNIQUE constraint can be null and there can be multiple null values. The purpose of UNIQUE KEY: uniquely identify each record in the database table, mainly used to prevent repeated insertion of data.

   Create a unique index:

   ALTER TABLE table_name
   ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3. Composite index: An index created on a combination of multiple fields in a table. The index will only be used when the left field of these fields is used in the query conditions. When using a composite index, follow the principle of the leftmost prefix.
4. Full-text index: Full-text index, which can only be used on MyISAM engine, and can only be used on CHAR, VARCHAR, and TEXT type fields.

Leftmost match

If the leftmost index in the composite index is used in the SQL statement, then this SQL statement can use the composite index to match. When a range query (>, <, between, like) is encountered, the matching will stop, and the index will not be used for the following fields.

To create an index on (a,b,c), use a/ab/abc as the query condition to use the index, and use bc to not use the index.

Create an index on (a, b, c, d), the query condition is a = 1 and b = 2 and c > 3 and d = 4 , then the index can be used for the three fields a, b, and c, but d cannot be matched. Because of a range query!

To build an index on (a, b), a is globally ordered in the index tree, and b is globally unordered and locally ordered (when a is equal, b will be compared and sorted). Directly executing b = 2 cannot use the index.

From a partial point of view, when the value of a is determined, b is ordered. For example, when a = 1, the value of b is 1, and 2 is an ordered state. When a=2, the value of b is 1, and 4 is also in order. Therefore, if you execute a = 1 and b = 2 , the a and b fields can be indexed. When you execute a > 1 and b = 2 , the a field can use the index, but the b field does not use the index. Because the value of a is a range at this time, it is not fixed, and the value of b is not ordered in this range, so the b field does not need to be indexed.

Clustered index

InnoDB uses the primary key of the table to construct the primary key index tree, and the record data of the entire table is stored in the leaf nodes. The storage of the leaf nodes of the clustered index is logically continuous, using a doubly linked list connection, and the leaf nodes are sorted in the order of the primary key, so the sorting and range searching of the primary key is faster.

The leaf nodes of the clustered index are the rows of the entire table. The InnoDB primary key uses a clustered index. Clustered index is much more efficient than non-clustered index query.

For InnoDB, the clustered index is generally the primary key index in the table. If the specified primary key is not displayed in the table, the first unique index in the table that is not allowed to be NULL is selected. If there is no primary key and no suitable unique index, then innodb will generate a hidden primary key as a clustered index. The hidden primary key is 6 bytes in length, and its value will increase with the insertion of data.

Covering index

The data column of select can be obtained only from the index, and there is no need to perform a second query on the data table. In other words, the query column must be covered by the index used. For the secondary index of the innodb table, if the index can cover the query column, then the secondary query on the primary key index can be avoided.

Not all types of indexes can become covering indexes. The covering index needs to store the value of the index column, while the hash index and full-text index do not store the value of the index column, so MySQL can only use the b+ tree index as a covering index.

For a query that uses a covering index, use explain in front of the query, and the output extra column will be displayed as using index.

For example, in the user_like user like table, the combined index is (user_id, blog_id), and neither user_id nor blog_id is null.

explain select blog_id from user_like where user_id = 13;

The extra is Using index , the column to be queried is covered by the index, and the where filter condition complies with the left-most prefix principle. The index can be used to search for to directly find the qualified data without returning to the table to query the data.

explain select user_id from user_like where blog_id = 1;

The extra is Using where; Using index , the column to be queried is covered by the index, and the where filter condition does not meet the left-most prefix principle, and the data that meets the conditions cannot be found through index search, but the data that meets the conditions can be found through index scan , and there is no need to return to the table. Query data.

Index failure

Circumstances leading to index failure:

• For a composite index, if the leftmost field of the composite index is not used, the index will not be used
• Like queries that start with %, such as %abc , cannot use indexes; like queries that do not start with %, such as abc% , are equivalent to range queries and use indexes
• The column type in the query condition is a string, and quotation marks are not used, and implicit conversion may occur due to different types, which will invalidate the index
• When judging whether the index column is not equal to a certain value
• Operate on the index column
• Using the condition of or connection, if the field on the left has an index and the field on the right has no index, then the index on the left will be invalid

Storage engine

The default storage engine after MySQL 5.5 is InnoDB.

InnoDB

InnoDB is MySQL's default transactional storage engine, the most widely used, and is based on a clustered index. InnoDB has made many internal optimizations, such as being able to automatically create an adaptive hash index in memory to speed up read operations.

Advantages : Support transaction and crash repair capabilities. InnoDB introduced row-level locks and foreign key constraints.

Disadvantages : The data space occupied is relatively large.

applicable scenarios : transaction support is required, and there is a higher frequency of concurrent read and write.

MyISAM

The data is stored in a compact format. For read-only data, or the table is relatively small, can tolerate repair operations, you can use the MyISAM engine. MyISAM will store the table in two files, the data file .MYD and the index file .MYI.

advantages : fast access speed.

Disadvantages : MyISAM does not support transactions and row-level locks, does not support safe recovery after a crash, and does not support foreign keys.

Applicable scenario : There is no requirement for transaction integrity; read-only data or small tables can tolerate the repair operation.

MyISAM features:

1. MyISAM locks the entire table, not the rows. When reading data, a shared lock is added to all tables that need to be read, and when writing, an exclusive lock is added to the table. But while reading the table records, you can insert new records into the table (parallel insertion).
2. For MyISAM tables, MySQL can manually or automatically perform check and repair operations. Performing a table repair may result in data loss, and the repair operation is very slow. You can CHECK TABLE tablename , and if there are errors, perform REPAIR TABLE tablename to fix them.

MEMORY

The MEMORY engine puts all the data in the memory, the access speed is faster, but once the system crashes, the data will be lost.

The MEMORY engine uses a hash index by default, and stores the hash value of the key and the pointer to the data row in the hash index. The hash index uses the zipper method to handle hash collisions.

advantages : faster access speed.

disadvantages :

1. Hash index data is not stored in the order of index values and cannot be used for sorting.
2. Partial index matching search is not supported, because the hash index uses the entire content of the index column to calculate the hash value.
3. Only equivalence comparison is supported, and range query is not supported.
4. When a hash conflict occurs, the storage engine needs to traverse all the row pointers in the linked list and compare row by row until it finds a row that meets the conditions.

The difference between MyISAM and InnoDB

1. support row-level locks? : MyISAM only has table-level locks, while InnoDB supports row-level locks and table-level locks. The default is row-level locks.
2. support transactions and safe recovery after crash? : MyISAM emphasizes performance, each query is atomic, and its execution speed is faster than InnoDB type, but does not provide transaction support. But InnoDB provides transaction support, with transaction, rollback, and crash repair capabilities.
3. support foreign keys: MyISAM does not support it, but InnoDB supports it.
4. support MVCC? : Only supported by InnoDB. To deal with high concurrent transactions, MVCC is more efficient than simple locking; MVCC only works at READ COMMITTED and REPEATABLE READ ; MVCC can be implemented using optimistic locking and pessimistic locking; the implementation of MVCC in various databases is not uniform.

5. MyISAM does not support clustered index, InnoDB supports clustered index.

   The primary key index of the myisam engine is not much different from other indexes. The leaf nodes all contain index values and row pointers.
   The secondary index leaf of the innodb engine stores the index value and the primary key value (not the row pointer), which can reduce the maintenance of the secondary index when the row moves and the data page is split.

   

MVCC

MVCC ( Multiversion concurrency control ) is a way to keep multiple versions of the same data to achieve concurrency control. You can think of MVCC as a variant of row-level locks. When querying, find the data of the corresponding version through the read view and version chain.

MVCC only applies to read committed and repeatable read. When a transaction is used to update a row record, a new version of the row record is generated.

Role: to improve concurrent performance. For high concurrency scenarios, MVCC is more efficient and less expensive than row-level locks.

Realization principle

The realization of mvcc depends on the version chain, which is realized through three hidden fields of the table.

• Transaction id: data_trx_id, current transaction id
• Rollback pointer: data_roll_ptr, which points to the previous version of the current row record. Through this pointer, multiple versions of data are connected to form an undo log version chain
• Primary key: db_row_id, if the data table does not have a primary key, InnoDB will automatically generate a primary key

When a transaction is used to update a row record, a version chain is generated:

1. Lock the row with an exclusive lock;
2. Copy the original value of the row to the undo log, as the old version for rollback;
3. Modify the value of the current row, generate a new version, update the transaction id, make the rollback pointer point to the record of the old version, thus forming a version chain;
4. Record redo log;

read view

Read view is to take a snapshot of the transaction at a certain moment. A linked list of active transactions is maintained inside read_view. This linked list contains transactions that have not yet been committed before the read view is created, and does not contain transactions that are committed after the read view is created.

The timing of creating a read view is different for different isolation levels.

read committed: A new read_view will be created every time the select is executed, ensuring that the modifications that have been committed by other transactions can be read.

Repeatable read: Within the scope of a transaction, the read_view is updated at the first select, and will not be updated in the future. All subsequent selects will reuse the previous read_view. In this way, it can be ensured that the content read every time within the transaction scope is the same, and the read can be repeated.

Data access process

When accessing a data row, it will first determine whether the current version data item is visible, and if it is invisible, a visible version will be found through the version chain.

• If the current version of the data row <read view's earliest active transaction id: it means that when the read_view is created, the transaction that modifies the data row has been committed, and the data row of this version can be read by the current transaction.
• If the current version of the data row >= the latest active transaction id of the read view: the transaction of the current version of the data row is generated after the read_view is created, and the data row of this version cannot be accessed by the current transaction. At this time, it is necessary to find the previous version through the version chain, and then re-judge the visibility of the version data to the current transaction.

• If the current version of the data row is between the earliest active transaction id and the latest active transaction id:

  1. It is necessary to find whether the latest transaction id of the data row is included in the active transaction linked list, that is, whether the transaction that generated the current version data row has been committed.
  2. If it exists, it means that the transaction that generated the data row of the current version has not been committed, so the data row of this version cannot be accessed by the current transaction. At this time, you need to find the previous version through the version chain, and then re-judge the visibility of that version.
  3. If it does not exist, the transaction has been committed, and the data row can be read directly.

summarizes : By comparing the current version of the read view and the data row, find the visible version of the current transaction, and then achieve the transaction isolation level of read commit and repeatable read.

Snapshot read and current read

Two ways to read records.

Snapshot read: Read the snapshot version, that is, the historical version. Ordinary SELECT is a snapshot read. Controlled by MVCC, no need to lock.

Current reading: Read the latest version. UPDATE, DELETE, INSERT, SELECT… LOCK IN SHARE MODE, SELECT… FOR UPDATE is the current reading.

In the case of snapshot reads, InnoDB avoids the phenomenon of phantom reads through the mvcc mechanism. The mvcc mechanism cannot avoid the phenomenon of phantom reading in the current reading situation.

Transaction a and transaction b open the transaction at the same time, transaction a inserts data and then commits, transaction b executes the update of the whole table, and then executes the query, and finds the data added in transaction A.

How does MySQL avoid phantom reads:

• In the case of snapshot reads, MySQL uses mvcc to avoid phantom reads.
• In the current read situation, MySQL uses next-key to avoid phantom reads (implemented by adding row locks and gap locks).

Next-key consists of two parts: row lock and gap lock. Row locks are locks added to indexes, and gap locks are added between indexes.

select * from table where id<6 lock in share mode;--共享锁 锁定的是小于6的行和等于6的行
select * from table where id<6 for update;--排他锁

In fact, the above two methods are not used in many projects. The performance of serialized reading is too poor, and in fact, phantom reading is completely acceptable to us in many cases.

The Serializable isolation level can also avoid phantom reading, which will lock the entire table, with extremely low concurrency, and is generally rarely used.

select read lock

The read lock in SELECT is mainly divided into two ways: shared lock and exclusive lock.

SELECT ... LOCK IN SHARE MODE　
SELECT ... FOR UPDATE

The main difference between these two methods is that LOCK IN SHARE MODE can easily cause deadlock when multiple transactions update the same form at the same time. In this case, it is best to use SELECT...FOR UPDATE.

select * from goods where id = 1 for update : The premise of applying for an exclusive lock is that no thread uses an exclusive lock or a shared lock for any row data in the result set, otherwise the application will be blocked. When performing transaction operations, MySQL will add an exclusive lock to each row of data in the query result set, and other threads' changes or deletion operations on these data will be blocked (only read operations), until the transaction of the statement is committed or Until the end of the rollback statement.

select... for update Precautions for use

1. for update only applies to Innodb, and must be within the scope of the transaction to take effect.
2. Query based on the primary key, the query condition is like or not equal, the primary key field generates a table lock.
3. Query based on non-indexed fields, and the name field generates a table lock.

Sub-library and sub-table

When the data volume of a single table reaches 1000W or 100G, optimizing indexes and adding slave libraries may not have obvious effect on database performance improvement. At this time, it is necessary to consider dividing it. The purpose of segmentation is to reduce the burden on the database and shorten the query time.

Data segmentation can be divided into two ways: vertical (vertical) division and horizontal (horizontal) division.

Vertical division

The vertical partition of the database is based on business. For example, the tables related to goods, orders, and users in the shop library are divided into one library, and the performance is improved by reducing the size of the single library, but this method does not solve the high data volume. Performance loss. Similarly, the case of sub-table is to split a large table into sub-tables according to business functions, such as basic product information and product description. The basic product information is generally displayed in the product list, and the product description is on the product details page. The basic information and product description are split into two tables.

Advantages: The row records become smaller, the data page can store more records, and the number of I/Os is reduced when querying.

shortcoming:

• The primary key is redundant, and redundant columns need to be managed;
• It will cause the table connection JOIN operation, and the database pressure can be reduced by joining on the business server;
• There is still the problem of too much data in a single table.

Level division

Horizontal division is to split data according to certain rules, such as time or id sequence values. For example, split different databases according to the year. Each database has the same structure, but the data is split to improve performance.

Advantages: The amount of data in a single database (table) can be reduced to improve performance; the structure of the split table is the same, and the program changes less.

shortcoming:

• Shard transaction consistency is difficult to solve
• Cross-node Join has poor performance and complex logic
• Data shards need to be migrated during expansion

Log

MySQL logs mainly include query logs, slow query logs, transaction logs, error logs, binary logs, etc. Among the more important ones are the binary log binlog and transaction log redo log (redo log) and undo log (rollback log).

bin log

The binary log (bin log) is a MySQL database-level file that records all operations performed on the MySQL database. It does not record select and show statements. It is mainly used to restore the database and synchronize the database.

Check whether the bin log is turned on and where to save it:

MySQL> show variables like '%log_bin%';
+---------------------------------+----------------------------------------------------+
| Variable_name                   | Value                                              |
+---------------------------------+----------------------------------------------------+
| log_bin                         | ON                                                 |
| log_bin_basename                | F:\java\MySQL8\data\Data\DESKTOP-8F30VS1-bin       |
| log_bin_index                   | F:\java\MySQL8\data\Data\DESKTOP-8F30VS1-bin.index |
| log_bin_trust_function_creators | OFF                                                |
| log_bin_use_v1_row_events       | OFF                                                |
| sql_log_bin                     | ON                                                 |
+---------------------------------+----------------------------------------------------+

Close the bin log, find the /etc/my.cnf file, and comment the following code:

log-bin=MySQL-bin
binlog_format=mixed

redo log

Redo log is the Innodb engine level, used to record the transaction log of the Innodb storage engine, regardless of whether the transaction is committed or not, it will be recorded for data recovery. When the database fails, the InnoDB storage engine will use redo log to recover to the time before the failure to ensure the integrity of the data. Set the parameter innodb_flush_log_at_tx_commit to 1, then the redo log will be synchronously written to the disk when the commit is executed.

The difference between bin log and redo log:

1. bin log will record all log records, including logs of storage engines such as innoDB and MyISAM; redo log only records the transaction log of innoDB itself
2. The bin log is only written to the disk before the transaction is committed, and a transaction is only written once, no matter how large the transaction is; while the transaction is in progress, there will be redo log continuously written to the disk
3. Binlog is a logical log, which records the original logic of SQL statements; redo log is a physical log, which records what changes have been made on a certain data page.

undo Log

In addition to recording the redo log, when the data is modified, it will also record the undo log. The undo log is used to withdraw the data. It retains the content before the modification. Transaction rollback can be realized through undo log, and MVCC can be realized by backtracking to a specific version of data based on undo log.

Query log

Record all information requested to MySQL, regardless of whether the request is executed correctly.

MySQL> show variables like '%general_log%';
+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log      | OFF                              |
| general_log_file | /var/lib/MySQL/VM_0_7_centos.log |
+------------------+----------------------------------+

MySQL architecture

MySQL is mainly divided into Server layer and storage engine layer:

• Server layer : Mainly includes connectors, query caches, analyzers, optimizers, executors, etc. All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, functions, etc., and A general log module binglog log module.
• storage engine : Mainly responsible for data storage and reading. The server layer communicates with the storage engine through APIs.

Server layer basic components

• connector: When the client connects to MySQL, the server layer will perform identity authentication and permission verification on it.
• query cache: When executes a query statement, it will first query the cache (removed after MySQL 8.0 version), first verify whether the sql has been executed, if the cache key (sql statement) is hit, it will be directly returned to the client If there is no hit, subsequent operations will be performed. It is not recommended to use the cache for MySQL queries, because query cache invalidation may be very frequent in actual business scenarios and is not recommended.
• Analyzer: does not hit the cache, the SQL statement will pass through the analyzer, which is divided into two steps, lexical analysis and grammatical analysis. First look at what the SQL statement does, and then check whether the SQL statement syntax is correct.
• optimizer: The optimizer optimizes the query, including rewriting the query, determining the read and write order of the table, and selecting the appropriate index, etc., to generate an execution plan.
• executor: first check whether the user has permission before executing. If there is no permission, it will return an error message. If it has permission, it will call the engine interface according to the execution plan and return the result.

Syntax parser and preprocessing

MySQL parses SQL statements through keywords to generate a parse tree.

The MySQL parser uses MySQL grammar rules to verify and parse queries, such as verifying that the correct keywords are used, the order of the keywords is correct, and that the quotation marks are correctly matched before and after.

The preprocessor will further check whether the parse tree is legal, such as checking the existence of data tables and data columns, and then verifying permissions.

Query optimizer

The optimizer will find an execution plan that it thinks is optimal.

Types of optimization that MySQL can handle:

1. Redefine the association order of the tables. The association of data tables is not always done in the order specified in the query.
2. Use equivalent transformations to simplify expressions. For example, convert 5=5 AND a > 5 to a > 5 .
3. Optimize COUNT/MIN/MAX. The minimum value of the MIN query corresponds to the first row of the b+ tree index. The optimizer treats this expression as a constant.
4. Comparison of list IN(). In many database systems, IN completion is equivalent to multiple OR clauses. MySQL is different. MySQL sorts the data in the IN list first, and then determines whether the value of the list meets the requirements through binary search. The time complexity is O(logN), and the time complexity of OR query is O(N). When the IN list has a large number of values, the processing speed will be faster than the OR query.
5. Covering index scan.
6. Convert external connections into internal connections. In some cases, an outer connection may be equivalent to an inner connection.

Query execution engine

In the parsing and optimization phase, MySQL will generate an execution plan corresponding to the query, and MySQL's query execution engine will call the storage engine interface to complete the entire query according to this execution plan.

Query statement execution flow

The execution process of the query statement is as follows: permission verification, query cache, analyzer, optimizer, permission verification, executor, and engine.

Check for phrases:

select * from user where id > 1 and name = '大彬';

1. Check the permissions, and return an error if there is no permission;
2. MySQL used to query the cache, if the cache hits, it will be returned directly, if not, the next step will be executed;
3. Lexical analysis and grammatical analysis. Extract the table name, query conditions, and check whether there are errors in the syntax;
4. Two execution schemes, first check id > 1 or name ='Dabin', the optimizer chooses the scheme with the best execution efficiency according to its own optimization algorithm;
5. Check the authority, call the database engine interface if there is authority, and return the execution result of the engine.

Update statement execution process

The update statement execution process is as follows: analyzer, permission check, executor, engine, redo log (prepare state), binlog, redo log (commit state)

Update statement:

update user set name = '大彬' where id = 1;

1. First query the record with id 1 and if there is a cache, the cache will be used
2. Get the query result, update the name to Dabin, then call the engine interface, write the updated data, the innodb engine saves the data in the memory, and records the redo log at the same time, the redo log enters the prepare state, and then tells the executor to execute Finished, you can submit it at any time.
3. After receiving the notification, the executor records the binlog, then calls the engine interface, and submits the redo log as the submission state.
4. update completed.

After recording the redo log, why not submit it directly and enter the prepare state first?

Suppose you write the redo log and submit directly, then write the binlog. After the redo log is written, the machine hangs and the binlog log is not written. After the machine restarts, the machine will recover the data through the redo log, but at this time the binlog does not Record this data, and subsequent machine backups will lose this piece of data, and the master-slave synchronization will also lose this piece of data.

Assuming that the binlog has been written and the machine restarts abnormally. Because there is no redo log, the machine cannot restore this record, but the binlog has a record. The same reason as above will cause data inconsistency.

Slow query

The query time of sql statement exceeds (not including equal to) long_query_time, which is called slow query.

View the slow query configuration:

show variables  like '%slow_query_log%'; #查看慢查询配置
set global slow_query_log=1; #开启慢查询

Using set global slow_query_log=1 open the slow query log only takes effect for the current database, and it will become invalid if MySQL is restarted. If you want to take effect permanently, you must modify the configuration file my.cnf.

slow_query_log =1slow_query_log_file=/tmp/MySQL_slow.log #系统默认会给一个缺省的文件host_name-slow.log

By default, the value of long_query_time is 10 seconds, which can be modified by command or in the my.cnf parameter.

show variables like 'long_query_time%';set global long_query_time=4; #需要重新连接或新开一个会话才能看到修改值或者使用show global variables like 'long_query_time'

MySQL database supports two simultaneous log storage methods, which can be separated by commas when configuring, such as: log_output='FILE,TABLE'.

Logging to the system's dedicated log table consumes more system resources than logging to a file. Therefore, if you need to enable slow query logging and need to be able to obtain higher system performance, it is recommended to record to a file first.

mysqldumpslow

If you manually find and analyze SQL by yourself, it is obviously a personal effort. MySQL provides a log analysis tool mysqldumpslow.

Get the 10 SQL statements with the longest execution time:

mysqldumpslow -s al -n 10 /usr/local/MySQL/data/slow.log

Partition Table

The partition table is an independent logical table, but the bottom layer is composed of multiple physical sub-tables.

When the data of the query condition is distributed in a certain partition, the query engine will only query in a certain partition instead of traversing the entire table. At the management level, if you need to delete the data of a certain partition, you only need to delete the corresponding partition.

Partition table type

1. Partition by range.

   CREATE TABLE test_range_partition(    id INT auto_increment,    createdate DATETIME,    primary key (id,createdate)) PARTITION BY RANGE (TO_DAYS(createdate) ) (   PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20210201') ),   PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20210301') ),   PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20210401') ),   PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20210501') ),   PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20210601') ),   PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20210701') ),   PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20210801') ),   PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20210901') ),   PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20211001') ),   PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20211101') ),   PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20211201') ));insert into test_range_partition (createdate) values ('20210105');insert into test_range_partition (createdate) values ('20210205');

The /var/lib/mysql/data/ . Each partition table has a table file named with # separation:

-rw-rw---- 1 mysql mysql    65 Aug 21 09:24 db.opt
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201801.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201802.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201803.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201804.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201805.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201806.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201807.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201808.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201809.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201810.ibd
-rw-rw---- 1 mysql mysql 98304 Aug 21 09:27 test_range_partition#P#p201811.ibd
-rw-rw---- 1 mysql mysql  8598 Aug 21 09:27 test_range_partition.frm
-rw-rw---- 1 mysql mysql   116 Aug 21 09:27 test_range_partition.par

2. list partition. For the List partition, the partition field must be known. If the inserted field is not in the enumerated value at the time of the partition, it cannot be inserted.

   create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

3. Hash partition can evenly distribute data into predefined partitions.

   drop table test_hash_partiotion;
   create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

Partition problem

1. The cost of opening and locking all underlying tables can be high. When querying to access a partitioned table, MySQL needs to open and lock all underlying tables. This operation occurs before partition filtering, so partition filtering cannot be used to reduce this overhead, which will affect the query speed. This type of overhead can be reduced through bulk operations, such as bulk inserts, LOAD DATA INFILE, and deleting multiple rows of data at a time.
2. The cost of maintaining partitions can be high. For example, to reorganize a partition, it will first create a temporary partition, then copy the data to it, and finally delete the original partition.
3. All partitions must use the same storage engine.

Query optimization

The biggest advantage of partitioning is that the optimizer can filter out some partitions based on the partitioning function, allowing the query to scan less data. Adding a partition column to the query condition allows the optimizer to filter out the partitions that do not need to be accessed. If the query condition does not have a partition column, MySQL will let the storage engine access all partitions of the table. It should be noted that the partition column in the query conditions cannot use expressions.

other

processlist

select * will query for unnecessary and additional data, and then these additional data are transmitted on the network, which brings additional network overhead.

show processlist or show full processlist can check whether the current MySQL is under pressure, running sql, and whether slow SQL is being executed.

• id -thread ID, you can use: kill id; kill a thread, very useful
• db -database
• user -user
• host -the host IP of the library
• command -currently executed command, such as the most common: Sleep, Query, Connect, etc.
• time -elapsed time, in seconds, very useful

• state -execution state

  sleep, the thread is waiting for the client to send a new request

  query, the thread is querying or sending the results to the client

  Sorting result, the thread is sorting the result set

  Locked, the thread is waiting for the lock

• info -SQL statement executed, very useful

exist and in

exists is used to filter external table records.

exists will traverse the outer table and substitute each row of the outer query table into the inner query for judgment. When the conditional statement in exists can return the record row, the condition is true and the current record of the outer table is returned. Conversely, if the conditional statement in exists cannot return a record row, and the condition is false, the current record of the outer table is discarded.

select a.* from A a
where exists(select 1 from B b where a.id=b.id)

in is to first find out the following statement and put it in the temporary table, then traverse the temporary table, and substitute each row of the temporary table into the outer query to find it.

select * from A
where id in(select id from B)

When the table of the subquery is large, using EXISTS can effectively reduce the total number of loops to increase the speed; when the table of the external query is large, using IN can effectively reduce the loop traversal of the external query table to increase the speed.

Reference materials:

High-performance MySQL books

MVCC realization principle: https://zhuanlan.zhihu.com/p/64576887

Multi-version concurrency control mechanism: https://www.cnblogs.com/axing-articles/p/11415763.html

Exclusive lock analysis: https://blog.csdn.net/claram/article/details/54023216

Partition table: https://www.cnblogs.com/wy123/p/9778590.html

How to perform a SQL statement in MySQL: https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247485097&idx=1&sn=84c89da477b1338bdf3e9fcd65514ac1&chksm=cea24962f9d5c074d8d3ff1ab04ee8f0d6486e3d015cfd783503685986485c11738ccb542ba7&token=79317275&lang=zh_CN#rd