Shulou(Shulou.com)05/31 Report--

How to start a slow query in a mysql database? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

1. Open slow query

1 > check whether slow query is enabled

Show variables like "% quer%"; slow_query_log = ON # is open

2 > enable method: my.cnf directory configuration

Whether slow_query_log=on # enables slow_query_log_file=/opt/MySQL_Data/TEST1-slow.log # slow query file location long_query_time=2 # how many seconds does it take to record a query

2. SELECT queries that appear in EXPLAIN slow query logs

Idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEuserNULLrefuseruser768const1100.00NULL

Interpretation of explain column

Table: shows which table the data in this row is about

Type: this is an important column that shows what type of connection is used. The best to worst connection types are const, eq_reg, ref, range, index, all

Possible_keys: displays the indexes that may be applied to this table. If empty, there is no possible index. You can select an appropriate statement from the where statement for the relevant domain

Key: the index actually used. If null, the index is not used. In rare cases, mysql chooses indexes that are not sufficiently optimized. In this case, you can use use index (indexname) in the select statement to force the use of an index or ignore index (indexname) to force mysql to ignore the index

Key_len: the length of the index used. The shorter the length, the better without losing accuracy.

Ref: shows which column of the index is used and, if possible, a constant

The number of rows that rows:mysql believes must be checked to return the request data

Extra: additional information about how mysql parses queries. Example: using temporary and using filesort, meaning that mysql cannot use an index at all, and the result is that retrieval will be very slow

Calculation of key_len

All index fields, if not null is not set, need to be added by one byte.

Fixed-length fields, four bytes for int, three bytes for date, and n characters for char (n).

For the field varchar (n), there are n characters + two bytes.

The number of bytes occupied by a character varies with different character sets. Latin1-encoded, one character occupies one byte, gbk-encoded, one character takes two bytes, utf8-encoded, one character takes three bytes.

3. Several principles of indexing

The leftmost prefix matching principle, a very important principle, mysql will always match to the right until it encounters a range query (>, 3 and d = 4). If you build an index in the order of (ameme bforce c), d does not need an index, if you build an index (a meme bdre c), then you can use it, and the order of aforce b d can be adjusted at will.

= and in can be out of order, for example, a = 1 and b = 2 and c = 3 indexes can be built in any order, and mysql's query optimizer will help you optimize it into a form that the index can recognize.

Try to select a highly differentiated column as the index. The formula for distinguishing degree is count (distinct column) / count (*), indicating the proportion of non-repetitive fields. The larger the proportion, the less the number of records we scan, and the differentiation degree of the only key is 1. While some status and gender fields may be 0 in front of big data, then some people may ask, is there any empirical value for this ratio? It is difficult to determine this value for different scenarios. Generally, we need more than 0.1 for the fields that require join, that is, an average of 10 records are scanned.

Index columns cannot participate in calculations and the use of functions to keep the columns clean.

Expand the index as much as possible, do not create a new index. For example, if you already have an index of an in the table, and now you want to add the index of (a), you only need to modify the original index.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.