Posted on the first build predicate sentence

 
The CREATE TABLE ` demo_table ` (
` id ` bigint (20) NOT NULL AUTO_INCREMENT, 
` user_id ` bigint (20) NOT NULL DEFAULT '0', 
` status ` tinyint (1) NOT NULL DEFAULT '0', 
` reward ` int (11), NOT NULL DEFAULT '0', 
` rules ` varchar (255) NOT NULL DEFAULT ', 
` created_time ` datetime NOT NULL DEFAULT '0000-00-00 00:00:00, 
` sign_time ` datetime DEFAULT NULL, 
` replenish_time ` datetime DEFAULT NULL, 

PRIMARY KEY (` id `), 
The UNIQUE KEY ` user_created_time ` (` user_id `, ` created_time `) USING HASH, 
The UNIQUE KEY ` user_sign_time ` (` user_id `, ` sign_time `) USING BTREE, 
The KEY ` user_id ` (` user_id `) USING HASH, 
The KEY ` created_time ` (` created_time `) USING BTREE, 
The KEY ` status ` (` status `) USING BTREE, 
The KEY ` status_created_time ` (` status `, ` created_time `) USING BTREE, 
The KEY ` sign_time ` (` sign_time `) USING BTREE, 
The KEY ` status_replenish_time ` (` status `, ` replenish_time `) USING BTREE, 
The KEY ` replenish_time ` (` replenish_time `) USING BTREE, 
The KEY ` user_replenish_time ` (` user_id `, ` replenish_time `) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=66681184 DEFAULT CHARSET=utf8 

Data table
Query analysis:

 
Explain the DELETE FROM ` demo_table ` WHERE ` user_id `=884308 AND ` sign_time ` & lt; 09:36:51='2018-04-20' AND ` status `=0 

Results show that using only the status field, expected at least user_id field should be used only in line with my ideas, but may be due to scan through the status index, lead to lock a lot of records, the other user status=0 to lock, other users on the other requests to update error

I want to live a mandatory statement using user_id INDEX to solve, but I still want to find out why, by adjusting the conditions to prevent the other user record locking, don't know why decided to use the status, is there something I have to declare the FORCE INDEX?

Table structure in a short period of time is not convenient to adjust the ~

CodePudding user response:

Delete the status field index

CodePudding user response:

Haven't seen you so that all of the fields and index

CodePudding user response:

refer to the second floor rucypli response:

haven't seen you so that all of the fields and index

Has not posted there are a lot of fields, but the field is not indexed, but didn't wake up the status should not be indexed, its value is only six, table now has 50 million records, hope to delete indexes won't lock for too long

CodePudding user response:

Also why decided to use the status of ah, I want to know the reason

CodePudding user response:

KEY ` user_id ` (` user_id `) USING HASH,


Try to BTREE