We all know that in MySql using the index can improve the efficiency of the query, but sometimes really execute Sql queries without using the index, according to our expected but a full table scan, leading to a slow Sql would influence the efficiency of the entire site, even causing the site to crash, so we need to know how to choose MySql is using the index of, To create appropriate indexes (this article is based on MySql5.7, InnoDB engine)

Prerequisite: Create a test list

Suppose you have a table of users

CREATE TABLE `test_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' '.`birthday` date NOT NULL.`sex` tinyint(1) DEFAULT NULL.`height` int(11) NOT NULL.`weight` int(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_name_height_weight` (`name`.`height`.`weight`),
  KEY `idx_height` (`height`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

We create a joint index of name,height, and weight, and insert the test data

INSERT INTO `test_user` (`user_id`.`name`.`birthday`.`sex`.`height`.`weight`)
VALUES
	(1.'Tony'.'1991-01-01'.1.176.65),
	(2.'Mary'.'1989-12-19'.2.160.50),
	(3.'Tom'.'1996-05-29'.1.180.70),
	(4.'Kiven'.'1994-08-09'.1.190.80),
	(5.'John'.'1992-11-12'.1.182.75);
Copy the code

What operations does Mysql use indexes for

Find the row that matches the WHERE clause

select * from test_user where name='mary'
Copy the code

Viewing the Execution Plan

Deletes a row from a table

    DELETE from test_user where name='mary';
Copy the code

Viewing the Execution Plan

Find the value of MIN() or MAX() for the indexed column

    SELECT MIN(height)  FROM `test_user`
Copy the code

    SELECT MIN(height)  from `test_user` where height>=176 AND height<=190;
Copy the code

    SELECT MIN(height)  from test_user where sex=1;
Copy the code

Perform SORT or ORDER BY operations on index columns

    SELECT name,height from test_user ORDER BY `name` DESC;
Copy the code

This article explains when MySql can use indexes. The next chapter explains when MySql can’t use indexes