Shulou(Shulou.com)06/01 Report--

This article mainly explains "what is the Sql optimization method of MySql". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the Sql optimization method of MySql"?

1. Sql optimization

Reasons: low performance, long execution time, long waiting time, poor SQL statements (join query), index failure, unreasonable server parameter settings (buffer, number of threads)

Process:

Select.. From.. Join.. On.. Where.. Group by.. Having.. Order by.. Limit.

Parsing:

From.. Join.. On.. Where.. Group by.. Having.. Select.. order by limit

Sql optimization, mainly to optimize the index

1) disadvantages of index:

1. The index itself is very large.

two。 Indexes are not used in all cases: a. A small amount of data b. The frequently updated field c. Rarely used fields

3. The index will reduce the efficiency of additions, deletions and modifications.

2) advantages of index:

1. Improve query efficiency (reduce IO usage)

two。 Reduce CPU usage (for example:. Order by age desc..., because B + Tree itself is a sorted structure, so it can be used directly in sorting)

3) Classification of the index:

1. Primary key index: cannot be repeated. Id cannot be Null.

two。 Unique index: cannot be repeated. Id can be Null.

3. Single-valued index: single column, age; a table can have multiple single-valued indexes, name

4. Composite index: an index consisting of multiple columns (equivalent to a secondary directory)

4) create an index:

Method 1: create index type index name on table (field)

1. Single-valued index:

Create index dept_index on tb (dept)

two。 Unique index:

Create unique index name_index tb (name)

3. Composite index:

Create index dept_name_index on tb (dept, name)

Method 2: alter table table name add index type index name (field)

1. Single-valued index:

Alter table tb add index dept_index (dept)

two。 Unique index:

Alter table tb add unique index name_index (name)

3. Composite index

Alter table tb add index dept_name_index (dept, name)

Note: if a field is primary key, the field is the primary key index by default

5) Delete the index:

Drop index index name on table name

Drop index name_index on tb

6) query index:

Show index from tb

7) SQL performance issues

a. Analyze the execution plan of SQL: explain, which can simulate the SQL optimizer to execute SQL statements

B. MySql query optimization will interfere with our optimization

At this point, I believe you have a deeper understanding of "what is the Sql optimization method of MySql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.