mysql - 适当的索引(或删除)以优化大型数据集表

Question

我们有一个正在进行的“访问者”跟踪模式 - 推送时，似乎会对数据库服务器造成一些压力。

VISITORS 表通过 HASH 标识唯一用户(当前记录 310,000)。对散列执行搜索，如果未找到，则将其添加。以下两张表需要ID

CREATE TABLE  visitors (
    id int(10) UNSIGNED NOT NULL auto_increment,
    ip varchar(25) NOT NULL,
    hash varchar(64) NOT NULL,
    first_visit varchar(32) NOT NULL,
    created_at datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;   

ALTER TABLE visitors ADD UNIQUE INDEX (hash);
ALTER TABLE visitors ADD INDEX (created_at);

VISITOR_VISITS 表仅在我们可以识别一些引荐来源时识别用户何时访问(当前计数 142,000)。执行搜索以查找 visitor_id、type 和 visit_date。如果没有找到 - 它被添加。下表中使用了 ID。

CREATE TABLE  visitor_visits (
    id int(10) UNSIGNED NOT NULL auto_increment,
    visitor_id int(10) UNSIGNED NOT NULL,
    source varchar(64) NULL DEFAULT NULL DEFAULT NULL,
    medium varchar(64) NULL DEFAULT NULL,
    campaign varchar(256) NULL DEFAULT NULL,
    page varchar(32) NULL DEFAULT NULL,
    landing varchar(32) NULL DEFAULT NULL,
    type enum('fundraiser_view') NULL DEFAULT NULL,
    visit_date date NOT NULL default '0000-00-00',
    created_at datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

ALTER TABLE visitor_visits ADD UNIQUE INDEX (visitor_id,type,visit_date);
ALTER TABLE visitor_visits ADD CONSTRAINT FK_visits_visitor_id FOREIGN KEY (visitor_id) REFERENCES visitors(id);

PAGE_VIEWS 记录单个页面浏览量(不是所有页面，只是我们正在跟踪的页面)。它可以链接到访问者并可以引用 visitor_visit(当前计数为 240 万——它更高的原因是我们在记录单个页面后开始微访问者记录)。插入/重复查询用于根据已识别用户的 view_date 添加记录。由于不需要 ID，因此不需要纯查找查询

CREATE TABLE page_views (
  id int(10) UNSIGNED NOT NULL auto_increment,
  page_id int(10) UNSIGNED NOT NULL,
  current_donations decimal(10,2) NOT NULL DEFAULT 0,
  ip varchar(25) NOT NULL,
  hash varchar(32) NOT NULL,
  visitor_id int(10) UNSIGNED NULL DEFAULT NULL AFTER,
  visitor_visit_id int(10) UNSIGNED NULL DEFAULT NULL AFTER,
  page_views int(10) UNSIGNED NOT NULL DEFAULT 0,
  widget_views int(10) UNSIGNED NOT NULL DEFAULT 0,
  view_date date NOT NULL,
  viewed_at datetime NOT NULL default '0000-00-00 00:00:00',
  created_at datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

ALTER TABLE page_views ADD UNIQUE INDEX (page_id,view_date,visitor_id,hash);
ALTER TABLE page_views ADD INDEX (visitor_id);
ALTER TABLE page_views ADD INDEX (visitor_visit_id);
ALTER TABLE page_views ADD CONSTRAINT FK_page_views_page_id FOREIGN KEY (page_id)    REFERENCES pages(id);
ALTER TABLE page_views ADD CONSTRAINT FK_page_views_visitor_id FOREIGN KEY (visitor_id)    REFERENCES visitors(id);
ALTER TABLE page_views ADD CONSTRAINT FK_page_views_visit_id FOREIGN KEY (visitor_visit_id) REFERENCES visitor_visits(id);

上周，我们的网站因一篇新闻文章而吸引了大批人流，这位访问者指出了所有性能瓶颈。我想知道那里是否有明显的优化。难道是外键约束？过度索引？需要更好的索引？

Answer 1

试试这个::1) varchar 上的索引不会提高性能。2)尝试在日期范围内对表进行分区。