MySQL Tuning series Basic article

Objective

There was no blog for a while, all day busy, to work, recording courses, happened recently a relatively idle time, to get the MySQL database.

About MySQL database, here do not do too much introduction, open source, free and other features favored by various internet industry, especially after some large e-commerce applications, it is highly respected.

There was a time in front of me writing a tuning series of SQL Server databases that interested in being able to follow.

From this chapter, I will begin to analyze the MySQL database of a series of tuning content, the same as the beginning, first in MySQL tuning in the most commonly used query plan to parse, to do a good job of the basic grasp, tamping basic skills! Then we talk about the overall statement tuning.

Part of the article will be the MySQL database and the SQL Server database part of the content of a comparison, not to criticize the pros and cons, only to describe the technology, the right to do learning.

Technical preparation

Hosted on the window platform, based on the MYSQL5.6 version, it is parsed using its own case library (Sakila).

First, about the query plan

In fact, in all relational databases, when running T-SQL statements, when the query is compiled and run, it will have its own internal optimization process, and this optimization of the product is: the execution Plan .

In SQL Server, we can be viewed in many ways, convenient and the execution of query statements have a certain grasp of the same in MySQL, also has its own execution plan, relative to SQL Server, its execution plan is viewed as follows:

In fact, a very simple way to look at, just to add the statement that we want to care about optimization: Explan keyword, MySQL will evaluate some of the points that need to be followed when the current statement is executed.

Similar to SQL Server execution plans, but may not be detailed and intuitive for SQL Server, but this does not hinder the tuning of statements.

Here's a simple example:

Select  from where 1

Simple to analyze the contents of the table above the output item.

Select_type: Select Type

<1>simple: Simple Select (not available for union or subquery)

<2>primary: The outermost Select

The second or subsequent SELECT statement in a <3>union:union

<4>dependent the second or subsequent SELECT statement in the Union:union, depending on the outside query

Results of <5>union Resut:unoion

<6>subquery: The first select in a subquery

<7>dependent subquery: The first select in a subquery, depending on the outside query

<8>derived: Export table's SELECT (subquery FROM clause)

Table: Name of the query

Type: Connection type

Here is a key reference to the efficiency of the query, which contains the following content values, performance is good or bad:

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All

The more forward the access type, the higher the efficiency, the faster, and the goal we want to optimize.

In general, make sure that the query reaches at least the range level, preferably to ref, and the worst is the last all .... This is similar to scale in SQL Server .... Full table Scan ...

Below, we analyze the scenarios for these connection types in turn:

<1>system: Table has only one row (= system table). This is typically a variable value and is a special case of a const connection type.

<2>const: A matching row with the highest table, which starts the query being read. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. Const is used to compare all parts of a primary key or unique index with a constant value.

<3>eq_ref: Reads a row from the table for each row combination from the preceding table. This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary
KEY. Eq_ref can be used for indexed columns that use the = operator comparison. The comparison value can be a constant or an expression that uses a column of a table that was read earlier in the table.

<4>ref: For each row combination from the preceding table, all rows with matching index values are read from this table. If the join uses only the leftmost prefix of the key, or if the key is not unique or primary key (in other words, if the join cannot select a single row based on the keyword), ref is used. If you use a key that matches only a few rows, the join type is good. Ref can be used with indexed columns that use the = or <=> operator.

<5>ref_or_null: The join type is like ref, but adding MySQL can specifically search for rows that contain null values. The optimization of the join type is often used in the resolution subquery.

<6>index_merge: The join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and Key_len contains the longest key element for the index used.

<7>unique_subquery: The type replaces the following form of in subquery Ref:value in (SELECT primary_key fromsingle_table WHERE some_expr); unique_ Subquery is an index lookup function that can completely replace a subquery and be more efficient.

<8>index_subquery: The join type is similar to Unique_subquery. You can replace in subqueries, but only for non-unique indexes in subqueries of the following form: value in SELECT key_column from single_table WHERE some_expr)

<9>range: Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used. The Key_len contains the longest key element of the index being used. In this type, the ref column is null. When you use the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators to compare key columns with a constant, you can use the range

<10>index: The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.

<11>all: A complete table scan for each row combination from the previous table. If the table is the first table that is not marked const, this is usually not good and is usually poor in its case. You can usually add more indexes instead of all, so that the rows can be retrieved based on the constant values or column values in the preceding table.

Possible_keys: indicates which index MySQL can use to find rows in the table. Note that the column is completely independent of the order of the tables shown in the explain output. This means that some keys in Possible_keys are not actually used in the generated table order.

Key :The key column shows the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null. To force MySQL to use or ignore the indexes in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

Key_len:The Key_len column shows the key lengths that MySQL decides to use. If the key is null, the length is null. Note that with the Key_len value we can determine that MySQL will actually use several parts of a multi-key word.

Ref:The ref column shows which column or constant is used together with key to select rows from the table.

Rows :rows column shows the number of rows that MySQL must check when it executes a query.

Extra: This column contains the details of the MySQL resolution query.

<1>distinct:mysql finds a 1th matching row, stops searching for more rows for the current row combination.

<2>not Exists:mysql is able to perform a left join optimization on the query, and after discovering 1 rows that match the left join standard, no more rows are checked within the table for the preceding row.

<3>range checked for each record (Index map: #): MySQL did not find a good index to use, but found that some indexes could be used if the column values from the previous table were known. For each row combination of the preceding table, MySQL checks whether a range or Index_merge access method can be used to request rows.

<4>using Filesort:mysql requires an extra pass to find out how rows are retrieved in sorted order. The sort is done by browsing all rows based on the join type and holding a pointer to the sort key and row for all rows that match the WHERE clause. The keywords are then sorted and the rows are retrieved in sorted order.

<5>using INDEX: Retrieves column information from a table by using only the information in the index tree without requiring further search to read the actual rows. This policy can be used when a query uses only columns that are part of a single index.

<6>using Temporary: In order to resolve the query, MySQL needs to create a temporary table to accommodate the results. A typical case is when a query contains a group by and an ORDER BY clause that can be listed in different cases.

The <7>using where:where clause is used to restrict which row matches the next table or send to the customer. Unless you specifically request or check all rows from a table, the query may have some errors if the extra value is not a using where and the table join type is all or index.

<8>using sort_union (...), using Union (...), using intersect (...) : These functions describe how to merge index scans for Index_merge join types.

<9>using index for group-by: similar to the using index method for accessing tables, using index for group-by means that MySQL has found an index that can be used to query the group By or distinct queries all columns without additional searching for hard disk access to the actual table. Also, indexes are used in the most efficient way so that only a small number of index entries are read for each group.

We know that after a certain amount of data, if only through simple table scan to get the data, it will be a good resource and poor performance of the execution process, and the process of general tuning is done by adding indexes, a simple example illustrates:

int, b varchar (5)); #插入数据insert into testonerow values (1,'111 ')

We're simply going to execute a script

Explain select B from Testonerow

As you can see here, you simply get the data through the table scan (all), without any indexes to refine the query.

Let's create an index

CREATE INDEX Index_b on Testonerow (b)

As you can see, this is a simple example of optimizing T-SQL by creating an index.

Conclusion

This article comes first, the content of MySQL performance tuning involves a wide range of articles, followed by the analysis.

If you read this blog, feel that you have something to gain, please do not skimp on your " recommendation ".

MySQL Tuning series Basic article