What's the best way to get the most out of MySQL? Interviews: Ten classic MySQL must-ask interview questions compiled overnight

I. How do you understand Count(*) and Count(1)?

There is no difference between these two, don't think count() will look up all the fields, whereas count(1) will not. So count() would be slower, do you think the MySQL authors would do that?

I can tell you very clearly that count() is the same as count(1), but the correct difference is count(field). If you count() a specific field, then MySQL determines whether the corresponding field in a row is null, and if it is null, it doesn't count it. Therefore, the result of count(field) may be less than the result of count().) and count(1).

In addition, the direct implementation of select (*) from t1; when you can also take advantage of the index, not necessarily a full table scan, you can also scan the leaf nodes of the B+ tree of a particular index, so as to get the total number of entries, because no matter what the index, the primary key index or auxiliary index, in fact, the number of their nodes in the leaf is the same, but the number of fields are not the same, the primary key index stored all the fields The primary key index stores all the fields, while the secondary index only stores the defined index fields + the primary key fields, so usually the secondary index is more space-consuming, so traversal will be faster, but the number of records is the same.

Second, how do you understand the leftmost prefix principle?

This principle suggests that conditions can only be optimized on columns in the left-hand portion of the composite index. In other words, when using a composite index, the conditions of the query should start from the leftmost column of the index in order to maximize the use of the index

We create a simple example table namedemployees, and create a composite index on it. The table structure is as follows:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    INDEX idx_name_age (last_name, first_name, age)
);

Next, we insert some example data

INSERT INTO employees (first_name, last_name, age, department) VALUES
('John', 'Doe', 30, 'HR'),
('Jane', 'Doe', 25, 'IT'),
('Mary', 'Smith', 35, 'Finance'),
('Michael', 'Johnson', 40, 'IT'),
('Emily', 'Davis', 29, 'HR');

Query SQL that conforms to the leftmost prefix principle

SELECT * FROM employees WHERE last_name = 'Doe' AND first_name = 'Jane' AND age=25;
SELECT * FROM employees WHERE  first_name = 'Jane' AND last_name = 'Doe' AND age=30;
SELECT * FROM employees WHERE  age=30 AND last_name = 'Doe' AND first_name = 'Jane';

For these queries, MySQL will use the idx_name_age index, from which it can be seen that the above SQL can go to the index, and the order of the Where condition is not relevant

+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | range | idx_name_age  | idx_name_age | 100     | NULL |     2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+

In this execution plan, we see that the type is RANGE, indicating that MySQL is using the idx_name_age index and only examining about 2 rows of data.

What about if you remove the last_name?

Queries that do not satisfy the leftmost prefix principle

SELECT * FROM employees WHERE first_name = 'Jane' AND age = 25;

For this query, MySQL will not use the compound index idx_name_age because it does not start with the leftmost column last_name.

View index execution by Explain execution plan

+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | employees | ALL   | NULL          | NULL | NULL    | NULL |     5 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+-------+-------------+

In this execution plan, we see that the type is ALL, which means that MySQL didn't use any indexes and did a full table scan, which is less efficient.

Recently, I inadvertently obtained a copy of the brush notes written by the big brother of Ali, which immediately opened my two veins, and it turned out to be not so difficult to enter the big factory. This is written by the big brother. 7,701 pages of brush-up notes written by BAT bigwigs, which made me get a soft offer

summarize

From this, we can see that the so-called leftmost prefix principle of the "leftmost", does not mean that the last_name in the where condition must be in the leftmost, but means that the where condition must give the definition of the joint index of the leftmost field, for example, we define "last_name, first_name, age" joint index SQL as follows: "last_name, first_name, age name, first_name, age" joint index SQL for example:

INDEX idx_name_age (last_name, first_name, age)

The last_name field is the leftmost field, so if you want to go to the idx_name_age index, then SQL must give the condition of the last_name field, which is the meaning of "leftmost".

Third, how do you understand row locks, GAP locks, and pro-health locks?

1. Number of rows

Row locks are locks on specific rows of data that allow multiple transactions to concurrently operate on different rows, blocking other transactions only when writing on the same row

Suppose we have the following table structure and data:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

If transaction A updates information about a specific employee:

-- transaction A
START TRANSACTION.
UPDATE employees SET age = 31 WHERE last_name = 'Doe';

During this process, row locks are added to the rows corresponding to last_name = 'Doe' (i.e. John and Jane). If at this point transaction B tries to update the same row:

-- Transaction B
START TRANSACTION.
UPDATE employees SET age = 29 WHERE last_name = 'Doe';

Transaction B will be blocked until the transaction A commit or rollback, because transaction A, transaction B are added to the exclusive lock, also known as pessimistic lock. This row lock ensures the consistency of the data.

GAP lock

Row lock lock is a row, while the GAP lock lock is the gap in front of the line, note that only the gap in front of the line, you may ask that the last line of the table before and after the gap ah, the last line behind the gap is not locked?

Of course it will lock, only no, over is given to a record called PAGE_NEW_SUPREMUM in terms of, you can understand that the PAGE_NEW_SUPREMUM record is InnoDB's default, which is fixed as the last record, so as long as you lock the gap in front of the PAGE_NEW_SUPREMUM, that's equivalent to locking the gap behind the last row, as we understand it. of the gap after the last row.

Next-Key Lock

A critical lock is a combination of a row lock and a GAP lock that locks specific rows of data as well as gaps between rows. It is used to ensure that not only does it prevent phantom reads, but it also protects row data in a range of queries

Continuing with the previous example, suppose we perform the following.

-- transaction D
START TRANSACTION;
SELECT * FROM employees WHERE last_name >= 'D' FOR UPDATE;

In this query, MySQL puts a row lock on all rows with last_name of 'D' and after, and a GAP lock on the gap before 'D', which prevents new rows from being inserted in that range.

IV. How do you understand MVCC?

The OCR recognition resulted in some errors and confusion, resulting in a lack of clarity. While the recognition is not ideal, I will outline what the image may convey based on my understanding and knowledge of the subject.

How to understand MVCC?

The so-called MVCC is Multi-Version Concurrency Control, MySQL in order to achieve repeatable reads of this isolation level, and in order not to use the locking mechanism to achieve repeatable reads, so the MVCC mechanism to achieve.

Key concepts

ReadView：
- When a transaction starts, MVCC creates aReadViewThis view records all versions that are currently visible, including which transactions are active and which have committed.
Transaction ID：
- Each transaction has a unique transaction ID. in theReadViewin which the current transaction ID, the minimum transaction ID, and the maximum transaction ID are recorded.
visibility rule：
- If the ID of a transaction：
  1. more thanReadViewMaximum transaction ID in: changes in this transaction are not visible to the current transaction.
  2. be part ofReadViewactive transaction: then changes to that transaction are not visible because the transaction is still in progress.
  3. less thanReadViewMinimum transaction ID in: the changes in this transaction are visible because it has been committed.

MVCC Workflow

Create ReadView：
- When the transaction starts, MVCC generates aReadView. It will contain the ID of the current transaction, the ID of the active transaction, and the maximum and minimum transaction IDs.
retrieve data：
- When a transaction reads data, it will refer to theReadViewinformation in the data to determine which versions of the data are visible.
Updated data：
- When a transaction updates data, MVCC does not directly overwrite the original data, but creates a new version. The old version is only cleaned up after all transactions referencing that data have completed.

summarize

MVCC allows multiple transactions to operate simultaneously without interfering with each other, which greatly improves database concurrency performance. By maintaining multiple versions of data, MVCC ensures data consistency and isolation while reducing lock contention.

Recently, I inadvertently obtained a copy of the brush notes written by the big brother of Ali, which immediately opened my two veins, and it turned out to be not so difficult to enter the big factory. This is written by the big brother. 7,701 pages of brush-up notes written by BAT bigwigs, which made me get a soft offer

V. How do you understand Online DDL?

Online DDL refers to modifying the structure of database tables without affecting the database services. In layman's terms, it means that we can make adjustments to the tables, such as adding new columns, modifying field types, adding indexes, and so on, while the database is functioning normally, without the need for downtime for maintenance.

A DDL-like operation, such as adding a new field, would have the following steps

1. Parsing and Inspection

MySQL first parses the DDL statement to ensure that the syntax is correct. For example:

ALTER TABLE users ADD COLUMN age INT;

MySQL checks the tableusers Existence, addition of newage Whether the field conflicts with an existing field (e.g., duplicate field name), whether the data type is supported, etc.

2. Metadata lock for tables

Before making any changes to the table structure, MySQL adds a metadata lock (MDL) to the table. The purpose of the MDL is to prevent other DDL operations from modifying the table while the structure is being changed, and to ensure that the table structure is consistent.

analogies: A metadata lock is like filling shelves in a supermarket to prevent others from coming along and changing the shelf positions at the same time to avoid confusion.

3. Creating Temporary Tables

When we execute theALTER TABLE statement, MySQL will create atemporary table. This temporary table is a replica of the existing table and will add new fields as per our requirements.

Steps for temporary tables:

Duplicate the original table structure: MySQL will copy the structure of the original table into a temporary table with the fields we added, for exampleage。
Copy data: MySQL copies all rows of data from the original table row by row to the temporary table, while populating each row with the default values of the newly added fields, if any.

analogies: It's like upgrading supermarket shelves by first building a new shelf model in the warehouse that holds the same items while adding new storage areas for the items.

4. switching table

When MySQL finishes replicating data, it replaces the original table with the temporary table. At this point, the temporary table becomes the official table, containing the new fields.

During this process, all DML (add, delete, and modify) operations are temporarily hung until the replacement is complete. This is a short period of time and has very little impact on the service.

analogies: It's like building new shelves in a warehouse and moving them into a supermarket while replacing the old ones. Customers will hardly notice the process.

5. Delete old table

After the original table is replaced by the new table, MySQL automatically deletes the metadata of the old table to free up space. This step is done in the background and does not affect the normal operation of the database.

6. release a lock

When all operations are complete, MySQL releases the metadata lock, allowing other DDL or DML operations to continue.

Summary:

Parses the statement and checks for legality.
Add metadata locks to tables (to prevent conflicting structural changes).
Create a temporary table and copy data from the old table to the temporary table.
Replaces the old table and deletes the metadata of the old table.
Release the lock.

take note of: This method is used inNot using Online DDL case, it may result in a large number of data replication operations, which in turn has an impact on performance, especially when the amount of table data is large.

VI. Do you know what circumstances will lead to index failure

In MySQL, indexes are a key tool for improving query efficiency, but sometimes you may encounter an indexlose effectivenesssituation, resulting in a significant drop in query performance. This situation is usually related to the way the query statement is written, the choice of data type and the database optimization mechanism. Here are a few common scenarios that can lead to index failure:

1. utilization`LIKE` when preceded by a wildcard character

If there is a change in theLIKE statement, the wildcard% Placing it at the beginning of the string causes the index to fail. This is because in this case, MySQL cannot quickly locate the eligible rows through the index and needs to scan all rows.

Example:

SELECT * FROM users WHERE name LIKE '%abc'; -- index failure

This way of writing causes MySQL to scan the entire table, whereas if it were written asLIKE 'abc%', the index is still valid.

analogies: It's like if you're looking for a file name that starts with "abc" in a pile of files, you can just find the appropriate section, but if you're looking for a file whose name contains "abc", you have to look at each file name.

2. Inconsistent data types

When the type of a field in a query condition does not match the type of an index field, MySQL may not use the index. It will first perform a type conversion on the data, which can result in an inability to efficiently utilize the index.

Example:

suppose that...id is an integer field:

SELECT * FROM users WHERE id = '123'; -- index failure

Here.'123' is a string, MySQL implicitly converts it, so the index fails.

3. Using Functions on Index Fields

If a function or arithmetic operation is used on an indexed field in a query, MySQL cannot query through the index, causing the index to fail.

Example:

SELECT * FROM users WHERE YEAR(created_at) = 2023; -- index failure

here areYEAR(created_at) Yes, it is.created_at field performs a function operation, so MySQL cannot use the index directly for lookups.

analogies: It's like trying to find content in a list arranged in a certain pattern, but you need to change its form before you can find it, leading to a loss of efficiency.

4. utilization`OR` Keywords.

(coll.) fail (a student)OR When a column in the condition is not indexed, the indexing of the entire query fails.

Example:

SELECT * FROM users WHERE id = 1 OR name = 'Alice'; -- index failure

suppose that...id columns are indexed, and thename columns are not indexed, then this query cannot utilize indexes and MySQL needs to perform a full table scan.

Optimization method: forname fields are indexed separately, or the query logic is rewritten to avoid theOR The indexing failure caused by the

5. Inequality conditions (`!=` maybe`<>`)

Using the not-equal operator (!= maybe<>) when MySQL cannot use indexes effectively, which can result in a full table scan.

Example:

SELECT * FROM users WHERE age ! = 30; -- index invalid

This type of query usually results in index failure because MySQL is unable to locate all the queries that satisfy the!= of the record.

6. Scope Query (`>`, `<`, `BETWEEN`) after columns

In composite indexes (i.e., multi-column indexes), when a range query is used for the first field, the indexes for subsequent fields may fail.

Example:

Suppose we have a compound index(age, name), the following query:

SELECT * FROM users WHERE age > 30 AND name = 'Alice'; -- `name` Indexing Failure

In this case, due to theage Having used a range query.name columns will be invalidated and MySQL will not be able to perform direct lookups through the composite index.

7. The index columns are prefixed with`IS NULL` maybe`IS NOT NULL`

For indexed columns use theIS NULL maybeIS NOT NULLSometimes MySQL may not utilize indexes, especially when a large amount of data exists.NULL value, MySQL considers the index to be uneconomical to use.

Example:

SELECT * FROM users WHERE name IS NOT NULL; -- possible index failure

8. The query condition uses the`NOT IN` maybe`NOT EXISTS`

utilizationNOT IN maybeNOT EXISTS It may also cause MySQL to not use indexes, which triggers a full table scan.

Example:

SELECT * FROM users WHERE id NOT IN (1, 2, 3); -- index not working

9. The amount of data in the table is small

When the amount of data in a table is small, MySQL may consider a full table scan more efficient than using an index. In this case, MySQL chooses a direct scan rather than a lookup through an index.

analogiesIf you only have a few files to look for, it's not cost-effective to take the time to create a directory index first; it's faster to just scan them all.

10. MySQL Optimizer Chooses Not to Use Indexes

Sometimes, even if an index is available, MySQL's query optimizer may drop the index based on table statistics and cost estimates that a full table scan is faster than using an index.

Summary:

Index failures are usually related to the way query statements are written, data types, function usage, and decisions made by the MySQL query optimizer. In order to avoid index failure, you need to try to avoid the above common scenarios such as:

existLIKE Avoiding wildcards in queries% beginning
Keeping data types consistent
Try not to use functions or operations on indexed columns.
Proper planning of query order in composite indexes

VII. How do you understand MySQL filesort?

In layman's terms, you can think of filesort as the database's "alternate sorting method". When the ORDER BY statement in a query cannot utilize the sort order in the index, MySQL enables filesort to manually sort the results.

When will it be triggered?`filesort`？

MySQL will use thefilesort, for example:

No proper indexing：
When the query in theORDER BY field is not indexed, MySQL cannot utilize the index order, but can only use it with the help of thefilesort to sort.

Example:
```
SELECT * FROM users ORDER BY age;
```
suppose that...users Not in the tableage field's index, at which point MySQL performs afilesort。
Multi-column sort, but indexes don't match：
When we sort multiple columns that are not covered by an index or the index order does not match the sorting requirements, thefilesort It will also be triggered.

Example:
```
SELECT * FROM users ORDER BY age, name;
```
Assuming that the table has onlyage indexes, but not the(age, name) composite index, then MySQL will use thefilesort。
Index failure due to combinatorial queries or function operations：
When computational or functional operations are performed on fields in a query, even if those fields are indexed, the index cannot be directly utilized for sorting.

Example:
```
SELECT * FROM users ORDER BY LENGTH(name);
```
LENGTH(name) is a function operation, and MySQL needs to sort it manually, so it will use thefilesort。

`filesort` The way it works:

filesort There are actually two implementations, depending on the version and configuration of MySQL:

Single row data sorting(Older Versions): MySQL puts all the rows of the query result into a buffer, and then it will use them for the query according to theORDER BY Fields are compared and sorted line by line. This approach is relatively inefficient because of the amount of data to be processed.
Two scanning sorts(Optimized Versions): In newer versions of MySQL, thefilesort will be optimized and will only collect the fields to be sorted on the first scan and theROW_ID, and then passes the sortedROW_ID Then go back and read the whole row of data. This approach reduces the amount of data to sort and improves performance.

filesort performance impact:

filesort It is not that every time a disk operation will be involved, it is possible that it will be done in memory, but when the amount of data is large and memory is not enough to complete the sorting, it is possible to write the data to disk for the sorting, which will affect the performance.

MySQL has two important parameters to controlfilesort Behavior:

sort_buffer_size: This is the size of the buffer MySQL uses to sort in memory. If the sorted data can fit in this buffer, the sorting is done in memory; otherwise, MySQL writes some of the data to disk, which affects performance.
max_length_for_sort_data: Controls which MySQL uses whichfilesort method (single-row sort or two-scan sort). For shorter data, MySQL is more likely to choose the more efficient two-scan sort method.

How to avoid or optimize`filesort`？

Using the right index：
The most straightforward approach is to create indexes for the sorted fields in the query. This is especially true when there areORDER BY clause, ensuring that a compound index has been created can be an effective way to avoid thefilesort。

Example:
```
CREATE INDEX idx_age_name ON users (age, name);
```
risesort_buffer_size：
If it can't be avoidedfilesort, which can be changed by increasing the number ofsort_buffer_size size, ensuring that more data can be sorted in memory and reducing disk I/O.
Reducing the amount of data for sorting：
utilizationLIMIT to limit the size of the query result set can reduce the amount of data that needs to be sorted, thus reducing the amount offilesort The overhead.

Example:
```
SELECT * FROM users ORDER BY age LIMIT 100;
```
Try to avoid using functions for sorted fields：
existORDER BY You can increase the likelihood that MySQL will use indexing by trying not to perform function operations or expressions on sorted fields.

Summary:

filesort is a sorting mechanism in MySQL that is enabled when the results of a query cannot be sorted sequentially by an index.filesort Manual sorting. Although the name has "file" in it, sorting does not necessarily involve disk operations; in-memory sorting is also common.filesort is MySQL's alternate sorting method, and although it is sometimes unavoidable, we can optimize its performance by creating indexes, resizing buffers, and so on.

VIII. Do you know what situations lock the table?

I. Common locking table situations

1. DDL operations (Data Definition Language)

When performing some structural change operations (e.g.ALTER TABLE、CREATE TABLE、DROP TABLE), MySQL locks the entire table, preventing other threads from operating on the table. This lock ismetadata lock(Metadata Lock), which is used to ensure that changes to the table structure do not conflict with other concurrent operations.

Example:

ALTER TABLE users ADD COLUMN age INT;

This operation locks the table, and other pairs ofusers Table operations are blocked until the change is complete.

2. full table scan`UPDATE` maybe`DELETE` manipulate

When you execute aUPDATE maybeDELETE operation and no index is used, MySQL may lock the entire table for updates or deletions because it must scan all rows.

Example:

UPDATE users SET age = 30 WHERE name LIKE '%John%'; -- may lock table if not indexed

due toLIKE '%John%' Unable to utilize indexes, MySQL requires a full table scan and a lock on the entire table.

3. Write operations in transactions (`INSERT`、`UPDATE`、`DELETE`）

In the InnoDB storage engine, a write operation adds to a row of data theline lock(Row Lock). However, in some cases (e.g., without indexing), MySQL may degrade to ameter lock（Table Lock.) Even row locks may block other transactions in the event that a long transaction is not committed or rolled back, creating an indirect locking manifestation.

Example:

BEGIN.
UPDATE orders SET status = 'shipped' WHERE order_id = 1234; -- row lock, but possibly table lock
COMMIT.

If the transaction has been running for a long time and other operations also need access to theorders records in the table, which may result in a wait.

4. `LOCK TABLES` explicit locking

MySQL supports the use ofLOCK TABLES command explicitly locks the table, which is divided into theREAD LOCKcap (a poem)WRITE LOCK. During a write lock, no other thread can perform any read or write operations on the table; during a read lock, other threads can only read the table, not write it.

Example:

LOCK TABLES users WRITE; -- Put a write lock on the `users` table.

At this point, the other threads are not interested in theusers Any read or write operations on the table are blocked until the lock is released.

5. Bulk insertion of data

When you use certain bulk insert statements (such as theINSERT INTO ... SELECT ... maybeINSERT IGNORE) When inserting large amounts of data without proper indexing, MySQL may lock the table, especially in the MyISAM storage engine.

Example:

INSERT INTO new_users SELECT * FROM users WHERE created_at > '2023-01-01'; -- Possible lock table

in the event thatcreated_at Without an index, MySQL needs to lock the table to complete the entire insert operation.

6. Foreign Key Constraint Checking

In InnoDB, when inserting or deleting data that involves foreign key constraints, MySQL may lock the parent or child table to ensure data integrity. Although InnoDB uses row locks in most cases, certain complex situations, such as not having the proper indexes, may result in table locks.

Example:

DELETE FROM orders WHERE order_id = 100; -- Triggers a foreign key constraint check, possibly locking the `customers` table

in the event thatorders table has a foreign key associated with thecustomers table and does not have a proper index, it may lock thecustomers Table.

7. Read and Write Operations in MyISAM Storage Engine

In the MyISAM storage engine, write operations lock the entire table, even if only one row is modified. Read operations do not block each other, but blocking can occur between read and write operations. As a result, MyISAM tables may lock frequently when handling highly concurrent write operations.

Example:

INSERT INTO myisam_table (name, age) VALUES ('John', 30); -- Write locks the entire table

If there are a large number of write operations, the table will be locked frequently, affecting concurrency performance.

Second, how to avoid locking the table?

Locking tables affects database concurrency and performance, so we usually need to avoid it as much as possible. Here are some ways to minimize the occurrence of locking tables:

1. Using the right storage engine

Try to useInnoDB storage engine, which supportsline lockthat can avoid locking the table in the vast majority of cases. In contrast.MyISAM Using theepilock, with poor performance in concurrent read and write scenarios.

2. Creating the right index

Avoid full table scans by creating appropriate indexes for the columns in the query condition. For example, if you frequently run a query based on thename fields are queried and updated, they should bename field to create an index:

CREATE INDEX idx_name ON users (name);

Indexes can effectively reduce the possibility of locking tables.

3. Reduction of long services

Transactions that remain uncommitted for long periods of time hold locks, which can block other queries. Therefore, try to minimize the execution time of transactions and ensure that operations are completed and committed as soon as possible within the transaction.

4. utilization`OPTIMIZE` cap (a poem)`ANALYZE` prudent

These commands lock the table metadata and prevent concurrent read and write operations. Highly concurrent time periods should be avoided when running these commands.

5. batch operation

If you need to perform a large number ofUPDATE maybeDELETEYou can execute operations in batches to reduce the amount of data involved in each operation and avoid locking tables for long periods of time.

Example:

DELETE FROM users WHERE created_at < '2022-01-01' LIMIT 1000; -- batch delete

6. Avoiding Explicit Table Locks

Try to avoid usingLOCK TABLES Explicit locking operations, especially in highly concurrent scenarios. InnoDB's transactional mechanism and row-level locking are sufficient for most concurrency problems.

IX. How do you understand the deadlock mechanism in MySQL?

In MySQL, thedeadlock(Deadlock) is when two or more transactions wait for a lock held by the other, causing them all to be unable to continue execution. This is a common concurrency problem, especially in highly concurrent situations where transactions are prone to deadlock when accessing the same data resources.

Common understanding:

A deadlock can be compared to two people walking on a narrow road, they need each other to give way in order to continue to move forward. a blocked the way of B, B blocked the way of A, who refused to give way, and the result is that both people are stuck. This is manifested in the database as transaction A waiting for transaction B to release resources, while transaction B is also waiting for transaction A to release resources, and ultimately the two transactions can not continue.

Deadlock generation process:

Transaction A acquires a lock on resource X。
Transaction B acquires a lock on resource Y.。
Transaction A tries to acquire a lock on resource Y, but resource Y is locked by transaction B. Transaction A enters a wait state.
Transaction B tries to acquire a lock on resource X, but resource X is already locked by transaction A, and transaction B enters a wait state.
The result is: transaction A waits for B, and transaction B waits for A, creating a circular wait that generates a deadlock.

Deadlock Mechanisms in MySQL:

Storage Engines Used by MySQLInnoDB Row-level locking is provided, which reduces the probability of lock conflicts but is also more likely to lead to deadlocks.InnoDB proactively detects and resolves the issue when it encounters a deadlock, breaking the deadlock by rolling back one of the transactions.

Deadlock handling:

In MySQL, when InnoDB detects a deadlock, it chooses to roll back theLeast costly services, which usually rolls back transactions that lock up fewer resources. It then returns an error message to the client, similar to:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

When a transaction is rolled back, another transaction can continue to execute, solving the deadlock problem.

How to avoid deadlocks?

Although MySQL automatically detects and handles deadlocks, frequent deadlocks can affect system performance, so it is essential to avoid them as much as possible. Here are some common ways to avoid deadlocks:

1. Sequence of fixed locks

Ensure that all transactions follow the same order of locking resources when accessing multiple tables or records. This avoids cross-locking between different transactions and reduces the possibility of deadlocks.

Example:
All transactions are updated in theusers tables andorders When the table is locked, it's all locked firstusers Table, then lockorders table to avoid deadlocks.

2. Reduced locking range

Minimize the scope and duration of each transaction lock to avoid long lock occupancy. For example, minimize transaction execution time and reduce unnecessary queries.

Example:

BEGIN;
UPDATE users SET age = age + 1 WHERE id = 1;
COMMIT;

Try to avoid performing too many operations within a transaction or waiting for user input.

3. Using the right index

Try to use indexes when querying to minimize the number of locked rows, especially when theUPDATE cap (a poem)DELETE operation, proper indexing can reduce the number of locked rows, thus reducing the risk of deadlocks.

Example:
because ofuser_id Create an index:

CREATE INDEX idx_user_id ON orders (user_id);

4. Reduced concurrency

Control concurrent access to the database and, if possible, avoid large data operations in highly concurrent situations. Highly concurrent access increases the probability of deadlocks.

5. Setting Transaction Isolation Levels Properly

Locking conflicts can be minimized by using the appropriate transactional isolation level.InnoDB supports several transactional isolation levels, the most common of which areREPEATABLE READ cap (a poem)READ COMMITTED. Among other things.READ COMMITTED The isolation level reduces lock contention and thus reduces the probability of deadlocks occurring.

Example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

6. Reduce lockup time by batch operation

If you need to perform update or delete operations on a large amount of data, consider batch processing to reduce the number of rows locked by each transaction, thus reducing the risk of deadlock.

Example:

DELETE FROM orders WHERE order_date < '2023-01-01' LIMIT 1000;

utilizationLIMIT Delete old data in batches.

How to detect deadlocks?

When a deadlock occurs, InnoDB records a deadlock message in the error log, containing information about the deadlock and the transaction and query that caused it. You can get the deadlock information by using the following SQL statement:

SHOW ENGINE INNODB STATUS;

This command displays status information for InnoDB with details of the most recent deadlock, including the transactions involved in the deadlock and the lock wait.

X. How are you optimizing for slow queries?

Optimization steps and methods for slow queries:

1. Analyzing Slow Query Logs

First, ensure thatSlow Query Log(Slow Query Log) is turned on, which is used by MySQL to log queries that take longer than a specified threshold to execute. You can analyze these logs to find the queries that are taking the longest time on your system.

Enable slow query logging:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- set slow query threshold to 1 second

After that, you can look at the slow query logs to see which queries are taking too long to execute and start optimizing from those queries.

2. utilization`EXPLAIN` Analyzing Query Execution Plans

utilizationEXPLAIN command can help you understand how MySQL executes a query. It will provide information such as whether the query uses indexes, how many rows were scanned, how it was sorted, and so on. You can find performance bottlenecks in a query by looking at the execution plan.

Example:

EXPLAIN SELECT * FROM users WHERE name = 'John';

The execution result will show the type of query (e.g.ALL、INDEX、RANGE etc.), indicating whether MySQL is using a full table scan (ALL) or the index (INDEX). If you seeALL Indicates a full table scan, which is usually a signal that optimization is needed.

How to interpret some common results:

type: ALL Indicates a full table scan that needs to be optimized;range、ref maybeconst Indicates that indexes are used and performance is better.
rows: Indicates the number of rows MySQL expects to scan, the fewer the better. The more rows scanned, the higher the query overhead.
key: Shows which index MySQL is using, if it is shown asNULL, indicating that no index is used.

3. Creating and Optimizing Indexes

Indexes are one of the most common means by which MySQL optimizes slow queries. Proper indexing can significantly reduce the number of rows scanned for a query and increase query speed.

Common indexing optimization strategies:

single-column index: A filtering condition commonly used in a query orWHERE clause to create an index on the field.
compound index: Create composite indexes for queries involving multiple conditions. For example.SELECT * FROM users WHERE age = 30 AND status = 'active';can be used for(age, status) Creates a compound index.

Example:

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_age_status ON users (age, status);

Coverage Index: If the index contains all the fields needed for a query, MySQL can read data directly from the index without accessing the table itself. This reduces I/O operations and dramatically improves query efficiency.

Example:

SELECT name FROM users WHERE age = 30; -- If both name and age are in the index, MySQL can just check the index.

Avoiding Index Failure: Ensure that the query conditions utilize the index correctly. Example:
- Avoid using functions or expressions for indexed fields such asWHERE UPPER(name) = 'JOHN' will cause the index to fail.
- Use exact matches and try to avoidLIKE '%abc' This wildcard-prefixed query.

4. Optimizing Query Statements

Improving the way query statements are written can dramatically improve performance. Here are a few common optimization suggestions:

Choosing the right data type: Try to use appropriate data types and avoid excessive field lengths. For example, useINT Store the age instead of usingVARCHAR。
Reducing the returned results of a query: AvoidSELECT *, query only the fields that are needed. The less data returned, the faster the query.

Example:

SELECT id, name FROM users WHERE age = 30; -- Avoid SELECT * and take only the required fields.

Decomposing Complex Queries: Splitting a complex query into multiple smaller queries can sometimes improve performance, especially when multiple related tables are involved. For example, splitting a query containing multipleJOIN of complex queries, split into multiple queries caching intermediate results.
utilizationLIMIT Optimize pagination: Avoid scanning large amounts of data in paging queries on large tables. This can be done by combining primary keys or indexes withLIMIT Optimization.

Example:

SELECT * FROM users WHERE id > 1000 LIMIT 10; -- index-based paging

5. make superior`JOIN` manipulate

JOIN operations are common in multi-table queries, but they tend to cause performance problems, especially when the table is large. OptimizationJOIN The precautions to be taken when doing so:

Ensure that the join condition field is indexed: ForJOIN The fields used in the make sure they are properly indexed.

Example:

SELECT * FROM orders o JOIN users u ON o.user_id =  WHERE  = 'active';

In this case.user_id cap (a poem)id It should be done separately in theorders cap (a poem)users The table is indexed.

Minimize the amount of data in related tables: By first filtering out the required data and then performing theJOIN operations. For example, placing filter criteria in a subquery reduces the number of rows that need to be associated.

Example:

SELECT * FROM (SELECT id FROM users WHERE status = 'active') u JOIN orders o ON o.user_id = ;

6. Adjusting MySQL Configuration Parameters

Some configuration parameters of MySQL directly affect query performance, especially in high concurrency and large data volume scenarios. The following are some common optimization parameters:

innodb_buffer_pool_size: This is the size of InnoDB's buffer pool and determines how much memory MySQL can use to cache data pages. This value is usually set to 70-80% of the system memory to minimize disk I/Os.
query_cache_size: If a large number of queries in the system have the same result, query caching can be enabled to reduce the overhead of duplicate queries. Note that query caching is deprecated in MySQL 8.0 because it can be a performance bottleneck for highly concurrent scenarios.
tmp_table_size cap (a poem)max_heap_table_size: These parameters determine the maximum size of the temporary table that can be used in memory; increasing the values of these parameters avoids the need to write the temporary table to disk frequently, thus improving sorting andGROUP BY The efficiency of the query.

7. Using Partitioned Tables

If your table is very large, consider using thepartition tableto optimize query performance. Partitioned tables reduce the amount of data scanned by dividing the data into smaller physical sub-tables that MySQL can locate directly in a partition based on query criteria.

Example:

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    user_id INT,
    ...
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2015),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

8. Avoid deadlocks and long transactions

If a slow query is caused by a transaction conflict or deadlock, you should try to avoid long transactions or frequent table locks. Reduce lock waiting and deadlocks by controlling the transaction scope, using appropriate isolation levels, and avoiding high-volume write operations to speed up queries.

9. (computing) cache

In addition to using MySQL's own query caching, you can also use application-level caching mechanisms (e.g., Redis, Memcached) to cache frequently accessed data into memory and reduce the frequency of database accesses.

Example:

# Cache MySQL query results at the application level
('users:active', active_users, timeout=60*5) # cache for 5 minutes

Summary:

Analyzing Slow Query Logsand useEXPLAIN Understanding the execution plan is the first step in optimizing slow queries.
Creating the right indexIt can significantly improve query speed, especially in large tables.
Optimize query statements andJOIN manipulate, minimize the number of rows scanned, and try to cover queries with indexes.
By adjustingMySQL Configuration Parameters, improving the system's utilization of memory and resources.
Use partitioned tables on large tables and reduce pressure on the database with caching

One final note (please follow, like, don't whore me out)

Recently, I inadvertently obtained a copy of the brush notes written by Ali's big brother, which immediately opened up my two chakras, and it turned out to be not so difficult to enter the big factory.
It was written by the big guy.7,701 pages of brush-up notes written by BAT bigwigs, which made me get a soft offer

This article, which has been featured on, my tech site: Programmer's Programming Resource StationThe company has a complete interview, work technology, architect growth path, and other experience to share.

Ask for a one-click trifecta: Like, Share, Favorite!

Likes are really important to me! Ask for likes online and I'd appreciate a follow!

What's the best way to get the most out of MySQL? Interviews: Ten classic MySQL must-ask interview questions compiled overnight

I. How do you understand Count(*) and Count(1)?

Second, how do you understand the leftmost prefix principle?

Query SQL that conforms to the leftmost prefix principle

Queries that do not satisfy the leftmost prefix principle

summarize

Third, how do you understand row locks, GAP locks, and pro-health locks?

1. Number of rows

GAP lock

Next-Key Lock

IV. How do you understand MVCC?

How to understand MVCC?

Key concepts

MVCC Workflow

summarize

V. How do you understand Online DDL?

1. Parsing and Inspection

2. Metadata lock for tables

3. Creating Temporary Tables

Steps for temporary tables:

4. switching table

5. Delete old table

6. release a lock

Summary:

VI. Do you know what circumstances will lead to index failure

1. utilizationLIKE when preceded by a wildcard character

Example:

2. Inconsistent data types

Example:

3. Using Functions on Index Fields

Example:

4. utilizationOR Keywords.

Example:

5. Inequality conditions (!= maybe<>)

Example:

6. Scope Query (>, <, BETWEEN) after columns

Example:

7. The index columns are prefixed withIS NULL maybeIS NOT NULL

Example:

8. The query condition uses theNOT IN maybeNOT EXISTS

Example:

9. The amount of data in the table is small

10. MySQL Optimizer Chooses Not to Use Indexes

Summary:

VII. How do you understand MySQL filesort?

When will it be triggered?filesort？

filesort The way it works:

filesort performance impact:

How to avoid or optimizefilesort？

Summary:

VIII. Do you know what situations lock the table?

I. Common locking table situations

1. DDL operations (Data Definition Language)

2. full table scanUPDATE maybeDELETE manipulate

3. Write operations in transactions (INSERT、UPDATE、DELETE）

4. LOCK TABLES explicit locking

5. Bulk insertion of data

6. Foreign Key Constraint Checking

7. Read and Write Operations in MyISAM Storage Engine

Second, how to avoid locking the table?

1. Using the right storage engine

2. Creating the right index

3. Reduction of long services

4. utilizationOPTIMIZE cap (a poem)ANALYZE prudent

5. batch operation

6. Avoiding Explicit Table Locks

IX. How do you understand the deadlock mechanism in MySQL?

Common understanding:

Deadlock generation process:

Deadlock Mechanisms in MySQL:

Deadlock handling:

How to avoid deadlocks?

1. Sequence of fixed locks

2. Reduced locking range

3. Using the right index

4. Reduced concurrency

5. Setting Transaction Isolation Levels Properly

6. Reduce lockup time by batch operation

How to detect deadlocks?

X. How are you optimizing for slow queries?

1. utilization`LIKE` when preceded by a wildcard character

4. utilization`OR` Keywords.

5. Inequality conditions (`!=` maybe`<>`)

6. Scope Query (`>`, `<`, `BETWEEN`) after columns

7. The index columns are prefixed with`IS NULL` maybe`IS NOT NULL`

8. The query condition uses the`NOT IN` maybe`NOT EXISTS`

When will it be triggered?`filesort`？

`filesort` The way it works:

How to avoid or optimize`filesort`？

2. full table scan`UPDATE` maybe`DELETE` manipulate

3. Write operations in transactions (`INSERT`、`UPDATE`、`DELETE`）

4. `LOCK TABLES` explicit locking

4. utilization`OPTIMIZE` cap (a poem)`ANALYZE` prudent

2. utilization`EXPLAIN` Analyzing Query Execution Plans

5. make superior`JOIN` manipulate