Mysql can read up to 5 w data of time compression within 1 s?-CodePudding

 
The CREATE TABLE ` resume_feature_simhash ` (
` resume_id ` varchar (36) NOT NULL, 
DEFAULT NULL ` real_name ` varchar (50), 
` last_name ` varchar (10) DEFAULT NULL, 
` school ` varchar (128) the DEFAULT NULL, 
DEFAULT NULL ` pinyin_name ` varchar (50), 
` real_name_sim ` bigint (11) the DEFAULT NULL 
` pinyin_name_sim ` bigint (11) the DEFAULT NULL, 
` company_sim ` bigint (11) the DEFAULT NULL, 
` major_sim ` bigint (11) the DEFAULT NULL, 
` school_sim ` bigint (11) the DEFAULT NULL, 
` title_sim ` bigint (11) the DEFAULT NULL, 
` work_start_date ` datetime (6) the DEFAULT NULL, 
` edu_start_date ` datetime (6) the DEFAULT NULL, 
` edu_end_date ` datetime (6) the DEFAULT NULL, 
` union_id ` varchar (36) DEFAULT NULL, 
` firm_id ` varchar (36) DEFAULT NULL, 
` created ` datetime DEFAULT NULL, 
` updated ` datetime DEFAULT NULL, 
PRIMARY KEY (` resume_id `), 
The KEY ` IX_resume_feature_unionId ` (` union_id `) USING BTREE, 
The KEY ` IX_resume_feature_firmId ` (` firm_id `) USING BTREE, 
The KEY ` IX_resume_feature_name ` (` real_name `) USING BTREE, 
The KEY ` IX_resume_feature_pinyin_name ` (` pinyin_name `) USING BTREE, 
The KEY ` IX_resume_feature_name_realname_unionid ` (` real_name `, ` union_id `), 
The KEY ` IX_resume_feature_realname_firmId ` (` real_name `, ` firm_id `), 
The KEY ` IX_resume_feature_pinyin_unionId ` (` pinyin_name `, ` union_id `), 
The KEY ` IX_resume_feature_pinyin_firmId ` (` pinyin_name `, ` firm_id `), 
The KEY ` IX_resume_feature_lastname_unionId ` (` last_name `, ` union_id `), 
The KEY ` IX_resume_feature_lastname_firmId ` (` last_name `, ` firm_id `), 
The KEY ` IX_resume_feature_lastname_school_unionId ` (` last_name `, ` school `, ` union_id `), 
The KEY ` IX_resume_feature_lastname_school_firmId ` (` last_name `, ` school `, ` firm_id `) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Above is built predicate sentence, there is a query from here out of the corresponding records, such as

 
SELECT 
* 
The FROM 
Resume_feature_simhash 
WHERE 
Union_id IN (
"A7e40258 a4d - 8591-4-87 a8 - f307266cd163", 
Cc7 e6b "a7eb0277-5-4-8 e91e67f8b b14-601", 
"A7eb037d - b491-401 - a - 9 ecc - b1bc060679e2", 
"E1 a7f5042f - 88-47 b0743049 b3-9308-2818", 
"A8bc022d bc7 ed81-4-81 a3 - fe0b0bfabdb6", 
"B6462e7b e7 - b8af - 70106-8962-11 fb01274", 
"B6464f70 e7 - b8af - 70106-8962-11 fb01274", 
"B6479575 e7 - b8af - 70106-8962-11 fb01274", 
"B647a889 e7 - b8af - 70106-8962-11 fb01274", 
"B64f5539 e7 - b8af - 70106-8962-11 fb01274", 
"B66494ae e7 - b8af - 70106-8962-11 fb01274", 
"B66a3ded e7 - b8af - 70106-8962-11 fb01274", 
"B66ac9da e7 - b8af - 70106-8962-11 fb01274", 
"B66bfcb0 e7 - b8af - 70106-8962-11 fb01274", 
"A8ef02fd - a6e c432-4-9526 - c5e2606d5ae2" 
) 
AND last_name='more than'

When the target data to 3 w need 3 s, almost every increase 1 w time will need to increase 1 s, queries are used to index, the following

Now the question is, is there any way to 3 w (any quantity) read into memory time compression for less than 1 s

CodePudding user response:

Refer to this, to optimize the SQL statements may a bit faster, but a large amount of data query efficiency, affected by various optimization point is more than the SQL statement:
https://blog.csdn.net/jie_liang/article/details/77340905

CodePudding user response:

Through the following methods to analyze where time is spent on,

The

reference

mysql> SELECT @ @ profiling;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +
| @ @ profiling |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | 0
+ -- -- -- -- -- -- -- -- -- -- -- -- -- +
1 row in the set (0.00 SEC)

Mysql> The SET of profiling=1;
Query OK, 0 rows affected (0.00 SEC)

Mysql> DROP TABLE IF the EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 SEC)

Mysql> The CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 SEC)

Mysql> The SHOW PROFILES;
+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +
| Query_ID | Duration | Query |
+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +
| | 0 | | 0.000088 SET PROFILING=1
| | | 0.000136 1 DROP TABLE IF the EXISTS t1 |
0.011947 | | 2 | CREATE TABLE t1 (id INT) |
+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +
3 rows in the set (0.00 SEC)

Mysql> SHOW the PROFILE;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- +
| Status | Duration |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- +
0.000040 | | checking permissions |
0.000056 | | creating table |
| After the create | | 0.011363
| | | 0.000375 query end
0.000089 | | freeing the items |
0.000019 | | logging missile query |
0.000005 | | cleaning up |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- +
7 rows in the set (0.00 SEC)

Mysql> SHOW the PROFILE FOR the QUERY 1;
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- +
| Status | Duration |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- +
| | | 0.000107 query end
0.000008 | | freeing the items |
0.000015 | | logging missile query |
0.000006 | | cleaning up |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- +
4 rows in the set (0.00 SEC)

Mysql> SHOW PROFILE CPU FOR QUERY 2;
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull