common database index creation rules are as follows: 1. The table's primary key and foreign key must have an index; 2. the table with more than 300 of the data volume should have an index; 3. The table that is often connected to other tables, indexes should be created on the connected fields; 4. Fields frequently appearing in the WHERE clause, especially those in large tables, should be indexed; 5. The indexes should be created on highly selective fields; 6. Indexes should be built on small fields. Do not create indexes for large text fields or even extra-long fields. 7. The establishment of compound indexes should be carefully analyzed; try to replace it with a single field Index: A. correctly select the primary column field in the composite index, which is generally a highly selective field; b. Do the fields of the composite index often appear in the WHERE clause in the and mode? Are there very few or even no single-field queries? If yes, a composite index can be created; otherwise, a single field index is considered; C. If the fields contained in the composite index are often separately contained in the WHERE clause, they are decomposed into multiple single field indexes; d. If the composite index contains more than three fields, consider the necessity carefully and reduce the composite fields. E. If there is a single field index, compound indexes on these fields can be deleted. 8. Do not create too many indexes for tables that frequently perform data operations. 9. Delete useless indexes, avoid negative impact on execution plans. The above are the basis for determining general indexes. The establishment of indexes must be careful, and the necessity of each index should be carefully analyzed, so there must be a basis for the establishment. Because too many indexes and inadequate or incorrect indexes have no benefits for performance: Each index created on a table increases storage overhead, indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes generally have no value when there is a single-field index. On the contrary, they also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is greater. In general, we recommend that you use non-relational databases if you do not create indexes for small tables or if the database records more than million data records. Databases with some special fields, such as blob and clob fields, are certainly not suitable for indexing. In fact, this problem is more inclined to be an experience in software projects.
How to index tens of millions of MySQL Databases and improve performance
I. Notes:
First, check whether the tablespace and disk space are sufficient.We know that indexes are also a kind of data, which will occupy a large number of tablespaces when creating indexes. Therefore, when creating an index for a large table, we should first consider the space capacity issue.
Second, you need to lock the table when creating an index. Therefore, you should pay attention to the operation when the business is idle.
Ii. Performance adjustment:
Disk I/O is the first consideration. Physically, try to distribute the index and data to different disks (regardless of the array ). Logically, the data table space is separated from the index tablespace. This is the basic principle to be followed when creating an index.
Secondly, we know that we need to scan the entire table when creating an index. Therefore, we should increase the value of the initialization parameter db_file_multiblock_read_count. Generally, it is set to 32 or greater.
In addition to full table scanning, index creation also requires a large number of sorting operations on data. Therefore, the size of the sorting area should be adjusted.
Before 9i, you can increase the sort_area_size at the session level, for example, set it to MB or larger.
After 9i, if the value of the initialization parameter workarea_size_policy is true, the sorting area is automatically allocated from pga_aggregate_target.
Finally, you can addNologging. To reduce the large amount of redo generated during index creation, thus improving the execution speed.
Notes for MySQL index creation Optimization
Designing a MySQL index can make your database fly, greatly improving database efficiency. Note the following points when designing a MySQL index:
1. Create an index
Indexes are especially important for applications where queries account for the majority. In many cases, the performance problem is very simple because we forget to add an index, or we have not added a more effective index. If no index is added, a full table scan is performed to search for any specific data. If the data volume of a table is large and the results meet the requirements are few, if you do not add an index, it will cause fatal performance degradation. However, it is not always necessary to create an index. For example, there may be only two values for gender. Creating an index not only has no advantages, but also affects the update speed. This is called excessive index.
2. Composite Index
For example, there is a statement like this: Select * from users where area = 'beijing' and age = 22;
If we create a single index on the area and age respectively, because MySQL queries can only use one index at a time, the full table scan improves the efficiency even if the index is not used.
Rate, However, creating a composite index on the "area" and "Age" columns will increase the efficiency. If we create an (area, age,
Salary) composite index, which is equivalent to creating three indexes (area, age, salary), (area, age), and (area). This is called the best left prefix.
Features. Therefore, when creating a composite index, we should place the columns that are most commonly used as restrictions on the leftmost and decrease in turn.
3. The index does not contain columns with null values.
As long as a column contains a null value, it will not be included in the index. If a column in the composite index contains a null value, this column is invalid for this composite index. Therefore, do not set the default value of a field to null during database design.
4. Use short Indexes
Index a string or column. If possible, specify a prefix length. For example, if a char (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.
5. Sorting index problems
MySQL queries only use one index. Therefore, if an index is already used in the WHERE clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.
6. Like statement operation
Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "A %" does not use indexes, but like "AAA %" can use indexes.
7. Do not perform operations on columns
Select * from users where
Year (adddate)
8. Do not use not in and operations
Neither the not in nor the operation will use the index to scan the entire table. Not in can be replaced by not exists, while ID3 can use ID> 3 or ID |
|
1. the operations for determining a data table are a large number of queries or a large number of increment operations, so as to determine the number of indexes used, the "add more index" operation should limit the index data. If you want to perform multiple queries, you can add the index data.
2. Create an index on the secondary indexes to help you query the secondary indexes. Check your SQL statement to create an index for the fields in the WHERE clause. Enables the query engine to quickly locate a specified condition.
3. Optimize the system performance by creating some merge indexes (modifying the merge indexes will consume more time and fill the magnetic space)
4. Creating an index on a small table (with few records) may affect the performance of the table, because it is more efficient to perform table profiling at this time. (The chatbot cannot be processed intelligently)
5. Avoid creating an index for a field with a minimum value (for example, attribute)
6. Avoid selecting columns with large data types as indexes.
Do not create indexes for frequently inserted or deleted tables
