About MySQL Index

From "Find" to B + Tree

1. The index is used to find.
2. Binary is a great way to find. Suitable for range lookups, the inherent disadvantage is that elements are ordered. Binary search tree is a basic realization of half-fold searching.
3. But binary search tree when encountering special data, there will be "biased", affecting performance. So there is a balanced binary tree, to ensure that in all cases, binary search efficiency and stability.
4. Balanced binary tree still have the problem, that is when a large number of data tree height will rapidly increase, need to switch tree nodes many times, so it is not suitable for external memory, so there is a tree height, tree node information multi-tree-B tree.

B + Tree

How to find data using B + trees

Each index is a B + tree

When multiple fields are contained in an index, a single record block contains multiple fields. The first field is sorted by the second field, and so on.

In this case, how do I use the index? (Leftmost prefix principle)

Similarly, when a field is a string, you can treat a string as if it contains more than one character in a block of records. How do I use the index in this case? (prefix index)

Page structure

Clustered index

There are rows on the primary key index

The index value and primary key are found on the secondary index, so the index location is also searched through the primary key value to the primary index.

Index overlay Phenomenon

The value on the secondary index is sufficient to not be found on the clustered index

Does that make it possible to add all the fields you want to use on the secondary index?

Common rule Interpretation 1, the number of indexes to control:

(1) The number of indexes in a single table is not more than 5

(2) The number of fields in a single index does not exceed 5

(3) indexing strings with prefixes

(4) It is recommended to prioritize the prefix index and, if necessary, to add pseudo-columns and build indexes

2. Primary KEY criteria

(1) The table must have a primary key

(2) Do not use frequently updated columns as primary keys

(3) Try not to select a string column as the primary key

(4) Do not allow UUID MD5 hash these as primary keys

(5) By default, a non-null unique key as the primary key

(6) It is recommended to choose self-increment

3. Important SQL must be indexed, such as:

(1) Where Condition column for UPDATE, DELETE statement

(2) ORDER by, GROUP by, distinct fields

4. The fields of the multi-table join note the following:

(1) The field with the highest degree of differentiation is placed in front

(2) Nuclear? SQL overrides the overriding index

(3) Avoid redundant and duplicate indexes

(4) Index to comprehensively assess data density and distribution and consider query and update ratios

5. Index Taboo

(1) Do not index on low cardinality columns, such as "gender"

(2) Do not perform mathematical operations and function operations on indexed columns

6, try not to use foreign keys

(1) Foreign key to protect referential integrity, can be implemented on the business side

(2) Actions on parent and child tables affect each other, reducing availability

7. Index Name:

Non-unique indexes must be named in the Idx_ field 1_ Field 2, unique so must be named in the Uniq_ field 1_ Field 2, the index name must all lowercase

8. The new unique index must not be duplicated with the primary key

9. The default value of the indexed field cannot be null, and it should be changed to the other defaults or null. Null affects query efficiency of indexes very much

10. Repeatedly view the SQL associated with the table, and index the characteristics of the leftmost prefix. Multiple-field repeating statements, to modify the order of the statement condition fields, to establish a federated index for them, and to reduce the number of indexes

11, can use unique index to use unique index, improve query efficiency

12. Use explain to determine if the SQL statement uses the index reasonably, and avoid extra columns as much as possible: using File sort,using Temporary

13. SQL changes need to confirm whether the index needs to change and notify the DBA

About MySQL Index