VISIT WEBSITE >>>>> http://gg.gg/y83ws?5691659 <<<<<<
Explore why MySQL indexes are so fast? What exactly is an index? Binary tree simply means that the left node is larger than the right node. In an ideal case, its search speed is close to the performance of dichotomy o log2n. Because the sorting time in memory is very fast and negligible, the total consumption time depends on the number of IO operations. The search speed of binary tree depends on the tree height.
Each query interface is an IO operation, which is also the bottleneck of performance. However, there will also be such a situation. It is also a binary tree, but its tree is very high, resulting in multiple IO operations for one query, which is extremely inefficient. Balanced binary tree can solve the problem of low query efficiency caused by the instability of binary tree.
Characteristics of balanced binary tree: the left and right node levels of the tree are the highest, with a difference of one level. In the case of insertion or deletion, the whole binary tree is balanced by left rotation or right rotation, and there will not be a lot of hierarchy differences. The performance of balanced binary tree is close to binary search o log2n. The balanced binary tree only needs two IO operations to find the record with ID 8.
But think about it carefully. What if there is a lot of data? Assuming that the data table has W data, about 20 IO operations are required according to o log2n.
In addition, the balanced binary tree does not support range query. Range query needs to be traversed from the root node every time, which is extremely inefficient. The previous tree structures are suitable for memory lookup with small amount of data, also known as internal lookup. In , R. Bayer and E. McCreight proposed a balanced multitree B-tree suitable for external search. MySQL data files exist on the disk. Each time, the memory is read according to the size of one page generally 16K.
Like binary trees and balanced binary trees, IO operations must be performed every time the node is read, so the higher the tree, the more IO operations. To improve the query efficiency, the first thing to solve is to reduce the tree height. Imagine that each IO operation reads a node and reads 16K memory data, but the data of each node is actually much less than 16K.
Assuming that the node data size is 16b, in order to enable one IO operation to read more nodes, we can store index data on each node as much as possible. Features of B-tree: 1. Each node stores multiple elements.
The reason is that while doing insert or update, a database needs to insert or update the index values as well. You can create a unique index on a table. A unique index means that two rows cannot have the same index value. Here is the syntax to create an Index on a table. You can create a simple index on a table. A Simple index allows duplicate values in a table.
If you want to index the values in a column in a descending order, you can add the reserved word DESC after the column name. You can add a primary key as well in the same way. The following code block is an example to add the primary key in an existing table.
Comentários