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

This article shows you how to create an index in MySQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

What is an index?

MySQL officially defines index as: index (Index) is a data structure that helps MySQL to obtain data efficiently. We can simply understand it as: quickly find a sorted data structure. There are two main structures of Mysql index: B+Tree index and Hash index. The index we usually talk about, if not specifically specified, generally refers to the index organized by the 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 represents blocks less than 17, P2 is between 17 and 35, and P3 points to blocks greater than 35. The real data exists in the cotyledon node, that is, the bottom layer 3, 5, 9, 10, 13. Non-leaf nodes do not store real data, but only data items that guide the search direction, such as 17 and 35.

Search process: for example, to search for 28 data items, first load disk block 1 into memory, occur an Icano, and use a 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 Ihand O occurs. Disk block 8 is found in the same way, and the third I _ hand O occurs.

The truth is, the upper three layers of B+Tree can represent millions of data, millions of data occurred only three times, instead of millions of times, the time boost is huge.

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

How can I tell if an index needs to be created?

1. Some fields need to be indexed when they are frequently used as query conditions.

We should all know this. What is frequent? Analyze all the SQL statements you execute. It's best to list them one by one. Then analyze and find that some of these fields are used in most SQL statements, so decisively index him.

2. Fields with poor uniqueness are not suitable for indexing.

What is a field that is too unique? Such as status field, type field. Fields that store only a few fixed values, such as the user's login status, the status of the message, and so on. This involves the feature of index scanning. For example, through the index to find some data with key values An and B, find some matching data through A, this data is on the X page, and then continue to scan, and find that the data matching An appears on the Y page. Then the storage engine will discard the data on the X page, and then store the data on the Y page until all the data corresponding to An is found, and then look for the B field. If you find that there is data corresponding to the B field on the X page, then he will scan the X page again, which means that the X page will be scanned 2 or more times. And so on, so the same data page may be read repeatedly, discarded, and read repeatedly, which undoubtedly puts a great burden on the storage engine on IO.

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, so when the field is updated too frequently, the index will be constantly updated, and the performance impact can be imagined. Fields that are retrieved dozens of times before they are updated are more in line with the indexing criteria. If a field is updated multiple times in the same period of time, it cannot be indexed decisively.

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

There is nothing to say about this, and it is useless to index fields that will not be used as query criteria.

The above is how to create an index in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.