Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "the difference between mysql clustered index and nonclustered index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Summary:

1. Mysql's innodb table, that is, the index organizes the table, and all the data rows in the table are placed on the index, which stipulates that the data is stored in strict order, so regardless of the order of insertion, its physical position has nothing to do with the order of insertion.

2. Clustered index. The leaf node stores the whole row of data, and finds a row directly through the key value of the clustered index.

3. Clustered index, the physical storage order of the data is consistent with the index order, that is, as long as the index is adjacent, then the corresponding data must also be stored on disk adjacent.

4. Clustered index, data rows and adjacent key values are stored compactly together, because data rows cannot be stored in two different places at the same time, so a table can only have one clustered index.

5. In a nonclustered index, the leaf node stores the value of the field, finds the value of the corresponding clustered index field through the key value of the nonclustered index, and then finds a row of the table through the key value of the clustered index, like oracle finds the rowid through the key value, and then finds the row through rowid

6. Mysql's innodb table, its clustered index is equivalent to the whole table, and the whole table is also a clustered index. By default, data is clustered through the primary key. If no primary key is defined, the first non-empty unique index is selected, and if there is no non-empty unique index, rowid is selected as the clustered index.

7. Mysql's innodb table, because the whole table is also a clustered index, the results from select are sorted sequentially, for example, the data insertion order of primary key fields can be 5, 3, 4, 2, 1, and the results obtained by query without order by are also sorted by 1, 2, 3, 4, 5.

8. Popular understanding

Clustered index: similar to the text content of Xinhua Dictionary, it is a kind of directory arranged according to certain rules.

Nonclustered index: this kind of catalog is purely a catalog, and the text is purely a way of sorting the text.

There can be only one clustered index per table because directories can only be sorted in one way.

9. Oracle generally uses heap tables, and mysql's innodb is an index to organize tables.

9.1. The heap table is managed in an obviously random way, and the storage location of the data is random whenever it is inserted, mainly determined by the availability of the blocks within the database, and the data will be placed in the most appropriate place, rather than in a particular order.

9.2, the storage speed of the heap table will be faster because there is no need to consider sorting. But to find records that meet certain criteria, you have to read all the records for filtering.

9.3. The rowid of the location of the record is recorded in the index of the heap table. When looking for it, find the index first, and then find the row data in the block according to the index rowid.

9.4. The index of the heap table and the table data are separated.

9.5. The index organizes the table, and its row data is stored in the form of an index, so to find the index is to find the row data.

9.6. Indexes organize tables, indexes and data are together.

What is the difference between a query based on a primary key index and a normal index?

Mysql > create table T (id int primary key,k int not null,name varchar (16), index (k)) engine=InnoDB

(ID,k) values are (100), (200), (300), (500) and (600) respectively.

The leaf node of the primary key index stores the entire row of data. In InnoDB, a primary key index is also called a clustered index (clustered index).

The leaf node content of a non-primary key index is the value of the primary key. In InnoDB, a non-primary key index is also called a secondary index (secondary index).

If the statement is select * from T where ID=500, that is, the primary key query mode, you only need to search the ID B+ tree.

If the statement is select * from T where query 5, that is, the normal index query method, you need to search the k index tree first to get a value of 500 for ID, and then search the ID index tree. This process is called returning to the table.

In order to maintain the order of the index, the B+ tree needs to do the necessary maintenance when inserting new values. Taking the above as an example, if you insert a new row with an ID value of 700, you only need to insert a new record after the record in R5. If the newly inserted ID value is 400, it is relatively troublesome and needs to logically move the following data to vacate the location.

Test1 table innodb engine, indexes and data are placed in one file

-rw-r- 1 mysql mysql 8678 Nov 20 14:05 test1.frm

-rw-r- 1 mysql mysql 98304 Nov 20 16:51 test1.ibd

Test2 table myisam engine, indexes and data are placed in different files

-rw-r- 1 mysql mysql 8558 Nov 22 10:22 test2.frm

-rw-r- 1 mysql mysql 0 Nov 22 10:22 test2.MYD

-rw-r- 1 mysql mysql 1024 Nov 22 10:22 test2.MYI

Https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_table

Table

The rows of an InnoDB table are organized into an index structure known as the clustered index, with entries sorted based on the primary key columns of the table. Data access is optimized for queries that filter and sort on the primary key columns, and each index contains a copy of the associated primary key columns for each entry. Modifying values for any of the primary key columns is an expensive operation. Thus an important aspect of InnoDB table design is choosing a primary key with columns that are used in the most important queries, and keeping the primary key short, with rarely changing values.

The rows of the InnoDB table are organized into an index structure called a clustered index, and entries are sorted according to the table's primary key column. Data access is optimized for queries that filter and sort primary key columns, and each index contains a copy of the associated primary key column for each entry. Modifying the value of any primary key column is an expensive operation. Therefore, an important aspect of InnoDB table design is to select a primary key that has the columns used in the most important queries and keeps the primary key short and rarely changes the value.

Https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_clustered_index

Clustered index

The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.In the Oracle Database product, this type of table is known as an index-organized table

The InnoDB term refers to a primary key index. The InnoDB table store is organized based on the values of the primary key columns to speed up queries and sorting involving the primary key columns. For best performance, carefully select the primary key column based on the query with the most critical performance. Because modifying the columns of a clustered index is an expensive operation, select primary columns that are rarely or never updated. In Oracle database products, such tables are called index organization tables.

Https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key.

When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record.

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

Each InnoDB table has a special index called a clustered index, which stores data for rows. Typically, a clustered index is synonymous with a primary key.

When PRIMARY KEY is defined on a table, InnoDB uses it as a clustered index. Define a primary key for each table you create. If there are no logically unique and non-empty columns or a set of columns, add a new auto-incremental column whose values are automatically populated.

If no PRIMARY KEY,MySQL is defined for the table, the first UNIQUE index is found, where all key columns are NOT NULL, and InnoDB uses it as a clustered index.

If the table does not have an PRIMARY KEY or an appropriate UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX inside the composite column that contains the row ID value. These rows are sorted by the ID that InnoDB assigns to rows in this type of table. The row ID is a 6-byte field that increases monotonously when a new row is inserted. Therefore, the rows sorted by the row ID are physically in the insertion order.

Accessing rows through a clustered index is fast because the index search points directly to the page that contains all the row data. If the table is large, the clustered index architecture typically holds disk I / O operations compared to storage organizations that store row data using pages that are different from index records.

All indexes except clustered indexes are called secondary indexes. In InnoDB, each record in the secondary index contains the primary key column of the row and the column specified for the secondary index. InnoDB uses this primary key value to search for rows in a clustered index.

That's all for the content of "the difference between mysql clustered index and nonclustered index". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.