Database index: Index Introduction and usage principles

I. Data pages and index pages

Table storage for a database is divided into data page stores and index page stores, which are pointers to data pages stored in index pages . index pages occupy a much smaller amount of storage space than data pages.

Clustered index and non-clustered index

Indexes are stored in two ways: Clustered index (Clustered) and nonclustered index (nonclustered index).

Clustered Index : It is not a separate index type, but a way of storing data, which specifies the order in which the data is physically stored in the table. Because a single table can have only one physical record sort on disk, a table can have only one clustered index. When you specify an indexed column, the data is sorted by the index column one by one (not specified by the primary key) and stored as a copy of the table. This copy (data page) and index page storage require an additional amount of space, at least 120% of the table. In addition, when inserting new rows, updating the index column values of rows, the DBMS will automatically reorder the data, and often do not use a clustered index when inserting rows or updating the values of indexed columns.

Nonclustered Indexes : The order of non-clustered indexes does not affect the physical storage order of the data. If the clustered index is a A-Z ordering of a dictionary (physical storage), then the nonclustered index is the various appendix indexes behind the dictionary. The sorting of keywords in different appendix indexes is not the same, but it is possible to quickly navigate to the number of pages (physical locations) where the words are located based on the index. Its query speed is not clustered index fast, but in a certain program can improve query efficiency. A table can create up to 249 nonclustered indexes, and each nonclustered index requires the storage of index pages. Because it takes up a lot of space, the non-clustered index is not as good as the more.

Third, unique index

Unique index : A special index that does not allow duplicate index values. That is, the specified index column, the duplicate value cannot appear, a bit like the primary key. When the index is created, the DBMS checks for duplicate index values and fails to create an index if there is an error. After the index is created, it is checked every time that data is added using the INSERT or UPDATE statement.

Iv. Creation of indexes

The database creation index requires a unique name, indicating the index name and index column, and the index name cannot be duplicated with the table name.

Take MySQL's create index as an example

Clustered index creation statement:

Create clustered index [index_name]

On [table_name] ([Column1], [Column2], ...)

Non -clustered index creation statement:

CREATE INDEX [index_name]

On [table_name] ([Column1], [Column2], ...)

Clustered unique index creation statement:

Create clustered unique index [index_name]

On [table_name] ([Column1], [Column2], ...)

non - clustered unique index creation statement:

Create unique index [index_name]

On [table_name] ([Column1], [Column2], ...)

V. Rules for the use of indexes

1, small data tables do not need to create an index , because there is no egg use, this does not improve query efficiency.

2, the user queries the field data has many numeric values or many null , the creation index can improve the query efficiency.

3, the query returns the result row less than the total amount of 25%, index can significantly improve the query efficiency ; Conversely, the role of the index is not much.

4. The index column must be used frequently in the where , otherwise it will not function very much.

5. When the table data is initialized, the data is loaded before the index is created . Otherwise, each additional piece of data will have to be updated with a large overhead.

6, the index improves the retrieval speed, reduces the data update speed. when you have a large number of writes and updates to a table , it is recommended that you remove the index and then create an index to save time and improve efficiency.

7, the index will occupy the database space, the design database needs to consider its size .

8, the table and its index as far as possible to store on different disk , can improve the query speed. This involves the reading principle of hard disk data.

Database index: Index Introduction and usage principles