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

This article introduces the relevant knowledge of "MySql performance optimization case analysis". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Example 1: the best left prefix principle for composite indexes (where... Order by.)

Create the following tables and indexes

CREATE TABLE test03 (A1 INT (4) NOT NULL, a2 INT (4) NOT NULL, a3 INT (4) NOT NULL, a4 INT (4) NOT NULL); ALTER TABLE test03 ADD INDEX idx_a1_a2_a3_a4

The execution plan of the first sql statement is as follows: (the conditions after where are consistent with the index order)

EXPLAIN SELECT a1, AND a3, FROM test03 WHERE a1, AND a2, AND a3, AND a4, AND a4.

The execution plan of the second sql statement is as follows: (the conditions after where are inconsistent with the index order)

EXPLAIN SELECT a1, AND a2, AND a3, FROM test03 WHERE a4, AND a4, AND 3, AND a2, AND 1

If the implementation plans of the above two are exactly the same, why?

Because the second sql is adjusted by the sql optimizer before it is actually executed, it is consistent with the previous one. The first sql is the recommended way to write it.

Take another look at the following sql statement:

EXPLAIN SELECT a1, AND a2, ORDER BY a3, FROM test03 WHERE a1, AND a2, AND 4, ORDER BY 3.

We look at the result of the execution plan: if using where is used, the table query is carried out and the index is invalid. The above sql uses two indexes: A1 Persona2. The two fields do not need to be queried back to the table, so they are using index. However, because A4 is used across columns, the index becomes invalid, so it needs to be queried back to the table, so it is using where. The above fields can be verified by key_length.

Take another look at the following sql statement:

EXPLAIN SELECT a1, AND a1, ORDER BY a3, ORDER BY a3

We see using filesort in the above sql (intra-file sorting, "extra search / sort"); do not use cross-column (where and order by, do not use cross-column)

Take another look at the following sql statement:

EXPLAIN SELECT A1, 2, 3, 3, 4, FROM test03 WHERE, A1, 1, AND, 4, 4, ORDER BY, 2, and 3.

We can see that the continuity is formed by A1 behind where and a2Maga3 behind order by, so there is no using filesort.

Summary:

1. If the composite index and the order of use are all the same (and not across columns), then the composite index is all used. If partially consistent (and not used across columns), partial indexes are used.

2. Where and order by are put together. Do not use them across columns.

Case 2: single table optimization

Create the following table:

CREATE TABLE book (bid INT (4) PRIMARY KEY, NAME VARCHAR (20) NOT NULL, authorid INT (4) NOT NULL, publicId INT (4) NOT NULL, typeid INT (4) NOT NULL); INSERT INTO book VALUES (1 recollection, 1); INSERT INTO book VALUES (2); INSERT INTO book VALUES (3); INSERT INTO book VALUES (4)

Query bid with authorid=1 and typeid of 2 or 3

EXPLAIN SELECT bid FROM book WHERE typeid IN (2 and 3) AND authorid = 1 ORDER BY typeid DESC

We see terrible things (type is ALL) because no indexes are used.

The first step is to optimize and add the index as follows:

ALTER TABLE book ADD INDEX idx_bta (bid,typeid,authorid)

We see that type uses index, a slight improvement, and using index shows the use of overlay indexes and a slight improvement.

Further optimization, according to the leftmost prefix principle, note: unused indexes should be deleted to avoid interference.

ALTER TABLE book ADD INDEX idx_bta1 (typeid,authorid,bid)

Further optimize, because the range query typeid in (2d3) sometimes fails, so we put typeid at the end:

ALTER TABLE book ADD INDEX idx_bta1 (authorid,typeid,bid); EXPLAIN SELECT bid FROM book WHERE authorid = 1 AND typeid IN (2) ORDER BY typeid DESC

We can see that type has improved by two levels, and directly index has become ref.

You can take a look at the simultaneous emergence of using where and using index. Why?

Using where represents back to the original table query, using index represents not to return to the original table query, because type in (2p3) makes the index invalid, so return to the original table query.

For example, the following statement is not used

EXPLAIN SELECT bid FROM book WHERE authorid = 1 AND typeid = 2 ORDER BY typeid DESC

It is proved again by key_len that in can invalidate the index.

Summary:

a. The best left prefix to maintain the consistency of the definition and use of the index

b. The index needs to be optimized step by step

c. Put the range query with in at the end of the where condition to prevent invalidation

Case 3: two-table optimization

Create two tables as follows:

CREATE TABLE teacher2 (tid INT (4) PRIMARY KEY, cid INT (4) NOT NULL)

INSERT INTO teacher2 VALUES (1); INSERT INTO teacher2 VALUES (2); INSERT INTO teacher2 VALUES (3)

CREATE TABLE course2 (cid INT (4), cname VARCHAR (20))

INSERT INTO course2 VALUES (1); INSERT INTO course2 VALUES (2); INSERT INTO course2 VALUES (3)

Look at the following sql query:

SELECT * FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = 'java'

Suppose 10 pieces of data in table t and 300 pieces of data in table c

Now there is a question, which table should the index be added to?

Specification: small tables drive large tables, indexes are based on frequently used fields

From the number of t table and c table, it can be concluded that the cid of t table is used frequently, and the cid of t table is used 300 times at a time, so it is used frequently, so the cid of t table is indexed. (generally speaking, for left outer join, index the left table; right outer join, index right table)

When writing.. on t.cid = c.cid, put the table with a small amount of data on the left (assuming that the amount of data in the t table is small at this time)

Indexless optimization looks at the following execution plan:

You can see the Using join buffer in extra, which means that sql is too poorly written to read MySql. The effect is that the MySql engine uses connection caching.

Based on the above rules, we index the cid in the t table

ALTER TABLE teacher2 ADD INDEX index_teacher2_cid (cid)

It can be seen that the t-table has been upgraded several levels.

Generally speaking, the fields after where should be indexed.

ALTER TABLE course2 ADD INDEX index_course2_cname (cname)

It can be seen that the c table has also been upgraded several levels.

To sum up, it is the optimization process of two-table query.

Case 4: three tables optimization

The rules are as follows:

a. A small table drives a large table.

b. The index is based on fields that are frequently queried.

It can be optimized according to the optimization rules of single table and two tables.

This is the end of the content of "MySql performance Optimization case Analysis". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.