mysql讀取5w以內資料的時間能否壓縮到1s以內？-有解無憂



CREATE TABLE `resume_feature_simhash` (

  `resume_id` varchar(36) NOT NULL,

  `real_name` varchar(50) DEFAULT NULL,

  `last_name` varchar(10) DEFAULT NULL ,

  `school` varchar(128) DEFAULT NULL,

  `pinyin_name` varchar(50) DEFAULT NULL,

  `real_name_sim` bigint(11) DEFAULT NULL

  `pinyin_name_sim` bigint(11) DEFAULT NULL,

  `company_sim` bigint(11) DEFAULT NULL,

  `major_sim` bigint(11) DEFAULT NULL,

  `school_sim` bigint(11) DEFAULT NULL,

  `title_sim` bigint(11) DEFAULT NULL,

  `work_start_date` datetime(6) DEFAULT NULL,

  `edu_start_date` datetime(6) DEFAULT NULL,

  `edu_end_date` datetime(6) 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`),

  KEY `IX_resume_feature_unionId` (`union_id`) USING BTREE,

  KEY `IX_resume_feature_firmId` (`firm_id`) USING BTREE,

  KEY `IX_resume_feature_name` (`real_name`) USING BTREE,

  KEY `IX_resume_feature_pinyin_name` (`pinyin_name`) USING BTREE,

  KEY `IX_resume_feature_name_realname_unionid` (`real_name`,`union_id`),

  KEY `IX_resume_feature_realname_firmId` (`real_name`,`firm_id`),

  KEY `IX_resume_feature_pinyin_unionId` (`pinyin_name`,`union_id`),

  KEY `IX_resume_feature_pinyin_firmId` (`pinyin_name`,`firm_id`),

  KEY `IX_resume_feature_lastname_unionId` (`last_name`,`union_id`),

  KEY `IX_resume_feature_lastname_firmId` (`last_name`,`firm_id`),

  KEY `IX_resume_feature_lastname_school_unionId` (`last_name`,`school`,`union_id`),

  KEY `IX_resume_feature_lastname_school_firmId` (`last_name`,`school`,`firm_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

以上是建表陳述句，有個查詢是從這里拿出相應的記錄，如



SELECT

	*

FROM

	resume_feature_simhash

WHERE

	union_id IN (

		"a7e40258-8591-4a4d-87a8-f307266cd163",

		"a7eb0277-5e6b-4cc7-8b14-601e91e67f8b",

		"a7eb037d-b491-401a-9ecc-b1bc060679e2",

		"a7f5042f-88e1-47b3-9308-2818b0743049",

		"a8bc022d-ed81-4bc7-81a3-fe0b0bfabdb6",

		"b6462e7b-8962-11e7-b8af-70106fb01274",

		"b6464f70-8962-11e7-b8af-70106fb01274",

		"b6479575-8962-11e7-b8af-70106fb01274",

		"b647a889-8962-11e7-b8af-70106fb01274",

		"b64f5539-8962-11e7-b8af-70106fb01274",

		"b66494ae-8962-11e7-b8af-70106fb01274",

		"b66a3ded-8962-11e7-b8af-70106fb01274",

		"b66ac9da-8962-11e7-b8af-70106fb01274",

		"b66bfcb0-8962-11e7-b8af-70106fb01274",

		"a8ef02fd-c432-4a6e-9526-c5e2606d5ae2"

	)

AND last_name = '余'

當目標資料達到3w的時候需要3s，幾乎每增加1w時間就需要增加1s。查詢都用到了索引，如下

現在問題是，有沒有什么辦法能將3w(隨便一個量)讀到記憶體的時間壓縮為1s以內

uj5u.com熱心網友回復：

參考下這個，優化一下sql陳述句也許能快一點，但是大量資料的查詢效率，受多方面影響，優化的點也不止sql陳述句：
https://blog.csdn.net/jie_liang/article/details/77340905

uj5u.com熱心網友回復：

通過下面方法分析時間都花在哪兒了。

參考

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

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT);
Query OK, 0 rows affected (0.01 sec)

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

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

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

mysql> SHOW PROFILE CPU FOR QUERY 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000038 |   0.000002 |
| creating table       | 0.000056 | 0.000028 |   0.000028 |
| After create         | 0.011363 | 0.000217 |   0.001571 |
| query end            | 0.000375 | 0.000013 |   0.000028 |
| freeing items        | 0.000089 | 0.000010 |   0.000014 |
| logging slow query   | 0.000019 | 0.000009 |   0.000010 |
| cleaning up          | 0.000005 | 0.000003 |   0.000002 |
+----------------------+----------+----------+------------+
7 rows in set (0.00 sec)

uj5u.com熱心網友回復：

試試這個
SELECT * FROM resume_feature_simhash WHERE union_id = 'a7e40258-8591-4a4d-87a8-f307266cd163' AND last_name = '余'
UNION
SELECT * FROM resume_feature_simhash WHERE union_id = 'a7eb0277-5e6b-4cc7-8b14-601e91e67f8b' AND last_name = '余'
UNION
SELECT * FROM resume_feature_simhash WHERE union_id = 'a7eb037d-b491-401a-9ecc-b1bc060679e2' AND last_name = '余'
……

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/80880.html

標籤：MySQL

上一篇：分庫分表——已有表如何分？

下一篇：mysql關于case when的疑慮