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

This article shows you how to understand the fatal points of the mysql5.6 version and the importance of the same field types associated with the two tables. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Background: mysql version 5.6

Because one of my colleagues was doing a report, both of the associated tables were indexed. However, due to the discovery of different field types, although the two tables with only hundreds of thousands of levels of data are only associated with data for 7 days (the results are actually only 1294), they have to be associated for nearly 500s. So this is not designed when designing the database table at that time, and then dig the hole. Those with version 5.7or above seem to be automatically converted to the same character type (the discovery of different field types will still be indexed in another mysql database with version 5.7or later).

After discovering this problem, I made a transformation:

SELECT n.order_code. (the middle script is omitted)

(select sum (re.order_rebate) from t_bm_order_return re where re.order_id=concat (n.orderbilidwriting) and re.state_result=' success' and re.activity_no not in ('merchants must pay for slowness') rebate_amount

FROM np_order n left join web114_order_ledger wol on n.order_id=wol.order_id.

WHERE

N.create_time > = '2017-05-12'

AND n.create_time

Const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

In general, you need to ensure that the query reaches at least the range level, preferably ref.

Possible_keys

Indicates which index MySQL can use to find rows in the table. If it is empty, there is no relevant index. To improve performance at this point, check the WHERE clause to see if some fields are referenced, or check that the fields are not suitable for the index.

Key

Displays the keys that MySQL actually decided to use. If no index is selected, the key is NULL.

Key_len

Displays the key length that MySQL decides to use. If the key is NULL, the length is NULL. The documentation notes that this value can determine which part of a multiple primary key is actually used by mysql.

Ref

Shows which field or constant is used with key.

Rows

This number indicates how much data mysql has to traverse to find it, which is not accurate on innodb.

Extra

In the case of Only index, this means that the information is retrieved only from the index tree, which is faster than scanning the entire table.

If it is where used, you are using the where restriction.

If it's impossible where, it means you don't need where, but you can't find anything.

If this information shows Using filesort or Using temporary, it will be very difficult, and the indexes of WHERE and ORDER BY are often unable to take into account. If you determine the index according to WHERE, then it will inevitably cause Using filesort in ORDER BY, depending on whether it is cost-effective to filter and sort first, or to sort first and then filter.

The above is how to understand the critical points of the mysql5.6 version and the importance of the same field types associated with the two tables. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.