- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
我们已经注意到我们的 MySQL 性能在查询时间上存在一些不一致的情况,我们认为这不能仅用服务器负载来解释。尽管具有类似的设置,但某些查询似乎比其他查询更有效。
编辑:自从打开这个问题,我们的数据库崩溃了(目前未知原因,RDS 团队正在调查),重启后问题不再重现,查询速度相同。我仍然想知道出了什么问题,因为问题可能会再次出现,但也许我们永远不会发现......
继续原始问题:为了测试这一点,我制作了一张表格的近副本。尽管具有相同的执行路径、相同的数据和非常相似的表示意图,但针对复制表的性能(查询时间)明显比源差。
在这个例子中,我们有以下 3 个表:
| 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 |
INSERT INTO offers_new (id, status) SELECT id, status FROM offers;
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)
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)
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
最佳答案
猜测(等待评论中要求的更多信息)...
innodb_buffer_pool_size
小于 Data_length
(请参阅 SHOW TABLE SIZE offers
),并且 15K 行分散在表格周围。这可能会导致命中磁盘而不是找到所需的 id
在缓存 (buffer_pool) 中。 offers_clicks
;什么是分区键?
offers
吗根本?难道不能从
SELECT COUNT(*) FROM offers_clicks WHERE date ...
得到正确的答案吗? ?
JOIN
,那么我们来谈谈构建和维护一个
summary table保持每小时的计数。
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 ...
JOIN
的老式“commalist”风格,后者是
首选语法 .这两种语法生成相同的代码,因此这不是性能问题。前者看起来像“交叉连接”,但
WHERE
使它有效地不是。
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
The first time is usually slower, which is when it shows major page faults. Follow up queries are usually fast.
offers
大得多,会有更多的 I/O。然而,这与你所看到的相反。
关于mysql - 相同的执行路径、数据和原理图;不同的查询时间,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/56397652/
我有 table 像这样 -------------------------------------------- id size title priority
我的应用在不同的 Activity (4 个 Activity )中仅包含横幅广告。所以我的疑问是, 我可以对所有横幅广告使用一个广告单元 ID 吗? 或者 每个 Activity 使用不同的广告单元
我有任意(但统一)数字列表的任意列表。 (它们是 n 空间中 bin 的边界坐标,我想绘制其角,但这并不重要。)我想生成所有可能组合的列表。所以:[[1,2], [3,4],[5,6]] 产生 [[1
我刚刚在学校开始学习 Java,正在尝试自定义控件和图形。我目前正在研究图案锁,一开始一切都很好,但突然间它绘制不正确。我确实更改了一些代码,但是当我看到错误时,我立即将其更改回来(撤消,ftw),但
在获取 Distinct 的 Count 时,我在使用 Group By With Rollup 时遇到了一个小问题。 问题是 Rollup 摘要只是所有分组中 Distinct 值的总数,而不是所有
这不起作用: select count(distinct colA, colB) from mytable 我知道我可以通过双选来简单地解决这个问题。 select count(*) from (
这个问题在这里已经有了答案: JavaScript regex whitespace characters (5 个回答) 2年前关闭。 你能解释一下为什么我会得到 false比较 text ===
这个问题已经有答案了: 奥 git _a (56 个回答) 已关闭 9 年前。 我被要求用 Javascript 编写一个函数 sortByFoo 来正确响应此测试: // Does not cras
所以,我不得不说,SQL 是迄今为止我作为开发人员最薄弱的一面。也许我想要完成的事情很简单。我有这样的东西(这不是真正的模型,但为了使其易于理解而不浪费太多时间解释它,我想出了一个完全模仿我必须使用的
这个问题在这里已经有了答案: How does the "this" keyword work? (22 个回答) 3年前关闭。 简而言之:为什么在使用 Objects 时,直接调用的函数和通过引用传
这个问题在这里已经有了答案: 关闭 12 年前。 Possible Duplicate: what is the difference between (.) dot operator and (-
我真的不明白这里发生了什么但是: 当我这样做时: colorIndex += len - stopPos; for(int m = 0; m < len - stopPos; m++) { c
思考 MySQL 中的 Group By 函数的最佳方式是什么? 我正在编写一个 MySQL 查询,通过 ODBC 连接在 Excel 的数据透视表中提取数据,以便用户可以轻松访问数据。 例如,我有:
我想要的SQL是这样的: SELECT week_no, type, SELECT count(distinct user_id) FROM group WHERE pts > 0 FROM bas
商店表: +--+-------+--------+ |id|name |date | +--+-------+--------+ |1 |x |Ma
对于 chrome 和 ff,当涉及到可怕的 ie 时,这个脚本工作完美。有问题 function getY(oElement) { var curtop = 0; if (oElem
我现在无法提供代码,因为我目前正在脑海中研究这个想法并在互联网上四处乱逛。 我了解了进程间通信和使用共享内存在进程之间共享数据(特别是结构)。 但是,在对保存在不同 .c 文件中的程序使用 fork(
我想在用户集合中使用不同的功能。在 mongo shell 中,我可以像下面这样使用: db.users.distinct("name"); 其中名称是用于区分的集合字段。 同样我想要,在 C
List nastava_izvjestaj = new List(); var data_context = new DataEvidencijaDataContext();
我的 Rails 应用程序中有 Ransack 搜索和 Foundation,本地 css 渲染正常,而生产中的同一个应用程序有一个怪癖: 应用程序中的其他内容完全相同。 我在 Chrome 和 Sa
我是一名优秀的程序员,十分优秀!