Indexes:

It is a data structure that helps MySQL obtain data efficiently;
It can be understood as a sorted fast lookup data structure.

Basic commands for indexing:

reference resources

Suitable for index creation

The primary key automatically creates a unique index;
Fields frequently used as query criteria should be indexed
The fields associated with other tables in the query are indexed by foreign key relationships
Single key / composite index selection, composite index is more cost-effective
For the sorted fields in the query, if the sorted fields are accessed through the index, the sorting speed will be greatly improved
Statistics or grouping fields in query

Not suitable for index creation

Too few table records
Frequently added, deleted or modified tables or fields
No index is created for fields that are not used in the Where condition
Those with poor filtering are not suitable for indexing

Index optimization case

Single table analysis

Create table:

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');
 

Query table data:

SELECT * FROM ARTICLE;

Query category_ When ID is 1 and comments is greater than or equal to 1, the articles with the most views_ id.

select id,author_id from article where category_id = 1 and comments >= 1 order by views desc limit 1;

Optimize the index using the explain keyword:

explain select id,author_id from article where category_id = 1 and comments >= 1 order by views desc limit 1;

The yellow arrow indicates the part to be optimized
key is NULL, query has no index, and type is ALL worst case (system > const > qe_ref > ref > range > index > ALL);
Rows: the number of rows checked is 3, and all data is checked;
Extra: sorting in Using filesort file appears;
Must be optimized!

View table index:

show index form article;

Create index: (the index is generally the field after the where condition)

ALTER TABLE article ADD INDEX idx_article_ccv(category_id,comments,views);

View index:

show index from article;

Execute the explain keyword again to optimize the index:

explain select id,author_id from article where category_id = 1 and comments >= 1 order by views desc limit 1;

type becomes range and rows becomes 1, which is optimized; However, Extra still contains sorting in Using filesort files, which still needs to be optimized.
Using filesort: indicates that the index is invalid, mainly because the comments > = 1 range condition statement makes the subsequent view part unable to use the index sorting, resulting in index invalidation.
Change the comments > = 1 condition to comments = 1 to view the index optimization.

explain select id,author_id from article where category_id = 1 and comments = 1 order by views desc limit 1;

The type is optimized to ref, and the Using filesort in Extra disappears.
The index is invalid mainly because comments > = 1 range condition statement makes the subsequent view part unable to sort by index, resulting in index invalidation.

Continue to optimize the index: (re create the index and delete the comments field in the index)
Delete index:

DROP INDEX idx_article_ccv ON article;

Recreate index:

create index idx_article_cv on article(category_id,views);

View index:

show index from article;

Execute the explain keyword again to optimize the index:

explain select id,author_id from article where category_id = 1 and comments >= 1 order by views desc limit 1;

The type is optimized to ref, and the Using filesort in Extra disappears.