Design and use of mysql index

Design and use of indexes
5.1 Overview of Mysql indexes
All MySQL column types can be indexed. Using indexes on related columns is the best way to improve the performance of SELECT operations. Define the maximum number of indexes and maximum index length for each table according to 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 higher limits.
In MySQL 5.1, for MyISAM and InnoDB tables, prefixes can be up to 1000 bytes long. Note that the limit of a prefix should be measured in bytes, whereas the prefix length in the CREATE TABLE statement is interpreted as a number of characters. Be sure to consider this when specifying a prefix length for columns that use multibyte character sets.
You can also create FULLTEXT indexes. The index can be used for full-text search. Only the MyISAM storage engine supports FULLTEXT indexes, and only for CHAR, VARCHAR, and TEXT columns. Indexes are always performed on the entire column, partial (prefix) indexes are not supported. Indexes can also be created on spatial column types. Only the MyISAM storage engine supports spatial types. Spatial indexes use R-trees. By default, the MEMORY (HEAP) storage engine uses hash indexes, but B-tree indexes are also supported.
5.2 Principles of index design
1. The index column to be searched is not necessarily the column to be selected. In other words, the most suitable columns for indexing are the columns that appear in the WHERE clause, or the columns specified in the join clause, rather than the columns that appear in the select list after the SELECT keyword.
2. Use unique index. Consider the distribution of values ​​in a column. Indexes work best for columns with unique values ​​and worst for columns with multiple duplicate values. For example, the column holding age has different values, making it easy to distinguish between rows.
The column used to record gender only contains "M" and "F", so indexing this column is of little use (no matter which value is searched, about half of the rows will be obtained)
3. Use a short index. If you are indexing a string, you should specify a prefix length, and this should be done whenever possible.
For example, if you have a CHAR(200) column, if most values ​​are unique within the first 10 or 20 characters, then don't 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 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 hold more values ​​in memory. This increases the likelihood of finding the row without reading larger blocks in the index.
(Of course, some common sense should be used. For example, indexing only by the first character of the column value is unlikely to be of much benefit, because there will not be many different values ​​in this index.)
4. Use the leftmost prefix. When you create an n-column index, you actually create n indexes that MySQL can use.
A multi-column index can function as several indexes because the leftmost set of columns in the index can be used to match rows. Such a set of columns is called a leftmost prefix. (This is different from indexing the prefix of a column, which uses the first n characters of the column as the index value.)
5. Don’t over-index. Don't think that "the more indexes, the better". It's wrong to use indexes for everything. Each additional index takes up additional disk space and reduces the performance of write operations, which we have already introduced. When the contents of the table are modified, the index must be updated and sometimes may need to be reconstructed. Therefore, the more indexes, the longer it takes. If you have an index that is rarely or never used, it will unnecessarily slow down modifications to the table.
In addition, MySQL must consider each index when generating an execution plan, which also takes time. Creating redundant indexes creates more work for query optimization. Too many indexes may also prevent MySQL from selecting the best index to use. Keeping only the required indexes facilitates query optimization. If you want to add an index to an already indexed table, you should consider whether the index to be added is the leftmost index of an existing multi-column index. If so, don't bother adding this index because it already exists.
6. Consider the types of comparisons you make on columns. Indexes can be used with the " < ", " < = ", " = ", " > = ", " >" and BETWEEN operations. Indexes are also used in LIKE operations when the pattern has a literal prefix. If a column is only used for other types of operations (such as STRCMP()), there is no value in indexing it.
5.3 btree index and hash index
For BTREE and HASH index, when using =, <=>, IN, IS NULL or IS NOT NULL operators, the comparison relationship between key elements and constant values ​​corresponds to a range condition. Hash indexes have some additional features: they are only used for equality comparisons using the = or <=> operators (but fast). The optimizer cannot use hash indexes to speed up ORDER BY operations.
(This type of index cannot be used to search for the next entry in sequence). MySQL cannot determine approximately how many rows there are between two values ​​(this is used by the range optimizer to determine which index to use). If you change a MyISAM table to a hash-indexed MEMORY table, some queries will be affected. Only the entire keyword can be used to search a row. (With a B-tree index, the leftmost prefix of any key can be used to find the row).
For BTREE index, when using >, <, >=, <=, BETWEEN, != or <>, or LIKE 'pattern' (where 'pattern' does not start with a wildcard) operator, The comparison relationship between key elements and constant values ​​corresponds to a range condition.
"Constant value" refers to: a constant in a query string, a const in the same join or a column in the system table, the result of an uncorrelated subquery, an expression composed entirely of subexpressions of the previous type
Here are some Examples of queries with range conditions in the WHERE clause:
The following range queries are applicable to btree indexes and hash indexes
SELECT * FROM t1WHEREkey_col = 1ORkey_col IN (15,18,20);
The following range queries are applicable to btree indexes
SELECT * FROM t1WHERE key_col > A row with a specific value in the column. Without using an index, MySQL must start at record 1 and read through the entire table until it finds the relevant row. The larger the table, the more time it takes. If the queried column in the table has an index, MySQL can quickly get to a point where it searches the middle of the data file without having to look at all the data. For example, if a table has 1000 rows, this is at least 100 times faster than sequential reading. Note that if you need to access a large portion of the rows, sequential reading is much faster because at that point we avoid a disk seek.
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX and FULLTEXT) are stored in B-trees. Only spatial column type indexes use R-tree, and MEMORY tables also support hash indexes.
For a detailed explanation of the circumstances under which the database will use indexes and the circumstances under which the database will not use indexes, please refer to the relevant chapters of the optimization chapter, which will not be repeated here.

If you want to get more related articles, please pay attention to php Chinese website (www.php.cn)!