我有一个400万数据的表,如何才能优化?

wsy940822 发布于 2016/02/26 18:31
阅读 498
收藏 2

    现在我有一个400万数据的表,表里面的create_time是精确到秒的,我的PHP的查询语句是:

    $sql = "SELECT count(*) AS type_count,type FROM user_action_record WHERE date(create_time) = '{$today}' AND wid = :mid GROUP BY type ORDER BY NULL ";

    这里的today是当前日期的年月日,即类似2016-02-23,而表中存储的却是2016-02-23 11:13:23 精确到了秒,type 跟mid的不同可能有不同的数量,但是一共有29种。

,表的结构如下:

CREATE TABLE `user_action_record` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `wid` int(11) unsigned NOT NULL,
  `openid` varchar(255) NOT NULL DEFAULT '',
  `type` varchar(255) NOT NULL DEFAULT '',
  `keyword` varchar(1000) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL,
  `source_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `source_value` varchar(255) NOT NULL DEFAULT '',
  `session_count` int(11) DEFAULT '1',
  `coupon_code` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `wid_type_create_time` (`wid`,`type`,`create_time`),
  KEY `OPENID_MID_INDEX` (`openid`,`wid`),
  KEY `wid` (`wid`)
) ENGINE=MyISAM AUTO_INCREMENT=4474647 DEFAULT CHARSET=utf8;

EXPLAIN出来如下:

id:1  select_type: SIMPLE   table: user_action_record   type: ref  possible_keys:  wid_type_create_time,wid  key:  wid_type_create_time  key_len:  4   ref: const  rows:  2192234   extra: Using where

目前提出的解决方案或是可能出问题的地方有几种:

    1.sql语句中的data()去掉,新增一个字段叫做created_date,这个字段保存年月日。

    2.可能是因为group by type所以才变慢的。

    3.索引不太正确。

求高手解答~感激不尽~如果对问题有疑问,可以留言~肯定回复~~


加载中
0
都市网达
都市网达
wid_type_create_time 索引命中不了,其实可以使用create_time <= '2016-02-23 23:59:59' and create_time >= '2016-02-23 00:00:00' 试试。
wsy940822
wsy940822
应该命中了吧,他选择的key就是wid_type_create恩,而且你这个方法我试了,用的是bteween and 比><效率应该还要高一点,但是速度并没有明显提高恩~
0
倪倪

用整形时间戳保存日期并索引,

用int保存type信息并索引,

SQL语句先子语句中缩小检索范围到某天,向上传id到外层再过滤wid,最后再group by

倪倪
回复 @wsy940822 : 不是改,而是增加
wsy940822
wsy940822
成熟的项目,如果把日期改为int时间戳的话,代码中的改动实在太多了,可能得不偿失,下面说的缩小检索范围是大概怎么做呢,可以帮忙举例写下语句么~表引擎的话,用的是innoDB
0
倪倪
再注意下选择适当的表引擎
0
倪倪

基本思路是先通过索引的日期获取符合条件的数据,

再在这些数据里查找符合wid的数据,

最后用这些过滤的数据来分组。

由于in语句结构最清晰,大概如下,没实测,但有些数据库或不同引擎优化不同,可能用in效率并不高,你可以在你的实际环境中用exists语句替换或两个条件返回两个虚表联结后再分组,都试下看,看下哪种最快。


SELECT count(id) AS type_count,type

 FROM user_action_record

 WHERE id in (

         SELECT id FROM user_action_record where wid =:mid and id in (

               SELECT id FROM user_action_record where   int_date={$today}

            )

)

建议新增一字段(如上面的int_date)单独索引记录日期,格式可以是这样:20160226. 用空间换时间

wsy940822
wsy940822
恩~我周末在家新增了个整型字段,速度的确提升了不少,已经可以在1秒以内了,谢谢~
返回顶部
顶部