MySQL Index Analysis: Fit to build an index? Not fit to build an index? Go

Tag:io   ar    use    sp   on    data    div    problem    log   

Database indexing commonly used rules are as follows: 1, the table of the primary key, foreign keys must be indexed, 2, the data volume of more than 300 of the table should be indexed; 3. Tables that are frequently connected to other tables should be indexed on the Join field, 4, fields that often appear in the WHERE clause, especially large table fields, should be indexed ; 5, the index should be built on the field of high selectivity; 6, the index should be built on the small section, for large text fields and even long fields, do not build indexes; 7. The establishment of composite indexes needs careful analysis; Consider using single-field indexes instead:   A, selecting the main column fields in the composite index correctly, is generally a better choice of fields;   B, how many fields of a composite index often appear in the WHERE clause with and? is      there very little or no single-field query? If it is, you can create a composite index, otherwise consider the single-field index;   C, if the compound index contains fields that often appear separately in the WHERE clause, the decomposition is to multiple one-field indexes;   D, if the composite index contains more than 3 fields, carefully consider its necessity, Consider reducing the number of composite fields;   E, if you have a single field index, and the composite index on these fields, you can generally delete the composite index; 8. Frequent data manipulation of the table, do not build too many indexes; 9. Delete useless indexes and avoid negative impact on execution plan; These are some common criteria for establishing an index. The  establishment of the index must be cautious, the need for each index should be carefully analyzed, to establish the basis.   because too many indexes and inadequate, incorrect indexes are not good for performance: Each index established on the table increases the storage overhead, and the index increases processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of single-field index, generally have no value; Conversely, it also reduces performance when data is being deleted, especially for tables that are frequently updated, with greater negative impact.     in general, small tables are definitely not indexed,  or databases are recorded at more than billion data levels, or a non-relational database is recommended.  databases that have special fields, such as the Blob,clob field, are certainly not fit for indexing.  In fact, this problem is more like a experience in software projects.

Indexing of Tens MySQL database and the means to raise high performance

First, the matters needing attention:
First, you should consider whether tablespace and disk space are sufficient. We know that the index is also a kind of data, when the index is set to occupy a large number of table space. Therefore, the first thing to consider when indexing a large table is the problem of spatial capacity.
Secondly, the table should be locked when the index is built, so be aware that the operation should be done when the business is idle.
Second, performance adjustment aspects:
The first factor to consider is disk I/O. Physically, you should try to spread the index and data to different disks (regardless of the pattern). Logically, the data table space is separated from the index table space. This is the basic guideline that should be followed when indexing is under construction.
Second, we know that the table should be scanned for the full table when indexing, so we should consider the value of Db_file_multiblock_read_count the initialization parameter. The general setting is 32 or greater.

Again, the index should be adjusted to the size of the sorting area, except for a full table scan and also to perform a large number of sorting operations on the data.
Before 9i, you can increase the size of the sort_area_size at the session level, for example, set to 100m or larger.
After 9i, if the value of the initialization parameter workarea_size_policy is true, the sort area is automatically assigned from Pga_aggregate_target.
Finally, when indexing is established, you can add the nologging option. To reduce the number of redo generated during the indexing process, thus increasing the speed of execution.

MySQL issues to be aware of when establishing index optimization The design of MySQL index can make your database fly up, greatly improve the efficiency of the database. There are a few things to note when designing MySQL indexes: 1, creating an index Indexing is especially important for queries that are the primary application. A lot of times the performance problem is simply because we forgot to add an index, or we didn't add a more efficient index. If you do not index, then find any even a specific data will be a full table scan, if a table of large amounts of data and meet the results of a few, then the non-index will cause a fatal performance degradation. However, it is not always possible to index, for example, gender may be only two values, index not only have no advantage, but also affect the speed of the update, which is called over-index. 2, composite Index For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22; If we were to create a single index on area and age, because the MySQL query can only use one index at a time, even though this has been relatively non-indexed, the full table scan has improved a lot Rate, but if you create a composite index on the area and age two columns, it will be more efficient. If we create a (area, age, Salary), then it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix Characteristics. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn. 3, the index does not contain columns with null values This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed. 4, using a short index Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations. 5, sort the index problem The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns. 6,like Statement Operations It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "? a%" does not use the index and like "aaa%" can use the index. 7, do not perform calculations on columns SELECT * from Users where Year (adddate) 8, do not use not in and operation None in and operations do not use the index to perform a full table scan. Not in can be replaced by not exists, ID3 can use id>3 or ID

MySQL Index Analysis: Fit to build an index? Not fit to build an index? Go