blocks|key|1477183|text|索引的“最佳”数量并没有什么神奇的数字。基本规则是:为经常使用和/或需要快速运行的查询添加索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1477184|拥有“太多”的索引不应该减慢查询的速度，但是添加每个索引会增加少量的时间来添加/更新数据库中的项(因为它也会修改索引)，以及少量的空间。但是，如果只是根据需要添加索引，这可能不是一个大问题。|1477185|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

There's no magic number for the "best" number of indexes. The basic rule is this: add indexes for queries that are used often and/or need to run quickly.

Having "too many" indexes shouldn't slow down queries, but it each index added adds a small amount of time to add/update items in the db (since it modifies the indices as well), and a small amount of space. However, if you're just adding indexes as required, this is probably not a big concern.

blocks|key|1477221|text|作为一般规则，您应该在所有主键(您在这方面没有选择)、所有外键以及您经常用来获取行的任何其他字段上都有索引。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1477222|例如，如果我通常通过用户名查找用户，即使用户ID是主键，我也会对其进行索引。|1477223|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

As a general rule, you should have indexes on all primary keys (you don't have a choice in that), all foreign keys, and any other fields you commonly use to fetch rows.

For example, if I commonly look up users by username, I would have that indexed, even if user ID was the primary key.

blocks|key|1262708|text|索引可以加快SELECT查询的速度，但会降低INSERT/+update+/DELETE查询的速度，因为它们还需要更新索引，而不仅仅是行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1262709|这只是我个人的观点(我没有任何事实支持它)，但是我认为，如果有一个查询需要很长时间，而索引会加速它，那就去做吧！“太多”的索引将是如果你添加的索引没有任何好处(例如，没有查询，它会加速)。例如，一件愚蠢的事情就是“仅仅因为”在每一列上放置一个索引。|1262710|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

An index can speed up a SELECT query, but it will slow down INSERT/UPDATE/DELETE queries because they need to update the index as well, not just the row.

This is just personal opinion (I've got no facts to back it up), but I think that if there is a query that is taking a long time and an index would speed it up - go for it! "Too many" indexes would be if you added indexes that didn't do any good (e.g. there were no queries it would speed up). For example, a silly thing to do would be to place an index on every column "just because".

blocks|key|1477386|text|索引的数量和做得太多的线将取决于许多因素。在像“类别”表这样的小型表中，您通常不想要或不需要索引，这实际上会影响性能。原因是读取索引需要I/O+(即时间)，然后需要更多的I/O和时间来检索与匹配的行相关联的记录。一个例外情况是，您只查询索引中包含的列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1477387|在您的示例中，您将检索所有列，并且只有22行，如果只进行一次表扫描并对这些列进行排序，而不是使用索引，可能会更快。优化器可能/应该这样做，并忽略索引。如果是这种情况，那么索引只会占用空间而没有任何好处。如果你的"categories“表经常被访问，你可能会考虑把它固定在内存中，这样db服务器就可以让它保持可访问性，而不必一直访问磁盘。|1477388|在添加索引时，您需要平衡磁盘空间、查询性能以及更新和插入表的性能。与每天有数百万次更新的表相比，您可以在静态且不会发生太多更改的表上使用更多索引。从那时起，您将开始感受到索引维护的影响。但是，在您的环境中什么是可接受的，并且只能由您和您的组织决定。|1477389|在进行分析时，请确保生成/更新表和索引统计信息，以便确保计算准确。|1477390|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

The amount of indexing and the line of doing too much will depend on a lot of factors. On small tables like your "categories" table you usually don't want or need an index and it can actually hurt performance. The reason being is that it takes I/O (i.e. time) to read an index and then more I/O and time to retrieve the records associated with the matched rows. An exception being when you only query the columns contained within the index.

In your example you are retrieving all the columns and with only 22 rows and it may be faster to just do a table scan and sort those instead of using the index. The optimizer may/should be doing this and ignoring the index. If that is the case, then the index is just taking up space with no benefit. If your "categories" table is accessed often, you may want to consider pinning it into memory so the db server keeps it accessible without having to goto the disk all the time.

When adding indexes you need to balance out disk space, query performance, and the performance of updating and inserting into the tables. You can get away with more indexes on tables that are static and don't change much as opposed to tables with millions of updates a day. You'll start feeling the affects of index maintenance at that point. What is acceptable in your environment though is and can only be determined by you and your organization.

When doing your analysis, be sure to generate/update your table and index statistics so that you can be assured of accurate calculations.

I'm trying to fine-tune my MySQL server so I check my settings, analyzing slow-query log, and simplify my queries if possible.

Sometimes it is enough if I am indexing correctly, sometimes not. I've read somewhere (please correct me if this is stupidity) that more indexes than I need make the same effect, like if I don't have any of indexes.

How many indexes are enough? You can say it depends on hundreds of factors, but I'm curious about how can I clean up my <code>mysql-slow.log</code> enough to reduce server load.

Furthermore, I saw some "interesting" log entries like this:

<pre><code># Query_time: 0 Lock_time: 0 Rows_sent: 22 Rows_examined: 44
SELECT * FROM `categories` ORDER BY `orderid` ASC;
</code></pre>

The table in question contains exactly 22 rows, index set in <code>orderid</code>. Why is this query showing up in the log after all? Why examine 44 rows if it only contains 22?

MySQL indexes - how many are enough?

数据库

服务器

MySQL

我正在尝试微调我的MySQL服务器，以便我检查我的设置，分析慢查询日志，如果可能的话，简化我的查询。有时候如果我的索引正确就足够了，有时候不是。我在某处读到过(如果这是愚蠢的，请纠正我)，比我需要的更多的索引会产生同样的效果，就像我没有任何索引一样。多少个索引就足够了？你可以说这取决于成百上千的因素，但我很好奇如何才能...

问MySQL索引--多少个就足够了？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL索引--多少个就足够了？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL索引--多少个就足够了？
EN