是否有任何 MySQL 基准测试工具？[关闭]

Question

Chris Muench

Asked: 2015-08-19 14:30:56 +0800 CST2015-08-19 14:30:56 +0800 CST 2015-08-19 14:30:56 +0800 CST

加速 MySQL 全文搜索

772

我正在对包含 130k+ 记录的表进行复杂的全文搜索。完成需要 0.5 秒。我希望它会快一点。

有什么我可以做的让它更快吗？

下面是查询连同解释+相关表

Query:

SELECT `phppos_items`.*, `phppos_categories`.`name` as category, `phppos_location_items`.`quantity` as quantity, `phppos_location_items`.`reorder_level` as location_reorder_level, `phppos_location_items`.`cost_price` as location_cost_price, `phppos_location_items`.`unit_price` as location_unit_price
FROM (`phppos_items`)
LEFT JOIN `phppos_categories` ON `phppos_categories`.`id` = `phppos_items`.`category_id`
LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and location_id = 1
LEFT JOIN `phppos_additional_item_numbers` ON `phppos_additional_item_numbers`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_items_tags` ON `phppos_items_tags`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_tags` ON `phppos_tags`.`id` = `phppos_items_tags`.`tag_id`
WHERE (MATCH (phppos_items.name, phppos_items.item_number, product_id, description) AGAINST ('test') 
or MATCH(phppos_tags.name) AGAINST ('test') or 
MATCH(phppos_categories.name) AGAINST ('test') or 
MATCH(phppos_additional_item_numbers.item_number) AGAINST ('test'))and phppos_items.deleted=0
LIMIT 20 

EXPLAIN:

| id | select_type | table                          | type   | possible_keys                        | key     | key_len | ref                            | rows  | Extra       |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
|  1 | SIMPLE      | phppos_items                   | ref    | deleted                              | deleted | 4       | const                          | 66679 | NULL        |
|  1 | SIMPLE      | phppos_categories              | eq_ref | PRIMARY                              | PRIMARY | 4       | pos.phppos_items.category_id   |     1 | NULL        |
|  1 | SIMPLE      | phppos_location_items          | eq_ref | PRIMARY,phppos_location_items_ibfk_2 | PRIMARY | 8       | const,pos.phppos_items.item_id |     1 | NULL        |
|  1 | SIMPLE      | phppos_additional_item_numbers | ref    | PRIMARY                              | PRIMARY | 4       | pos.phppos_items.item_id       |     1 | Using index |
|  1 | SIMPLE      | phppos_items_tags              | ref    | PRIMARY                              | PRIMARY | 4       | pos.phppos_items.item_id       |     1 | Using index |
|  1 | SIMPLE      | phppos_tags                    | eq_ref | PRIMARY                              | PRIMARY | 4       | pos.phppos_items_tags.tag_id   |     1 | Using where |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
6 rows in set (0.00 sec)


mysql> show create table phppos_items;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items | CREATE TABLE `phppos_items` (
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `item_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `tax_included` int(1) NOT NULL DEFAULT '0',
  `cost_price` decimal(23,10) NOT NULL,
  `unit_price` decimal(23,10) NOT NULL,
  `promo_price` decimal(23,10) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `reorder_level` decimal(23,10) DEFAULT NULL,
  `expire` date DEFAULT NULL,
  `item_id` int(10) NOT NULL AUTO_INCREMENT,
  `allow_alt_description` tinyint(1) NOT NULL,
  `is_serialized` tinyint(1) NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  `is_service` int(1) NOT NULL DEFAULT '0',
  `commission_percent` decimal(23,10) DEFAULT '0.0000000000',
  `commission_fixed` decimal(23,10) DEFAULT '0.0000000000',
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `item_number` (`item_number`),
  UNIQUE KEY `product_id` (`product_id`),
  KEY `phppos_items_ibfk_1` (`supplier_id`),
  KEY `name` (`name`),
  KEY `deleted` (`deleted`),
  KEY `phppos_items_ibfk_2` (`image_id`),
  KEY `phppos_items_ibfk_3` (`category_id`),
  FULLTEXT KEY `full_search` (`name`,`item_number`,`product_id`,`description`),
  FULLTEXT KEY `name_search` (`name`),
  FULLTEXT KEY `item_number_search` (`item_number`),
  FULLTEXT KEY `product_id_search` (`product_id`),
  FULLTEXT KEY `description_search` (`description`),
  FULLTEXT KEY `size_search` (`size`),
  CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`),
  CONSTRAINT `phppos_items_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`),
  CONSTRAINT `phppos_items_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table phppos_categories;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `deleted` (`deleted`),
  KEY `parent_id_name_index` (`parent_id`,`name`),
  FULLTEXT KEY `name_search` (`name`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table phppos_location_items;
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_location_items | CREATE TABLE `phppos_location_items` (
  `location_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `location` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cost_price` decimal(23,10) DEFAULT NULL,
  `unit_price` decimal(23,10) DEFAULT NULL,
  `promo_price` decimal(23,10) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `quantity` decimal(23,10) DEFAULT '0.0000000000',
  `reorder_level` decimal(23,10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`location_id`,`item_id`),
  KEY `phppos_location_items_ibfk_2` (`item_id`),
  CONSTRAINT `phppos_location_items_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `phppos_locations` (`location_id`),
  CONSTRAINT `phppos_location_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table phppos_additional_item_numbers;
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_additional_item_numbers | CREATE TABLE `phppos_additional_item_numbers` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`item_id`,`item_number`),
  UNIQUE KEY `item_number` (`item_number`),
  FULLTEXT KEY `item_number_search` (`item_number`),
  CONSTRAINT `phppos_additional_item_numbers_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table phppos_items_tags;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items_tags | CREATE TABLE `phppos_items_tags` (
  `item_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`item_id`,`tag_id`),
  KEY `phppos_items_tags_ibfk_2` (`tag_id`),
  CONSTRAINT `phppos_items_tags_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`),
  CONSTRAINT `phppos_items_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `phppos_tags` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table phppos_tags;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_tags | CREATE TABLE `phppos_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_name` (`name`),
  KEY `deleted` (`deleted`),
  FULLTEXT KEY `name_search` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3 个回答

Voted

Rick James · Answer 1 · 2015-08-19T18:46:24+08:00

优化器很少做任何有用的OR构造。

你可以尝试做的是

从ids每个MATCHs
UNION结果
JOIN返回必要的表以获得所需的字段。

是这样的：

SELECT i2.*, c2.name AS category, ...
    FROM (
       SELECT i.id
           FROM phppos_items AS i
           WHERE MATCH(...) AGAINST(...)
       UNION DISTINCT
       SELECT i.id
           FROM phppos_items AS i
           JOIN phppos_categories AS c  ON c.id = i.category_id
           WHERE MATCH(c.name) AGAINST(...)
       UNION DISTINCT
       ...
        ) AS u
    JOIN phppos_items AS i2  ON i2.id = u.id
    LEFT JOIN phppos_categories AS c2  ON c2.id = i.category_id
    ... ;

进一步解释：

每一个都SELECT应该UNION有效地使用FULLTEXT索引。
UNION DISTINCT将消除任何重复。
AND deleted=0可以添加到每个子查询或等到外部查询。
由于JOINs它们“运行相反的方向”，您可能需要添加索引，例如i.category_id. （我没有检查。）
每个内在SELECT的意志（希望）只有很少（或零）的 ID
将UNION有少量的 id。
最后一组JOINs将只对那个小数字起作用，而不是全部 66679；这是加速将发生的地方。
尽管很复杂，但总时间可能会更少。

3manuek · Answer 2 · 2015-08-19T17:25:05+08:00

3manuek

2015-08-19T17:25:05+08:002015-08-19T17:25:05+08:00

我建议减少内部查询中出现的次数phppos_items。一种方法是使用子查询只获取较重的记录。我看到你正在使用LIMIT 20，所以我假设你需要从整体上在线20条记录。文档

为此，您可以使用匹配分数上的条件来修剪工作集。IE：

WHERE [...] (MATCH(phppos_tags.name) AGAINST ('test')) > 0.65

0

Tan Hong Tat · Answer 3 · 2015-08-19T19:58:58+08:00

Tan Hong Tat

2015-08-19T19:58:58+08:002015-08-19T19:58:58+08:00

MySQL FT 搜索没有我们希望的那么快。

我推荐 Sphinx - http://sphinxsearch.com/ - 它提供快速且相关的全文搜索。

如果您想知道 Sphinx 有多快（根据我的经验），使用 MySQL 搜索 200M 记录可能需要几分钟，但使用 Sphinx，不到 2 秒，而且准确性更高。

0

加速 MySQL 全文搜索

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

加速 MySQL 全文搜索

3 个回答

相关问题