Database index Principle

Database index Principle
When you encounter a new knowledge, you can learn it through the what-why-how method. Below we will learn the database index through this method.What is database index?
Definition 1: an index is a structure used to sort the values of one or more columns in a database table. You can use an index to quickly access specific information in a database table. Definition 2: an index is a disk structure associated with a table or view, which can accelerate row retrieval from a table or view. An index contains keys generated by one or more columns in a table or view. These keys are stored in a structure (B tree.
Why use database indexes?Advantages: 1. By creating a unique index, You can ensure the uniqueness of each row of data in the database table.
2. The data retrieval speed can be greatly accelerated, which is also the main reason for creating an index.
3. It can accelerate the connection between tables, especially for Data Reference integrity. 4. When you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.
5. By using indexes, you can use the optimizer during the query process to improve system performance.
Of course, indexes also have disadvantages: 1. It takes time to create and maintain indexes. This time increases with the increase of data volume.
2. In addition to the data space occupied by data tables, each index also occupies a certain amount of physical space. To create a clustered index, the required space will be larger.
3. When adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed.
How to Use Database indexes?Take mysql as an example.Common Index:

Method 1:

Create index name ON table name (field name );

Method 2:

Create table Name (field data type, field data type, INDEX [indexName] (field name ));

Delete An index:

Drop index [INDEX name] ON table name;

Unique index:It is similar to the previous normal index. The difference is that the values in the index column of the MySQL database must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
Method 1:

Create unique index name ON table name (field name );

Method 2:

Create table Name (field data type, field data type, unique index [indexName] (field name ));

Primary Key Index:It is Special unique index, Null values are not allowed. It is generally used to create a primary key index when creating a table. For example:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );  

Combined index:An index contains multiple columns. Note:Indexes are created on certain columns in the database table. When creating an index, you should consider which columns can create an index and which Columns cannot create an index. In general, you should create an index on these columns: 1. You can speed up the search on columns that frequently need to be searched;2. In a column that acts as a primary key, force the uniqueness of the column and the data arrangement structure in the organization table; 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. The specified range is continuous because the index has been sorted;
5. Create an index on a column that often requires sorting. Because the index has been sorted, you can use the index sorting to speed up the sorting query time. 6. Create an index on a column that is frequently used in the WHERE clauseTo speed up condition judgment. Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following features: 1. Indexes should not be created for columns that are rarely used or referenced in queries.. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of 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 data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed. 3. Indexes should not be added for columns defined as text, image, and bit data types.. This is because the data volume of these columns is either large or small, which is not conducive to the use of indexes. 4. When the modification performance is much higher than the retrieval performance, you should not create an index.This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when modification operations are far more than search operations, you should not create an index.

What is an index? What are the characteristics of index types?

An 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 a specific employee by name, the index will help you get the information faster than all rows in the table that must be searched.
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. The Index provides pointers to the data values stored in the specified column of the table, and then sorts these pointers according to the sort order you specify. The database uses an index in a similar way as you use an index in a book: it searches for an index to find a specific value, and then returns the pointer to the row containing the value. In the database graph, you can create, edit, or delete each index type on the index/Key Attribute page of the selected table. When you save the table to which the index is attached or the relational graph of the table is saved, the index is saved in the database.
You can create an index based on a single or multiple column in a database table. Multiple-column indexes enable you to differentiate rows with the same value in one of the columns. 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.

Advantages of index creation:
1. Greatly speed 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 you use grouping and sorting clauses to retrieve data, you can significantly reduce the time for grouping and sorting in queries.

Index type:
Based on the functions of the database, four indexes can be created in the Database Designer: unique index, non-unique index, primary key index, and clustered index. 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 index:
A 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.

Non-unique index:
A non-unique index is a relatively unique index that allows any two rows to have the same index value. When duplicate key values exist in existing data, the database allows you to save the newly created index with the table. In this case, the database cannot prevent adding new data that will create duplicate key values in the table.

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. 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 index (also called clustered index ):
In 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 .... Remaining full text>

What is an index? What are the characteristics of index types?

An 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 a specific employee by name, the index will help you get the information faster than all rows in the table that must be searched.
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. The Index provides pointers to the data values stored in the specified column of the table, and then sorts these pointers according to the sort order you specify. The database uses an index in a similar way as you use an index in a book: it searches for an index to find a specific value, and then returns the pointer to the row containing the value. In the database graph, you can create, edit, or delete each index type on the index/Key Attribute page of the selected table. When you save the table to which the index is attached or the relational graph of the table is saved, the index is saved in the database.
You can create an index based on a single or multiple column in a database table. Multiple-column indexes enable you to differentiate rows with the same value in one of the columns. 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.

Advantages of index creation:
1. Greatly speed 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 you use grouping and sorting clauses to retrieve data, you can significantly reduce the time for grouping and sorting in queries.

Index type:
Based on the functions of the database, four indexes can be created in the Database Designer: unique index, non-unique index, primary key index, and clustered index. 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 index:
A 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.

Non-unique index:
A non-unique index is a relatively unique index that allows any two rows to have the same index value. When duplicate key values exist in existing data, the database allows you to save the newly created index with the table. In this case, the database cannot prevent adding new data that will create duplicate key values in the table.

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. 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 index (also called clustered index ):
In 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 .... Remaining full text>