What is mysql index and how to use it? Organized in great detail

When learning mysql, you often encounter indexes. What is an index? I was only vaguely able to use it before, but it was a bit difficult for me to explain it, so I took advantage of my free time to read some books, just in case someone asked me in the future, I sorted it out and wrote it down, which gave me some impressions. It's good after all, and it's quite embarrassing to say no. After all, I've been exposed to these for a few years. Let's not talk nonsense. Let's talk about the key points of apache php mysql:

begin!

1. What is an index?

1. Index introduction
The index is actually a data structure stored on the disk in the form of a file. Index retrieval Disk I/O operations are required. Unlike main memory, disk I/O involves mechanical movement costs, so the time consumption of disk I/O is huge.

2.IO Introduction
IO refers to input and output in computers. Since programs and runtime data reside in memory, they are executed by the ultra-fast computing core of the CPU, which involves data exchange. Places, usually disks, networks, etc., require IO interfaces. Life example: All key things that need to be remembered need to be written in a notebook. Take them out and read them when needed. Every time you go to the notebook to read the records, it is IO. If people with good memory will remember this matter, they can do it directly. Read it out, this is the cache (it cannot be saved all the time in the computer).

2. Index algorithm

1. The database is basically implemented using the B Tree algorithm

2. The database index uses the number of disk I/O To evaluate the quality of the index structure

3.B-Tree
(1) The definition of B-Tree shows that a maximum of h-1 nodes need to be accessed for one retrieval (the root node is resident in memory). The designers of the database system cleverly took advantage of the disk read-ahead principle and set the size of a node to equal one page, so that each node only needs one I/O to be fully loaded

(2) Actual implementation B-Tree also needs to use the following skills: each time a new node is created, it directly applies for a page of space. This ensures that a node is physically stored in a page. In addition, the computer storage allocation is aligned by page, and this is achieved. A node only needs one I/O

(3) Using B-Tree storage structure, the number of I/Os during search will generally not exceed 3 times, so using B-Tree as an index structure is very efficient. , but the nodes in B-tree can contain a large amount of keyword information and branches depending on the actual situation

4.B Tree
(1) The search complexity of B-Tree is O(h)=O (logdN), so the greater the out-degree d of the tree, the smaller the depth h, and the fewer the number of I/Os. B Tree can exactly increase the width of out-degree d, because each node is one page size, so the upper limit of out-degree depends on the size of the key and data in the node

(2) Since the internal nodes of B Tree Data is removed, so it can have a larger out-degree and thus have better performance

3. Clustered index and non-clustered index

1. Clustered index
(1) The physical storage order of clustered index data is consistent with the index order, that is: as long as the indexes are adjacent, the corresponding data must also be stored adjacently on the disk. Clustered indexes are much more efficient than non-clustered index query

(3) Each table can only have one clustered index, because records in a table can only be stored in one physical order

(4) Innodb's default index

2. Non-clustered index

(1) Non-clustered index, similar to the appendix of a book, in which chapter the professional term appears, these The technical terms are in order, but the position where they appear is not. However, a table can have more than one non-clustered index

(2) The implementation principle is to use leaf nodes to store the primary key of the reference row (it can be said to be a clustered index)

(3) Clustered index It is an index of non-clustered index, that is, the indexing method of primary and secondary indexes. The advantage of this primary and secondary index is that when data row movement or page split occurs, the auxiliary index tree does not need to be updated, because the auxiliary index tree stores The primary key keyword of the primary index, rather than the specific physical address of the data

(4) Therefore, the non-clustered index needs to access the index twice

4. Index type

1.UNIQUE (unique index): the same value cannot appear, and NULL values ​​are allowed

2.INDEX (ordinary index): the same index content is allowed

3.PROMARY KEY (primary key index): The same value is not allowed

4.FULLTEXT INDEX (full-text index): It can target a certain word in the value, but the efficiency is very poor

5. Combined index: Essentially, multiple fields are built into one index, and the combination of column values ​​must be unique

5. Indexing skills

1. The index is not Columns that will contain NULL

(1) As long as the column contains NULL values, they will not be included in the index. As long as there is a column in the composite index that contains NULL values, then this column is eligible for the index. It is invalid

2. Use short index

(1) to index the string. If possible, you should specify a prefix length. For example, if you have a column of char(255), don't index the entire column if most values ​​are unique within the first 10 or 20 characters. Short indexes can not only improve query speed but also save disk space and I/O operations

3. Index column sorting

(1) MySQL query only uses one index, so if the index has been used in the where clause, the columns in order by will not use the index. Therefore, do not use sorting operations when the default sorting of the database can meet the requirements. Try not to include sorting of multiple columns. If necessary, it is best to build composite indexes for these columns

4.like statement operations

(1) Generally, the use of like operations is discouraged. If it must be used, pay attention to the correct way of use. Like '�a%' will not use the index, but like 'aaa%' can use the index

5. Do not perform operations on columns

6. Do not use NOT IN, <> ;,! = operation, but <,<=, =,>,>=,BETWEEN,IN can use indexes

7. Indexes should be established on fields where select operations are often performed

(1) This is because if these columns are rarely used, the presence or absence of indexes will not significantly change the query speed. On the contrary, due to the addition of indexes, it reduces the maintenance speed of the system and increases the space requirements

8. The index should be established on the fields with relatively unique values

9. For those defined as Columns of text, image, and bit data types should not be indexed. Because the amount of data in these columns is either quite large or has very few values

10. The columns appearing in where and join need to be indexed

11.There is an inequality sign in the query condition of where ( where column != …), mysql will not be able to use the index

12. If a function is used in the query condition of the where clause (such as: where DAY(column)=…), mysql will not be able to use the index

13. In the join operation (when data needs to be extracted from multiple data tables), mysql can only use the index when the data type of the primary key and the foreign key is the same, otherwise the index will not be used if it is established in time

14.explain can help developers analyze SQL problems. Explain shows how mysql uses indexes to process select statements and connection tables. It can help choose better indexes and write more optimized query statements

6. Indexes and locks

1. If the lock uses an index, it is a row lock. If the index is not used, it is a table lock, so the data to be operated must use a lock.

(1) If there is no index, data selection or positioning will be done through a full table scan, which will form a table lock. If there is an index, the specified row will be directly located, that is A row lock is formed. Note here that if the index is not used when updating the data, the entire table will be scanned

end

Most of this content is I usually accumulate some unclear information from the Internet and books, so please forgive me!

Related articles:

How to use mysql index name and when to use it

What is an index? There are currently several main index types in Mysql

Related videos:

A brief introduction to indexes - a video tutorial to take you through MySQL in six days

The above is the detailed content of What is mysql index and how to use it? Organized in great detail. For more information, please follow other related articles on the PHP Chinese website!