Design, use, and optimization of MySQL indexes

Original: http://bbs.landingbj.com/t-0-243071-1.html

MySQL Index overview

All MySQL column types can be indexed. Using indexes on related columns is the best way to improve the performance of select operations. Defines the maximum number of indexes and the maximum index length per table based on the storage engine. All storage engines support at least 16 indexes per table with a total index length of at least 256 bytes. Most storage engines have a higher limit.

In MySQL 5.1, for MyISAM and InnoDB tables, the prefix can be up to 1000 bytes long. Note that the prefix limit should be measured in bytes, whereas the prefix length in the CREATE TABLE statement is interpreted as the number of characters. When specifying a prefix length for a column that uses multibyte character sets, you must consider it.

You can also create Fulltext indexes. The index can be used for full-text search. Only the MyISAM storage engine supports Fulltext indexes and is only char, VARCHAR, and text columns. Indexes are always made for the entire column, and local (prefix) indexes are not supported. You can also create indexes for spatial column types. Only the MyISAM storage engine supports space types. The spatial index uses the R-tree. The memory (HEAP) storage engine uses a hash index by default, but it also supports B-tree indexing.

Principles for designing indexes

1) The index column for 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.

2) use a unique index.

Consider the distribution of values in a column. For columns with unique values, the index works best, and columns with multiple duplicate values have the worst indexes. For example, a column that holds age has different values, and it's easy to differentiate between rows. The columns used to record sex, which contain only "M" and "F", are not much useful for indexing this column (whichever value you search for, you will get about half of the rows).

3) Use a short index.

If you index a string, you should specify a prefix length, which you should do whenever possible. For example, if you have 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 cable??? Space, and may make queries faster. Smaller indexes involve fewer disk I/O, 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 accommodate more values in memory. This increases the likelihood of finding rows without having to read more chunks in the index. (Of course, some common sense should be used.) It is not possible to have the advantage of indexing only the first character of a column value, because there are not many different values in the index. ）

4) Use 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. (This is different from indexing a column, where the prefix of a column is the index value using the first n characters.) ）

5) 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 write operations, as we have described earlier. 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 each index 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. If you want to add an index to an indexed table, you should consider whether the index you want to increase is the leftmost index of an existing multi-column index. If so, don't bother to increase the index because it's already there.

6) Consider the type of comparison that is performed on the column.

Indexes are available for <, < =, =, > =, >, and between operations. When a pattern has a direct volume prefix, the index also uses the?? A like operation. If you use only one column for other types of operations, such as strcmp (), it is not worthwhile to index it.

Btree Index and Hash index

For Btree and hash indexes, when you use =, <=>, in, is null, or is the NOT NULL operator, the comparison relationship between the key element and the constant value corresponds to a range condition. Hash indexes also have some other characteristics: they are used only for equality comparisons (but soon) using the = or <=> operator. The optimizer cannot use a hash index to speed up the order by operation. (This type of index cannot be used to search for the next entry sequentially). MySQL cannot determine approximately how many rows between two values (this is used by the range optimizer to determine which index to use). If you change a MyISAM table to a memory table of hash-index, some queries will be affected. You can only use the entire keyword to search for a row. (with a B-tree index, the leftmost prefix of any keyword can be used to find rows).

For Btree indexes, when using >, <, >=, <=, between,! =, or <>, or like ' pattern ' (where ' pattern ' does not start with a wildcard) operator, The comparison relationship between the key element and the constant value corresponds to a range condition. "Constant value" means a constant in the query string, a const in the same join, or a column in the system table, the result of an unrelated subquery, and an expression that consists entirely of a subexpression of the preceding type.

The following is an example of a query with a scope condition in the WHERE clause.

The following range queries apply to Btree indexes and hash indexes:

SELECT * from t1 WHERE key_col = 1 OR key_col in (15,18,20);

The following range queries apply to Btree indexes

SELECT * from t1 WHERE key_col > 1 and Key_col < 10;
SELECT * from T1 WHERE key_col like ' ab% ' OR key_col between ' bar ' and ' foo ';

How MySQL uses the index

Indexes are used to quickly find rows that have a specific value in a column. Without an index, MySQL must start with the 1th record and finish reading the entire table?? To find the relevant line. 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 find 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 If you need to access most of the rows, sequential reads are much faster because at this point we avoid disk searches.

Most MySQL indexes (PRIMARY KEY, UNIQUE, index, and fulltext) are stored in the B-tree. Only the index of the spatial column type uses the R-tree, and the memory table also supports the hash index.

Design, use, and optimization of MySQL indexes