Why do you want to know about indexes?
Real case
Case 1: The university has been studying reptiles for some time, and it has captured 300w user answer data from Zhihu and stored it in mysql data. At that time, the index was not known, and a simple "sql for searching all answers by user name" took about half a minute to execute, which could not meet the normal use at all.
Case 2: There are often many slow sql risk warnings in the database of near-online application, but little is known about database optimization since work. For example, a user data page needs to perform many database queries, and its performance is very slow. You can barely access by increasing the timeout, but the performance needs to be optimized.
Exponential advantage
Appropriate index can greatly reduce the amount of data scanned by mysql server, avoid memory sorting and temporary tables, and improve the query performance of applications.
Exponential type
There are many index types of mysql data, such as primarykey, unique and normal, but the data structure of the underlying storage is BTREE. Some storage engines also provide hash index and full-text index.
BTREE is a common index structure that needs to be optimized, and all of them are discussed based on BTREE.
B tree
The simplest and rudest way to query data is to traverse all records; If the data is not duplicated, it can be organized into an ordered binary tree and queried by method of bisection algorithm, which greatly improves the query performance. BTREE is a more powerful sort tree, which supports multiple branches, has a lower height, and can insert, delete and update data faster.
The index files of modern database and file blocks of file system are organized into BTREE.
Each node of a btree contains a key, data and a unique child node pointer.
The concept of three-dimensional d >. = 1。 Assuming that the degree of btree is d, each internal node can have n=[d+ 1, 2d+ 1) keys and n+ 1 child node pointers. The height of the tree is h=Logb[(N+ 1)/2].
In index and file system, the nodes of B-tree are always calculated to be close to the size of a memory page (also the size of a disk sector), and the degree of the tree is very large. In this way, the number of disk I/O is equal to the height h of the tree. Suppose b= 100, a tree with one million nodes, H will have only three levels. That is, only three disk I/O are needed to complete the search, and the performance is very high.
Index query
After the index is established, appropriate query statements can give full play to the advantages of the index.
In addition, because the query optimizer can parse the sql statements of the client, it will adjust the conditional order of the sql query statements to match the appropriate index.