mysql - 使用大量行(1,000,000 + 记录)提高性能

Question

下面是我的表格和我运行的一些查询，这些查询需要很长时间(10-40 秒)。我应该添加哪些索引以在不使表太大的情况下提高性能。我还被告知，如果我对类似查询使用“abc%”，我可以使用索引。这是真的吗？

phppos_items

+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| name                  | varchar(255) | NO   |     | NULL    |                |
| category              | varchar(255) | NO   |     | NULL    |                |
| supplier_id           | int(11)      | YES  | MUL | NULL    |                |
| item_number           | varchar(255) | YES  | UNI | NULL    |                |
| description           | varchar(255) | NO   |     | NULL    |                |
| cost_price            | double(15,2) | NO   |     | NULL    |                |
| unit_price            | double(15,2) | NO   |     | NULL    |                |
| quantity              | double(15,2) | NO   |     | 0.00    |                |
| reorder_level         | double(15,2) | NO   |     | 0.00    |                |
| location              | varchar(255) | NO   |     | NULL    |                |
| item_id               | int(10)      | NO   | PRI | NULL    | auto_increment |
| allow_alt_description | tinyint(1)   | NO   |     | NULL    |                |
| is_serialized         | tinyint(1)   | NO   |     | NULL    |                |
| deleted               | int(1)       | NO   |     | 0       |                |
+-----------------------+--------------+------+-----+---------+----------------+

#checking if item exists
SELECT * FROM (`phppos_items`) WHERE `item_id` = 1

#Get all offset + limit, can take 20+ seconds, take longer as offset gets bigger
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 ORDER BY `name` asc LIMIT 16, 16

#Count all non deleted, haven't tested yet bug I would imagine it would take awhile as deleted is not indexed
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0

#Filtering, haven't tested yet, but I would guess it would take a while as there are no indexes on any of these fields
SELECT * FROM (`phppos_items`) WHERE `quantity` <= reorder_level AND `is_serialized` = 1 AND `description` = '' AND `deleted` = 0 ORDER BY `name` asc

#Get info about a particular item. This is pretty fast
SELECT * FROM (`phppos_items`) WHERE `item_id` = 1

#Get info about an item based on item_number, this seems pretty fast
SELECT * FROM (`phppos_items`) WHERE `item_number` = '1234'

#Search queries, very slow
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `name` LIKE '%abc%' ORDER BY `name` asc
SELECT DISTINCT `category` FROM (`phppos_items`) WHERE `deleted` = 0 AND `category` LIKE '%abc%' ORDER BY `category` asc
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `item_number` LIKE '%abc%' ORDER BY `item_number` asc
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `name` LIKE '%abc%' ORDER BY `name` asc
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `item_number` LIKE '%abc%' ORDER BY `item_number` asc
SELECT * FROM (`phppos_items`) WHERE (name LIKE '%abc%' or item_number LIKE '%abc%' or category LIKE '%abc%') and deleted=0 ORDER BY `name` asc LIMIT 16

#Category search, pretty fast
SELECT DISTINCT `category` FROM (`phppos_items`) WHERE `deleted` = 0 AND `category` LIKE '%abc%' ORDER BY `category` asc

#Get Categories, pretty fast
SELECT DISTINCT `category` FROM (`phppos_items`) WHERE `deleted` = 0 ORDER BY `category` asc

Answer 1

您的搜索查询根本没有使用任何索引，也不能对当前查询使用索引。

如果您执行 like '%....%' 则不可能使用索引。

您的选择是:

1. 将您的查询更改为如下内容:like '...%'
2. 使用带有全文搜索的 MyISAM 表
3. 使用单独的全文搜索引擎(Sphinx、Solr 等...)

---

至于你的limit/offset问题。

不要使用 offset，而是尝试使用类似 name > 'previous name' 的东西。尽管类似的东西只有在 name 是唯一的情况下才能正常工作。一般来说，您永远不想使用超过 1000 的 limit/offset，因为数据库将不得不遍历所有这些行。