Basic operation of mysql sql statement

@[TOC]

mysql basic addition, deletion, and modification check (select, update, delete, insert)

The difference between mysql left join, right join, inner join and full join (left join, right join, ininer join)

MySQL aggregate functions (max, min, avg, sum)

mysql paging and group by

mysql subquery

What is the execution order of a SQL

sql optimization

Slow positioning query

The efficiency of joint query is higher. Examples to illustrate the advantages and usage of joint query: inner join, left outer join, right outer join, full outer join.

The efficiency of joint query is higher. The following examples illustrate the benefits of joint query (inline, left-join, right-join, full-join):

T1 table structure
(user name, password)

userid
(int)

username
varchar(20)

password 
varchar(20)

 

1

jack

jackpwd

 

2

owen

owenpwd

 

T2 table structure
(user name, password)

userid
(int)

jifen
varchar (20)

dengji 
varchar (20)

 

1

20

3

 

3

50

6

First: inner join.

If you want to list user information, points, and levels, you will generally write: select * from T1, T3 where T1.userid = T3.userid (in fact, this result is equivalent to select * from T1 inner join T3 on T1. userid=T3.userid ).

Combine the rows with userid in the two tables into one line (ie inline), but the efficiency of the latter will be much higher than that of the former. It is recommended to use the latter (inline).

SQL statement: select * from T1 inner join T2 on T1.userid=T2.userid

operation result

T1.userid

username

password

T2.userid

jifen

dengji

 

1

jack

jackpwd

1

20

3

Second: left outer join .

Display all rows in the left table T1, and add the conditions in the right table T2 to the left table T1; if the conditions in the right table T2 do not meet the conditions, there is no need to add them to the result table, and NULL indicates.

SQL statement: select * from T1 left outer join T2 on T1.userid=T2.userid

operation result

T1.userid

username

password

T2.userid

jifen

dengji

 

1

jack

jackpwd

1

20

3

 

2

owen

owenpwd

NULL

NULL

NULL

Third: right outer join.

Display all rows in the right table T2, and add the conditions in the left table T1 to the right table T2; if the conditions in the left table T1 are not met, you do not need to add them to the result table, and NULL indicates.

SQL statement: select * from T1 right outer join T2 on T1.userid=T2.userid

operation result

T1.userid

username

password

T2.userid

jifen

dengji

 

1

jack

jackpwd

1

20

3

 

NULL

NULL

NULL

3

50

6

Fourth: full outer join.

Display all rows on both sides of the left table T1 and the right table T2, that is, combine the left-joined result table + the right-joined result table, and then filter out the duplicates.

SQL statement: select * from T1 full outer join T2 on T1.userid=T2.userid

operation result

T1.userid

username

password

T2.userid

jifen

dengji

 

1

jack

jackpwd

1

20

3

 

2

owen

owenpwd

NULL

NULL

NULL

 

NULL

NULL

NULL

3

50

6

To sum up, the efficiency of joint query is indeed relatively high. If the four joint methods can be used flexibly, basically the complex sentence structure will be simpler. The 4 ways are: 1) Inner join 2) left outer join 3) right outer join 4) full outer join

①The commonly used aggregate function
count(col) : Indicates the total number of rows in the specified column
max(col) : Indicates the maximum value of the specified column
min(col) : Indicates the minimum value of the specified column
sum(col) : Indicates the specified column的
和avg(col) : Means to find the average value of the specified column

②Find the total number of rows
-return the total number of rows of non-NULL data
select count(height) from students;
-return the total number of rows, including the null value record
select count(*) from students;

③Find the maximum value
-query the maximum number of girls
select max(id) from students where gender = 2;

④Find the minimum value
-query the minimum number of undeleted students
select min(id) from students where is_delete = 0;

⑤Summation
-query the total height of boys
select sum(height) from students where gender = 1;-average
height
select sum(height) / count(*) from students where gender = 1;

⑤Find the average value
-find the average height of boys, the aggregate function does not count the null value, the average height is wrong
select avg(height) from students where gender = 1; -find
the average height of boys, including the height of null
select avg(ifnull (height,0)) from students where gender = 1;
Description: ifnull function: means to determine whether the value of the specified field is null, if it is empty, use the value provided by yourself-
the characteristics of the
aggregate function The aggregate function ignores the field by default If you want records with null column values ​​to participate in the calculation, you must use the ifnull function to replace the null values.

--------Summary-------------
count(col): Indicates the total number of rows in the specified column
max(col): Indicates the maximum value of the specified column
min(col) : Means to find the minimum value of the specified column
sum(col):
Means to find the sum of the specified column avg(col): Means to find the average value of the specified column
————————————————
Limit Pagination:

select * from t (pageNumber-1)*pageSize, in short, the front end only needs to tell us which page we need, and how many pieces of data are displayed on each page.

For example: select * from t limit 0,10, which means: query the data of the first page, each page will display 10

group by (with deduplication function):

Give Zhang such a table:

Check with this sql:

SELECT name FROM test GROUP BY name

The result is shown as follows:

Subquery:

IN subquery

Comparison operator subqueries

EXIST subquery

f rom type subquery (the inner query result is regarded as a temporary table for the outer SQL to query again. The query result set can be treated as a table. The temporary table should use an alias.)

where type subquery (use the inner query result as the comparison condition of the outer query)

sql optimization:

1. Use subquery optimization

select * from orders_history where type=8 limit 100000,1;select id from orders_history where type=8 limit 100000,1; select * from orders_history where type=8 and id>=(select id from orders_history where type=8 limit 100000,1) limit 100; select * from orders_history where type=8 limit 100000,100;

The query time for the 4 statements is as follows:

The first sentence: 3674ms
The second sentence: 1315ms
The third sentence: 1327ms
The fourth sentence: 3710ms The
above query requires attention:

Compare the first statement and the second statement: use select id instead of select * The speed has increased by 3 times.
Compare the second statement and the third statement: the speed difference is tens of milliseconds.
Compare the third statement and the fourth statement: get Thanks to the increase in select id speed, the query speed of the third statement has been increased by three times
. Compared with the original general query method, this method will be several times faster.

① SQL statement and index optimization
SQL statement optimization:
1, try to avoid the use of sub-queries


2. Avoid functional indexes


3. Replace OR with IN


    In addition, MySQL has made corresponding optimizations for IN, that is, all the constants in IN are stored in an array, and this array is sorted. However, if the value is large, the consumption will be relatively large. Another example: select id from table_name where num in(1,2,3) For continuous values, do not use in if you can use between; or use connection to replace.

4. LIKE prefix% sign, double percent sign, _underscore to query non-indexed columns or * cannot use the index, if the query is an indexed column, you can


5. Read the appropriate record LIMIT M, N, instead of reading redundant records
select id, name  
from table_name limit 866613, 20
When using the above SQL statement for paging, someone may find that as the amount of table data increases, Directly using limit paging query will become slower and slower.

The optimization method is as follows: you can take the id of the maximum number of rows on the previous page, and then limit the starting point of the next page according to this maximum id. For example, in this column, the largest id on the previous page is 866612. sql can be written as follows:

select id,name from table_name  
where id> 866612 limit 20

6. Avoid data type inconsistency.
7. Group statistics can prohibit sort sort, and sum query can prohibit row reuse union all


The main difference between union and union all is that the former needs to combine the result set and then perform unique filtering operation, which will involve sorting, increase a lot of CPU operations, increase resource consumption and delay. Of course, the prerequisite for union all is that there is no duplicate data in the two result sets. Therefore, it is generally recommended to use union all to increase the speed when we clearly know that there will be no duplicate data.

In addition, if the index is not used in the sort field, sort as little as possible;

8. Avoid random fetching of records


9, prohibit unnecessary ORDER BY sorting


10. Bulk INSERT


11. Don't use negative query conditions such as NOT.
You can imagine that for a B+ tree, the root node is 40. If your condition is equal to 20, go to the left to check, and if your condition is equal to 50, go to the right to check. But your condition is not equal to 66, what should the index do? I didn't know it after traversing it.

12. Try not to use select *
SELECT * to increase a lot of unnecessary consumption (cpu, io, memory, network bandwidth); increase the possibility of using a covering index; when the table structure changes, the former also needs to be updated frequently. So it is required to connect the field name directly after the select.

13. Distinguish between in and exists
select * from table A  
where id in (select id from table B) The
above sql statement is equivalent

select * from table A  
where exists(select * from table B where table B.id=table A.id)
distinguishing between in and exists is mainly caused by the change of the driving sequence (this is the key to performance changes), if it is exists, then The outer table is the driving table and is accessed first. If it is IN, the subquery is executed first. Therefore, IN is suitable for the case where the outer surface is large and the inner surface is small; EXISTS is suitable for the case where the outer surface is small and the inner surface is large.

14. Optimize the Group By statement.
If there is no ordering requirement for the results of the group by statement, add order by null after the statement (group will sort by default);

Try to use the index of the above table in the group by process, and confirm that there is no Using temporary and Using filesort in the explain result;

If the amount of data to be counted by group by is not large, try to use only memory temporary tables; you can also increase the tmp_table_size parameter appropriately to avoid using disk temporary tables;

If the amount of data is too large, use the hint SQL_BIG_RESULT to tell the optimizer to directly use the sorting algorithm (directly use the disk temporary table) to get the result of the group by.

Slow query positioning

1. Introduction

Enabling the slow query log allows MySQL to record queries that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized.

Two, parameter description

slow_query_log Slow query on state
slow_query_log_file The location where the slow query log is stored (this directory needs the writable permission of the MySQL running account, and is generally set to the MySQL data storage directory)
long_query_time How many seconds to record the query

Three, setting steps

1. View slow query related parameters

Copy code

mysql> show variables like'slow_query%';+---------------------------+--------------------- -------------+| Variable_name | Value |+---------------------------+--------------------- -------------+| slow_query_log | OFF || slow_query_log_file | /mysql/data/localhost-slow.log |+---------------------------+--------------------- -------------+mysql> show variables like'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

Copy code

2. Setting method
Method 1: Global variable setting
Set slow_query_log global variable to "ON" state

mysql> set global slow_query_log='ON';

Set the storage location of slow query logs

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

Query more than 1 second to record

mysql> set global long_query_time=1;

Method 2: Configuration file settings
Modify the configuration file my.cnf and add it under [mysqld]

[mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.loglong_query_time = 1

3. Restart the MySQL service

service mysqld restart

4. View the parameters after setting

Copy code

mysql> show variables like'slow_query%';+---------------------+--------------------------- -----+| Variable_name | Value |+---------------------+--------------------------- -----+| slow_query_log | ON || slow_query_log_file | /usr/local/mysql/data/slow.log |+---------------------+--------------------------- -----+mysql> show variables like'long_query_time';+-----------------+----------+| Variable_name | Value |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+

Copy code

Four, test

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check whether the slow query log is generated

ls /usr/local/mysql/data/slow.log

If the log exists, MySQL has successfully started the slow query setting!