Myssql Indexing Tips

Index

An index is a structure that sorts the values of one or more columns of a data table to speed up data rows based on indexed fields
and optimize query execution speed to avoid full table scanning. Indexes are database schema pairs that directly affect database performance.
The image, so it is very important. When you define a primary key and a unique key constraint, the database engine automatically creates an index of the related field, which
It requires the user to define the index itself. index creation must be cautious, too many indexes will reduce the efficiency of data table update, because
The index needs to be updated every time the data table is updated, so the update frequency of the table needs to be considered when creating the index.

In practice, a test or test can be used to select the appropriate index for the database, generally the following principles of indexing:

(1) index on the large table , if the large table data retrieval only a small part of the total data, can significantly improve
Data retrieval speed;

(2) indexes on foreign keys or columns used for table joins to avoid full table scans when data connections or cascading updates increase
Table connection speed;

(3) The index on the commonly used data sort field can improve the speed of data sorting;

(4) It is not intended to be indexed on a column with a large number of NULL values or duplicate values;

(5) Do not plan to index on frequently updated columns.

Indexes can improve the speed of data retrieval, but reduce the efficiency of updating data tables. For bulk loads or updates, you can test
The index is deleted when the mount or update is completed, and the indexes are rebuilt after the mount or update is complete. The index consumes disk space within the database,
Must be taken into account in database space planning. Create a separate tablespace for the index and ensure that the index table space
Data files and table data files are stored on separate disks, avoiding drive contention and accelerating the overall data access
Performance.

- principles of other design indexes
Index design can follow some of the existing principles, when creating the index, please try to consider compliance with these principles, easy to improve the efficiency of the index, and more efficient use of the index.

the index column of the search is not necessarily the column you want to select. In other words, the column that best fits the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause, rather than the column that appears in the select list after the SELECT keyword.

use a unique index . Consider the distribution of values in a column. The larger the cardinality of the indexed columns, the better the index works. For example, the column that holds the date of birth has different values, and it is easy to differentiate between rows. The columns used to record the gender, which contain only "M" and "F", are not much useful for indexing this column, because no matter what value is searched, approximately half of the rows are drawn.

use a short index . If you index a string column, you should specify a prefix length, which you should do whenever possible. For example , there is a char (200) column, and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Indexing the first 10 or 20 characters can save a lot of index space, and may make queries faster. Smaller indexes involve less disk IO, and shorter values compare faster. More importantly, for shorter key values, the blocks in the index cache can hold more key values, so MySQL can also hold more of the blocks in the cache in memory to accommodate more key values, so MySQL can also accommodate more values in memory. This increases the likelihood of finding rows without having to read more chunks in the index.

take advantage of the leftmost prefix . When you create an index of n columns, you are actually creating n indexes that MySQL can use. Multiple-column indexes can serve several indexes because the leftmost Lie in the index can be used to match rows. such a column set is called the leftmost prefix.

do not over-index . Do not assume that the index "the more the better", everything is indexed is wrong. Each additional index consumes additional disk space and reduces the performance of the write operation. When you modify the contents of a table, the index must be updated, and sometimes refactoring may be required, so the more indexes you have, the longer it takes. If an index is seldom exploited or never used, it will unnecessarily slow down the table's modification speed. In addition, MySQL takes time to consider individual indexes when generating an execution plan. Creating redundant indexes has brought more work to query optimization. Too many indexes may also make MySQL choose the best index to use. Maintaining only the desired index is advantageous for query optimization.

Summary
Indexes are used to quickly find rows that have a specific value in a column. If you do not use an index, MySQL must start with the 1th record and then read through the entire table until it finds the relevant row. The larger the table, the more time it takes. If there is an index to the column queried in the table, MySQL can quickly reach a location to search the middle of the data file, and there is no need to look at all the data. If a table has 1000 rows, this is at least 100 times times faster than sequential reads. Note Sequential reads are much faster if you need to access most of the rows because you should avoid disk searches at this time.
Most MySQL indexes (such as primary KEY, UNIQUE, Index, Fulltext, and so on) are stored in btree. Just the index of the spatial column type uses Rtree, and the memory table also supports the hash index.

Myssql Indexing Tips