mysql-Index Design principles

Index design principles

1. The index column of the search is not necessarily the column you want to select. That is, the column that best fits the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause, not the one that appears in the selection list after the Select keyword.

2. Use a unique index. Considering the distribution of a column, the larger the cardinality of the indexed column, the better the index works. For example, it is not much use to index the Gender m/f column.

3. Use a short index. If you are indexing a string, you should specify a prefix length if possible.

4. Use the leftmost prefix. Try to place the "left" field with the most frequent and well-filtered fields

5. Do not over-index.

6. 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.

For section 6, select time from Table a where ID >xxxx. Where ID is the primary key. Therefore, if time is an index, the index overrides are performed.

mysql-Index Design principles