A brief discussion on which fields in Mysql are suitable for indexing

1 The common rules for creating database indexes are as follows:

1. The primary key and foreign key of the table must have indexes;
2. Tables with more than 300 data should have indexes;
3. For tables that are often connected to other tables, indexes should be created on the connection fields;
4. Fields that often appear in the Where clause, especially those in large tables, should be indexed;
5. Indexes should be built on fields with high selectivity;
6. Indexes should be built on small fields. Do not build indexes on large text fields or even very long fields.
7. The establishment of composite indexes requires careful analysis; try to consider using single-field indexes instead:

A. Correctly select the primary column field in the composite index, which is generally a field with good selectivity;
B. Do several fields of a composite index often appear in the Where clause in AND mode at the same time? Are there few or no single-field queries? If yes, you can create a composite index; otherwise, consider a single field index;
C. If the fields included in the composite index often appear alone in the Where clause, decompose it into multiple single-field indexes;
D. If there is a single-field index and a composite index on these fields, you can generally delete the composite index;

8. Do not create too many indexes for tables that frequently perform data operations;
9. Delete useless indexes to avoid negative impact on the execution plan;

The above are some common criteria for judging when establishing indexes.
The establishment of indexes must be cautious. The necessity of each index should be carefully analyzed and there should be a basis for its establishment.
Because too many indexes and insufficient or incorrect indexes are of no benefit to performance: each index created on a table increases storage overhead, and the index also increases processing overhead for insert, delete, and update operations. In addition, too many composite indexes are generally worthless when there are single-field indexes. On the contrary, they will reduce the performance when adding and deleting data, especially for tables that are frequently updated, where the negative impact is greater.
Generally speaking, small tables should not be indexed.
Or if the database records more than 100 million data items, it is recommended to use a non-relational database.
There are also some databases with special fields, such as BLOB and CLOB fields, which are definitely not suitable for indexing.
In fact, this question feels more like an experience of doing software projects.

2. Matters related to indexing of MySQL databases with tens of millions of records and methods to improve performance

1. Notes:

First, you should consider whether there is enough table space and disk space. We know that index is also a kind of data, and when creating an index, it will inevitably take up a lot of table space. Therefore, when creating an index for a large table, the first thing to consider is the space capacity.
Secondly, the table needs to be locked when creating the index, so care should be taken to perform the operation when the business is idle.

2. Performance adjustment:

The first consideration is disk I/O. Physically, you should try to spread indexes and data across different disks (regardless of the array). Logically, the data tablespace is separate from the index tablespace. This is the basic principle that should be followed when building an index.

Secondly, we know that when creating an index, the entire table must be scanned, so we should consider increasing the value of the initialization parameter db_file_multiblock_read_count. Generally set to 32 or greater.

Again, in addition to scanning the entire table, creating an index also requires a large amount of sorting operations on the data. Therefore, the size of the sorting area should be adjusted.

Before 9i, you can increase the size of sort_area_size at the session level, for example, set it to 100m or larger.

After 9i, if the value of the initialization parameter workarea_size_policy is TRUE, the sort area is automatically allocated from pga_aggregate_target.

Finally, when creating an index, you can add the nologging option. This can reduce the amount of redo generated during indexing and increase execution speed.

Issues that need to be paid attention to when optimizing MySql indexes

Well-designed MySql indexes can make your database fly and greatly improve database efficiency. There are a few things to note when designing a MySQL index:

1. Create an index

For applications where queries are the main focus, indexes are particularly important. Many times, performance problems are simply caused by forgetting to add an index, or failing to add a more effective index. If no index is added, then a full table scan will be performed to find any specific data, even if it is just one specific data. If a table has a large amount of data but few results that meet the conditions, then not adding an index will cause a fatal performance degradation. However, it is not necessary to create an index in all situations. For example, gender may only have two values. Creating an index not only has no advantage, but also affects the update speed. This is called over-indexing.

2. Composite Index

For example, there is a sentence like this:

select * from users where area='beijing' and age=22; 

If we create a single index on area and age respectively, since MySQL query can only use one index at a time, although this has improved the efficiency a lot compared to the full table scan without indexing, creating a composite index on the area and age columns will bring higher efficiency. If we create (area, age,
salary), then it is equivalent to creating three indexes: (area,age,salary), (area,age), (area), which is called the best left prefix feature. Therefore, when creating a composite index, we should put the columns that are most often used as restrictions on the left, in descending order.

3. The index will not include columns with NULL values

As long as the column contains NULL values, it will not be included in the index. As long as one column in a composite index contains NULL values, this column will be invalid for this composite index. Therefore, we should not set the default value of the field to NULL when designing the database.

4. Use short indexes

When indexing a series column, a prefix length should be specified if possible. For example, if you have a CHAR(255) column, if most values ​​are unique within the first 10 or 20 characters, then do not index the entire column. Short indexes can not only improve query speed but also save disk space and I/O operations.

5. Sorting index problem

MySQL queries use only one index, so if the index is already used in the where clause, the columns in the order by clause will not use the index. Therefore, do not use sorting operations if the default sorting of the database can meet the requirements; try not to include sorting of multiple columns. If necessary, it is best to create a composite index for these columns.

6. Like statement operation

Generally speaking, the use of the like operation is not encouraged. If it must be used, how to use it is also a problem. like "%a%" will not use the index but like "aaa%" can use the index.

7. Don’t perform operations on columns

select * from users where YEAR(adddate)

8. Do not use NOT IN and operations

Neither the NOT IN nor the IN operation will use the index and will result in a full table scan. NOT IN can be replaced by NOT EXISTS, and for id3, you can use id>3 or id

Add index example:

CREATE INDEX IDX_AUDITSTATUS ON [shanghaiDB].[dbo].[Activity](AUDITSTATUS) WITH(ONLINE=ON)
 
CREATE INDEX IDX_ANUMMID ON [nantongDB].[dbo].[Orders](ANUM,MID) WITH(ONLINE=ON)
 
CREATE INDEX IDX_SiteCode ON Usercenter.[dbo].MO(SiteCode) WITH(ONLINE=ON)
 
CREATE INDEX IDX_AccessDt ON [all].[dbo].[AccessLog](AccessDt) WITH(ONLINE=ON)

Create index Note: If you are creating an index for a large table, remember to add the ONLINE parameter.

I was optimizing the database these days. I had a table with 200 million records and found that I needed to add a joint index. I used the common create index index_name on tablename (entp_id,sell_date). The result was tragic. All DML statements were blocked, causing the system to be unable to be used normally. Fortunately, it was 10 o'clock in the evening and there were not many users. After one hour, the index was completed and the blocking was resolved.

I checked online and found that if you add the online parameter, you can do indexing online without blocking all DML statements. This is a lesson learned the hard way, and I want to share it with you. The specific differences between adding online and not adding online are as follows:

The impact of DML operations on create index. If other processes are performing DML operations on the data corresponding to this index during the create operation, the create operation will be affected:

SQL> create table test (id number, name varchar2(20)); 
Table created.

Then restart a session:

SQL> insert into test values ​​(1,'lms');
1 row created.
<no commit> 
SQL> create index t1 on test(id);
create index t1 on test(id)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Add the parameter "online". After adding this parameter, in addition to keeping the index online during the create process, Oracle will also wait for all DML operations to complete before creating the index, and then obtain the DDL lock and start the create.

SQL> create index t1 on test(id) online;
<hold before commit>
<after commit> 
SQL> commit; 
Commit complete.
Index altered.

If you don't commit, the above operations will be held.

Therefore, it is best to add online when creating and rebuilding indexes in the future.

This is the end of this article about which MySQL fields are suitable for indexing. For more relevant MySQL field index content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!