MySQL - Advanced Application + optimization.

lyfGeek 2022-05-22 11:59:00 阅读数:811

mysqladvancedapplicationoptimization.optimization

MySQL senior .


List of articles


Linux(CentOS) install MySQL.

《MySQL Server install ~ CentOS.》


Indexes .

In a relational database , The index is a separate 、 A storage structure that physically sorts the values of one or more columns in a database table , It is a collection of one or more column values in a table and a corresponding list of logical pointers to the data pages in the table that physically identify these values . The function of index is equivalent to the catalogue of books , You can quickly find the required content according to the page number in the table of contents .
An index provides a pointer to a data value stored in a specified column of a table , Then sort the pointers according to the sort order you specify . The database uses indexes to find specific values , Then find the row containing the value in the order of the pointer . In this way, you can make the table corresponding to SQL Statements execute faster , Quick access to specific information in database tables .
When there are a lot of records in the table , To query a table , The first way to search information is full table search , It's to take all the records one by one , Compare with the query conditions one by one , Then return the records that meet the conditions , Doing so consumes a lot of database system time , And create a lot of disks I/O operation ; The second is to index the table , Then find the index value that matches the query condition in the index , Finally, by saving in the index ROWID( Equivalent to page number ) Quickly find the corresponding record in the table .
~ Baidu Encyclopedia .

Indexes (Index) Help MySQL Data structure for efficient data acquisition ( Orderly ). Out of data , The database also maintains a data structure that satisfies a specific search algorithm , These data structures are referenced in some way ( Point to ) data , In this way, advanced search algorithms can be implemented on these data structures .

  • advantage .
  • It's like a catalog index of books , Improve the efficiency of data retrieval , Reduce the IO cost .
  • Sort column data by index , Reduce the cost of data sorting , Reduce CPU Consumption of .
  • Inferiority .

The index is actually a table , The table holds the primary key and index fields , And point to the entity class in the record , So index columns also need to occupy space .
Although indexing greatly improves query efficiency , At the same time, it also reduces the speed of updating tables .eg. Do... On the watch INSERT、DELETE、UPDATE. Because when updating tables ,MySQL Not only to save data , Also save the index file and update the fields with index columns every time , Will adjust the index information after the key value changes due to the update .

 Insert picture description here


Index structure .

MySQL common 4 Species index .

B-Tree.

The most common index . Most indexes support B Tree index .

HASH.

Only Memory Engine support , The use scenario is simple .

R-tree.

The spatial index is MyISAM A special index type of the engine . Mainly used for geospatial data types , Usually used less , No special introduction .

Full-Text.

Full text index is also MyISAM A special index type of , It is mainly used for full text retrieval ,InnoDB from MySQL 5.6 Full text indexing is now supported .

Indexes InnoDB engine MyISAM engine MEMORY engine
B-Tree
HASH××
R-tree××
Full-Text5.6 Later versions support .×

What we usually call index , If not specified , All refer to B+ Trees ( Multiple search trees , It doesn't have to be a binary ,B-Tree Improved version ) Index of structural organization . Where the clustered index 、 Composite index 、 Prefix index 、 The only index defaults to B+Tree Indexes , Collectively referred to as index .


B-tree.

B-tree( Multiple search trees , It's not binary ).

A tree m Forked B-tree The features are as follows .

  • Each node can have at most m Child nodes .
  • There are restrictions on the keywords of each node and the number of child nodes you can have .
    Regulations m rank B-tree in , The root node has at least 2 Child nodes , Unless the root node is a leaf node .
  • All the leaf nodes are on the same layer .
  • Each non leaf node is composed of n individual key And n+1 Pointer composition , among [m/2]-1 <= n <= m-1.

With 5 fork B-tree For example .

m = 5.
2 <= n <= 4.
When n > 4 when , The intermediate node splits into the parent node , Split nodes on both sides .

Insert C N G A H E K Q M F W L T Z D P R X Y S. The evolution process is as follows .

 Insert picture description here
Compared to a binary tree ,B-tree More child nodes , Deeper ——> Efficient .


B+Tree.

B+Tree yes B-Tree Variants .

  • difference .
    n fork B+Tree Up to n individual key, and B-Tree Up to n-1 individual key.
    B+Tree The leaf node of saves all key Information , In accordance with the key In order of size .
    All non leaf nodes can be regarded as key The index part of .

 Insert picture description here
because B+Tree Only leaf nodes are saved key Information , Query any key All from root Go to the leaves . therefore B+Tree More stable query efficiency .


MySQL Medium B+Tree.

MySQL Index data structure for classic B+Tree optimized . In the original B+Tree On the basis of , Add a linked list pointer to adjacent leaf nodes , So we have a sequence pointer B+Tree, Improve the performance of interval access .

 Insert picture description here

Index classification .
  • Single value index .

That is, an index contains only a single column , A table can have multiple single valued indexes .

  • unique index .

The value of the index column must be unique , But you can have an empty value .

  • Composite index .

That is, an index contains multiple columns .


Index Syntax .

Indexes can be created when creating tables , You can also add new indexes at any time .

  • Table preparation .
CREATE SCHEMA `demo_01` DEFAULT CHARACTER SET utf8mb4 ;
use demo_01;
CREATE TABLE `demo_01`.`city` (
`city_id` INT NOT NULL AUTO_INCREMENT,
`city_name` VARCHAR(45) NOT NULL,
`country_id` INT NULL,
PRIMARY KEY (`city_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE `demo_01`.`country` (
`country_id` INT NOT NULL AUTO_INCREMENT,
`country_name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`country_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
INSERT INTO `demo_01`.`city` (`city_name`, `country_id`) VALUES (' Xi'an ', '1');
INSERT INTO `demo_01`.`city` (`city_name`, `country_id`) VALUES ('NewYork', '2');
INSERT INTO `demo_01`.`city` (`city_name`, `country_id`) VALUES (' Beijing ', '1');
INSERT INTO `demo_01`.`city` (`city_name`, `country_id`) VALUES (' Shanghai ', '1');
INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES ('1', 'China');
INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES ('2', 'America');
INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES ('3', 'Japan');
INSERT INTO `demo_01`.`country` (`country_id`, `country_name`) VALUES ('4', 'UK');

Create index .

The primary key is the index by default .

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[index_type]
ON tbl_name (key_part,...)
[index_option]
[algorithm_option | lock_option] ...
key_part:
col_name [(length)] [ASC | DESC]
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
}
index_type:
USING {
BTREE | HASH}
algorithm_option:
ALGORITHM [=] {
DEFAULT | INPLACE | COPY}
lock_option:
LOCK [=] {
DEFAULT | NONE | SHARED | EXCLUSIVE}

eg. by city In the table city_name Field creation index .

ALTER TABLE `demo_01`.`city`
ADD INDEX `idx_city_name` (`city_name` ASC);
;

or

create index idx_city_name on city(city_name);

Look at the index .
show index from city;
mysql> show index from city;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | city_id | A | 4 | NULL | NULL | | BTREE | | |
| city | 1 | idx_city_name | 1 | city_name | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
show index from city \G;
mysql> show index from city\G;
*************************** 1. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: city_id
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: city
Non_unique: 1
Key_name: idx_city_name
Seq_in_index: 1
Column_name: city_name
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
ERROR:
No query specified

Delete index .
ALTER TABLE `demo_01`.`city`
DROP INDEX `idx_city_name` ;
;

or

drop index idx_city_name on city;

ALTER command .
alter table -tb_name- add primary key (column_list);
-- Add a primary key . The index value must be unique , And cannot be NULL.
alter table -tb_name- add unique -index_name- (-column_list-);
-- The value of the index must be unique ( except NULL Outside .NULL There may be many times ).
alter table -tb_name- add index -index_name- (-column_list-);
-- Add a normal index . Index values can appear multiple times .
alter table -tb_name- add fulltext -index_name- (column_list);
-- Specify the index as FULLTEXT, For full-text search .
ALTER TABLE `demo_01`.`city`
ADD UNIQUE INDEX `idx_city_name` (`city_name` ASC);
;

Index design principles .
  • The query frequency is high , And a table with a large amount of data is indexed .

  • Selection of index fields . The best selected column should be from where Clause is extracted from the condition of clause . If where There are more combinations in clauses , Then choose the most commonly used 、 The combination of the best filtering Columns .

  • Try to use a unique index , The more distinguishable , The more efficient the index is .

  • Index can effectively improve the efficiency of query data , But the number of indexes is not the better . More indexes , The cost of maintaining the index naturally goes up . For inserting 、 to update 、 Delete etc. DML For tables that operate more frequently , Too many indexes , It's going to introduce quite a high maintenance cost , Reduce DML Efficiency of operation , Increase the corresponding time consumption . If the total length of the fields that make up the index is short , Then more index values can be stored in a given size of memory block , The corresponding can effectively improve MySQL Access to the index I/O efficiency .

  • Use the leftmost prefix ,N A composite index of columns , So it's equivalent to creating N An index , If you query where Clause uses the first few fields that make up the index , So this query SQL We can use composite index to improve query efficiency .

Create composite index .
CREATE INDEX idx_name_email_status ON tb_seller (name, email, status);
amount to
Yes name Created index .
Yes name,email Created index .
Yes name,email,status Created index .

The leftmost index : As long as the first field is included in the query , The query will use the index .


View .

View ~what.

View (View) It's a virtual existence table . The view does not actually exist in the database , Row and column data comes from tables used in queries that define views , And it's dynamically generated when using views . Generally speaking , The view is just a line SELECT The result set returned after the statement is executed . So when we create a view , The main work falls on this SQL On the query statement .

View VS An ordinary watch .

  • Simple .

Users of views do not need to care about the structure of the corresponding tables 、 Association and screening criteria , It is already the result set of filtered composite conditions for users .

  • Security .

Users of views can only access the result set they are allowed to query , Permission management of a table cannot be limited to a row or a column , But it can be realized simply by view .

  • Data independence .

Once the structure of the view is determined , It can shield the influence of table structure change on users , Adding columns to the source table has no effect on the view ; Source table change column name , You can modify the view to solve , No impact on visitors .


establish 、 Modify the view .
CREATE
[OR REPLACE]
[ALGORITHM = {
UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
SELECT
c.*, t.country_name
FROM
city c,
country t
WHERE
c.country_id = t.country_id

trouble ——> Use view .

  • Create view .
CREATE VIEW `view_city_country` AS
SELECT
c.*, t.country_name
FROM
city c,
country t
WHERE
c.country_id = t.country_id;
  • Use ( Virtual table ).
SELECT
*
FROM
view_city_country;
  • Modify the view .
update view_city_country set city_name = ' Xi'an City ' where city_id = 1;

What is updated is the base table data encapsulated in the view .

ALTER [algorithm = {UNDEFINED | MERGE | TEMPLATE}]
VIEW -view_name- [(column_list)]
AS select_statement
[with| CASCADED | LOCAL] CHECK OPTION]

Options .

WITH [CASCADED | LOCAL] CHECK OPTION Decide whether to allow the data to be updated so that the record no longer meets the view conditions .

LOCAL: You can update as long as you meet the conditions of this view .
CASCADED: All view conditions for this view must be met to update . Default .

-- The data in the original table is updated .
UPDATE view_city_country
SET
city_name = ' Xi'an City '
WHERE
city_id = 1;

Views are used to simplify query operations , It is not recommended to update .


View view .

from MySQL 5.1 Start , Use SHOW TABLES Command not only displays the name of the table , The name of the view is also displayed , There is no separate display view SHOW VIEWS command .

mysql> show tables;
+-------------------+
| Tables_in_demo_01 |
+-------------------+
| city |
| country |
| view_city_country |
+-------------------+
3 rows in set (0.00 sec)
mysql> show views;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'views' at line 1
mysql>

Again , In the use of SHOW TABLE STATUS When ordered , It can not only display the information of the table , At the same time, it can also display the information of the view .

mysql> show table status \G;
*************************** 1. row ***************************
Name: city
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 32768
Data_free: 0
Auto_increment: 5
Create_time: 2020-04-06 09:51:14
Update_time: 2020-04-06 12:38:03
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: country
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 5
Create_time: 2020-04-06 09:01:28
Update_time: 2020-04-06 09:03:31
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 3. row ***************************
Name: view_city_country
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql> show create view view_city_country;
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_city_country | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_city_country` AS select `c`.`city_id` AS `city_id`,`c`.`city_name` AS `city_name`,`c`.`country_id` AS `country_id`,`t`.`country_name` AS `country_name` from (`city` `c` join `country` `t`) where (`c`.`country_id` = `t`.`country_id`) | utf8mb4 | utf8mb4_general_ci |
+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

Delete view .
DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]
DROP VIEW city_country_view;

Stored procedures and functions .

what.

stored procedure (Stored Procedure) In a large database system , A group of SQL Statements set , It's stored in a database , Once compiled, it's permanent , The user specifies the name of the stored procedure and gives the parameters ( If the stored procedure has parameters ) To execute it. . Stored procedure is an important object in database . In the case of a huge amount of data, the use of stored procedures can achieve double speed efficiency .
~ Encyclopedias .

Stored procedures and functions are compiled in advance and stored in a database SQL Collection of statements , Calling stored procedures and functions can simplify a lot of work for application developers , Less data transfer between database and application server , It's good for improving the efficiency of data processing .

The difference between a stored procedure and a function is that the function must have a return value , And stored procedures don't have .

  • function : Is a process with a return value .
  • The process : Is a function with no return value .

Create stored procedure ~ create procedure ….
CREATE PROCEDURE procedure_name ([proc_parameter[, ...]])
begin
-- SQL sentence .
end;
CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
mysql> delimiter //
mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
BEGIN
SELECT COUNT(*) INTO cities FROM world.city
WHERE CountryCode = country;
END//
Query OK, 0 rows affected (0.01 sec)

eg.

mysql> create procedure pro_test01()
-> begin
-> select "hello mysql";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Report an error before you finish writing , Because MySQL in ,; The separator is , Indicates that the statement has been written .
First set the delimiter to non ;.

delimiter $

mysql> create procedure pro_test01()
-> begin
-> select 'hello mysql';
-> end$
Query OK, 0 rows affected (0.00 sec)

The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. See Section 24.1, “Defining Stored Programs”.


Calling stored procedure ~call.
mysql> call pro_test01();
-> $
+-------------+
| hello mysql |
+-------------+
| hello mysql |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

View stored procedures .
-- Inquire about db_name All stored procedures in the database .
mysql> select name from mysql.proc where db='demo_01'$
+------------+
| name |
+------------+
| pro_test01 |
+------------+
1 row in set (0.00 sec)
-- Query the status information of the stored procedure .
show PROCEDURE STATUS;
show PROCEDURE STATUS\G;
-- Query the definition of a stored procedure .
mysql> show create procedure pro_test01 \G;
*************************** 1. row ***************************
Procedure: pro_test01
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test01`()
begin
select 'hello mysql';
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

Delete stored procedure .
DROP {
PROCEDURE | FUNCTION} [IF EXISTS] sp_name

grammar .
Variable .
  • - declare.

adopt declare You can define a local variable , This variable can only be used in BEGIN…END In block .

DECLARE var_name [, var_name] ... type [DEFAULT value]

eg.

delimiter $
create procedure pro_test2()
begin
declare num int default 5;
SELECT CONCAT('num The value of is :', num);
end$
delimiter ;
call pro_test2()
  • - set~ Assign values to variables .
delimiter $
create procedure pro_test3()
begin
declare num int default 0;
set num = num - 10;
SELECT CONCAT('num The value of is :', num);
end$
delimiter ;
call pro_test3()
~
num The value of is :10
  • It can also be done through select … into Method assignment .
delimiter $
create procedure pro_test4()
begin
declare countnum int;
SELECT
COUNT(*)
INTO countnum FROM
city;
SELECT CONCAT('city The number of records in the table is :', countnum);
end$
delimiter ;
call pro_test4()

if conditional .
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

demand .

According to the defined height variable , Determine the type of body the current height belongs to .

180 And above ~~ Tall and tall .
170 ~ 180 ~~ Standard figure .
170 following ~~ Average figure .

delimiter $
create procedure testIF()
begin
declare height int default 175;
declare description varchar(50) default '';
if height > 180 then set description = " Tall and tall ";
elseif height >= 170 then set description = " Standard figure ";
else set description = ' Average figure ';
end if;
SELECT
CONCAT(' height ',
height,
' The corresponding figure type is :',
description);
end$
delimiter ;
call testIF();

Pass parameters .
create procedure procedure_name ([in / out / inout] Parameter name Parameter type )
  • in: Input . The caller needs to pass in the value . Default .
  • out: Output . This parameter is used as the return value .
  • inout: It can be used as an input parameter , It can also be used as an output parameter .
- in~ Input .

demand .

According to the defined height variable , Determine the type of body the current height belongs to .

delimiter $
create procedure testIN(IN height INT)
begin
declare description varchar(50) default '';
if height > 180 then set description = " Tall and tall ";
elseif height >= 170 then set description = " Standard figure ";
else set description = ' Average figure ';
end if;
SELECT
CONCAT(' height ',
height,
' The corresponding figure type is :',
description);
end$
delimiter ;
call testIN(188);

- out~ Output .

demand .

According to the defined height variable , Get the body type of the current height ( Return value ).

delimiter $
create procedure testOUT(IN height INT, out description varchar(10))
begin
if height > 180 then set description = " Tall and tall ";
elseif height >= 170 then set description = " Standard figure ";
else set description = ' Average figure ';
end if;
SELECT
CONCAT(' height ',
height,
' , The corresponding figure type is :',
description);
end$
delimiter ;
call testOUT(188, @description);-- @ ——> User session variables .
SELECT @description;

@ ——> User session variables .

@@——> System variables .

mysql> set name = 'geek';
ERROR 1193 (HY000): Unknown system variable 'name'
mysql> set @name = 'geek';
Query OK, 0 rows affected (0.00 sec)
mysql> select @name;
+-------+
| @name |
+-------+
| geek |
+-------+
1 row in set (0.00 sec)
mysql>

case structure .
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
delimiter $
-- IN Default , Don't write .
-- create procedure testCASE(IN mon INT)
create procedure testCASE(mon INT)
begin
declare result varchar(10);
case
when mon >= 1 and mon <= 3 then
set result = ' first quarter ';
when mon >= 4 and mon <= 6 then
set result = ' The second quarter ';
when mon >= 7 and mon <= 9 then
set result = ' The third quarter ';
when mon >= 10 and mon <= 12 then
set result = ' In the fourth quarter ';
end case;
SELECT
CONCAT(' The month of delivery is :',
mon,
', The result is :',
result) AS content;
end$
delimiter ;
call testCASE(3);

while loop .
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
delimiter $
create procedure pro_testWHILE(n INT)
begin
declare total int default 0;
declare num int default 1;
while num <= n do
set total = total - num;
set num = num - 1;
end while;
SELECT total;
end$
delimiter ;
call pro_testWHILE(100);

repeat loop .
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
delimiter $
create procedure pro_testREPEAT(n INT)
begin
declare total int default 0;
repeat
set total = total - n;
set n = n - 1;
until n = 0
end repeat;
SELECT total;
end$
delimiter ;
call pro_testREPEAT(100);

loop loop & leave sentence .
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LEAVE label
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 - 1;
IF p1 < 10 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END;
delimiter $
create procedure pro_testLOOP_LEAVE(n INT)
begin
declare total int default 0;
c: loop
set total = total - n;
set n = n - 1;
if n <= 0 then
leave c;
end if;
end loop c;
SELECT total;
end$
delimiter ;
call pro_testLOOP_LEAVE(100);

The cursor 、 cursor .

Cursors are data types used to store query result sets , In stored procedures and functions, you can use the cursor to cycle the result set . The use of cursors includes the declaration of cursors 、OPEN、FETCH and CLOSE.

  • Statement .
DECLARE cursor_name CURSOR FOR select_statement
  • OPEN.
OPEN cursor_name
  • FETCH.
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
  • CLOSE.
CLOSE cursor_name

Case study .
CREATE TABLE `demo_01`.`emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL COMMENT ' full name .',
`age` INT(11) NULL COMMENT ' Age .',
`salary` INT(11) NULL COMMENT ' salary .',
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
INSERT INTO `demo_01`.`emp` (`name`, `age`, `salary`) VALUES (' The Golden Lion ', '55', '3800');
INSERT INTO `demo_01`.`emp` (`name`, `age`, `salary`) VALUES (' White browed eagle king ', '60', '4000');
INSERT INTO `demo_01`.`emp` (`name`, `age`, `salary`) VALUES (' King of green winged bat ', '38', '2800');
INSERT INTO `demo_01`.`emp` (`name`, `age`, `salary`) VALUES (' Dragon King in purple shirt ', '42', '1800');
USE `demo_01`;
DROP procedure IF EXISTS `new_procedure`;
DELIMITER $$
USE `demo_01`$$
CREATE PROCEDURE `new_procedure` ()
BEGIN
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
fetch emp_result into e_id, e_name, e_age, e_salary;
SELECT
CONCAT('id = ',
e_id,
', name = ',
e_name,
', age = ',
e_age,
', The salary is :',
e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
SELECT
CONCAT('id = ',
e_id,
', name = ',
e_name,
', age = ',
e_age,
', The salary is :',
e_salary);
close emp_result;
END$$
DELIMITER ;

Possible problems .

Error Code: 1329 No data - zero rows fetched, selected, or processed

↓ ↓ ↓


loop .
CREATE DEFINER=`root`@`%` PROCEDURE `new_procedure`()
BEGIN
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare has_data int default 1;
declare emp_result cursor for select * from emp;
declare exit handler for not found set has_data = 0;
open emp_result;
fetch emp_result into e_id, e_name, e_age, e_salary;
SELECT
CONCAT('id = ',
e_id,
', name = ',
e_name,
', age = ',
e_age,
', The salary is :',
e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
SELECT
CONCAT('id = ',
e_id,
', name = ',
e_name,
', age = ',
e_age,
', The salary is :',
e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
SELECT
CONCAT('id = ',
e_id,
', name = ',
e_name,
', age = ',
e_age,
', The salary is :',
e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
SELECT
CONCAT('id = ',
e_id,
', name = ',
e_name,
', age = ',
e_age,
', The salary is :',
e_salary);
fetch emp_result into e_id, e_name, e_age, e_salary;
SELECT
CONCAT('id = ',
e_id,
', name = ',
e_name,
', age = ',
e_age,
', The salary is :',
e_salary);
close emp_result;
END

Storage function .

The storage function has a return value .

The stored procedure did not return a value . But it can go through IN OUT Output results .

CREATE [AGGREGATE] FUNCTION function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}
SONAME shared_library_name
CREATE FUNCTION `new_function` ()
RETURNS INTEGER
BEGIN
RETURN 1;
END
USE `demo_01`;
DROP function IF EXISTS `new_function`;
DELIMITER $$
USE `demo_01`$$
CREATE FUNCTION `new_function` (countryId INT)
RETURNS INT
BEGIN
DECLARE cnum INT;
SELECT
COUNT(*)
INTO cnum FROM
city
WHERE
country_id = countryId;
RETURN cnum;
END$$
DELIMITER ;
  • Use select call .
select demo_01.new_function(1);

trigger .

what.

Triggers are database objects related to tables , Referring to insert / update / delete Before or after , Trigger and execute the... Defined in the trigger SQL Statement set . This feature of the trigger can help the application in the database to ensure the integrity of the data court , logging , Data verification, etc .

Use the alias OLD and NEW To refer to the record content that changes in the trigger , This is similar to other databases . Now triggers only support row level triggering , Statement level triggering is not supported .

Trigger Type NEW and OLD Use
INSERT Type trigger NEW Indicates the data to be added or added .
UPDATE Type trigger OLD Represents the data before modification ,NEW Represents the data that will be or has been modified .
DELETE Type trigger OLD Data that will be or has been deleted .

establish .
CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW -- Line level triggers .
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

eg. Record by trigger emp Log of table data changes .
CREATE TABLE `demo_01`.`emp_logs` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`operation` VARCHAR(20) NOT NULL COMMENT ' Operation type .insert/update/delete.',
`operation_time` DATETIME NOT NULL COMMENT ' Operating time .',
`operation_id` INT(11) NOT NULL COMMENT ' Operation table id.',
`operation_params` VARCHAR(500) NULL COMMENT ' Operating parameters .',
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
  • establish insert Type of trigger , Complete logging when inserting data .
DROP TRIGGER IF EXISTS `demo_01`.`emp_AFTER_INSERT`;
DELIMITER $$
USE `demo_01`$$
CREATE DEFINER = CURRENT_USER TRIGGER `demo_01`.`emp_AFTER_INSERT` AFTER INSERT ON `emp` FOR EACH ROW
BEGIN
insert into emp_logs (id, operation, operation_time, operation_id, operation_params)
values (null, 'insert', now(), new.id, concat(' After inserting (id ~ ', new.id, ', name ~ ', new.name, ', age ~ ', new.age, ', salary ~ ', new.salary, ')'));
END$$
DELIMITER ;
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('5', ' The left emissary of light ', '30', '3500');
mysql> select * from emp_logs;
+----+-----------+---------------------+--------------+-----------------------------------------------------------+
| id | operation | operation_time | operation_id | operation_params |
+----+-----------+---------------------+--------------+-----------------------------------------------------------+
| 1 | insert | 2020-06-27 13:14:32 | 5 | After inserting (id: 5name: The left emissary of light , age: 30, salary: 3500) |
+----+-----------+---------------------+--------------+-----------------------------------------------------------+
1 row in set (0.00 sec)
  • establish update Type of trigger , Complete logging when modifying data .
DROP TRIGGER IF EXISTS `demo_01`.`emp_AFTER_UPDATE`;
DELIMITER $$
USE `demo_01`$$
CREATE DEFINER = CURRENT_USER TRIGGER `demo_01`.`emp_AFTER_UPDATE` AFTER UPDATE ON `emp` FOR EACH ROW
BEGIN
INSERT INTO emp_logs(id, operation, operation_time, operation_id, operation_params) VALUES
(null, 'update', now(), new.id,
concat(' Before the change (id ~ ', old.id,
', name ~ ', old.name,
', age ~ ', old.age,
', salary ~ ', old.salary,
'), After modification (id ~ ', new.id,
', name ~ ', new.id,
', name ~ ', new.name,
', age ~ ', new.age,
', salary ~ ', new.salary));
END$$
DELIMITER ;
UPDATE `demo_01`.`emp` SET `name` = 'o' WHERE (`id` = '6');
mysql> select * from emp_logs
-> ;
+----+-----------+---------------------+--------------+--------------------------------------------------------------------------------------------------------------------------+
| id | operation | operation_time | operation_id | operation_params |
+----+-----------+---------------------+--------------+--------------------------------------------------------------------------------------------------------------------------+
| 1 | insert | 2020-06-27 13:14:32 | 5 | After inserting (id: 5name: The left emissary of light , age: 30, salary: 3500) |
| 2 | update | 2020-06-27 13:24:34 | 3 | Before the change (id: 3name: King of green winged bat , age: 38, salary: 2800), After modification (3name: King of green winged bat , age: 39, salary: 2800 |
| 3 | insert | 2020-12-01 15:58:18 | 6 | After inserting (id ~ 6, name ~ o, age ~ 11, salary ~ 11|
| 4 | update | 2020-12-01 16:59:12 | 6 | Before the change (id ~ 6, name ~ o, age ~ 11, salary ~ 11),
After modification (id ~ 6, name ~ 6, name ~ o, age ~ 11, salary ~ 11 |
+----+-----------+---------------------+--------------+--------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Delete .
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

see .
SHOW TRIGGERS
[{
FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
mysql> show triggers;
+------------------+--------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+
| Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation |
+------------------+--------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+
| emp_AFTER_INSERT | INSERT | emp | BEGIN
INSERT INTO emp_logs (id, operation, operation_time, operation_id, operation_params) values (NULL, 'insert', now(), new.id, concat(' After inserting (id: ', new.id, 'name: ', new.name, ', age: ', new.age, ', salary: ', new.salary, ')'));
END | AFTER | 2020-06-27 13:14:05.60 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | [email protected]% | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| emp_AFTER_UPDATE | UPDATE | emp | BEGIN
INSERT INTO emp_logs (id, operation, operation_time, operation_id, operation_params) values (NULL, 'update', now(), new.id, concat(' Before the change (id: ', old.id, 'name: ', old.name, ', age: ', old.age, ', salary: ', old.salary, '), After modification (', new.id, 'name: ', new.name, ', age: ', new.age, ', salary: ', new.salary));
END | AFTER | 2020-06-27 13:23:46.47 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | [email protected]% | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+------------------+--------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)
mysql>
mysql> show triggers\G;
*************************** 1. row ***************************
Trigger: emp_AFTER_INSERT
Event: INSERT
Table: emp
Statement: BEGIN
INSERT INTO emp_logs (id, operation, operation_time, operation_id, operation_params) values (NULL, 'insert', now(), new.id, concat(' After inserting (id: ', new.id, 'name: ', new.name, ', age: ', new.age, ', salary: ', new.salary, ')'));
END
Timing: AFTER
Created: 2020-06-27 13:14:05.60
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: [email protected]%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
*************************** 2. row ***************************
Trigger: emp_AFTER_UPDATE
Event: UPDATE
Table: emp
Statement: BEGIN
INSERT INTO emp_logs (id, operation, operation_time, operation_id, operation_params) values (NULL, 'update', now(), new.id, concat(' Before the change (id: ', old.id, 'name: ', old.name, ', age: ', old.age, ', salary: ', old.salary, '), After modification (', new.id, 'name: ', new.name, ', age: ', new.age, ', salary: ', new.salary));
END
Timing: AFTER
Created: 2020-06-27 13:23:46.47
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: [email protected]%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql>

MySQL Architecture .


Storage engine .

Unlike most databases ,MySQL There's a storage engine concept in , We can choose the best storage engine for different storage requirements .

The storage engine is to store data , Index , Update query data and so on . The storage engine is table based , Not based on . So a storage engine can also be called a table type .

Oracle、sqlServer And other databases have only one kind of storage .MySQL Provides plug-in storage engine architecture . therefore MySQL There are multiple storage engines , You can use the engine as needed , Or write a storage engine .

MySQL 5.0 Supported storage engines include :lnnoDB、MylSAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED etc. , among InnoDB and BDB Provide transaction safety watch , Other storage engines are non transactional security tables .

It can be done by command show engines, To query the storage engine supported by the current database .

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql>
mysql> show variables like '%engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.02 sec)

contrast .
characteristic InnoDBMyISAMMEMORYMERGENDB
Storage limits 64 TB Yes Yes No, Yes
Transaction security Support
Locking mechanism Support ( Suitable for high concurrency ) Table locks Table locks Table locks Row lock
B Tree index Support Support Support Support Support
Hash index Support
Full-text index Support (5.6 After the version ) Support
Cluster index Support
Data index Support Support Support
The index buffer Support Support Support Support Support
Data can be compressed Support
Space use high low N/A low low
Memory usage high low secondary low high
Batch insertion speed low high high high high
Support foreign keys Support

InnoDB.
Business .
CREATE SCHEMA `demo02` DEFAULT CHARACTER SET utf8 ;
CREATE TABLE `demo02`.`goods_innodb` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO `demo02`.`goods_innodb` (`name`) VALUES ('Mate20');

MySQL Transactions are automatically committed by default . Repeatable .

If explicitly start transaction;

Must be submitted manually commit.


Foreign keys .
CREATE TABLE `demo02`.`country_innodb` (
`country_id` INT NOT NULL AUTO_INCREMENT,
`country_name` VARCHAR(100) NOT NULL,
PRIMARY KEY (`country_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE `demo02`.`city_innodb` (
`city_id` INT NOT NULL AUTO_INCREMENT,
`city_name` VARCHAR(45) NOT NULL,
`country_id` INT NOT NULL,
PRIMARY KEY (`city_id`),
INDEX `idx_fk_country_id` (`country_id` ASC),
CONSTRAINT `fk_city_country`
FOREIGN KEY (`country_id`)
REFERENCES `demo02`.`country_innodb` (`country_id`)
ON DELETE RESTRICT
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
mysql> desc country_innodb;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| country_id | int(11) | NO | PRI | NULL | auto_increment |
| country_name | varchar(100) | NO | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
mysql> desc city_innodb;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| city_id | int(11) | NO | PRI | NULL | auto_increment |
| city_name | varchar(50) | NO | | NULL | |
| country_id | int(11) | NO | MUL | NULL | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
INSERT INTO `demo02`.`country_innodb` (`country_name`) VALUES ('China');
INSERT INTO `demo02`.`country_innodb` (`country_name`) VALUES ('America');
INSERT INTO `demo02`.`country_innodb` (`country_name`) VALUES ('Japan');
INSERT INTO `demo02`.`city_innodb` (`city_name`, `country_id`) VALUES ('Xian', '1');
INSERT INTO `demo02`.`city_innodb` (`city_name`, `country_id`) VALUES ('NewYork', '2');
INSERT INTO `demo02`.`city_innodb` (`city_name`, `country_id`) VALUES ('BeiJing', '1');
  • ON DELETE RESTRICT
    When deleting master table data , If there are related records , Don't delete .
    ON UPDATE CASCADE
    When updating the main table , If the child table has associated records , Update sub table records .
Executing:
DELETE FROM `demo02`.`country_innodb` WHERE (`country_id` = '2');
ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails (`demo02`.`city_innodb`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON UPDATE CASCADE)
SQL Statement:
DELETE FROM `demo02`.`country_innodb` WHERE (`country_id` = '2')
Operation failed: There was an error while applying the SQL script to the database.
UPDATE `demo02`.`country_innodb` SET `country_id` = '100' WHERE (`country_id` = '1');

 Insert picture description here

[[email protected] ~]$ sudo ls /var/lib/mysql/
192-slow.log demo_01 ibtmp1 public_key.pem
auto.cnf demo02 mysql sakila
ca-key.pem ib_buffer_pool mysql.sock server-cert.pem
ca.pem ibdata1 mysql.sock.lock server-key.pem
client-cert.pem ib_logfile0 performance_schema sys
client-key.pem ib_logfile1 private_key.pem testdb
[[email protected] ~]$ sudo ls -l /var/lib/mysql/demo02
total 344
-rw-r-----. 1 mysql mysql 8648 Jun 27 11:48 city_innodb.frm
-rw-r-----. 1 mysql mysql 114688 Jun 27 12:03 city_innodb.ibd
-rw-r-----. 1 mysql mysql 8618 Jun 27 11:43 country_innodb.frm
-rw-r-----. 1 mysql mysql 98304 Jun 27 12:03 country_innodb.ibd
-rw-r-----. 1 mysql mysql 61 Jun 27 11:38 db.opt
-rw-r-----. 1 mysql mysql 8586 Jun 27 11:39 goods_innodb.frm
-rw-r-----. 1 mysql mysql 98304 Jun 27 11:40 goods_innodb.ibd
[[email protected] ~]$
  • .frm ~ Table structure .
  • .ibd ~ Data and index .

MyISAM ~ Unsupported transaction .
CREATE TABLE `demo02`.`goods_MyISAM` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

start transaction; You can also query without submitting .( No transaction ).

[[email protected] ~]$ sudo ls -l /var/lib/mysql/demo02
[sudo] password for geek:
total 360
-rw-r-----. 1 mysql mysql 8648 Jun 27 11:48 city_innodb.frm
-rw-r-----. 1 mysql mysql 114688 Jun 27 12:03 city_innodb.ibd
-rw-r-----. 1 mysql mysql 8618 Jun 27 11:43 country_innodb.frm
-rw-r-----. 1 mysql mysql 98304 Jun 27 12:03 country_innodb.ibd
-rw-r-----. 1 mysql mysql 61 Jun 27 11:38 db.opt
-rw-r-----. 1 mysql mysql 8586 Jun 27 11:39 goods_innodb.frm
-rw-r-----. 1 mysql mysql 98304 Jun 27 11:40 goods_innodb.ibd
-rw-r-----. 1 mysql mysql 8586 Jun 27 12:18 goods_MyISAM.frm
-rw-r-----. 1 mysql mysql 0 Jun 27 12:18 goods_MyISAM.MYD
-rw-r-----. 1 mysql mysql 1024 Jun 27 12:18 goods_MyISAM.MYI
  • .MYD(MYData) ~ Table data .
  • .MYI(MYIndex) ~ Indexes .

MEMORY.

Memory The storage engine stores the table data in memory . Every MEMORY The table actually corresponds to a disk file , Format .frm , Only the structure of the table is stored in this file , And its data files , Are stored in memory , This is conducive to the rapid processing of data , Improve the efficiency of the whole watch .MEMORY Table access of type is very fast , because ta Is stored in memory , And by default HASH Indexes . But once the service is shut down , The data in the table will be lost .


MERGE.

MERGE The storage engine is a set of MyISAM Combination of tables , these MyISAM Tables must be exactly the same structure ,MERGE The table itself does not store data , Yes MERGE Types of tables can be queried 、 to update 、 Delete operation , These operations are actually internal MylSAM Table .

about MERGE Insert operation of type table , It's through INSERT-METHOD Clause defines the inserted table , There can be 3 Different values , Use FIRST or LAST Value so that the insert operation is applied to the first or last table accordingly , Do not define this clause or define it as NO, That means you can't do this MERGE The table performs the fit operation .

It can be done to MERGE table DROP operation , But this operation just deletes MERGE The definition of the table , It has no effect on the internal table .


MyISAM & MERGE.

Create three test tables , The first two are MyISAM engine ,payment_2020, payment_2021.payment_all It's from the first two tables MERGE surface .

CREATE TABLE `demo02`.`order_2020` (
`order_id` INT NOT NULL,
`order_money` DOUBLE(10,2) NULL,
`order_address` VARCHAR(45) NULL,
PRIMARY KEY (`order_id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
CREATE TABLE `demo02`.`order_2021` (
`order_id` INT NOT NULL,
`order_money` DOUBLE(10,2) NULL,
`order_address` VARCHAR(45) NULL,
PRIMARY KEY (`order_id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
CREATE TABLE `demo02`.`order_all` (
`order_id` INT NOT NULL,
`order_money` DOUBLE(10,2) NULL,
`order_address` VARCHAR(45) NULL,
PRIMARY KEY (`order_id`))
ENGINE = MRG_MyISAM
DEFAULT CHARACTER SET = utf8
INSERT_METHOD = LAST
UNION = (order_2020, order_2021);
INSERT INTO `demo02`.`order_2020` (`order_id`, `order_money`, `order_address`) VALUES ('1', '100', ' Beijing ');
INSERT INTO `demo02`.`order_2020` (`order_id`, `order_money`, `order_address`) VALUES ('2', '100', ' Shanghai ');
INSERT INTO `demo02`.`order_2021` (`order_id`, `order_money`, `order_address`) VALUES ('10', '200', ' Beijing ');
INSERT INTO `demo02`.`order_2021` (`order_id`, `order_money`, `order_address`) VALUES ('11', '200', ' Shanghai ');

order_all Data in .

 Insert picture description here


Choice of storage engine .

When choosing a storage engine , A suitable storage engine should be selected according to the characteristics of the system . For complex applications , You can also select a variety of storage engines to combine according to the actual situation . Here are some common storage engine usage environments .

  • lnnoDB: yes MySQL The default storage engine for , For transactional applications , Support foreign keys . If the application has higher requirements for transaction integrity , Data consistency is required under concurrent conditions , Data operations in addition to inserts and queries , It also contains a lot of updates 、 Delete operation . that InnoDB The storage engine is a better choice . InnoDB The storage engine can effectively reduce the lock caused by deletion and update , It also ensures the complete commit and rollback of the transaction , For similar billing system or financial system and other data accuracy requirements of the system ,InnoDB Is the most appropriate choice .
  • MylSAM: If the application is based on read operation and insert operation , There are very few update and delete operations , And the integrity of the transaction 、 Concurrency requirements are not very high , So choosing this storage engine is very suitable .
  • MEMORY: Save all data in RAM in , In an environment where you want to quickly locate records and other similar data , Can provide extremely fast access to .MEMORY The drawback is that there is a limit on the size of the table . Too large tables cannot be cached in memory , The second is to ensure that the data in the table can be recovered , After the database stops abnormally, the data in the table can be recovered .MEMORY Tables are usually used to update small tables that are less frequent , To quickly get access to results .
  • MERGE: Used to equate a series of MylSAM Put together in a logical way , And refer to them as an object .MERGE The advantage of the table is that it can break through the single from MyISAM Table size limit , And by distributing different tables on multiple disks . Can effectively improve MERGE Table access efficiency . It's important for storage such as data warehousing VLDB The environment is perfect .

Optimize SQL step .

In the process of application development , Due to the small amount of data in the initial stage , Kaiyou staff write SQL We should pay more attention to the realization of function when we make a statement , But when the application system goes online , With the rapid growth of production data , quite a lot SQL Statements begin to show performance problems , The impact on production is also greater and greater . At this point, these problems SQL Statement becomes the bottleneck of the whole system performance , So we must be right ta We optimize .

When faced with a person who has SQL Performance issues with the database , Where should we start to make a systematic analysis , Enables the problem to be located as soon as possible SQL And solve the problem as soon as possible .


see SQL Frequency of execution .

MySQL After successful client connection , adopt SHOW [GLOBAL | SESSION] STATUS Command can provide server status information .SHOW [GLOBAL | SESSION] STATUS You can add parameters to the root “session” perhaps “global” To display session level ( Current connection ) Statistical results and global level ( Since the database was last started ) The statistics of . If you don't write , The default parameter used is “session”.

The following command shows the current session The values of all statistical parameters in .

Current database .

mysql> show status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |
| Com_insert | 0 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 5 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
+---------------+-------+
10 rows in set (0.01 sec)

overall situation .

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 10 |
| Com_delete | 7 |
| Com_insert | 26 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 137 |
| Com_signal | 0 |
| Com_update | 12 |
| Com_xa_end | 0 |
+---------------+-------+
10 rows in set (0.00 sec)

Innodb Table data of type .

mysql> show global status like 'Innodb_rows_%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Innodb_rows_deleted | 4 |
| Innodb_rows_inserted | 1750 |
| Innodb_rows_read | 1858 |
| Innodb_rows_updated | 14 |
+----------------------+-------+
4 rows in set (0.00 sec)

Locate inefficient SQL sentence .
Slow query log .

There are two ways to locate the less efficient SQL sentence .

  • Slow query log : Slow query logs to locate those that are less efficient SQL sentence , use --log-slow-queries[=file-name] When the option starts ,mysqld Write a containing all execution events more than long_query_time Of a second SQL Statement log file .

  • show processlist. The slow query log is not recorded until after the end of the query , So when the application reflects the problem of execution efficiency, the query of slow query log cannot locate the problem , have access to show processlist Command to view the current MySQL Thread in progress , Including the state of the thread 、 Lock the watch, etc , Can view... In real time SQL Implementation of , At the same time, some lock table operations are optimized .( real time ).

mysql> show processlist;
+----+------+---------------------+---------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+---------+---------+------+----------+------------------+
| 7 | root | localhost | demo_01 | Query | 0 | starting | show processlist |
| 10 | root | 192.168.142.1:49414 | demo_01 | Sleep | 463 | | NULL |
| 11 | root | 192.168.142.1:49415 | demo_01 | Sleep | 463 | | NULL |
+----+------+---------------------+---------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

If it is a slow query ,State yes Sending data.

copying to tmp table
sorting result
sending data


explain Analysis execution plan .

Through the above steps, we can find the words with low efficiency SQL After the statement , Can pass EXPLAIN perhaps DES Command acquisition MySQL How to execute SELECT Statement information , Included in SELECT How tables are joined and the order in which they are joined during statement execution .

mysql> explain SELECT * FROM demo_01.emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • id.
    select The serial number of the query . It's a set of numbers , Represents execution in query select Clause or the order of the operation table .
  • select_type.
    Express SELECT The type of , Common values are SIMPLE( A simple watch , That is, no table join or subquery is used )、PRIMARY( Main inspection room , That is, the outer query )、UNION(UNION The second or subsequent query statement in )、SUBQUERY( First in subquery SELECT) etc. .
  • table.
    Output result set table .
  • type.
    Indicates the connection type of the table , The connection types with good to poor performance are system -> const -> eq_ref -> ref -> ref_or_null -> index_merge -> index_subquery -> range -> index -> all.
  • possible keys.
    The index that may be used when querying .
  • key.
    Actual index used .
  • key_len.
    Length of index field .
  • rows.
    Number of scan lines .
  • extra.
    Description and description of the implementation .
  • table
    The data in this row is about which table .
  • type
    This is an important column , Shows what type of connection is used . The best to worst connection type is const、eq-reg、ref、range、index and ALL.
  • posible_keys
    Indexes that may be applied to this table . If it is empty , There is no possible index .
  • key
    Actual index used . If NULL, No index is used .
  • key_len
    Length of index used . Without losing accuracy , The shorter the length, the better .
  • ref
    Shows which column of the index is used , If possible , It's a constant .
  • rows
    MySql The number of rows that must be checked to return the requested data .
CREATE TABLE `demo_01`.`t_role` (
`id` VARCHAR(32) NOT NULL,
`role_name` VARCHAR(255) DEFAULT NULL,
`role_code` VARCHAR(255) DEFAULT NULL,
`description` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `unique_role_name` (`role_name` ASC)
) ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
CREATE TABLE `t_user` (
`id` varchar(32) NOT NULL,
`username` varchar(45) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `demo_01`.`user_role` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR(32) NULL DEFAULT NULL,
`role_id` VARCHAR(32) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `fk_ur_role_id_idx` (`role_id` ASC),
INDEX `fk_ur_user_id_idx` (`user_id` ASC),
CONSTRAINT `fk_ur_role_id`
FOREIGN KEY (`role_id`)
REFERENCES `demo_01`.`t_role` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_ur_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `demo_01`.`t_user` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('1', 'super', 'super', ' Super administrator ');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('2', 'admin', 'admin', ' System administrator ');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('3', 'geek', 'geek', 'test');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('4', 'stu1', 'stu1', ' Student 1');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('5', 'stu2', 'stu2', ' Student 2');
INSERT INTO `demo_01`.`t_user` (`id`, `username`, `password`, `name`) VALUES ('6', 't1', 't1', ' teacher 1');
INSERT INTO `demo_01`.`t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ('5', ' Student ', 'student', ' Student ');
INSERT INTO `demo_01`.`t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ('7', ' teacher ', 'teacher', ' teacher ');
INSERT INTO `demo_01`.`t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ('8', ' Teaching administrator ', 'teachmanager', ' Teaching administrator ');
INSERT INTO `demo_01`.`t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ('9', ' Administrators ', 'admin', ' Administrators ');
INSERT INTO `demo_01`.`t_role` (`id`, `role_name`, `role_code`, `description`) VALUES ('10', ' Super administrator ', 'super', ' Super administrator ');
INSERT INTO `demo_01`.`user_role` (`user_id`, `role_id`) VALUES ('1', '5');
INSERT INTO `demo_01`.`user_role` (`user_id`, `role_id`) VALUES ('1', '7');
INSERT INTO `demo_01`.`user_role` (`user_id`, `role_id`) VALUES ('2', '8');
INSERT INTO `demo_01`.`user_role` (`user_id`, `role_id`) VALUES ('3', '9');
INSERT INTO `demo_01`.`user_role` (`user_id`, `role_id`) VALUES ('4', '8');
INSERT INTO `demo_01`.`user_role` (`user_id`, `role_id`) VALUES ('5', '10');
mysql> select * from t_user;
+----+----------+----------+-----------------+
| id | username | password | name |
+----+----------+----------+-----------------+
| 1 | super | super | Super administrator |
| 2 | admin | admin | System administrator |
| 3 | geek | geek | test |
| 4 | stu1 | stu1 | Student 1 |
| 5 | stu2 | stu2 | Student 2 |
| 6 | t1 | t1 | teacher 1 |
+----+----------+----------+-----------------+
6 rows in set (0.00 sec)
mysql> select * from t_role;
+----+-----------------+--------------+-----------------+
| id | role_name | role_code | description |
+----+-----------------+--------------+-----------------+
| 10 | Super administrator | super | Super administrator |
| 5 | Student | student | Student |
| 7 | teacher | teacher | teacher |
| 8 | Teaching administrator | teachmanager | Teaching administrator |
| 9 | Administrators | admin | Administrators |
+----+-----------------+--------------+-----------------+
5 rows in set (0.00 sec)
mysql> select * from user_role;
+----+---------+---------+
| id | user_id | role_id |
+----+---------+---------+
| 1 | 1 | 5 |
| 2 | 1 | 7 |
| 3 | 2 | 8 |
| 4 | 3 | 9 |
| 5 | 4 | 8 |
| 6 | 5 | 10 |
+----+---------+---------+
6 rows in set (0.00 sec)
mysql>

id.

id The fields are select The serial number of the query . It's a set of numbers , Represents execution in query select Clause or the order of the operation table .

  • id The same means that the order in which tables are loaded is from top to bottom .
mysql> explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id;
+----+-------------+-------+------------+--------+-------------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | r | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL |
| 1 | SIMPLE | ur | NULL | ALL | fk_ur_role_id_idx,fk_ur_user_id_idx | NULL | NULL | NULL | 6 | 20.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | u | NULL | eq_ref | PRIMARY | PRIMARY | 98 | demo_01.ur.user_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+-------------------------------------+---------+---------+--------------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
  • id Different .id The bigger the value is. , The higher the priority , The first to be executed .
mysql> explain select * from t_role where id = (select role_id from user_role where user_id = (select id from t_user where username = 'stu1'));
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | t_role | NULL | const | PRIMARY | PRIMARY | 98 | const | 1 | 100.00 | NULL |
| 2 | SUBQUERY | user_role | NULL | ref | fk_ur_user_id_idx | fk_ur_user_id_idx | 99 | const | 1 | 100.00 | Using where |
| 3 | SUBQUERY | t_user | NULL | const | unique_user_username | unique_user_username | 137 | const | 1 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
  • id It's the same , There are different , At the same time .id The same can be thought of as a group , From top to bottom . In all groups ,id The greater the value of , The higher the priority , Execute first .
mysql> EXPLAIN SELECT * FROM t_role r, (SELECT * FROM user_role ur WHERE ur.user_id = '2') a WHERE r.id = a.role_id;
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------+------+----------+-------------+
| 1 | SIMPLE | ur | NULL | ref | fk_ur_role_id_idx,fk_ur_user_id_idx | fk_ur_user_id_idx | 99 | const | 1 | 100.00 | Using where |
| 1 | SIMPLE | r | NULL | eq_ref | PRIMARY | PRIMARY | 98 | demo_01.ur.role_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+-------------------------------------+-------------------+---------+--------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

select_type.
  • simple.
    ordinary select Inquire about , The query does not contain subqueries or UNION.
mysql> explain select * from t_user;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • primary.
    If the query contains any complex subqueries , The outermost query is marked with this identifier .
mysql> explain select * from t_user where id = (select id from user_role where role_id = '9');
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | t_user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 6 | 16.67 | Using where |
| 2 | SUBQUERY | user_role | NULL | ref | fk_ur_role_id_idx | fk_ur_role_id_idx | 99 | const | 1 | 100.00 | Using index |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
2 rows in set, 3 warnings (0.00 sec)
  • subquery.
    stay select or where The list contains subqueries .
mysql> explain select * from t_user where id = (select id from user_role where role_id = '9');
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | t_user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 6 | 16.67 | Using where |
| 2 | SUBQUERY | user_role | NULL | ref | fk_ur_role_id_idx | fk_ur_role_id_idx | 99 | const | 1 | 100.00 | Using index |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
2 rows in set, 3 warnings (0.00 sec)
  • derived.
    stay from Subqueries included in the list , Marked as DERIVED.MySQL These subqueries will be executed recursively , Put the results in the provisional table .
mysql> select * from (select * from t_user where id in ('1', '2'));
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select a.* from (select * from t_user where id in ('1', '2')) a;
+----+----------+----------+-----------------+
| id | username | password | name |
+----+----------+----------+-----------------+
| 1 | super | super | Super administrator |
| 2 | admin | admin | System administrator |
+----+----------+----------+-----------------+
2 rows in set (0.00 sec)
mysql> explain select a.* from (select * from t_user where id in ('1', '2')) a;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | range | PRIMARY | PRIMARY | 98 | NULL | 2 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
  • union.
    If the second select Appear in the union after , Then it is marked with union. if union Included in from Clause , Outer layer select Will be marked as derived.
  • union result.
    from union Table to get the result select.
mysql> explain select * from t_user where id = '1' union select * from t_user where id = '2';
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | t_user | NULL | const | PRIMARY | PRIMARY | 98 | const | 1 | 100.00 | NULL |
| 2 | UNION | t_user | NULL | const | PRIMARY | PRIMARY | 98 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

table.

Show which table this row of data is about .


type.

type It shows the type of access , Is a more important indicator .

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all.

type meaning
NULLMySQL Don't access any tables 、 Indexes , Direct return .
system There is only one line in the table ( It's equal to the system table ), This is a const Special case of type . In general, it will not appear .
const Indicates that it is found through index once ,const For comparison primary key perhaps unique Indexes . Because only one line of data is matched , So soon . For example, place the primary key in where In the list ,MySQL You can convert the query to a constant .const At will “ Primary key ” or “ only ” All parts of the index are compared with constants .
eq-ref similar ref, The difference is that you use a unique index , Use the association query of the main spring , There is only one record found by association . Common in primary key or unique index scan .
ref Non unique index scan . Returns all rows that match a single value . In essence, it is also an index access , Returns all rows that match a single value ( Multiple ).
range Retrieve only rows returned for a given , An index is used to select the row .where After that between,<,>,in Wait for the operation .
index And ALL The difference is index Type just traverses the index tree , Often than ALL fast ,ALL It's traversing data files .
all Will traverse the entire table to find the matching rows .
mysql> explain select now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

const | Indicates that it is found through index once ,const For comparison primary key perhaps unique Indexes . Because only one line of data is matched , So soon . For example, place the primary key in where In the list ,MySQL You can convert the query to a constant .const At will “ Primary key ” or “ only ” All parts of the index are compared with constants .

mysql> explain select * from t_user where username = 'stu1';
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_user | NULL | const | unique_user_username | unique_user_username | 137 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

key.
  • possible_keys.
    Possible indexes , One or more .

  • key.
    Actual index used , If NULL, There is no index .

  • key_len.
    The number of bytes used in the index , This value is the maximum possible length of the index field , It's not the actual length , Without losing accuracy , The shorter the length, the better .


rows.

Number of scan lines .

If index is used , Scan only one line . Otherwise, scan all lines .

mysql> explain select * from t_user where name = 'a';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> create index idx_user_name on t_user(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t_user where name = 'a';
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_user | NULL | ref | idx_user_name | idx_user_name | 137 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

extra.

Other additional execution plan information . Show in this column .

extra meaning
using filesortMySQL Will use an external index to sort the data , Instead of reading in the order of the indexes in the table , be called “ File sorting ”, Low efficiency .
using temporary Temporary tables are used to save intermediate results .MySQL Use temporary tables when sorting query results . Common in order by and group by, Low efficiency .
using index It means corresponding select The operation uses an override index , Avoid accessing table data rows , Good efficiency .
-- solve : to name Index .
mysql> explain select * from t_user order by username;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | t_user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
-- It's indexed ,select * It doesn't work , want select name.
mysql> explain select username from t_user order by username;
+----+-------------+--------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | index | NULL | unique_user_username | 137 | NULL | 6 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+----------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t_user where name = 'a';
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_user | NULL | ref | idx_user_name | idx_user_name | 137 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select name from t_user where name = 'a';
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | ref | idx_user_name | idx_user_name | 137 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------------+

show profile analysis sql.

MySQL from 5.0.37 The version starts with show profiles and show profile Statement support . Help us understand where time is spent .

  • See if... Is supported .
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
  • Turn on .1 Turn on .0 close .
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

The statement executed and the time it takes will be recorded .

see sql Time consuming .

mysql> show profiles;
+----------+------------+----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------+
| 1 | 0.00024250 | select @@profiling |
| 2 | 0.00014950 | SELECT DATABASE() |
| 3 | 0.00023050 | show databases |
| 4 | 0.00010450 | show tables |
| 5 | 0.00025275 | select * from t_user |
| 6 | 0.00016300 | select count(*) from t_user |
| 7 | 0.00023950 | show tables |
| 8 | 0.00028950 | select count(*) from city_innodb |
+----------+------------+----------------------------------+
8 rows in set, 1 warning (0.00 sec)
  • Further details .
mysql> show profile for query 8;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000055 |
| checking permissions | 0.000005 |
| Opening tables | 0.000016 |
| init | 0.000044 |
| System lock | 0.000009 |
| optimizing | 0.000004 |
| statistics | 0.000011 |
| preparing | 0.000009 |
| executing | 0.000002 |
| Sending data | 0.000101 |
| end | 0.000006 |
| query end | 0.000006 |
| closing tables | 0.000005 |
| freeing items | 0.000009 |
| cleaning up | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

all ~ Contains specific details .

show profile cpu for query 8;

mysql> show profile all for query 8;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
| starting | 0.000055 | 0.000015 | 0.000031 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| checking permissions | 0.000005 | 0.000001 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 |
| Opening tables | 0.000016 | 0.000005 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5781 |
| init | 0.000044 | 0.000014 | 0.000027 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 128 |
| System lock | 0.000009 | 0.000002 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 |
| optimizing | 0.000004 | 0.000001 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |
| statistics | 0.000011 | 0.000004 | 0.000007 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 |
| preparing | 0.000009 | 0.000003 | 0.000005 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 482 |
| executing | 0.000002 | 0.000000 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |
| Sending data | 0.000101 | 0.000032 | 0.000063 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 202 |
| end | 0.000006 | 0.000001 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |
| query end | 0.000006 | 0.000002 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4956 |
| closing tables | 0.000005 | 0.000001 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5009 |
| freeing items | 0.000009 | 0.000003 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5622 |
| cleaning up | 0.000010 | 0.000003 | 0.000006 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)

trace Analyze optimizer execution plan .

MySQL 5.6 Provide for the right to SQL Keep track of trace, adopt trace File to learn more about why the optimizer chose A plan , Instead of choosing B plan .

open trace Format as JSON, And set up trace Maximum available memory size , Avoid that the default memory is too small to be fully displayed during parsing .

SET optimizer_trace = “enabled=on”, end_markers_in_json = on;
SET optimizer_trace_max_mem_size=1000000;

mysql> SET optimizer_trace = "enabled=on", end_markers_in_json = on;
Query OK, 0 rows affected (0.00 sec)
mysql> SET optimizer_trace_max_mem_size = 1000000;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from city_innodb
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `city_innodb`.`city_id` AS `city_id`,`city_innodb`.`city_name` AS `city_name`,`city_innodb`.`country_id` AS `country_id` from `city_innodb`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"table_dependencies": [
{
"table": "`city_innodb`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"rows_estimation": [
{
"table": "`city_innodb`",
"table_scan": {
"rows": 1,
"cost": 1
} /* table_scan */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`city_innodb`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 1,
"access_type": "scan",
"resulting_rows": 1,
"cost": 1.2,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.2,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`city_innodb`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`city_innodb`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>

Index usage .

  • Get ready 300W Data .
CREATE TABLE `demo_01`.`tb_item` (
`id` INT NOT NULL COMMENT ' goods id.',
`title` VARCHAR(45) NOT NULL COMMENT ' Commodity title .',
`price` DECIMAL(20,2) NOT NULL COMMENT ' commodity price , Company : element .',
`num` INT NOT NULL COMMENT ' Inventory quantity .',
`categoryid` BIGINT NOT NULL COMMENT ' Genus .',
`status` VARCHAR(1) NOT NULL COMMENT ' Goods state .1~ normal ,2~ Off the shelf ,3~ Delete .',
`sellerid` VARCHAR(45) NOT NULL COMMENT ' merchants id.',
`createtime` DATETIME NOT NULL COMMENT ' Creation time .',
`updatetime` DATETIME NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = ' Commodity list .';
USE `demo_01`;
DROP procedure IF EXISTS `insert_tb_item`;
DELIMITER $$
USE `demo_01`$$
CREATE DEFINER=`root`@`%` PROCEDURE `insert_tb_item`()
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= 3000000 DO
INSERT INTO tb_item VALUES (
num,
concat(' goods ', num, ' Number '),
round(rand() * 100, 2),
floor(rand() * 100),
floor(rand() * 10),
'1',
'2450702011',
'2020-09-09 19:19:19',
'2020-09-09 19:19:19');
SET num = num + 1;
END WHILE;
END$$
DELIMITER ;
call demo_01.insert_tb_item();

SQL Optimize .

Use of index .
mysql> select count(*) from tb_item;
+----------+
| count(*) |
+----------+
| 3000000 |
+----------+
1 row in set (1.07 sec)

title Without index .

mysql> select * from tb_item where title = ' goods 1 Number ';
+----+--------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+----+--------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 1 | goods 1 Number | 26.54 | 44 | 4 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+----+--------------+-------+-----+------------+--------+------------+---------------------+---------------------+
1 row in set (1.44 sec)

id There's a primary key index .

mysql> select * from tb_item where id = 11111;
+-------+------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+-------+------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 11111 | goods 11111 Number | 92.00 | 73 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+-------+------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
1 row in set (0.00 sec)
  • to title Create index .
ALTER TABLE `demo_01`.`tb_item`
ADD INDEX `idx_item_title` (`title` ASC);
;
mysql> create index idx_item_title on tb_item(title);
Query OK, 0 rows affected (8.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from tb_item where title = ' goods 222 Number ';
+-----+----------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+-----+----------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 222 | goods 222 Number | 1.20 | 35 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+-----+----------------+-------+-----+------------+--------+------------+---------------------+---------------------+
1 row in set (0.00 sec)
Create index Syntax .
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[index_type]
ON tbl_name (key_part,...)
[index_option]
[algorithm_option | lock_option] ...
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
| ENGINE_ATTRIBUTE [=] 'string'
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
index_type:
USING {
BTREE | HASH}
algorithm_option:
ALGORITHM [=] {
DEFAULT | INPLACE | COPY}
lock_option:
LOCK [=] {
DEFAULT | NONE | SHARED | EXCLUSIVE}

Avoid index invalidation .
CREATE TABLE `tb_seller` (
`sellerid` varchar(45) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`nickname` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`status` varchar(1) DEFAULT NULL,
`address` varchar(45) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
PRIMARY KEY (`sellerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('alibaba', ' Alibaba ', ' Ali shop ', '123', '1', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('baidu', ' Baidu Technology Co., Ltd ', ' Baidu store ', '123', '1', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('huawei', ' Huawei Technology Co., Ltd ', ' Huawei store ', '123', '0', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('itcast', ' Spreading wisdom Podcast ', ' Spreading wisdom Podcast ', '123', '1', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('heima', ' Black horse programmer ', ' Black horse programmer ', '123', '0', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('luoji', ' Logitech Technology Co., Ltd ', ' Logitech shop ', '123', '1', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('oppo', 'oppo Technology Co., Ltd ', 'oppo Official flagship store ', '123', '0', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('qiandu', ' QIANDU Technology Co., Ltd ', ' QianDu store ', '123', '1', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('sina', ' Sina Technology Co., Ltd ', ' Sina's official flagship store ', '123', '2', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('xiaomi', ' Xiaomi Technology Co., Ltd ', ' Xiaomi's official flagship store ', '123', '1', ' wuhan ', '2099-09-09 09:09:09');
INSERT INTO `demo_01`.`tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES ('yijia', ' IKEA ', ' IKEA flagship store ', '123', '1', ' Xi'an ', '2099-09-09 09:09:09');
  • Create index .

create index idx_seller_name_stat_addr on tb_seller(name, status, address);

  • Application index .
Full match , Specify specific values for index columns in the index .
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status = '1' and address = ' Xi'an City ';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 373 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

The leftmost prefix rule .
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 183 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status = '1';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 190 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
-- Composite index name, status, address, Where indexes can be used :name,name\status,name\status\address.
-- address Index cannot be used .
mysql> explain select * from tb_seller where address = ' Xi'an City ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- Order doesn't matter , Just include .
mysql> explain select * from tb_seller where status = '1' and address = ' Xi'an City ' and name = ' Xiaomi Tech ';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 373 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
-- If it is name and address, Also go to the index , Just walking name Indexes ,address Don't walk index (key_len and name The index is the same 183).
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and address = ' Xi'an City ';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 183 | const | 1 | 10.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+

Range query right column , No index .
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status = '1' and address = ' Xi'an City ';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 373 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
-- Composite index name, status, address,status Using range queries , So the one on the right address The index is not used . It only took name and address.
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status > '1' and address = ' Xi'an City ';
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_seller | NULL | range | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 190 | NULL | 1 | 10.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status = '1';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 190 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status > '1';
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_seller | NULL | range | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 190 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Do not use arithmetic operations on index columns , Otherwise, the index will fail .
mysql> explain select * from tb_seller where substring(name, 3, 2) = ' Technology ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

String without single quotes , Index failure ( Implicit type conversion ).
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status = '1';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 190 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ' and status = 1;
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 183 | const | 1 | 10.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use range access on index 'idx_seller_name_stat_addr' due to type or collation conversion on field 'status' |
| Note | 1003 | /* select#1 */ select `demo_01`.`tb_seller`.`sellerid` AS `sellerid`,`demo_01`.`tb_seller`.`name` AS `name`,`demo_01`.`tb_seller`.`nickname` AS `nickname`,`demo_01`.`tb_seller`.`password` AS `password`,`demo_01`.`tb_seller`.`status` AS `status`,`demo_01`.`tb_seller`.`address` AS `address`,`demo_01`.`tb_seller`.`createtime` AS `createtime` from `demo_01`.`tb_seller` where ((`demo_01`.`tb_seller`.`name` = ' Xiaomi Tech ') and (`demo_01`.`tb_seller`.`status` = 1)) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

Try to use overlay index ~ avoid select *.

Overlay index ~ Queries that only access the index ( Index column contains query column completely ).

mysql> explain select * from tb_seller where name = ' Xiaomi Tech ';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 183 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

extra.(5.7 After that, only null and using index).
Using index ~ An error occurred while using the overlay index .
Using where ~ In the case of search using index , You need to go back to the table to find the required data .
Using index condition ~ Search uses index , But you need to return the table to query the data .
Using where; Using index ~ Search uses index , But the required data can be found in the index column , So you don't need to go back to the table to query the data .


or.

use or The conditions of separation , If or The columns in the previous condition are indexed , And there's no index in the next column , Then the indexes involved will not be used .

mysql> explain select * from tb_seller where name=' Xiaomi Tech ' or nickname = ' Xiaomi's official flagship store ';
+----+-------------+-----------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | idx_seller_name_stat_addr | NULL | NULL | NULL | 11 | 19.00 | Using where |
+----+-------------+-----------+------------+------+---------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- and You can use indexes .
mysql> explain select * from tb_seller where name=' Xiaomi Tech ' and nickname = ' Xiaomi's official flagship store ';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 183 | const | 1 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

like ~ % The index fails before .
mysql> explain select * from tb_seller where name like '% Technology ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where name like ' Technology %';
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_seller | NULL | range | idx_seller_name_stat_addr | idx_seller_name_stat_addr | 183 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where name like '% Technology %';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

like ~ % The index fails before ~ solve : Use overlay index .
mysql> explain select sellerid from tb_seller where name like '% Technology %';
+----+-------------+-----------+------------+-------+---------------+---------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb_seller | NULL | index | NULL | idx_seller_name_stat_addr | 373 | NULL | 11 | 11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select sellerid, name from tb_seller where name like '% Technology %';
+----+-------------+-----------+------------+-------+---------------+---------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb_seller | NULL | index | NULL | idx_seller_name_stat_addr | 373 | NULL | 11 | 11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+---------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
-- Need to be all index columns .
mysql> explain select sellerid, name, nickname from tb_seller where name like '% Technology %';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

If MySQL Evaluation uses indexes more slowly than full tables , Index is not used .
mysql> select * from tb_seller;
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
| sellerid | name | nickname | password | status | address | createtime |
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
| alibaba | Alibaba | Ali shop | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| baidu | Baidu Technology Co., Ltd | Baidu store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| heima | Black horse programmer | Black horse programmer | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| huawei | Huawei Technology Co., Ltd | Huawei store | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| itcast | Spreading wisdom Podcast | Spreading wisdom Podcast | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| luoji | Logitech Technology Co., Ltd | Logitech shop | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| oppo | oppo Technology Co., Ltd | oppo Official flagship store | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| qiandu | QIANDU Technology Co., Ltd | QianDu store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| sina | Sina Technology Co., Ltd | Sina's official flagship store | 123 | 2 | wuhan | 2099-09-09 09:09:09 |
| xiaomi | Xiaomi Technology Co., Ltd | Xiaomi's official flagship store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| yijia | IKEA | IKEA flagship store | 123 | 1 | Xi'an | 2099-09-09 09:09:09 |
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
11 rows in set (0.00 sec)
mysql> explain select * from tb_seller where address = ' wuhan ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where address = ' Xi'an ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | NULL | NULL | NULL | NULL | 11 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
-- address Create index .
mysql> create index idx_address on tb_seller(`address`);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- Most of them are wuhan , Don't walk index .
mysql> explain select * from tb_seller where address = ' wuhan ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | idx_address | NULL | NULL | NULL | 11 | 90.91 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller where address = ' Xi'an ';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_address | idx_address | 183 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

NULL Value determination .( If MySQL Evaluation uses indexes more slowly than full tables , Index is not used .).
-- For the most part not null, Want to find null, Go to the index .
mysql> explain select * from tb_seller where address is null;
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_address | idx_address | 183 | const | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
-- For the most part not null, Want to find not null, Don't walk index .
mysql> explain select * from tb_seller where address is not null;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | idx_address | NULL | NULL | NULL | 11 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

in Go to the index & not in Index failure .
mysql> explain select * from tb_seller where sellerid in ('oppo', 'xiaomi', 'sina');
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | range | PRIMARY | PRIMARY | 182 | NULL | 3 | 100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from tb_seller where sellerid not in ('oppo', 'xiaomi', 'sina');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | PRIMARY | NULL | NULL | NULL | 11 | 81.82 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

Single index & Composite index .

Try to use composite indexes , Use less single column indexes .

create index idc_name_sta_addr on tb_seller (name, status, address);
It's equivalent to creating 3 An index .
name
name + styatus
name + status + adsdress

Single index

create index idc_name_sta_addr on tb_seller (name);
create index idc_name_sta_addr on tb_seller (status);
create index idc_name_sta_addr on tb_seller (address);

MySQL Will choose an optimal index to use , Not all indexes will be used .


Check index usage .

  • Handler_read_first.
    The number of times the first item in the index has been read . If it's higher , Indicates that the server is performing a large number of full index scans ( The lower the value, the better ).
  • Handler_read_key.
    If the index is working , This value represents the number of times a row has been read by the index value , If the value is lower , Indicates that the performance improvement of index is not high , Because indexes are not often used ( The higher the value, the better ).
  • Handler_read_next.
    The number of requests to read the next line in order . If you use range constraints or if you perform index scans to query index columns , The value increases .
  • Handler_read_prev
    Read the first line of the request in the order of , This reading method is mainly used to optimize ORDER BY.
  • Handler_read_rnd
    The number of requests to read a line according to a fixed location . If you are executing a large number of queries and need to sort the results, the value is high . You may be using a lot of needs MySQL Scan the entire table for queries or your connection is not properly keyed , A higher value means less efficient operation , An index should be established to remedy .
  • Handler_read_rnd_next
    Number of requests to read the next line in the data file , If you're doing a lot of scanning , The value is higher . It usually means that your table index is incorrect or the index is not used in the written query .

SQL Optimize .

Mass insert data .

https://dev.mysql.com/doc/internals/en/load-data-infile-events.html

CREATE TABLE `demo_01`.`tb_user_1` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`password` VARCHAR(96) NOT NULL,
`name` VARCHAR(45) NOT NULL,
`birthday` DATETIME NULL DEFAULT NULL,
`sex` CHAR(1) NULL DEFAULT NULL,
`email` VARCHAR(45) NULL DEFAULT NULL,
`phone` VARCHAR(45) NULL DEFAULT NULL,
`qq` VARCHAR(45) NULL DEFAULT NULL,
`status` VARCHAR(45) NULL,
`create_time` VARCHAR(45) NOT NULL,
`update_time` DATETIME NULL DEFAULT NULL,
`tb_user_1col` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `unique_user_username` (`username` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE `demo_01`.`tb_user_1` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`password` VARCHAR(96) NOT NULL,
`name` VARCHAR(45) NOT NULL,
`birthday` DATETIME NULL DEFAULT NULL,
`sex` CHAR(1) NULL DEFAULT NULL,
`email` VARCHAR(45) NULL DEFAULT NULL,
`phone` VARCHAR(45) NULL DEFAULT NULL,
`qq` VARCHAR(45) NULL DEFAULT NULL,
`status` VARCHAR(45) NULL,
`create_time` VARCHAR(45) NOT NULL,
`update_time` DATETIME NULL DEFAULT NULL,
`tb_user_1col` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `unique_user_username` (`username` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Insert 100W Data .

Use load Command to import data , Appropriate settings can improve the efficiency of import .

  • Insert primary key in order .

because InnoDB Tables of type are saved in the order of primary keys , So the imported data is arranged in the order of primary key , It can effectively improve the efficiency of importing data . As if InnoDB Table has no primary key , Then the system will automatically create an internal column as the primary key by default , So if you can create a primary key for a table , This can be used to improve the efficiency of importing data .

$ head sql1.log # Orderly .
1,“username1”,"123","name1","2020-09-09 09:09:09","1","[email protected]","13812345678","2450702011","0","2020-09-09 09:09:09","2020-09-09 09:09:09"
2,“username2”,"123","name1","2020-09-09 09:09:09","2","[email protected]","13812345678","2450702011","0","2020-09-09 09:09:09","2020-09-09 09:09:09"
3,“username3”,"123","name1","2020-09-09 09:09:09","3","[email protected]","13812345678","2450702011","1","2020-09-09 09:09:09","2020-09-09 09:09:09"
$ head sql2.log # disorder .
17836,“username17836”,"123","name17836","2020-09-09 09:09:09","17836","[email protected]","13812345678","2450702011","0","2020-09-09 09:09:09","2020-09-09 09:09:09"
90962,“username90962”,"123","name90962","2020-09-09 09:09:09","90962","[email protected]","13812345678","2450702011","1","2020-09-09 09:09:09","2020-09-09 09:09:09"
77345,“username77345”,"123","name77345","2020-09-09 09:09:09","77345","[email protected]","13812345678","2450702011","0","2020-09-09 09:09:09","2020-09-09 09:09:09"

load data infile ‘/home/geek/geek/sql1.log’ into table tb_user1 fields terminated by ‘,’ lines terminated by ‘\n’;
load data infile ‘/home/geek/geek/sql2.log’ into table tb_user2 fields terminated by ‘,’ lines terminated by ‘\n’;

mysql> load data infile ‘/home/geek/geek/sql1.log’ into table tb_user_1 fields terminated by ‘,’ lines terminated by ‘\n’;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

load data infile ‘/m/tmp/u.txt’ replace into table x fields
terminated by ‘,’ optionally enclosed by ‘"’ escaped by ‘\’
lines starting by ‘>’ terminated by ‘\n’ ignore 2 lines (a,b,c);

  • Turn off uniqueness check .

Execute... Before importing data SET UNIQUE_CHECK = 0, Turn off uniqueness check , Execute... After import SET UNIQUE_CHECK = 1 Restore uniqueness check . Can improve the efficiency of import .

  • Commit transactions manually .

Execute... Before importing data SET AUTOCOMMIT= 0, Turn off auto submit , Execute... After import SET AUTOCOMMIT= 1 Resume auto commit . Can improve the efficiency of import .


Optimize insert sentence .

When it comes to data insert During operation , The following optimization schemes can be considered .

  • If you need to insert many rows of data into a table at the same time , You should try to use more than one value table insert sentence , This way will greatly reduce the connection between the client and the database 、 Turn off consumption, etc . Make the efficiency ratio separate from the single execution insert Fast sentence .

eg. The original way is


insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jerry');

The optimized scheme is

insert into tb_test values(1, 'Tom'), (2, 'Cat'), (3, 'Jerry');
  • Data insertion in transactions .
start transaction;
insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jerry');
commit;
  • Data is inserted in order
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

Optimize .

insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jerry');
insert into tb_test values(4, 'Tim');
insert into tb_test values(5, 'Rose');

Optimize order by sentence .
  • Environmental preparation .
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL COMMENT ' full name .',
`age` int(11) DEFAULT NULL COMMENT ' Age .',
`salary` int(11) DEFAULT NULL COMMENT ' salary .',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('1', 'Tom', '25', '2300');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('2', 'Jerry', '30', '3500');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('3', 'Lucy', '25', '2800');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('4', 'Jay', '36', '3500');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('5', 'Tom2', '21', '2200');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('6', 'Jerry2', '31', '3300');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('7', 'Lucy2', '26', '2700');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('8', 'Jay2', '33', '3500');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('9', 'Tom3', '23', '2400');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('10', 'Jerry3', '32', '3100');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('11', 'Lucy3', '26', '2900');
INSERT INTO `demo_01`.`emp` (`id`, `name`, `age`, `salary`) VALUES ('12', 'Jay3', '37', '4500');
create index idx_emp_age_salary on emp(age, salary);
mysql> show index from emp;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp | 0 | PRIMARY | 1 | id | A | 12 | NULL | NULL | | BTREE | | |
| emp | 1 | idx_emp_age_salary | 1 | age | A | 10 | NULL | NULL | YES | BTREE | | |
| emp | 1 | idx_emp_age_salary | 2 | salary | A | 12 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
MySQL Two ways of sorting .
  • The first is to sort the returned data , That is to say filesort Sort .
    All sorts that do not return sorting results directly through index are called fileSort Sort .
mysql> explain select * from emp order by age, salary;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
  • The second way is to return ordered data directly through ordered index scanning , This is the case using index, No need for extra sorting , High operating efficiency .

( Override fields ).

mysql> explain select id from emp order by age;
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_age_salary | 10 | NULL | 12 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id, age from emp order by age;
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_age_salary | 10 | NULL | 12 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select id, age, salary from emp order by age;
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_age_salary | 10 | NULL | 12 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • order by The fields are either all desc, Or all asc.
  • The sorting field order shall be consistent with the index order .

Optimize filesort.

By creating the appropriate index , Can reduce the Filesort Appearance , But in some cases , Conditionality cannot allow filesort disappear , Then we need to speed up Filesort The sorting operation of . about Filesort,MySQL There are two sort algorithms .

  • Two scan algorithm .

MySQL 4.1 Before , Use this method to sort . First, take out the sorting field and row pointer information according to the conditions , Then in the sorting area sort buffer Middle order , If sort buffer Not enough , On the temporary watch temporary table The sorting result is stored in . After sorting , Then read the record according to the row pointer back to the table , This operation may result in a large number of random I/O operation .

  • One scan algorithm .

Take out all the fields that meet the conditions at once , Then in the sorting area sort buffer After sorting, output the result set directly . When sorting, memory overhead is large , But the efficiency of sorting is higher than that of twice scanning algorithm .

MySQL By comparing system variables max_length_for_sort_data The size and Query The total size of the field taken out by the statement , To determine the sort algorithm . If max_length_for_sort_data Bigger , Then use the second optimized algorithm , Otherwise use the first .

Can be improved properly sort_buffer_size and max_length_for_sort_data System variable to increase the size of the sorting area , Improve the efficiency of sorting .

mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024 |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

Optimize group by sentence .

because GROUP BY In fact, the sorting operation will also be carried out , And with the ORDER BY comparison ,GROUP BY It's mainly about the grouping operation after sorting . Of course , If some other aggregate functions are used when grouping , So we need to calculate some aggregate functions . therefore , stay GROUP BY During the implementation of , And ORDER BY You can also use the index .

If the query contains group by But users want to avoid the consumption of sorting results , Then you can execute order by null No sorting .

mysql> drop index idx_emp_age_salary on emp;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select age, count(*) from emp group by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select age, count(*) from emp group by age order by null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
  • Continue to optimize using temporary.
mysql> create index idx_emp_age_salary on emp(age, salary);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select age, count(*) from emp group by age order by null;
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | index | idx_emp_age_salary | idx_emp_age_salary | 10 | NULL | 12 | 100.00 | Using index |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Optimize nested queries .

MySQL 4.1 After the version , Start supporting SQL Subquery of . This technology can be used SELECT Statement to create a single column query result , Then use this result as a filter in another query . Using subquery can complete many logical steps at once SQL operation , At the same time, it can also avoid transaction or table locking , And it's easy to write . however , In some cases , Subqueries can be joined more efficiently (JOIN) Instead of .

mysql> explain select * from t_user where id in (select user_id from user_role);
+----+--------------+-------------+------------+--------+-------------------+-------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+-------------------+-------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | t_user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 6 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_key> | <auto_key> | 99 | demo_01.t_user.id | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | user_role | NULL | index | fk_ur_user_id_idx | fk_ur_user_id_idx | 99 | NULL | 6 | 100.00 | Using index |
+----+--------------+-------------+------------+--------+-------------------+-------------------+---------+-------------------+------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)
mysql> explain select * from t_user u, user_role ur where u.id = ur.user_id;
+----+-------------+-------+------------+--------+-------------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+-------------------+---------+---------+--------------------+------+----------+-------------+
| 1 | SIMPLE | ur | NULL | ALL | fk_ur_user_id_idx | NULL | NULL | NULL | 6 | 100.00 | Using where |
| 1 | SIMPLE | u | NULL | eq_ref | PRIMARY | PRIMARY | 98 | demo_01.ur.user_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+-------------------+---------+---------+--------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Optimize or Conditions .

To contain or Query clause for , If you want to use indexes , be OR Every condition between must use an index , And you can't use composite indexes . If you don't use the index , You should consider adding an index .

Optimize : have access to union sentence .

mysql> show index from emp;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp | 0 | PRIMARY | 1 | id | A | 12 | NULL | NULL | | BTREE | | |
| emp | 1 | idx_emp_age_salary | 1 | age | A | 10 | NULL | NULL | YES | BTREE | | |
| emp | 1 | idx_emp_age_salary | 2 | salary | A | 12 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> explain select * from emp where id = 1 or age = 10;
+----+-------------+-------+------------+-------------+----------------------------+----------------------------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+----------------------------+----------------------------+---------+------+------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | emp | NULL | index_merge | PRIMARY,idx_emp_age_salary | idx_emp_age_salary,PRIMARY | 5,4 | NULL | 2 | 100.00 | Using sort_union(idx_emp_age_salary,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+----------------------------+----------------------------+---------+------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where id = 1 union select * from emp where age = 10;
+----+--------------+------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | UNION | emp | NULL | ref | idx_emp_age_salary | idx_emp_age_salary | 5 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

Optimize paging queries .

Paging query the data at the back , It's going to be slow .MySQL Before sorting 2000010 Bar record , Just go back to 2000000 ~ 2000010 Bar record , Other records discarded , The cost of query sorting is very high .

mysql> select * from tb_item limit 10;
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 1 | goods 1 Number | 26.54 | 44 | 4 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2 | goods 2 Number | 88.50 | 8 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 3 | goods 3 Number | 63.52 | 83 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 4 | goods 4 Number | 92.13 | 74 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 5 | goods 5 Number | 54.99 | 88 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 6 | goods 6 Number | 11.69 | 32 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 7 | goods 7 Number | 50.62 | 63 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 8 | goods 8 Number | 33.99 | 75 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 9 | goods 9 Number | 52.79 | 36 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 10 | goods 10 Number | 5.08 | 57 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
10 rows in set (0.00 sec)
mysql> select * from tb_item limit 10, 10;
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 11 | goods 11 Number | 81.17 | 94 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 12 | goods 12 Number | 56.83 | 0 | 3 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 13 | goods 13 Number | 54.76 | 80 | 3 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 14 | goods 14 Number | 46.71 | 20 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 15 | goods 15 Number | 57.93 | 97 | 1 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 16 | goods 16 Number | 73.00 | 25 | 1 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 17 | goods 17 Number | 76.08 | 48 | 1 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 18 | goods 18 Number | 15.72 | 42 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 19 | goods 19 Number | 3.54 | 18 | 8 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 20 | goods 20 Number | 58.79 | 44 | 4 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
10 rows in set (0.00 sec)
mysql> select * from tb_item limit 20, 10;
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 21 | goods 21 Number | 7.31 | 91 | 3 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 22 | goods 22 Number | 97.80 | 86 | 3 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 23 | goods 23 Number | 26.87 | 23 | 3 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 24 | goods 24 Number | 8.55 | 35 | 5 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 25 | goods 25 Number | 58.62 | 33 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 26 | goods 26 Number | 59.06 | 19 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 27 | goods 27 Number | 42.33 | 51 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 28 | goods 28 Number | 91.46 | 70 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 29 | goods 29 Number | 74.26 | 40 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 30 | goods 30 Number | 71.39 | 21 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+----+---------------+-------+-----+------------+--------+------------+---------------------+---------------------+
10 rows in set (0.01 sec)
mysql> select * from tb_item limit 2000000, 10;
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 2000001 | goods 2000001 Number | 20.36 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000002 | goods 2000002 Number | 3.13 | 24 | 1 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000003 | goods 2000003 Number | 0.45 | 57 | 8 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000004 | goods 2000004 Number | 51.80 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000005 | goods 2000005 Number | 31.64 | 50 | 5 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000006 | goods 2000006 Number | 32.46 | 92 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000007 | goods 2000007 Number | 42.53 | 21 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000008 | goods 2000008 Number | 25.66 | 94 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000009 | goods 2000009 Number | 4.22 | 27 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000010 | goods 2000010 Number | 44.92 | 47 | 0 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
10 rows in set (1.32 sec)
  • Optimize .

Complete sort paging operation on Index , Finally, other columns required by the query of the original table are associated according to the primary key .

mysql> select * from tb_item limit 2000000, 10;
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 2000001 | goods 2000001 Number | 20.36 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000002 | goods 2000002 Number | 3.13 | 24 | 1 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000003 | goods 2000003 Number | 0.45 | 57 | 8 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000004 | goods 2000004 Number | 51.80 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000005 | goods 2000005 Number | 31.64 | 50 | 5 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000006 | goods 2000006 Number | 32.46 | 92 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000007 | goods 2000007 Number | 42.53 | 21 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000008 | goods 2000008 Number | 25.66 | 94 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000009 | goods 2000009 Number | 4.22 | 27 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000010 | goods 2000010 Number | 44.92 | 47 | 0 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
10 rows in set (1.32 sec)
-- primary key .
mysql> explain select * from tb_item t, (select id from tb_item order by id limit 2000000, 10) a where t.id = a.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2000010 | 100.00 | NULL |
| 1 | PRIMARY | t | NULL | eq_ref | PRIMARY | PRIMARY | 4 | a.id | 1 | 100.00 | NULL |
| 2 | DERIVED | tb_item | NULL | index | NULL | PRIMARY | 4 | NULL | 2000010 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)
mysql> select * from tb_item t, (select id from tb_item order by id limit 2000000, 10) a where t.id = a.id;
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+---------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime | id |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+---------+
| 2000001 | goods 2000001 Number | 20.36 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000001 |
| 2000002 | goods 2000002 Number | 3.13 | 24 | 1 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000002 |
| 2000003 | goods 2000003 Number | 0.45 | 57 | 8 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000003 |
| 2000004 | goods 2000004 Number | 51.80 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000004 |
| 2000005 | goods 2000005 Number | 31.64 | 50 | 5 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000005 |
| 2000006 | goods 2000006 Number | 32.46 | 92 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000006 |
| 2000007 | goods 2000007 Number | 42.53 | 21 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000007 |
| 2000008 | goods 2000008 Number | 25.66 | 94 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000008 |
| 2000009 | goods 2000009 Number | 4.22 | 27 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000009 |
| 2000010 | goods 2000010 Number | 44.92 | 47 | 0 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 | 2000010 |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+---------+
10 rows in set (0.58 sec)
-- Full table scan .
mysql> explain select * from tb_item limit 2000000, 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
| 1 | SIMPLE | tb_item | NULL | ALL | NULL | NULL | NULL | NULL | 2983100 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

This scheme is applicable to tables with self increasing primary key , You can put limit The query is converted to a query in a certain location .

mysql> select * from tb_item where id > 2000000 limit 10;
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| id | title | price | num | categoryid | status | sellerid | createtime | updatetime |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
| 2000001 | goods 2000001 Number | 20.36 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000002 | goods 2000002 Number | 3.13 | 24 | 1 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000003 | goods 2000003 Number | 0.45 | 57 | 8 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000004 | goods 2000004 Number | 51.80 | 4 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000005 | goods 2000005 Number | 31.64 | 50 | 5 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000006 | goods 2000006 Number | 32.46 | 92 | 6 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000007 | goods 2000007 Number | 42.53 | 21 | 7 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000008 | goods 2000008 Number | 25.66 | 94 | 9 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000009 | goods 2000009 Number | 4.22 | 27 | 2 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
| 2000010 | goods 2000010 Number | 44.92 | 47 | 0 | 1 | 2450702011 | 2020-09-09 19:19:19 | 2020-09-09 19:19:19 |
+---------+--------------------+-------+-----+------------+--------+------------+---------------------+---------------------+
10 rows in set (0.01 sec)
mysql> explain select * from tb_item where id > 2000000 limit 10;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | tb_item | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 1491550 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Use sql Tips .

stay SQL Add some artificial hints to the statement to achieve the purpose of optimization .

use index.
mysql> create index idx_seller_name on tb_seller(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from tb_seller where name = ' Xiaomi Tech ';
+----+-------------+-----------+------------+------+-------------------------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------------+---------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name_stat_addr,idx_seller_name | idx_seller_name_stat_addr | 183 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-------------------------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller use index(idx_seller_name) where name = ' Xiaomi Tech ';
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_seller_name | idx_seller_name | 183 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

ignore index.

explain select * from tb_seller ignore index(idx_seller_name) where name = ‘ Xiaomi Tech ’;


force index.

Possible indexes , But it doesn't actually use . Because this field is in part “ wuhan ”,MySQL I think it's faster to use full table scanning without index . Need here force.

mysql> select * from tb_seller;
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
| sellerid | name | nickname | password | status | address | createtime |
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
| alibaba | Alibaba | Ali shop | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| baidu | Baidu Technology Co., Ltd | Baidu store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| heima | Black horse programmer | Black horse programmer | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| huawei | Huawei Technology Co., Ltd | Huawei store | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| itcast | Spreading wisdom Podcast | Spreading wisdom Podcast | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| luoji | Logitech Technology Co., Ltd | Logitech shop | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| oppo | oppo Technology Co., Ltd | oppo Official flagship store | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| qiandu | QIANDU Technology Co., Ltd | QianDu store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| sina | Sina Technology Co., Ltd | Sina's official flagship store | 123 | 2 | wuhan | 2099-09-09 09:09:09 |
| xiaomi | Xiaomi Technology Co., Ltd | Xiaomi's official flagship store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| yijia | IKEA | IKEA flagship store | 123 | 1 | Xi'an | 2099-09-09 09:09:09 |
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
11 rows in set (0.01 sec)
mysql> explain select * from tb_seller where address = ' wuhan ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | idx_address | NULL | NULL | NULL | 11 | 90.91 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller use index(idx_address) where address = ' wuhan ';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_seller | NULL | ALL | idx_address | NULL | NULL | NULL | 11 | 90.91 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_seller force index(idx_address) where address = ' wuhan ';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_seller | NULL | ref | idx_address | idx_address | 183 | const | 10 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Application level optimization .


Use database connection pool .

For accessing databases , The cost of establishing a connection is more expensive , Because we often create closed connections , It's more resource consuming , It's necessary for us to establish a database connection pool , To improve access performance .


Reduce to MySQL The interview of .
Avoid duplicate retrieval of data .

When writing application code , Need to be able to clarify the access logic to the database . Able to get results in one connection , You don't have to connect twice , This can greatly reduce unnecessary repeated requests to the database .

eg. Need to get books id and name Field , Then query as follows .

select id, name from tb_book;

after , In business logic, it is necessary to obtain the book status information , Then query as follows .

select id, status from tb_book;

such , You need to submit two requests to the database , The database has to do two queries . Actually, you can use one SQL Statement to get the desired result .

select id, name, status from tb_book;


increase cache layer .

In the application , We can add cache Layer to reduce the burden on the database . There are many kinds of cache layers , There are also many ways to implement , As long as it can reduce the burden of the database and meet the application requirements .

Therefore, part of the data can be extracted from the database and stored in the application side in the form of text , Or use a framework (Mybatis, Hibernate) First level cache provided / Second level cache , Or use redis Database to cache data .


Load balancing .

Load balancing is a very common optimization method in application , Its mechanism is to use some kind of equalization algorithm , Distribute the fixed load to different servers , To reduce the load of a single server , Achieve the optimized effect .

utilize MySQL Copy split query .

adopt MySQL Master-slave replication of , Read and write separation , Make add, delete, and change the primary node , Query operation goes from node , Thus, the reading and writing pressure of a single server can be reduced .


Adopt distributed database architecture .

Distributed database architecture is suitable for large data volume 、 High load , It has good expansibility and high availability . By distributing data between multiple servers , Load balancing among multiple servers can be realized , Improve access efficiency .


Query cache optimization .

Turn on MySQL The query cache , When performing exactly the same SQL When the sentence is , The server will read the results directly from the cache . When the data is modified , The previous cache will be invalidated . Tables with frequent changes are not suitable for query caching .

Do you support .

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)

Open or not .

mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_type | OFF |
+------------------+-------+
1 row in set (0.00 sec)

Query cache size .

mysql> show variables like 'query_cache_size';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+
1 row in set (0.00 sec)
mysql> show variables like '%cache%';
+--------------------------------+----------------------+
| Variable_name | Value |
+--------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | YES |
| host_cache_size | 279 |
| innodb_disable_sort_file_cache | OFF |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_total_cache_size | 640000000 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 1400 |
| table_open_cache | 2000 |
| table_open_cache_instances | 16 |
| thread_cache_size | 9 |
+--------------------------------+----------------------+
24 rows in set (0.01 sec)
mysql> show status like '%cache%';
+--------------------------------+---------+
| Variable_name | Value |
+--------------------------------+---------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Com_assign_to_keycache | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 199 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 9 |
| Ssl_session_cache_mode | SERVER |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 128 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Table_open_cache_hits | 16 |
| Table_open_cache_misses | 11 |
| Table_open_cache_overflows | 0 |
| Threads_cached | 4 |
+--------------------------------+---------+
25 rows in set (0.00 sec)
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 199 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
Turn on query cache .

MySQL The query cache of is turned off by default , Parameters need to be configured manually query_cache_type, To open the query cache .query_cache_type There are three values of this parameter .

value meaning
OFF or 0 Query caching is off .
ON or 1 Query cache function on ,SELECT If the result meets the caching conditions, it will be cached , otherwise , No caching , Explicitly specify SQL_NO_CACHE, No caching .
DEMAND or 2 The query cache function is on demand , Explicitly specify SQL_CACHE Of SELECT Statements are cached ; Others are not cached .

stay /usr/my/cnf To configure .

# Turn on MySQL The query cache .
query_cache_type = 1

The query cache SELECT Options .

Can be in SELECT Statement specifies two options related to query caching .

  • SQL_CACHE.
    If the query results are cacheable , also query_cache_type The value of the system variable is ON( No addition SQL_CACHE Go, too ) or DEMAND( Explicitly specify SQL_CACHE Just go ), Then cache the query results .

  • SQL_NO_CACHE.
    The server does not use query caching . It does not check the query cache , Also does not check whether the results are cached , Also do not cache query results .

eg.

mysql> select sql_cache * from tb_seller;
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
| sellerid | name | nickname | password | status | address | createtime |
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
| alibaba | Alibaba | Ali shop | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| baidu | Baidu Technology Co., Ltd | Baidu store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| heima | Black horse programmer | Black horse programmer | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| huawei | Huawei Technology Co., Ltd | Huawei store | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| itcast | Spreading wisdom Podcast | Spreading wisdom Podcast | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| luoji | Logitech Technology Co., Ltd | Logitech shop | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| oppo | oppo Technology Co., Ltd | oppo Official flagship store | 123 | 0 | wuhan | 2099-09-09 09:09:09 |
| qiandu | QIANDU Technology Co., Ltd | QianDu store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| sina | Sina Technology Co., Ltd | Sina's official flagship store | 123 | 2 | wuhan | 2099-09-09 09:09:09 |
| xiaomi | Xiaomi Technology Co., Ltd | Xiaomi's official flagship store | 123 | 1 | wuhan | 2099-09-09 09:09:09 |
| yijia | IKEA | IKEA flagship store | 123 | 1 | Xi'an | 2099-09-09 09:09:09 |
+----------+--------------------------+-----------------------+----------+--------+---------+---------------------+
11 rows in set, 1 warning (0.00 sec)
mysql> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1029368 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+-------------------------+---------+
8 rows in set (0.01 sec)

Query cache failure .
  • SQL In case of Inconsistent Statements , To hit the query cache , Of the query SQL Statements must be consistent .( Case also affects ).
SQL1 : select count(*) from tb_item;
SQL2 : Select count(*) from tb_item;
  • When there is some uncertainty in the query statement , Will not cache .eg. now(),current_date(),curdate(),curtime(),rand(),uuid(),user(),database() .
SQL1 : select * from tb_item where updatetime < now() limit 1;
SQL2 : select user();
SQL3 : select database();
  • Do not use any table query statements .
select 'A';
  • Inquire about mysql,information_schema or performance_schema When tables in the database , Does not walk query cache .
select * from information_schema.engines;
  • Functions in storage , Queries executed within the body of a trigger or event .

  • If the table changes , All cached queries that use this table will become invalid and be removed from the cache . This includes the use of MERGE Queries mapped to tables that have changed tables . A table can be used by many types of statements , If changed INSERT,UPDATE,DELETE,TRUNCATE TABLE,ALTER TABLE,DROP TABLE, or DROP DATABASE .


Memory management optimization .

Memory optimization principles .
  • Allocate as much memory as possible to MySQL Do the cache , But reserve enough memory for the operating system and other programs .

  • MyISAM The data file reading of the storage engine depends on the operating system itself IO cache , therefore , If there is MyISAM surface , We need to reserve more memory for the operating system IO cache .

  • Sorting area 、 Caches such as connection areas are allocated to each database session (session) A dedicated , The default value should be set according to the maximum number of connections , If the setting is too large , Not only waste resources , And when the concurrent connection is high, the physical memory will be exhausted .


MyISAM Memory optimization .

MyISAM Storage engine use key_buffer Cache index blocks , Speed up MyISAM Reading and writing speed of index . about MyISAM Data block of table ,mysql There is no special caching mechanism , Completely dependent on the operating system IO cache .

key_buffer_size.

key_buffer_size decision MyISAM Size of index block cache , Directly affect MyISAM Table access efficiency . Can be in MySQL Parameter file key_buffer_size Value , For the average MyISAM database , Suggest at least general 1/4 Available memory is allocated to key_buffer_size.

Default 8 M.

mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.03 sec)

stay /usr/my.cnf Do the following configuration in .

key_buffer_size = 512M

read_buffer_size.

If frequent sequential scanning is needed MyISAM surface , By increasing read_buffer_size Value to improve performance . But it should be noted that read_buffer_size Is each session Exclusive , If the default setting is too large , Memory is wasted .

mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)

read_rnd_buffer_size.

For those that need to be sorted MyISAM Table in the query , If with order by Clause sql, Add... Appropriately read_rnd_buffer_size Value , Can improve this kind of sql performance . But it should be noted that read_rnd_buffer_size Is each session Exclusive , If the default setting is too large , It will cause internal
Save and waste .

mysql> show variables like 'read_rnd_buffer_size';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set (0.00 sec)

InnoDB Memory optimization .

innodb Use a block of memory to do IO Buffer pool , The cache pool is not just for caching innodb The index block of , And it's also used to cache innodb A block of data .

innodb_buffer_pool_size.

This variable determines innodb Maximum cache size to store engine table data and index data . Under the condition that the operating system and other programs have enough memory available ,innodb_buffer_pool_size The greater the value of , The higher the cache hit rate , visit InnoDB The disk required by the table I/O The less , The higher the performance .

innodb_buffer_pool_size = 512M

Default 128M.

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

innodb_log_buffer_size.

To determine the innodb Size of redo log cache , For large transactions that can generate a large number of update records , increase innodb_log_buffer_size Size , You can avoid innodb Perform unnecessary log writes to disk before the transaction is committed .

innodb_log_buffer_size = 10M

Default 16M.

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

MySQL Concurrent parameter adjustment .

In terms of implementation ,MySQL Server It's a multithreaded structure , Including background thread and customer service thread . Multithreading can make effective use of server resources , Improve the concurrent performance of database . stay MySQL in , The main parameters that control concurrent connections and threads include max_connections、back_log、thread_cache_size、table_open_cahce.


max_connections.

use max_connections Control allows connection to MySQL The maximum number of databases , The default value is 151. If the state variable connection_errors_max_connections Not zero , And it keeps growing , It means that there are continuous connection requests failed due to the maximum number of database connections , It is possible to consider increasing max_connections Value .

MySQL Maximum number of connections supported , It depends on many factors , Include the quality of the thread library for a given operating system platform 、 Memory size 、 Load per connection 、CPU Processing speed of , Expected response time, etc . stay Linux Under the platform , Good performance server , Support 500-1000 A connection is not difficult , You need to evaluate settings based on server performance .

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)

back_log.

back_log Parameter control MySQL monitor TCP The size of the backlog request stack set at port . If MySQL The number of connections reached max_connections when , New requests will be stored in the stack , To wait for a connection to release resources , The number of stacks is back_log, If the number of waiting connections exceeds back_log, Will not be granted connection resources , Will be an error .5.6.6 Before version, the default value was 50, Later versions default to 50 + (max_connections / 5), But not more than 900.

If you need a database to handle a large number of connection requests in a short time , You can consider increasing it appropriately back_log Value .

mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log | 80 |
+---------------+-------+
1 row in set (0.00 sec)

table_open_cache.

This parameter is used to control all SQL Statement execution thread can open the number of table cache , And in the execution SQL When the sentence is , every last SQL The thread of execution must at least be open 1 Table cache . The value of this parameter should be based on the maximum number of connections set max_connections And the maximum number of tables involved in the execution of the associated query for each connection .

max_connections x N;

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 2000 |
+------------------+-------+
1 row in set (0.00 sec)

thread_cache_size.

In order to speed up the connection to the database ,MySQL A certain number of customer service threads will be cached for reuse , Through parameters thread_cache_size Controllable MySQL Number of cached customer service threads .( Thread pool ).

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+
1 row in set (0.00 sec)

innodb_lock_wait_timeout.

This parameter is used to set InnoDB Transaction waiting time for row lock , The default value is 50ms, It can be set dynamically as required .

For business systems that need quick feedback , The waiting time of row lock can be reduced , To avoid long-term transaction suspension .

For batch processors running in the background , You can increase the waiting time of the row lock , To avoid large rollback operations .

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.00 sec)


MySQL lock .

Lock overview .

A lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently ( Avoid fighting ).

In the database , In addition to traditional computing resources ( Such as CPU、RAM、I/O etc. ) Beyond contention , Data is also a resource that is Shared by many users . How to ensure the consistency of data concurrent access 、 Validity is a problem that all databases must solve , Lock conflicts are also an important factor affecting the performance of concurrent database access . From this perspective , Locks are especially important for databases , It's more complicated .


Lock classification .

From the granularity of data operations .

  • Table locks : In operation , Will lock the entire table .
  • Row lock : In operation , Will lock the current action line .

From the type of data operation :

  • Read the lock ( Shared lock ) ~ For the same data , Multiple read operations can be performed simultaneously without affecting each other .
  • Write lock ( Exclusive lock ) ~ Before the current operation is completed , It blocks other write and read locks .

Relative to other databases ,MySQL The locking mechanism is relatively simple , The most notable feature is that different storage engines support different locking mechanisms . The following table lists the support of each storage engine for locks .

Storage engine Table lock Row-level locks Page lock
MyISAM Support I won't support it I won't support it
InnoDB Support Support I won't support it
MEMORY Support I won't support it I won't support it
BDB Support I won't support it Support

MySQL this 3 The characteristics of seed locks can be summarized as follows .

  • Table lock .
    deviation MyISAM Storage engine , Low overhead , Locked fast ; A deadlock will not occur ; Large locking size , The highest probability of lock collisions , Lowest degree of concurrency .

  • Row-level locks .
    deviation InnoDB Storage engine , Spending big , Lock the slow ; A deadlock occurs ; Locking granularity minimum , The lowest probability of lock collisions , The highest degree of concurrency .

  • Page lock .
    Cost and lock time are between table lock and row lock ; A deadlock occurs ; Lock granularity is between table lock and row lock , The concurrency is average .

It can be seen from the above characteristics that , It's hard to generalize which lock is better , Only on the characteristics of specific applications, which lock is more suitable ! Only from the point of view of lock :

Table-level locking is better suited for query-oriented locking , Only a small number of applications update data by index criteria , Such as Web application .
Row level lock is more suitable for a large number of concurrent updates of different data according to index conditions , At the same time, it has the application of parallel query , Like some online transactions (OLTP) System .


MyISAM Table locks .

MyISAM The storage engine only supports table locks , This is also MySQL The only lock type supported in the first few versions .

  • How to lock the watch .

MyISAM In the execution of the query statement (SELECT) front , Will automatically lock all tables involved , Is performing an update operation (UPDATE、DELETE、INSERT etc. ) front , Will automatically lock the tables involved , This process does not require user intervention , therefore , Users generally don't need to use LOCK TABLE Order to MyISAM The watch is explicitly locked .

Explicit table lock Syntax .

 Add read lock ~ lock table -table_name- read;
Add write lock ~ lock table -table_name- write;

MyISAM Read the lock .
CREATE SCHEMA `demo_02` DEFAULT CHARACTER SET utf8mb4 ;
CREATE TABLE `demo_02`.`tb_book` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL DEFAULT NULL,
`publish_time` DATE NULL DEFAULT NULL,
`status` VARCHAR(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
INSERT INTO `demo_02`.`table_book` (`name`, `publish_time`, `status`) VALUES ('Java Programming idea ', '2088-09-09', '1');
INSERT INTO `demo_02`.`table_book` (`name`, `publish_time`, `status`) VALUES ('Python Programming idea ', '2088-09-09', '0');
CREATE TABLE `demo_02`.`tb_user` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;
INSERT INTO `demo_02`.`tb_user` (`name`) VALUES (' linghu chong ');
INSERT INTO `demo_02`.`tb_user` (`name`) VALUES (' Tian boguang ');

terminal 1 Add read lock in ,

mysql> lock table tb_book read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_book;
+----+---------------------+--------------+--------+
| id | name | publish_time | status |
+----+---------------------+--------------+--------+
| 1 | Java Programming idea | 2088-09-09 | 1 |
| 2 | Python Programming idea | 2088-09-09 | 0 |
+----+---------------------+---

terminal 2 You can read ,

mysql> select * from tb_book;
+----+---------------------+--------------+--------+
| id | name | publish_time | status |
+----+---------------------+--------------+--------+
| 1 | Java Programming idea | 2088-09-09 | 1 |
| 2 | Python Programming idea | 2088-09-09 | 0 |
+----+---------------------+---

But in terminal 1 You can't read other tables in .

mysql> select * from tb_user;
ERROR 1100 (HY000): Table 'tb_user' was not locked with LOCK TABLES

A table locked by a read lock cannot update.

mysql> update tb_book set name = 'solr' where id = 2;
ERROR 1099 (HY000): Table 'tb_book' was locked with a READ lock and can't be updated

terminal 2 uodate In a blocking state .

 Insert picture description here

terminal 1 It takes effect immediately after unlocking .

 Insert picture description here


MyISAM Write lock .

terminal 1.

mysql> lock table tb_book write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb_book;
+----+-------------------+--------------+--------+
| id | name | publish_time | status |
+----+-------------------+--------------+--------+
| 1 | Java Programming idea | 2088-09-09 | 1 |
| 2 | solr | 2088-09-09 | 0 |
+----+-------------------+--------------+--------+
2 rows in set (0.00 sec)
mysql> update tb_book set name = 'solr Programming idea ' where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into tb_book (name) values ('es');
Query OK, 1 row affected (0.00 sec)
mysql>

terminal 2 Reading data , In a blocking state .

 Insert picture description here

 Insert picture description here


Summary .

The lock modes are compatible with each other as shown in the table :

 Insert picture description here

As can be seen from the above table .

  • Yes MyISAM Read operation of table , Does not block other users' read requests for the same table , But it blocks write requests to the same table .

  • Yes MyISAM Write operation of table , Will block other users to read and write to the same table .

In short , It's just that reading locks block writing , But it doesn't block reading . And write lock , It will block reading , Will block writing again .

Besides ,MyISAM Read and write lock scheduling is write first , This is also MyISAM The reason why it's not suitable to be a storage engine for write based tables . Because after writing the lock , No other thread can do anything , A large number of updates will make it difficult for queries to get locks , And cause permanent obstruction .


Check lock contention .
  • show open tables;
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| performance_schema | memory_summary_by_user_by_event_name | 0 | 0 |
| performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
| performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
mysql> lock table tb_user read;
mysql> show open tables;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| demo_02 | tb_user | 1 | 0 |
  • show status like ‘Table_locks%’;
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 179 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 15 |
| Table_open_cache_misses | 15 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)
mysql> show status like 'Table_locks_%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 180 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)

Table_locks_immediate ~ The number of times that table level locks can be obtained immediately , Every time you get a lock immediately , value +1.
Table_locks_waited ~ The number of times that the table level lock cannot be acquired immediately and needs to wait , Every time I wait , This value +1, The high value indicates that there is a serious table level lock contention .


Innodb lock ~ Row lock ( The default support , Watch lock is also supported ).

Introduction to line lock .

Line lock features : deviation InnoDB Storage engine , Spending big , Lock the slow ; A deadlock occurs ; Locking granularity minimum , The lowest probability of lock collisions ; The highest degree of concurrency .

InnoDB And MyISAM There are two big differences : One is to support affairs ; The second is the use of row level lock .


Background knowledge .
Affairs and ACID attribute .

The business is made up of a group of SQL A logical processing unit made up of statements .

The transaction has the following 4 A feature , Short for business ACID attribute .


Atomicity (Atomicity).

A transaction is an atomic unit of operation , Its modification of data , All or nothing , All or nothing .

Uniformity (Consistency).

At the beginning and end of the transaction , Data must be consistent .

Isolation, (Isolation).

The database system provides a certain isolation mechanism , Ensure that transactions are not affected by external concurrent operations “ Independent ” Operation in environment .

persistence (Durability).

After the transaction completes , The modification of the data is permanent .


Problems caused by concurrent transaction processing .
Lost update (Lost Update).

When two or more transactions select the same row , The original transaction modifies the value , It will be overwritten by the value modified by subsequent transactions .

Dirty reading (Dirty Reads).

When a transaction is accessing data , And the data has been modified , This modification has not yet been committed to the database , At this time , Another transaction also accesses this data , And then I used this data .

It can't be read repeatedly (NonRepeatable Reads).

A time after a transaction reads some data , Read the previously read data again , But it was found that it was inconsistent with the data read out before .

Fantasy reading (Phantom Reads).

A transaction re reads the previously queried data according to the same query criteria , However, it is found that other transactions have inserted new data satisfying their query criteria .


Transaction isolation level .

In order to solve the problem of transaction concurrency mentioned above , The database provides a transaction isolation mechanism to solve this problem . The more strict the transaction isolation of the database ,
The less side effects , But the more it costs , Because transaction isolation is essentially the use of transactions to a certain extent “ Serialization ” Conduct , This is obviously related to “ Concurrent ” Is contradictory .

The isolation level of the database is 4 individual , From low to high Read uncommitted、Read committed、Repeatable read、Serializable, These four levels can solve dirty writing one by one 、 Dirty reading 、 It can't be read repeatedly 、 These questions of unreal reading .

Isolation level Lost update Dirty reading It can't be read repeatedly Fantasy reading
Read uncommitted×
Read committed××
Repeatable read( Default )×××
Serializable××××

remarks :√ Represents the possibility of ( The problem cannot be solved ),× Means there won't be ( Can solve the problem ).

  • show variables like ‘tx_isolation’;
mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

InnoDB The row lock mode of .

InnoDB Two types of row locks are implemented .

  • Shared lock (S) ~ Also known as read lock , abbreviation S lock , Shared lock is that multiple transactions can share a lock for the same data , All have access to data , But it can only be read but not modified .

  • Exclusive lock (X) ~ Also known as write lock , abbreviation X lock , Exclusive locks are not allowed to coexist with other locks , For example, a transaction acquires an exclusive lock of a data row , Other transactions can no longer acquire other locks of the row , Including shared lock and exclusive lock , But the transaction of acquiring exclusive lock can read and modify data on line .

about UPDATE、DELETE and INSERT sentence ,InnoDB An exclusive lock will be automatically added to the involved data set (X).

For ordinary SELECT sentence ,InnoDB No locks .

You can add shared lock or exclusive lock to the recordset through the following statement .

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

Case study .
CREATE TABLE `demo_02`.`test_innodb_lock` (
`id` INT(11) NULL,
`name` VARCHAR(45) NULL,
`sex` VARCHAR(1) NULL
) ENGINE=INNODB DEFAULT CHARACTER SET=UTF8;
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('1', '100', '1');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('3', '3', '1');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('4', '400', '0');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('5', '500', '1');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('6', '600', '0');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('7', '700', '0');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('8', '800', '1');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('9', '900', '1');
INSERT INTO `demo_02`.`test_innodb_lock` (`id`, `name`, `sex`) VALUES ('1', '200', '0');
ALTER TABLE `demo_02`.`test_innodb_lock`
ADD INDEX `idx_test_innodb_lock_id` (`id` ASC),
ADD INDEX `idx_test_innodb_lock_name` (`name` ASC);
;

 Insert picture description here

 Insert picture description here

  • Database isolation level ~ Repeatable .

terminal 2 Has not been submitted ,

 Insert picture description here

Here, because auto submit is turned off .

 Insert picture description here

Row lock , Modifying different lines does not affect each other .

 Insert picture description here


Upgrade row lock to table lock ~ ( Index failure ).
  • The index at this point .
mysql> show index from test_innodb_lock\G;
*************************** 1. row ***************************
Table: test_innodb_lock
Non_unique: 1
Key_name: idx_test_innodb_lock_id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 8
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: test_innodb_lock
Non_unique: 1
Key_name: idx_test_innodb_lock_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

 Insert picture description here


The danger of clearance lock .

When we use range conditions , Instead of using equal conditions to retrieve data , And request sharing or exclusive lock ,InnoDB Lock the existing data that meets the conditions . For records where the key value is in the condition range but does not exist , be called “ The gap (GAP)” ,InnoDB It's also about this “ The gap ” Lock , This kind of lock mechanism is called gap lock (Next-Key lock ) .

ID < 10
1
2
3
4
.
6
.
.
9
~ ID by 5 7 8 My line is called gap .

 Insert picture description here


Row lock contention .

show status like ‘innodb_row_lock%’;

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 234330 |
| Innodb_row_lock_time_avg | 33475 |
| Innodb_row_lock_time_max | 51020 |
| Innodb_row_lock_waits | 7 |
+-------------------------------+--------+
5 rows in set (0.01 sec)

Optimization Suggestions .

InnoDB The storage engine implements row level locking , Although in the implementation of locking mechanism, the performance loss may be higher than that of table locking , But it's much better than MyISAM The watch is locked . When the system concurrency is high ,InnoDB The overall performance and MyISAM There will be obvious advantages in comparison .
however ,InnoDB The row level lock also has its weak side , When we don't use it properly , May let InnoDB The overall performance of MyISAM high , Even worse .

  • As far as possible, all data retrieval can be completed through index , Avoid upgrading non indexed row locks to table locks .
  • Design index reasonably , Try to narrow down the range of locks .
  • Minimize index conditions , And index range , Avoid gap locks .
  • Try to control the transaction size , Reduce the amount of locked resources and the length of time .
  • Use low level transaction isolation as much as possible ( But it needs to be met at the business level ).

Commonly used SQL skill .

SQL Writing order .
SELECT [DISTINCT]
select_expr [, select_expr] ...
FROM
<left_table> <join_type>
JOIN
<right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
{col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
HAVING
<where_condition>
ORDER BY
{col_name | expr | position}
[ASC | DESC], ...]
LIMIT
{
[offset,] row_count | row_count OFFSET offset}]
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr] ...
[into_option]
[FROM table_references
[PARTITION partition_list]]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {
[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[into_option]
[FOR UPDATE | LOCK IN SHARE MODE]
into_option: {
INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name] ...
}

Execution order .
[HAVING where_condition]
SELECT [DISTINCT]
select_expr [, select_expr] ...
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
LIMIT
{
[offset,] row_count | row_count OFFSET offset}]

Regular .

select * from tb_book where name regexp ‘^Java’;

Symbol meaning
^ Match at the beginning of a string .
$ Match at the end of the string .
. Match any single character , Include line breaks .
[…] Match any character in parentheses .
[^…] Can't match any characters in parentheses .
a* Match zero or more a( Including empty strings ).
a+ Match one or more a( It doesn't include empty strings ).
a? Match zero or one a.
a1a2
a(m) matching m individual a.
a(m,) Match at least m individual a.
a(m,n) matching m individual a To n individual a.
a(,n) matching 0 To n individual a.
(…) Make pattern elements into a single element .

MySQL Commonly used tools .

《MySQL Commonly used ~ Tools 、 journal .》


MySQL journal .


MySQL Master slave copy .


Comprehensive case .

copyright:author[lyfGeek],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/142/202203140552359705.html