How to correctly create MySQL indexes

Indexing is similar to building bibliographic indexes in university libraries, which can improve the efficiency of data retrieval and reduce the IO cost of the database. MySQL performance starts to decline around 3 million records, although the official documentation says 5-8 million records, so it is very necessary to create indexes for large amounts of data. MySQL provides Explain, which is used to display detailed information about SQL execution and to optimize indexes.

What is an index?

MySQL's official definition of an index is: An index is a data structure that helps MySQL obtain data efficiently. We can simply understand it as: a data structure that quickly finds a sorted order. There are two main structures of MySQL index: B+Tree index and Hash index. When we talk about indexes, unless otherwise specified, we usually refer to indexes organized in a B-tree structure (B+Tree index). The index is shown in the figure:

The outermost light blue disk block 1 contains data 17, 35 (dark blue) and pointers P1, P2, P3 (yellow). The P1 pointer indicates a disk block smaller than 17, P2 is between 17 and 35, and P3 points to a disk block larger than 35. The real data exists in the sub-leaf nodes, which are the bottom layer 3, 5, 9, 10, 13... Non-leaf nodes do not store real data, but only store data items that guide the search direction, such as 17, 35.

Search process: For example, to search for data item 28, first load disk block 1 into memory, incur an I/O, and use binary search to determine the P2 pointer. Then it is found that 28 is between 26 and 30, and disk block 3 is loaded into memory through the address of the P2 pointer, and the second I/O occurs. Disk block 8 is found in the same way, and the third I/O occurs.

The reality is that the top three layers of B+Tree can represent millions of data. Millions of data only require three I/O operations instead of millions of I/O operations, and the time improvement is huge.

Data table indexes can improve data retrieval efficiency and reduce database IO costs. Indexes can also reduce database sorting costs. Sorting and grouping operations mainly consume CPU resources and memory, so being able to make good use of indexes in sorting and grouping operations will greatly reduce the consumption of CPU resources. Below we will briefly analyze how to correctly create MySQL data indexes.

How to determine whether an index needs to be created?

1. When some fields need to be frequently used as query conditions, you need to create indexes for them

Everyone should know this, but what is considered frequent? Comprehensively analyze all SQL statements you execute. It would be best to list them all one by one. Then after analysis, we found that some of the fields are used in most SQL query statements, so we decisively created indexes for them.

2. Fields with poor uniqueness are not suitable for indexing

What are the fields that are not unique enough? Such as status field and type field. Fields that only store a few fixed values, such as user login status, message status, etc. This involves the characteristics of index scanning. For example, if you search for data with key values ​​A and B through the index, and find a matching piece of data through A, which is on page X, and then continue scanning, and find data matching A on page Y, the storage engine will discard the data on page X and store the data on page Y until all data matching A is found. Then, if you search for field B and find data matching field B on page X, the storage engine will scan page X again, which means page X will be scanned twice or even more times. By analogy, the same data page may be read, discarded, and read again repeatedly, which undoubtedly greatly increases the IO burden on the storage engine.

3. Fields that are updated too frequently are not suitable for index creation

When you create an index for a field, if you update the field data again, the database will automatically update its index. Therefore, when the field is updated too frequently, the index will be constantly updated, and the performance impact can be imagined. Fields that are updated only once after being searched dozens of times are more suitable for indexing. If a field is updated multiple times within the same time period, it cannot be indexed.

4. Fields that do not appear in the where condition should not be indexed

There is actually nothing much to say about this. It is useless to create an index for fields that will not be used as query conditions.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links