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

Today, I will talk to you about the non-clustered index in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

The non-clustered indexes in mysql are:

Non-clustered index, a kind of index. Indexes are divided into clustered index and non-clustered index. The purpose of indexing is to speed up the lookup or sorting of records in a table. The index order has nothing to do with the physical order of the data.

There can be only one clustered index per table because records in a table can only be stored in one physical order. However, a table can have more than one non-clustered index. In fact, you can build up to 249 non-clustered indexes per table. Non-clustered indexes require a lot of hard disk space and memory. In addition, although a non-clustered index can increase the speed of fetching data from a table, it also slows down the speed of inserting and updating data into the table. Whenever you change the data in a table that has a non-clustered index, you must update the index at the same time. Therefore, you should carefully consider when building a non-clustered index on a table. If you expect a table to update data frequently, don't build too many non-clustered indexes on it. In addition, if the hard disk and memory space is limited, you should also limit the number of non-clustered indexes used.

An example

Next we create a student table and make three queries to show when it is a clustered index and when it is not.

Create table student (id bigint, no varchar (20), name varchar (20), address varchar (20), PRIMARY KEY (`branch_ id`) USING BTREE, UNIQUE KEY `idx_ no` (`no`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

First, get all the field data directly according to the primary key query, where the primary key is a clustered index, because the index leaf node corresponding to the primary key stores the values of all fields of id=1.

Select * from student where id = 1

Second, according to the number query number and name, the number itself is a unique index, but the column of the query contains the student number and student name. when the numbered index is hit, the data of the node of the index stores the primary key ID and needs to be queried again according to the primary key ID, so no is not a clustered index under this query.

Select no,name from student where no = 'test'

Third, we query the number according to the number (some people will ask if you know the number, you still need to query it? Yes, you may need to verify whether the number exists in the database). When this query hits the numbered index, it returns the number directly, because the data needed is the index and there is no need to query back to the table. In this scenario, no is a clustered index.

Select no from student where no = 'test'

Summary:

The primary key must be a clustered index, and there must be a primary key in the InnoDB of MySQL. Even if the developer does not set it manually, the unique index will be used. If there is no unique index, the id of a row in the database will be used as the primary key index. Other ordinary indexes need to distinguish between SQL scenarios. When the column of the SQL query is the index itself, this ordinary index can also be called a clustered index. There is no clustered index in the MyisAM engine.

After reading the above, do you have any further understanding of non-clustered indexes in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.