Original author, public number [programmer reading], welcome to pay attention to the public number, reprint the article please indicate the source oh.

Knowledge related to the database index, really, really is very complex, originally want to have a look at this thing, and then write an article detailed talk, later found out that the index of knowledge is too deep, it’s really difficult to talk about comprehensive and detailed, so finally learn and think of themselves into the following question one by one, hope can help to you!

knowledge

Question 1: What is a database index?

Database index is an important concept in database system, the index also called key, is a kind of data structure for database query efficiency, we can interpret the index into a book catalog, through the catalogue we can quickly find the content of the corresponding chapter, in the same way, through the database index, we can quickly find the corresponding record in the data table.

In short, an index is like creating a table of contents for your data.

Question 2: Why are indexes being used?

1. Using indexes greatly reduces the amount of data that the storage engine needs to scan. Without indexes, the table is scanned for each row of data, which would be very slow.

SQL > alter table ORDER BY, GROUP BY; SQL > alter table ORDER BY;

3. Indexes can convert random I/ OS into sequential I/ OS, avoiding high disk I/O costs and improving query efficiency.

Question 3: In which module is the MySQL index implemented?

MySQL indexes are implemented in the storage engine layer, so each storage engine has a different implementation method, and the processing method of the same index is also different.

Q4: Why does the index set not work?

Indexes cannot be used if a LIKE statement starting with % is used for fuzzy matching, as in:

SELECT * FROM users WHERE name LIKE '% zhang %';

SELECT * FROM users WHERE name LIKE Zhang '%';
Copy the code

However, indexes ending in % can be used, as in:

SELECT * FROM users WHERE name LIKE 'a %';
Copy the code

Select * from table where id = 1; select * from table where id = 1; select * from table where id = 1; select * from table where id = 1;

SELECT * FROM users id = 10 or name='test'
Copy the code

Question 5: What data structure does MySQL index use underneath?

In most cases, indexes in MySQL use B-tree as the underlying data structure. B-tree is just a general term. In fact, different storage engines use B-tree, there are different variations, for example, InnoDB uses B+Tree.

In addition, there are some special index structures, such as hash index, hash table is used at the bottom of the index. In MySQL, only Memory storage engine supports hash index.

Question 6: Under what circumstances is a data table not suitable for indexing?

1. Do not create indexes for tables that are used to store archived historical data and are rarely used for queries.

2. Indexes should not be built for tables with a small amount of data that will not grow much in the future, such as tables used to save configurations.

3. If the index is frequently modified and the modified performance is much higher than the query performance, do not create indexes.

Question 7: What is a callback table?

Back tables are for the Innodb storage engine. In the Innodb storage engine, the leaf node of the primary key index stores the recorded data, while the leaf node of the normal index stores the primary key index location.

When we query by primary key, we only need to search the search tree of the primary key index, and can get the recorded data directly.

When we search through a common index, we need to use the primary key to search the primary key search tree after we get the address of the primary key by searching the search tree of the common index. This process is called back table.

Q8: What is the difference between a clustered index and a non-clustered index?

Clustered index: The order of clustered index is the physical storage order of data, and the index and data together, through the index can directly obtain data, a data table only one clustered index.

Non-clustered index: the index order has nothing to do with the physical order of data. Index files and data are stored separately.

Question 9: What is the difference between a primary key index and a unique index in MySQL?

Fields that are set to primary key indexes cannot be NULL, and only one primary key index can exist in a table.

A field set as a unique index whose field value is not allowed to be significant.

Normal indexes can contain duplicate values or they can be NULL.

Q10: Indexes can improve query performance. Is it better to create more indexes?

As a table directory, the index itself consumes a lot of disk and memory storage space.

In addition, when writing data to the table, the index needs to be updated each time, so the more indexes, the slower the write.

Especially bad indexes, the more you build them, the worse the performance of your database.

11. What is the difference between MyISAM and InnoDB in handling indexes?

MyISAM storage engine is non-clustered index, index and data are stored separately, index files record Pointers to data

InnoDB storage engine is a clustered index, that is, index and data are placed together. InnoDB usually puts primary key and data together. If there is no unique key, InnoDB automatically creates a rowid as the primary key. Other secondary index leaf Pointers store the primary key location.

12. What is the left-most prefix principle for indexes?

MySQL database can not only create an index for a single column, but also create a joint index for multiple columns, such as:

CREATE TABLE test(
    a INT NOT NOT,
    b INT NOT NOT,
    KEY(a,b)
);
Copy the code

When we use the following query statement, the data can be queried quickly because the condition in the WHERE statement is the union index.

SELECT * FROM test WHERE a=1 AND b=1; 
Copy the code

Similarly, the following statement will use the union index created above. This is because MySQL sorts the index in the order in which it was created, and then checks whether the query criteria meet the index based on the query criteria, starting from the leftmost index.

SELECT * FROM test WHERE a=1; 
Copy the code

If field B is used in the query, the index condition is met. Because field A is matched from the left, MySQL determines that the index condition is not met.

SELECT * FROM test WHERE b=1; 
Copy the code

The above example gives a good idea of the left-most prefix principle for indexes and also illustrates the importance of index order.

13. What is an overwrite index?

An overwrite index is an index that contains the fields required by the query and does not need to be queried back to the table.

For example, in the following query, the field ID is the primary key index, so the value of the index can be returned directly, significantly improving the performance of the query.

SELECT id FROM users WHERE id BETWEEN 10 AND 20;
Copy the code

summary

Of course, the list above is only a small part of the index, if there is any wrong answer, please point out.

If you think the article is good, welcome to scan code attention, your attention is my biggest motivation to write