8-2 Mysql Index Optimization Frequent Exam Interview Questions

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 B-Tree?

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
3 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

What is B-Tree, why use B-Tree
1. Multi-balanced search tree (each node up to m(m >= 2)a child, called the m
2 leaf nodes have the same depth
data 3. nodes keyfrom left to right are increasing

Insert picture description here


B+B-TreeVariations of the tree
1. The Mysqlactual 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


Insert picture description here

Insert picture description here

Five, Mysqlthe type of index

MysqlCreate index type
1. Ordinary index ( CREATE 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 InnoDBfrom the 1.2.xbeginning Increase the full-text index, that is Mysql5.6, the version is only upgraded to1.2.x

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. WHEREcondition)
2. often used as a field-table joins
3. often appear in order by, group byafter the field

7. What should I pay attention to when creating an index?

Best practice
1. Non-null fields NOT NULL, 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 %begin with 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.

Insert picture description here

Insert picture description here

X. File storage methods of non-clustered and clustered index

CREATE TABLE myisam_table (
	title VARCHAR(80)

CREATE TABLE innodb_table (
	`url_md5` char(32)
	KEY `idx_url` (`url_md5`)
) ENGINE = InnoDB;

Eleven, clustered index and auxiliary index

Insert picture description here

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
1. slow_query_log_fileOpen and query slow query log
2. Through 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
2. B+TreeThe structure
3. The difference between different indexes