Mysql优化查询

Question

我有一些现有的 Mysql 查询，只是想知道如何改进它。因为执行有时需要长达 20 秒。事实上，找到正确的记录最多需要 0.3690 秒，但当需要获得 40k 记录时最多需要 20 秒。

所以我的问题是如何改进我的设置或我的 sql 代码以更快地获取记录？或者它现在只依赖于我的机器(比如 SAS 硬盘)？

首先是一些必要的信息:
我的应用程序使用 MySQL 服务器 5.6 和 InnoDB 引擎
我的自定义设置:

innodb_buffer_pool_size = 7G
innodb_log_buffer_size = 64M
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 0
innodb_write_io_threads = 32
join_buffer_size = 32M
tmp_table_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 128M
table_open_cache = 4000
bulk_insert_buffer_size = 256M 

表定义:

CREATE TABLE `tblusers` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(100) NOT NULL,
`password` varchar(50) NOT NULL,
`user_name` varchar(45) NOT NULL,
`phone` varchar(12) NOT NULL,
`machine_id` int(11) NOT NULL DEFAULT '1',
`lang_id` int(11) NOT NULL DEFAULT '14',
`user_type` tinyint(4) NOT NULL DEFAULT '1' ,
`created_on` datetime NOT NULL,
`active_open` tinyint(4) NOT NULL DEFAULT '0' ,
`email_hash` varchar(50) NOT NULL DEFAULT '1',
`profile_approved` tinyint(4) NOT NULL DEFAULT '0',
`menage_data` tinyint(4) NOT NULL DEFAULT '0' ,
`mailing_agree` tinyint(4) NOT NULL DEFAULT '0' ,
`edited` tinyint(4) NOT NULL DEFAULT '0',
`deleted` tinyint(4) NOT NULL DEFAULT '0',
`warnings` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`user_id`),
UNIQUE KEY `email_UNIQUE` (`email`),
UNIQUE KEY `user_name_UNIQUEE` (`user_name`),
KEY `fk_tblUsers_hlpLangs1_idx` (`lang_id`),
KEY `email_hash` (`email_hash`),
KEY `trio` (`user_type`,`profile_approved`,`deleted`,`email_hash`),
CONSTRAINT `tblusers_ibfk_1` FOREIGN KEY (`lang_id`) REFERENCES `hlplangs` (`lang_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tblhostess` (
  `user_id` int(11) NOT NULL,
  `first_name` varchar(45) NOT NULL,
  `sure_name` varchar(45) DEFAULT NULL,
  `gender` tinyint(4) NOT NULL DEFAULT '0' ,
  `dob` datetime NOT NULL,
  `driver_license` tinyint(4) NOT NULL DEFAULT '0',
  `sanepid` tinyint(4) NOT NULL DEFAULT '0',
  `city_id` int(11) NOT NULL,
  `province_id` int(11) NOT NULL,
  `picture_id` int(11) NOT NULL DEFAULT '1',
  `hair_id` int(11) NOT NULL DEFAULT '1',
  `hair_color_id` int(11) NOT NULL DEFAULT '1',
  `number_of_view` int(11) NOT NULL DEFAULT '0',
  `who_can_see` tinyint(4) NOT NULL DEFAULT '0' ,
  `complete_register` tinyint(4) NOT NULL DEFAULT '0',
  `skin_id` int(11) NOT NULL DEFAULT '1',
  `bra_id` int(11) NOT NULL DEFAULT '1',
  `wear_id` int(11) NOT NULL DEFAULT '1',
  `shoe_id` int(11) NOT NULL DEFAULT '1',
  `desc` text,
  `height` int(11) NOT NULL DEFAULT '0',
  `weight` int(11) NOT NULL DEFAULT '0',
  `bust` int(11) NOT NULL DEFAULT '0',
  `waist` int(11) NOT NULL DEFAULT '0',
  `hip` int(11) NOT NULL DEFAULT '0',
  `redirect_url` varchar(255) DEFAULT NULL,
  `friend_url` varchar(90) DEFAULT NULL,
  `premium` tinyint(4) NOT NULL DEFAULT '0',
  `premium_until` datetime DEFAULT NULL,
  `work_as_model` tinyint(4) NOT NULL DEFAULT '0',
  `work_as_hostess` tinyint(4) NOT NULL DEFAULT '1',
  `work_as_fotomodel` tinyint(4) NOT NULL DEFAULT '0',
  `work_in_club` tinyint(4) NOT NULL DEFAULT '0',
  `work_in_party` tinyint(4) NOT NULL DEFAULT '0',
  `work_in_promo` tinyint(4) NOT NULL DEFAULT '0',
  `work_in_trade` tinyint(4) NOT NULL DEFAULT '0',
  `work_in_event` tinyint(4) NOT NULL DEFAULT '0',
  `work_in_gala` tinyint(4) NOT NULL DEFAULT '0',
  `phone_ver` tinyint(4) NOT NULL DEFAULT '0',
  `cert` tinyint(4) NOT NULL DEFAULT '0',
  `fb_premium` tinyint(4) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`user_id`),
  KEY `fk_tblHostess_tblCities1_idx` (`city_id`),
  KEY `fk_tblHostess_hlpProvinces1_idx` (`province_id`),
  KEY `fk_tblHostess_hlpHairColor1_idx` (`hair_color_id`),
  KEY `fk_tblHostess_hlpHair1_idx` (`hair_id`),
  KEY `fk_tblHostess_hlpShoes1_idx` (`shoe_id`),
  KEY `fk_tblHostess_hlpBra1_idx` (`bra_id`),
  KEY `fk_tblHostess_hlpWear1_idx` (`wear_id`),
  KEY `fk_tblHostess_hlpSkinColor1_idx` (`skin_id`),
  KEY `premium` (`premium`),
  KEY `num_of_views` (`number_of_view`),
  KEY `views_premium` (`number_of_view`,`premium`),
  CONSTRAINT `tblhostess_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tblusers` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `tblhostess_ibfk_2` FOREIGN KEY (`city_id`) REFERENCES `hlpcities` (`city_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `tblhostess_ibfk_3` FOREIGN KEY (`province_id`) REFERENCES `hlpprovinces` (`province_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tblhostessmailings` (
  `hostess_id` int(11) NOT NULL,
  `new_job_offers` int(11) NOT NULL DEFAULT '2' ,
  `comments` int(11) NOT NULL DEFAULT '1' ,
  `job_offer_accept` int(11) NOT NULL DEFAULT '1',
  `private_message` int(11) NOT NULL DEFAULT '1' ,
  `job_offer_sms` int(11) NOT NULL DEFAULT '0' ,
  `job_offer_private` int(11) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`hostess_id`),
  CONSTRAINT `tblhostessmailings_ibfk_1` FOREIGN KEY (`hostess_id`) REFERENCES `tblhostess` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbljoboffers` (
  `offer_id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) NOT NULL,
  `subject` varchar(250) NOT NULL,
  `content` text NOT NULL,
  `content_html` text,
  `date_added` datetime NOT NULL,
  `active` tinyint(3) unsigned NOT NULL DEFAULT '1' ,
  `approved` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `edited` tinyint(3) unsigned NOT NULL DEFAULT '0' ,
  `email` varchar(100) DEFAULT NULL,
  `freqence_id` int(11) NOT NULL DEFAULT '1' ,
  `premium` tinyint(3) unsigned NOT NULL DEFAULT '0' ,
  `start_date` datetime DEFAULT NULL ,
  `end_date` datetime DEFAULT NULL ,
  `premium_old_user` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sending` tinyint(3) unsigned NOT NULL DEFAULT '0' ,
  `external_sent` tinyint(3) unsigned NOT NULL DEFAULT '0' ,
  `internal_sent` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `archiwal` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `friend_url` varchar(250) DEFAULT NULL,
  `deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `to_export` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `exported` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sms_sent` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sms_sending` tinyint(3) unsigned NOT NULL DEFAULT '0' ,
  `private` tinyint(4) NOT NULL DEFAULT '0' ,
  `private_paid` tinyint(4) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`offer_id`,`freqence_id`),
  KEY `fk_tblJoboffers_tblEmployers1_idx` (`employer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tbljobofferslocations` (
  `location_id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_id` int(11) NOT NULL,
  `city_id` int(11) NOT NULL,
  `province_id` int(11) NOT NULL,
  `ref_code` varchar(100) DEFAULT NULL,
  `display_times` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`location_id`),
  UNIQUE KEY `offer_id` (`offer_id`,`city_id`,`province_id`),
  KEY `fk_tblJobOffersLocations_hlpProvinces1_idx` (`province_id`),
  KEY `fk_tblJobOffersLocations_tblCities1_idx` (`city_id`),
  KEY `fk_tblJobOffersLocations_tblJobOffers1_idx` (`offer_id`),
  CONSTRAINT `tbljobofferslocations_ibfk_1` FOREIGN KEY (`offer_id`) REFERENCES `tbljoboffers` (`offer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `tbljobofferslocations_ibfk_2` FOREIGN KEY (`city_id`) REFERENCES `hlpcities` (`city_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `tbljobofferslocations_ibfk_3` FOREIGN KEY (`province_id`) REFERENCES `hlpprovinces` (`province_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

记录数大约在 db
用户 100k
工作机会约 20k(全部)
和大约 40k 的位置

最后是我的 sql 示例:

select email, first_name, sure_name
            from tblusers u join tblhostess h on h.user_id = u.user_id 
            join tblhostessmailings m on m.hostess_id = h.user_id
            join 
            (select province_id from tbljoboffers o join tbljobofferslocations l on l.offer_id = o.offer_id
            where o.deleted = 0 and o.start_date > date_add(current_timestamp, INTERVAL -1 DAY) and o.approved = 1 and o.active = 1 and o.internal_sent = 1 and o.private = 0
             group by l.province_id) z on z.province_id = h.province_id
            where u.deleted = 0 and u.email_hash = '1' and email != '' and user_type = 1 and (m.new_job_offers = 2)

我想从这里得到的是 1 天内在其内部邮箱中至少收到一个报价的所有用户的列表
所有工作都分省所以这个

(select province_id from tbljoboffers o join tbljobofferslocations l on l.offer_id = o.offer_id
            where o.deleted = 0 and o.start_date > date_add(current_timestamp, INTERVAL -1 DAY) and o.approved = 1 and o.active = 1 and o.internal_sent = 1 and o.private = 0
             group by l.province_id)

获取区域的完整列表，其余获取用户信息
有时我需要用这个查询检索 40k 条记录，所以我真的需要改进这一点。

提前致谢。

Answer 1

我会查看您自己加入的子查询。

select province_id from tbljoboffers o join tbljobofferslocations l on l.offer_id = o.offer_id
        where o.deleted = 0 and o.start_date > date_add(current_timestamp, INTERVAL -1 DAY) and o.approved = 1 and o.active = 1 and o.internal_sent = 1 and o.private = 0
         group by l.province_id

WHERE 子句中的日期比较可能是减速的一部分，该子查询需要多长时间才能自行运行？大于 date_add 函数的比较可能会很慢。但是，在不更改查询本身的情况下，您可以通过简单地将子查询放入 View 中来获得一些性能，然后您可以将 View 连接回主查询。

CREATE VIEW vprovince_ids AS
select province_id from tbljoboffers o join tbljobofferslocations l on l.offer_id = o.offer_id
            where o.deleted = 0 and o.start_date > date_add(current_timestamp, INTERVAL -1 DAY) and o.approved = 1 and o.active = 1 and o.internal_sent = 1 and o.private = 0
             group by l.province_id

那么您的主要查询将变为:

select email, first_name, sure_name
            from tblusers u join tblhostess h on h.user_id = u.user_id 
            join tblhostessmailings m on m.hostess_id = h.user_id
            join vprovince_ids z on z.province_id = h.province_id
            where u.deleted = 0 and u.email_hash = '1' and email != '' and user_type = 1 and (m.new_job_offers = 2)

希望对您有所帮助!