What is the MySQL index and how is it used? It's a very detailed arrangement.

When you learn MySQL, you often meet the index, what exactly is an index? Before I was only vague will use, but let me explain it is really a bit difficult, so that in the free time to consult the next book, in case someone asked me, I sorted out the next, a little impression is good, said will not be very embarrassed, after all, also contact these some years, nonsense don't say, Apache PHP MySQL focuses on:

begin!

First, what is the index

1. Introduction to the Index
An index is actually a data structure stored on disk as a file, and index retrieval requires disk I/O operations. Unlike main memory, disk I/O is mechanically active, so the time spent on disk I/O is huge.

2.IO Introduction
IO in the computer input and output, because the program and runtime data is in memory, by the CPU this ultra-fast computing core to execute, involving data exchange, usually disk, network, etc., need IO interface. Life example: Need to remember the key things need to write in the notebook, when needed to see, every time you go to the notebook to read the record is IO, if the memory of a good person will remember this thing, directly can read out, this is the cache (the computer is more unable to keep).

Second, the index algorithm

1. The database is basically implemented using the B+tree algorithm

2. Database indexing is the pros and cons of using disk I/O times to evaluate the index structure

3.b-tree
(1) The definition of B-tree, it is known that the retrieval requires access to H-1 nodes (the root node resident memory) at most once. The designer of the database system skillfully exploits the principle of disk pre-reading, setting the size of a node equal to one page so that each node can be fully loaded with only one I/O

(2) The actual implementation of B-tree also need to use the following techniques: each time a new node, directly request a page of space, so that a node can be physically stored in a page, in addition to the computer storage allocation is page-aligned, the implementation of a node only once I/O

(3) Using B-TREE storage structure, the number of I/O in the search is generally not more than 3 times, so using B-tree as index structure efficiency is very high, but the nodes in B-tree can contain a lot of keyword information and branches according to the actual situation

4.b+tree
(1) B-tree's search complexity is O (h) =o (LOGDN), so the larger the tree's out D, the smaller the depth h, the less I/O. B+tree can increase the width of the degree d, because each node size is a page size, so the upper limit depends on the size of the key and data within the node

(2) because the internal node of the B+tree is stripped of data, it can have a greater degree of out-of-order and better performance

Third, clustered index and non-clustered index

1. Clustered index
(1) The physical order of the data of the clustered index is consistent with the index order, that is, if the index is adjacent, then the corresponding data must also be stored adjacent to the disk. Clustered indexes are much more efficient than non-clustered index queries

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

(4) Default index of InnoDB

2. Non-clustered index

(1) Non-clustered index, similar to the book's appendix, which section of the professional terminology appears, these terms are sequential, but the location is not the order. However, a table can have more than one non-clustered index

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

(3) A clustered index is an index of a nonclustered index, that is, the index of the primary + secondary index, the advantage of this primary + secondary index is that the secondary index tree does not need to be updated when data row movement or page splitting occurs, because the secondary index tree stores the primary key keyword for the primary index, not the physical address of the data

(4) Non-clustered index to access two indexes

Iv. Types of indexes

1.UNIQUE (unique index): Can not appear the same value, can have a null value

2.INDEX (normal index): Allows the same indexed 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 is inefficient

5. Composite index: Essentially Jianjian multiple words into an index, the combination of column values must be unique

Five, indexing skills

1. The index will not contain NULL columns

(1) As long as the column contains a null value, it will not be included in the index, as long as there is a column in the composite index with a null value, then this column is not valid for this compliance index

2. Using a short index

(1) Index The string, if you can specify a prefix length. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes can improve query speed and save disk space and I/O operations

3. Index column sorting

(1) The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. Therefore, the database default sort can meet the requirements of the case do not use the sorting operation, try not to include multiple columns of the sorting, if necessary to build a composite index of these columns

4.like Statement Operations

(1) Generally do not encourage the use of like operations, if not used, pay attention to the correct use. Like '%aaa% ' does not use indexes, and like ' aaa% ' can use indexes

5. Do not perform calculations on columns

6. Do not use not in, <>,! = operation, but <,<=,=,>,>=,between,in can be used to index the

7. The index is to be established on the field where the select operation is frequently performed

(1) This is because, if these columns are seldom used, then there is no index that can significantly change the query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

8. The index is to be built on a field that is unique in value comparison

9. For columns that are defined as text, image, and bit data types, the index should not be incremented. Because the amount of data in these columns is either quite large or has very little value

10. Columns that appear in where and join need to be indexed

The 11.where query condition has an equal sign (where column! = ...), and MySQL will not be able to use the index

12. If a function is used in the query condition of the WHERE clause (for example: Where day (column) = ...), MySQL will not be able to use the index

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

14.explain can help developers analyze SQL problems, explain shows how MySQL uses indexes to process SELECT statements and join tables, helping to select better indexes and write more optimized query statements

Vi. Indexes and Locks

1. The lock is used to the index is the row lock, if no use of the index is a table lock, so the operation of the data must be used to lock the line

(1) If there is no index, in the data selection or positioning is done through the form of full table scan, which will form a table lock, if there is an index will be directly positioned to the specified row, is the formation of row lock, here Note that when updating data, if the index is not used to the full table scan

End

This content is most of my usual accumulation, and some not clear reference to the network and books, a lot of understanding!

Related articles:

MySQL index name How to use, when to use

What is an index? MySQL current main types of index

Related videos:

Index simple introduction-six days to take you to play MySQL video tutorial