Database Tuning tutorial (1) preface & amp; definition of slow queries, Database Tuning tutorial Preface

Database optimization tutorial (I) preface & definition of slow query, and introduction of database optimization tutorial

Preface

 

Recently, I helped the company optimize the database. Based on what I learned, I used the learning knowledge step by step in practice, and finally I got the speed up, it takes only three seconds for a query to be executed for more than one minute.

I will summarize what I have learned and what I have used. On the one hand, I will consolidate my knowledge for myself, and on the other hand, I will give some reference to my colleagues.

This tutorial covers a total of 13 lectures.

In this series of tutorials, you will learn

1. How to optimize the database step by step

2. How to make full use of Indexes

3. How to solve the problem that Mysql does not use indexes when like '% xxx %' fuzzy queries

4. How to design a high-quality table

5. How to Write high-quality SQL statements

6. other methods for database Optimization

 

First, let's start with discovering slow queries.

1. Slow query found

This section describes how to quickly locate slow statements in a large project.

1. Definition of slow Query

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

Slow query Definition

Slow query refers to the SQL statement whose execution time exceeds the slow query time.

How to view slow query time

[Plain]View plaincopy

1. Show variables like 'long _ query_time ';

The current slow query time is displayed. MySql Default slow query time is 10 seconds

You can use the following statement to modify the definition of a slow query:

[Plain]View plaincopy

1. Set global long_query_time = 1;

(If the cache is set for your mysql instance, you need to enter the command line window again to find out the changes)

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

This is just an example. Next, we will prepare data for slow queries, that is, to create a large table.