Optimize database indexes and database Indexes

Optimize database indexes and database Indexes

This article mainly describes how to create and use indexes. We will not go into details about why indexes are used, what benefits are brought about by indexes, and the classification of indexes, for more information, see related documents.

I. How to create indexes correctly

1. Index primary key and foreign key

In development, primary keys or foreign keys are often used to search for one or more records. Therefore, you need to index the primary key and foreign key.

2. Create an index for fields that frequently appear in the query Conditions

Indexing fields that frequently appear in query conditions can improve query efficiency.

3. Create an index based on the fields to be returned

You can create a composite index for fields returned by the query result without querying the data table. For example, select id, name, code from student where id =? If you create an index for id, name, and code, you can directly query the index table to obtain the desired data. In this case, you do not need to access the student table.

There are several common notes for creating indexes. The more indexes you create, the better. If you create an index improperly, the query results will be lower than if you do not create an index, or the data in the index table is larger than the data in the data table, so you need to be careful when using it.

A guide table for index creation is provided below:

	Field Type	Common Field Names
Fields to be indexed	Primary Key	ID, PK
	Foreign key	PRODUCT_ID, COMPANY_ID, MEMBER_ID, ORDER_ID, TRADE_ID, PAY_ID
	Fields with the object or identity meaning	HASH_CODE, USERNAME, IDCARD_NO, EMAIL, TEL_NO, IM_NO
Use fields with caution in indexing. Data Distribution and detailed evaluation of application scenarios are required.	Date	GMT_CREATE, GMT_MODIFIED
	Year and month	YEAR, MONTH
	Status flag	PRODUCT_STATUS, ORDER_STATUS, IS_DELETE, VIP_FLAG
	Type	ORDER_TYPE, IMAGE_TYPE, GENDER, CURRENCY_TYPE
	Region	COUNTRY, PROVINCE, CITY
	Operator	CREATOR, AUDITOR
	Value	LEVEL, AMOUNT, SCORE
	Long character	ADDRESS, COMPANY_NAME, SUMMARY, SUBJECT
Unsuitable index fields	Description remarks	DESCRIPTION, REMARK, MEMO, DETAIL
	Large Field	FILE_CONTENT, EMAIL_CONTENT

2. How to correctly use Indexes

1. Do not make the is null judgment on the field in the where clause. This will cause the engine to stop using the index but scan data in the entire table.

2. Avoid unequal pandan fields in the where clause (<> ,! =). Otherwise, full table scan is performed using the engine.

3. Avoid using or in the where clause, which also causes the engine to scan data in the entire table.

We recommend that you use union instead. The following example assumes that the code is an index.

Statement not recommended: select id from student where code = '20160901' or code = '20160901'

Recommended statement: select id from student where code = '000000' union all select id from student where code = '000000'

4. Avoid judging fields not in and not exists in the where clause, which also causes index failure.

5. Avoid expression operations (function algorithm calculation) on fields in the where clause, which also causes index failure.

Example: select id from student where DATEDIFF (SYSDATE, createDate)> 30

Select id from student where col/2 = 15

6. When using Like, do not start with a non-letter

For example, select id from student where name like '% Wang %' should use select id from student where name like 'wang %'

7. When using an index field as a condition, if the index is a composite index, you must use the first field in the index as the condition to ensure that the system uses the index, otherwise, the index will not be used, and the query field sequence should be consistent with the index sequence as much as possible. 

PS: the third item marked in red above is the standard executed by the database before January 1, year 56. Currently, databases generally support in or index queries.

Database index function

Why create an index? This is because creating an index 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.

Some may ask: why not create an index for each column in the table because increasing Indexes has so many advantages? Although such an idea has its own rationality, it also has its own 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. 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.

Indexes are created on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index. In general, you should create indexes on these columns. For example, you can speed up the search for columns that frequently need to be searched, force the uniqueness of the column and the data arrangement structure in the organization table. These columns are usually used in connected columns. These columns are mainly foreign keys, which can speed up the connection; 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. Create an index on a column that frequently needs to be sorted because the index has been sorted, in this way, the sorting of indexes can be used to speed up the sorting query time. indexes are often created on the columns in the WHERE clause to accelerate the condition judgment speed.

Similarly, indexes should not be created for some columns. In general, these columns that should not be indexed have the following characteristics: first, indexes should not be created for those columns that are rarely used in queries or referenced. 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. Second, indexes should not be added for those 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. Third, 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. Fourth, when the modification performance is much higher than the retrieval performance, you should not create a cable reference. 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 the modification performance is much higher than the retrieval performance, you should not create an index.

Index creation methods and features
How to create an index: 51aspx.com
There are multiple ways to create an index. These methods include directly creating an index and indirectly creating an index. Directly CREATE an INDEX. For example, you can use the create index statement or the create index Wizard to indirectly CREATE an INDEX. For example, you can also CREATE an INDEX when defining a primary key constraint or a unique key constraint in a table. Although both methods can create indexes, the specific content of the indexes they create is different.
You can use the create index statement or the INDEX creation Wizard to CREATE an INDEX. This is the most basic INDEX creation method, and this method is the most flexible. You can CREATE a custom INDEX ...... remaining full text>

Database index advantages and disadvantages

Creating an index 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.

Adding indexes also 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.

Indexes are created on certain columns in the database table. Therefore, when creating an index, you should carefully consider which columns can create an index and which Columns cannot create an index. In general, you should create an index on these columns, for example:

You can speed up the search on columns that frequently need to be searched;
In a column that acts as a primary key, the uniqueness of the column and the data arrangement structure in the organization table are enforced;
These columns are usually used in connection columns. These columns are mainly foreign keys, which can speed up the connection;
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;
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;
Create an index on the columns in the WHERE clause frequently to speed up condition judgment.
Reference: www.newsmth.net/..20.118