Principles of MySQL Design index

Content from the book "Mysql++ Database development, optimization and Management Maintenance + 2nd edition + Tang Hanming"

Principles for designing indexes
1. The index column of the search is not necessarily the column you want to select. In other words, the most appropriate column for the index is the one that appears in the Where child
The column in the sentence, or the column specified in the JOIN clause, instead of 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 has multiple
A column of duplicate values that has the worst index effect. For example, a column that holds age has different values, and it's 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 (whichever value you search, 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 there is a CHAR (200) column, if within the first 10 or 20 characters, the majority value is unique,
Then do not index the entire column. Indexing the first 10 or 20 characters can save a lot of index space
may also make the query 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 the MyS QL
You can also hold 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 benefit of indexing only the first character of a column value,
Because there are not many different values in this 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
called the leftmost prefix. (This is different from indexing a column, and the prefix for a column is to use the first n words
As the index value. ）
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 is also used for the like operation. If you use only one column for other types of operations, such as STRCMP (), it is not worthwhile to index it.

7. InnoDB saves the data in a certain order by default, and if the primary key is explicitly defined, it is saved in the primary key order. If there is no primary key, but there is a unique index, it is saved in the order of the unique index. If several columns are unique and can be used as primary keys, you should select the most commonly accessed column as the primary key in order to improve query efficiency. In addition, the normal index of INNODB will hold the key value of the primary key, and all primary keys should choose a shorter data type whenever possible. as you can see, the modification of the primary key should be avoided as much as possible. With the DBA's test, the increment of the primary key can improve insert performance.

Principles of MySQL Design index