SQL statement Optimization (i) (52)

General steps for optimizing SQL statements 1 Learn the frequency of various SQL executions with the show status command.

Format:mysql> Show [Session|global]status;

Where: Session (default) indicates the current connection,

Global indicates that the database has been started since

Mysql>show status;

Mysql>show Global status;

Mysql>show status like ' com_% ';

Mysql>show Global status like ' com_% ';

Parameter description:

COM_XXX indicates the number of times each XXX statement executes as follows:

Com_select the number of times a select operation is performed, only one query is cumulative plus 1

Com_update number of update operations performed

Com_insert the number of times the insert operation is performed, only once for bulk inserts.

Com_delete number of delete operations performed

Only for the InnoDB storage engine.

Innodb_rows_read number of select operations performed

innodb_rows_updated number of update operations performed

innodb_rows_inserted number of INSERT operations performed

innodb_rows_deleted number of delete operations performed

Other:

Connections number of connections to MySQL

Number of seconds that the Uptime server has been working

Slow_queries: Number of slow queries

2 locating a SQL statement that performs less efficiently

1) explain select * from table where id=1000;

2) DESC SELECT * from table where id=1000;

3 Analyzing execution plans for inefficient SQL through explain

Mysql> Explain select COUNT (*) from Stu where name like "a%" \g
1. Row ***************************
Id:1
Select_type:simple
Table:stu
Type:range
Possible_keys:name,ind_stu_name
Key:name
Key_len:50
Ref:null
Rows:8
Extra:using where; Usingindex
1 row in Set (0.00 sec)

A simple explanation of each column

Id:1

Select_type:simple represents the type of select, common values are simple (), i.e. not using table joins or subqueries), PRIMARY (main query, or outer query), union (the second or subsequent query in union), Subquery (the first sesect in a subquery), etc.

Table:stu Table of output result set

Type:range represents the connection type of the table with good performance: System (only one row for the table), const (row-only match), Eq_ref (using primary key and unique for each preceding row), ref (same as EQ_REF, but no primary key and unique), Ref_or_ Null (with the previous null query), index_merge (index merge optimization), Unique_subquery (primary key subquery), Index_subquery (non-primary key subquery), range (range query in the form), Index (both get data by querying the index), all (data obtained through full table scan)

Possible_keys:name,ind_stu_name indexes that may be used when querying tables.

Key:name represents the index that is actually used.

Key_len:50 Length of index field

Ref:null

Rows:8 Number of scanned rows

Extra:using where; Description and description of the Using index execution condition

Index issues

Indexing is one of the most common and important means of database optimization, and indexing often helps users to solve most SQL performance problems.

Storage classifications for indexes

MyISAM Storage Engine table data and indexes are automatically stored separately, each is the only one file; the data and indexes of the tables of the INNODB storage engine are stored in the same table space, but can have multiple files.

MySQL currently does not support the function index, but can be indexed to a previous part of the column, such as the Name field, can be indexed only the first 4 characters of name, this feature can greatly reduce the size of the index file, the user in the design of the table structure of the text column can be flexibly designed according to this feature.

Mysql>create index ind_company2_name on Company2 (name (4));
Where company table name Ind_company2_name index name

How MySQL uses the index

Indexes are used to quickly find rows that have a specific value in a column. Using indexes on related columns is the best way to improve the performance of select operations.

1. Use index

(1) For a multi-column index that is created, the index is generally used as long as the leftmost column is used in the condition of the query. Create a composite index as follows.

Mysql>create index Ind_sales2_com_mon onsales2 (Company_id,moneys);

Then press company_id to query and find that the composite index is used

Mysql>explain SELECT * from Sales2 where company_id=2006\g

Using the following query, you are not using a composite index.

Mysql>explain SELECT * from Sales2 where moneys=1\g

(2) Using a like query, the index may be used if the following is a constant and only the% number is not in the first character:

Mysql> Explain select * from Company2 where name is like "%3" \g
1. Row ***************************
Id:1
Select_type:simple
Table:company2
Type:all
Possible_keys:null
Key:null
Key_len:null
Ref:null
rows:1000
Extra:using where
1 row in Set (0.00 sec)

The following is used to index, and the example below can use the index, the difference is that the "%" position is different, the above example is "%" in the first place, and the following example is not

Mysql> Explain select * from Company2 where name is like "3%" \g
1. Row ***************************
Id:1
Select_type:simple
Table:company2
Type:range
Possible_keys:ind_company2_name
Key:ind_company2_name
Key_len:11
Ref:null
rows:103
Extra:using where
1 row in Set (0.00 sec)

(3) If a large text is searched, the full-text index is used instead of the like "%...%".

(4) If the column name is an index, use column_name is null to use the index. As follows

Mysql> Explain select * from Company2 where name is Null\g
1. Row ***************************
Id:1
Select_type:simple
Table:company2
Type:ref
Possible_keys:ind_company2_name
Key:ind_company2_name
Key_len:11
Ref:const
Rows:1
Extra:using where
1 row in Set (0.00 sec)

2. Index is present but not used

(1) If MySQL estimates that using an index is slower than a full table scan, the index is not used. For example, if the column key_part1 is evenly distributed between 1 and 100, it is not very good to use the index when querying

Mysql>select * FROM table_name where key_part1>1 and key_part<90;

(2) If you use the Memory/heap table and the Where condition does not use "=" to index the column, then the index is not used. The heap table uses the index only if the "=" condition is used.

(3) A condition that is separated by or, if the column in the condition before or is indexed, and there is no index in the subsequent column, the index involved is not used.

Mysql>show index from Sales\g

1. Row ***************************
... ...
Key_name:ind_sales_year
Seq_in_index:1
Column_name:year
... ...

SQL statement Optimization (i) (52)