CREATE TABLE `test` (
`logtime` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '开始时间',
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`a_id` int(8) unsigned NOT NULL DEFAULT '0',
`b_id` int(8) unsigned NOT NULL DEFAULT '0',
`c_id` int(8) unsigned NOT NULL DEFAULT '0' ,
`d_id` int(8) unsigned NOT NULL DEFAULT '0',
`regtime` int(11) NOT NULL DEFAULT '0' COMMENT '注册时间',
PRIMARY KEY (`id`),
KEY `time` (`logtime`,`regtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8有联合索引time,表总数据为1000W
select * from test where logtime between xxx and xxx and regtime between xxx and xxx 查询出了500W数据
使用explain查看语句
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "test" "ALL" "time" \N \N \N "5123868" "Using where"请问这样的语句该如何优化?好像并没有走联合索引。
以供分析。
show index from ..
以供分析。
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
test 0 PRIMARY 1 id A 0 (NULL) (NULL) BTREE
test 1 time 1 logtime A 0 (NULL) (NULL) BTREE
test 1 time 2 regtime A 0 (NULL) (NULL) BTREE
kc 0 PRIMARY 1 id A 5123868 (NULL) (NULL) BTREE
kc 0 order_id 1 order_id A 5123868 (NULL) (NULL) BTREE
kc 1 sub1 1 sub_1 A 213494 (NULL) (NULL) BTREE
kc 1 sub1 2 sub_2 A 269677 (NULL) (NULL) BTREE
kc 1 sub1 3 sub_3 A 365990 (NULL) (NULL) BTREE
kc 1 sub1 4 sub_4 A 365990 (NULL) (NULL) BTREE
kc 1 sub1 5 sub_5 A 365990 (NULL) (NULL) BTREE
kc 1 adinfo 1 bk_id A 22473 (NULL) (NULL) BTREE
kc 1 adinfo 2 op_id A 21528 (NULL) (NULL) BTREE
kc 1 adinfo 3 res_id A 20660 (NULL) (NULL) BTREE
kc 1 adinfo 4 cap_id A 39113 (NULL) (NULL) BTREE
kc 1 adinfo 5 a_id A 46580 (NULL) (NULL) BTREE
kc 1 adinfo 6 game_id A 62486 (NULL) (NULL) BTREE
kc 1 adinfo 7 m_id A 60280 (NULL) (NULL) BTREE
kc 1 adinfo 8 server_id A 301404 (NULL) (NULL) BTREE
kc 1 account 1 identity A 1024773 (NULL) (NULL) BTREE
kc 1 account 2 account A 1024773 (NULL) (NULL) BTREE
kc 1 times 1 logtime A 5123868 (NULL) (NULL) BTREE
kc 1 times 2 regtime A 5123868 (NULL) (NULL) BTREE
9楼中根本找不到你 顶楼 explain 中的 time 索引.kc 1 times 1 logtime A 5123868 (NULL) (NULL) BTREE
kc 1 times 2 regtime A 5123868 (NULL) (NULL) BTREE如果这个就是楼主所指的 time索引,则再创建一个 regtime 的独立索引然后再尝试。(提问的智慧)
没看出有什么问题好像强制走索引速度更慢。。我勒个去,这种情况适合用分区表么??
只要你能把不需要的数据和需要的数据分到不同的分区,分区就有效果,否则没什么效果
谢谢建议,已换mongo。。不死磕mysql了
为什么会出现这种情况?不是应该最左匹配吗?请稍微讲解下原理