Query the SQL execution time and view the SQL execution plan. I found that the amount of data was too small and the time gap was not obvious. Here we go. Ten million pieces of data.

1. Data Preparation

1. Create tables and import 10 million data tables and data addresses

2. Import a large amount of data. A Import the database to the server (skip this step if Windows is used)

Create database (DB2), table TB_SKUCopy the code

C Log in to the database using the cli

Mysql -u username -p password;Copy the code

D Switch to the used database

use db2;
Copy the code

E Running Commands

load data local infile '/tmp/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
Copy the code

Explanation of command:‘/ TMP/tb_skU1.sql ‘data directory (Windows directory for example: D:\life\ tb_skU1.sql),tb_sku to import table.

Note:We used INSERT SQL to import data into the database, but it would take a long time to import millions of data into the library.

Second-slow query analysis (find SQL that takes a long time to execute)

2.1 show profiles

Show Profiles is provided by mysql to analyze resource consumption of statement execution in the current session. Can be used for SQL tuning measurements.

2.1.1 Setting MySQL to support profiles

1. Check whether it is supported

select @@have_profiling
Copy the code



The result is YES, which means YES.

2. Check the profiling(Profiling is off by default)

select @@profiling
Copy the code



The result is 0, which means it is not enabled3. Open the profiling

set profiling=1; 
Copy the code

2.1.2 Use of Show Profiles

1. Enter a series of query statements

show databases;
use db01;
show tables;
select * from tb_ksu where id < 5;
select count(*) from tb_ksu;
Copy the code

2. Check the execution time of each SQL query

show profiles; // If profiling is not enabled, run the select @@profiling command.Copy the code



Check the execution time of each SQL entry.

3. Query the execution time of each SQL item in each phase

select profile for query 6; / / 6, on behalf of Query_IDCopy the code



Above explanation

Sending Data The MySQL thread starts accessing the data row and returns the result to the client, not just to the client. In the Sending Data state, the MySQL thread usually performs a large number of disk read operations, so it is the most time-consuming state in the query.Copy the code

4. Check what resources the thread consumes (type all, CPU, block IO, context, switch, Page faults)

show profile cpu for query 7;
Copy the code



The picture above shows

2.2 Querying Logs Slowly

Slow query logs record all SQL logs whose execution time exceeds the value of parameter long_QUERy_time and the number of scan records is not less than min_examined_ROW_LIMIT. Long_query_time indicates 10 seconds, the minimum is 0, and the accuracy can be up to microseconds.

2.2.1 Setting Slow Log Query

1. Modify the configuration file (slow log query is disabled by default). Run the vi /etc/my. CNF command to modify the configuration file and add the following configuration to the bottom of the configuration file

# This parameter is used to control whether the slow query log function is enabled. Slow_query_log_file = slow_query_log # This parameter specifies the file name of the slow query log slow_query_log_file=slow_query.log # This parameter specifies the time limit for the query, beyond which the query is considered slow. 10s long_QUERy_time =10 by defaultCopy the code

2. Restart the mysql service

service mysqld restart
Copy the code

noteIf the following error message is displayed after the command is executed



Run the systemctl restart mysqld.service command

3. View the slow query log directory

cd /var/lib/mysql
Copy the code

2.2.2 Reading Logs

Query the value of long_query_time

show variables like 'long%';
Copy the code



2. Perform query operations

select * from tb_sku where id = '100000030074'\G; Select * from tb_sku where name like '% Meta87384 Pro%'\G;Copy the code

3. Query Slow query logs

A. use the cat

B If there are many slow query logs, use the mysqlDumpslow tool provided by mysql to classify and summarize the logs

Explain execution plan, index usage, and SQL optimization (analyze a PARTICULAR SQL)

After you find inefficient SQL statements in the preceding steps, you can run the EXPLAIN command to obtain information about how Mysql executes Select statements, including how tables are connected during the Select statement execution and the order of the join.

3.1 Run explain commands for analysis

explain select * from tb_sku where id = '100000030074';
Copy the code

Explain select * from tb_sku where name like '%HuaWei mobile Meta87384 Pro%';Copy the code



Execution plan field description

3.2 Description of field Values

1. id

A. The same ID indicates that the table is loaded from top to bottom. B. Id The higher the id value is, the higher the priority is. C. Ids exist at the same time. Those with the same ID can be considered a group and executed from top to bottom. In all groups, the greater the id value, the higher the priority and the earlier the execution.Copy the code

2. select_type



3. type



The results ranged from good to bad

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

system > const > eq_ref > ref > range > index > ALL
Copy the code

4. key

A. Possible_keys: Displays one or more possible indexes that can be applied to the table. B. key: indicates the actual used index. If the value is NULL, no index is used. C. key_len: indicates the number of bytes used in the index. This value is the maximum possible length of the index field, not the actual length. The shorter the length is, the better without losing accuracy.Copy the code

5. rows

Number of scanned rows.Copy the code

6. filtered

This field represents the percentage of the data returned by the storage engine that is left to satisfy the number of records in the query after filtering at the Server layer.Copy the code

note

The next article covers index use and common SQL optimizations.

Recommend the article

Use Netty development, and step to solve the whole process of the pit (with solution source, Netty series) | Denver annual essay WebSocket practical integrated SSL, ali gathering pace into the SSL (security network programming) is an introduction to Netty (Netty)