Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQL slow SQL how to optimize", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "MySQL slow SQL how to optimize"!

Index is similar to university library bibliography index, which can improve the efficiency of data retrieval and reduce the IO cost of database. MySQL performance began to decline around 3 million records, although the official documentation said 500~800w records, so large data volume index is very necessary.

MySQL provides Explain, which is used to display the details of SQL execution and can optimize the index.

1. Reasons for slow SQL execution

1. Hardware issues. Such as slow network speed, insufficient memory, low I/O throughput, full disk space, etc.

2. There is no index or the index is invalid. (Typically in Internet companies, DBAs lock tables in the middle of the night and rebuild indexes, because when you delete data, the tree structure of the index is incomplete.) So what Internet company's data does is false delete. One is to do data analysis, and the other is not to destroy the index)

3. Too much data (separate database and table)

4. Server tuning and various parameter settings (adjust my.cnf)

Second, when analyzing the reasons, we must find an entry point.

1. First observe, open the slow query log, set the corresponding threshold (for example, more than 3 seconds is slow SQL), and after running for a day in the production environment, see which SQL is slower.

2. Explain and slow SQL analysis. For example, SQL statements are poorly written, indexes are missing or invalid, there are too many associative queries (sometimes design flaws or unacceptable requirements), and so on.

3. Show Profile is a step closer to the implementation details than Explain, which can query what each SQL has done and how many seconds it took to execute each SQL.

4. Find DBAs or OPS to tune MySQL server parameters.

III. What is an index?

MySQL's official definition of an index is: Index is a data structure that helps MySQL obtain data efficiently. We can simply understand it as: quickly find a data structure in good order.

Mysql indexes have two main structures: a B+Tree index and a Hash index. What we usually call an index, if not specifically specified, generally refers to an index organized by a B tree structure (B+Tree index). Why does indexing speed queries? recommend

The index is shown in the figure below:

The outermost light blue disk block 1 contains data 17, 35 (dark blue) and pointers P1, P2, P3 (yellow). P1 points to disk blocks smaller than 17, P2 is between 17 and 35, and P3 points to disk blocks larger than 35. The real data exists in the cotyledon nodes, which are the lowest layers 3, 5, 9, 10, 13... Non-leaf nodes do not store real data, only data items that direct the search direction, such as 17, 35.

Search process: For example, search for 28 data items, first load disk block 1 into memory, take an I/O, and use binary search to determine the pointer at P2. Then find 28 between 26 and 30, load disk block 3 into memory through the address of the P2 pointer, and a second I/O occurs. Find disk block 8 in the same way, and a third I/O occurs.

The truth is that the top three layers of B+Tree can represent millions of data, millions of data only occurred three I/Os instead of millions of I/Os, and the time increase is huge.

IV. Explain analysis

After the previous preparation is completed, enter the practical operation part, and first insert the data needed for the test:

CREATE TABLE `user_info` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL DEFAULT '', `age` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_index` (`name`) )ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO user_info (name, age) VALUES ('xys', 20); INSERT INTO user_info (name, age) VALUES ('a', 21); INSERT INTO user_info (name, age) VALUES ('b', 23); INSERT INTO user_info (name, age) VALUES ('c', 50); INSERT INTO user_info (name, age) VALUES ('d', 15); INSERT INTO user_info (name, age) VALUES ('e', 20); INSERT INTO user_info (name, age) VALUES ('f', 21); INSERT INTO user_info (name, age) VALUES ('g', 23); INSERT INTO user_info (name, age) VALUES ('h', 50); INSERT INTO user_info (name, age) VALUES ('i', 15); CREATE TABLE `order_info` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `user_id` BIGINT(20) DEFAULT NULL, `product_name` VARCHAR(50) NOT NULL DEFAULT '', `productor` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`) )ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL'); INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX'); INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL'); INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA'); INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH'); INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

For the first time, execute Explain's effect:

Index usage is in possible_keys, key, and key_len columns. Let's talk about it from left to right.

1. id

--id same, execution order from top to bottom

explain select u.*, o.* from user_info u,order_info o where u.id=o.user_id;

--id is different, the larger the value, the more executed it is.

explain select * from user_info where id=(select user_id from order_info where product_name ='p8');

2. select_type

You can see the implementation examples of id, there are several types:

SIMPLE: Indicates that this query contains no UNION queries or subqueries

PRIMARY: Indicates that this query is the outermost query

SUBQUERY: First SELECT in a subquery

UNION: Indicates that this query is the second or subsequent query of UNION

DEPENDENT UNION: The second or subsequent query statement in UNION, depending on the outer query

UNION RESULT, RESULT OF UNION

DEPENDENT SUBQUERY: The first SELECT in a subquery, depending on the query outside. That is, subqueries depend on the results of outer queries.

DERIVED: Derived, representing SELECT of the exported table (subquery of FROM clause)

3. table

table indicates the table involved in the query or the derived table:

explain select tt.* from (select u.* from user_info u,order_info o where u.id=o.user_id and u.id=1) tt

An id of 1 is derived from the u and o tables with id of 2.

4. type

The type field is important because it provides an important basis for judging whether the query is efficient. Through the type field, we determine whether the query is a full table scan or an index scan.

The common values of type are:

system: There is only one piece of data in the table, and this type is a special const type.

const: Equivalent query scan for primary key or unique index, returning at most one row of data. Const queries are very fast because it can be read only once. For example, the following query uses the primary key index, so type is of type const: explain select * from user_info where id = 2;

eq_ref: This type usually appears in multi-table join queries, indicating that for each result in the previous table, only one row of results in the subsequent table can be matched. And the query comparison operation is usually =, query efficiency is high. Example: explain select * from user_info, order_info where user_info.id = order_info.user_id;

ref: This type typically occurs in multi-table join queries, for non-unique or non-primary key indexes, or queries that use leftmost prefix rule indexes. For example, in the following example, a query of ref type is used: explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5

range: indicates to use index range query to obtain some data records in the table through index field range. This type usually appears in =, , >, >=,

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.