Technical PrincipleAn index is a structure that sorts the values of one or more columns in a database table, such as the name (name) column of the employee table. If you want to find him or her by the name of a particular employee, the index helps you get information faster than searching all the rows in the table. For example such a query: SELECT * FROM table1 where id=10000. If there is no index, you must traverse the entire table until the row with the ID equals 10000 is found, and after the index (which must be an index established on the ID column), you can find it in the index. Because the index is optimized by some algorithm, the number of lookups is much less. It is visible that the index is used for positioning.2Main TypesA database index is like a directory in front of a book that canthe B-tree structure of SQL ServerSpeed up the database query. Index is divided into
Clustered IndexAnd
non-clustered indexThe clustered index is in the order of the physical location where the data is stored, but not the clustered index, which can improve the speed of multi-row retrieval, and the non-clustered index is very fast for the single-line retrieval. Depending on the capabilities of your database, you can create three indexes in the Database Designer: Unique indexes, primary key indexes, and clustered indexes. For more information about the index features supported by the database, see the database documentation. Tip: Although a unique index helps locate information, for best performance results, it is recommended that you use a primary KEY or a unique constraint instead.
Unique indexA unique index is one 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 a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.
PRIMARY KEY IndexDatabase tables often have one or more column combinations whose values uniquely identify each row in the table. This column is called the primary key of the table. Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The 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 the data.
Clustered IndexIn a clustered index, the physical order of rows in a table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index. If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes.
Indexed ColumnsYou can create indexes based on single or multiple columns in a database table. Multiple-column indexes can differentiate between rows in which one column might have the same value. Indexes are also helpful if you frequently search for two or more columns or sort by two or more columns at the same time. For example, if you frequently set criteria for a first and last name in the same query, it would make sense to create a multicolumn index on those two columns. Determine the validity of the index: check the WHERE and join clauses of the query. Each column included in either clause is an object that the index can select. Experiment with the new index to check its impact on running query performance. Consider the number of indexes that have been created on the table. It is best to avoid having many indexes on a single table. Checks the definition of an index that has been created on the table. It is best to avoid overlapping indexes that contain shared columns. Checks the number of unique data values in a column and compares the number to the number of rows in the table. The result of the comparison is the selectivity of the column, which helps to determine if the column is suitable for indexing and, if appropriate, the type of the index.3Basic FeaturesThe purpose of indexing is to speed up the records in the table
FindOr
sort. There is a cost to indexing a table: one is to increase the storage space for the database, and the other is to spend more time inserting and modifying the data (because the index changes as well). A database index is a directory in which values in some fields are established to improve the search efficiency of a table. Creating an index can greatly improve the performance of your system. First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table. Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes. Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data. Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries. By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying. Because there are many disadvantages to increasing the index. First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases. Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger. Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.
Database indexing Technology
