Performance test issues-mysql The database server has a high CPU footprint

MySQL server CPU consumption is high

1. Description of the problem

A simple interface, according to the incoming number segment query number attribution, run Performance test script, 20 concurrent MySQL CPU is very high, monitoring found only a SELECT statement, and the table is indexed

2. Cause of the problem

Query Statement index not hit causes

Select at the beginning

SELECT

' Province_name ',

' City_name '

From ' Phoneno_section '

WHERE SUBSTRING (?, phoneno_section_len) = Phoneno_section

LIMIT?

The advisory said that where the use of the SUBSTRING function does not, modify the function to left, the statement is

SELECT

' Province_name ',

' City_name '

From ' Conf_phoneno_section '

WHERE left (?, Phoneno_section_len) = Phoneno_section

LIMIT?

The test finds that the CPU is still high, the parameters in the left function are not constants, modify the SELECT statement again, specify that the Phoneno_section_len in the left function is a fixed value, and that the CPU is occupied properly

3. mysql Index Introduction

ü Give an example first

Table A, field: ID (self-increment ID), User (username), pass (password), type (0,1),

Index: User + Pass establish Federated Index, user unique index, pass normal index, type Normal index

ü Index hit description

(1) SELECT * from a WHERE user = ' t ' and PASS = ' P ' will hit User+pass's federated Index

(2) Sql:select * from a WHERE user = ' t ' OR user= ' F ' cannot hit any index

(3) Sql:select * from a WHERE user = ' t ' will hit user unique index

(4) Sql:select * from a WHERE pass = ' P ' cannot hit any index

(5) SELECT * from a WHERE user = ' t ' or user= ' F ' relative to SELECT user,pass from a WHERE user = ' t ' or user= ' F ' will be slow

(6) SELECT * from a WHERE length (user) = 3 cannot hit

(7) User unique index, type index can be deleted

Index is the sort, the current computer technology and mathematical theory is not supported at the same time in accordance with two keywords to sort, even if it is a federated index, but also the leftmost keyword first row, and then on the left of the keyword sort based on the other keywords sorted, is a multi-order results. Therefore, a single-table query can hit at most one index at a time, and the index must follow the leftmost prefix . The index-based structure and the leftmost prefix, such as or, like ' percent ' are not indexed, and the likes ' aa% ' can be hit.

Whether it's InnoDB or MyISAM, the index only records the primary key or address of the row being sorted, and the other fields require two of queries, so if the queried field is just contained in the index, the index overlay will be efficient.

If all the data is the same, or basically the same, then there is no need for sorting. Like the type in the example is only 1 or 0, selectivity is 0.5, very low sample paper, so can be ignored, even if established, is a waste of space, MySQL will also choose to discard when querying.

Similar to the leftmost prefix, when querying the index, if the column is applied to the function, then in the query, it will not be used in the index. The reason is simple, the function operation has changed the contents of the column, and the original index is the column content of the full order.

In summary, the index of several knowledge points: The leftmost prefix, index coverage, index selectivity, column isolation in the establishment and use of the index requires extra attention.

4. mysql Index Invalid scenario supplement

The Üwhere clause has a non-equal number in the query condition (WHERE column!= ... ), MySQL will not be able to use the index

A function is used in the query condition of the Üwhere clause (for example: Where day (column) = ... ), MySQL will not be able to use the index, the left function in the experiment is possible, but the condition cannot be a variable, use the Left function and the condition is a variable, cannot use the index, if there are other functions outside the left function to be verified

ü In a JOIN operation (when data needs to be extracted from multiple data tables), MySQL can use the index only if the primary key and foreign key are of the same data type, otherwise the index will not be used

ü If the comparison operator like and Regexp,mysql is used in the query condition of the WHERE clause, the index can only be used if the first character of the search template is not a wildcard. For example, if the query condition is like ' abc% ', MySQL will use the index, and if the condition is like '%abc ', MySQL will not use the index.

ü In an order by operation, MySQL uses the index only if the sort condition is not a query condition expression. However, in queries involving multiple data tables, even if an index is available, those indexes have little effect in accelerating the order by operation.

ü If a data column contains many duplicate values, it will not be very effective even if you set up an index for it. For example, if a data column contains the equivalent of something like "0/1" or "y/n", there is no need to create an index for it.

As long as the index is established, except in the case where the index mentioned above is not used, in other cases, the index is generally valid as long as it is used in the Where condition, the ORDER by field, and the Union table fields.

5. References

Http://www.jb51.net/article/37190.htm

http://terry831010.blog.163.com/blog/static/69161171201382011498834/

Performance test issues-mysql The database server has a high CPU footprint