在MySQL中，如何构建索引来加速这个查询？ [英] In MySQL, how to build index to speed up this query?
问题描述
在MySQL中，如何构建索引以加速此查询？
In MySQL, how to build index to speed up this query?
SELECT c1, c2 FROM t WHERE c3='foobar';
推荐答案
要真正给出答案，看看会很有用如果你已经有了现有的索引，但是......
To really give a answer it would be useful to see if you have existing indexes already, but...
所有这一切都假设表't'存在并且你需要添加一个索引而你目前只有一个索引在主键上或根本没有索引。
All this is assuming table 't' exists and you need to add an index and you only currently have a single index on your primary key or no indexes at all.
查询的覆盖索引将为您的需求提供最佳性能，但是对于任何索引，您都会牺牲一些插入速度。牺牲多少取决于您的应用程序的配置文件。如果你主要从表中阅读它并不重要。如果您只有几个索引，即使是适度的写入负载也无关紧要。您桌子的有限存储空间也可能会发挥作用......您需要对权衡做出最终评估，如果它是明显的。好消息是它不断受到打击。通常，添加索引不会以指数方式减慢插入速度，只是线性地减慢。
A covering index for the query will give best performance for your needs, but with any index you sacrifice some insertion speed. How much that sacrifice matters depends on your application's profile. If you read mostly from the table it won't matter much. If you only have a few indexes, even a moderate write load won't matter. Limited storage space for your tables may also come into play... You need to make the final evaluation of the tradeoff and if it is noticable. The good thing is it's fairly a constant hit. Typically, adding an index doesn't slow your inserts exponentially, just linearly.
无论如何，以下是您选择最佳表现的选择：
Regardless, here are your options for best select performance:
- 如果是c3是表t的主键，你不能在查询中做任何更好的事情来使索引更快。
-
假设c1是你的主键t：
- If c3 is your primary key for table t, you can't do anything better in the query to make it faster with an index.
Assuming c1 is your primary key t:
ALTER TABLE t ADD INDEX covering_index (c3,c2);
如果c1不是你的pk（也不是c2），请使用：
If c1 is not your pk (and neither is c2), use this:
ALTER TABLE t ADD INDEX covering_index (c3,c2,c1);
如果c2是你的PK，请使用：
If c2 is your PK use this:
ALTER TABLE t ADD INDEX covering_index (c3,c1);
如果磁盘空间或插入速度有问题，您可以选择点指数。你会牺牲一些性能，但是如果你插入很重，它可能是正确的选择：
If space on disk or insert speed is an issue, you may choose to do a point index. You'll sacrifice some performance, but if you're insert heavy it might the right option:
ALTER TABLE t ADD INDEX a_point_index (c3);
这篇关于在MySQL中，如何构建索引来加速这个查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
