This article is participating in “Java Theme Month – Java Debug Notes Event”, see < Event link > for more details.

preface

  • The existence of database is indispensable in network development. Now website development is no longer the previous static web page data storage based on data growth out of the big data is king

Problem description

  • The only thing a database can’t talk about is indexes. The presence of indexes can greatly speed up the query of data. Therefore, the major databases are constantly optimizing their indexes
  • When we design the database in the early stage of system development, we will try our best to consider the design of the index and can not use the index frequently
  • Even so, I ran into problems. In one of my SQL, I wrote it according to index rules but it was still very slow. He didn’t exactly follow the index

Problem analysis

  • As above, I created it temporarily. Five of the field ids are primary keys. Let’s see what the index has

  • In addition to the primary key, there is a secondary keyuser_nickandschoolA federated index composed of.

  • Each of the two SQL statements above performs the index. One didn’t go through the index. User_nick must be able to go through the index based on the left-most matching rule. This is a classic index failure problem

  • In the case of the index tree above, if we were to execute the second failed SQL, we would have to globally traverse the index tree if we wanted to query all the contents containing a through the index. The row data is then retrieved through the index.
  • But if we do a direct full table scan instead of a global index tree scan, there is one less index to fetch content. It’s faster. So there is no index in this SQL

Index failure condition

  • Full fuzzy matching of index fields in a query results in index invalidation
  • An or query in the search criteria invalidates the index
  • The index type must match, otherwise the type conversion will cause index invalidation; For example, userName=’ ZXH ‘can go. UserName =123
  • A range query can invalidate an index
  • Does not mean that a query will invalidate an index
  • Using field expressions can also invalidate indexes
  • Not in invalidates the index

conclusion

  • Indexes are important but can also produce slow queries if used poorly
  • Understanding the internal storage of indexes helps you understand why these conditions can cause indexes to fail. Keep in mind that indexes are data. Is a directory level of data

Like, like, comment