In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Preface
I have seen a lot of articles about the leftmost prefix matching of the mysql joint index before. I thought I knew its principle. I recently communicated with the interviewer during the interview and found that something was omitted. Here I sort out some of this content.
Leftmost prefix matching principle
When mysql builds a federated index, it follows the principle of leftmost prefix matching, that is, leftmost priority. When retrieving data, it starts from the leftmost side of the federated index. Example:
Create a federated index on column col1, column col2, and column col3
KEY test_col1_col2_col3 on test (col1,col2,col3)
The federated index test_col1_col2_col3 actually establishes (col1), (col1,col2), and (col,col2,col3) three indexes.
SELECT * FROM test WHERE col1= "1" AND clo2= "2" AND clo4= "4"
The above query is executed according to the leftmost prefix matching principle, and the index (col1,col2) is used for data matching when retrieving.
Be careful
The fields of the index can be in any order, such as:
SELECT * FROM test WHERE col1= "1" AND clo2= "2" SELECT * FROM test WHERE col2= "2" AND clo1= "1"
The col1,col2 is used in both query statements. The rule for mysql to create a federated index is to sort the data in the leftmost part of the federated index, that is, the first field col1, based on the sort of the first field, and then sort the second field col2. In fact, it is equivalent to implementing a collation like order by col1 col2.
Some people may wonder that the second query statement does not match the leftmost prefix match: first of all, it is certain that the two query statements are both the col1 and col2 fields in the guarantee index (col1,col2), but the order is different, the query conditions are the same, and the final query result must be the same. Since the results are the same, what is the best query order? At this point, we can use the mysql query optimizer explain,explain to correct the order in which the sql statements should be executed most efficiently, and finally generate the real execution plan.
Why use federated indexes?
Reduce expenses. Building a joint index (col1,col2,col3) is actually equivalent to building (col1), (col1,col2), (col1,col2,col3) three indexes. Each additional index increases the overhead of write operations and disk space. For tables with a large amount of data, using federated indexes will greatly reduce the overhead!
Overwrite the index. For the federated index (col1,col2,col3), if you have the following sql: select col1,col2,col3 from test where col1=1 and col2=2. Then MySQL can get the data directly by traversing the index without going back to the table, which reduces a lot of random io operations. Reducing io operations, especially random io, is actually the main optimization strategy of dba. Therefore, in real practical applications, overlay index is one of the main optimization means to improve performance.
High efficiency. The more index columns, the less data is filtered by the index. A table with 1000W pieces of data has the following sql:select from table where col1=1 and col2=2 and col3=3, assuming that 10% of the data can be filtered out for each condition, and if there is only a single-valued index, then the index can filter out 1000W10%=100w pieces of data, and then go back to the table to find col2=2 and col3=3-compliant data from 100w pieces of data, and then sort and paginate. If it is a federated index, you can imagine the improvement in efficiency by screening 1000W 10% * 10% 1w through the index.
Extension
For federated indexes (col1,col2,col3), can the query statement SELECT * FROM test WHERE col2=2; trigger the index?
Most people would say NO, but it's actually YES.
Reason:
EXPLAIN SELECT * FROM test WHERE col2=2;EXPLAIN SELECT * FROM test WHERE col1=1
Observe the type field in the above two explain results. The queries are as follows:
Type: indextype: ref
Index: this type means that mysql scans the entire index. In order to use this type of index, there is no special requirement for this index, as long as it is an index, or part of a federated index, mysql may scan in the index type. However, the disadvantage is that it is not efficient. Mysql will find the last data one by one from the first data in the index until it finds an index that meets the judgment criteria. Therefore, the above statement triggers the index.
Ref: this type means that mysql will quickly find an index that meets the criteria according to a specific algorithm, instead of scanning every data in the index one by one, that is, you usually understand that using an index query will fetch data more quickly. In order to achieve this kind of search, the index has requirements, in order to achieve this fast search algorithm, the index must meet the specific data structure. To put it simply, the data of the index field must be ordered in order to achieve this type of search and to make use of the index.
Summary
The above is the leftmost matching principle of the Mysql joint index introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave a message for me, and the editor will reply to you in time. Thank you very much for your support to the website!
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.