Shulou(Shulou.com)06/01 Report--

Editor to share with you how to use the mysql index, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

What is the index

1. Brief introduction to Index

Index is actually a kind of data structure stored on disk in the form of files, and index retrieval requires disk Imax O operation. Unlike the main memory, there is a mechanical motion cost on disk Imax O, so the time consumption of disk Imax O is huge.

Introduction to 2.IO

IO refers to input and output in the computer. Because the program and runtime data reside in memory, it is executed by CPU, a super-fast computing core. When it comes to data exchange, usually disk, network, etc., IO interface is needed. Life example: need to remember the key things need to be written in the notebook, when you need to take it out to see, every time you go to the notebook to read the record is IO, if people with a good memory will remember this thing, can directly read it, this is the cache (even more can not be kept in the computer).

2. Indexing algorithm

1. Databases are basically implemented using B+Tree algorithm.

two。 Database index is to evaluate the index structure by using the number of disk Ibind O times.

3.B-Tree

(1) according to the definition of B-Tree, you need to access at most one node for each search (the root node is resident in memory). The designers of the database system skillfully make use of the principle of disk pre-reading by setting the size of a node to equal to one page, so that each node can be fully loaded only once.

(2) the actual implementation of B-Tree also needs to use the following skills: each new node, directly apply for a page of space, so as to ensure that a node is physically stored in a page, coupled with the computer storage allocation is page-aligned, the implementation of a node only need to go to O

(3) using the B-Tree storage structure, the number of B-Tree O searches is generally no more than 3 times, so it is very efficient to use B-Tree as the index structure, but the nodes in B-tree can contain a lot of keyword information and branches according to the actual situation.

4.B+Tree

The main results are as follows: (1) the search complexity of B-Tree is O (h) = O (logdN), so the larger the degree d of the tree is, the smaller the depth h is, and the less the times of I logdN O are. B+Tree can just increase the width of degree d, because each node size is a page size, so the upper limit of output degree depends on the size of key and data in the node.

(2) because data is removed from the inner node of B+Tree, it can have greater output and better performance.

Clustered index and nonclustered index

1. Clustering index

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

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

(4) default index of Innodb

two。 Non-clustered index

(1) A nonclustered index, similar to the appendix to a book, in which chapter does the technical term appear? these terms are in order, but there is no order in which they appear. However, a table can have more than one non-clustered index

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

(3) clustered index is a kind of index of non-clustered index, that is, primary + secondary index. the advantage of this primary + secondary index is that when data rows move or page split occurs, the secondary index tree does not need to be updated. because the secondary index tree stores the primary key keywords of the primary index, not the specific physical address of the data.

(4) so the non-clustered index has to access the index twice.

IV. Type of index

1.UNIQUE (unique index): cannot have the same value, can have null value

2.INDEX (General Index): allows the same index content to appear

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

4.FULLTEXT INDEX (full-text index): can target a word in a value, but it is very inefficient

5. Composite index: in essence, multiple fields are built into one index, and the combination of column values must be unique.

Fifth, indexing skills

1. The index will not contain columns with NULL

(1) as long as a column contains a null value, it will not be included in the index. As long as a column in a composite index contains a null value, then this column is invalid for the matching index.

two。 Use short index

(1) Index the serial column and specify a prefix length if possible. For example, if you have a column of char, and if most of the values are unique within the first 10 or 20 characters, do not index the entire column. The short index can not only improve the query speed, but also save disk space and Imax O operation.

3. Index column sorting

(1) the mysql query uses only one index, so if the index is already used in the where clause, the columns in the order by will not use the index. Therefore, if the default sorting of the database can meet the requirements, do not use the sorting operation, try not to include the sorting of multiple columns, and it is best to build a compound index for these columns if necessary.

4.like statement operation

(1) in general, the use of like operation is not encouraged. If it must be used, pay attention to the correct way of using it. Like'% aaa%' does not use indexes, while like 'aaa%' can use indexes

5. Do not operate on columns

6. Do not use NOT IN,! = operation, but =, BETWEEN,IN can use the index

7. The index should be based on fields that often perform select operations.

(1) this is because if these columns are rarely used, the presence or absence of an index does not significantly change the query speed. On the contrary, due to the increase of the index, the maintenance speed of the system is reduced and the space requirement is increased.

8. The index should be based on a field with a unique value.

9. Columns defined as 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. Columns that appear in where and join need to be indexed

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

twelve。 If a function is used in the query condition of the where sentence (for example: where DAY (column) = …) , mysql will not be able to use the index

13. In join operations (when you need to extract data from multiple data tables), mysql can use indexes only if the data types of primary and foreign keys are the same, otherwise it will not be used if the index is established in time.

14.explain can help developers analyze SQL problems. Explain shows how mysql uses indexes to handle select statements and join tables, which can help select better indexes and write more optimized query statements.

VI. Index and lock

1. The index used in the lock is the row lock, and if the index is not used, it is the table lock, so the operation data must use the lock.

(1) if no index is established, the data selection or positioning is carried out in the form of a full table scan, so that a table lock will be formed. If there is an index, it will be located directly to the specified row, that is, a row lock will be formed. Note here that if the index is not used to update the data, the whole table will be scanned.

The above is all the content of how the mysql index is used, thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.