Indexes, keys, and constraints in databases, and database index Constraints

Indexes, keys, and constraints in databases, and database index Constraints

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.
2. 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.

3. Some Index categories
(1) Common Index
That is, the index without any restrictions.
(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.
(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.
(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
1. 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.
2. Unique key
Unique key, that is, one or more columns, with no Repeated Records. A record can be uniquely identified.
3. Primary Key
It is a special unique key. The difference is that the primary key cannot be empty.
4. 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
5. Parent key
For two tables with foreign key relationships, the tables referenced by tables with foreign keys are called primary tables, while tables with foreign keys are called slave tables.

Iii. Constraints
Such as primary key, unique, and so on are actually a 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.

Address: http://blog.csdn.net/dlodj/article/details/7017331