Farmers in yards world, beautiful application experience, from the programmer for the processing of detail, and self requirements state, agriculture in the yard a member of the young people is busy, every day, every week, can leave some footprints, is the creation of content, there is a persistent, is I don’t know why, if you lost, might as well to Chou Chou code track of farmers.

  • Beautiful musical beats take you through the coding process of this effect
  • Insist on every day, is the pursuit of every ideal youth
  • Follow in the footsteps of young people, and maybe your answer is right here
  • Take a look here if you’re confused

You have a million pieces of data, and you have to sift through 10 pieces of data, and it takes 3 seconds, and then you add an index, and then you go from seconds to milliseconds, and you know what happens?

# add indexALTER TABLE `question_wrong` ADD INDEX create_time_index ( `create_time` ) 

Copy the code

1 the index

The purpose of indexing is to improve the efficiency of data query, like the book table of contents, you can use the book table of contents to quickly locate the general location of the content.

In MySQL, indexes are implemented in the storage engine layer, so there is no unified index standard. Indexes in different storage engines work differently.

1.1 Hash table index model

Hash table data structures are suitable for equivalent-only queries.

Hash table is a structure of key-value storing data. As long as you input the key to be searched, you can obtain the corresponding value from it. The realization principle is that in an array, the key is converted into a certain position through hash function, and then the value is put in the position of the array.

After converting multiple key values into hash functions, the same value will appear. Then, the way to deal with conflicts is to pull out a linked list, as shown in the figure 152 below.

1.2 Ordered array index model

Ordered arrays are usually used for equivalent queries and ranges, and are suitable for static storage engine queries.

For example, if the phone of the user table is stored in the array in increasing order, when querying a certain phone number, it can be quickly obtained by dichotomy, and the time complexity is very small.

1.3 Binary search tree

Binary search trees are characterized by the following figure:Binary trees are the most efficient search engines, but most database storage does not use binary trees. Reason is that the index not only exists in memory, disk, for a more than 100 rows, if you are using a binary tree to store, a query might need access to 20 data block, random read a data block from mechanical drive takes about 10 ms or so, then query a need to access the 20 10 ms, It’s inefficient.

N – tree in the read and write performance advantages, as well as adaptive disk access mode, has been widely used in the database engine.

2 InnoDB storage engine index model

InnoDB uses the B+ tree index model, so all data is stored in the B+ tree, where tables are stored in the form of indexes according to the order of primary keys. Tables stored in this way are called indexed organized tables.

Each index in InnoDB corresponds to a B+ tree.

For example, when writing a text, there are instructions

# add indexALTER TABLE `question_wrong` ADD INDEX create_time_index ( `create_time` ) 

Copy the code

The question_wrong table has the primary key ID and adds an index to create_time, making the distinction between primary and non-primary key indexes.

Leaf nodes of primary key indexes, also known as clustered indexes in InnoDB, store entire rows of data.

The leaf contents of a non-primary key index are the values of the primary key. In InnoDB, non-primary key indexes are also called secondary indexes.

For example, execute a query statement

select * from question_wrong where ID=100
Copy the code

In primary key query mode, only the B+ tree ID needs to be searched

select * from question_wrong where create_time='2021-03-05 00:00:00'
Copy the code

In common index query mode, the create_time index tree is searched first to obtain the corresponding ID value, and then the ID index tree is searched again (this process is called table back).

A query based on a non-primary key index requires scanning one more index tree. Therefore, primary key query is recommended in practical applications.

3 Index Maintenance

When inserting new values, the B+ tree also needs to be updated to maintain index order. For example, the current primary key index B+ tree is maintained to the ID of 100. When inserting new data with the ID of 101, you only need to insert a new record after the primary key index B+ tree record. You have to logically move the data behind to make room.

If the current primary key index B+ tree is full, it needs to apply for a new data page and move some data to it. This process is called page splitting.

4 Add the primary key automatically

In general, a table construction clause must contain an AUTO_INCREMENT PRIMARY KEY. An AUTO_INCREMENT PRIMARY KEY refers to a PRIMARY KEY defined on an AUTO_INCREMENT column. In general, a table construction clause defines NOT NULL PRIMARY KEY AUTO_INCREMENT.

Create a table with an autoincrement primary key (ID).

CREATE TABLE `t_course_teacher`(
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
	`user_id` BIGINT(20) DEFAULT '0' COMMENT 'users; ' ,
	`course_id` BIGINT(20) DEFAULT '0' COMMENT 'course' ,
	`course_status` INT DEFAULT '0' COMMENT '0 is available by default; 1 disabled; 2 remove; ' ,
	`create_time` DATETIME DEFAULT NULL COMMENT 'Course Creation Time' ,
	`update_time` DATETIME DEFAULT NULL COMMENT 'Course Revision Time' ,
	PRIMARY KEY(`id`)
) ENGINE = INNODB AUTO_INCREMENT = 39 DEFAULT CHARSET = utf8 COMMENT = 'User schedule';

Copy the code

When the auto-increment primary key ID is specified, the ID value can not be specified when inserting a new record. The system will obtain the maximum value of the current ID plus 1 as the ID value of the next record. Each insertion of a new record is an append operation, which does not involve moving other records and will not trigger the splitting of leaf nodes.

The smaller the primary key length, the smaller the leaf nodes of a normal index, and the smaller the space taken up by a normal index. Therefore, auto-increment primary keys are often a good choice for performance and storage.

The completion of

Not limited to thinking, not limited to language restrictions, is the highest realm of programming.

With xiaobian character, must be to record a set of video, and then upload

If you are interested, you can check out the watermelon video – early risers