问题：
相同的执行路径、数据和示意图；不同查询时间
厉坚
继续原问题：为了测试这一点，我制作了一个表的近似副本。尽管执行路径相同、数据相同、表示意图非常相似，但对复制表的性能（查询时间）明显比源表差。
在本例中，我们有以下3个表：
>
提供_clicks
| offers | CREATE TABLE `offers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(4) NOT NULL DEFAULT '0',
`user_id` int(11) NOT NULL DEFAULT '0',
.....many_other_fields.....
KEY `ix_public_view_key` (`public_view_key`),
FULLTEXT KEY `name` (`name`,`internal_name`)
) ENGINE=InnoDB AUTO_INCREMENT=18425582 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
| offers_new | CREATE TABLE `offers_new` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18423831 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
插入offers_new(id,status）选择id,status FROM offers；
在这之后，我们开始运行一些测试查询。正如您所看到的，对“offers_new”表的查询时间大约是“offers”表的10倍。
mysql> SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (26.04 sec)
mysql> SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (2.90 sec)
mysql> SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (28.07 sec)
mysql> SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----------+
| COUNT(*) |
+----------+
| 15472 |
+----------+
1 row in set (2.26 sec)
请注意，这两个查询的执行路径完全相同：
mysql> explain SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+
| 1 | SIMPLE | offers_clicks | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | range | pts_id,date,date_2 | date | 5 | NULL | 15472 | 100.00 | Using index condition |
| 1 | SIMPLE | offers_new | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dejong_pointstoshop.offers_clicks.offer_id | 1 | 100.00 | Using index |
+----+
2 rows in set, 1 warning (0.00 sec)
mysql> explain SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00';
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
| 1 | SIMPLE | offers_clicks | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | range | pts_id,date,date_2 | date | 5 | NULL | 15472 | 100.00 | Using index condition |
| 1 | SIMPLE | offers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dejong_pointstoshop.offers_clicks.offer_id | 1 | 100.00 | Using index |
+----+-------------+---------------+-----------------------------------------------------------------------+--------+--------------------+---------+---------+--------------------------------------------+-------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
-
null
-
null
mysql> SELECT COUNT(*) FROM offers_new;
+----------+
| COUNT(*) |
+----------+
| 5093127 |
+----------+
1 row in set (0.13 sec)
mysql> SELECT COUNT(*) FROM offers;
+----------+
| COUNT(*) |
+----------+
| 5107742 |
+----------+
1 row in set (2.54 sec)
计数非常接近。Offers是一个活动表，所以自从我昨天的测试以来，已经向表中添加了新的条目。不过，计数仍然接近。有趣的是，“offers_new”的计数(*)明显比“offers”快，因此与我们提出这个问题的查询结果相反！“offers_new”的计数约为0.1/0.2秒，“offers”表的计数为几秒（范围在2-6秒之间）。我每次运行大约10次，以确保它不仅仅是服务器负载，而且它一直都很慢。我假设这与“offers”表的列数要高得多有关。不管怎样，看到另一个查询的相反结果都很有趣...
下面是分析：
mysql> SHOW PROFILES;
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+---+
| 1 | 26.03997750 | SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
| 2 | 2.89890600 | SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
| 3 | 28.07228225 | SELECT COUNT(*) FROM offers_clicks, offers_new WHERE offers_new.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
| 4 | 2.25160675 | SELECT COUNT(*) FROM offers_clicks, offers WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date > '2019-05-30 00:00:00' and offers_clicks.date < '2019-05-30 01:00:00' |
+----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE ALL FOR QUERY 1;
+---+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+---+
| starting | 0.000364 | 0.016000 | 0.000000 | 3 | 3 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | NULL | NULL | NULL |
| Executing hook on transaction | 0.000134 | 0.008000 | 0.000000 | 6 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1100 |
| starting | 0.000128 | 0.008000 | 0.000000 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1102 |
| checking permissions | 0.000173 | 0.008000 | 0.000000 | 3 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 1899 |
| checking permissions | 0.000182 | 0.012000 | 0.000000 | 65 | 33 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 1899 |
| Opening tables | 0.003432 | 0.176000 | 0.012000 | 556 | 224 | 0 | 0 | 0 | 0 | 0 | 20 | 0 | open_tables | sql_base.cc | 5586 |
| init | 0.000235 | 0.012000 | 0.000000 | 25 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 555 |
| System lock | 0.000151 | 0.008000 | 0.000000 | 62 | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 332 |
| optimizing | 0.000171 | 0.008000 | 0.000000 | 32 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 212 |
| statistics | 0.001255 | 0.068000 | 0.000000 | 230 | 125 | 0 | 24 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 425 |
| preparing | 0.000166 | 0.012000 | 0.000000 | 43 | 19 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | optimize | sql_optimizer.cc | 499 |
| executing | 0.000134 | 0.004000 | 0.000000 | 27 | 16 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | exec | sql_executor.cc | 197 |
| Sending data | 26.032492 | 999.999999 | 67.940000 | 5361975 | 1420548 | 227416 | 1584568 | 0 | 0 | 0 | 299459 | 0 | exec | sql_executor.cc | 273 |
| end | 0.000325 | 0.012000 | 0.000000 | 7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 608 |
| query end | 0.000115 | 0.004000 | 0.000000 | 11 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4581 |
| waiting for handler commit | 0.000118 | 0.008000 | 0.000000 | 34 | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1533 |
| closing tables | 0.000118 | 0.004000 | 0.000000 | 23 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4627 |
| freeing items | 0.000163 | 0.008000 | 0.000000 | 4 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5256 |
| cleaning up | 0.000125 | 0.008000 | 0.000000 | 5 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2108 |
+---+
19 rows in set, 1 warning (0.00 sec)
mysql> SHOW PROFILE ALL FOR QUERY 2;
+---+
| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
+---+
| starting | 0.000364 | 0.012000 | 0.000000 | 41 | 24 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
| Executing hook on transaction | 0.000137 | 0.008000 | 0.000000 | 40 | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1100 |
| starting | 0.000135 | 0.004000 | 0.000000 | 21 | 6 | 0 | 16 | 0 | 0 | 0 | 2 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1102 |
| checking permissions | 0.000124 | 0.008000 | 0.000000 | 26 | 7 | 0 | 16 | 0 | 0 | 0 | 2 | 0 | check_access | sql_authorization.cc | 1899 |
| checking permissions | 0.000139 | 0.008000 | 0.000000 | 19 | 9 | 32 | 24 | 0 | 0 | 0 | 4 | 0 | check_access | sql_authorization.cc | 1899 |
| Opening tables | 0.000152 | 0.004000 | 0.000000 | 30 | 14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5586 |
| init | 0.000125 | 0.004000 | 0.008000 | 25 | 19 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | execute | sql_select.cc | 555 |
| System lock | 0.000237 | 0.004000 | 0.004000 | 26 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 332 |
| optimizing | 0.000150 | 0.008000 | 0.000000 | 24 | 7 | 0 | 0 | 0 | 0 | 0 | 4 | 0 | optimize | sql_optimizer.cc | 212 |
| statistics | 0.001082 | 0.048000 | 0.004000 | 192 | 59 | 0 | 0 | 0 | 0 | 0 | 17 | 0 | optimize | sql_optimizer.cc | 425 |
| preparing | 0.000162 | 0.008000 | 0.000000 | 19 | 22 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 499 |
| executing | 0.000136 | 0.008000 | 0.000000 | 33 | 32 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 197 |
| Sending data | 2.894895 | 120.524000 | 6.512000 | 551014 | 158606 | 43632 | 125120 | 0 | 0 | 0 | 34154 | 0 | exec | sql_executor.cc | 273 |
| end | 0.000359 | 0.012000 | 0.000000 | 28 | 27 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 608 |
| query end | 0.000130 | 0.004000 | 0.004000 | 51 | 33 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4581 |
| waiting for handler commit | 0.000135 | 0.004000 | 0.000000 | 58 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1533 |
| closing tables | 0.000144 | 0.008000 | 0.000000 | 27 | 15 | 0 | 16 | 0 | 0 | 0 | 2 | 0 | mysql_execute_command | sql_parse.cc | 4627 |
| freeing items | 0.000165 | 0.008000 | 0.000000 | 23 | 4 | 0 | 8 | 0 | 0 | 0 | 3 | 0 | mysql_parse | sql_parse.cc | 5256 |
| cleaning up | 0.000137 | 0.008000 | 0.000000 | 6 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2108 |
+---+
19 rows in set, 1 warning (0.00 sec)
mysql> show create table offers \G
*************************** 1. row ***************************
Table: offers
Create Table: CREATE TABLE `offers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` tinyint(4) NOT NULL DEFAULT '0',
`field_a` int(11) NOT NULL DEFAULT '0',
`field_b` text COLLATE utf8_unicode_ci NOT NULL,
`field_c` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`field_d` text COLLATE utf8_unicode_ci NOT NULL,
`field_e` text COLLATE utf8_unicode_ci NOT NULL,
`field_f` text COLLATE utf8_unicode_ci,
`field_g` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
`field_h` text COLLATE utf8_unicode_ci,
`field_i` mediumint(9) NOT NULL DEFAULT '0',
`field_j` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`field_k` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`field_l` decimal(10,4) NOT NULL,
`field_m` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_n` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_o` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_p` decimal(5,2) NOT NULL DEFAULT '0.00',
`field_q` text COLLATE utf8_unicode_ci NOT NULL,
`field_r` text COLLATE utf8_unicode_ci NOT NULL,
`field_s` int(11) NOT NULL,
`field_t` mediumint(9) NOT NULL DEFAULT '0',
`field_u` tinyint(4) NOT NULL DEFAULT '0',
`field_v` tinyint(4) NOT NULL DEFAULT '0',
`field_w` tinyint(4) NOT NULL DEFAULT '0',
`field_x` tinyint(4) NOT NULL,
`field_y` tinyint(4) DEFAULT NULL,
`field_z` tinyint(4) NOT NULL DEFAULT '0',
`field_aa` tinyint(1) NOT NULL DEFAULT '0',
`field_ab` tinyint(1) NOT NULL,
`field_ac` tinyint(4) NOT NULL,
`field_ad` tinyint(1) NOT NULL DEFAULT '0',
`field_ae` tinyint(1) NOT NULL,
`field_af` int(10) unsigned DEFAULT '0',
`field_ag` int(10) unsigned NOT NULL,
`field_ah` int(10) unsigned NOT NULL,
`field_ai` int(11) NOT NULL,
`field_aj` tinyint(1) NOT NULL DEFAULT '0',
`field_ak` decimal(6,3) DEFAULT '0.000',
`field_al` tinyint(1) NOT NULL,
`field_am` decimal(8,3) NOT NULL,
`field_an` decimal(8,3) NOT NULL,
`field_ao` decimal(5,2) NOT NULL,
`field_ap` decimal(8,3) NOT NULL,
`field_aq` tinyint(3) unsigned DEFAULT NULL,
`field_ar` int(11) NOT NULL,
`field_as` mediumint(9) NOT NULL DEFAULT '0',
`field_at` mediumint(9) NOT NULL,
`field_au` mediumint(9) NOT NULL,
`field_av` mediumint(9) NOT NULL,
`field_aw` mediumint(9) NOT NULL,
`field_ax` mediumint(9) NOT NULL DEFAULT '8388607',
`field_ay` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`field_az` tinyint(1) NOT NULL DEFAULT '0',
`field_ba` smallint(5) unsigned DEFAULT NULL,
`field_bb` tinyint(1) NOT NULL DEFAULT '0',
`field_bc` tinyint(1) NOT NULL DEFAULT '0',
`field_bd` tinyint(1) NOT NULL DEFAULT '1',
`field_be` tinyint(1) NOT NULL,
`field_bf` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`field_bg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`field_bh` tinyint(1) NOT NULL DEFAULT '0',
`field_bi` text COLLATE utf8_unicode_ci NOT NULL,
`field_bj` tinyint(1) NOT NULL DEFAULT '0',
`field_bk` date DEFAULT NULL,
`field_bl` date DEFAULT NULL,
`field_bm` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_bn` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_bo` tinyint(1) NOT NULL DEFAULT '0',
`field_bp` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`field_bq` smallint(5) unsigned DEFAULT NULL,
`field_br` tinyint(3) unsigned NOT NULL DEFAULT '0',
`field_bs` decimal(10,6) DEFAULT NULL,
`field_bt` decimal(10,6) DEFAULT NULL,
`field_bu` tinyint(1) NOT NULL DEFAULT '0',
`field_bv` tinyint(1) NOT NULL DEFAULT '0',
`field_bw` tinyint(1) NOT NULL DEFAULT '0',
`field_bx` tinyint(1) NOT NULL DEFAULT '0',
`field_by` tinyint(1) NOT NULL DEFAULT '0',
`field_bz` datetime DEFAULT NULL,
`field_ca` datetime DEFAULT NULL,
`field_cb` datetime DEFAULT NULL,
`field_cc` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`field_cd` datetime DEFAULT NULL,
`field_ce` varchar(155) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_cf` int(11) DEFAULT NULL,
`field_cg` decimal(10,4) NOT NULL DEFAULT '0.0000',
`field_ch` tinyint(1) NOT NULL DEFAULT '0',
`field_ci` decimal(5,4) NOT NULL DEFAULT '0.0000',
`field_cj` mediumint(9) NOT NULL DEFAULT '0',
`field_ck` datetime DEFAULT NULL,
`field_cl` tinyint(1) NOT NULL DEFAULT '0',
`field_cm` datetime DEFAULT NULL,
`field_cn` smallint(5) unsigned NOT NULL DEFAULT '0',
`field_co` smallint(5) unsigned NOT NULL DEFAULT '0',
`field_cp` tinyint(1) NOT NULL DEFAULT '0',
`field_cq` tinyint(1) NOT NULL DEFAULT '0',
`field_cr` tinyint(1) NOT NULL DEFAULT '0',
`field_cs` tinyint(1) NOT NULL DEFAULT '0',
`field_ct` tinyint(1) NOT NULL DEFAULT '0',
`field_cu` tinyint(1) NOT NULL DEFAULT '0',
`field_cv` tinyint(1) NOT NULL DEFAULT '0',
`field_cw` tinyint(1) NOT NULL DEFAULT '0',
`field_cx` decimal(5,2) NOT NULL,
`field_cy` tinyint(1) NOT NULL DEFAULT '0',
`field_cz` tinyint(1) NOT NULL DEFAULT '0',
`field_da` int(11) NOT NULL DEFAULT '0',
`field_db` tinyint(1) NOT NULL DEFAULT '0',
`field_dc` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`field_dd` tinyint(3) unsigned DEFAULT NULL,
`field_de` tinyint(1) DEFAULT '0',
`field_df` datetime DEFAULT NULL,
`field_dg` tinyint(1) NOT NULL DEFAULT '0',
`field_dh` tinyint(1) NOT NULL DEFAULT '0',
`field_di` bigint(20) DEFAULT NULL,
`field_dj` datetime DEFAULT NULL,
`field_dk` tinyint(1) NOT NULL DEFAULT '0',
`field_dl` int(1) DEFAULT NULL,
`field_dm` tinyint(1) NOT NULL DEFAULT '0',
`field_dn` int(11) NOT NULL DEFAULT '0',
`field_do` tinyint(1) NOT NULL DEFAULT '0',
`field_dp` tinyint(3) unsigned NOT NULL DEFAULT '0',
`field_dq` int(11) NOT NULL DEFAULT '0',
`field_dr` json DEFAULT NULL,
`field_ds` int(11) NOT NULL DEFAULT '0',
`field_dt` datetime DEFAULT NULL,
`field_du` tinyint(1) NOT NULL DEFAULT '0',
`field_dv` json NOT NULL,
`field_dw` decimal(8,5) DEFAULT '0.00000',
`field_dx` tinyint(1) NOT NULL DEFAULT '0',
`field_dy` smallint(6) NOT NULL DEFAULT '14',
PRIMARY KEY (`id`),
UNIQUE KEY `field_i_field_j` (`field_i`,`field_j`),
UNIQUE KEY `field_j_field_i` (`field_k`,`field_i`),
KEY `status_field_bf` (`status`,`field_bf`),
KEY `field_bm` (`field_bm`),
KEY `status_field_i_field_bo` (`status`,`field_i`,`field_bo`),
KEY `field_j` (`field_j`),
KEY `field_bz` (`field_bz`),
KEY `field_br` (`field_br`),
KEY `field_bh` (`field_bh`),
KEY `field_ba` (`field_ba`),
KEY `field_cm` (`status`,`field_cm`),
KEY `field_cc` (`field_cc`),
KEY `field_i_field_cc` (`field_i`,`field_cc`),
KEY `field_g` (`field_g`),
FULLTEXT KEY `field_c` (`field_c`,`field_ce`)
) ENGINE=InnoDB AUTO_INCREMENT=18425582 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
共有1个答案
武彭薄
猜测（等待评论中要求的更多信息）...
INNODB_BUFFER_POOL_SIZE小于data_lengte（请参见显示表大小提供)，并且15k行分散在表周围。这可能导致击中磁盘，而不是在缓存(buffer_pool)中找到所需的ID.- 这两对运行都证明了大约10：1的比例，这是我在比较访问磁盘的查询和在缓存中运行的查询时多次看到的结果。
部分解决方案可能是增加buffer_pool。然而，由于您使用的是云服务，实现这一增长的唯一方法可能是为更大的RAM付费。
FROM offers_clicks, offers
WHERE offers.id = offers_clicks.offer_id AND offers_clicks.date
FROM offers_clicks
JOIN offers ON offers.id = offers_clicks.offer_id -- how the tables relate
WHERE offers_clicks.date ...
还有...这只是3599秒，不是一个完整的小时。会议将包括第一个午夜。
date > '2019-05-30 00:00:00'
and date < '2019-05-30 01:00:00'
我更喜欢这种风格，因为它避免了闰年问题，等等：
date >= '2019-05-30 00:00:00'
and date < '2019-05-30 00:00:00' + INTERVAL 1 HOUR
页面错误。..
类似资料：
-
问题内容： SQL2008。 我有一个测试表： 我用10k测试行填充它。 我运行以下两个查询： 我不知道为什么这两个查询有不同的执行计划。 查询1确实针对UQ_Sale_RowVersion索引进行索引搜索。 查询2对PK_Sale进行索引扫描。 我想查询2做索引查找。 我将不胜感激。 谢谢你。 [编辑] 尝试使用datetime2而不是rowversion。同样的问题。 我也尝试强制使用索引（查
-
问题内容： 我正在尝试调整SQL查询以检查服务器上每个数据库中存在的特定字段中存在的值。 有100个单独的数据库，我想检查每个数据库的特定记录。 答案可能是使用下面的命令，但是我很难适应它。 我在下面的链接上获得了更大的成功； https://stackoverflow.com/a/18462734/3461845 我需要能够执行以下查询： 并且还为返回的每一行拉回数据库的名称； 任何帮助是极大的
-
我为每个日期创建了多个表来存储每个日期的一些信息。例如历史3108、历史0109..所有这些表共享相同的模式。有时候，我需要查询多个表，获取记录的行数和计数。在oracle和SQL Server中，最快的方法是什么？ 目前我正在这样做。。。 当我需要多个表的计数时：为每个表选择计数（*），然后添加 当我需要多个表的记录时：从表1中选择*，从表2中选择*（基本上为每个表选择*） 如果我们在一个事务中
-
使用JDBC驱动程序，我如何使用来自不同查询的多个结果集，而不不断地打开和关闭连接，因为我正在提取所需的w.e并将其传递给另一个方法。每次打开新的conn、语句和结果集时 我试图在一个方法中使用多个结果集，但它一直抛出异常，称结果集已关闭。我没有太多的SqlServver经验，所以任何指导都会有所帮助：-）
-
在程序中有一部分代码，用于执行查询： 数据集 （） 表应该有大约 20000 条记录（行计数），但在程序内部，它在调试模式下说只有 20 条记录。 我在同一个数据库表上单独运行了脚本，该表在 SQL Server 连接字符串中使用，但在 SQL Server Management Studio 中，它按预期返回了 20000 条记录。 该脚本看起来像： 它是相同的脚本文本，代码也会执行该文本。 有
-
我正在使用swagger编辑器记录一个现有的API，该API允许一个路径支持两个不同的请求，这两个请求仅在查询参数名上有所不同。例如： 这里我支持对和的请求。 swagger编辑器不会显示上述yaml的任何错误，但它只为第二个路径（具有UserId和令牌queryparams的路径）生成文档，而不是同时生成。有人能指出我哪里出了问题吗？谢了。 编辑： 如果我将第二个路径更改为（例如），那么我会在文