Shulou(Shulou.com)06/01 Report--

What is an index? Why build an index?

The index is used to quickly find the row with a specific value in a column, without using the index, MySQL must read the entire table from the first record until finding out the relevant row, the larger the table, the more time it takes to query the data. If the query column in the table has an index, MySQL can quickly reach a location to search the data file without having to view all the data, then it will save a lot of time.

For example, there is a person table with 2W records and 2W personal information. There is a Phone field that records everyone's phone number, and now you want to find out the information of the person whose phone number is xxxx.

If there is no index, it traverses down from the first record in the table until the information is found.

If there is an index, then the Phone field will be stored in a certain way, so that when querying the information on the field, the corresponding data can be found quickly without having to traverse 2W pieces of data. There are two storage types of indexes in MySQL: BTREE and HASH. That is, the field is stored with a tree or hash value, and knowledge of the algorithm is needed to know how to find it in detail. Now we just need to know the function of the index and what the function is.

Introduction

Today, I wrote a sql, in which the amount of data in the tables involved is about 50w, and the query found that it took 8s. This is just a test suit on the data, put on the formal service, must be dead as soon as it runs.

SELECT Orders. NO, GuidNo, Orders.CreateTime, sum (OrderItem.Quantity) AS Quantity, Brand. NAME AS BrandName, member.Mobile, Street AS deliveryaddress, AreaFROM OrdersINNER JOIN OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNoINNER JOIN Brand ON Brand.Id = Orders.BrandIdINNER JOIN member ON member.Id = 13INNER JOIN memberaddress ON member.Id = memberaddress.MemberIdWHERE orders.GuidNo IN (SELECT orderpayment.OrderGuidNo FROM paymentrecord LEFT JOIN orderpayment ON paymentrecord.`No` = orderpayment.PaymentNo WHERE paymentrecord.PaymentMethod = 'MemberCard' AND paymentrecord.Payer = 13) GROUP BY GuidNo

Then I analyze it with EXPLAIN and find that the Orders table does not hit the index, but the GuidNo in the query Orders has an index set, but it just cannot be hit.

Solution process

Then I divide the above statement into two statements. First of all, modify the sql statement to: the data of the subquery is written directly into the sql, and the query takes 0.12s.

SELECT Orders. NO, GuidNo, Orders.CreateTime, sum (OrderItem.Quantity) AS Quantity, Brand. NAME AS BrandName, member.Mobile, Street AS deliveryaddress, AreaFROM OrdersINNER JOIN OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNoINNER JOIN Brand ON Brand.Id = Orders.BrandIdINNER JOIN member ON member.Id = 13INNER JOIN memberaddress ON member.Id = memberaddress.MemberIdWHERE orders.GuidNo IN ('0A499C5B1A82B6322AE99D107D4DA7B88,' 18A5EE6B1D4E9D76B6346D2F6B836442, '327A5AE2BACEA714F8907865F08503,' B42B085E4BA14516CEC13CF38187, 'FBC978E1602ED342E5567168E73F0602') GROUP BY GuidNo

Second: it only took 0.1s for Sql to run the subquery separately.

SELECT orderpayment.OrderGuidNo FROM paymentrecord LEFT JOIN orderpayment ON paymentrecord.`No` = orderpayment.PaymentNo WHERE paymentrecord.PaymentMethod = 'MemberCard' AND paymentrecord.Payer = 13

This makes it clear that it must be the problem associated with the child query and the parent query. Because the child query alone is very fast, the parent query is also very fast to check directly with the child query data, that is, it is very slow when they are combined. You can roughly lock the problem on these two associated fields, OrderGuidNo.

Finally, it is found that the character sets of orderpayment table and Orders table are different. The character set of one table is: utf8_general_ci, and one is: utf8mb4_general_ci. (do not check do not know, found that in a database, many tables have different character sets)

Modify the character set of orderpayment table and the character set of OrderGuidNo in table to: utf8_general_ci

ALTER TABLE orderpayment DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; / / modify the character set of the table ALTER TABLE orderpayment CHANGE OrderGuidNo OrderGuidNo VARCHAR / / modify the character set of the field

Then after analyzing it with EXPLAIN, you can see that indexes are all used.

Then run the query for 0.112 seconds.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, 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.