需要澄清一下MySQL索引

我最近一直在考虑我的数据库索引,过去我只是不带挑衅地把它们作为一种事后考虑,如果它们是正确的,甚至是有帮助的,我从来没有认真考虑过。我读过一些相互矛盾的信息,有人说更多的索引更好,也有人说太多的索引不好,所以我希望在这里得到一些澄清和了解。

假设我有这个假设表:

CREATE TABLE widgets (
    widget_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    widget_name VARCHAR(50) NOT NULL,
    widget_part_number VARCHAR(20) NOT NULL,
    widget_price FLOAT NOT NULL,
    widget_description TEXT NOT NULL
);

我通常会为将要联接的字段和最常排序的字段添加索引:

ALTER TABLE widgets ADD INDEX widget_name_index(widget_name);

所以现在,在查询中,例如:

SELECT w.* FROM widgets AS w ORDER BY w.widget_name ASC

这个 widget_name_index 用于对结果集排序。

现在,如果添加搜索参数:

SELECT w.* FROM widgets AS w 
WHERE w.widget_price > 100.00 
ORDER BY w.widget_name ASC

我想我需要一个新的索引。

ALTER TABLE widgets ADD INDEX widget_price_index(widget_price);

但是,它会同时使用这两个索引吗?据我所知,这不会……

ALTER TABLE widgets ADD INDEX widget_price_name_index(widget_price, widget_name);

现在 widget_price_name_index 将用于选择和排序记录。但是如果我想把它转过来做这个呢:

SELECT w.* FROM widgets AS w 
WHERE w.widget_name LIKE '%foobar%'
ORDER BY w.widget_price ASC

威尔 小部件价格名称索引 用于这个?或者我需要一个 widget_name_price_index 也?

ALTER TABLE widgets ADD INDEX widget_name_price_index(widget_name, widget_price);

如果我有一个搜索框 widget_name , widget_part_number 和 widget_description ?

ALTER TABLE widgets
ADD INDEX widget_search(widget_name, widget_part_number, widget_description);

如果最终用户可以按任何列排序呢?很容易看出,我怎么可能只得到5列的十几个索引。

如果我们添加另一个表:

CREATE TABLE specials (
    special_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    widget_id INT UNSIGNED NOT NULL,
    special_title VARCHAR(100) NOT NULL,
    special_discount FLOAT NOT NULL,
    special_date DATE NOT NULL
);
ALTER TABLE specials ADD INDEX specials_widget_id_index(widget_id);
ALTER TABLE specials ADD INDEX special_title_index(special_title);

SELECT w.widget_name, s.special_title
FROM widgets AS w
INNER JOIN specials AS s ON w.widget_id=s.widget_id
ORDER BY w.widget_name ASC, s.special_title ASC

我想这个会用 widget_id_index 以及 widgets.widget_id 联接的主键索引,但是排序呢?两个都用吗 小部件名称索引 和 special_title_index ?

我不想漫谈太久,有无数的场景我可以理解。显然,这在实际场景中可能会变得更复杂,而不是几个简单的表。如有任何澄清,将不胜感激。