Analysis of MySql index usage strategy

MySql Index

Index advantages

1. You can ensure the uniqueness of the data by creating a unique index or primary key index.
2. Improve the performance of retrieved data
3. The connection conditions in table connection can speed up the direct connection between tables
4. Create indexes. Using indexes in queries can improve performance.

Index Disadvantages

1. Creating and maintaining indexes takes time, which increases with the amount of data.
2. Index files will take up physical space. In addition to the physical space required for the data table, each index will also take up a certain amount of physical space.
3. When inserting, updating, or deleting data in a table, the index must also be maintained dynamically, which will slow down the data maintenance process.

(Creating an index will take up disk space for the index file. Generally this problem is not too serious, but if you create multiple combined indexes on a large table, the index file will expand quickly).

Things to note when using indexes

1. Speed ​​up indexing on columns that are frequently searched
2. The uniqueness of the column can be ensured on the primary key column
3. Adding indexes to the join conditions between tables can speed up the join query.
4. Adding indexes to distinct columns that are often used for sorting (order by), grouping (group by), and so on can speed up sorting query time. (Order by alone cannot use indexes, so consider adding where or limit to the index.)
5. Create an index on the field after some where clauses < <= > >= BETWEEN IN and like in some cases (B-TREE)

6. If you create an index for the nickname field in the like statement, the index will not work when the query statement is nickname lick '%ABC%'. However, the index will work if the query statement is nickname lick 'ABC%'.

7. The index will not include NULL columns. If a column contains NULL values, it will not be included in the index. If a column in a composite index contains NULL values, the composite index will be invalid. Generally, a default value of 0 or ' ' string is required.

8. Use short indexes. If one of your fields is Char(32) or int(32), specify a prefix length when creating the index, such as the first 10 characters (assuming that most values ​​are unique). Then short indexes can increase query speed, save disk space, and reduce I/O operations.

9. Do not perform operations on columns, as this will invalidate the MySQL index and perform a full table scan

10. Choose the smaller the data type, the better, because usually the smaller the data type, the smaller the space occupied in the disk, memory, CPU, cache, and the faster the processing.

When not to create an index

1. Columns that are rarely used in queries should not be indexed. If indexes are created, however, MySQL performance will be reduced and space requirements will increase.
2. Columns with very little data should not be indexed. For example, a gender field with 0 or 1. In the query, the data in the result set accounts for a large proportion of the data rows in the table. MySQL needs to scan a large number of rows. Adding indexes will not improve efficiency.
3. Columns defined as text, image, and bit data types should not have indexes added.
4. When the modification (UPDATE, INSERT, DELETE) operations of the table are much greater than the retrieval (SELECT) operations, you should not create an index. These two operations are mutually exclusive.

The above is the detailed analysis of MySql index usage strategy. For more information about MySQL index, please pay attention to other related articles on 123WORDPRESS.COM!