主页 / dba / 问题 / 158075
Accepted
Chris Muench
Asked: 2016-12-14 11:48:12 +0800 CST

innodb和内存+磁盘效率的多个MYSQL全文索引

  • 772

我有下表：（建表语句）

CREATE TABLE `phppos_people` (
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `comments` text COLLATE utf8_unicode_ci NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `person_id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`person_id`),
  KEY `phppos_people_ibfk_1` (`image_id`),
  FULLTEXT KEY `full_search` (`first_name`,`last_name`,`email`,`phone_number`),
  FULLTEXT KEY `first_name_search` (`first_name`),
  FULLTEXT KEY `last_name_search` (`last_name`),
  FULLTEXT KEY `full_name_search` (`first_name`,`last_name`),
  FULLTEXT KEY `email_search` (`email`),
  FULLTEXT KEY `phone_number_search` (`phone_number`),
  CONSTRAINT `phppos_people_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

我需要一次使用名字、姓氏、电子邮件和电话号码进行快速搜索；而且还能够搜索每个单独的字段。我也喜欢 Btree 无法完成的全文相关性排序

查询运行良好，但根据我的云数据库提供商的说法，他们推荐使用 btree 索引以获得更好的内存和磁盘使用率。

我是否有任何其他选项/配置或方法来创建不使用大量磁盘+内存的索引？每个数据库有大约 40 个遵循类似模式的全文索引。这些索引的性能非常好，并且确实加快了产品速度。

Innodb全文设计是为了占用大量内存+磁盘吗？这是我的mysql变量设置

https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984

这是我的提供商的一些统计数据

经过审查，我们了解了存储在 InnoDB 中的实际数据集大小。当我发现您的数据集大小比您的磁盘使用量小得多时，我有点惊讶，如下所示：

磁盘和表空间使用率之间的这种差异最初让我担心我们有一些孤立的东西，比如旧的临时表，但后来发现这是由InnoDB 全文索引的大量使用引起的。 

PHP-POS-DB-01
  On Disk:                              18GB
  Total DataSet Size:                   4.56GB
  Unused allocated Tablespace:          3.10GB  ## Fragmentation
  Total innodb used plus fragmentation: 7.66GB

PHP-POS-DB-2-01
  On Disk:                              9.7G
  Total DataSet Size:                   923.85MB
  Unused allocated Tablespace:          363.00MB
  Total innodb used plus fragmentation: 1.29GB
mysql innodb

1 个回答

  1. Best Answer

    first_name有一个词，对吗？ FULLTEXT(first_name) is not a use case for that type of indexing. I recommend you use simple任何简单列的索引。

    这工作非常有效，可能比任何可以完成的事情都要快FULLTEXT：

    WHERE last = 'Muench' AND first LIKE 'C%'
INDEX(last, first)

    您可能仍然需要FULLTEXT(first, last, email, phone)处理可能输入其中任何一个的情况。（但是，尚不清楚这是否是一个好的用例。）

    同时，不要盲目地使用(255)所有的字符串列；使用合理的限制。

    WHERE让 UI根据用户提供/不提供的内容构建子句通常是明智的。在您的情况下，这可能包括在 、和之间切换MATCH..AGAINST，甚至可能是。=LIKEREGEXP

    您还可以@查找电子邮件、全数字查找电话等，从而避免在姓氏中搜索[email protected].

    作为替代方案，您可以添加一个额外的列，其中包含名称、电子邮件等的所有“单词”。然后FULLTEXT在该列上有一个索引；不需要 40。缺点是需要维护这些冗余信息。对于足够新的 MySQL 或 MariaDB，它可能是一个“生成的”列。

    • 4

相关问题