mysql - 向大型数据库表正确添加索引

Question

问题是，在我将 200.000-300.000 行数据插入这些列后，搜索速度非常慢，我首先想到的是我可能没有正确添加的索引。我尝试添加尽可能多的 BTREE 索引，但 phpmyadmin 不允许我添加所有索引。我的表的正确索引是什么？我有下表，其中包含以下索引:

CREATE TABLE IF NOT EXISTS `carads` (
  `ADID` int(7) NOT NULL AUTO_INCREMENT,
  `LINK` varchar(255) CHARACTER SET latin1 NOT NULL,
  `TITLE` varchar(255) NOT NULL,
  `MAKE` varchar(50) CHARACTER SET latin1 NOT NULL,
  `MODEL` varchar(100) CHARACTER SET latin1 NOT NULL,
  `FUEL` varchar(50) CHARACTER SET latin1 NOT NULL,
  `LOC` varchar(100) NOT NULL,
  `TRANS` varchar(50) NOT NULL,
  `YEAR` varchar(4) CHARACTER SET latin1 NOT NULL,
  `BODY` varchar(255) CHARACTER SET latin1 NOT NULL,
  `DESCRIPT` text CHARACTER SET latin1 NOT NULL,
  `PHONENR` varchar(20) NOT NULL,
  `MILEAGE` int(11) NOT NULL,
  `PRICE` int(20) NOT NULL,
  `DISTANCE` int(250) NOT NULL,
  `POSTCODE` varchar(250) NOT NULL,
  `IMAGE1` varchar(255) NOT NULL,
  `IMAGE2` varchar(255) NOT NULL,
  `IMAGE3` varchar(255) NOT NULL,
  `IMAGE4` varchar(255) NOT NULL,
  `IMAGE5` varchar(255) NOT NULL,
  `CPHONE` varchar(250) NOT NULL,
  `CEMAIL` varchar(500) NOT NULL,
  `COLOUR` varchar(250) NOT NULL,
  `EQUIPMENT` text NOT NULL,
  `STATUS` tinyint(1) NOT NULL DEFAULT '1',
  `DATE` date NOT NULL,
  `DEL` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ADID`),
  KEY `ix_MakeModelPrice` (`STATUS`,`MAKE`(25),`MODEL`(25),`PRICE`),
  KEY `ix_Price` (`PRICE`,`STATUS`,`DEL`,`TITLE`(30),`ADID`),
  KEY `ix_Date` (`DATE`,`STATUS`,`DEL`,`TITLE`(30),`ADID`),
  KEY `LINK` (`LINK`),
  FULLTEXT KEY `MODEL` (`MODEL`),
  FULLTEXT KEY `SearchIndex`  (`TITLE`,`LOC`,`TRANS`,`CPHONE`,`CEMAIL`,`COLOUR`,`EQUIPMENT`),
  FULLTEXT KEY `MAKE` (`MAKE`)
) 
ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2478687;

Answer 1

这个问题很复杂，我们无法给你正确的答案，你必须自己理解并找到最好的答案。

您必须记住以下几点:

1. 查询优化器将仅选择一个索引。
2. 以“status”和/或“del”( bool 值或 95% 的行具有选定值的值)开头的索引不会添加任何值，此外这些虚拟列后面会经常查询，高度选择性的值(value)观。

您应该首先找到以下属性

• 填写了大部分查询(我可以想象“品牌”、“价格”和“年份”都是不错的选择)
• 最具选择性(意味着结果行< 10%)

您必须找出表中每列的值的分布情况。示例:

品牌:

• 宝马:5%
• 阿尔法罗密欧:1%
• 大众:7%
• ...

价格范围:

• 0..999:3%
• 1000..1999:4%
• 2000..3000:5%
• ...

如果 80% 的搜索包含“品牌”、“价格”和“年份”，则创建包含所有 3 列的索引。将最具选择性和/或在大多数搜索中提到的列放在前面，然后是其他列。

如果运气好的话，您可以显着缩短许多搜索的响应时间。然后，您可以更深入地研究统计数据并添加一些其他索引。也许 80% 的搜索都会选择“make”，但在其余的情况下，仍然有许多搜索没有“make”，但重点关注“price”和“fuel”，然后为这些搜索创建索引。

当您使用“代码”(例如 Alfa Romeo=1、BMW=2、VW=3...)或值的集群范围(例如 Price_range: 0..999、1000. .2000，...)。这可以帮助 MySQL 构建更高效的索引(较小的索引会减少内存占用和 I/0)。

为了更好地理解索引，请尝试提交这样的查询(我希望使用索引 ix_MakeModelPrice):

-- ix_MakeModelPrice: STATUS`,`MAKE`(25),`MODEL`(25),`PRICE`

SELECT * FROM carads
    where STATUS=1 AND MAKE='Alfa Romeo' 
      AND MODEL='159' and PRICE BETWEEN 100 and 1000
    order by ADID Desc
    LIMIT 0

这个查询应该很快(希望有一些匹配的行)。你知道为什么它这么快吗？ “STATUS”不是选择性的，但其余的应该将索引扫描找到的行数减少到可能远低于 1%。物理读取(行)的数量减少到最小=>更快的响应。