Database indexing technology, database indexing

Database indexing technology, database indexing
Technical Principle index is a structure that sorts the values of one or more columns in a database table (for example, the name column of the employee table. If you want to search for him or her by the name of a specific employee, the index will help you get information faster than searching all rows in the table. For example, select * from table1 where id = 10000. If no index exists, you must traverse the entire table until the row with ID equal to 10000 is found. If an index exists, it must be an index created on the column with ID ), you can search in the index. Because the index is optimized by a certain algorithm, the number of queries is smaller. It can be seen that the index is used for locating. 2. The main types of database indexes are like directories in front of a book, which can accelerate the database query speed through the B-Tree Structure of SQL Server. Index classificationClustered IndexAndNon-clustered IndexTwo types: clustered indexes are stored in the order of physical locations, but not clustered indexes. Clustered indexes can speed up multi-row search, instead of clustered indexes, a single row is quickly searched. Based on the functions of the database, you can create three indexes in the Database Designer: unique index, primary key index, and clustered index. For more information about the index functions supported by the database, see the database documentation. Tip: although the unique index helps to locate information, we recommend that you use primary keys or unique constraints to obtain the best performance results.Unique IndexA unique index is an index that does not allow any two rows to have the same index value. When duplicate key values exist in existing data, most databases do not allow you to save the newly created unique index with the table. The database may also prevent adding new data that will create duplicate key values in the table. For example, if the employee's last name (lname) in the employee table creates a unique index, neither employee can have the same name.Primary Key IndexA database table often has one or more columns. Its Values uniquely identify each row in the table. This column is called the primary key of the table. When you define a primary key for a table in the database relationship diagram, the primary key index is automatically created. The primary key index is a specific type of unique index. This index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows quick access to data.Clustered IndexIn the clustered index, the physical order of the row in the table is the same as the logic (INDEX) Order of the key value. A table can contain only one clustered index. If an index is not a clustered index, the physical sequence of the row in the table does not match the logical sequence of the key value. Compared with non-clustered indexes, clustered indexes generally provide faster data access speeds.Index ColumnYou can create an index based on a single or multiple column in a database table. Multiple-column indexes can differentiate rows in which one column may have the same value. If you often search for two or more columns at the same time or sort by two or more columns, the index is also helpful. For example, if you often set a criterion for the first and second columns in the same query, it makes sense to create multiple columns of indexes in these two columns. Determine the validity of the index: Check the WHERE and JOIN clauses of the query. Each column in any clause is an object that can be selected by the index. Test the new index to check its impact on running query performance. Consider the number of indexes created on the table. It is best to avoid having many indexes on a single table. Check the definitions of indexes created on the table. It is best to avoid overlapping indexes that contain shared columns. Check the number of unique data values in a column and compare the quantity with the number of rows in the table. The comparison result is the selectivity of the column, which helps to determine whether the column is suitable for creating an index. If so, determine the index type. 3 basic features index creation aims to accelerate the record in the tableSearchOrSort.The cost for setting indexes for a table is: first, the storage space of the database is increased, second, it takes a lot of time to insert and modify data (because the index also needs to change ). Database indexes are directories created for values in certain fields to improve the search efficiency of tables. Creating indexes can greatly improve the system performance. First, you can create a unique index to ensure the uniqueness of each row of data in the database table. Second, it can greatly speed up data retrieval, which is also the main reason for creating an index. Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity. Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries. Fifth, by using indexes, you can use the optimizer during the query process to improve system performance. This is because adding Indexes has many disadvantages. First, it takes time to create and maintain indexes. This time increases with the increase of data volume. Second, 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. Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.