MySQL Common optimization points

MySQL common optimization points for optimized point SQLJune 8, 2014 DBA released reply

# ###################################################
# Index related
# ###################################################
1. Query (or update, delete, can be converted to query) is not used in the index
This is the most basic step that needs to be performed on SQL explain to see if the index is used in the execution plan and to focus on Type=all, key=null fields.

2. Applying a function on an indexed field
To_char (gmt_created, ' mmdd ') = ' 0101′
The correct wording
gmt_created between To_date ("20090101″," YYYYMMDD ") and To_date (" 20090102″, "YYYYMMDD")

3. Use full blur on the index field
member_id like '%alibab% '
B-Tree does not solve such problems, you can consider search engines.
But member_id like ' alibab% ' can use the index.
In fact, using like '%xxxx% ' for any one field is an irregular practice that needs to be able to check for this error usage.

4. Index of multi-column fields, no leading index
Index: (memeber_id, group_id)
Where group_id=9234, in fact, this condition is no way to use the index above. This is a very common misuse. To understand why this index is not available, you need to understand how MySQL constructs multi-column indexes.
The index is a B-tree, and the problem is that for multi-column indexes, MySQL is assembling the indexed fields in the order in which they are indexed, forming a new string that is used as the key to build the B-tree. Therefore, in the query condition, if the leading column is not used, there is no way to access the B-Tree of the Multi-column index.
should be indexed: (group_id, member_id)

5. Access to fields outside of the index
Index (member_id, subject)
Select subject from offer where member_id=234
In the case of a lot of member_id=234 records, it will be better than
Select Subject, gmt_created from offer where member_id=234
The reason for this is that the second SQL will look up the records in the ROWID table according to the index. The first SQL uses an index range scan to get the results.
If you have a large number of SQL executions, but the fields you read are not covered by the index, you may need to establish an overlay index.

6. Count (ID) is sometimes slower than count (*)
COUNT (id) = = = Count (1) where ID is not NULL
If there is no (ID) index, then full-table scan is used, and count (*) uses the optimal index for fast full scan with index
Count Uniform Use COUNT (*)

7. Correct use of the stop mechanism
Determine if the member_id has a record in the offer table:
Select COUNT (*) from offer where member_id=234 limit 1
Better than
Select COUNT (*) from offer where member_id=234
The reason is that the first SQL will stop after it gets the first qualifying record.

# ###################################################
# Efficient Paging
# ###################################################
1. Efficient Paging
Using the join technique, the index is used to find a qualifying ID, constructed as a temporary table, and used to join the original table with this small temporary table.
SELECT *
From
(
Select T.*, rownum as RN
From
(SELECT * FROM Blog.blog_article
where domain_id=1
and draft=0
Order by domain_id, draft, gmt_created DESC) t
where RowNum >= 2
) A
where A.rn <= 3
Should be changed to write
Select blog_article.*
From
(
Select RID, rownum as RN
From
(
Select rowID as ID from blog.blog_article
where domain_id=1
and draft=0
Order by domain_id, draft, gmt_created DESC
) T
where RowNum >= 2
) A, blog_article
where A.rn >= 3
and A.rid = Blog_article.rowid

2. Order BY is not used index
has index (a, b,c)
Mixed collation
Order by a ASC, b desc, c desc/Mixed Sort direction */
Missing leading column
W Here's G = Const ORDER BY B, c/* A prefix is missing */
Missing Intermediate column
WHERE a = const ORDER BY c/* b is missing */
Used not Sort the columns in the index
WHERE a = Const ORDER by a, D/* and D is not part of index */

# ###################################################
# Efficient use of primary key
# ############################## #####################
Random Query
An incorrect practice:
Select * from title where kind_id=1 order by Rand ()  limit 1;
CREATE index k on title (kind_id);
This SQL execution requires a full table scan and saves the data to a temporary table, which is a very time-consuming operation.
improved practice, using offsets.
Select round (rand () * COUNT (*))  from title where kind_id=1;
Select * from title where kind_id=1 limit 1 offset $random;
CREATE index k on title (kind_id);
compared to the above approach, this notation can take advantage of the index on the kind_id, reducing the data blocks that need to be scanned. However, if the offset is very large, then the data block that needs to be scanned is also very large, in extreme cases all data blocks that scan the index K.
Best practices for scoping with primary keys
Select round (rand () * COUNT (*))  from title where kind_id=1;
Select * from Title where kind_id = and ID > $random  limit 1;
This SQL uses primary key for scoping queries, fully indexing, and reading only one record, very quickly. However, the limitation of this usage is that primary key must be of type int and is continuously self-growing.

# ###################################################
# Efficient Join
# ###################################################

1. Small table Drive Large table for join2. Avoid sub-queries

Subqueries are a potential risk factor for performance. You should use join to overwrite SQL.

# ###################################################
# data type
# ###################################################
1. Avoid an implicit conversion
CREATE TABLE ' user ' (
' ID ' smallint (5) unsigned not NULL auto_increment,
' Account ' char (one) not NULL COMMENT ",
' Email ' varchar (128),
PRIMARY KEY (' id '),
UNIQUE KEY ' username ' (' account ')
) Engine=innodb Charset=utf8;
Mysql> Explain select * from user where account=123 \g
1. Row ***************************
Id:1
Select_type:simple
Table:user
Type:all
Possible_keys:username
Key:null
Key_len:null
Ref:null
Rows:2
Extra:using where
1 row in Set (0.00 sec)
As you can see, the account=123 condition does not use a unique index of ' username '. MySQL server reads all the records from storage engine, uses the To_number () function, converts the account in the record to a number, and the converted number is used to compare with the parameters. Our test table has 2 records, and the value of rows in the execution plan is 2, and the value of type is all, which also means that the index ' username ' is not used.
Mysql> Explain select * from user where account= ' 123′\g
1. Row ***************************
Id:1
Select_type:simple
Table:user
Type:const
Possible_keys:username
Key:username
Key_len:33
Ref:const
Rows:1
Extra:
1 row in Set (0.00 sec)
The parameter is a string type and we can see the index ' username ', which is used.
This is a practice that is often misused.

2. The primary key is not a self-increment column
The primary key for the self-increment column has several benefits:
High insertion performance.
Reduce the fragmentation of the page.
Provides performance for level two indexes, reducing the level of two indexes because the two-level index stores the value of the primary key, not the row ID in the page.