MYSQL需要在单表上更快地分组
MYSQL需要在单表上更快地分组
提问于 2018-07-18 22:42:05
我尝试从包含1,000,000个属性的表中为每个州选择一个属性。我在努力
select * from properties
where latitude is not null and longitude is not null
group by property_state;但是查询需要3秒钟。我有一个纬度和经度的索引，还有一个州的索引。我尝试在所有3列上添加第三个索引，但没有帮助。有什么想法吗？
下面是create table代码，如果有帮助的话(我删除了没有帮助的新索引)
CREATE TABLE `t_national_comps` (
`deal_Id` INT(11) NULL DEFAULT NULL,
`nc_id` INT(11) NOT NULL AUTO_INCREMENT,
`property_id` INT(15) NULL DEFAULT NULL,
`reonomy_property_id` VARCHAR(50) NULL DEFAULT NULL,
`reonomy_url` VARCHAR(80) NULL DEFAULT NULL,
`confidence` FLOAT NULL DEFAULT NULL,
`latitude` DECIMAL(11,8) NULL DEFAULT NULL,
`longitude` DECIMAL(11,8) NULL DEFAULT NULL,
`prop_key` VARCHAR(255) NULL DEFAULT NULL,
`fmt_address` VARCHAR(255) NULL DEFAULT NULL,
`property_street_number` VARCHAR(20) NULL DEFAULT NULL,
`property_street_name` VARCHAR(40) NULL DEFAULT NULL,
`property_street_mode` VARCHAR(20) NULL DEFAULT NULL,
`property_city` VARCHAR(40) NULL DEFAULT NULL,
`property_state` VARCHAR(10) NULL DEFAULT NULL,
`property_zip` VARCHAR(10) NULL DEFAULT NULL,
`property_zip4` VARCHAR(10) NULL DEFAULT NULL,
`municipality` VARCHAR(40) NULL DEFAULT NULL,
`property_class_id` VARCHAR(15) NULL DEFAULT NULL,
`std_land_use_code` VARCHAR(15) NULL DEFAULT NULL,
`sale_doc_num` VARCHAR(30) NULL DEFAULT NULL,
`mortgage_doc_num` VARCHAR(30) NULL DEFAULT NULL,
`mortgage_date` DATE NULL DEFAULT NULL,
`lender` VARCHAR(100) NULL DEFAULT NULL,
`bank_id` INT(11) NULL DEFAULT NULL,
`loan_amount` BIGINT(15) NULL DEFAULT NULL,
`maturity_date` DATE NULL DEFAULT NULL,
`rate` VARCHAR(20) NULL DEFAULT NULL,
`sale_date` DATE NULL DEFAULT NULL,
`curr_sale_contract_date` DATE NULL DEFAULT NULL,
`curr_sale_document_type` VARCHAR(20) NULL DEFAULT NULL,
`sale_price` BIGINT(22) NULL DEFAULT NULL,
`curr_sale_buyer1_full_name` VARCHAR(60) NULL DEFAULT NULL,
`curr_sale_buyer2_full_name` VARCHAR(60) NULL DEFAULT NULL,
`reported_owner` VARCHAR(60) NULL DEFAULT NULL,
`mailing_address` VARCHAR(500) NULL DEFAULT NULL,
`curr_sale_seller1_full_name` VARCHAR(60) NULL DEFAULT NULL,
`curr_sale_seller2_full_name` VARCHAR(60) NULL DEFAULT NULL,
`sq_footage` VARCHAR(10) NULL DEFAULT NULL,
`resi_units` VARCHAR(10) NULL DEFAULT NULL,
`commercial_units` VARCHAR(10) NULL DEFAULT NULL,
`num_floors` VARCHAR(10) NULL DEFAULT NULL,
`num_buildings` VARCHAR(10) NULL DEFAULT NULL,
`price_per_sq_ft` INT(11) NULL DEFAULT NULL,
`price_per_unit` INT(11) NULL DEFAULT NULL,
`property_type_id` INT(11) NULL DEFAULT NULL,
`property_type` VARCHAR(60) NULL DEFAULT NULL,
`long_lat_point` POINT NULL DEFAULT NULL,
PRIMARY KEY (`nc_id`),
INDEX `t_national_comps_latitude_longitude_index` (`latitude`, `longitude`),
INDEX `t_national_comps_property_city_index` (`property_city`),
INDEX `t_national_comps_property_state_index` (`property_state`),
INDEX `t_national_comps_sale_date_index` (`sale_date`),
INDEX `t_national_comps_point_index` (`long_lat_point`(25)),
INDEX `t_national_comps_reonomy_id_index` (`reonomy_property_id`),
INDEX `mailing_address_index` (`mailing_address`),
INDEX `mortgage_date_index` (`mortgage_date`),
INDEX `t_national_comps_lender_index` (`lender`),
INDEX `bank_id_index` (`bank_id`),
INDEX `street_num_and_zip` (`property_street_number`, `property_zip`)
);编辑
我没有聚合查询中的任何内容的原因，是因为我没有要聚合的内容。我知道这不是group by的主要用途，但它通常是这样使用的，只是为了获得每条记录中的一条记录。
我能够通过在所有3列上强制使用索引来加快查询速度，例如
select latitude, longitude, property_street_number, property_street_name,
property_city, property_state, property_zip from properties
USE INDEX (lat_long_state_index)
where latitude is not null and longitude is not null
group by property_state;但我仍然在寻找更多的优化。
感谢大家的帮助。
回答 2
Stack Overflow用户
发布于 2018-07-19 08:41:50
按分组的
我不相信Group By应该以这种方式使用，虽然在内部MySQL可以足够聪明(我不确定)，当它看到一个没有聚合的group By时使用Distinct，但我认为这不是使用Group by的正确方式。
索引
MySQL为每个查询的每个表使用一个索引，并且它只会选择一个索引，因此在有三个列之前，使用property_state选择索引是正确的，因为MySQL通常不会在不相等的条件下使用索引。
您可以在force index之前和之后对查询进行解释比较。MySQL优化器认为单列索引更好。
过多的索引也会增加插入的开销。在有了三列索引之后，实际上可以删除property_state索引，因为它被三列索引(最左边)所覆盖。您将来的查询肯定会使用您创建的新索引。
Stack Overflow用户
发布于 2018-07-18 22:48:12
我的建议是不要使用select *。使用select id, ...而不是select *。这肯定会减少你的执行时间。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51404905
复制相关文章
相似问题