What is the index of MySQL? How to optimize?

Index similar to the bibliographic index of university library, it can improve the efficiency of data retrieval and reduce the IO cost of database. MySQL in the 3 million records around the performance began to gradually decline, although the official document said 500~800w records, so large data volume index is very necessary. MySQL provides explain, which shows the details of SQL execution and can be optimized for indexing.

the cause of slow SQL execution:

1. Hardware issues. such as slow network speed, low memory, I/O throughput is small, disk space is full and so on.

2. No index or index invalidation. (usually in an internet company, the DBA will lock the table at midnight and re-establish the index again, because the tree structure of the index is incomplete when you delete the data.) So the internet company's data do is fake delete. One is to do the data analysis, the second is to not destroy the index)

3. Too much data (sub-database table)

4. Server tuning and individual parameter settings (adjustment my.cnf)

second, when analyzing the reasons, we must find the starting point:

1. First observe, turn on the slow query log, set the appropriate thresholds (for example, more than 3 seconds is slow SQL), in the production environment after the last day to see which SQL is slow.

2.Explain and slow SQL analysis. For example, the SQL statement is bad, the index is not or invalid, the association query too many (sometimes design flaws or not to be required) and so on.

3.Show profile is a step closer to the execution details of explain, which can be queried to do what each SQL did, and how many seconds were spent.

4. Find the DBA or OPS to tune the server parameters for MySQL.

Iii. What is an index?

The official MySQL definition of an index is: index is the data structure that helps MySQL to get data efficiently. We can simply understand that: quickly find a data structure that is well sequenced. There are two main structures of MySQL indexes: B+tree index and Hash index. Our usual index, if not specified, generally refers to the index of the B-tree structure Organization (b+tree index). Index:

The outermost light blue disk Block 1 has data 17, 35 (dark blue) and pointer P1, P2, P3 (yellow). The P1 pointer represents a disk block less than 17, P2 is between 17-35 and P3 points to a disk block greater than 35. Real data exists at the sub-leaf nodes, the bottom layer 3, 5, 9, 10, 13 ... non-leaf nodes do not store real data, only data items that guide the direction of the search, such as 17, 35.

The lookup process: for example, searching for 28 data items, first loading disk blocks 1 into memory, occurring once I/O, using binary lookup to determine the P2 pointer. Then found 28 between 26 and 30, the address of the P2 pointer to load disk block 3 to memory, the second I/O occurs. The same way to find disk Block 8, the third I/O occurs.

The real situation is that the above 3 layers of b+tree can represent millions of data, millions of data have only three I/O instead of millions I/O, and the time boost is huge.

Iv. Explain analysis

Before the completion of the paper, into the real part, first to insert the data required for testing:

CREATE TABLE' User_info ' (' ID ')BIGINT( -) not NULLauto_increment, ' name 'VARCHAR( -) not NULL DEFAULT "', ' age 'INT( One)DEFAULT NULL,  PRIMARY KEY(' id '),KEY' Name_index ' (' name ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;INSERT  intoUser_info (name, age)VALUES('XYs', -);INSERT  intoUser_info (name, age)VALUES('a', +);INSERT  intoUser_info (name, age)VALUES('b', at);INSERT  intoUser_info (name, age)VALUES('C', -);INSERT  intoUser_info (name, age)VALUES('D', the);INSERT  intoUser_info (name, age)VALUES('e', -);INSERT  intoUser_info (name, age)VALUES('F', +);INSERT  intoUser_info (name, age)VALUES('g', at);INSERT  intoUser_info (name, age)VALUES('h', -);INSERT  intoUser_info (name, age)VALUES('I', the);CREATE TABLE' Order_info ' (' ID ')BIGINT( -) not NULLauto_increment, 'user_id`BIGINT( -)DEFAULT NULL, ' Product_Name 'VARCHAR( -) not NULL DEFAULT "', ' Productor 'VARCHAR( -)DEFAULT NULL,  PRIMARY KEY(' id '),KEY' User_product_detail_index ' ('user_id' , ' product_name ', ' productor ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(1,'P1','whh');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(1,'P2','WL');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(1,'P1','DX');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(2,'P1','whh');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(2,'P5','WL');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(3,'P3','MA');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(4,'P1','whh');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(6,'P1','whh');INSERT  intoOrder_info (user_id, Product_Name, Productor)VALUES(9,'P8','TE');

First experience, perform the explain effect:

The index usage is in Possible_keys, key, and Key_len, and then we'll go from left to right in turn.

1.id

--id相同,执行顺序由上而下explain select u.*,o.* from user_info u,order_info o where u.id=o.user_id;

--id不同,值越大越先被执行explain select * from  user_info  where id=(select user_id from order_info where product_name =‘p8‘);

2.select_type

You can see the execution instances of the IDs in a total of the following categories:

• Simple: Indicates that this query does not contain a UNION query or subquery
• PRIMARY: Indicates that this query is the outermost query
• Subquery: The first SELECT in a subquery
• Union: Indicates that this query is the second or subsequent query of UNION
• DEPENDENT the second or subsequent query statement in Union:union, depending on the outside query
• Union result, results of union
• DEPENDENT subquery: The first SELECT in a subquery depends on the outside query. That is, the subquery relies on the results of the outer query.
• DERIVED: Derived, representing the select of the exported table (subquery FROM clause)

3.table

Table represents the tables or derived tables that are involved in the query:

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

A <derived2> with ID 1 is derived from the U and O tables with ID 2.

4.type

The Type field is important, and 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 for type are:

• System: There is only one piece of data in the table, and this type is a special const type.
• Const: An equivalent query scan for a primary key or a unique index that returns a maximum of one row of data. The const query is very fast because it is only read once. For example, the following query, which uses the primary key index, so the type is a const type: Explain SELECT * from user_info where id = 2;
• Eq_ref: This type typically appears in a multiple-table join query, which indicates that for each result of the preceding table, only one row of results from the following table can be matched. And the comparison operation of the query is usually =, the query efficiency is high. For example: Explain select * from User_info, order_info where user_info.id = order_info.user_id;
• Ref: This type typically appears in a join query for multiple tables, for non-unique or non-primary key indexes, or for queries that use the leftmost prefix rule index. For example, in the following example, a query of type ref is used: Explain select * from User_info, order_info where user_info.id = order_info.user_id and Order_i nfo.user_id = 5
• Range: Indicates that part of the data record in the table is obtained by indexed field scope using an indexed range query. This type usually appears in the =, <>,;, >=, <, <=, is NULL, <=>, between, in () operation. For example, the following example is a range query: Explain SELECT * from User_info where ID between 2 and 8;
• Index: Represents a full index scan, similar to the all type except that the all type is a full table scan, and the index type simply scans all indexes without scanning the data. The index type is usually present: the data to be queried is available directly in the index tree, without the need to scan the data. When this is the case, the Extra field displays the Using index.
• All: Represents a full table scan, and this type of query is one of the worst-performing queries. In general, our query should not have all types of queries, because such a query in the case of large amounts of data, the performance of the database is a huge disaster. If a query is an all type query, you can generally avoid it by adding an index to the corresponding field.

In general, the performance relationships for different type types are as follows:
All < index < range ~ Index_merge < ref < EQ_REF < Const < SYSTEM
The all type is a full table scan, so it is the slowest under the same query condition. A query of index type is not a full table scan, but it scans all indexes, so it is slightly faster than the all type. The following types use indexes to query data, so you can filter some or most of the data, so queries are more efficient.

5.possible_keys

It represents the index that MySQL may use when querying. Note that even though some indexes appear in Possible_keys, it does not mean that the index is actually used by MySQL. What indexes MySQL uses at query time, as determined by the key field.

6.key

This field is the index that MySQL is actually using at the time of the current query. For example, the Possible_keys is to how many people, key is the real number of people. When we are not indexing:

explain select o.* from order_info o where  o.product_name= ‘p1‘ and o.productor=‘whh‘;create index idx_name_productor on order_info(productor);drop index idx_name_productor on order_info;

After creating the composite index, query:

7.key_len

Represents the number of bytes indexed by the query optimizer, which can be used to evaluate whether a composite index is fully in use.

8.ref

This indicates which column of the index is being used and, if possible, a constant. The type attribute in the preceding article also has ref, note the difference.

9.rows

Rows is also an important field, and the MySQL query optimizer estimates the number of rows of data that SQL needs to scan to find the result set, based on statistics, which is a very intuitive display of SQL efficiency, which in principle is as small as possible. You can compare the example in key, one without indexed money, rows is 9, and after indexing, rows is 4.

10.extra

Many of the additional information in the explain is displayed in the Extra field, which is common in the following categories:

• Using Filesort: Indicates that MySQL requires additional sorting operations and cannot be sorted by index order. Generally, using Filesort is recommended for optimization removal, because such queries CPU resource consumption is large.
• Using index: Overwrites the index scan, indicating that the query can find the required data in the index tree, without scanning the table data file, often indicating good performance.
• Using temporary: Queries have the use of temporary tables, generally appear in the case of sorting, grouping and multi-table joins, query efficiency is not high, recommended optimization.
• Using Where: The table name uses the where filter.

V. Optimization cases

explain select u.*,o.* from user_info u LEFT JOIN order_info o on u.id=o.user_id;

Execution result, type has all, and there is no index:

To start the optimization, create an index on the associated column, and clearly see that all of the type column becomes ref and that the index is used, and rows becomes 1 rows from the scan 9 rows:

There is a general rule in this: The left link index is added to the right table above, the right link index is added to the left table above.

VI. Do I need to create an index?

Indexes can improve query speed very efficiently while reducing the speed of updating tables. In fact, the index is also a table, which holds the primary key and index fields, and points to the records of the entity table, so the indexed columns also occupy space.

Reference Document: https://my.oschina.net/liughDevelop/blog/1788148

What is the index of MySQL? How to optimize?