Complete MySQL Learning Notes

MyISAM and InnoDB
Reasons for performance degradation and slow SQL:
Mysql execution order
SQLJoin
index

Pros and cons of indexing

1. Advantages
2. Disadvantages

Index Classification

create
delete
Check

mysql index structure

In which cases should we create indexes?

When not to create an index

Performance Analysis

What can you do?
Three situations of id
select_type
type::

Extra

Index optimization

Single table optimization
Dual table optimization
Three table optimization

Index Tips

Index Case

Order By Index Optimization

exsites

Mysql slow query log command

Function operation batch insert data

show Profile analysis sql

Global query log

Mysql lock

Lock Command

Table lock: read lock

Table lock: write lock

Row Lock

Transactions

Row lock test

Gap Lock

Row Lock Summary

MyISAM and InnoDB

contrast	MyISAM	InnoDB
Primary and foreign keys	Not supported	support
Transactions	Not supported	support
Row table lock	Table lock: Even if you operate on one record, the entire table will be locked, which is not suitable for high-concurrency operations.	Row lock, which only locks a row during operation without affecting other rows, is suitable for high concurrency
cache	Only cache indexes, not other data	Cache indexes and real data require high memory, and memory size affects performance
Tablespace	Small	big
Focus	performance	Transactions
Default installation	Y	Y

Reasons for performance degradation and slow SQL:

Poorly written query
Index failure
Too many joins in an associated query (design flaw or unavoidable requirement)
Server tuning and various parameter settings (buffer, thread parameters)

Mysql execution order

handwriting
Machine reading starts with from

SQLJoin

a table

 mysql> select * from tbl_dept;
 +----+----------+--------+
 | id | deptName | locAdd |          
 +----+----------+--------+
 | 1 | RD | 11 |
 | 2 | HR | 12 |
 | 3 | MK | 13 |
 | 4 | MIS | 14 |
 | 5 | FD | 15 |
 +----+----------+--------+
 5 rows in set (0.00 sec)

b table

 +----+------+--------+
 | id | name | deptId |
 +----+------+--------+
 | 1 | z3 | 1 |
 | 2 | z4 | 1 |
 | 3 | z5 | 1 |
 | 4 | w5 | 2 |
 | 5 | w6 | 2 |
 | 6 | s7 | 3 |
 | 7 | s8 | 4 |
 | 8 | s9 | 51 |
 +----+------+--------+
8 rows in set (0.00 sec)

mysql不支持全連接

使用以下方式可以實現全連接

 mysql> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId
     -> union
     -> select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;
 +------+----------+--------+------+------+--------+
 | id | deptName | locAdd | id | name | deptId |
 +------+----------+--------+------+------+--------+
 | 1 | RD | 11 | 1 | z3 | 1 |
 | 1 | RD | 11 | 2 | z4 | 1 |
 | 1 | RD | 11 | 3 | z5 | 1 |
 | 2 | HR | 12 | 4 | w5 | 2 |
 | 2 | HR | 12 | 5 | w6 | 2 |
 | 3 | MK | 13 | 6 | s7 | 3 |
 | 4 | MIS | 14 | 7 | s8 | 4 |
 | NULL | NULL | NULL | 8 | s9 | 51 |
 | 5 | FD | 15 | NULL | NULL | NULL |
 +------+----------+--------+------+------+--------+
 9 rows in set (0.00 sec)

The uniqueness of a and the uniqueness of b

 mysql> select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.id is null
     -> union
     -> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;
 +------+----------+--------+------+------+--------+
 | id | deptName | locAdd | id | name | deptId |
 +------+----------+--------+------+------+--------+
 | 5 | FD | 15 | NULL | NULL | NULL |
 | NULL | NULL | NULL | 8 | s9 | 51 |
 +------+----------+--------+------+------+--------+
 2 rows in set (0.01 sec)

index

Definition of index:

An index is a data structure that helps SQL efficiently retrieve data. The essence of an index is:數據結構

It can be simply understood as:排好序的快速查找數據結構

In addition to the data, the database system also maintains data structures that satisfy specific search algorithms. These data structures point to the data in some way (reference), so that advanced search algorithms can be implemented on these data structures. This data structure is an index. The following figure is an example:

Generally speaking, indexes are also very large, so they are often stored on disk as index files.

When we talk about indexes, unless otherwise specified, we usually refer to indexes organized in a B-tree (multi-way search tree, not necessarily binary) structure.

Among them, clustered index, secondary index, composite index, prefix index, and unique index all use B+ tree index by default, collectively referred to as index. Of course, in addition to B+ tree indexes, there are also hash indexes.

Pros and cons of indexing

1. Advantages

Similar to the indexing of book numbers in university libraries, it improves the efficiency of data retrieval and reduces the IO cost of the database.

Sorting data by index reduces the cost of data sorting and reduces CPU consumption

2. Disadvantages

In fact, the index is also a table that stores the primary key and the fields with indexes, and points to the records of the entity table, so the index column also takes up space.

Although indexes greatly improve query speed, they slow down table updates, such as update, insert, and delete operations. This is because when updating a table, MySQL needs to save not only the data but also the index file. Each time you update a field with an index, it will adjust the index information after the key value changes caused by the update.

Index is only one factor to improve efficiency. For a table with a large amount of data, you need to create the best index or write excellent query statements, rather than just adding indexes to improve efficiency.

Index Classification

Single value index
Unique Index
Composite Index
Basic syntax:

create

create [unique] index indexName on mytable(cloumnname(length));

alter mytable add [unique] index [indexName] on (columnname(length));

delete

drop index [indexName] on mytable

Check

show index from table_name\G

There are four ways to add indexes to a table:

mysql index structure

BTree Index
Hash Index
full-text index
R-Tree

In which cases should we create indexes?

The primary key automatically creates a unique index
Fields that are frequently used as query conditions should be indexed
Fields related to other tables in the query, foreign key relationships are indexed
Frequently updated fields are not suitable for creating indexes, because each update not only updates the record but also updates the index.
Do not create indexes for fields that are not used in the where condition
Single key/combination index selection problem who? (Combined indexes are recommended under high concurrency)
The sorting fields in the query will greatly improve the sorting speed if the sorting fields are accessed through indexes
Count or group fields in a query

When not to create an index

Few records in the table
Tables that frequently operate DML statements
The data in the table fields is repeated and evenly distributed, so only the most frequently queried and sorted data columns are indexed. Note that if a data column contains a lot of repeated content, indexing it will not have much practical effect.

Performance Analysis

Explian key points

What can you do?

Table reading order
Operation type of data read operation
Which indexes can be used
Which indexes are actually used?
References between tables
How many rows of each table are queried by the optimizer

Three situations of id

The id is the same, and the execution order is from top to bottom
The id is different. If it is a subquery, the id number increases, and the larger the id, the higher the priority.
The ids are the same but different and exist at the same time

select_type

SIMPLE Simple query
PRIMARY Main query (outermost query)
SUBQUERY
DERIUED A temporary table of a subquery of a query
UNION
UNION RESULT Union query results

type::

Type shows the access type arrangement, which is a more important indicator

From best to worst they are:

system > const > eq_ref> ref > range > index > ALL ;

Generally speaking, you need to ensure that the query reaches at least the range level, preferably ref

----------------------------------------------type------------------------------------------------------

system: The table has only one row of records (equal to the system table). This is a special column of const type and generally does not appear and can be ignored.
const: indicates that the search is done once through the index. const is used to compare primary keys or unique indexes. Because only one row of data is matched, MySQL can quickly convert the query into a constant by placing the primary key in the where list.
eq_ref: unique index scan, there is only one record matching it in the table, often used for primary key or unique index scan (two tables are many-to-one or one-to-one relationship, when the connected table is one, its query is eq_ref)
ref: non-unique index scan, returns all rows matching a single value, which is essentially an index access, it returns all rows matching a single value, but it may find multiple rows with compound conditions, which is a combination of search and scan
Range: retrieves only rows in a given range, using an index to select rows. The key column shows which index is used. Generally, queries such as between, <, >, and in appear in the where statement. This range scan index is better than a full table scan.
index: The difference between index and ALL is that index only traverses the index tree, and the index file is usually smaller than the data file.
ALL: Full table scan

----------------------------------------------type------------------------------------------------------

possible_keys: Displays the possible indexes that can be applied (theoretically)
key: the index actually used. If a covering index is used in the query, the index only appears in the key
key_len: indicates the number of bytes used in the index. The shorter the better without losing precision. The value displayed by kenlen is the maximum possible length of the index field, not the actual length used. kenlen is calculated based on the table definition, not retrieved from the table.

key_len length: 13 because char(4)*utf8(3)+allowed to be null(1)=13

ref: Shows which column of the index is used, a constant if possible, and which columns or constants are used to look up values on the index column

rows: Based on the table statistics and index selection, roughly calculate the number of rows that need to be read to find the required records

When no index is created, query the t1 t2 table. The id of the t1 table corresponding to the t2 table. The value of col1 in the t2 table must be 'ac'

For the Id field, table t1 is equivalent to one-to-many for table t2.

The type of the t1 table is eq_ref, which means unique index scan. There is only one record matching it in the table. There is only one col value corresponding to the id of the t1 table in the t2 table. According to the primary key id index query of the t2 table, one row is read from the t1 table and 640 rows are read from the t2 table.

After indexing

t1 reads one row, t2 reads 142 rows, ref non-unique index scan, returns all rows matching a single value, returns all rows of col corresponding to id in t2, but t1 has only one row of col corresponding to id, so type is eq_ref

Extra

Contains important information that does not fit in other columns

\G : Display sorting vertically

Using filesort: This means that MySQL will use an external index to sort the data instead of reading it in the order of the index in the table. The operation that cannot use the index to complete the sorting in MySQL is called file sorting. The figure not framed by the box has established a composite index, but directly using col3 to sort results in castles in the air. MySQL has no choice but to use filesoft

Using temporary: A temporary table is used to store intermediate results. MySQL uses a temporary table when sorting query results. Commonly used in order by sorting and group by grouping. The composite index col1_col2 is established in the above table, but grouping directly by col2 causes MySQL to have to perform filesoft and create a temporary table.
Using index indicates that a covering index is used in the corresponding select operation to avoid accessing the data rows of the table. If using where appears at the same time, it means that the index is used to perform the search of the index key value. If there is no using where, it means that the index is used to read data instead of performing a search action.
Using where means using where filtering
Using join buffer privately uses the link cache
The value of the impossible buffer where clause is always false and cannot be used to retrieve any tuples.
select tables optimized away In the absence of a group by clause, min/max operations are optimized based on indexes, or count(*) operations are performed on the MyISAM storage engine. The optimization is completed at the query execution plan generation stage without waiting for the execution operation to be performed.
distinct optimizes the distinct operation and stops searching for identical values immediately after finding the first matching tuple

Case

Index optimization

Single table optimization

 CREATE TABLE IF NOT EXISTS `article`(
 
 `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `author_id` INT (10) UNSIGNED NOT NULL,
 `category_id` INT(10) UNSIGNED NOT NULL , 
 `views` INT(10) UNSIGNED NOT NULL , 
 `comments` INT(10) UNSIGNED NOT NULL,
 `title` VARBINARY(255) NOT NULL,
 `content` TEXT NOT NULL
 );
 INSERT INTO `article`(`author_id`,`category_id`,`views`,`comments`,`title`,`content`)VALUES
 (1,1,1,1,'1','1'),
 (2,2,2,2,'2','2'),
 (1,1,3,3,'3','3');
 
 SELECT * FROM ARTICLE;
 mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
 +----+-----------+
 | id | author_id |
 +----+-----------+
 | 3 | 1 |
 +----+-----------+
 1 row in set (0.00 sec)
 
 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc li
 imit 1;
 +----+-------------+---------+------------+------+---------------+-----+---------+------+------+----------+-----------------------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+---------+------------+------+---------------+-----+---------+------+------+----------+-----------------------------+
 | 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
 +----+-------------+---------+------------+------+---------------+-----+---------+------+------+----------+-----------------------------+
 1 row in set, 1 warning (0.00 sec)

It can be seen that although the query is completed, the type is all, and using filesort appears in Extra, which proves that the query efficiency is very low.

Need to be optimized

Create an index

create index idx_article_ccv on article(category_id,comments,views);

Query

 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
 +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
 | 1 | SIMPLE | article | NULL | range | inx_article_ccv | inx_article_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |
 +----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
 1 row in set, 1 warning (0.00 sec)

Here we find that type has changed to range, and querying the entire table has changed to range query, which is a bit optimized.

However, extra still uses filesort, which proves that index optimization is not successful.

So we delete the index

drop index idx_article_ccv on article;

Create a new index and exclude range

create index idx_article_cv on article(category_id,views);
 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
 +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
 | 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | Using where |
 +----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
 1 row in set, 1 warning (0.00 sec)
At this time, you will find that the optimization is successful. Type has changed to ref and extra has changed to using where

In this experiment, I added another test and found that it is also feasible to put comments at the end when creating an index. mysql> create index idx_article_cvc on article(category_id,views,comments);
 Query OK, 0 rows affected (0.02 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 
 mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
 +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
 | 1 | SIMPLE | article | NULL | ref | idx_article_cvc | idx_article_cvc | 4 | const | 2 | 33.33 | Using where |
 +----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+
 1 row in set, 1 warning (0.00 sec)

At this time, you will find that the optimization is successful. Type has changed to ref and extra has changed to using where
In this experiment, I added another test and found that it is also feasible to put comments at the end when creating an index.

Here we find that type is still ref, extra is still usingwhere, but the index creation position is just changed, and the range query field is moved to the end!!!!

Dual table optimization

 CREATE TABLE IF NOT EXISTS `class`(
 `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `card` INT (10) UNSIGNED NOT NULL
 );
 CREATE TABLE IF NOT EXISTS `book`(
 `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `card` INT (10) UNSIGNED NOT NULL
 );
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
  
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
 
 mysql> create index Y on book(card);
  explain select * from book left join class on book.card=class.card;
 +----+-------------+-------+------------+-------+---------------+------+---------+------+------+------+----------+----------------------------------------------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+------+---------+------+------+------+----------+----------------------------------------------------+
 | 1 | SIMPLE | book | NULL | index | NULL | Y | 4 | NULL | 20 | 100.00 | Using index |
 | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
 +----+-------------+-------+------------+-------+---------------+------+---------+------+------+------+----------+----------------------------------------------------+
 2 rows in set, 1 warning (0.00 sec)

You will find that there is not much difference and it is still a full table query. This is because the two tables are queried left-joined. The left table must be fully queried. At this time, it is only useful to create an index on the right table.

Conversely, the right link must be indexed on the left table to be useful

Create an index on the right table

 create index Y on class;
 explain select * from book left join class on book.card=class.card;
 +----+-------------+-------+------------+-------+---------------+------+--------+----------------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+------+--------+----------------+------+----------+-------------+
 | 1 | SIMPLE | book | NULL | index | NULL | Y | 4 | NULL | 20 | 100.00 | Using index |
 | 1 | SIMPLE | class | NULL | ref | Y | Y | 4 | db01.book.card | 1 | 100.00 | Using index |
 +----+-------------+-------+------------+-------+---------------+------+--------+----------------+------+----------+-------------+
 2 rows in set, 1 warning (0.00 sec)

You will find that the right table is only queried once. . type is ref

Three table optimization

 CREATE TABLE IF NOT EXISTS `phone`(
 `phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 `card` INT (10) UNSIGNED NOT NULL
 )ENGINE = INNODB;
 
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
 INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

Delete all indexes first

 drop index Y on book;
 drop index Y on class;
 explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
 | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
 | 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
 | 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
 3 rows in set, 1 warning (0.00 sec)

Create an index

 create index y on book(card);
 
 create index z on phone(card);
 explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
 +----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
 | 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
 | 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | Using index |
 | 1 | SIMPLE | phone | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | Using index |
 +----+-------------+-------+------------+------+---------------+------+---------+----------------+------+----------+-------------+
 3 rows in set, 1 warning (0.00 sec)

You will find that the index was created very successfully. . However, left join 最左表必須全部查詢and indexed.

 create index x on class(card);
 explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
 +----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+
 | 1 | SIMPLE | class | NULL | index | NULL | x | 4 | NULL | 20 | 100.00 | Using index |
 | 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | Using index |
 | 1 | SIMPLE | phone | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | Using index |
 +----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+
 3 rows in set, 1 warning (0.00 sec)

The result is still the same

Create table

 CREATE TABLE staffs(
 id INT PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'Name',
 `age` INT NOT NULL DEFAULT 0 COMMENT'Age',
 `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'Position',
 `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'Job entry time'
 )CHARSET utf8 COMMENT'employee record table';
 INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
 INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
 INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
Create an index ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

Index Tips

1. The leading index cannot die, and the middle index cannot be broken: When creating a composite index, you must include the leading index. You cannot skip the middle index and use the following indexes directly. To use the following indexes, you must add the middle index (you can use the following index first and then the middle index, but you cannot directly use the following index and skip the middle index) (for where)

It can be seen from the above figure that the index cannot be used if the name is skipped

 mysql> explain select * from staffs where name='july';
 +----+-------------+--------+------------+------+-------------------------+-------------------------+----------+-------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+--------+------------+------+-------------------------+-------------------------+----------+-------+------+------+------+
 | 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
 +----+-------------+--------+------------+------+-------------------------+-------------------------+----------+-------+------+------+------+
 1 row in set, 1 warning (0.00 sec)
 
 mysql> explain select * from staffs where name='july' and pos='dev';
 +----+-------------+--------+------------+------+-------------------------+-------------------------+----------+-------+------+----------+-----------------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+--------+------------+------+-------------------------+-------------------------+----------+-------+------+----------+-----------------------+
 | 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition |
 +----+-------------+--------+------------+------+-------------------------+-------------------------+----------+-------+------+----------+-----------------------+
 1 row in set, 1 warning (0.00 sec)

It can be seen from the statement that key_len remains unchanged after skipping the middle index, proving that the index pos is not used.

2. No operations (calculations, type conversions, etc.) can be performed on index columns
3. The storage engine cannot use the columns to the right of the range condition in the index (less calculation on the index column)
4. Try to use covering indexes, that is, queries that only access the index to reduce the use of select *
5. Use less (!=, <>, <, >) is not null, is null;
6. Like starts with '%' which will cause the index to fail (use covering index to avoid index failure) Covering index: (the order of the index created should be consistent with the order of the fields queried)
7. If the string is not enclosed in single quotes, the index will be invalid (MySQL will force the string type to be converted, causing the index to be invalid)
8. Use or less, as it will cause the connection to fail

Index Case

Assume index (a, b, c)

Y means all indexes are used, N means none of them are used

where statement	Is the index used?
where a=3 and c=5	(b is broken in the middle) a is used but c is not used
where a=3 and b=4 and c=5	Y
where a=3 and c=5 and b=4	Here MySQL automatically optimizes the statement sorting
where a=3 and b>4 and c=5	a,b is used
where a=3 and b like 'k%' and c=5	Y like all constants start with the index
where b=3 and c=4	N
where a=3 and c>5 and b=4	Y: MySQL automatically optimizes the statement sort range c before the index becomes invalid
where b=3 and c=4 and a=2	Y: MySQL automatically optimizes the order of statements
where c=5 and b=4 and a=3	Y: MySQL automatically optimizes the order of statements

Assume index (a, b, c, d)

 create table test03(
 id int primary key not null auto_increment,
 a int(10),
 b int(10),
 c int(10),
 d int(10),
 
 insert into test03(a,b,c,d) values (3,4,5,6);
 insert into test03(a,b,c,d) values (3,4,5,6);
 insert into test03(a,b,c,d) values (3,4,5,6);
 insert into test03(a,b,c,d) values (3,4,5,6);
 
 create index idx_test03_abcd on test03(a,b,c,d);

###

where a=3 and b>4 and c=5	When a and b are used, all indexes after b become invalid.
where a=3 and b=4 and d=6 order by c	a and b are used. c is actually used, but it is used for sorting and is not counted in MySQL.
where a=3 and b=4 order by c	a and b are used. c is actually used, but it is used for sorting and is not counted in MySQL.
where a=3 and b=4 order by d	Using a and b, skipping c here will result in using filesort
where a=3 and d=6 order by b ,c	A is used, and the sorting uses indexes b and c.
where a=3 and d=6 order by c ,b	Using a will generate using filesort because b is skipped to sort c.
where a=3 and b=4 order by b ,c	Y Use all
where a=3 and b=4 and d&##61;6 ordered by c , b	Using a and b will not generate using filesort because b is queried before sorting c and b. When querying, b is already determined (constant). In this way, c is not sorted by skipping b, but directly sorting c, which is equivalent to the query statement in the third cell.

Group by is more serious. Group by groups first and then sorts. Replacing order by with group by may even cause using temporary, which is similar to order by, but more serious. The effect is similar to group by, so I will not demonstrate it.

Order By Index Optimization

orderBy Condition	Extra
where a>4 order by a	using where using index
where a>4 order by a,b	using where using index
where a>4 order by b	using where, using index, using filesort (the leader behind the order by is not there)
where a>4 order by b,a	using where, using index, using filesort (order by followed by order)
where a=const order by b,c	If the leftmost prefix of the index used in where is defined as a constant, then order by can use the index
where a=const and b=const order by c	Where the leftmost prefix of the index is defined as a constant, then order by can use the index
where a=const and b>3 order by bc	using where using index
order by a asc, b desc ,c desc	Sorting inconsistent lifts

exsites

 select a.* from A a where exists(select 1 from B b where a.id=b.id)
 The above query uses the exists statement. exists() will be executed A.length times. It does not cache the exists() result set because the content of the exists() result set is not important. What is important is whether there are records in the result set. If there are records, true is returned, otherwise false is returned. Its query process is similar to the following process
 List resultSet=[]; Array A=(select * from A)
 for(int i=0;i<A.length;i++) { if(exists(A[i].id) { //Execute select 1 from B b where b.id=a.id to see if there is a record. return resultSet.add(A[i]); } } return resultSet;
 
 When the data in table B is larger than that in table A, it is suitable to use exists(), because it does not require so many traversal operations, and only needs to execute the query again. For example: table A has 10,000 records and table B has 1,000,000 records, then exists() will be executed 10,000 times to determine whether the id in table A is equal to the id in table B. For example: table A has 10,000 records and table B has 100,000,000 records, then exists() will still be executed 10,000 times, because it only executes A.length times. It can be seen that the more data in table B, the more suitable exists() is for it to work. For another example: table A has 10,000 records and table B has 100 records, then exists() will still be executed 10,000 times, it is better to use in() to traverse 10,000*100 times, because in() traverses and compares in memory, while exists() needs to query the database. We all know that querying the database consumes higher performance, and memory comparison is very fast.

Mysql slow query log command

show VARIABLES like '%slow_query_log%';

Displays whether to enable MySQL slow query log

set global slow_query_log=0;

Turn off MySQL slow query log

set global slow_query_log=1;

Enable MySQL slow query log

show VARIABLES like '%long_query_time%';

Displays how long a slow query takes

set global long_quert_time=10;

Change the slow query time to 10 seconds. If the query statement time exceeds 10 seconds, it is a slow query.

show global status like '%Slow_queries%';

Displays a total of several slow query statements

[root@iZ0jlh1zn42cgftmrf6p6sZ data]# cat mysql-slow.log

Linux query slow sql

Function operation batch insert data

 CREATE TABLE dept(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    dname VARCHAR(20) NOT NULL DEFAULT '',
    loc VARCHAR(13) NOT NULL DEFAULT ''
 )ENGINE=INNODB DEFAULT CHARSET=GBK;
 CREATE TABLE emp(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #number enname VARCHAR(20) NOT NULL DEFAULT '', #name job VARCHAR(9) NOT NULL DEFAULT '', #job mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #superior number hiredate DATE NOT NULL, #time of employment sal DECIMAL(7,2) NOT NULL, #salary comm DECIMAL(7,2) NOT NULL, #bonus deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 #department number)ENGINE=INNODB DEFAULT CHARSET=GBK;

 show variables like 'log_bin_trust_function_creators';
 set global log_bin_trust_function_creators=1;

創建函數：隨機產生部門編號隨機產生字符串

DELIMITER $$ is because sql all ends with ; but the function creation process needs to be used multiple times; so the condition for ending sql execution is changed to input $$, which is equivalent to replacing the semicolon ' ;'

//Define function 1

 DELIMITER $$
 CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
 BEGIN
    DECLARE chars_set VARCHAR(100) DEFAULT 'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
       SET return_str = CONCAT(return_str,SUBSTRING(chars_set,FLOOR(1 + RAND()*52),1));
       SET i = i + 1;
    END WHILE;
    RETURN return_str;
 END $$
//Define function 2

 DELIMITER $$
 CREATE FUNCTION rand_num() RETURNS INT(5)
 BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100 + RAND()*10);
    RETURN i;
 END $$
//Define stored procedure 1

 DELIMITER $$
 CREATE PROCEDURE insert_emp(IN start INT(10), IN max_num INT(10))
 BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno, enname, job, mgr, hiredate, sal, comm, deptno) VALUES((start + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
    UNTIL i = max_num  
    END REPEAT;
    COMMIT;
 END $$
//Define stored procedure 2

 DELIMITER $$
 CREATE PROCEDURE insert_dept(IN start INT(10), IN max_num INT(10))
 BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO dept(deptno,dname,loc) VALUES((start + i),rand_string(10),rand_string(8));
    UNTIL i = max_num  
    END REPEAT;
    COMMIT;
 END $$
//Start inserting data DELIMITER;
 call insert_dept(100,10);
 call insert_emp(100001,500000);

show Profile analysis sql

 mysql> show variables like 'profiling';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | profiling | OFF |
 +---------------+-------+
 1 row in set (0.00 sec)
 
 mysql> set profiling=on;
 Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 mysql> show variables like 'profiling';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | profiling | ON |
 +---------------+-------+
 1 row in set (0.01 sec)

Write a few insert statements at random'

Displays the speed of query operation statements

 mysql> show profiles;
 +----------+------------+----------------------------------------------------------------+
 | Query_ID | Duration | Query |
 +----------+------------+----------------------------------------------------------------+
 | 1 | 0.00125325 | show variables like 'profiling' |
 | 2 | 0.00018850 | select * from dept |
 | 3 | 0.00016825 | select * from tb1_emp e inner join tbl_dept d on e.deptId=d.id |
 | 4 | 0.00023900 | show tables |
 | 5 | 0.00031125 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id |
 | 6 | 0.00024775 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id |
 | 7 | 0.00023725 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id |
 | 8 | 0.00023825 | select * from tbl_emp e left join tbl_dept d on e.deptId=d.id |
 | 9 | 0.35058075 | select * from emp group by id%10 limit 15000 |
 | 10 | 0.35542250 | select * from emp group by id%10 limit 15000 |
 | 11 | 0.00024550 | select * from tbl_emp e left join tbl_dept d on e.deptId=d.id |
 | 12 | 0.36441850 | select * from emp group by id%20 order by 5 |
 +----------+------------+----------------------------------------------------------------+
 12 rows in set, 1 warning (0.00 sec)

Display query process sql life cycle

 mysql> show profile cpu,block io for query 3;
 +----------------------+----------+----------+------------+--------------+---------------+
 | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
 +----------------------+----------+----------+------------+--------------+---------------+
 | starting | 0.000062 | 0.000040 | 0.000021 | 0 | 0 |
 | checking permissions | 0.000004 | 0.000003 | 0.000001 | 0 | 0 |
 | checking permissions | 0.000015 | 0.000006 | 0.000003 | 0 | 0 |
 | Opening tables | 0.000059 | 0.000039 | 0.000020 | 0 | 0 |
 | query end | 0.000004 | 0.000002 | 0.000001 | 0 | 0 |
 | closing tables | 0.000002 | 0.000001 | 0.000000 | 0 | 0 |
 | freeing items | 0.000014 | 0.000010 | 0.000005 | 0 | 0 |
 | cleaning up | 0.000009 | 0.000006 | 0.000003 | 0 | 0 |
 +----------------------+----------+----------+------------+--------------+---------------+
 8 rows in set, 1 warning (0.00 sec)
 
 mysql> show profile cpu,block io for query 12;
 +----------------------+----------+----------+------------+--------------+---------------+
 | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
 +----------------------+----------+----------+------------+--------------+---------------+
 | starting | 0.000063 | 0.000042 | 0.000021 | 0 | 0 |
 | checking permissions | 0.000006 | 0.000003 | 0.000002 | 0 | 0 |
 | Opening tables | 0.000013 | 0.000009 | 0.000004 | 0 | 0 |
 | init | 0.000028 | 0.000017 | 0.000008 | 0 | 0 |
 | System lock | 0.000007 | 0.000004 | 0.000002 | 0 | 0 |
 | optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |
 | statistics | 0.000014 | 0.000010 | 0.000004 | 0 | 0 |
 | preparing | 0.000008 | 0.000005 | 0.000003 | 0 | 0 |
 | Creating tmp table | 0.000028 | 0.000018 | 0.000009 | 0 | 0 |
 | Sorting result | 0.000003 | 0.000002 | 0.000001 | 0 | 0 |
 | executing | 0.000002 | 0.000002 | 0.000001 | 0 | 0 |
 | Sending data | 0.364132 | 0.360529 | 0.002426 | 0 | 0 |
 | Creating sort index | 0.000053 | 0.000034 | 0.000017 | 0 | 0 |
 | end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |
 | query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 |
 | removing tmp table | 0.000005 | 0.000003 | 0.000002 | 0 | 0 |
 | query end | 0.000003 | 0.000002 | 0.000001 | 0 | 0 |
 | closing tables | 0.000006 | 0.000004 | 0.000002 | 0 | 0 |
 | freeing items | 0.000023 | 0.000016 | 0.000007 | 0 | 0 |
 | cleaning up | 0.000012 | 0.000007 | 0.000004 | 0 | 0 |
 +----------------------+----------+----------+------------+--------------+---------------+
 20 rows in set, 1 warning (0.00 sec)

If any of the above four occurs, the query statement needs to be optimized

Global query log

  set global general_log=1;
 set global log_output='TABLE';

After that, the SQL statements you write will be recorded in the general_log table in the MySQL database, and can be viewed with the following command

 select * from mysql.general_log;
 mysql> select * from mysql.general_log;
 +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
 | event_time | user_host | thread_id | server_id | command_type | argument |
 +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
 | 2021-12-06 11:53:53.457242 | root[root]@localhost [] | 68 | 1 | Query | select * from mysql.general_log |
 +----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
 1 row in set (0.00 sec)

Mysql lock

Read lock (shared lock): multiple read operations can be performed simultaneously on the same data without affecting each other.
Write lock (exclusive lock): When the current write operation is not completed, it will block other write locks and read locks
Row lock: prefers InnoDB engine, with high overhead, slow locking, and deadlock: has the smallest locking granularity, the lowest probability of lock conflict, and high concurrency
Table lock: prefers the MyISAM engine, with low overhead and fast locking; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency

Test the table lock below

 use big_data;
 
 create table mylock (
 id int not null primary key auto_increment,
 name varchar(20) default ''
 ) engine myisam;
 
 insert into mylock(name) values('a');
 insert into mylock(name) values('b');
 insert into mylock(name) values('c');
 insert into mylock(name) values('d');
 insert into mylock(name) values('e');
 
 select * from mylock;

Lock Command

 lock table mylock read,book write;## read lock mylock write lock book
 show open tables; ##Show which tables are locked unlock tables;##Cancel the lock

Table lock: read lock

 ##After adding a read lock, it cannot be modifiedmysql> lock table mylock read;##1
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select * from mylock;##1
 +----+------+
 | id | name |
 +----+------+
 | 1 | a |
 | 2 | b |
 | 3 | c |
 | 4 | d |
 | 5 | e |
 +----+------+
 5 rows in set (0.00 sec)
 
 mysql> update mylock set name='a2' where id=1; ##1
 ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
 ##Cannot change the table currently locked by read lock##Cannot read other tablesmysql> select * from book;##1
 ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

To distinguish the two commands, treat 1 as the operation on the original MySQL command terminal and 2 as the newly created MySQL terminal.

Create a new mysql terminal command operation

 ##Create a new mysql terminal command operationmysql> update mylock set name='a3' where id=1; ##2

Blocking operations are found

Cancel the lock on the original mysql command terminal

 unlock tables;##1
 Query OK, 1 row affected (2 min 1.46 sec) ##2
 Rows matched: 1 Changed: 1 Warnings: 0 ##2

You will find that it has been blocked for more than two minutes.

Summary: After reading and locking the table mylock: 1. Query operation: The current client (terminal command operation 1) can query the table mylock

Other clients (terminal command operation 2) can also query the table mylock 2. DML operations (add, delete, and modify) on the current client will fail and report an error ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated Other clients performing DML operations will cause MySQL to be blocked until the current session releases the lock

Table lock: write lock

 mysql> lock table mylock write;
 Query OK, 0 rows affected (0.00 sec)
Add a write lock to the current session mylock table mysql> update mylock set name='a4'where id=1;
 Query OK, 1 row affected (0.00 sec)
 Rows matched: 1 Changed: 1 Warnings: 0
 
 mysql> select * from mylock;
 +----+------+
 | id | name |
 +----+------+
 | 1 | a4 |
 | 2 | b |
 | 3 | c |
 | 4 | d |
 | 5 | e |
 +----+------+
 mysql> select * from book;
 ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES

You will find that you cannot operate other tables but you can operate the locked table.

Open a new client to test the locked table

 mysql> select * from mylock;
 
 5 rows in set (2 min 30.92 sec)

It is found that the table locked by the write lock will be blocked when the new client operates (add, delete, modify, and query)

Analysis table lock

 mysql> show status like 'table%';
 +----------------------------+-------+
 | Variable_name | Value |
 +----------------------------+-------+
 | Table_locks_immediate | 194 |
 | Table_locks_waited | 0 |
 | Table_open_cache_hits | 18 |
 | Table_open_cache_misses | 2 |
 | Table_open_cache_overflows | 0 |
 +----------------------------+-------+
 5 rows in set (0.00 sec)

Row Lock

InnoDB row lock mode

InnoDB implements the following two types of row locks.

Shared lock (S): also known as read lock, or S lock for short. A shared lock means that multiple transactions can share a lock for the same data and can access the data, but can only read and not modify it.
Exclusive lock (X): also known as write lock, or X lock for short. An exclusive lock cannot coexist with other locks. For example, if a transaction acquires an exclusive lock for a data row, other transactions cannot acquire other locks for the row, including shared locks and exclusive locks. However, the transaction that acquires the exclusive lock can read and modify the data.

For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved;

For normal SELECT statements, InnoDB does not add any locks;

You can use the following statement to explicitly add a shared lock or an exclusive lock to the record set.

 Shared lock(s): SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
 
 Exclusive lock (X): SELECT * FROM table_name WHERE ... FOR UPDATE

Since row locks support transactions, let's review it here

Transactions

A transaction is a logical processing unit consisting of a group of SQL statements. A transaction has four properties: ACID

Atomicity: A transaction is an atomic operation unit, and its operations on data are either all executed or not executed at all.
Consistency: Data must remain in a consistent state when a transaction starts and completes. This means that all relevant data must be applied to the transaction's modifications to maintain data integrity; at the end of the transaction, all internal data structures (such as B-tree indexes or doubly linked lists) must also be correct.
Isolation: The database provides a certain isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that the intermediate states of the transaction process are not visible to the outside world, and vice versa.
Durable: After the transaction is completed, its operation on the data is permanent and can be maintained even if a system failure occurs.

Problems caused by concurrent transactions:

Lost updates, dirty reads, non-repeatable reads, phantom reads

ACID Properties	meaning
Atomicity	A transaction is an atomic operation unit, and its modifications to data are either all successful or all failed.
Consistent	Data must be in a consistent state both when a transaction begins and when it completes.
Isolation	The database system provides a certain isolation mechanism to ensure that transactions run in an "independent" environment that is not affected by external concurrent operations.
Durable	After the transaction is completed, the changes to the data are permanent.

Problems with concurrent transaction processing

question	meaning
Lost Update	When two or more transactions select the same row, the value modified by the initial transaction will be overwritten by the value modified by the subsequent transaction.
Dirty Reads	When a transaction is accessing data and modifying the data, but the modification has not yet been committed to the database, another transaction also accesses the data and uses it.
Non-Repeatable Reads	At some point after a transaction reads certain data, it reads the previously read data again and finds that the data is inconsistent with the previously read data.
Phantom Reads	A transaction re-reads previously queried data according to the same query conditions, but finds that other transactions have inserted new data that meets its query conditions.

Transaction Isolation Level

In order to solve the transaction concurrency problem mentioned above, the database provides a certain transaction isolation mechanism to solve this problem. The stricter the transaction isolation of the database, the smaller the concurrency side effects, but the greater the price paid, because transaction isolation is essentially the use of transactions to a certain extent "serialize", which is obviously contradictory to "concurrency".

There are four isolation levels for the database, from low to high: Read uncommitted, Read committed, Repeatable read, and Serializable. These four levels can solve problems such as dirty writes, dirty reads, non-repeatable reads, and phantom reads one by one.

Isolation Level	Lost Updates	Dirty Read	Non-repeatable read	Phantom Read
Read uncommitted	×	√	√	√
Read committed	×	×	√	√
Repeatable read (default)	×	×	×	√
Serializable	×	×	×	×

Note: √ 代表可能出現， × 代表不會出現.

The default isolation level of the MySQL database is Repeatable read. Check it:

 show variables like 'tx_isolation';

Row lock test table creation, case preparation

 create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1)
 )engine = innodb default charset = utf8;
 
 insert into test_innodb_lock values(1,'100','1');
 insert into test_innodb_lock values(3,'3','1');
 insert into test_innodb_lock values(4,'400','0');
 insert into test_innodb_lock values(5,'500','1');
 insert into test_innodb_lock values(6,'600','0');
 insert into test_innodb_lock values(7,'700','0');
 insert into test_innodb_lock values(8,'800','1');
 insert into test_innodb_lock values(9,'900','1');
 insert into test_innodb_lock values(1,'200','0');
 
 create index idx_test_innodb_lock_id on test_innodb_lock(id);
 create index idx_test_innodb_lock_name on test_innodb_lock(name);

Row lock test

Or open two terminals for testing and turn off automatic transaction submission, because automatic transaction submission will automatically lock and release the lock;

 mysql> set autocommit=0;

 mysql> set autocommit=0;

You will find that the query has no effect

Update the left side

 mysql> update test_innodb_lock set name='100' where id=3;
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 1 Changed: 0 Warnings: 0

Update the left side

Stop the operation after updating the right side

 mysql> update test_innodb_lock set name='340' where id=3;
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You will find that it is blocked until the lock is released or the transaction is committed.

對于innodb引擎來說，對某一行數據進行DML（增刪改）操作會對操作的那行添加排它鎖

Other transactions cannot execute this statement, but can operate on data in other rows.

無索引行鎖會升級成表鎖：如果不通過索引條件檢索數據，那么innodb會對表中所有記錄加鎖，實際效果和表鎖一樣

Remember to use indexes when performing operations: when the innodb engine index fails, the row lock will be upgraded to a table lock

 mysql> update test_innodb_lock set sex='2' where name=400;
 Query OK, 0 rows affected (0.00 sec)
 Rows matched: 2 Changed: 0 Warnings: 0

Note that the index will be invalid if the name is not enclosed in single quotes

 mysql> update test_innodb_lock set sex='3' where id=3;
 Query OK, 1 row affected (23.20 sec)
 Rows matched: 1 Changed: 1 Warnings: 0

It was found that operations on other rows were also blocked. This was because the row lock was upgraded to a table lock due to the lack of indexes.

Originally, only one row of data was locked, but because I forgot to add single quotes to the name field, the index became invalid and the entire table was locked;

Gap Lock

When we use a range condition instead of a wait condition to retrieve data and request a shared or exclusive lock, there is a non-existent record in that range condition, called a gap. InnoDB will also lock this gap. This locking mechanism is called a gap lock.

 mysql> select * from test_innodb_lock;
 +------+------+------+
 | id | name | sex |
 +------+------+------+
 | 1 | 100 | 2 |
 | 3 | 100 | 3 |
 | 4 | 400 | 0 |
 | 5 | 500 | 1 |
 | 6 | 600 | 0 |
 | 7 | 700 | 3 |
 | 8 | 800 | 1 |
 | 9 | 900 | 2 |
 | 1 | 200 | 0 |
 +------+------+------+
 There is no data with id 2

Check row lock requisition status

 mysql> show status like 'innodb_row_lock%';
 +-------------------------------+--------+
 | Variable_name | Value |
 +-------------------------------+--------+
 | Innodb_row_lock_current_waits | 0 |
 | Innodb_row_lock_time | 284387 |
 | Innodb_row_lock_time_avg | 21875 |
 | Innodb_row_lock_time_max | 51003 |
 | Innodb_row_lock_waits | 13 |
 +-------------------------------+--------+
 5 rows in set (0.00 sec)
 Innodb_row_lock_current_waits: The number of rows currently waiting for locks.
 Innodb_row_lock_time: The total length of time locked from system startup to now.
 Innodb_row_lock_time_avg: average time spent waiting each time
 Innodb_row_lock_time_max: The longest waiting time from system startup to now.
 Innodb_row_lock_waits: The total number of waits since the system was started

Row Lock Summary

The InnoDB storage engine implements row-level locking. Although the performance loss in the implementation of the locking mechanism may be higher than that of the table lock, its overall concurrent processing capability is far superior to that of the MyISAM table lock. When the system concurrency is high, InnoDB's overall performance will have a clear advantage over MyISAM.

However, InnoDB's row-level locks also have their fragile side. If we use them improperly, the overall performance of InnoDB may not only be lower than that of MyISAM, but may even be worse.

Optimization suggestions:

Whenever possible, all data retrieval should be done through indexes to prevent non-index row locks from being upgraded to table locks.
Design indexes reasonably to minimize the scope of locks
Minimize index conditions and index ranges to avoid gap locks
Try to control the transaction size and reduce the amount of locked resources and the length of time
Use low-level transaction isolation whenever possible (but the business needs to be met)

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in: