In-depth principles rather than rote memorization
1. Focus on test sites
1. Principle, type and structure of
index 2. Precautions and usage principles for index creation
3. How to troubleshoot and eliminate slow queries
High-energy early warning: the test site of this chapter is partial to theory
2. What is an index?
Why do you need an index?
1. An index is a data structure in which one or more columns in a data table are sorted.
2. An index can greatly improve the retrieval speed (review the search structure you know)
3. Creating and updating the index itself will also consume space and time
Three, what is
Search structure evolution history
1. Linear search: find one by one; simple to implement; shortcoming is too slow
2. Very search: requires data to be ordered, easy to implement; shortcoming is particularly slow to insert
HASH.: query fast; disadvantage is that it takes up space, not Too suitable for storing large-scale data
4. Binary search tree: insertion and query is fast (
log(n)), the disadvantage is that it cannot store large-scale data, and the complexity is degraded.
4. Balance tree: To solve the problem of
bstdegradation, the tree is balanced. But it still cannot solve the problem that the tree height is very deep when there are too many nodes.
5. Multi-way search tree: a father has multiple child nodes (degrees); when there are too many nodes, the tree height will not be particularly deep
. 6. Multi-way balanced search tree:
B-Tree, why use
1. Multi-balanced search tree (each node up to
m(m >= 2)a child, called the
2 leaf nodes have the same depth
data 3. nodes
keyfrom left to right are increasing
B+B-TreeVariations of the tree
B+Treedata structure used as an index
2. Only the leaf nodes have pointers to the records (why? The degree of the tree can be increased)
3. The leaf nodes are connected by pointers. why? Implement range query
Mysqlthe type of index
MysqlCreate index type
1. Ordinary index (
2. Unique index, the value of the index column must be unique (
CREATE UNIQUE INDEX)
3. Multi-column index
4. Primary key index (
PRIMARY KEY), a table can only have one
5. Full-text index (
FULLTEXT INDEX), only
1.2.xbeginning Increase the full-text index, that is
Mysql5.6, the version is only upgraded to
6. When will the index be created?
Construction of the table when you need to create a query based on the needs of the index
field (often used as a query criteria 1.
2. often used as a field-table joins
3. often appear in
group byafter the field
7. What should I pay attention to when creating an index?
1. Non-null fields
Mysqlit is difficult to optimize the query for null values. (Many Internet companies’ table building specifications require index fields to have default values)
2. The degree of discrimination is high, and the dispersion is in. Try not to have a large number of the same value as the field value of the
index. 3. The length of the index should not be too long (more time-consuming)
8. When does the index become invalid?
Memorization formula: fuzzy matching, type implicit conversion, leftmost matching
1. Statements that
LIKE, fuzzy search
2. Implicit type conversion occurs (
Pythonnote that in this dynamic language query)
3. The leftmost prefix principle is not satisfied (think Think why is the leftmost match?)
9. What is a clustered index and a non-clustered index?
1. Clustered or non-clustered refers to whether the
B+Treeleaf node stores pointers or data records
. 2. The
MyISAMindex and data are separated, and the non-clustered index is used
. 3. The
InnoDBdata file is the index file, and the primary key index is the clustered index.
X. File storage methods of non-clustered and clustered index
CREATE TABLE myisam_table ( `id` INTEGER PRIMARY KEY, title VARCHAR(80) ) ENGINE = MYISAM; CREATE TABLE innodb_table ( `id` INTEGER PRIMARY KEY, `url_md5` char(32) KEY `idx_url` (`url_md5`) ) ENGINE = InnoDB;
Eleven, clustered index and auxiliary index
The secondary index first finds the primary key and then finds the data according to the primary key
12. How to troubleshoot slow queries
Slow query is usually lack of index, unreasonable index or business code implementation leads to
slow_query_log_fileOpen and query slow query log
explaintroubleshooting index problem
3. Adjust data to modify index; business code layer restricts unreasonable access
13. Review of this chapter
The principle of the index is the key point
1. The principle of the index
3. The difference between different indexes