@SQL skill improvement

MySQL index

At present, I'm a junior. I want to be engaged in data warehouse development, but the ability of data development is also particularly high in the market,
I also interviewed many small companies and agreed that I didn't have a deeper understanding of the workflow and project expansion of the whole project
 And the problems encountered in the project.
The foundation is not particularly solid, so I hope to write a blog to record my usual practice SQL Ability improvement notes.

Welcome to criticize and correct, thank you for your browsing and clicking...
W3 school sql query dictionary

Use of index

Create index

select * from tb_item; -- 3000000 Data bar
id: primary key 
title: Without index, the query efficiency is relatively low

Create index
create index indx_tem_title on tb_item;
The underlying database is creating an index

Indexes are one of the most efficient ways to optimize queries
 Creating an index can improve query efficiency and make rational use of the index

How to avoid index invalidation

Create federated index
create index name on table(column)
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

explain To analyze SQL Query efficiency of engine
1,Weight matching
select * from tb_seller where name='Xiaomi Tech ' and status='1' and address = 'Xi'an';

mysql> explain select * from tb_seller where name='Xiaomi Tech ' and status='1' and address = 'Xi'an';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref               | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

Most sitting front rule

Start with the leftmost column, and there are no skipped columns in the middle
explain select * from tb_seller where name='Xiaomi Tech ';

mysql> explain select * from tb_seller where name='Xiaomi Tech ';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

explain select * from tb_seller where name='Xiaomi Tech ' and status='1';

mysql> explain select * from tb_seller where name='Xiaomi Tech ' and status='1';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | const,const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)


Query is not in name Start with status start
explain select * from tb_seller where status='1' and address = 'Xi'an';

mysql> explain select * from tb_seller where status='1' and address = 'Xi'an';
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tb_seller | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Index order changes
explain select * from tb_seller where address = 'Xi'an' and name = 'Xiaomi Tech ' and status='1' ;

mysql> explain select * from tb_seller where address = 'Xi'an' and name = 'Xiaomi Tech ' and status='1' ;
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref               | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)


The index query is the same as where The order of the conditions is irrelevant
 Leftmost column, no jumping
explain select * from tb_seller where name = 'Xiaomi Tech ' and address = 'Xi'an' ;

mysql> explain select * from tb_seller where name = 'Xiaomi Tech ' and address = 'Xi'an' ;
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Just go name The index, similar to the operation of climbing stairs, can only be used step by step

Invalid range query

explain select * from tb_seller where name='Xiaomi Tech ' and status > '1'  and address = 'Beijing';

mysql> explain select * from tb_seller where name='Xiaomi Tech ' and status > '1'  and address = 'Beijing';
+----+-------------+-----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys            | key                      | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
|  1 | SIMPLE      | tb_seller | range | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | NULL |    1 | Using where |
+----+-------------+-----------+-------+--------------------------+--------------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

The length of the index has changed
 Just go name,status，The index of the field after the range query is invalid, status，invalid

Do not operate on the index
explain select * from tb_seller where name = 'Xiaomi Tech ';

mysql> explain select * from tb_seller where name = 'Xiaomi Tech ';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

explain select * from tb_seller where substring(name,3,2) = 'Xiaomi Tech ';

mysql> explain select * from tb_seller where substring(name,3,2) = 'Xiaomi Tech ';
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tb_seller | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.03 sec)

If calculated on the above index, the index will become invalid

No single quotation mark is added to the string, and the index is invalid
explain select * from tb_seller  where name = 'Xiaomi Tech ' and status = 1;

mysql> explain select * from tb_seller  where name = 'Xiaomi Tech ' and status = 1;
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

403 yes name Index length of

explain select * from tb_seller  where name = 'Xiaomi Tech ' and status = '1';

mysql> explain select * from tb_seller  where name = 'Xiaomi Tech ' and status = '1';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref         | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | const,const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

Detected status yes string If the field of type is not quoted, the bottom layer will be a hermit type
 If the underlying layer operates on the index, the index will become invalid

String type, with single quotation marks

Use overlay index
mysql> explain select * from tb_seller where name = 'Xiaomi Tech ';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Extra       

mysql> explain select name,status,address from tb_seller where name = 'Xiaomi Tech ';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 | Using where; Using index |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

or Failure problem

explain select * from tb_seller where name = 'Xiaomi Tech ' or nickname = ''Xiaomi official flagship store;
mysql> explain select * from tb_seller where name = 'Xiaomi Tech ' or nickname = 'Xiaomi official flagship store';
+----+-------------+-----------+------+--------------------------+------+---------+------+------+-------------+
| id | select_type | table     | type | possible_keys            | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+------+--------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tb_seller | ALL  | idx_seller_name_sta_addr | NULL | NULL    | NULL |   12 | Using where |
+----+-------------+-----------+------+--------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

use and The index is valid. Just go name Indexes

Index failure summary

 1. Full value matching, specifying specific values for all columns in the index
 2. Leftmost prefix rule
 3. The column on the right of range query cannot use index
 4. Do not operate on the index column. The index will become invalid
 5. The string is not enclosed in single quotation marks, resulting in index invalidation
 6. Try to use overlay indexes and avoid them select * ,Use overlay indexes whenever possible
 Queries that only access indexes (index columns, full query columns), reduce select * 
 Commonly used query columns can be added to a composite index, otherwise they will be used confition where Will use back to table query
 7. or If the index fails, it will be effective only if there is an index
 8. like Fuzzy matching problem,%If the number is added to the front, it will become invalid
 Use overlay index to solve this problem. The query includes primary key index, simple index and composite index
 explain select sellerid,name,status,address from tb_seller where name like '%science and technology%';
 explain select sellerid,name,status,address,password from tb_seller where name like '%science and technology%';
 The second one is to overwrite the index without going through the index. The query only includes the index

Extra

1. using index: It will appear when using overlay index
2. using where: When the index is used for searching, you need to go back to the table to query the required data
3. using index condition: The index is used in the search, but the data needs to be queried back to the table
4. using index & using where :  The search uses the index, but the required data can be found in the index column. The index does not need to query the data back to the table

Pay attention to MySQL performance

1. The problem that full table scanning is faster than indexing
2.  If the full table scan is fast, the index will not be used, is null The index takes effect, is not null Index failure
	is null or is not null ，First, I will judge the fast problem of full table and index
	It is related to the amount of data. The small one is indexed
3. in and not in
	in Go to the index, not in No index
mysql> explain select * from tb_seller where sellerid in ("oppo","xiaomi","sina");
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | tb_seller | range | PRIMARY       | PRIMARY | 402     | NULL |    3 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Single column index and composite index

Try to use composite index and less single column index. Composite index is equivalent to creating multiple indexes
 If a single column index is used, when multiple fields are queried, only the best index will be used.
mysql> show index from tb_seller;
+-----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_seller |          0 | PRIMARY                  |            1 | sellerid    | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| tb_seller |          1 | idx_seller_name_sta_addr |            1 | name        | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_seller |          1 | idx_seller_name_sta_addr |            2 | status      | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_seller |          1 | idx_seller_name_sta_addr |            3 | address     | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_seller |          1 | index_tb_seller_address  |            1 | address     | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> drop index index_tb_seller_address on tb_seller;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

explain select * from tb_seller where name = 'Xiaomi Tech ' and status = '0' and address = 'Xi'an';

mysql> explain select * from tb_seller where name = 'Xiaomi Tech ' and status = '0' and address = 'Xi'an';
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
| id | select_type | table     | type | possible_keys            | key                      | key_len | ref               | rows | Extra       |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 | Using where |
+----+-------------+-----------+------+--------------------------+--------------------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

Delete composite index

mysql> drop index idx_seller_name_sta_addr on tb_seller;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Create three single column indexes
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

mysql> show index from tb_seller;
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_seller |          0 | PRIMARY            |            1 | sellerid    | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| tb_seller |          1 | idx_seller_name    |            1 | name        | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_seller |          1 | idx_seller_status  |            1 | status      | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_seller |          1 | idx_seller_address |            1 | address     | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

This only takes name This index, name Indexes are optimal, rather than using all indexes
 It mainly depends on whether the recognition degree is the highest. For single column index combined query, it is easiest to use that index
mysql> explain select * from tb_seller where name = 'Xiaomi Tech ' and status = '0' and address = 'Xi'an';
+----+-------------+-----------+------+------------------------------------------------------+-----------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys                                        | key             | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+------------------------------------------------------+-----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | tb_seller | ref  | idx_seller_name,idx_seller_status,idx_seller_address | idx_seller_name | 403     | const |    1 | Using where |
+----+-------------+-----------+------+------------------------------------------------------+-----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

View index usage

Index usage for the current session
mysql> show status like "Handler_read%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 23    |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 16    |
+-----------------------+-------+
7 rows in set (0.01 sec)


View global index usage
mysql> show  global status  like "Handler_read%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |
| Handler_read_key      | 42    |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 48    |
+-----------------------+-------+
7 rows in set (0.00 sec)

Introduction to SQL index indicators

Handler_read_first  ---->   The number of times the first entry in the index is read. If it is higher, it is marked
 The server is performing a large number of full table index scans (the lower the value, the better)

Handler_read_key  ---->  If the index is working, this value represents a row being retrieved
 The number of times the index value is read. If the value is lower, it indicates that the performance improvement of the index is not high, because the index is not often used
 The higher the value, the better

Handler_read_next  ---->  according to key The number of requests to read the next line in sequence, if you use
 This value is incremented by a range constraint or if an index scan is performed to query index columns

Handler_read_prev  ---->  The number of requests to read the previous line in key order
 Mainly used for optimization order by ... desc

Handler_read_rnd  ---->  The number of requests to read a line according to a fixed position, if you are executing
 A large number of queries and the results need to be sorted. This value is high, and you may have used a large number of queries MySQL Scan entire
 The table query or your connection does not use the key correctly. This value is high, which means that the operation efficiency is low and should be established
 Index to remedy

Handler_read_rnd_next  ---->  The number of requests to read the next line in the data file, if you
 A large number of table scans are in progress. A high value usually indicates that your table index is incorrect or the written query is not valid
 Use index

dba

Insert data for optimization

Bulk insert data

Table creation

CREATE TABLE `tb_user_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(45) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(45) NOT NULL,
`birthday` datetime DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`qq` varchar(32) DEFAULT NULL,
`status` varchar(32) NOT NULL COMMENT 'User status',
`create_time` datetime NOT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

View import effect

-- Import ordered data
mysql> load data local infile 'E:\\bigdata\\highSkill\\sql1.log' into table tb_user_1 fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected, 65535 warnings (31.28 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 4000000

-- Import unordered data
mysql> load data local infile 'E:\\bigdata\\highSkill\\sql2.log' into table tb_user_2 fields terminated by ',' lines terminated by '\n';
Query OK, 1000000 rows affected, 65535 warnings (1 min 4.69 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 4000000

Summary: before inserting data in batch, make the data orderly, which can provide insertion efficiency

Turn off uniqueness check

set unique_checks=0; -- close
set unqiue_checks=1; -- open

Manually commit transactions

set autocommit=0; -- Turn off auto submit
set autocommit=1; -- Turn on auto submit

Optimize insert statement

Insert multiple pieces of data at one time

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

Manually commit 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;

Primary key sequential insertion

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');

Order by optimization

Create table structure

CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');

create index idx_emp_age_salary on emp(age,salary);

View index

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         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_age_salary |            1 | age         | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_age_salary |            2 | salary      | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

Two sorting methods of mysql

filesort sort

mysql> explain select * from emp order by age;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

According to age,salary

mysql> explain select * from emp order by age,salary;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

Summary: sorting through multi field, single field, index and composite index is based on fileSort, which is very inefficient

using index

mysql> explain select id from emp order by age;
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key                | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 | Using index |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> explain select id ,age,salary from emp order by age;
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key                | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 | Using index |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)


mysql> explain select id ,age,salary from emp order by age,salary;
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key                | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 | Using index |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Summary: when sorting, either all are in ascending order or all are in descending order, so the efficiency should be higher
If you sort by multiple fields, the sorting order should be consistent with the index order. Try to use the using index for sorting

In the development process, the emergence of fileSort cannot be avoided. How to optimize fileSort

fileSort optimization
1) Twice scanning algorithm, on MySQL4 1. Before sorting with this algorithm, first take out the sorting field and row pointer according to the conditions, and then sort in the sort buffer. If the sort buffer is not enough, store the sorting results in the temporary table in the temporary table. After sorting, read the records back to the table according to the row pointer. This operation may lead to a large number of random I/O operations

2) One scan algorithm: take out all the fields that meet the conditions at one time, and then sort them in the sort buffer, and then directly output the result set. The memory overhead is large during sorting, but the sorting efficiency is higher than that of the two scan algorithm.

MySQL compares the variable max_ length_ for_ sort_ The size of data and the total size of the fields taken out by the Query statement to determine whether it is the sort algorithm. If Max_ length_ for_ sort_ If the data is larger, use the one-time scanning algorithm, otherwise use the two-time scanning algorithm.

Sort can be improved appropriately_ buffer_ Size and max_length_for_sort_data system variable to increase the size of the sorting area and 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)

Optimization of group by statement

Since group by actually performs sorting operations, and compared with order by, group by mainly performs grouping operations after sorting. Of course, if other aggregation functions are used during grouping, some aggregation function calculation is also required. Therefore, in the implementation process of group by, the index can also be used like order by
If the query includes group by but the user wants to avoid the consumption of sorting results, you can execute order by Null to prohibit sorting.

mysql> explain select age,count(*) from emp group by age;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

mysql> explain select age,count(*) from emp group by age;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)

mysql> explain select age,count(*) from emp group by age order by null;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)

The temporary table is used, and the index is used for query

mysql> create index inde_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 | type  | possible_keys | key                 | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | index | NULL          | inde_emp_age_salary | 9       | NULL |   12 | Using index |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Subquery optimization

Finite or condition

To prevent index invalidation, composite indexes cannot be used, and single column indexes must be used
Use union to replace or, and the type is range

Paging condition query

Application optimization

Database connection pool

Reduce access to MySQL

• One time read
• Add cache layers redis, Memcache, mybatis and hibernate

load balancing

• Use MySQL replication to complete streaming query (master-slave replication, read-write separation)
• Using distributed database architecture

Query cache optimization of MySQL

Cache parameter configuration

SHOW VARIABLES LIKE 'have_query_cache';
SHOW VARIABLES LIKE 'query_cache_type'; 
SHOW VARIABLES LIKE 'query_cache_size';
SHOW STATUS LIKE 'Qcache%';

SQL exercise record

Use of windowing function

SQL JOIN

join name	Difference description
Join	If there is a match in the table, the row is returned
Left Join	Even if there is no match in the right table, all rows are returned from the left table
Right	Even if there is no match in the left table, all rows are returned from the right table
full join	As long as there is a match in one of the tables, the row is returned

Sort window
Sort function row_number，rank，dense_ The difference between rank

Sort name	Difference description
row_number	There is no juxtaposition
dense_rank & rank	If there is juxtaposition, rank will jump sort
row_number	Regardless of juxtaposition, even if the order is the same, the order is a natural number of continuity
dense_rank & rank	Considering the situation of juxtaposition, the difference is that rank is very jumping. The number of juxtaposed sorting will affect the next ranking, which is represented by the interruption of numbers, while deny_ Rank is continuous no matter how many are arranged side by side

sort

1. In the employees table, output first_ Name ranking (sorted in ascending order by first_name) is the first of odd ranking_ name.

Problem solving ideas:
	View table structure
		emp_no|int|NO|PRI|None|
		birth_date|date|NO||None|
		first_name|varchar(14)|NO||None|
		last_name|varchar(16)|NO||None|
		gender|char(1)|NO||None|
		hire_date|date|NO||None|
	yes first_name Sort and output first_name，rank
		select first_name,row_number() over(order by first_name asc) as r_num from employees;
	Modify the original table join operation
		select e.first_name
		from employees e join
		(
			select first_name,row_number() over(order by first_name asc) as r_num
			from employees
		) t
		on e.first_name = t.first_name
		where t.r_num % 2 = 1;

dense_rank()

2. Passing according to the number of questions_ In the number table, the ranking of the passed questions is the same, in ascending order

Analytical ideas:
	The first is sorting. I just want to use it rank(),row_number(),dense_rank()Window function to realize the sorting process
	Because it is a continuous and uninterrupted operation, parallel operation must be considered. Therefore, this time, it is used dense_rank()

SQL to write:
	select id,number,dense_rank() over(order by number desc) as t_rank
	from passing_number;	

Whether in the process of interview or in the realization of requirements, sorting and windowing are often used by us. Generally, we use rank_number() and deny_ There are many rank() functions...