Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to optimize MySQL Index

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article mainly introduces how to optimize the MySQL index, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Create a test test table

CREATE TABLE `test` (

`id`int (11) NOT NULL AUTO_INCREMENT

`c1` varchar (10) DEFAULT NULL

`c2` varchar (10) DEFAULT NULL

`c3` varchar (10) DEFAULT NULL

`c4` varchar (10) DEFAULT NULL

`c5` varchar (10) DEFAULT NULL

PRIMARY KEY (`id`)

KEY `idx_test_ c1234` (`c1`, `c2`, `c3`, `c4`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values.

Insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values.

Insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values.

Insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values.

Insert into `test` (`id`, `c1`, `c2`, `c3`, `c4`, `c5`) values.

Analyze the following Case index usage

Case 1:

Execute the following SQL statement:

① EXPLAIN SELECT * FROM test WHERE C1 / A1 'AND c2 / a2' AND c3 / A3 'AND c4 / A4'

② EXPLAIN SELECT * FROM test WHERE C1 / A1 'AND c3 / A3' AND c2 / A2 'AND c4 / A4'

③ EXPLAIN SELECT * FROM test WHERE C1 / A1 'AND c4 / A4' AND c3 / A3 'AND c2 / A2'

④ EXPLAIN SELECT * FROM test WHERE c4 / A4 / AND / c2 / a2 / AND / c3 / a3 / AND / c1 / a1'

Analysis: the order of creating the joint index is C1 Magi, c2, c3, and c4. The execution results of the above four groups of explain are all the same: type=ref,key_len=132,ref=const,const,const,const.

Conclusion: changing the order of index columns does not change the execution result of explain when executing constant equivalent queries, because the underlying optimizer of MySQL automatically optimizes, but it is recommended that SQL statements be written according to index columns.

Case 2:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1rooma1' AND c2fua2'

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, AND c3 > a3 'AND c4, rooma4

Analysis: when the range appears, the type=range,key_len=99 is more than the unused range key_len=66, indicating that the index is used, but comparing the execution results in Case 1 shows that the s index on c4 is invalid.

Conclusion: the index column on the right side of the range is invalid, but the index of the current position of the range (c3) is valid, as can be proved from key_len=99.

Case 2.1:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, AND c4 > c4' AND c3, rooma3'

Analysis: compared with the above explain execution results, key_len=132 states that 4 indexes are referenced, so the MySQL underlying optimizer for this SQL statement will be optimized (optimized to WHERE c1, indexes, a1, AND, c2, optimizer, WHERE, c3, indexes, columns, c3, AND, c4, c4). So all four indexes are used.

Conclusion: the index columns on the right side of the range fail in order: C1, c2, c3, c4, if c3 has a range, c4 fails; if c4 has a range, there are no invalid index columns, so all indexes will be used.

Case 2.2: (disclaimer: the explanation of this Case remains to be examined)

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE C1 > 'a1' AND c2' AND c3 'AND c4'

Analysis: if the scope is used at C1, then type=ALL,key=NULL, index invalidation, full table scan, which violates the best left prefix principle, and the leader is dead, because C1 is mainly used for scope, not query.

Solution: use override indexes.

Conclusion: in the principle of the best left prefix of the index, if the index of the leftmost leading column (leading elder brother) fails, then the later index fails.

Case 3:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, AND c4, ORDER BY c3

Analysis: use the best left prefix principle: the middle brother can not be broken, so the C1 and c2 indexes (search) are used. From key_len=66,ref=const,const, it can be seen that the c3 index column is also used in the order by sorting process (that is, the c3 index is also used).

Question: how to prove that order by c3 also uses indexes?

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, AND c4, ORDER BY c5

Analysis: because c5 non-index fields, when sorting with order by c5, Using filesort appears in the column, and file sorting is used, which means that index sorting is not used and the performance is low.

Case 3.1:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1 AND c2 ORDER BY c3

Analysis: as you can see from key_len=66,ref=const,const, only C1 and c2 indexes are used for lookups, and c3 indexes are used for sorting.

Case 3.2:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, ORDER BY c4

Analysis: it can be seen from key_len=66,ref=const,const that the query uses C1 and c2 indexes. Due to the use of c4 for sorting, c3 is skipped, the middle is broken, and c4 indexes can not be used for sorting, resulting in Using filesort.

Case 4:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c5, ORDER BY c2, c3

Analysis: the search only uses indexes C1 and c2 and c3 for sorting, no Using filesort.

Case 4.1:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c5, ORDER BY c3, c2

Analysis: the execution result is the same as that of explain in Case 4, but there is Using filesort, because the order of index creation is C1, c2, c3, c4, but when sorting, c2 and c3 are reversed.

Case 4.2:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, ORDER BY c2, c3

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, AND c5, ORDER BY c2, c3

Analysis: c5 is added to the query, but the execution result of explain is the same, because c5 does not create an index.

Case 4.3:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c2, AND c5, ORDER BY c3, c2

Analysis: compared to Case 4.1, Using filesort does not appear in Extra, because c2 is constant and is optimized in sorting, so the index is not reversed and Using filesort does not appear.

Case 5:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c4, GROUP BY c2, c3

Analysis: if gourp by does not use an index, it will result in a temporary table (Using temporary). The underlying layer will first use order by to sort. If group by uses index grouping, the prerequisite is that order by uses index sorting. Above, only the index on C1 is used to query, because c4 is broken in the middle. According to the leftmost prefix principle of the index, the index key_len=33,ref=const means that only one index is used.

Case 5.1:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE c1, AND c4, GROUP BY c3, c2

Analysis: compared with Case 5, the positions of c2 and c3 are exchanged during group by, resulting in inability to meet order by (Using filesort), that is, unable to meet group by (Using temporary), which is extremely bad. Reason: C3 and c2 are in the opposite order of index creation.

Case 6:

Execute the SQL statement: EXPLAIN SELECT * FROM test WHERE C1 > 'a1' ORDER BY C1

Analysis:

① created the index on C1, c2, c3, and c4, and directly used the range on C1, resulting in index failure. Full table scan: type=ALL,ref=NULL. Because at this time, C1 is mainly used for sorting, not a query.

② uses C1 for sorting, and Using filesort appears.

③ solution: use override indexes.

Execute the SQL statement: EXPLAIN SELECT C1 FROM test WHERE C1 > 'a1' ORDER BY C1

Case 7:

Execute the SQL statement:

EXPLAIN SELECT c1 FROM test ORDER BY c1 ASC, c2 DESC

Analysis: although the sorted field columns are in the same order as the index, and order by default ascending order, where c2 DESC becomes descending, resulting in a different sort from the index, resulting in Using filesort.

Case 8:

Execute the SQL statement: EXPLAIN SELECT C1 FROM test WHERE C1 IN ('a1 FROM test WHERE c1') ORDER BY c2meme c3

Analysis: for sorting, multiple equality conditions are also range queries.

Summary:

① MySQL supports two ways of sorting filesort and index,Using index, which means that MySQL scans the index itself to complete the sorting. The efficiency of index is high, while that of filesort is low.

② order by uses Using index in two situations:

A. the order by statement uses the leftmost front column of the index.

b. The combination of the where clause and the order by clause conditional column satisfies the leftmost front column of the index.

③ tries to sort on index columns, following the best left prefix principle for index establishment (the order in which the index is created).

④ Using filesort is generated if the condition of order by is not on the index column.

⑤ group by is similar to order by in that it essentially sorts before grouping, following the principle of the best left prefix in the order in which the index is created. Note that where is higher than having, so don't go to having for the qualification that can be written in where.

Thank you for reading this article carefully. I hope the article "how to optimize the MySQL Index" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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: 263

*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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report