13 MySQL index knowledge points you need to master

This article introduces 13 knowledge points about MySQL indexes. I believe it will be very helpful for your interviews. Now I will share it with you.

To be honest, the knowledge related to database index is really complicated. I originally wanted to take a good look at this aspect and then write an article to talk about it in detail. Later, I found that the knowledge of indexing is too difficult and too deep. It is really difficult to talk about it comprehensively and in detail, so in the end I turned what I learned and thought into the following questions. I hope it can help everyone!

Knowledge Points

Question 1: What is a database index?

Database index is an important concept in the database system. The index is also called key. It is a data structure used to improve the efficiency of database query. We can understand the index as a book. Table of contents, through which we can quickly find the content of the corresponding chapter. Similarly, through the database index, we can quickly find the corresponding record in the data table.

In short, the index is like creating a directory for the data table.

Question 2: Why are you using indexes?

1. Using indexes greatly reduces the amount of data that the storage engine needs to scan. If indexes are not used, the data table must be scanned for each row of data, which will be very slow.

2. Since the index has been sorted, the results can be obtained quickly when performing ORDER BY and GROUP BY operations on the data table.

3. The index can convert random I/O into sequential I/O to avoid high disk IO costs and improve Query efficiency.

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

(Free learning video tutorial recommendation: mysql video tutorial)

MySQL index is implemented at the storage engine layer, so each A storage engine has different implementation methods, and the same index is processed in different ways.

Question 4: Why does it not work after setting the index?

If you use a LIKE statement starting with % for fuzzy matching, the index cannot be used, such as:

SELECT * FROM users WHERE name LIKE '%小张%';

SELECT * FROM users WHERE name LIKE '%小张';

but starts with % If ends with an index, you can use an index, such as:

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

OR There is no index used before and after the statement, such as the following statement, field id has an index, and Field name If no index is created, the following statement can only scan the entire table and cannot use the index:

SELECT * FROM users id = 10 or name='test'复制代码

Question 5: What data structure is used at the bottom of the 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 have different variants when using B-Tree. For example, InnoDB uses B Tree.

There are also some special index structures, such as hash indexes. The bottom layer of hash indexes uses hash tables. In MySQL, only Memory is stored The engine supports hash indexes.

Question 6: Under what circumstances is it not suitable to create an index on a data table?

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 data that will not grow much in the future, should not build indexes, such as data tables used to save configurations.

3. When modifications are frequent and the modification performance is much greater than the query performance, no more indexes should be created.

Question 7: What is a return form?

Table return is for the Innodb storage engine. In the InnoDB storage engine, the leaf nodes of the primary key index store the recorded data, while the leaf nodes of the ordinary index store the primary key index. location.

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

When we query through an ordinary index, after we obtain the address of the primary key by searching the search tree of the ordinary index, we also use the primary key to search the primary key search tree. This process is called table return.

Question 8: What is the difference between clustered index and non-clustered index?

Clustered index: The order of the clustered index is the physical storage order of the data, and the index and the data are placed together. 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 arrangement order of the data. The index files and data are stored separately.

Question 9: What are the differences between MySQL primary key index, unique index and ordinary index?

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

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

普通索引可以包含重复的值，也可以为 NULL 。

问题10：索引可以提高查询性能，那是不是索引创建越多越好？

索引作为一个数据表的目录，本身的存储就需要消耗很多的磁盘和内存存储空间。

并助在写入数据表数据时，每次都需要更新索引，所以索引越多，写入就越慢。

尤其是糟糕的索引，建得越多对数据库的性能影响越大。

问题11：MyISAM与InnoDB在处理索引上有什么不同？

MyISAM 存储引擎是非聚族索引，索引与数据是分开存储的，索引文件中记录了数据的指针

而 InnoDB 存储引擎是聚族索引，即索引跟数据是放在一块的， InnoDB 一般将主键与数据放在一块，如果没有主键，则将 unique key 作为主键，如果没有 unique key ，则自动创建一个 rowid 作为主键，其他二级索引叶子指针存储的是主键的位置。

问题12：什么是索引的最左前缀原则？

MySQL 数据库不单可以为单个数据列创建索引，也可以为多个数据列创建一个联合索引，比如：

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

 当我们使用下面的查询语句时，由于 WHERE 语句中查询的条件就是联合索引，所以可以很快查询到数据。

SELECT * FROM test WHERE a=1 AND b=1;

 同样，下面的语句也会利用上面创建的联合索引，这是因为 MySQL 会按照索引创建的顺序进行排序，然后根据查询条件从索引最左边开始检测查询条件是否满足该索引，由于字段 a 在最左边，所以满足索引。

SELECT * FROM test WHERE a=1;

而使用 字段b 进行查询时，则为满足，因为从最左边匹配到的是 字段a ，所以 MySQL 判断为不满足索引条件。

SELECT * FROM test WHERE b=1;

从上面例子可以很好地了解索引的最左前缀原则，同时也说明了索引顺序的重要性。

问题13：什么是覆盖索引？

如果一个索引中包含查询所要的字段时，此时不需要再回表查询，我们就称该索引为覆盖索引。

比如下面的查询中，字段id是主键索引，所以可以直接返回索引的值，显著提升了查询的性能。

SELECT id FROM users WHERE id BETWEEN 10 AND 20;

小结

当然，上面列出的只是索引的一小部分知识点，有什么回答不对的地方，欢迎指出。

The above is the detailed content of 13 MySQL index knowledge points you need to master. For more information, please follow other related articles on the PHP Chinese website!