Shulou(Shulou.com)06/01 Report--

Today, what the editor shares with you is the summary of MySQL index knowledge points that must be understood. I believe many people do not know much about it. In order to let you know more about MySQL index knowledge points, we summarized the following contents and looked down together. I'm sure you'll get something.

To be honest, the knowledge related to database indexing is really complicated. I originally wanted to take a good look at it, and then I wrote an article to talk about it in detail. Later, I found that the knowledge of index was too difficult and deep, and it was really difficult to talk about it comprehensively and in detail. so in the end, I still put what I have learned and think of into the following problems, hoping to help you!

Knowledge point question 1: what is a database index?

Database index is an important concept in database system, index is also called key, is a kind of data structure used to improve the efficiency of database query, we can understand the index as a book catalog, through the catalog we can quickly find the corresponding chapter content, similarly, through the database index, we can quickly find the corresponding records in the data table.

All in all, an index is like creating a directory for a data table.

Question2: why are indexes being used?

1. The use of indexes greatly reduces the amount of data that the storage engine needs to scan, and if the index is not used, the data table is scanned for each row of data queried, which is very slow.

2. Because the indexes are already sorted, you can get results quickly when you perform operations such as ORDER BY and GROUP BY on the data table.

3. The index can change the random Imax O into the sequential Imax O, avoiding the high disk IO cost and improving the query efficiency.

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

The index of MySQL is implemented in the storage engine layer, so each storage engine has a different implementation, and the same index is handled differently.

Question 4: why is the index set but it doesn't work?

If you use a LIKE statement that starts with% for fuzzy matching, you cannot use an index, such as:

SELECT * FROM users WHERE name LIKE'% Xiao Zhang%'; SELECT * FROM users WHERE name LIKE'% Xiao Zhang'

However, you can use an index that ends with%, such as:

SELECT * FROM users WHERE name LIKE 'Zhang%'

Indexes are not used at the same time before and after the OR statement, such as the following statement. Field id has an index, but field name does not create an index, so the following statement can only scan the whole table and cannot use the index:

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

Question what data structure is used at the bottom of the 5:MySQL index?

In MySQL, in most cases, indexes 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 variants, such as InnoDB uses B+Tree.

There are also some special index structures, such as hash indexes, which use hash tables at the bottom. In MySQL, only the Memory storage engine supports hash indexes.

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

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

2. Data tables with a relatively small amount of data, and there will not be much data growth in the future, should not be indexed, such as the data table used to save the configuration.

3. When the modification is frequent and the modification performance is much greater than the query performance, the index should no longer be created.

Question 7: what is returning to the table?

The return table is for the Innodb storage engine, where the recorded data stored by the leaf node of the primary key index and the location of the primary key index stored by the leaf node of the normal index.

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

When we query through the ordinary index, after we get the address of the primary key by searching the search tree of the ordinary index, we have to use the primary key to search the primary key search tree, a process called back to the table.

Question 8: what is the difference between clustered indexes and non-clustered indexes?

Clustered index: the order of the clustered index is the physical storage order of the data, and the index and the data are put together, and the data can be obtained directly through the index. there is only one clustered index in a data table.

Non-clustered index: the index order has nothing to do with the physical order of the data, and the index file is stored separately from the data.

Question what is the difference between a 9:MySQL primary key index, a unique index and a normal index?

Fields set as primary key indexes are not allowed to be NULL, and a data table can have only one primary key index.

The field value of a field set as a unique index is not allowed to be important.

A normal index can contain duplicate values, or it can be NULL.

Question 10: indexes can improve query performance, so is it better to create as many indexes as possible?

Index as a table of data directory, its own storage needs to consume a lot of disk and memory storage space.

It also helps to update the index every time you write data to the data table, so the more indexes you have, the slower the write.

In particular, the more bad indexes are built, the greater the impact on the performance of the database.

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

The MyISAM storage engine is a non-clustered index, the index and data are stored separately, and the pointer of the data is recorded in the index file.

The InnoDB storage engine is clustered index, that is, the index and data are put together, InnoDB generally put the primary key and data together, if there is no primary key, then unique key as the primary key, if there is no unique key, then automatically create a rowid as the primary key, other secondary index leaf pointers store the location of the primary key.

Question 12: what is the leftmost prefix principle of an index?

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

CREATE TABLE test (an INT NOT NOT, b INT NOT NOT, KEY (arecom b))

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

SELECT * FROM test WHERE axi1 AND bliss 1

Similarly, the following statement takes advantage of the federated index created above, because MySQL sorts according to the order in which the index was created, and then checks whether the query condition satisfies the index from the far left of the index based on the query condition, which is satisfied because field an is on the far left.

SELECT * FROM test WHERE axi1

When querying with field b, it is satisfied, because field an is matched from the leftmost, so MySQL determines that the index condition is not met.

SELECT * FROM test WHERE bread1

The leftmost prefix principle of the index can be well understood from the above example, and it also illustrates the importance of the index order.

Question 13: what is an overlay index?

If an index contains the fields required by the query, and there is no need to return to the table query at this time, we call the index an override index.

For example, in the following query, the field id is the primary key index, so you can return the value of the index directly, which significantly improves the performance of the query.

Summary of SELECT id FROM users WHERE id BETWEEN 10 AND 20;

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

About must understand the MySQL index knowledge point summary to share here, hoped that the above content can have certain reference value to everybody, can apply what you have learned. If you like this article, you might as well share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.