This is the back-end small class of the monastery. Each article is shared from

[background introduction] [knowledge analysis] [common problems] [solutions] [coding practice] [extended thinking] [more discussion] [References]

Eight aspects of in-depth analysis of back-end knowledge / skills. This article shares:

[primary key & unique]

1. Background introduction

concept

An index is a special file (the index on the InnoDB data table is a part of the table space), which contains reference pointers to all records in the data table.

The establishment of MySQL index is very important for the efficient operation of MySQL. Index can greatly improve the retrieval speed of MySQL.

If the index is not established, MySQL will query from the first row of the data table to the desired data result.

2. Knowledge analysis

Classification of index -- distinguish by single column

1). Normal index - this is the most basic index, and it has no restrictions

2). Unique index -- the value of the index column must be unique, but null values are allowed; If it is a composite index, the combination of column values must be unique

3). Primary key index -- column value is unique and non empty. A data table can only have one primary key

4). Composite index: build multiple columns in the data table into one index. MySQL composite index follows the principle of "leftmost prefix",

That is, you must select the value containing the column of the combined index in your query condition statement to use it

Change the composite index. For example, create a composite index for the three columns user, name and age in a data table,

The index can only be called if there is a where user * in the query statement.

Classification of indexes (storage structure) (InnoDB)

B-tree

B-tree data structure is adopted for primary key, unique, common and full text in MySQL database

In InnoDB, the table data file itself is an index structure organized by B + tree,

The leaf node data field of this tree keeps a complete data record. The key of this index is the primary key of the data table,

Therefore, the InnoDB table data file itself is the primary index. Inoodb must require a primary key. If no declaration is displayed, it will be added by default

In addition to the primary key index, other indexes are auxiliary indexes, and the auxiliary index will also contain primary key columns. Therefore, it is best not to set too large column values for primary key columns

Because it is a cluster index method, the order of cluster index is the physical storage order of data, and the leaf node is the data node. The primary key should preferably be monotonous.

hash index

In short, the hash index is to use a certain hash algorithm to convert the key value into a new hash value. During retrieval, it does not need to look up level by level from the root node to the leaf node like the B tree. It only needs one hash algorithm to locate the corresponding position immediately, which is very fast

1. A unique index or primary key index can be established to ensure the uniqueness of each row of data in the database table

2. Indexing can greatly improve the retrieval data and reduce the number of rows in the table

3. Under the connection condition of meter connection, the accelerometer can be directly connected with the meter

4. Data retrieval in grouping and sorting words and sentences can reduce the time consumed in grouping and sorting in query time (the records of the database will be reordered)

5. Build an index. Using an index in a query can improve performance

3. Frequently asked questions

1. Indexing can improve query efficiency. Is it feasible to build an index for each column of the data table?

1). It takes time to create and maintain indexes, which increases with the increase of the amount of data

2). The index file will occupy physical space. In addition to the physical space required by the data table, each index will also occupy a certain physical space

3). When you insert, update, and delete data in a table, the index should also be maintained dynamically, which will reduce the speed of data maintenance. (creating an index will occupy disk space. Generally, this problem is not too serious, but if you create multiple composite indexes on a large table, the index file will expand rapidly).

4. Solutions

1. On the columns that need to be searched frequently, you can speed up the index

2. The uniqueness of the primary key column can be ensured

3. Adding an index to the distinct column that often needs sorting (order by), grouping (group by) and can speed up the time of sorting query

4. Index the fields with < = > = between in after some where and like in some cases (B-tree)

5. The smaller the data type, the better, because the smaller the data type usually takes up less space in disk, memory, CPU and cache, and it is faster to process

5. Under what circumstances do you not build an index?

1. The columns rarely used in the query should not be indexed. If an index is established, it will reduce the performance of MySQL and increase the space requirements

2. Columns with little data should not be indexed, such as a gender field 0 or 1. In the query, the data in the result set accounts for a large proportion of the data rows in the table. MySQL needs to scan a large number of rows. Increasing the index does not improve the efficiency

3. When the table modification (update, insert, delete) operation is much larger than the retrieval (select) operation, the index should not be created. The two operations are mutually exclusive

Author: techmessage link: https://www.jianshu.com/p/9b4efb7506b3 Source: the copyright of Jianshu Jianshu belongs to the author. Please contact the author for authorization and indicate the source for any form of reprint.

For more information, you can join the IT communication group 565734203 to discuss and communicate with you

Here is the skill tree · it Academy: a gathering place for beginners to switch to the Internet