Recently, I have been busy dealing with some SQL optimization problems left by the original old project. Due to the problems in the design of tables and fields, a large number of slow SQL appeared with the growth of business, resulting in the surge of CPU resources of MySQL. Based on this, I would like to share with you some easy to learn and use experience.

This time I will briefly talk about how to prevent your index from failing.

Say before I say first according to my recent experience under my opinion of index, I feel not so table need to set up index, for some business data, possible amount is larger, the query data have a bit of pressure, the most simple and fast way is to set up appropriate indexes, but some business may not much data table, Or if the table is used very infrequently, there is no need to index it. For example, we have some tables that are 2 years old and have maybe 10 or so pieces of data, with and without indexes in terms of performance.

Indexing is just one way we optimize our business, for the sake of indexing.

Here is a table structure that I used for this test, along with some test data

CREATE TABLE `user` (

  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,

  `create_time` datetime NOT NULL,

  `name` varchar(5) NOT NULL,

  `age` tinyint(2) unsigned zerofill NOT NULL,

  `sex` char(1) NOT NULL,

  `mobile` char(12) NOT NULL DEFAULT ' '.

  `address` char(120) DEFAULT NULL,

  `height` varchar(10) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_createtime` (`create_time`) USING BTREE,

  KEY `idx_name_age_sex` (`name`,`sex`,`age`) USING BTREE,

  KEY `idx_ height` (`height`) USING BTREE,

  KEY `idx_address` (`address`) USING BTREE,

  KEY `idx_age` (`age`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8;

Copy the code
INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (1, 'the 2019-09-02 10:17:47'.'cold', 22, 'male'.'1'.'Binxian County, Xianyang City, Shaanxi Province'.'175');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (2, 'the 2020-09-02 10:17:47'.'u', 13.'woman'.'1', NULL, '180');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (3, 'the 2020-09-02 10:17:48'.'beans', 20, 'woman'.'1', NULL, '180');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (4, 'the 2020-09-02 10:17:47'.'cold', 20, 'male'.'17765010977'.'Xi 'an, Shaanxi Province'.'155');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (255, 'the 2020-09-02 10:17:47'.'shoots', 22, 'male'.'LET me test how many Chinese words I can save', NULL, '180');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (256, 'the 2020-09-03 10:17:47'.'cold', 21, 'woman'.' ', NULL, '167');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (257, 'the 2020-09-02 10:17:47'.'little red', 20, ' '.' ', NULL, '180');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (258, 'the 2020-09-02 10:17:47'.'xiao peng', 20, ' '.' ', NULL, '188');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (259, 'the 2020-09-02 10:17:47'.'Joe', 20, ' '.' ', NULL, '180');

INSERT INTO `bingfeng`.`user`(`id`, `create_time`, `name`, `age`, `sex`, `mobile`, `address`, `height`) VALUES (260, 'the 2020-09-02 10:17:47'.'bill', 22, ' '.' ', NULL, '165');

Copy the code

A single index

1. Use! = or <> Causes the index to become invalid
SELECT * FROM `user` WHERE `name` ! ='cold';

Copy the code

We indexed the name field, but if! = or <> This will invalidate the index and result in a full table scan, so use it with caution if there is a large amount of data

You can see by analyzing the SQL that the type is ALL, 10 rows of data are scanned, and a full table scan is performed. The same goes for <>.

2. The index is invalid due to type inconsistency

Before we talk about this, we must say that when designing table fields, we must, must, must keep the field types consistent. What does that mean? User_id = user_id; user_id = user_id; user_id = user_id; user_id = user_id;

SELECT * FROM `user` WHERE height= 175;

Copy the code

The height table column type is VARCHar, but I used a numeric type to query it. There is an implicit cast in the height table, so the index is invalid and the full table scan is performed.

Select * from ‘1’ where ‘1’ = ‘1’; select * from ‘1’ where ‘1’ = ‘1’; select * from ‘1’ where ‘1’ = ‘1’;

Encounter such a table, there are tens of millions of data, change can not change, the pain may be you temporarily experience.

Boys, remember, remember.

3. Index invalid due to function
SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';

Copy the code

If your index field is indexed, sorry, it is not indexed.

4. The index is invalid due to operator
SELECT * FROM `user` WHERE age - 1 = 20;

Copy the code

If you do (+, -, *, /!) , will not be indexed.

5. The index is invalid due to OR
SELECT * FROM `user` WHERE `name` = 'Joe' OR height = '175';

Copy the code

An OR causes an index to be invalidated under certain circumstances. Not all OR’s are invalidated. If an OR joins the same field, the index will not be invalidated.

6. The index is invalid due to fuzzy search
SELECT * FROM `user` WHERE `name` LIKE Ice '%';

Copy the code

I’m sure you all know this, fuzzy search if you do fuzzy search with the prefix, you don’t go through the index.

7. NOT IN or NOT EXISTS causes the index to become invalid
SELECT s.* FROM `user` s WHERE NOT EXISTS (SELECT * FROM `user` u WHERE u.name = s.`name` AND u.`name` = 'cold')

Copy the code
SELECT * FROM `user` WHERE `name` NOT IN ('cold');

Copy the code

In both cases, the index will also be invalidated. However, “NOT IN” is NOT indexed. Do NOT mistake it for “IN”. I had a misunderstanding before. .

8. IS NULL does NOT walk the index, IS NOT NULL walks the index
SELECT * FROM `user` WHERE address IS NULL

Copy the code

No index.

SELECT * FROM `user` WHERE address IS NOT NULL;

Copy the code

The index.

In this case, it is recommended that when designing the field, if it is not required to be NULL, it is better to give the default value an empty string, which can solve a lot of problems later (and have a good experience < experience = lesson >).

Conform to the index

1. Left-most matching principle
EXPLAIN SELECT * FROM `user` WHERE sex = 'male';

Copy the code
EXPLAIN SELECT * FROM `user` WHERE name = 'cold' AND sex = 'male';

Copy the code

Delete other single-column indexes before testing.

The leftmost matching rule is that an index that matches an index is compared from left to right. For example, in the second query statement, the name is compared, and then the age is compared. In the second query statement, the name is compared, and then the age is compared.

Note:

SELECT * FROM `user` WHERE sex = 'male' AND age = 22 AND `name` = 'cold';

Copy the code

Some of you may have had a misunderstanding with me at the beginning. Our index order is clearly name, sex and age, while your current query order is sex, age and name, which is definitely not indexed. If you have not tested yourself and have such immature ideas, you are still too young like me. It doesn’t really have anything to do with order, because mysql’s base will do an optimization for you, and it will optimize your SQL to what it thinks is the most efficient way to execute it. So don’t get that wrong.

2, if used! = causes all subsequent indexes to become invalid
SELECT * FROM `user` WHERE sex = 'male'AND `name` ! ='cold' AND age = 22;

Copy the code

We used the name field! =, since the name field is the leftmost field, according to the leftmost matching principle, if the name is not indexed, the following fields will not be indexed.

In fact, I think it is important to establish an efficient index for the conformance index. Do not say that I use that field I go to create a separate index, is not it can be used globally. That’s fine, but it’s not as efficient as an index, so in order to become an advanced brick mover, we’ll continue to learn how to create an efficient index.

– END –