如何索引该表（a_level、b_level、item_id）
我有一个项目表，每个项目都有一个 a_level、b_level 和一个 item_id。任何 b_level 仅专用于一个 a_level（例如：b_level 14 仅是 a_level 2 的“子级”）
假设我们有数百万个项目，所有这些项目都被 INSERT 一次，然后仅请求 SELECT。
如果我根据 item_id 选择一个项目，那么我需要为 item_id 列建立索引。这将使 MySQL 查找所有数百万个项目，这很糟糕，因为我已经有了 a_level 和 b_level 信息。所以我想如果我根据特定级别选择一个项目并且我在该列上有一个索引，那么MySQL将不必查看所有数百万个项目，而只需查看具有该特定级别的项目。
如果我在a_level、b_level（当然还有item_id）上都进行索引并且SELECT WHERE a_level= b_level= item_id= 会很糟糕吗？我想只有 INDEX on b_level 和 item_id 以及 SELECT WHERE b_level= AND item_id= 就足够了/最好的解决方案？
那么，既然我有 a_level 和 b_level（正如我所说，任何 b_level 都是一个 a_level 的“子级”），那么为选取一项而创建的最有效的 SELECT 和 INDEX 是什么？
I have a table of items, each of it has an a_level, b_level, and an item_id. Any b_level is dedicated to only one a_level (example: b_level 14 is "child" of a_level 2 only)
Lets say we have million of items all of them are INSERTed once and then only SELECTs are requested.
If i SELECT an item based on item_id, then i need to index the item_id column. This will make the MySQL to look all millions of items, which is bad, since i already have the a_level and b_level information. So i guess if i SELECT an item based on a specific level and i have an index on that column, the MySQL will not have to look all millions of items, just the items with that particular level.
If i INDEX both on a_level, b_level (and of course item_id) and SELECT WHERE a_level= b_level= item_id= will it be bad? I guess only INDEX on b_level and item_id and SELECT WHERE b_level= AND item_id= will be enough/the best solution?
So, since i have a_level and b_level (which any b_level as i said is "child" of only one a_level) what will be the most efficient SELECT and INDEXes created for picking up an item?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您当然可以为每一列建立索引。如果这样做，MySQL将使用
索引合并< /code>优化以将多个索引应用于单个查询。但是，为了提高效率，您可能需要使用复合索引（多个列上的单个索引）。 MySQL复合索引在优化时遵循左前缀规则< /a>.如果 SELECT 语句受到索引左前缀中包含的术语的限制，则使用该索引。例如，如果您有，则适当的索引必须包含
a_level或b_level作为第一列。换句话说，(a_level, b_level)的索引可以索引诸如但不能索引
的查询，因为
b_level不是索引的左前缀。您可能首先想要对最常执行的选择进行基准测试，并基于该选择创建索引，只要它们遵循左前缀规则即可。您可能希望对几个不同的 SELECT 查询使用多个索引，以免覆盖整个表。在不准确了解数据和查询的情况下，要完美回答这个问题并不容易。
但是，如果您确定永远不会再次写入表中，并且空间不是问题，您也可以用索引覆盖整个内容。
You can certainly index every column. If you do, MySQL will use
index mergeoptimization to apply many indexes to a single query. However, for more efficiency, you might want to use composite indexes (single index on multiple columns). MySQL composite indexes are used in optimization by following the left-prefix rule. If theSELECTstatement is restricted by terms that are included in a left-prefix of an index, that index is used. For example, if you havethen the appropriate index would have to include
a_levelorb_levelas the first columns. In other words, an index for(a_level, b_level)could index queries such asbut not
because
b_levelis not a left-prefix of the index.You'd probably first want to benchmark which of the selects you're performing most often and create indexes based on that, as long as they follow the left-prefix rule. You might want to use several indexes for a few of the different
SELECTqueries in order to keep from blanketing the entire table. It's not easy to perfectly answer this question without knowing the data and queries exactly.However, if you're sure you're never going to write into the table again, you might as well cover the entire thing with an index, if space is not an issue.
如果您经常按一列或一组列进行选择，则对该列或一组列建立索引。索引不会查找所有数百万个项目，这就是它们是索引的原因（没有索引，它确实会查找所有数百万个项目）
if you do select by a column or set of colums frequently, then index that column or set of columns. Indexes don't look all millions of items, that's why they're indexes (without an index, it would indeed look all millions of items)