1. Index operations

MySQL > alter database

2. Index type

  • PRIMARY Unique and cannot be null; A table can have only one primary key index
  • INDEX Common INDEX
  • UNIQUE UNIQUE index
  • FULLTEXT full-text index: Works best when searching for long articles. For short text, if only one or two lines, plain INDEX can be used

3. Clustered indexes vs. non-clustered indexes

3.1 the difference between

* Clustered index: primary key index, the logical order of the key values in the index determines the physical order of the corresponding rows in the table. * Non-clustered index (non-primary key index, also known as secondary index) : Except for primary key index (normal index, unique index, full text index), the logical order of the indexes is different from the physical storage order of the diskCopy the code

Query process:

  • Query the clustered index to get all the data directly,
  • Searching a non-clustered index requires obtaining the clustered index address first, and then retrieving the data from the table.

3.1 Clustered index rules

  1. If a primary key is defined, it is used as a clustered index
  2. If no primary key is defined, the first unique non-empty index of the table is used as the clustered index
  3. If there is no primary key and no suitable unique index, innoDB internally generates a hidden primary key as the clustered index. The hidden primary key is a 6-byte column whose value increases as data is inserted.

4 Index Structure

Default B+Tree, Hash(key-value insertion and query, Hash table time complexity is O(1), if you do not need to order traversal data, Hash table performance is the best.)

B+ tree m – order tree evolved from binary tree

Why B+ tree? (With the read/write feature of the disk, the number of disk accesses in a single query is reduced.)

4.1 B+ Tree characteristics

———— Geek time data structures and the beauty of algorithms

  • The number of neutron nodes in each node cannot be more than m or less than M /2.
  • The root node can have no more than m/2 children, which is an exception;
  • M-tree only stores indexes, it doesn’t really store data, it’s kind of like a hop table;
  • Leaf nodes are connected in series through a linked list, which makes it easy to search by interval;
  • Typically, the root node is stored in memory and the other nodes are stored on disk.

The complexity of the

  • Time complexity of all operations (query, insert, delete) O(logm(N)),
  • Worst space complexity O(n)

4.2 How do I compute order m?

The operating system reads by page (default is 4K or 8K). To improve I/O efficiency, an index page is consistent with the operating system read space.

M = data page size/index entry sizeCopy the code

Therefore, the smaller the space occupied by the index entry field (int 4byte, less than half of bigINT 8byte), the more index data can be stored on a page, and the length of the index field should also be considered in optimization.

The child node is a bidirectional linked list structure, convenient range query and sorting.

Consider: 10 million data, how tall is the tree?

5. Overwrite indexes

selectA primary keyfrom table wherePlain index field = **;Copy the code

Overwrite index concept: inserts directly into the result through the index without the need for back table operations.

Example: ID number and name

If you want to query information according to id number, as long as the id card index is established, do you need to build [ID card, name] combination index?

If there is a high frequency query of id number query name, the above combination index is established, so that the covered index can be achieved, and there is no need to look up the whole row of data back to the table, reducing the execution time.

6. Left-most prefix rule

Two concepts:

  • The leftmost prefix can be the leftmost N field of the composite index
  • It can also be the left-most M character of the string index.

The establishment of composite index (A, B, C) is equivalent to the establishment of four indexes (A, B, C) (a, B) (a, C) (a)

Indexes can be used as long as they match the leftmost N fields. [a,b,c] [a,c] [a,b] [a] can trigger index, internal order variable, mysql automatic adjustment.

String index left M characters: like x% OK, %x, %x% no.

7. Index push down

MySQL 5.6 index condition pushdown

During index traversal, you can judge the fields in the index first and directly filter out the records that do not meet the conditions to reduce the number of table returns.

8. Whether the index takes effect is optimized

You can use EXPLAIN to analyze whether an index is working. Slow SQL does some index optimization and EXPLAIN optimization query detection

  • When the index field is of type int, the condition can be wrapped in “or can be directly compared by value

  • When the index field is of type VARCHAR, the condition is wrapped in ‘ ‘

  • Trigger range index >,<, not in, in! =,BETWEEN AND (after 5.5)

9. Common index naming conventions

Unique uk_[field name]_[field name]... Common idX_ [field name]_[field name]...Copy the code

github blog