Key, index, constraints and their differences

This article is reposted from Baidu Library-primary key, index, and other differences

This afternoon, it's just fine. I want to straighten out some basic concepts, archive them, and save trouble.

I. Index

1. What is an index?

An index is a structure that sorts the values of one or more columns in a database table.

In relational databases, indexes are table-related database structures and exist in fact. It allows you to perform select operations on tables more quickly, which is equivalent to a directory of a book.

For a table, if we want to find a record that matches a specific value in a column, the first method is to search and match the entire table, and then list all matching records, however, 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 and then find the index value that meets the query conditions in the index, finally, you can 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. Therefore, indexes consume database space.

Not all databases use indexes in the same way. As a general rule, an index must be created on a table only when data in the index column is frequently queried. Indexes occupy disk space and speed up adding, deleting, and updating rows. In most cases, the speed advantage of indexing for data retrieval greatly exceeds its shortcomings. However, if the application updates data frequently or the disk space is limited, you may need to limit the number of indexes.

You can use a single column as an index, or use multiple columns as an index.

1. Advantages and disadvantages of Indexes

Advantages:

(1) greatly speeding up data retrieval;

(2) create a unique index to ensure the uniqueness of each row of data in the database table;

(3) Accelerate the connection between tables;

(4) When using grouping and sorting clauses for data retrieval, You can significantly reduce the time for grouping and sorting in queries.

Disadvantages:

(1) The index must occupy physical space.

(2) When adding, deleting, and modifying data in a table, indexes must also be dynamically maintained, reducing the Data Maintenance speed.

Notes for creating an index:

(1) consider the number of indexes already created on the table. It is best to avoid having many indexes on a single table.

(2) Check the definitions of indexes created on the table. It is best to avoid overlapping indexes containing shared Columns

(3) Check the number of unique data values in a column and compare the quantity with the number of rows in the table. For example, if there are million records, a certain field is gender, only male and female. That is to say, half of the records are repeated, so we need to consider whether it is necessary to establish an index.

1. Some Index categories

(1) Common Index

That is, the index without any restrictions. You can use the following statements.

Create Table zjj_temp_1
(ID number (10 ),
First_name char (10 ),
Last_name char (10 ),
Age number (3 ),
Val number (10, 2)
);

Insert into zjj_temp_1 values (1, 'junjie', 'zhang );

Select * From zjj_temp_1;

 

At this time, a record has been inserted.

Create index zjj_temp_index_1 on zjj_temp_1 (first_name );

-------- Create an index

Insert into zjj_temp_1 values (1, 'junjie', 'zhang)

-------- Insert an identical record again

Select * From zjj_temp_1;

 

OK! Two records appear, that is, the index is used to make it faster to search for records whose first_name is a specific value.

(2) unique index

An index. Duplicate indexes or key values are not allowed because rows with the same index value are not allowed. The system checks whether duplicate key values exist when creating the index, and performs a check every time the insert or update statement is used to add data.

Example of continuing analysis:

Drop index zjj_temp_index_1; ---- Delete the Common Index created above.

Create unique index zjj_temp_1 on zjj_temp_1 (ID );

---- Create a unique index

 

Database Error?

Yes. Find duplicate keywords.

We can see from the above that there are two records in the zjj_temp_1 table, with IDs 1.

The unique index is not allowed, so it cannot be created.

Delete from zjj_temp_1 where rownum <2; -- delete a row

Create unique index zjj_temp_1 on zjj_temp_1 (ID );

---- Continue to create and find that this operation is successful.

Insert into zjj_temp_1 values (2, 'junjie', 'zhang );

---- Successful

Insert into zjj_temp_1 values (1, 'kesi ', 'Ma',); failed !!!

Yeah! That's it!

(3) Primary Key Index

A database table often has a column or a combination of columns. Its Values uniquely identify each row in the table. This column is called the primary key of the table. It is similar to a unique index in that duplicate records are not allowed. The difference is that a unique index does not limit null, that is, more than one null value can be inserted, however, the primary key cannot be empty.

Continue to execute the statement:

Insert into zjj_temp_1 values (null, 'kesi ', 'Ma',); --- success

Select * From zjj_temp_1;

 

This indicates that the unique index allows null values.

Drop index zjj_temp_1; --- delete a unique index

Alter table zjj_temp_1 add constraint zjsy_1 primary key (ID); --- create a primary key

 

We can find that a record with an ID is empty, so the primary key cannot be created.

Delete the empty record.

(4) clustered index and non-clustered Index

A clustered index is also called a clustered index. It sorts the actual data on a disk by the values of one or more specified columns. Because the index page pointer of the clustered index points to the data page, using the clustered index to search for data is almost always faster than using a non-clustered index. Each table can only create a clustered index, and creating a clustered index requires at least 120% additional space for the table to store copies of the table and the intermediate index page.

 

Clustering is to locate the physical storage location of data based on the code value, so as to quickly retrieve data. The order of Oracle Cluster Index is the physical storage order of data, and the leaf node is the data node. The order of non-clustered Indexes has nothing to do with the physical order of data. The leaf node is still an index node, but there is a pointer pointing to the corresponding data block. Because clustering indexes are sorted by indexes, a table can have only one clustering index, but multiple columns can be used.

The clustering table in oracle means that two tables have the same fields and are usually connected to the two tables based on this field in the business. In this case, a clustering table is created,
The two tables share one field, which can reduce the occupied space and significantly improve the connection query speed.

These two articles have actual examples, so we will not discuss them further here.

Idea of building clustered Index

1. Most tables should have clustered indexes or use partitions to reduce competition on the last page of the table. In a highly transactional environment, blocking the last page seriously affects the system throughput.

2. In the clustered index, data is physically arranged on the data page in order, and duplicate values are also arranged together, therefore, when the queries that contain range checks (between, <, <=, <>,> =) or use group by or orderby, once a row with the first key value in the range is found, the row with the subsequent index value is physically contiguous without further searching, avoiding large-scale scanning, this greatly improves the query speed.

3. When you create a clustered index on a table with frequent insert operations, do not create a column with a monotonous appreciation (such as identity). Otherwise, blocking conflicts may often occur.

4. Do not include frequently modified columns in the clustered index, because after the code value is modified, the data row must be moved to a new location.

5. Select the cluster index based on the where clause and connection operation type.

For details about clustering indexes, refer to the following articles:

Http://blog.sina.com.cn/s/blog_607b68cc0100f5jo.html

Http://space.itpub.net/9778796/viewspace-660186

 

Ii. Keys

• What is a key?

A key in a database, also known as a keyword, is an important concept in a relational model. It is a logical structure rather than a physical part of a database.

• Unique key

Unique key, that is, one or more columns, with no Repeated Records. A record can be uniquely identified.

• Primary Key

It is a special unique key. The difference is that the primary key cannot be empty.

• Foreign key

If a public keyword is a primary keyword in a link, this public keyword is called a foreign key of another link. The foreign key represents the relationship between two links. A table with a foreign key as the primary keyword is called a master table, and a table with another key is called a slave table of the master table. A foreign key is also called a foreign key. In other words, if an attribute set in relational mode R is not the primary key of R, but the primary key of another relational R1, the attribute set is the foreign key of relational mode R, it is generally abbreviated as FK in database design.

Foreign keys are rarely used in development. They are mainly used by databases to ensure data integrity.

A simple example

Table A has column C1

Table B contains column C2

Direct the foreign key of C2 to C1

When you insert data to table B, the content of C2 must be one

You need to set several constraints.

For example, how to process associated data in Table B When deleting or modifying fields in Table

Example:

Create Table z_laopo (ID number (5) primary key, name char (20), age Number (3), zhiye char (20); ---- create a wife table

Create Table z_nanren

(ID number (5) primary key,

Name char (20 ),

Age number (3 ),

Laopo_id number (5 ),

Foreign key (laopo_id) References z_laopo (ID)

) ----- Create a man table and set laopo_id to the foreign key of the wife table

Insert into z_laopo values (1, 'fengjie', 18, 'accontant ');

Insert into z_laopo values (2, 'cangjingkong ', 25, 'teacher ');

---- Insert records into the wife table

Insert into z_nanren values (1, 'nanren _ 1', 24, 1)

Insert into z_nanren values (2, 'nanren _ 2', 24, 2)

Insert into z_nanren values (3, 'nanren _ 3', 24, 3)

---- Insert 3 records into the man table

The first two are okay. The third is an error:

 

Because there is no wife whose ID is 3, that is, laopo_id can only be selected between 1 and 2. Unless the z_laopo table has the ID 3.

• Parent key

For two tables with a foreign key relationship, the table referenced by a table with a foreign key is the master table, and the table with a foreign key is the slave table. In the preceding example, z_nanren is the slave table, z_laopo is the primary table. Lao_id is the foreign key, and the ID of the z_laopo table is the parent key.

Iii. Constraints

Such as primary key, unique, and so on are actually a constraint.

Let's take a look at the statements.

Create Table z_test1 (a char (10); --- create a table

Alter table z_test1 add constraint pk_z_test1 primary key ();

---- Add a primary key

Create Table z_test2 (a char (10); --- create a table

Alter table z_test2 add constraint uq_z_test1 unique (a); ---- Add a unique constraint

The primary key constraint is stricter than the unique constraint and cannot be blank.

Iv. Differences between keys, indexes, and constraints

Generally, we can see that the terms "Index" and "key" are used for exchange, but they are actually different. An index is a physical structure stored in a database. A key is purely a logical concept. The key represents the integrity constraints created to implement business rules. The confusion between indexes and keys is generally caused by the use of indexes in the database to implement integrity constraints.

 

(1) What is the relationship between the primary key index and the primary key?

A primary key index is an index automatically created when a primary key is created. The primary key must be unique and not empty. But how does one determine the efficiency? Of course, the index is built, and the whole table traversal is still not crazy.

Therefore, the primary key index is automatically created when a primary key is created.

(2) The difference between a primary key and a unique key is that the unique key can be empty and the primary key cannot.

(3) What is the difference between creating a unique constraint and a unique index?

Similarly, when a unique constraint is created, a unique index is automatically created. Creating a unique index is a means of unique constraints.

Basically, there is no difference in implementation. If you can't understand it, you should be the same.

(4) What is the difference between clustered index and non-clustered index?

I have already explained and discussed this in detail. I have attached two articles, so I won't talk about it here.

(5) What are the differences between constraints and primary keys?

Constraints generally include primary key constraints, foreign key constraints, and unique constraints.

They are respectively primary key, foreign key, and unique. The primary key constraint is only one type of constraint.

They are actually different concepts.

OK, just sort it out!

 

Key, index, constraints and their differences