“This is the 19th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

preface

Hello everyone, as a developer, in the operation of a relational database, often encounter index failure, resulting in slow query speed, query time is too long. So what are the cases that cause index invalidation? First, let’s look at indexes in MSQL.

The index

What is an index

An index is a single, physical storage structure that sorts one or more columns of a database table in a relational database. The introduction of indexes is similar to a kind of navigation directory, which can quickly find the data information to be queried. When there are indexes in the query criteria, indexes enable database applications to find data more quickly without reading the entire table. After indexes are added to a table, users cannot directly view them. They can only be used to speed up search or query. To check whether indexes are added to a table, you can query the table structure.

Although indexes improve the query speed, they also reduce the speed of updating and modifying the table. When data needs to be updated, indexes need to be updated.

The main types of indexes in MySQL are: normal index, unique index, primary key index, composite index.

A normal index is the most basic index and has no restrictions. A unique index is similar to a common index. A unique index must be unique in the columns of a unique index, which can be null. A unique index can be a single column or a combination of multiple columns. A primary key index is a table whose primary key is used as the index. When a primary key is created for a table, the primary key index is usually created. A composite index is simply an index composed of multiple database fields.

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `expiredDate` date DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `salt` varchar(255) DEFAULT NULL,
  `state` tinyint(4) DEFAULT NULL,
  `tel` varchar(255) DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4;
Copy the code

To facilitate the test index creation process, the following “user” table is used as a test table for demonstration.

Creating a normal index

In practice, we use the user name to query is more, we create a normal index based on the user name. The user name field is “Name”. The syntax structure for creating a common INDEX is as follows: CREATE INDEX INDEX name ON Table name (field information)

CREATE INDEX `index_name` ON  user(`name` )
Copy the code

After executing the above SQL, you can see that it has been successfully executed.As you can see, you can create a normal index by creating a table. You can also create an index by modifying the table structure. In Navicat and other database visualization tools, select the table to be operated, click Design table, and the following page will appear

Click on the index

The name is the index name, and the field is the field name in the table in the index data. Select the index type and index method, fill in the annotation, and click Save to create the index. This way is not very fast. Create index before savingCreate an index and save itOf course, unique and composite indexes can also be created in this way.

Create a unique index

When we design the table, sometimes some fields are unique and cannot be repeated. For example, when we register users, an email address or mobile phone number can generally register only one user. Therefore, in the design, a unique index should be added for such fields. In the program for such data verification processing. SQL > CREATE UNIQUE INDEX CREATE UNIQUE INDEX

CREATE UNIQUE INDEX `index_email` ON `user` (`email`)
Copy the code

Execute the SQL statement above and you can see that the unique index has been created successfully.If you look at the table structure, there are already two indexes, one normal and one unique.

Create composite indexes

In business development, for some scenarios, user coarse and composite indexes are required, which follow the left-most prefix set when using composite indexes. The syntax structure for creating a composite INDEX is as follows: CREATE INDEX INDEX name ON Table name (Field information 1, field information 2…)

CREATE  INDEX index_age_tel ON `user` (`age`.`tel`)
Copy the code

After executing the above SQL, the result is as follows:

Create a primary key index

A primary key index is usually created when the table is created. There is no need to create a primary key index. For example, in the table we tested above, the PRIMARY KEY (ID) id is the PRIMARY KEY index.

conclusion

Ok, the above is the INDEX of MSQL and its introduction summary, thank you for reading, I hope you like it, if it is helpful to you, welcome to like collection. If there are shortcomings, welcome comments and corrections. See you next time.

About the author: [Little Ajie] a love tinkering with the program ape, JAVA developers and enthusiasts. Public number [Java full stack architect] maintainer, welcome to pay attention to reading communication.