Course 3 of massive database design: Index (note)

1. The index is defined in relational databases. An index is a table-related database structure that enables faster execution of SQL statements corresponding to a table. The index function is equivalent to the book directory. You can quickly find the desired content based on the page number in the directory. When a table contains a large number of records, to query the table, the first method of searching information is full table search, which is

1. The index is defined in relational databases. An index is a table-related database structure that enables faster execution of SQL statements corresponding to a table. The index function is equivalent to the book directory. You can quickly find the desired content based on the page number in the directory. When a table contains a large number of records, to query the table, the first method of searching information is full table search, which is

I. Index Definition

In relational databases, an index is a table-related database structure that enables faster execution of SQL statements corresponding to tables. The index function is equivalent to the book directory. You can quickly find the desired content based on the page number in the directory. When a table contains a large number of records, to query the table, the first method of searching information is full table search. All records are retrieved one by one and compared with the query conditions, then, return records that meet the conditions. This will consume a lot of database system time and cause a lot of disk I/O operations. The second is to create an index in the table, then, find the index value that meets the query conditions in the index, and quickly find the corresponding records in the table by the ROWID (equivalent to the page number) saved in the index.

An index is a separate, physical database structure. It is a set of one or more column values in a table and a logical pointer list pointing to the data page that physically identifies these values in the table.

The Index provides pointers to the data values stored in the specified column of the table, and then sorts these pointers according to the sort order you specify. The database uses an index in a similar way as you use an index in a book: it searches for an index to find a specific value, and then returns the pointer to the row containing the value.

In the database graph, you can create, edit, or delete each index type on the index/Key Attribute page of the selected table. When you save the table to which the index is attached or the relational graph of the table is saved, the index is saved in the database.

Ii. Index classification

In oracle databases, indexes can be roughly divided into three types based on the index mechanism:

1. B-tree indexes

2. Bitmap Index

3. Full-text index

In mysql databases, the indexing mechanism can be roughly divided

1. R-tree Index

2. B-tree indexes

3. hash Index

In the SQL Server database, the indexing mechanism can be roughly divided:

1. Clustered Index

2. Non-clustered Index

1) Non-clustered index on the stack

2) Non-clustered indexes on Clustered Indexes

Iii. Use Cases of various Oracle Indexes

1. B-tree indexes

-OLAP system

-Primary Key or uniqueness Constraint

-Fields with low key-value repetition rate

2. Bitmap Index

-OLAP system

-Fields with high key-value repetition rate

-Specific types of data

-It is not suitable for frequent DML table creation.

3. Full-text index

-Full-text indexes are suitable for text storage in fields and are often used for text search.

Iv. Summary

For a massive database, no index is fatal, but the same is fatal if you choose the wrong index.

Next we will compare the advantages and disadvantages of each index:

1. Full-text index:

Advantage: indexes are not stored by key values, but are saved by word segmentation and fuzzy queries are faster.

Disadvantage: it occupies a large amount of space and has many bugs and is difficult to maintain.

2. Comparison between Bitmap indexes and B-tree indexes:

Bitmap index saves more space

It is helpful for the operation of count, and, or, and other key values with high repetition rate.

Through the above, we have briefly learned the applicable scenarios and advantages of each index. So how should we choose indexes? We can refer to the following tips to help us select an appropriate index:

1. If the repetition rate of field data is not high, use B-tree indexes.

2. If the automatic disconnection data has a high repetition rate and a specific query method (for example, there are or and other logical operations between columns), you should consider using Bitmap indexes.

3. If you need to perform fuzzy query or language query on the fields in the column, you should consider using full-text indexes.