Basics

Writing order

Execution order except select is first, the rest of the order is the same Execution order: select then Having executes after (the execution order is also the order in which composite indexes are declared) ^ib46t0 The execution order is determined by the MySQL query optimizer based on the query plan, which may include choosing the best index, optimizing the join order, and other optimization strategies

1. SELECT Stage (can use aggregate functions)(can use aliases)
2. FROM Stage (JOIN ON)(executes first ON then JOIN)(can assign aliases)(Execution order first)
3. WHERE Stage (high efficiency)(cannot use aggregate functions)(cannot assign aliases, can use previously assigned aliases)(write what can be written in where instead of having)
4. GROUP BY Stage
5. HAVING Stage (low efficiency) can use grouped data (can use aggregate functions)(does not need to appear in pairs with where)
6. ORDER BY Stage ASC ascending/DESC descending The sort field is not necessarily select in (can assign aliases) (default ascending) (consumes resources, reduce usage)
7. LIMIT X,Y; X is the displacement offset, Y is the number of entries, query Y entries of data starting after the Xth entry of data

LIKE : fuzzy query, can be used in JOIN ON / WHERE / HAVING % : represents an uncertain number of characters (0, 1, or more)

• mysql built in core database, user account and permission information
• information_schema information about all other databases maintained (such as table indexes and constraints)
• performance_schema monitors various performance metrics of the MySQL service (such as lock information)
• sys built in database, through views it combines performance_schema and information_schema together

DESC [table/field]

import an existing table (`mysql` command line) : source d:\mysqldb.sql


- enter/switch database 
USE database_name;

- SHOW : usually used to display multiple variables or statuses, allowing all related information to be viewed at once.
- - usually does not distinguish between global and session, directly displays the variables in the current context.

SHOW DATABASES：display the list of all databases
SHOW TABLES：display all tables in the current database
SHOW TABLE STATUS LIKE '表'：view the status of the corresponding table
SHOW COLUMNS FROM table_name：display the column information of the specified table
SHOW CREATE TABLE table_name：display the statement that creates the specified table SQL statement
SHOW STATUS：display server status information.
SHOW PROFILES：display performance analysis information for recently executed queries. Can be used to compare the performance of multiple queries, suitable for in depth analysis.
SHOW ENGINES：display supported storage engines and their statuses.
SHOW VARIABLES：display system variables and their current values.
	view page size 
 	show variables like '%innodb_page_size%'
SHOW INDEX FROM table_name:display the indexes of the table


- @@ prefix : can query specific variables, making it convenient to obtain the value of a single variable.
- - can be used to query global variables and session variables, supports the use of `global` or `session` prefix.
@@ Prefix variables are MySQL internal state variables of the server. They are not stored by any specific database table, but are managed directly in memory by the server process. They represent MySQL server configuration, state and runtime parameters. Through SELECT @@variable_name you can directly access these values. These values are a reflection of the real time state of the server, not data statically stored in the database.

SELECT @@global.variable_name;：Query global variables.
SELECT @@session.variable_name;：Query the variables of the current session.
SELECT @@max_connections;Query the maximum number of connections.
SELECT @@connections;   Query the total number of connections
SELECT @@version;：Get the current MySQL version.
SELECT @@datadir;：Get the data directory path
SELECT @@innodb_buffer_pool_size;   Query InnoDB buffer pool size
SELECT @@thread_count;  Query the current number of threads
SELECT @@query_cache_size;  Query the size of the query cache
select @@optimizer_switch 

SELECT DATABASE();
SELECT USER();
SELECT UUID();  Return a unique identifier

SELECT * FROM INFORMATION_SCHEMA.table_constraints WHERE TABLE_NAME = '表名称'; Table constraints
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = '表名称'; Table indexes
SHOW TABLE STATUS LIKE 'countries';Table constraints, not so detailed


Turn it off when not in use, it consumes performance
![[assets/Mysql Interview/file-20240904192510469.png]]
- View the default row format

select @@innodb_default_row_format;
or
show table status like'表';

- Specify the row format
Create Table Table name ROW_FORMAT=Row format
Alter Table  Table name ROW_FORMAT=Row format

![[assets/Mysql Advanced/image-20231208101341686.png|500]]

SHOW PROFILES：Display performance analysis information for the most recently executed query. This includes the execution time, lock time and so on of the query. It provides more detailed performance data to help developers identify performance bottlenecks

> If a certain stage takes a long time, optimization can be considered
show profile for query 7;
show profile cpu,block io for query 6;

![[assets/Mysql Advanced/image-20231120172201970.png|600]]


SHOW STATUS：Display server status information

> Last lookup cost,Reflects the complexity of the query and the resource consumption
SHOW STATUS LIKE 'last_query_cost';


We can use

select * from performance_.schema.data_locks\G;
this statement to view what locks were added during transaction execution SQL What locks were added during the process.

- If LOCK_MODE is `X`，it indicates next-key Lock,
- If LOCK_MODE is `X, REC_NOT_GAP`，This indicates a record lock,
- If LOCK_MODE is `X, GAP`，This indicates a gap lock,

Create table
CREATE TABLE [IF NOT EXISTS] Table name(
Field 1, Data type [Constraint] [Default value],
Field 2, Data type [Constraint] [Default value],
Field 3, Data type [Constraint condition] [Default value],
[Table constraint condition]
[constraint Custom constraint name] unique key(Field name)
[CONSTRAINT <Foreign key constraint name>] FOREIGN KEY（A field of the child table) references Parent table name(Referenced field)
);

Drop table
DROP TABLE [IF EXISTS] Data table 1 [, Data table 2, …, Data table n];

Rename table
RENAME TABLE Table name TO New table name;

Copy table structure
CREATE TABLE New table name LIKE Old table name;

Copy table data
INSERT INTO New table name SELECT * FROM 旧表名;

Truncate table
TRUNCATE TABLE Table name;

Modify a field in the table

Add ALTER TABLE Table name ADD Field name Field type [FIRST|AFTER Field name] [Constraint name]

Modify(Rarely used, modifying the default value only affects future modifications to the table)
ALTER TABLE Table name MODIFY Field name Field type [DEFAULT Default value] [FIRST|AFTER Field name]
[Constraint condition];

ALTER TABLE Table name CHANGE Column name New column name New data type;

Delete ALTER TABLE Table name DROP Field

Drop constraint
ALTER TABLE Table name DROP PRIMARY KEY;
Drop unique constraint ALTER TABLE Table name DROP INDEX Constraint name;
Drop foreign key ALTER TABLE Table name DROP FOREIGN KEY Constraint name;


Index

On fields declared with a primary key constraint, unique constraint or foreign key constraint, the related index is automatically added

Create index

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

![[assets/Mysql Advanced/image-20231208205801757.png]]

Add index

()Must include

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]


In MySQL in, `CREATE INDEX` is mapped to a  `ALTER TABLE` statement


CREATE INDEX Index name ON Table name (Column name);
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

Delete
DROP INDEX Index name ON Table name;
ALTER TABLE table_name DROP INDEX index_name;


`AUTO_INCREMENT` The affected column must have either a unique constraint or a primary key constraint, so it cannot be deleted
If a column used as an index is deleted, that index is also deleted
![[assets/Mysql Advanced/image-20231208221009925.png]]
Mysql8.0 Supports creating descending indexes


Toggle index visibility state
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #Switch to hidden index
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #Switch to non hidden index

- Insert(Using a single `INSERT` statement to insert multiple rows of records is usually better in performance than using multiple single row `INSERT` statement)

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

Insert data from one table into another table.

INSERT INTO Target table name
(tar_column1 [， tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM Source table name
[WHERE condition]

update, must use where

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]
#Use WHERE clause specifies the data that needs to be updated,If omitted WHERE clause, then all data in the table will be updated.


Delete(Must where
DELETE FROM table_name [WHERE <condition>];


START TRANSACTION;
COMMIT;
ROLLBACK;

Logical architecture

1. Connection layer: TCP connects to Mysql, performs identity authentication, permission retrieval
2. Service layer:
   1. SQL interface SQL execution first performs Lexical analysis(keywords, identifiers, constants, operators, etc.), then Syntax analysis: based on the lexical analysis results, determine whether the input SQL statement satisfies Mysql syntax
   2. Parser Parser, takes the lexical units generated by lexical analysis, parses them according to SQL syntax rules, and builds an abstract syntax tree
   3. Optimizer Query optimizer, calculates the IO cost and CPU cost corresponding to SQL, generates an execution plan for SQL, determines the execution path, reorganizes SQL
   4. Cache&Buffer Execution path
3. Engine layer: truly responsible for Mysql storage and retrieval of data in the data, performs operations on the underlying data of the physical server, all logical processing and read and write operations only process operations buffer poll, will record the old value to Undo Log in
4. Storage layer

Database buffer pool buffer poll

InnoDB The buffer pool includes data pages, index pages, insert buffers, lock information, adaptive indexes Hash and data dictionary information, etc. The buffer pool size is limited, and it will based on position×frequency, prioritize caching for high frequency items

When InnoD B discovers that certain queries frequently access specific data pages, it will create hash indexes in memory for these data pages.

View/Set the size of the buffer pool

show variables like 'innodb_buffer_pool_size';
set global innodb_buffer_pool_size = 268435456;

View how many Buffer Pool instances
show variables like 'innodb_buffer_pool_instances';

Default is one. If set to multiple, the cache pool size set above is divided equally.
The cache pool needs at least 1g Setting multiple is only effective above this value.

For high concurrency, multiple may be better.
[server]
innodb_buffer_pool_instances = 2

Cache pool (part of memory): data modifications happen in the cache pool. Memory parts outside the cache pool: storage of metadata such as table structures and index information, state and variable information of database connections, temporary tables.

Query data First check the cache pool. If not present, check the disk, then load it into the cache pool (if it has not been flushed yet). Update data First check the cache pool. If not present, check the disk, then load it into the cache pool and modify and update it. The modified data page will be marked as dirty page (indicates that this part of the data has been modified in memory but has not yet been written back to disk)

• Dirty pages are subsequently, ata certain momentflushed back to disk. The timing of the flush may include: (if a crash occurs at this time, the update will be permanent)

1. The buffer pool reaches a certain threshold (such as the number of dirty pages exceeding a certain ratio).
2. When a transaction commits,MySQL the related dirty pages are flushed back to disk to ensure data durability.
3. Periodic background tasks (such as scheduled flushing) write dirty pages back to disk.

The flushing process writes the contents of dirty pages to disk, ensuring that data remains consistent and durable in physical storage.

JOIN

To obtain data in another table related to the current table, the following was designed JOIN

LEFT/RIGHT that is, retaining the information of one table A and the information of another table B that matches the ON condition; for non-matching ones, A will also have the fields of table B, but they will be NULL JOINthat is, merging the two tables, according to ON Under the condition, keep the data of another table

The optimizer converts the above into the following

• inner join(INNER JOIN)(JOIN) ON xxx;
• JOIN table USING(field);: same as inner join, using field and type can be used when identical, no need to write ON the condition after, it will be removed automatically USING(列名) the column names of
• Nature JOIN; equivalent to inner join, no condition needs to be written, the parts with equal fields are set as conditions automatically, duplicate column names are removed automatically
• left join(LEFT JOIN xxx ON xxx);: Keep the data unique to the left table, and the data from the right table that matches the conditions in the intersection.
• full outer join FULL OUTER JOIN (mysql is not supported), use UNION AL
• cross join CROSS JOIN(Cartesian product) suppose table1 has 3 rows,table2 has 4 rows, the result set will contain 3. Cross join does not need conditions, all rows will be combined.
• UNION ALL but UNION will deduplicate and cause low efficiency, generally not recommended
• JOIN ... USING is to simplify the display of fields and remove duplicates field, without affecting result set number of rows.
• UNION and UNION ALL are operations on the result set rows, the former deduplicates the result set of the same fields in two tables, the latter keeps all rows.

data type

Mysql基础#数据类型

char: 255, fixed, may be faster than varchar by 50 percent varchar : 65532, trading time for space

if any field is non negative, it must be UNSIGNED number type is DECIMAL, do not use FLOAT and DOUBLE

• (Mandatory) If the stored string lengths are almost equal, use the CHAR fixed-length string type.
• (Mandatory) VARCHAR is a variable-length string that does not preallocate storage space, and its length should not exceed 5000. If the stored length is greater than this value, define the field type as TEXT, separate it into a table, correspond to it with a primary key, and avoid affecting the indexing efficiency of other fields. - AlibabaJava Development Manual

DATETIME and TIMESTAMP similarities and differences

The storage time format is the same, and both can store microseconds, but the timestamp range extends to 2038

store emoji

default utf8mb3 change toutf8mb4

differences between delete, drop and truncate

DELETE : DML Delete specific records, supports transactions, and can be recovered.

• Function: used to delete specific records from a table.
• Syntax：DELETE FROM table_name WHERE condition;
• Effect：
  • Deletes only the records that meet the conditions.
  • You can use the WHERE clause to limit the scope of deletion.
  • Supports transactions, allows rollback operations (in storage engines that support transactions, such as InnoDB).
  • The delete operation records each deleted row in the log, which is suitable for fine-grained control.
• Usage scenario: used when part of the data needs to be deleted while other data is retained.

DROP：DDL Fast deletion speed, deletes the entire table or database, and cannot be recovered.

• Function: used to delete an entire table, view, or database.
• Syntax：DROP TABLE table_name;
• Effect：
  • Deletes the entire table along with its structure and data.
  • It cannot be recovered, and all relatedconstraints and indexeswill also be deleted.
• Usage scenario: used when an entire table or database needs to be completely removed and no data or structure is needed anymore.

TRUNCATE：DDL Quickly clears all records in the table, retains the table structure, and is usually unrecoverable.

• Function: used to quickly delete all records in a table.
• Syntax：TRUNCATE TABLE table_name;
• Effect：
  • Deletes all records in the table, but retains the table structure.
  • Does not support the WHERE clause, and cannot delete selectively.
  • Usually faster than DELETE , because it does not record deletions row by row, but directly releases data pages.
  • In some cases,TRUNCATE may not trigger DELETE triggers.
  • Cannot be rolled back in a transaction (in most cases).
• Usage scenario: used when all data in a table needs to be quickly cleared and the details of row-by-row deletion are not a concern.

Money

Fixed-point number : decimal(x,y) x represents the number of digits, y represents the number after the decimal point

count (*)，count (1)，count (列名)

count(*) does not ignore Null count(1) Do not ignore Null count(列名) Ignore Null

count (主键)>count (1)>count(列名)>count (*)

If there is only one field, count(*) fastest

Mysql/Mysql advanced > 10 11 2 Efficiency of COUNT and COUNT specific field

Engine comparison

Page structure

Three ways the buffer pool loads pages

1. Memory read
2. Random read
3. Sequential read Mysql内部结构#^vep6gv

To avoid random reads, the extent was designed

page (16 kb)(I/O and the smallest unit of disk interaction) leaf node page/non leaf node page (directory page) That is, at minimum, one read 16kb into memory --> extent(at most 1MB=64 continuous pages×16kb)(at least two extents[leaf/non leaf])(avoid random reads caused by different physical page locations IO)``fragment extent:at least two extents 2M,wastes space, so later the fragment extent was introduced,does not independently belong to any segment --> segment(leaf node extent/non leaf node extent)(extents of the same type are called segments)(data segment, index segment, rollback segment)

Although the InnoDB storage engine of MySQL tends to store data pages physically contiguously in its design to optimize performance, due to the dynamic nature of data, deletions, updates and other operations, discontinuous situations may occur in actual storage. Therefore, physical contiguity is not absolutely guaranteed.

• Index space allocation

No longer by page, but by extent. A large amount of data may allocate many contiguous extents (it cannot guarantee that all pages are contiguous, but it can guarantee that pages within one extent are physically contiguous, avoiding random IO. Extents are not necessarily contiguous between each other and are connected by a doubly linked list)

Page internal structure

• Data page

The data page is divided into file header File Header (page number, previous and next page numbers, page type, checksum[roll back when an anomaly is found]) file trailer File Trailer (checksum, roll back when an anomaly is found, the checksum at the trailer can provide extra protection) page header Page Header (such as how many rows of data this page stores, the address of the first record, etc.) (the number of slots in the page directory) maximum and minimum records of this page Infimum minimum+Supremum maximum user records (each record is connected by a singly linked list) stored according to the specified row format Mysql内部结构#COMPACT 行格式 free space will be requested from the free space into user records page directory (A page contains a lot of data, and to quickly find data, the page also needs a page directory) Divide into 4-8 records per group, and take only one maximum from each group (the first group takes the minimum)

user records：InnoDB row format: Compact , Redundant, Dynamic, Compressed

• Compact row format : every inserted record has a 变长字段的长度列表 null 值列表 (records whether the value of each column is null)(1 represents as NULL，0 not as NULL)

  • record header information: delete_mask records whether this record is deleted, 0 not deleted 1 represents deleted (those marked as garbage form a so called garbage linked list, called reusable space, and new data may overwrite the space of deleted records) min_rec_mask whether it is the smallest record in a non leaf node (B+the smallest record in each non leaf node of each level of the tree adds this flag, marked as 1) heap_no the position of this record in the page n_owned the last record of each group in the page directory has this value, represents how much data this group has record_typerecord type 0 represents ordinary record,1 directory entry,2 represents smallest record,3 represents largest record (whether it is the largest or the smallest is based on the primary key, or one defined by yourself) next_record the byte offset of the next record

  隐藏列 Mysql internal structure#hidden column row_id/transaction_id/roll_point

• Between pages: doubly linked list, convenient for traversal across pages.

• Between records within a page (row format): singly linked list, convenient for traversing records within the same page.

• Page directory Mysql内部结构#页目录 The page directory stores slots, the maximum value of each group

Because records within a page are a singly linked list, traversal is needed, and insertion and deletion are efficient, but retrieval is inefficient, so a page directory is specially made to use binary search to quickly find data in a page

If a page has 1000 records, there must also be a page directory internally, and using all 1000 records for the page directory is too large, so group them in batches of 4 to 8, and take only one maximum from each group (the first group takes the minimum), and place the one taken from each group into a slot slot , requiring at most 250 slots, and then perform binary search on all slots

The first slot (minimum record): corresponds to one record (only one, usually used to ensure there is a valid starting point during search. Its existence is for convenient range queries and sequential traversal, for structural optimization purposes) The middle slots (maximum record): each corresponds to 4 to 8 records The last slot (maximum record): corresponds to 1 to 8 records

insert into table (x,x,x) VALUES (x,x,x)  where
update table set x=x x=x x=x  where 
delete FROM table where xxx

index

Function categories: primary key, normal, unique, single column, multiple columns, full text

An index is the data structure of a table, a B plus tree data structure. The data seen in Navicat is seen by accessing the clustered index, that is, the B plus tree structure. In order to help mysql a data structure for efficiently obtaining data

InnoDB the three iterations of the index B+tree, which means additions were made to the directory page structure Indexes are in storage engine and the storage engine can define the maximum number of indexes and index length

where the index is used

1. select data needed later
2. first where then group by then having then order by finally limit the position of

index advantages : reduce IO, ensure the uniqueness of each row of data in the database, reduce the time for grouping and sorting in queries Index drawbacks : Creating and maintaining indexes consumes time. Indexes occupy disk space. They reduce the speed of updating tables. The more indexes built on a table, the more storage space is occupied, and the worse the performance of insert, delete, and update operations becomes. The number of indexes generally should not exceed six. When exceeded, while table data is being modified, the indexes are also adjusted and optimized, creating a burden. The optimizer selects the actual index to use. With multiple indexes, the time for the SQL optimizer to generate an execution plan increases, reducing query performance.

MySQL and MySQL Advanced > Clustered index. Index is data. Data is index.

• Clustered index/Primary key index``Index is data/Data is index Each table has a clustered index that contains all the data in the table, which means the table itself constitutes the clustered index.

Clustered index It is not a separate index type. By default it is based on the Primary key Build. If no primary key is defined, automatically use the unique field as the primary key. If there is still none, the system will automatically provide a hidden field Mysql/Mysql internal structure > hidden columnto build the index. All user records are in the leaf nodes of the clustered index

• (secondary index)nonclustered index/composite index The order of the composite index must satisfy sql the execution order so it can be used first where then group by Then having Then order by Finally limit

• Clustered versus non clustered Considered from CURD Insert and update performance for clustered indexes is poorer: inserts and updates may cause data reordering, affecting performance, especially when a large amount of data is inserted. A clustered index is stored according to the primary key, that is, in true physical sort order, while a non clustered index is sorted according to some field, such as update At time C3, the clustered index needs to be changed, while the non clustered index may not store this field and does not need to be changed

Non clustered indexes do not affect the physical storage order of data, and insert and update operations are relatively fast. Different non clustered indexes can be created according to different query needs to optimize specific queries.

Mysql8 Support for creating descending indexes and support for invisible indexes (sometimes index creation consumes a large amount of system resources, and sometimes it is necessary to test the difference between having and not having an index; deleting it directly may then require recreating it and consume a large amount of resources, so invisible indexes are supported)

Suitable indexing: UPDATE、DELETE of WHERE condition columns /Group By of/ DISTINCT fields / multiple tables JOIN join fields / limit fields / small data types (saving space) / string prefixes (prefix index) (a portion) / leftmost prefix (put the most frequent on the left) / limits /

Unsuitable for indexing : many duplicate data and 10% of / unused where fields, tables that are frequently updated with too many indexes created / unordered values / rarely used / repeatedly defined / fewer than 1000 pieces of data

For limit , avoid such as LIMIT 1000, 10; the database also does not know when the 1000 th piece of data begins (use bookmarks or self join optimization)

为什么程序员对这8种SQL的写法零容忍 (09_12_2023 16_30_06) ↗

Performance analysis tool usage

disk IO>memory IO>algorithm time complexity

What the index reduces is the number of disk IO operations, which is one level higher than time complexity (in memory); assume one disk interaction takes 200 ms, while time complexity takes only 4 ms

SHOW STATUSView the status and configuration of the MySQL server.

Mainly used to monitor some basic performance metrics, such as connection count, query count, lock waits, and so on.

Last lookup cost : SHOW STATUS LIKE 'last_query_cost';

If the lookup is on a sequential index, the effect of the number of pages looked up is small, and the time difference is not large, because sequential lookup loads pages into the buffer pool all at once

mysqldumpslow Targets slow executing SQL (default 10s)

Mysql/Mysql advanced > Slow query log analysis tool: mysqldumpslow

Performance SchemaProvides detailed monitoring of MySQL server performance

• Allows collection and analysis of various performance metrics, such as locks, wait events, I/O, and so on.
• Provides real time monitoring capability, suitable for long term performance analysis and trend observation.

explainUsed to analyze the execution plan of SQL queries EXPLAIN You can view what the optimizer specific execution plan is

After 8.0 explian introduced tree shaped explain outputFORMAT=tree

EXPLAIN DELETE FROM tabe where XXXX # did not truly delete, only view what the execution plan is

Mysql/Mysql advanced > Role of each EXPLAIN column

explain Each item

id/select_type/table/type/possible_keys/key/key_len/ref/rows/filtered/Extra

id : several select there are several id, the optimizer rewrites nesting as a multi table join

select_type : primary,union.subquery Does not include union or subqueries are all considered simple

• type :
  system tableonly one row，generally a system table, often does not require disk IO，very fast const using a primary key or unique index to return only one rowequality match eq_ref 被驱动表 is through the primary key orunique secondary index column equality matchingway for access refwhen through普通的二级索引列and常量perform等值匹配to query a table fulltextindicates use of full text index lookup, suitable for full text search on text fields ref_or_nullwhen performing an equality matching query on a normal secondary index, the value of that index column can also be NULL value index_mergeindicates use of multiple indexes for the query,MySQL will merge the results of multiple indexes unique_subqueryOptimization when a subquery returns a unique value, usually used for IN subqueries. index_subquerysimilar to unique_subquery，but the subquery may return multiple rows. range Retrieves only rows in a given range, using索引to retrieve. Inwhereused in the statement bettween...and、<、>、<=、in and other conditional queries type are all range index indicates a full index scan, although compared to a full table scan (ALL）faster, but still not the best choice, usually used when using the last condition of a composite index, index，this indicates MySQL It did scan the index, but that does not mean the query effectively used the index. It still counts as an invalid index.

  ALTER TABLE `countries` ADD INDEX (country_id,region_id,country_name);
   Explain SELECT * FROM countries WHERE country_name='Canada';
  

  ALL The least efficient full table scan system > const > eq_ref > ref > range > index > ALL

• possible_keys Column: indexes that might be used, but are not necessarily actually used.

• keyThe index actually used. If it is NULL, then no index is used.

• key_len MySQL The length of the index decided to be used (in bytes), the length of the index actually used, convenient for checking whether the index is fully utilized, compared with its own value such as varchar range of The larger the value, the better. , mainly for composite indexes, has some reference value, when the table has multiple indexes available,key_len the field can help identify which index is most effective and whether the index is fully utilized

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';#  KEY_len=303
 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b'; #  KEY_len=606
 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; # KEY_len=909

• ref Column: the source of the value used for comparison with the index column

• rows the estimated number of records that need to be read The smaller the value, the better.

• filtered the percentage of remaining records in a table after filtering by search conditions, which determines the number of times the driven table must execute, rows multiply filtered to get the number of rows that satisfy the condition

• Extra additional information about query execution

  • Using index``covering index
  • use where normal where Using an index where does not prompt, for Null
  • WHERE the condition is always false pattern will prompt impossible WHERE
  • using MRR(off by default) For a secondary index range query, first cache and sort the query results of the secondary index, then uniformly go back to the clustered index for table lookups

Index coverage: you can find the desired data without going back to the table. The leaf node happens to contain these two pieces of data, that is to say select the data in is exactly in the index

ALL The further forward, the higher the efficiency, and the last three have the lowest efficiency

Index invalidation

Index invalidation means that a query fails to effectively use the index, resulting in performance degradation type is Index even ifindex invalidation

clustered index : physical storage is sorted according to the index nonclustered index : physical storage is not sorted according to the index

Mysql/Mysql Advanced > Index invalidation cases

• (+,-,!=) and function (mysql also do not know what function it is, it will take out the data one by one and perform function operations)

• leftmost prefix principle

• type conversion (automatic or manual) (may cause the database system to be unable to use indexes, resulting in a full table scan, and row locks escalating to gap or next-key locks, causing stalls)

• used in a composite indexrangefield, the fields to the right of it cannot use the index (juxtaposed and, or where the fields after Order by) (the fields on the right cannot continue to use the above index), but the field that declares the range can itself use the index. If the range field has a relatively large impact on performance, consider using the range,sacrificing the indexes of other fields. Sometimes creating an index on the range can greatly reduce the amount of data. Let the index filter out as much data as possible (the optimizer can optimize the left and right order of and, but the index order is fixed) (or set the field that declares the range as the last field of the composite index, then all of them can use the index) the one declaring the range is the last field of the composite index

• with a wildcard % Index at the beginning becomes invalid. Strictly prohibited Left/Full fuzzy

• OR Before and after There is a non-indexed column, the index becomes invalid, both left and right must have indexes

• ascending descendingTo use the index, they must remain consistent

• Left/Right join For related queries, the field encoding format Not the same, the character encoding formats connecting fields are different, which may cause index invalidation. Use the same character set uniformly for the database and tables utf8mb4, to unify the garbled text produced by character set conversion. Different character sets need to be converted before comparison Conversion will cause index invalidation

• is not null(may) means not equal to a certain value, causing index invalidation (too many table lookups, too much data, too high a cost, the optimizer will force a full table scan), is null Indexes can be used, and it is best to design and set the field as not null a constraint

• When the data volume is small, the optimizer will choose not to use the index

Mysql If the optimizer estimates that a full table scan will be faster than using an index, then the index is not used and The order of the statements on the left and right does not affect the composite index; the optimizer automatically sorts the statements. Order BY If the fields in it are sorted in ascending or descending order, all fields must be consistent to be able to use the index.

Sometimes not all indexes are used; the optimizer might decide during optimization that using the second index is unnecessary.

In addition, whether an SQL statement uses an index is related to the database version, the amount of data, and the data selectivity.

##### `Join` Optimization

`simple/index/block/ Nested-Loop join`
[[Mysql Optimization#Index optimization and query optimization]]

Sometimes an outer join is changed to an inner join by the optimizer

For inner joins
 
If neither has an index: (The small result set drives the large result set)
If only one index is added, The table containing the indexed field is used as the`被驱动表`
if both have indexes, the optimizer decides itself which one is the driving table, it chooses the one with less data as the driving table, the one with more data as the large table, automatically choose the lower cost (small table drives large table)

`simple/index/block/ Nested-Loop join`

`simple` : driven table (non driving table) loaded into memory, the driving table fetches one row of data, after traversing the non driving table it is cleared, and next time it must be loaded again
`index` : on the driven table `有` index, the driving table compares the index, then goes back to the table to get the data, when both tables have indexes it is also index
`block` : driving table`没有`when indexing, load some data of the driving table at once, so the driven table does not need to be cleared after each load, compared to simple saves a large amount of resources

which join algorithm is used specifically depends on the query execution plan and the optimizer decision


##### subquery optimization (temporary table)

avoid using subqueries, because `mysql` it will create `临时表` to complete the subquery, and after the query completes these tables are dropped , consumes too much `CPU` and `IO` resources, and the created temporary table does not have any index

through `Join` a query can replace a subquery, no temporary table is needed, plus with an index, it is much faster than a subquery


#####  `Order By` sorting optimization

[[Mysql advanced#sorting optimization|Mysql/Mysql advanced > sorting optimization]] 

`Extra` in `Using index/Using FileSort` 

`Using index` no sorting is performed, the index is used, the index already guarantees order
`Using FileSort` sorting in memory, requires additional disk IO and sorting processing time, occupies CPU high, not necessarily the slowest, in the case it is faster than `using where` fast

on `WHERE` using an index is to avoid a full table scan
on `Order BY` using an index is to avoid `FileSort` sorting,`Index` the efficiency of sorting is high

when the data volume is too small `Group By` it will not use the index, or it only uses part of a composite index


`UsingFileSort` not necessarily the slowest, if `where` after filtering the data volume is very small, it may be faster than ` index` even faster

`Order BY` if the fields in are used for`升序`or`降序`，all fields need to stay consistent to use the index, if the sorted data rows exceed the length set by this parameter,`MySQL` it will use a disk based sorting algorithm, for example using temporary files, otherwise it will use quicksort in memory.



```ad-info
title:introduction

![[assets/Mysql Advanced/image-20231217164859352.png]]

![[assets/Mysql Advanced/image-20231217165241344.png]]
![[assets/Mysql Advanced/image-20231217165249432.png]]
`name` is also used

![[assets/Mysql Advanced/image-20231217165551724.png]]
![[assets/Mysql Advanced/image-20240120172156467.png]]
![[assets/Mysql Advanced/image-20240120172546935.png]]
![[assets/Mysql Advanced/image-20240120172151464.png]]

GROUP BY Optimization

group by The principle of using an index is almost the same as order byconsistent,group by even if there is nowherefilter condition that uses the index, the index can still be used directly.

When the index really cannot be used, adjust the parameters to increase max_length_for_sort_data and sort_buffer_size parameter settings, which respectively affect the processing of sorted data and the size of the sorting buffer.

Pagination query optimization

Optimization considers the index, converting the LIMIT operation into an operation that can use the primary key index

• Bookmark method optimization: use the position of the previous query

1. Use an index
2. first order by / where reduce the data volume and then Limit

• self join Optimization

change to an equality join

Index covering

Index covering specifically refers to using a non clustered index such as a secondary index to satisfy the query condition, where the index contains all the data needed by the query

avoid the secondary query caused by going back to the table, which reduces efficiency, turning random IO into sequential IO but personnel are needed to maintain the index fields

EXPLAIN SELECT * FROM student WHERE age = 20 Using the index but going back to the table, it will quickly find the primary key id satisfying age equal to 20 through the index, and then go back to the table based on the primary key to look it up from the clustered index

Index condition pushdown (ICP: index condition push)

Extra:Using index condition

Both query conditions arethe same fieldWhen another index becomes invalid, during the query it no longer goes back to the table and directly uses the data found by the first condition for filtering

Others

Mysql or Mysql advanced > Other query optimization

EXISTS and IN distinction

in The statement treats the outer table and the inner table as an hash join, exists The statement performs a loop loop on the outer table, the above join, loop

1. If the two tables being queried are similar in size, then using in and exists there is little difference.
2. If one of the two tables is smaller and the other is a large table, then when the subquery table is large use exists, when the subquery table is small use in
3. not inand not exists:
   1. not in, if no index is used, then both the inner and outer tables perform a full table scan,
   2. not extsts the subquery can still use the index on the table. So no matter which table is large, using not exists is faster than not in Be quick

index condition pushdown (enabled by default)

When both query conditions in SQL are on the same field one query condition can use the index, while the other query condition cannot use the index. For the other query condition that cannot use the index, there is no need to go back to the table again; directly use the data from the first query condition that uses the index.

Normal index vs unique index change buffer

Normal index vs unique index. There is no difference between the two in query capability.

Change Buffer is InnoDB a mechanism used to optimize non unique index update operations. It is located in the shared buffer pool and is used to cache changes tosecondary index pages.

unique index updates do not use change buffer ,
normal secondary index : update performance well, and can be used together with change buffer to work together. It is quite obvious for optimizing updates on tables with large amounts of data. If an update is immediately followed by a query every time, it should be disabled. change buffer, while in other cases change buffer can improve performance (update cache operations are cached in the change buffer, avoiding rereading from disk)

If the main consideration is the impact on, it is recommended,

Change Buffer is a memory structure used to store modifications to secondary indexes, such as inserts, updates and deletes, without immediately writing these changes to disk. This can reduce disk I/O operations and thereby improve performance.

Summary : frequently modified : normal index

After a normal index finds records that meet the conditions, it continues searching until it encounters a record that does not meet the conditions. For a unique index, once a matching record is found, it does not continue searching further.

change buffer : When a data page is to be updated, if the page is in memory it is updated directly, otherwise it is updated to change buffer , avoiding directly reading data from disk, performing page related operations in change buffer , and then background system threads periodically merge, that is, updating back to the original data page, and when the database is shut down it also merge, through change buffer , the execution speed of statements will be significantly improved, and this method can also avoid reading into memory and occupying buffer pool

LIMIT 1 impact on optimization

For a normal index : if it is known that there is only one result, then after obtaining one result it will not continue scanning, which speeds things up For a unique index : there is no difference

commit

commit can improve performance

Release resources : undo segments are used to restore data information and locks acquired by program statements redo/undo log buffer space in

Taobao database, how is the primary key designed?

Taobao database, how is the primary key designed?

does not use a primary key, because it is unreliable, unsafe, poor for query performance, involves many interactions, and does not have global uniqueness

Taobao order ID = time+deduplication field+user ID after 6 digit tail number

Mysql, Mysql advanced > Taobao database, how is the primary key designed?

when not suitable for indexing

where fields that are not used, high data repetition rate, cannot quickly locate, tables with small amounts of data (less than 1000), where creating an index has little meaning, tables whose data is frequently updated, unordered fields, rarely used indexes that are deleted, redundant duplicate indexes

Summary

optimization is through explian analyzing the current sql statements, in view of where / join / sorting / GROUP BY / limit analyze whether these statements have room for optimization, check index usage, how many indexes are used, whether index coverage is used (no need to return to the table, random IO becomes sequential IO), and avoid index invalidation

If it is join need to check filtered

index covering: you can query the desired data without going back to the table

where efficiency is higher than having , can be written in where for qualifying conditions, do not write them in having in it reduce order by, do sorting on the program side contains order by、group by、distinct the statements of these queries, please keep the result set filtered by the where condition within 1000 rows, otherwise SQL will be very slow

what ways are there to optimize slow SQL

• slow query log: enable the slow query log of MySQL, then through some tools such as mysqldumpslow to analyze the corresponding
  slow query log, of course nowadays general cloud vendors all provide visual platforms.

• service monitoring: you can add monitoring of slow SQL in the business infrastructure, common solutions include bytecode instrumentation, connection pool extension, ORMframework process, to monitor and alert on slow SQL running in the service.

normal form

reduce data redundancy, improve data integrity and consistency, and make the database structure clearer。

Mysql高级#范式

first : each field has atomicity and cannot be split second : on the basis of satisfying the first normal form, satisfy that every data record in the data table is identifiable, all non primary key fields must fully depend on the primary key, and cannot depend on only part of the primary key, that is, all primary key fields must be used to derive the primary key before it counts as the second normal form (name, student number, course number-->score) satisfies (name, student number, course number, gender-->score) does not satisfy

third : on the basis of the second, ensure that every primary key field is directly related to the primary key

denormalization: generally the first normal form is followed, the second and third are not followed, for example, specially designed redundant fields

when to consider redundant fields: this field is indispensable for queries and does not need to be modified frequently

ER model

the fewer the number of tables, fields, and fields in composite primary keys, the better the more primary keys and foreign keys are used, the better

transaction

a transaction is a group of logical operation units , is data changing from one state to another state it originated in the financial industry at the earliest, and later when computers needed data consistency operations, it was naturally referenced

• ACID features atomic , consistency ,isolation, durable

atomicity (basis): the transaction state remains fully consistent, either all commit or all fail and roll back, there will be no intermediate state, indivisible just like an atom. (For example, program crashes, deadlocks, etc., UNDO LOG implemented by the rollback log, if the transaction fails midway or is explicitly rolled back, the system can pass UNDO LOG restore to the state before the transaction began.) consistency (constraint): before and after transaction execution, data changes from one legal state to another legal state, consistency ensures that transaction execution will not violate the integrity constraints of the database (UNDO LOG rollback log implementation UNDO LOG will ensure that all modifications are undone, thereby returning the database to the consistent state before the transaction began) isolation isolation (means): during the execution of one transaction it cannot be interfered with by other transactions ( lock mechanism implementation) (isolation level) durability (Purpose): Once a thing is committed, the change to the database data is permanent (REDO LOG implemented by the redo log, and if the system crashes immediately after the transaction commits,REDO LOG it can also be used to redo these operations)

undo The log ensures atomicity and consistency,redo and ensures durability

The state of a thing

Active / partially committed / failed / aborted / committed

MySQL thing concurrency problems

mysql 8.0 The default isolation level is repeatable read

• Repeatable read isolation level write-write : write lock solves read-write: without locking, MVCC snapshot read (multi version concurrency control), does not affect writes, can read and write at the same time, but there may be data inconsistency problems, so some business logic must manually add locks

Under the serializable isolation level,in most cases there is no need to manually add locks, because the database handles it automatically.

MVCC In specific scenarios a better solution is provided, which does not mean read write locks are no longer useful

1. Multi version concurrency control MVCC : it is generating ReadView to find historical versions (by undo log), different isolation levels control differently, that is, the timing of generating ReadView is different
   1. READ COMMITTED : each time SELECT operation is executed, a ReadView is generated, reading is also selectwill only see the changes made by things committed before ReadView was generated, changes after or uncommitted cannot be seen, solving dirty read
   2. REPEATABLE READ : a transaction during execution only generates SELECT on the first execution of a ReadViewoperation, and before this thing ends, subsequent SELECT operations all reuse this ReadView, which also avoids the problems of non repeatable read and phantom read

Cannot guarantee that the data obtained is the latest, READ COMMITTED relatively REPEATABLE READ a bit more updated

Dirty write > Dirty read > Non repeatable read > Phantom read (all are isolation problems caused by not guaranteeing) Read uncommitted>Read committed>Repeatable read>Serializable

current read means a transaction directly reads the latest data version of the current database when executing. It usually needs to acquire locks (such as exclusive locks or shared locks) to ensure the accuracy and consistency of the data when reading.

snapshot read means a transaction reads the data version of a certain moment without needing to add locks. It uses the MVCC mechanism to provide a snapshot of the data, allowing the transaction to read data without interfering with other transactions.

dirty write: thing B modifies the data that thing A is modifying, and thing A rolls back causing thing B modification to become invalid solve: 读未提交 ,a transaction can perform read operations without acquiring any locks, so other transactions can still concurrently perform current reads, which can cause dirty reads, but writes add locks to avoid dirty writes

Under the read uncommitted isolation level, a transaction can read changes made by other uncommitted transactions, even if these changes are not formally committed in MVCC. MVCC Version:MVCC is designed to manage committed data versions, so under higher isolation levels such as Read Committed, Repeatable Read, Serializable, a transaction reads MVCC the committed version in

dirty read: one data item, one transaction A modifies the data and it is still uncertain whether it will roll back or the data has not finished being processed, when another transaction B accesses that data, and the data read at this time is dirty data (Dirty Data), transaction A may roolback cause the data read to be invalid 解决 : 读已提交: A transaction can only read data that other transactions have already committed. If not committed, it reads the data from before the commit. Non repeatable read means that data from other committed transactions also cannot be read. mysql落地解决方式：读已提交 Each read generates a Readview, This way only the committed snapshot can be read, and data currently being modified will not be read. For writes, a write lock is applied.

Non repeatable read: one piece of data, onetransaction Areads multiple times, anothertransaction Balso reads and modifies this data, causing the first transaction A to read different results multiple times 理论解决方式: 可重复读: in 自己事务过程中 the data read multiple times is consistent, and is not affected by other transactions during multiple reads within its own transaction,另一个事务已经提交了也读取不到（MVCC）/让另一个事务无法修改（锁） mysql落地解决方式：MVCC、lock mechanism可重复读 Generate only on the first read Readview, Guarantee it will not read other things, But it cannot guarantee the timeliness of the data, another transaction may modify during the read, causing stale data, still need to manually acquire the lock to handle, to avoid the influence of other transactions, in addition combined with 临键锁``间隙 avoid some phantom reads

phantom read : transaction A After itself reads data within a range, another transaction inserts new data, causing the content read twice to be inconsistent Query a specific row, Although the second time there is no more than the first time 多出 newly inserted data, but inserting the same data will fail (If data is missing it is called 不可重复读) solution: Serializable: fully uses locks and no longer uses MVCC，adds locks during reads,

spring the things in

springboot Enable transaction management on the startup file, add the annotation @EnableTransactionManagement

annotation @Transactional, annotated on the service layer of the method, class, interface on

• read only @Transactional(readOnly = true)
• timeout rollback, release resources@Transactional(timeout = 3)
• Rollback Strategy rollbackFor / rollbackForClassName / noRollbackFor / rollbackFor
• Isolation Level : Not all databases support all isolation levels. Some databases may downgrade a higher isolation level to a lower one

@Transactional(isolation = Isolation.DEFAULT)//Use the database default isolation level
@Transactional(isolation = Isolation.READ_UNCOMMITTED)//Read uncommitted
@Transactional(isolation = Isolation.READ_COMMITTED)//Read committed
@Transactional(isolation = Isolation.REPEATABLE_READ)//Repeatable read
@Transactional(isolation = Isolation.SERIALIZABLE)//Serializable

• Propagation Behavior propagation ^sz3dj3

When A calls method B, whether A has a transaction and the propagation setting of B ../JAVA微服务核心/JAVAWEB/JavaWeb#传播行为 propagation required / required_new / supports / not_supports / never / mandatory / nested

When A calls method B

• required: B needs a transaction A If there is a transaction, then B join A A no thing , B then create a thing
• required_new B needs a new thing A if there is a thing then suspend,B a new thing will prioritize ensuring it runs
• supports B supports transactions, A can have no transaction A if there is a thing then join, if no thing then it does not matter
• not_supports B does not support things A call without a thing B, A there is a thing B then suspend
• never A, B both must have no thing A call B, A also must have no thing, other d then exception
• mandatory A call B, A must have a thing, otherwise exception
• nested A call a new thing B, A a thing exists as a nested thing,A no thing executes as a new thing

log

redo and undo binlog

data modification before appearance undo log rollback log, its function istransaction rollback, that is, when an error occurs during transaction execution or the user actively rolls back, the data can be restored to the state before the transaction started.undo log It is also used to implement MVCC, so that different transactions can read different versions of data. The main function of undo log is to roll back uncommitted transactions, it cannot be used to undo committed things, that is, human error or code execution cannot be recovered

data modification after appearance redo log redo log

if an operator makes a mistake and wrongly executes a statement, and it is unrelated to the transaction, then it is necessary to rely on binlog (change history query, database replication, backup and recovery), redo log is only responsible for ensuring that data committed by transactions is not lost, it does not record the statements executed by transactions. If an operator makes a mistake and executes a wrong statement, redo log cannot provide a rollback operation to the state before the mistake.

• undo log is the regret medicine, it takes effect during transaction execution;
• The redo log is a safe deposit box, taking effect when a transaction commits, ensuring the durability of committed transactions
• The binlog is a time machine, taking effect after a transaction ends, allowing recovery to the data state at some past point in time.

redo log (data recovery guarantee durability) redo log

REDO LOG Mainly used in the system crash after recovery modifications of committed transactions to ensure transaction durability REDO LOG Not exposed externally, mainly used for the internal crash recovery process of the database

Records for InnoDB the storage engine transaction log

redo log : To ensure persistence, we only need to ensure that changes can be recovered after a crash and restart For example, a transaction changes the value in the system tablespace page 10 at offset 100 the byte at 1 to 2。We only need to record: the value at the offset in tablespace page 0 of tablespace 10 open parenthesis page with offset 100 is updated to 2

redo Logs are written to disk sequentially During transaction execution,redo log continuously records The space occupied by logs is very small Reduce flush frequency

undo log Log (guarantees atomicity and consistency ) undo log

Each SQL, through the executor, is immediately in the rollback segment UNDO LOG

UNDO LOG Mainly used forrollback transaction, to ensure the atomicity and consistency of the database. It records the original value of each transaction before modifications were made to the data during execution.

Role :

1. Rollback data
2. MVCC MySQL/MySQL Advanced > undo log

binlog andredo log what is the difference

   Only when a transaction successfully commits , will MySQL write to the binlog the all logical operations during this transaction.

Unsuccessful during transaction execution ->Used for rollback undo log That is, during transaction execution ->continuously written redo log ->But only after the transaction commits will it be used in a crash redo log, if the transaction crashes before committing then undo log When the transaction commits ->write bin log(because writing before commit is meaningless, not committing means execution is not finished, a failure may occur relying on undo log is sufficient) ->- Use binlog to restore the database to some past point in time (for example, when a misoperation occurs) / During master slave replication, the replica synchronizes data by reading the master binlog / Used for auditing database operations

bin log It is recorded after the transaction commits, and it is necessary to ensure the transaction has committed before recording, so two phase commit is needed

Binlog Used to record all operations that change the database, including INSERT、UPDATE、DELETE etc. It is mainly used for data replication and recovery. It supports master-slave replication and can be used to restore data to a certain point in time.

Do you understand how an update statement is executed

An update statement is executed, first in Undo Log record a reverse record,Undo Log after the record is completed, then enter Buffer pool in, to Buffer pool modify the data in it (this step executes the modification command), after modification write to Redo log Buffer

1. enter prepare phase
   1. Redo log Buffer write redo Log, marked as prepare ,
   2. binlog will be written to the in-memory binlog cache
2. enter commit phase
   1. flush to disk and write binLog, after the write is complete set Redo log to commit, and one data write is complete. As long as binlog the disk write succeeds, even if redo log state is still prepare it does not matter, the transaction will still be considered successfully executed;

write first redo log , then write bin log

Relational databases (such as MySQL) usually record the log before executing the command (to facilitate failure recovery), while the Redis AOF persistence mechanism records the log after executing the command.

Why does redo log have two-phase commit

• redo log It focuses on changes to physical data, ensuring that updates to data pages can be recovered after a crash.
• binlog It focuses on logical operations, recording how to change from one state to another

binlog what is recorded islogical operation, that is, the specific operation on the database, such as an SQL statement or a data change event. When you execute UPDATE table_name SET a = 3 WHERE ... ,binlog will record this SQL statement, not the change of physical data.

• Crash scenario

Direct commit is divided into the following two types

write firstredo log , then writebin log ：

after writing binlog log and commit after, it has durability, If it has not yet commit and then crashes, it is divided into three cases

1. binlog A failure occurs when trying to write, MySQL checks and finds no data, and performs undo log rollback
2. binlog has finished writing but has not had time to commit and then crashes, at this time MySQL will check whether there is a record of this transaction in binlog。

• If there is a record of this transaction in binlog, it means the first step of the second phase succeeded, at this time MySQL considers this transaction complete, sets this transaction redo log to commit, and completes the transaction commit

1. binlog: A crash occurs halfway through writing. When MySQL starts and recovers, it will check the binlog file and identify which are complete transaction records and which are incomplete. For incomplete binlog records,MySQL it will directly skip them during recovery. This avoids data inconsistency on the slave, and finally mysql performs undo log rollback

• Why not delete binlog:
  • Performance consideration: binlog is append write, deletion operation would be very complex, involving modification of the file structure, and very inefficient.
  • Fault tolerance consideration: Even if there are some incomplete transaction records in binlog, these records will be skipped during recovery and will not affect data consistency, because only after a transaction is successfully committed will the slave synchronize these complete binlogs.

If write first bin log , after a crash the updated data will not be recovered, but, because bin log This update operation has already been recorded in it. When using bin log for data recovery or backup, it will be considered that the data has already been updated, but in fact it has not been updated. This update will be synchronized to the replica, causing the replica to have one more record than the primary

Phase one prepare: used to ensure that a crash can be recovered correctly. If the crash occurs after prepare , MySQL can, based on the state of redo log  , determine whether the transaction can be committed or needs to be rolled back Phase two commit: used to ensure that the replica redo log is consistent, ensuring the atomicity of the transaction(either all succeed or all fail) and the integrity of the primary database binlog, thereby indirectly ensuring primary and replica data consistency。

Index

redo log how it is flushed to disk

Mysql/Mysql advanced > ^5wu9ub

When will it be flushed to disk

log buffer / when the transaction is committed / background thread input / normal server shutdown / checkpoint Rules

How many records can one B+ tree store

Amount of stored data = number of leaf nodes = number of records stored in a single directory page multiplied by number of records stored in a single subdirectory page multiplied by number of records stored in a leaf node page

If we assume the directory page can store 1000 directory entries and the leaf node can store 100 directory entries then three levels can store 1000 multiplied by 1000 multiplied by 100 equals 100 million

Why use a B+ tree and not an ordinary binary tree

Why use a B+ tree and not a B tree

B+ is an enhanced version of B / strong scanning capability, only needs to traverse leaves / strong disk read and write, fewer IO times, and stable IO times, meaning stable efficiency / strong sorting capability (data is connected via a linked list)

B+ All data is at the bottom layer, efficiency is stable, and non leaf nodes do not store data, so they can store more directory information and connect more leaf nodes, which means they can store more data. Also, since all data is at the bottom layer, each page is connected via a doubly linked list, making range queries very convenient

The search process of one B+ index tree lookup

How to add an index to a large table

Because creating an index locks the table, it may affect normal business. Therefore, create a table B with the same data structure, create the required indexes, then import the data from the large table into table B, then rename table B to the large table name and give the original large table an alias

hash Index and B+ tree differences

Lock

Dirty write: modified the data of another thing

Dirty read: (reading data being modified by another transaction) one transaction is accessing and modifying data, and before the modification is committed to the database, another transaction also accesses and uses this data. The data read is dirty data (Dirty Data), and operations based on dirty data may be incorrect
Solution : when transaction A has not ended, the value read by transaction B query is the value from before transaction A started. Even if it has been modified, querying again after transaction A commits will produce a changed value, so it is non repeatable read

Non repeatable read: (the data read by the transaction itself is modified) one transactionreads multiple timesBefore the same data read has ended, another transaction also reads and modifies the same data, causing the first transaction to get a different result when reading that data again.

Solution: the query is still the unmodified value

Phantom read : after the transaction itself reads data within a range, another transaction inserts new data, causing the content read twice to be inconsistent
Query a specific row. Although the second time compared with the first time there is no extra newly inserted data, inserting the same data will fail (if there is missing data it is called Non repeatable read) Gap lock within row lock/Next key lock solution

Mysql/Mysql advanced > ^5cjxde Isolation level: read uncommitted<Read committed (dirty read)<Repeatable read<Serializable

Table lock

Table level S/X lock

Triggered by: DDL operation, drop table``alter table Inability to use an index may cause a row lock to escalate to a table lock

Manually triggered: Lock Tables t READ/WRITE

When executing complex queries such as aggregations and joins, an S lock may be placed on the entire table to ensure query consistency. When executing data definition language DDL operations such as creating, modifying, or dropping tables, an X lock is usually placed to ensure the safety and consistency of structural changes.

A table lock is simply not DML locking, but DDL blocking during the period DML, similarly DML blocking DDL

is generally not used, InnoDB Table level S locks and X locks just need to be understood

Intention lock (intention shared lock IS or intention exclusive lock IX)

The two can coexist; Insert intention lock(Row lock) This indicates that data wants to be inserted into a gap, unlike Intention lock

Triggered by: any isolation level, any read or write

It is just Identification lock , in order to coordinate Row lock and Table lock relationship, supporting the coexistence of both locks
When adding a X lock to a certain row in a table, other transactions do not know whether there is any X/S lockin the table and need to traverse the data one row at a time, which is inefficient, so IX/IS Intention locks are added to identify which row in the table has a X/S lock (Exclusive lock), so as to quickly determine

If there is a Read intention lock you can add a table level Read lock If there is a Write intention lock, table level Read write lock neither can be added, but row level Read write lock

Auto increment lock

Mysql or Mysql advanced > 3 Auto increment lock AUTO INC lock, just understand When a transaction needs to insert insert a new record containing an auto increment column, it first acquires the auto increment lock

• Metadata lock (MDL lock) Mysql/Mysql Advanced > Metadata lock MDL lock Mysql/Mysql Advanced > Metadata lock MDL lock MDL Read lock : Added when performing on a table insert delete update query operations, at this time it is not possible to perform structural changes MDL Write lock : Added when performing on a table structural changes operations, at this time it is not possible to perform insert delete update query Record lock : can implement two isolation levels : read uncommitted<read committed

add S lock
select ... FOR SHARE

generally add X lock,because when reading, for dirty reads,non repeatable reads,phantom reads, the lock is added directly X lock

add X lock
select ... FOR UPDATE 

8.0 : added`NOWAIT,SKIP LOCKED`syntax,can return immediately,if the queried row is already locked,then it will
	NOWAIT immediately return an error
	SKIP LOCKED immediately return without including the locked row

newly inserted data is protected by an implicit lock,ensuring it will not be seen by other transactions

Row lock

If you want a read operation to acquire a write lock, you must acquire it manually

Record lock (Record Locks）

Record lock is a row lock (read write lock of a row)

Mysql/Mysql Advanced > 2 write operations

Gap lock(Gap Locks)< > : Solves phantom reads

Gap lock/Next key lock Make Repeatable read isolation level avoid phantom reads to a certain extent

mysql 8.0 Default gap-lock is not disabled

it will add between the inserted record primary key value and the primary key value of the previous record in the mysql table Gap lock, as Open interval, Closed interval lock is Gap lock

Next key lock<= >=（Next-Key Locks） Closed space version

is to lock nonexistent data

The closed space version of a gap lock additionally locks a certain data row itself on top of the gap lock. The purpose of a gap lock is to prevent other transactions from inserting new rows between two existing values, but it does not block modification operations on existing rows. Next key lock, which is the closed space version of a gap lock, locks not only the gap but also the existing data rows within the gap, thereby providing stricter data protection

Failure to use an index causes row locks that originally only appeared in update statements to record lock on the basis of adding gap lock, causing other transactions to be unable to update data in the gap

Locking(7,+∞)is to prevent transaction A from queryingid<7 and then possibly also querying values greater than 7. If not locked, phantom reads may occur and id = 7 for update; Without an index, the entire table will be locked

Insert intention lock

Intention locks are table locks Insert intention locks are row locks

indicates that a transaction wants to insert data in a gap but is currently waiting, and the lock is generated during the wait

When a transaction inserts a record, it needs to determine whether the position has been locked by another transaction with a gap、next-key locks lock. If so, it waits,InnoDB The rule is that during the wait, a lock must also be generated in memory to indicate that a transaction wants to insert data into a gap but is currently waiting, and an insert intention lock, a type of Gap lock(not intention lock)

The first case adopted MVCC resolve

If one transaction holds an insert intention lock, other transactions attempting to insert into the same gap will be blocked

Page lock

The data of a table is organized into one or more pages, page lock is between table lock and row lock a type of lock granularity

In fact, InnoDB row locks mark lock information in data pages, so they can be regarded as finer control of page locks. The locked resources are more than row locks and less than table locks

How does MySQL add row level locks, lock degradation

The object of locking is the index Basic unit of lockingis next-key lock(left open right closed Basic locking rangeis the range of adjacent indexes, Index order and the previous record of the current record Basic locking order: index scan order, scanning starts from the starting position of the index

Lock degradation condition: unique index

next-key lock the locking object must be within the current query range records. Because the locking mechanism of InnoDB is based on records and gaps

unique indexequalityquery

will lock the primary key index. If it is an exact match on the index of the record, the next-key lock will degrade to a record lock (the reason is that in the scenario of a unique index equality query where the queried record exists, a record lock alone can also avoid the phantom read problem).

(Special case: if the exact match content does not exist, it will degrade togap lock, locking the interval where data before and after exist in the table)

unique index range query

If the number declared in the range does not exist in the table, the index of that record next-key next key lock degenerates into gap lock For other scanned records, add on the index of these records next-key lock

Cases

• For > < select * from user where id > 15 for update;

will add to >15 every interval of values adds next key lock, finally equivalent to (15,+∞]

select * from user where id < 5 for update; select * from user where id < 6 for update; InnoDB will start fromthe starting point of the indexposition to scan, until finding records that do not meet id < 6the condition found 1, 5, and the first non matching 10 start locking 2 next-key lock: respectively lock (-infinity, 1] and (1, 5] 1 gap lock: lock the gap in the range (5, 10). Note there is only a gap lock here, no record lock, because id=10 does not satisfy id < 6 condition.

1 and 5 are both records within the query range, need to add next-key lock

• For >= <= select * from user where id >= 15 for update;

the record itself matched by equality adds record lock, finally equivalent to [15,+∞]

select * from user where id <= 5 for update;

When performing range queries on a unique index (primary key index) with less than or less than or equal to, these two cases will cause the index next-key lock to degenerate into a gap lock:

1. Secondary index: if the query is based on a secondary index (non clustered index), it will first lock the secondary index, then lock the corresponding primary key index. Asnext key lock

transaction A : update name='xiaolin' where id =1 transaction B : update name='xiaolin' where id =5

If id there is a unique index, record lock, transactions A, B are not affected Assume ID is a secondary index and the primary key is ID2, then it will add next-key lock, transactions A, B block and affect - will add a next key lock on the id index for the record with id = 1, locking (-infinity, 1] this range (including the record and the gap). Through the back to table lookup, find id = 1 corresponds to ID2 = 100 (assumed), then on the primary key index, add a next key lock to the record with ID2 = 100, locking (-infinity, 100] (here it is assumed the primary key values are consecutive, actual situations are more complex)

Without a unique index it will do a full table scan, so it will add to all records next-key lock (record lock plus gap lock), equivalent to locking the entire table.

Then can an update statement with an index in its where clause avoid locking all records in the table? Not necessarily. The key depends on what the optimizer ultimately chooses during the execution of this statement, whether it isindex scan, orfull table scan, if it does a full table scan, it will lock the records of the entire table.

• Lock escalation

innoDB The engine does not have the problem of lock escalation, because it does not generate row locks based on each record, but manages locks based on each page accessed by each transaction.

Pessimistic lock

optimistic lock, pessimistic lock is a Design thought, the classification above is based on practical operation intermittent locks and row locks can both be classified as pessimistic locks

optimistic lock

Mysql default pessimistic locking mechanism, but supports optimistic locking mechanism

does not use the database own locking mechanism, but through the program version number T mechanism / CAS mechanism to achieve, there is no deadlock

java.util.concurrent.atomic The atomic variable classes under the package are one implementation of optimistic locking : CAS implementation

As long as there is a modification statement, the version increments by one UPDATE ... SET version=version+1 WHERE version=version Before each modification, check whether the version is consistent with the version read, if not it means another transaction has modified it, ensuring the data read has not been changed by others

version number mechanism / timestamp mechanism

Mysql / Mysql Advanced > ^p2oa6s

MySQL 8.0 comprehensive usageMVCC, locking mechanism and version number mechanism or CAS mechanism

MVCC

MVCC (Multiversion Concurrency Control), namely multi version concurrency control technology, since frequent locking and unlocking greatly affects performance, in order to avoid relying only on locks and reduce the use of locks, through the MVCC mechanism can reduce the use of row level locks usage, it makes most transaction engines that support row locks no longer simply use row locks for database concurrency control, instead combining database row locks with multiple versions of rows, requiring only a small overhead to achieve non locking read, thereby greatly improving the concurrency performance of the database system

For example, reading data does not add a read lock. In certain specific queries, such as those involving certain aggregate functions or subqueries, MySQL may choose to add locks instead of using snapshots.

under the default isolation level using MVCC+lock problems that still exist

snapshot read : no locking, it is possible to read historical data, if the serial isolation level snapshot read becomes current read, snapshot read provides a data snapshot through MVCC, does not add locks, allows other transactions to modify data at the same time. problem: inconsistency (possibly reading historical data)/幻读

current read : locking, for the latest data

MVCCbased on undo log and Read View undo log chain: associated with each transaction commit, equivalent to having various versions of the data at each stage

How to choose the needed version, according to the readview read view

Read view records the statistical values of the version chain, subsequent query processing does not need to traverse all version chains

1. creator_trx_id: create that Read View transaction ID。
2. min_trx_id: the smallest active transaction in the current database ID。
3. max_trx_id: the largest transaction in the current database ID。
4. m_ids: the active transactions ID set.

Each transaction creates its own Read view, including the current transaction own creator_trx_id, traverse undo log chain if in the undo log chain in transaction id＜min_trx_id, then before this transaction was generated, this transaction had already committed, transaction id＞max_trx_id, then it is generated by that transaction Readview operations of other transactions afterward, so it is not visible. transaction min_trx_id＜id＜max_trx_id & id not in m_ids(active transaction id), it indicates that the transaction has already been committed and can be read. transaction id=creator_trx_id is created by its own transaction and can be accessed directly

Therefore,Read View purpose is to help a transaction determine which data is visible when it is created, and by traversing undo log to achieve this.

(Design idea similar to: page directory, intention lock)

Mysql/Mysql Advanced > hidden columns DB_TRX_ID, transactionID , each modification will assign this transaction IDcopied to DB_TRX_ID； DB_ROLL_PTR, rollback pointer, pointing to the rollback segment undo log

MVCC can solvenon repeatable readandphantom read?

phantom read cannot non repeatable read can

read committed a new one is generated for each read Readview repeatable read only one is generated Readview, ensuring that other transactions data will not be read

operations

backup

1. first backed up the data of the user table:
2. Then a user initiated a purchase of a product:
3. Then back up the data of the product table. That is, between backing up the user table and the product table, a user purchased a product. In this case

the result of a regular backup is that the user balance in the user table was not deducted, while the stock of that product in the product table was reduced. If this backup file is later used to restore the database data, the user money was not reduced, but the stock was reduced, which means the user got a product for free.

add global lock: the entire database is read only, affecting business

Solution: use mysqldump --single-transaction ，MySQL start a transaction before backing up the database. It will create readview, does not affect reads and writes, and in this way neither the balance nor the stock will be reduced. This method only applies to storage engines that support transactions at the repeatable read isolation level. For engines such as MyISAM that do not support transactions, the global lock method must be used when backing up the database.

interview

《面试笔记》——MySQL终结篇（30问与答） (10_12_2023 21_44_09) ↗

为什么程序员对这8种SQL的写法零容忍 (09_12_2023 16_30_06) 1 ↗