java interview-slow query

Let’s take a look at the interview questions first:

Interview questions: How to judge whether the SQL query operation is slow SQL and how to optimize it (Alibaba interview questions)

Interview question: Open MySQL slow query, statement analysis (Alibaba interview questions)

(Learning video sharing: java teaching video)

1. Open mysql slow query

Method 1: Modify the configuration file

Add a few lines in my.ini:

[mysqlld]
//定义查过多少秒的查询算是慢查询，我这里定义的是2秒
long_query_time=2

#5.0、5.1等版本配置如下选项
log-slow-queries="mysql_slow_query.log"
#5.5及以上版本配置如下选项
slow-query-log=On
slow_query_log_file="mysql_slow_query.log"

//记录下没有使用索引的query
log-query-not-using-indexes

(Related learning video sharing: java interview questions and answers)

Method 2: Enable slow query through MySQL database

mysql>set global slow_query_log=ON
mysql>set global long_query_time = 3600;
mysql>set global log_querise_not_using_indexes = ON;

2. Execute a slow query operation

In fact, it is difficult to execute a meaningful slow query because During my own testing, even querying a massive table with 200,000 pieces of data only took a few seconds. We can use the following statement instead:

SELECT SLEEP(10);

3. Check the number of slow queries
Use the following sql statement to check how many slow queries have been executed:

show global status like '%slow%';

4. Analyze slow queries Query log

Method 1: Analysis through tools

MySQL comes with the mysqldumpslow tool to analyze slow query logs. In addition, there are some useful open source tools.

Assume that the saved log is named long.log

List the 10 sql statements with the most records:

mysqldumpslow -s c -t 10 long.log

List the 10 sql statements that return the most record sets Statement:

mysqldumpslow -s r -t 10 long.log

Method 2: Directly analyze mysql slow query log

# Time: 121017 17:38:54 
# User@Host: root[root] @ localhost [127.0.0.1] 
# Query_time: 3.794217 Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 4194304 
SET timestamp=1350466734; 
select * from wei where text='orange'; 
# Time: 121017 17:46:22 
# User@Host: root[root] @ localhost [127.0.0.1] 
# Query_time: 3.819219  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 4194304 
SET timestamp=1350467182; 
select * from wei where text='long';

Related recommendations: java introductory tutorial

The above is the detailed content of java interview-slow query. For more information, please follow other related articles on the PHP Chinese website!