MySQL中的索引大小 [英] Index sizes in MySQL

问题描述

我刚刚开始研究MySQL数据库的优化问题。从我正在阅读的内容来看，索引似乎是一个好主意 - 所以我想在一个表上使用<$ c在我的 VARCHAR 列之一上创建一个索引$ c> MyISAM 引擎。

I am just starting to investigate optimization for my MySQL database. From what I'm reading, indexing seems like a good idea - so I am wanting to create an index on one of my VARCHAR columns, on a table using the MyISAM engine.

从我正在阅读的内容中，我了解索引的大小限制为1,000字节。但是， VARCHAR 字符大小为3个字节。这是否意味着如果我想索引一个包含50行的 VARCHAR 列，我需要一个6个字符的索引前缀？ （每个字符1,000字节/ 50行/ 3字节= 6.66）

From what I'm reading, I understand that an index is limited to a size of 1,000 bytes. A VARCHAR character is 3 bytes in size, though. Does that mean that if I want to index a VARCHAR column with 50 rows, I need an index prefix of 6 characters? (1,000 bytes / 50 rows / 3 bytes per character = 6.66)

如果是这样，那似乎有点复杂 - 这就是为什么我质疑我的理解。看起来很奇怪你只能使用1个字符的前缀在 VARCHAR 列中索引333行。

If so, that seems a little comiplicated - which is why I'm questioning my understanding. It seems pretty odd that you would only be able to index 333 rows in a VARCHAR column, using a prefix of 1 character.

我错过了什么吗？

推荐答案

据我所知，我理解索引的大小限制为1000字节。

From what I've read, I understand that an index is limited to a size of 1000 bytes.

索引键长度限制为 1000 中的字节> MyISAM ， 767 InnoDB （每列）。

Index key length is limited to 1000 bytes in MyISAM, 767 bytes in InnoDB (per column).

这意味着您无法索引单个 UTF8 列 333 字符长 MyISAM 表（计算最大索引大小时， MySQL 假设每个字符 3 字节，但实际长度可能要小得多）

This means that you cannot index a single UTF8 column more than 333 characters long in a MyISAM table (when calculating the max index size, MySQL assumes 3 bytes per character, though actual length may be much smaller)

您可以根据需要创建任意数量的索引记录。

You can create as many indexed records as you need.

这篇关于MySQL中的索引大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！