Overview of database systems- index and database system overview 04
1. Index Overview
Index creation is an effective way to speed up the query. You can create one or more indexes on the basic table based on the needs of the application environment to provide multiple access paths and accelerate the search speed.
Generally, the database administrator DBA or table owner is responsible for creating and deleting indexes, the system automatically selects an appropriate index as the access path when accessing data. You do not have to select an index as needed.
2. Index advantages and disadvantages
First, you need to understand why index creation can greatly improve system performance.
- By creating a unique index, You can ensure the uniqueness of each row of data in the database table.
- This can greatly speed up data retrieval, which is also the main reason for creating an index.
- It can accelerate the connection between tables, especially for Data Reference integrity.
- When you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.
- By using indexes, you can use the optimizer during the query process to improve system performance.
There are so many advantages to adding indexes. Why not create an index for each column in the table? Although this idea is reasonable, it also has one-sidedness. Although indexes have many advantages, it is unwise to add indexes to every column in the table. This is because, adding indexes also has many disadvantages.
- It takes time to create and maintain indexes. This time increases with the increase of data volume.
- Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.
- When adding, deleting, and modifying data in a table, indexes must be maintained dynamically, which reduces the Data Maintenance speed.
- An index is created on certain columns in a database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index. Generally, you should create an index on these columns:
1. In columns that frequently need to be searched, the search speed can be accelerated. 2. In columns that serve as the primary key, the uniqueness of the column and the data arrangement structure in the organization table are enforced. 3. These columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection. 4. Create an index on a column that often needs to be searched by range. Because the index has been sorted, the specified range is continuous. 5. Create an index on the columns that frequently need to be sorted. Because the index has been sorted, you can use the index sorting to speed up the sorting query time. 6. Create an index on the columns in the where clause frequently to speed up condition judgment. Similarly, indexes should not be created for some columns. Generally, these columns that do not need to be created have the following features: 1. Indexes should not be created for those columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there are indexes or no indexes, the query speed cannot be improved. On the contrary, increasing indexes reduces the system maintenance speed and space requirements. 2. Indexes should not be added for columns with few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of the data rows to be searched in the table is large. However, the national index cannot significantly accelerate the retrieval speed. 3. Indexes should not be added for columns defined as text, Image, or bit data because the data volume of these columns is either large or small. 4. When the modification performance is far greater than the retrieval performance, indexes should not be created because the modification performance and retrieval performance are in conflict. When an index is added, the retrieval performance will be improved, however, the modification performance will be reduced. When the index is reduced, the modification performance will be improved and the retrieval performance will be reduced. Therefore, when the modification performance is far greater than the retrieval performance, the index should not be created.
3. Create an index
You can create an index by using the following statements: CREATE [UNIQUE] [CLUSTER] [INDEX <INDEX Name> ON <Table Name> (<column Name> [<sequence>] [, <column Name> [<order>] ......); note: <Table Name> is the name of the basic table for which an index is to be created. indexes can also be created on one or more columns of the table, separated by commas, you can also specify the order of index values after each <column Name>. Optional options are ASC (ascending) or DESC (descending). The default value is ASC. UNIQUE indicates that each index value of this index corresponds to a UNIQUE data record. CLUSTER indicates that the index to be created is a clustered index. The so-called clustered index refers to the index organization in which the order of index items is consistent with the physical order recorded in the table. Another method is to create an index. An index is also created when the primary key constraint or unique constraint is defined in the table. Both methods can create an index, however, the specific content of indexes is different: when the primary key or unique constraint is defined on the table, if the table already has a standard INDEX created using the create index statement, the INDEX created by the primary key constraint or uniqueness constraint overwrites the previously created Standard INDEX. That is to say, the INDEX created by the primary key constraint or unique constraint has a higher priority than the INDEX created by the create index statement. Eg: Create an index for Student, Course, and SC tables in the Student-Course database. The Student table creates a unique index by Student ID in ascending order, the Course table creates a unique index in ascending order of Course numbers, and the SC table creates a unique index in ascending order of student numbers and in descending order of Course numbers. Create unique index Stusno ON Student (Sno); create unique index Coucno ON Course (Cno); create unique index SCno on SC (Sno ASC, Cno DESC );
4. delete an index
Once an index is created, it is systematically used and maintained without user intervention. The index is created to reduce the query operation time. However, if data is frequently added, deleted, or changed, the system will spend a lot of time maintaining the index, thus reducing the query efficiency. In this case, you can delete unnecessary indexes. In SQL, the DROP INDEX statement is used to delete an INDEX. The general format is: DROP INDEX <INDEX Name>; eg: Delete the Stusname INDEX of the Student table DROP INDEX Stusname, the system also deletes the description of the index from the data dictionary.
In RDBMS, indexes are generally implemented using B + trees and HASH indexes. B + trees have the advantage of dynamic balance, and HASH indexes have the characteristics of fast search speed, indexes are internal implementation technologies of relational databases and fall into the scope of internal models. When you use the create index statement to define an INDEX, you can define that the INDEX is a unique INDEX, a non-unique INDEX, or a dinner INDEX. For an INDEX, the B + tree is used, or the HASH index is determined by the specific RDBMS.
Introduction to Database Systems
Data: The values obtained by scientific experiments, tests, and statistics for scientific research, technical design, verification, and decision-making.
Database: (Database) is a warehouse that organizes, stores, and manages data according to the data structure. It was born fifty years ago. With the development of information technology and the market, especially after 1990s, data management is no longer just about data storage and management, but also the various data management methods required by users. There are many types of databases. From the simplest storage of tables with various types of data to the large database systems capable of storing massive data, they have been widely used in various aspects.
Database System: (database systems) is a system composed of databases and management software. It is an ideal core organization for data processing to meet the needs of data processing. It is a software system that provides data for actual and operational storage, maintenance, and application systems. It is a collection of storage media, processing objects, and management systems.
Database management system (database management system) is a large-scale software used to manipulate and manage databases. It is used to establish, use, and maintain databases, or dbms for short. It manages and controls the database in a unified manner to ensure the security and integrity of the database. The user accesses the data in the database through the dbms, and the database administrator also maintains the database through the dbms. It allows multiple applications and users to create, modify, and query databases in different ways at the same time or at different times. DBMS provides Data Definition Language (DDL) and Data Manipulation Language (DML) for you to define the schema structure and permission constraints of the database, allows you to append or delete data.
Introduction to Database System
The first three or four chapters are about to be learned.
Chapter 1, concept-test Affirmation
Chapter 2 may be a relational model. This is mainly about figuring out the logical relationship, but I forgot some symbols.
In chapter 3, I remember it was an SQL statement, which is more important and also the focus of the exam. Of course, it must be used when the course is set.
In chapter 4, indexes and other things may not take too many tests, but they will be used in design.
Other chapters: the trigger or stored procedure may be briefly introduced. The test scores cannot be too many, but they are very useful in design.