Database Tuning Tutorial (i) preface & slow query definition

Objective

Recently help the company to optimize the database, with the previous learning, step-by-step learning knowledge for practice, finally is the speed rub up, a query originally to execute more than 1 minutes now only need 3 seconds.

Now the study of what they think and use to summarize, on the one hand to consolidate knowledge, on the other hand to the vast number of students to reference.

This set of tutorials is a total of 13 speaking.

In this series of tutorials, you will learn

1. How to optimize a database step-by-step

2. How to bring the role of the index to the fullest

3. How to troubleshoot MySQL not using the index in the case of like '%xxx% ' fuzzy queries

4. How to design a high-quality table

5. How to write high-quality SQL statements

6. Other methods of database optimization

First, let's start by discovering slow queries.

First, find slow query

How to quickly position execution of slow statements from a large project is a problem that will be addressed in this chapter.

1. Definition of slow query

What kind of query is a slow query, is there a quantitative standard?

Slow query definition

A slow query is an SQL statement that executes longer than a slow query time.

How to view slow query times

[Plain]View Plaincopy

1. Show variables like ' long_query_time ';

You can display the current slow query time. MySQL default slow query time is 10 seconds

You can modify the definition of a slow query by using the following statement

[Plain]View Plaincopy

1. Set global long_query_time=1;

(If your MySQL is set to cache, you will need to re-enter the command-line window to detect changes)

Note that this statement deliberately adds a global to the variable, indicating that the setting is valid for the entire MySQL, whereas the modifier before the variable by default is the session, which is only valid for the current window.

This is only a start, and next, we will prepare the data for the slow query, that is, to create a large table.

Database Tuning Tutorial (i) preface & slow query definition