Summary of several common optimizations of MySQL, and summary of mysql

Summary of several common optimizations of MySQL, and summary of mysql

Index-related

1. No index is used for queries (or updates, deletions, and can be converted to queries ).
This is the most basic step. You need to explain the SQL statement to check whether indexes are used in the execution plan. You need to focus on the fields of type = ALL and key = NULL.

2. Apply a function to the index Field

to_char(gmt_created, ‘mmdd') = '0101′

Correct writing

gmt_created between to_date(“20090101″, “yyyymmdd”) and to_date(“20090102″, “yyyymmdd”)

3. Use Full fuzzy search for index fields

member_id like ‘%alibab%'

B-tree cannot solve such problems. You can consider the search engine.
However, indexes can be used for member_id like 'alibabab %.
In fact, using like '% xxxx %' for any field is not a standard practice. You need to check the usage of this error.

4. The index of multiple columns does not use the leading index.
Index :( memeber_id, group_id)
Where group_id = 9234. In fact, this condition cannot use the above index. This is a common misuse. To understand why this index cannot be used, you need to understand how mysql constructs multi-column indexes.
An index is a B-tree. The problem is that for multiple-column indexes, mysql assembles index fields in the order they are created to form a new string, this string is used as the key to build the B-tree. Therefore, if no leading column is used in the query condition, the B-tree with multiple column indexes cannot be accessed.
Index should be created: (group_id, member_id)

5. Access fields other than the index
Index (member_id, subject)

select subject from offer where member_id=234

Member_id = 234 is superior

select subject, gmt_created from offer where member_id=234

The reason is that the second SQL statement accesses the records in the table based on the rowid found by the index. The first SQL statement uses the index range scan to obtain the results.
If an SQL statement is executed many times but the read field is not covered by the index, a overwriting index may be required.

6. count (id) is sometimes slower than count (*)

count(id) === count(1) where id is not null

If no (id) index exists, full table scan will be used, and count (*) will use the Optimal Index for quick full scan with the index
Count (*)

7. Use the stop Mechanism correctly
Judge whether member_id has records in the offer table:

select count(*) from offer where member_id=234 limit 1

Better

select count(*) from offer where member_id=234

The reason is that the first SQL statement will stop after obtaining the first qualified record.

Efficient Paging
1. Efficient Paging
Use join technology, use the index to find the qualified id, and construct a temporary table. Use this small temporary table to join the original table.

select *from(select t.*, rownum AS rnfrom(select * from blog.blog_articlewhere domain_id=1and draft=0order by domain_id, draft, gmt_created desc) twhere rownum >= 2) awhere a.rn <= 3

It should be rewritten

select blog_article.*from(select rid, rownum as rnfrom(select rowid as id from blog.blog_articlewhere domain_id=1and draft=0order by domain_id, draft, gmt_created desc) twhere rownum >= 2) a, blog_articlewhere a.rn >= 3and a.rid = blog_article.rowid

2. order by is not indexed
Index (a, B, c)
Hybrid sorting rules

ORDER BY a ASC, b DESC, c DESC /* mixed sort direction */

Leading column missing

WHERE g = const ORDER BY b, c /* a prefix is missing */

The middle column is missing.

WHERE a = const ORDER BY c /* b is missing */

Used columns not indexed for sorting

WHERE a = const ORDER BY a, d /* d is not part of index */

Use primary key efficiently
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 statement requires a full table scan and saves the data to a temporary table, which is a very time-consuming operation.
Improve the practice by using offset.

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 with the above practice, this method can use the index on kind_id to reduce the data block to be scanned. However, if the offset value is very large, the data block to be scanned is also very large. In extreme cases, all data blocks of the index k are scanned.
The best practice is to use the primary key for range search.

select round(rand() * count(*)) from title where kind_id=1;select * from title where kind_id = and id > $random limit 1;

This SQL statement uses the primary key to perform a range query. It completely performs the index and reads only one record, which is very fast. However, the restriction of this usage is that the primary key must be of the int type and is continuously increasing.

Efficient join
1. Small tables drive large tables to join
2. Avoid subqueries

Subqueries are a potential risk that affects performance. Use join to rewrite the SQL statement.

Data Type
1. Avoid implicit conversion

CREATE TABLE `user` (`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,`account` char(11) 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: 1select_type: SIMPLEtable: usertype: ALLpossible_keys: usernamekey: NULLkey_len: NULLref: NULLrows: 2Extra: Using where1 row in set (0.00 sec)

We can see that the account = 123 condition does not use the unique index 'username '. Mysql server reads all records from the storage engine and uses the to_number () function to convert the account in the record to a number. The converted number is used to compare with the parameter. There are two records in our test table, and the rows value in the execution plan is also 2, and the type value is ALL, which also indicates that the index 'username' is not used.
Copy codeThe Code is as follows: mysql> explain select * from user where account = '000000' \ 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 of the string type. We can see that the index 'username' is used.
This is a practice that is often misused.

2. The primary key is not an auto-incrementing column.
The primary key of the auto-incrementing column has multiple advantages:

• High insertion performance.
• Reduce page fragments.
• Provides the performance of secondary indexes to reduce the space for secondary indexes, because secondary indexes store the primary key value rather than the row id in the page.