blocks|key|307569|text|高效地加载数据：索引加快了检索速度，但减慢了插入和删除操作，以及索引列中值的更新。也就是说，索引会降低大多数涉及写入的操作的速度。之所以会发生这种情况，是因为写入行不仅需要写入数据行，还需要更改任何索引。表的索引越多，需要进行的更改就越多，平均性能降级也就越大。大多数表都会收到很多读操作和很少的写操作，但是对于写入百分比很高的表来说，索引更新的开销可能会很大。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|307570|避免索引：如果您不需要特定的索引来帮助查询更好地执行，请不要创建它。|307571|Disk+Space：一个索引占用磁盘空间，多个索引相应占用更多空间。与没有索引相比，这可能会使您更快地达到表大小限制。尽可能避免索引。|307572|外卖:不要过度索引|307573|entityMap^0|0|7|0|0|4|0|0|A|0|0|9|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|M|$]]

Load Data Efficiently: Indexes speed up retrievals but slow down inserts and deletes, as well as updates of values in indexed columns. That is, indexes slow down most operations that involve writing. This occurs because writing a row requires writing not only the data row, it requires changes to any indexes as well. The more indexes a table has, the more changes need to be made, and the greater the average performance degradation. Most tables receive many reads and few writes, but for a table with a high percentage of writes, the cost of index updating might be significant.

Avoid Indexes: If you don’t need a particular index to help queries perform better, don’t create it.

Disk Space: An index takes up disk space, and multiple indexes take up correspondingly more space. This might cause you to reach a table size limit more quickly than if there are no indexes. Avoid indexes wherever possible.

Takeaway: Don't over index

blocks|key|307575|text|1/2)索引可以加速某些select操作，但会减慢插入、更新和删除等其他操作的速度。这可能是一个很好的平衡。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|307576|3)使用全文索引或sphinx|307577|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|$]]

1/2) Indexes speed up certain select operations but they slow down other operations like insert, update and deletes. It can be a fine balance.

3) use a full text index or perhaps sphinx

blocks|key|3037830|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3037831|如果一个表有六列，并且所有列都是可搜索的，我应该索引所有列还是不索引所有列|blockquote|3037832|3037833|3037834|您是逐个字段进行搜索，还是某些搜索使用多个字段？搜索次数最多的字段是什么？字段类型是什么？(例如，索引在INT上比在VARCHAR上工作得更好)您是否尝试过对正在运行的查询使用EXPLAIN？|3037835|3037836|索引的负面性能影响是什么|3037837|3037838|3037839|更新和插入将变慢。还有额外的存储空间要求，但这在当今通常并不重要。|3037840|3037841|如果我有一个VARCHAR2500列可以从我的站点的某些部分进行搜索，我应该为它编制索引吗|3037842|3037843|3037844|不，除非它是唯一的(这意味着它已经被索引)，或者您只搜索该字段的精确匹配(不使用LIKE或mySQL的全文搜索)。|3037845|3037846|通常，我会使用WHERE子句对要搜索或选择的任何字段建立索引|3037847|3037848|3037849|我通常索引查询次数最多的字段，然后是INT/BOOLEANs/ENUM，而不是VARCHARS类型的字段。不要忘记，您通常需要在组合字段上创建索引，而不是在单个字段上创建索引。使用EXPLAIN，并检查慢日志。|3037850|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|14|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|15|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|16|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|17|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|18|7|@]|8|@]|9|$]]|$1|H|3|-4|4|5|6|19|7|@]|8|@]|9|$]]|$1|I|3|J|4|C|6|1A|7|@]|8|@]|9|$]]|$1|K|3|-4|4|5|6|1B|7|@]|8|@]|9|$]]|$1|L|3|-4|4|5|6|1C|7|@]|8|@]|9|$]]|$1|M|3|N|4|5|6|1D|7|@]|8|@]|9|$]]|$1|O|3|-4|4|5|6|1E|7|@]|8|@]|9|$]]|$1|P|3|Q|4|C|6|1F|7|@]|8|@]|9|$]]|$1|R|3|-4|4|5|6|1G|7|@]|8|@]|9|$]]|$1|S|3|-4|4|5|6|1H|7|@]|8|@]|9|$]]|$1|T|3|U|4|5|6|1I|7|@]|8|@]|9|$]]|$1|V|3|-4|4|5|6|1J|7|@]|8|@]|9|$]]|$1|W|3|X|4|C|6|1K|7|@]|8|@]|9|$]]|$1|Y|3|-4|4|5|6|1L|7|@]|8|@]|9|$]]|$1|Z|3|-4|4|5|6|1M|7|@]|8|@]|9|$]]|$1|10|3|11|4|5|6|1N|7|@]|8|@]|9|$]]|$1|12|3|-4|4|5|6|1O|7|@]|8|@]|9|$]]]|13|$]]

<blockquote>
 If a table has six columns and all of them are searchable, should i index all of them or none of them
</blockquote>

Are you searching on a field by field basis or are some searches using multiple fields?
Which fields are most being searched on?
What are the field types? (Index works better on INTs than on VARCHARs for example)
Have you tried using EXPLAIN on the queries that are being run?

<blockquote>
 What are the negetive performance impacts of indexing
</blockquote>

UPDATEs and INSERTs will be slower. There's also the extra storage space requirments, but that's usual unimportant these days.

<blockquote>
 If i have a VARCHAR 2500 column which is searchable from parts of my site, should i index it
</blockquote>

No, unless it's UNIQUE (which means it's already indexed) or you only search for exact matches on that field (not using LIKE or mySQL's fulltext search).

<blockquote>
 Generally I put an index on any fields that i will be searching or selecting using a WHERE clause
</blockquote>

I'd normally index the fields that are the most queried, and then INTs/BOOLEANs/ENUMs rather that fields that are VARCHARS. Don't forget, often you need to create an index on combined fields, rather than an index on an individual field. Use EXPLAIN, and check the slow log.

blocks|key|305508|text|查看More+Mastering+the+Art+of+Indexing等演示文稿。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|305509|2012年12月更新:我发布了一个新的演示文稿：How+to+Design+Indexes,+Really。我于2012年10月在圣克拉拉的ZendCon上展示了这一点，并于2012年12月在伦敦Percona+Live上展示了这一点。|305510|设计最好的索引是一个必须与应用程序中运行的查询相匹配的过程。|305511|很难推荐任何关于哪些列最适合索引的通用规则，或者您是否应该索引所有列、不索引任何列、哪些索引应该跨越多个列等。这取决于您需要运行的查询。|305512|是的，有一些开销，所以你不应该创建不必要的索引。但是，您应该创建有利于快速运行的查询的索引。索引的开销通常远远大于它的好处。|305513|对于为VARCHAR(2500)的列，您可能希望使用FULLTEXT+index或前缀索引：|305514|CREATE+INDEX+i+ON+SomeTable(longVarchar(100));|code-block|syntax|javascript|305515|请注意，如果您正在搜索可能位于长varchar中间的单词，则传统索引不能提供帮助。为此，请使用全文索引。|305516|entityMap|0|LINK|mutability|MUTABLE|url|https://www.slideshare.net/matsunobu/more-mastering-the-art-of-indexing|1|https://www.slideshare.net/billkarwin/how-to-design-indexes-really|2|https://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html^0|2|Y|0|0|O|T|1|0|0|0|0|Q|E|2|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@$A|17|B|18|1|19]]|C|$]]|$1|D|3|E|5|6|7|1A|8|@]|9|@$A|1B|B|1C|1|1D]]|C|$]]|$1|F|3|G|5|6|7|1E|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|1F|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1G|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1H|8|@]|9|@$A|1I|B|1J|1|1K]]|C|$]]|$1|N|3|O|5|P|7|1L|8|@]|9|@]|C|$Q|R]]|$1|S|3|T|5|6|7|1M|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1N|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]|12|$5|X|Y|Z|C|$10|13]]|14|$5|X|Y|Z|C|$10|15]]]]

Check out presentations like <a href="https://www.slideshare.net/matsunobu/more-mastering-the-art-of-indexing" rel="noreferrer">More Mastering the Art of Indexing</a>.

Update 12/2012: I have posted a new presentation of mine: <a href="https://www.slideshare.net/billkarwin/how-to-design-indexes-really" rel="noreferrer">How to Design Indexes, Really</a>. I presented this in October 2012 at ZendCon in Santa Clara, and in December 2012 at Percona Live London.

Designing the best indexes is a process that has to match the queries you run in your app.

It's hard to recommend any general-purpose rules about which columns are best to index, or whether you should index all columns, no columns, which indexes should span multiple columns, etc. It depends on the queries you need to run.

Yes, there is some overhead so you shouldn't create indexes needlessly. But you should create the indexes that give benefit to the queries you need to run quickly. The overhead of an index is usually far outweighed by its benefit.

For a column that is VARCHAR(2500), you probably want to use a <a href="https://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html" rel="noreferrer">FULLTEXT index</a> or a prefix index:

<pre><code>CREATE INDEX i ON SomeTable(longVarchar(100));
</code></pre>

Note that a conventional index can't help if you're searching for words that may be in the middle of that long varchar. For that, use a fulltext index.

blocks|key|3037859|text|我不会在其他答案中重复一些好的建议，但会补充：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3037860|复合指数|offset|length|style|BOLD|3037861|您可以创建复合索引-包含多个列的索引。MySQL可以使用这些从left到right。所以，如果你有：|3037862|Table+A
Id
Name
Category
Age
Description|code-block|syntax|javascript|3037863|如果您的复合索引按顺序包含Name/Category/Age，则这些WHERE子句将使用该索引：|3037864|WHERE+Name='Eric'+and+Category='A'

WHERE+Name='Eric'+and+Category='A'+and+Age+>+18|3037865|但|3037866|WHERE+Category='A'+and+Age+>+18|3037867|不会使用该索引，因为所有内容都必须从左到右使用。|3037868|解释|3037869|使用Explain+/+Explain扩展来了解哪些索引可用于MySQL，以及它实际选择了哪些索引。MySQL将在每个查询中仅使用一个密钥。|3037870|EXPLAIN+EXTENDED+SELECT+*+from+Table+WHERE+Something='ABC'|3037871|慢查询日志|3037872|打开slow+query+log以查看哪些查询运行速度较慢。|3037873|Wide+Columns|3037874|如果您有一个宽列，其中大部分区别发生在前几个字符中，则只能在索引中使用前N个字符。示例:我们有一个定义为varchar(255)的ReferenceNumber列，但是在97%25的情况下，引用数字是10个字符或更少。我更改了索引，只查看前10个字符，性能也有了很大提高。|3037875|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html^0|0|0|4|0|V|4|10|5|0|0|0|0|0|0|0|0|2|0|1T|2|0|0|0|5|0|2|E|0|0|0|C|0|0^^$0|@$1|2|3|4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1L|8|@$D|1M|E|1N|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|1O|8|@$D|1P|E|1Q|F|G]|$D|1R|E|1S|F|G]]|9|@]|A|$]]|$1|J|3|K|5|L|7|1T|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1U|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1V|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1W|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|1X|8|@]|9|@]|A|$M|N]]|$1|W|3|X|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1Z|8|@$D|20|E|21|F|G]]|9|@]|A|$]]|$1|10|3|11|5|6|7|22|8|@$D|23|E|24|F|G]]|9|@]|A|$]]|$1|12|3|13|5|L|7|25|8|@]|9|@]|A|$M|N]]|$1|14|3|15|5|6|7|26|8|@$D|27|E|28|F|G]]|9|@]|A|$]]|$1|16|3|17|5|6|7|29|8|@]|9|@$D|2A|E|2B|1|2C]]|A|$]]|$1|18|3|19|5|6|7|2D|8|@$D|2E|E|2F|F|G]]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|2G|8|@]|9|@]|A|$]]|$1|1C|3|-4|5|6|7|2H|8|@]|9|@]|A|$]]]|1D|$1E|$5|1F|1G|1H|A|$1I|1J]]]]

I won't repeat some of the good advice in other answers, but will add:

Compound Indices

You can create compound indices - an index that includes multiple columns. MySQL can use these from left to right. So if you have:

<pre><code>Table A
Id
Name
Category
Age
Description
</code></pre>

if you have a compound index that includes Name/Category/Age in that order, these WHERE clauses would use the index:

<pre><code>WHERE Name='Eric' and Category='A'

WHERE Name='Eric' and Category='A' and Age &gt; 18
</code></pre>

but 

<pre><code>WHERE Category='A' and Age &gt; 18
</code></pre>

would not use that index because everything has to be used from left to right.

Explain

Use Explain / Explain Extended to understand what indices are available to MySQL and which one it actually selects. MySQL will only use ONE key per query.

<pre><code>EXPLAIN EXTENDED SELECT * from Table WHERE Something='ABC'
</code></pre>

Slow Query Log

Turn on the <a href="http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html" rel="noreferrer">slow query log</a> to see which queries are running slow.

Wide Columns

If you have a wide column where MOST of the distinction happens in the first several characters, you can use only the first N characters in your index. Example: We have a ReferenceNumber column defined as varchar(255) but 97% of the cases, the reference number is 10 characters or less. I changed the index to only look at the first 10 characters and improved performance quite a bit.

blocks|key|304726|text|你绝对应该花一些时间阅读索引，有很多关于它的文章，了解正在发生的事情是很重要的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|304727|从广义上讲，索引对表中的行进行排序。|304728|为了简单起见，假设一个表只是一个很大的CSV文件。每当插入一行时，它都会插入到末尾。因此，表的“自然”顺序就是插入行的顺序。|304729|假设您已经在一个非常初级的电子表格应用程序中加载了CSV文件。这个电子表格所做的一切就是显示数据，并按顺序对行进行编号。|304730|现在假设您需要查找第三列中具有值"M“的所有行。考虑到您拥有的可用资源，您只有一个选择。您可以扫描该表，检查每行第三列的值。如果你有很多行，这种方法(“表扫描”)可能需要很长时间！|304731|现在想象一下，除了这个表之外，您还有一个索引。这个特定的索引是第三列中值的索引。索引以某种有意义的顺序(例如，按字母顺序)列出第三列中的所有值，并为每个值提供出现该值的行号列表。|304732|现在，您有了一个很好的策略来查找第三列的值为"M“的所有行。例如，您可以执行binary+search！表扫描需要查看N行(其中N是行数)，而二进制搜索在最坏的情况下只需要查看log-n个索引项。哇，那肯定容易多了！|offset|length|304733|当然，如果您有这个索引，并且要向表中添加行(最后，因为这就是我们的概念表的工作方式)，那么每次都需要更新索引。所以当你写新行的时候，你需要做更多的工作，但是当你在搜索的时候，你节省了大量的时间。|304734|因此，通常情况下，索引会在读取效率和写入效率之间进行权衡。在没有索引的情况下，插入可以非常快--数据库引擎只向表中添加一行。添加索引时，引擎必须在执行插入操作时更新每个索引。|304735|另一方面，读取变得更快了。|304736|希望这涵盖了您的前两个问题(正如其他人已经回答的那样--您需要找到合适的平衡点)。|304737|您的第三个场景稍微复杂一些。如果你使用LIKE，索引引擎通常会帮助你将读取速度提高到第一个"%25“。换句话说，如果您是SELECTing+WHERE列，如'foo%25bar%25'，数据库将使用索引来查找列以"foo“开头的所有行，然后需要扫描该中间行集以查找包含"bar”的子集。选择...像'%25bar%25‘这样的WHERE列不能使用索引。我希望你能明白为什么。|304738|最后，您需要开始考虑多个列上的索引。概念是相同的，其行为类似于类似的东西--本质上，如果您在(a，b，c)上有一个索引，引擎将尽可能地从左到右继续使用该索引。因此，对列a的搜索可能使用(a，b，c)索引，对(a，b)的搜索也是如此。但是，如果您要搜索b=5和c=1的位置，引擎将需要执行全表扫描)|304739|希望这有助于阐明一些事情，但我必须重申，您最好花几个小时挖掘一些深入解释这些事情的好文章。阅读特定数据库服务器的文档也是一个好主意。查询规划者实现和使用索引的方式可能有很大的不同。|304740|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Binary_search_algorithm^0|0|0|0|0|0|0|12|D|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1C|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1D|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1E|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1F|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1G|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1H|8|@]|9|@$N|1I|O|1J|1|1K]]|A|$]]|$1|P|3|Q|5|6|7|1L|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1M|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1N|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1O|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|11|3|12|5|6|7|1R|8|@]|9|@]|A|$]]|$1|13|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|14|$15|$5|16|17|18|A|$19|1A]]]]

You should definitely spend some time reading up on indexing, there's a lot written about it, and it's important to understand what's going on.

Broadly speaking, an index imposes an ordering on the rows of a table. 

For simplicity's sake, imagine a table is just a big CSV file. Whenever a row is inserted, it's inserted at the end. So the "natural" ordering of the table is just the order in which rows were inserted.

Imagine you've got that CSV file loaded up in a very rudimentary spreadsheet application. All this spreadsheet does is display the data, and numbers the rows in sequential order.

Now imagine that you need to find all the rows that have some value "M" in the third column. Given what you have available, you have only one option. You scan the table checking the value of the third column for each row. If you've got a lot of rows, this method (a "table scan") can take a long time!

Now imagine that in addition to this table, you've got an index. This particular index is the index of values in the third column. The index lists all of the values from the third column, in some meaningful order (say, alphabetically) and for each of them, provides a list of row numbers where that value appears.

Now you have a good strategy for finding all the rows where the value of the third column is "M". For instance, you can perform a <a href="http://en.wikipedia.org/wiki/Binary_search_algorithm" rel="noreferrer">binary search</a>! Whereas the table scan requires you to look N rows (where N is the number of rows), the binary search only requires that you look at log-n index entries, in the very worst case. Wow, that's sure a lot easier!

Of course, if you have this index, and you're adding rows to the table (at the end, since that's how our conceptual table works), you need to update the index each and every time. So you do a little more work while you're writing new rows, but you save a ton of time when you're searching for something.

So, in general, indexing creates a tradeoff between read efficiency and write efficiency. With no indexes, inserts can be very fast -- the database engine just adds a row to the table. As you add indexes, the engine must update each index while performing the insert.

On the other hand, reads become a lot faster. 

Hopefully that covers your first two questions (as others have answered -- you need to find the right balance).

Your third scenario is a little more complicated. If you're using LIKE, indexing engines will typically help with your read speed up to the first "%". In other words, if you're SELECTing WHERE column LIKE 'foo%bar%', the database will use the index to find all the rows where column starts with "foo", and then need to scan that intermediate rowset to find the subset that contains "bar". SELECT ... WHERE column LIKE '%bar%' can't use the index. I hope you can see why.

Finally, you need to start thinking about indexes on more than one column. The concept is the same, and behaves similarly to the LIKE stuff -- essentially, if you have an index on (a,b,c), the engine will continue using the index from left to right as best it can. So a search on column a might use the (a,b,c) index, as would one on (a,b). However, the engine would need to do a full table scan if you were searching WHERE b=5 AND c=1)

Hopefully this helps shed a little light, but I must reiterate that you're best off spending a few hours digging around for good articles that explain these things in depth. It's also a good idea to read your particular database server's documentation. The way indices are implemented and used by query planners can vary pretty widely.

blocks|key|305541|text|通常，索引有助于加速数据库搜索，其缺点是使用额外的磁盘空间和减慢INSERT+/+UPDATE+/+DELETE查询。使用EXPLAIN并阅读结果，以了解MySQL何时使用您的索引。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|305542|305543|如果一个表有6列，并且所有列都是可搜索的，我应该索引所有列还是不索引所有列？|blockquote|305544|305545|305546|对所有六列进行索引并不总是最佳实践。|305547|(a)在搜索特定信息时，您是否会使用这些列中的任何一列？|305548|(b)这些列的选择性是什么(与表中记录的总量相比，存储了多少不同的值)？|305549|MySQL使用基于成本的优化器，该优化器在执行查询时试图找到“最便宜”的路径。具有低选择性的字段不是很好的候选者。|305550|305551|索引的负面性能影响是什么？|305552|305553|305554|已经回答过了:额外的磁盘空间，在插入-更新-删除过程中性能较低。|305555|305556|如果我有一个VARCHAR2500列，可以从我的站点的某些部分进行搜索，我应该对它进行索引吗？|305557|305558|305559|试试FULLTEXT+Index吧。|305560|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html^0|W|6|15|6|1E|6|1P|7|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|E|0|0^^$0|@$1|2|3|4|5|6|7|1F|8|@$9|1G|A|1H|B|C]|$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]|$9|1M|A|1N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|1P|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|1Q|8|@]|D|@]|E|$]]|$1|K|3|-4|5|6|7|1R|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|1S|8|@]|D|@]|E|$]]|$1|N|3|O|5|6|7|1T|8|@]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1U|8|@]|D|@]|E|$]]|$1|R|3|S|5|6|7|1V|8|@]|D|@]|E|$]]|$1|T|3|-4|5|6|7|1W|8|@]|D|@]|E|$]]|$1|U|3|V|5|I|7|1X|8|@]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1Y|8|@]|D|@]|E|$]]|$1|X|3|-4|5|6|7|1Z|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|20|8|@]|D|@]|E|$]]|$1|10|3|-4|5|6|7|21|8|@]|D|@]|E|$]]|$1|11|3|12|5|I|7|22|8|@]|D|@]|E|$]]|$1|13|3|-4|5|6|7|23|8|@]|D|@]|E|$]]|$1|14|3|-4|5|6|7|24|8|@]|D|@]|E|$]]|$1|15|3|16|5|6|7|25|8|@]|D|@$9|26|A|27|1|28]]|E|$]]|$1|17|3|-4|5|6|7|29|8|@]|D|@]|E|$]]]|18|$19|$5|1A|1B|1C|E|$1D|1E]]]]

In general, indices help speedup database search, having the disadvantage of using extra disk space and slowing <code>INSERT</code> / <code>UPDATE</code> / <code>DELETE</code> queries. Use <code>EXPLAIN</code> and read the results to find out when MySQL uses your indices.

<blockquote>
 If a table has six columns and all of them are searchable, should i index all of them or none of them?
</blockquote>

Indexing all six columns isn't always the best practice.

(a) Are you going to use any of those columns when searching for specific information?

(b) What is the selectivity of those columns (how many distinct values are there stored, in comparison to the total amount of records on the table)?

MySQL uses a cost-based optimizer, which tries to find the "cheapest" path when performing a query. And fields with low selectivity aren't good candidates.

<blockquote>
 What are the negetive performance impacts of indexing?
</blockquote>

Already answered: extra disk space, lower performance during insert - update - delete.

<blockquote>
 If i have a VARCHAR 2500 column which is searchable from parts of my site, should i index it?
</blockquote>

Try the <a href="http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html" rel="noreferrer">FULLTEXT Index</a>.

I've been using indexes on my MySQL databases for a while now but never properly learnt about them. Generally I put an index on any fields that I will be searching or selecting using a <code>WHERE</code> clause but sometimes it doesn't seem so black and white.

What are the best practices for MySQL indexes?

Example situations/dilemmas:

<ul>
<li>If a table has six columns and all of them are searchable, should I index all of them or none of them?</li>
<li>What are the negative performance impacts of indexing?</li>
<li>If I have a VARCHAR 2500 column which is searchable from parts of my site, should I index it?</li>
</ul>

MySQL indexes - what are the best practices?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我在我的MySQL数据库上使用索引已经有一段时间了，但是从来没有正确地学习过它们。通常，我会使用WHERE子句将索引放在要搜索或选择的任何字段上，但有时看起来并不是那么黑白分明。 MySQL索引的最佳实践是什么？示例情况/困境：如果一个表有六列，并且所有列都是可搜索的，我应该为它们全部编制索引，还是不编制索引？编制索引...

问MySQL索引-最佳实践是什么？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL索引-最佳实践是什么？EN