While we’ve discussed indexing in several articles, this article will begin with a brief overview of the concept of indexing. The database version is 8.0.2

What is the index?

Now we have a table in our database with 500W entries. Let’s query by name without using an index

select * from user_innodb where name='bobo'
Copy the code

The query time we saw was 2.214 seconds. Well, what if we gave this listnameWhat happens if you index a field?

ALTER TABLE `user_innodb`
ADD INDEX `idx_user_name` (`name`) USING BTREE ;
Copy the code

After the index is created successfully, run the same query again. The results are as follows

By comparison, it is found that the request with index is tens of times more effective than the case without index. From this example, it should be very intuitive that indexes can improve the performance of data retrieval. So what exactly is an index? Why can have such a big impact on our query? What happens when the index is created?

1. Index definition

Wikipedia’s definition of a database index:

A database index is a sorted data structure in a database management system (DBMS) to assist in quickly querying and updating data in a database table.

Data is stored on disk as a file, and each line of data has its disk address. Without an index, we would have to go through the entire table to retrieve one piece of data from five million rows until we found it. But once we have the index, we just need to retrieve the data in the index, because it’s a special type of data that’s used to retrieve it quicklyThe data structureAfter we find the address of the disk where the data is stored, we can get the data.

2. Index type

Three index types are supported in all MySQL

Modify the table structure by adding indexes

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

Create an index when you create a table

CREATE TABLE table ( id int(11) NOT NULL AUTO_INCREMENT , title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , time int(10) NULL DEFAULT NULL , PRIMARY KEY (id), INDEX index_name (title(length)) )

Remove the index

DROP INDEX index_name ON table

### Unique     Unique indexes require that key values be unique. It is also important to note that the primary key index is a special type of unique index and has the additional constraint that the key cannot be null. Primary Key An index is created using a primary key. SQL # CREATE UNIQUE INDEX indexName ON table(column(length)) # ALTER table table_name ADD UNIQUE indexName ON (column(length)) # CREATE TABLE 'TABLE' (' id 'int(11) NOT NULL AUTO_INCREMENT, `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL , `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), UNIQUE indexName (title(length))Copy the code

Fulltext

MySQL supports full-text indexes and full-text retrieval since version 3.23.23. FULLTEXT indexes are only available for MyISAM tables. They can be created from CHAR, VARCHAR, or TEXT columns as part of the CREATE TABLE statement, or added later using ALTER TABLE or CREATE INDEX. For larger data sets, entering your data into a table without a FULLTEXT index and then creating an index is faster than entering data into an existing FULLTEXT index. But keep in mind that for large tables, generating full-text indexes is a very time consuming and hard disk intensive practice.

Add full-text indexes to tables that are suitable for creating tablesCREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARYKEY (`id`), FULLTEXT (content) ); Alter table structure add full-text indexALTER TABLE article ADDFULLTEXT index_Content (content) # Create index directlyCREATE FULLTEXT INDEX index_content ON article(content)
Copy the code

~ Through the introduction of this article, I believe you should have a basic understanding of the index, so we will give you the next article to analyze the specific storage structure of the index!

Finally feel bobo to share the article is helpful to you, please like and follow oh ~ ^ _ ^