“This is the 28th day of my participation in the November Gwen Challenge. See details of the event: The Last Gwen Challenge 2021”.
1 The common rules for creating database indexes are as follows:
The primary key and foreign key of a table must have an index. 2. Tables with more than 300 data should have indexes; 3. If a table is frequently joined with other tables, create an index on the join field. 4. Columns that frequently appear in the Where clause, especially 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 for large text fields or even long fields. 7. The establishment of composite indexes requires careful analysis; Consider using single-field indexes instead:
- A. Correctly select the main column field in the composite index, generally A field with good selectivity;
- B. How often do several fields of a composite index appear together in the Where clause as AND? Are there few or no single-field queries? If so, you can create a composite index; Otherwise consider a single-field index;
- C. Decompose a composite index into multiple single-field indexes if it contains fields that often appear alone in the Where clause;
- D, if there is a single field index, and there is a compound index on these several fields, generally can delete the compound index;
8, frequent data operations of the table, do not create too many indexes; 9. Delete useless indexes to avoid negative impact on execution plan;
These are some common criteria for indexing.
The index must be carefully established, the necessity of each index should be carefully analyzed, and there should be a basis for the establishment. Because too many indexes and inadequate or incorrect indexes are not good for performance: each index created on the table increases storage overhead, and indexes increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of a single field index, are generally worthless; Conversely, it degrades performance when data is added and deleted, especially for frequently updated tables. In general, small tables definitely do not have indexes, or the database records are more than 100 million data level, still recommended to use a non-relational database. There are also special field databases, such as BLOBs, where CLOB fields are definitely not suitable for indexing. In fact, this question is more inclined to do a software project experience.
2. The index of MySQL database and the means to improve performance
One, matters needing attention:
First, you should consider whether the table space and disk space are sufficient. We know that an index is also data, and that creating an index is bound to take up a lot of table space. Therefore, when creating indexes for a large table, the first consideration should be space capacity. Second, the table is locked while the index is being built, so care should be taken that the operation takes place when the business is idle.
Two, performance adjustment:
The first consideration is disk I/O. Physically, indexes and data should be distributed on different disks as much as possible (regardless of the array). Logically, data table Spaces are separated from index table Spaces. This is the basic rule to follow when building an index.
Second, you should consider increasing the db_FILe_multiblock_read_count initialization parameter because you know that a full table scan is required during index creation. It is generally set to 32 or greater.
Thirdly, in order to build an index, a large number of sorting operations should be performed on the data in addition to the full table scan. Therefore, the size of the sorting area should be adjusted.
Before 9I, sort_AREA_size can be increased at the session level, for example, to 100m or greater.
After 9i, if the value of the workareA_size_policy initialization parameter is TRUE, then the sort range is automatically allocated from the pGA_aggregate_target.
Finally, when indexing, you can add the Nologging option. To reduce the amount of redo generated during indexing and thus speed up execution.
MySql > create index optimization
MySql index design can make your database fly, greatly improve database efficiency. When designing MySql indexes, note the following:
1, create index
Indexes are especially important in applications where queries dominate. Many times performance problems are simply caused by forgetting to add an index, or not adding a more efficient index. Without indexing, any search for even a specific piece of data will result in a full table scan, and if a table has a large amount of data and few qualifying results, not indexing can cause fatal performance degradation. However, not all cases need to be indexed. For example, there may be only two values for gender. Indexing not only has no advantage, but also can affect the update speed, which is called over-indexing.
2. Composite index
For example, a statement looks like this:
select * from users where area='Beijing'and age=22;
Copy the codeIf we create a single index on area and age columns, the mysql query can only use one index at a time, so the efficiency of a full table scan is much higher than that of a full table scan without indexes. However, if we create a compound index on area and age columns, the efficiency will be much higher. If we create a composite index of (area,age,salary), then we actually create three indexes of (area,age,salary), (area,age), and (area), which is called the optimal left prefix feature. Therefore, when creating a composite index, we should place the columns most commonly used as constraints at the far left, descending in order.
3. The index will not contain columns with NULL values
Any column that contains a NULL value will not be included in the index, and any column in the composite index that contains a NULL value is invalid for the composite index. So we don’t want the default value of the field to be NULL when we design the database.
4. Use short indexes
Index the string, specifying a prefix length if possible. For example, if you have a CHAR(255) column, do not index the entire column if multiple values are unique within the first 10 or 20 characters. Short indexes not only improve query speed but also save disk space and I/O operations.
5. Sort index problem
Mysql queries use only one index, so if an index is already used in the WHERE clause, the column in order BY will not be indexed. Therefore, the database default sort can meet the requirements of the situation do not use the sort operation; Try not to include more than one column sort, and create composite indexes for those columns if necessary.
6, the like statement operation
The use of the like operation is generally discouraged, and it is also a question of how to use it if you must. Like “%a%” does not use indexes whereas like “aaa%” can use indexes.
7. Do not perform operations on columns
select * from users where YEAR(adddate)
Copy the code8. Do NOT use NOT IN and operations
Neither NOT IN nor operation will use indexes to perform a full table scan. NOT IN can be replaced by NOT EXISTS. If id3 is used, id>3 or ID can be used
Example for adding an index:
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)
Copy the codeN If a large table is to be indexed, be sure to add the ONLINE parameter
Create index index_NAME on tablename (entp_id, Sell_date); create index index_name on tablename As a result, the system can not be used normally. Fortunately, it is 10:00 PM and there are not many users. In one hour, the index ends and the blocking is solved.
If you add the online parameter, you can do the index online, and do not need to block all the DML statements, the difference is as follows:
- Impact of DML operations on Create Index. If another process is performing DML operations on the index at the time of create, the create will be affected:
SQL> create table test (id number, name varchar2(20));
Table created.
Copy the codeThen open a new 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
Copy the code- Oracle will wait for all DML operations to complete before creating index, and then obtain the DDL lock to start creating index.
SQL> create index t1 on test(id) online;
<hold before commit>
<after commit>
SQL> commit;
Commit complete.
Index altered.
Copy the codeIf you do not commit, the above operation will hold.
Therefore, it is better to add online to create index and rebuild index in the future.