Considerations for mysql Indexes

Mysql index considerations mysql optimization can be y-optimized from the selection of hardware devices, operating systems, database structure design, SQL queries, and applications, here, we will only optimize the database design and query statements. 1. Creating an index is especially important for the most important query applications. 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 this parameter is not specified, 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. Www.2cto.com 2. For example, a composite index statement is as follows: select * from users where area = 'beijing' and age = 22; for example, if we create a single index on the area and age, mysql queries can only use one index at a time. Therefore, although full table scan improves the efficiency when no index is available, however, creating a composite index on the "area" and "age" columns will increase the efficiency.

If we create a composite index (area, age, salary), it is equivalent to creating (area, age, salary), (area, age), (area) three indexes, which are called the best left prefix feature. 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. If an index does not contain a column with a NULL value, as long as the column contains a NULL value, it will not be included in the index. If one column in the composite index contains a NULL value, this column is invalid for this compound index. Therefore, do not set the default value of a field to NULL during database design. 4. Use a short index to index the string 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 problem 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. Generally, like statements are not encouraged to operate on www.2cto.com 6. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes. 7. Do not perform the select * from users where YEAR (adddate) <2007 operation on the column. The operation will be performed on each row, which will cause the index to fail and scan the entire table, therefore, we can change it to select * from users where adddate <'2017-01-01 '; 8, if you do NOT use the not in or <> operation, the entire table is scanned without an index. Not in can be replaced by not exists, and id <> 3 can be replaced by id> 3 or id <3