MySQL BTREE的优势？

Advantage of BTREE?

提问by shantanuo

I create indexes without the USING BTREEclause. Is there any advantage of using BTREEindex?

我在没有USING BTREE子句的情况下创建索引。使用BTREE索引有什么好处吗？

CREATE INDEX `SomeName` USING BTREE ON `tbl_Name`(`column_name`);

采纳答案by yk4ever

BTREE is the default index method. You may safely omit it.

BTREE 是默认的索引方法。您可以放心地省略它。

回答by mjv

First off, depending on the Storage Engine used, you may just not have a choice (InnoDB for example is exclusively using BTREE for its index).

首先，根据所使用的存储引擎，您可能没有选择（例如 InnoDB 专门使用 BTREE 作为其索引）。

Also, BTREE is the default index type for most storage engines.

此外，BTREE 是大多数存储引擎的默认索引类型。

Now... There are cases, when using alternative index types may result in improved performance. There are (relatively rare case) when a HASH index may help. Note that when a HASH index is created, a BTREE index is also produced. That's in part due to the fact that hash indexes can only resolve equality predicates. (a condition such as WHERE Price > 12.0 could not be handled by a hash index).

现在... 在某些情况下，使用替代索引类型可能会提高性能。在某些情况下（相对罕见的情况），HASH 索引可能会有所帮助。请注意，当创建 HASH 索引时，也会生成一个 BTREE 索引。这部分是因为哈希索引只能解析相等谓词。（散列索引无法处理诸如 WHERE Price > 12.0 之类的条件）。

In short: Keep using BTREE, whether implicitly (if BTREE is the default for the Storage used), or explicitly. Learn about the other types of indexes so that you know about them would the need arise.

简而言之：继续使用 BTREE，无论是隐式（如果 BTREE 是所用存储的默认值），还是显式。了解其他类型的索引，以便在需要时了解它们。

Edit:(in searching cases when alternate index types may be used)
Effectively the case is rather straight forward for RTREEindexes. These are only supported, with MySQL, in the context of "SPATIAL" databases, i.e. databases which include Geo position context such as Point and other object in the GIS model).

编辑：（在可能使用替代索引类型的搜索情况下）
实际上，RTREE索引的情况相当简单。这些仅在 MySQL 的“空间”数据库上下文中受支持，即包含地理位置上下文（例如 GIS 模型中的点和其他对象）的数据库。

HASH indexes are more generic (not limited to a particular application or data type), and one can generally follow one's intuitive understanding of hashes to get a hint as to when these may outperform the old-but-faithful BTREE. As indicated earlier, this would imply columns typically searched with an equal predicate. I'm guessing relatively short lookup tables and the like could benefit, depending on the effective implementation within MySQL.

HASH 索引更通用（不限于特定的应用程序或数据类型），通常可以根据自己对哈希的直观理解来获得有关何时这些可能会胜过旧但忠实的 BTREE 的提示。如前所述，这意味着通常使用相等谓词搜索列。我猜测相对较短的查找表等可能会受益，具体取决于 MySQL 中的有效实现。

回答by RC.

It depends on which storage engine you're using. For most, BTREE is the default so specifying it doesn't really change anything. For storage engines such as MEMORY/HEAP and NDB, the default is to use HASH indexes by default.

这取决于您使用的存储引擎。大多数情况下，BTREE 是默认值，因此指定它并不会真正改变任何东西。对于 MEMORY/HEAP 和 NDB 等存储引擎，默认使用 HASH 索引。

More information can be found here.

可以在此处找到更多信息。

Whether or not a B-tree or a HASH index is advantageous for you from a performance perspective depends on the data and how you're accessing it. If you know you're queries are going to target exactly one row or scattered individual rows, then a HASH index may be useful. Anything other than that, I generally prefer a BTREE index as the data is sorted and thus makes range queries and those that return multi-rows more efficient.

从性能角度来看，B 树或 HASH 索引是否对您有利取决于数据以及您访问它的方式。如果您知道您的查询将精确定位到一行或分散的单个行，那么 HASH 索引可能会很有用。除此之外，我通常更喜欢 BTREE 索引，因为数据已排序，从而使范围查询和返回多行的查询更加高效。

回答by Vaishali Bhardwaj

searching a balanced tree means that all the leaves are at the same depth. There is no runway pointer overhead. Indeed,even larger B-trees can guarantee a small number of nodes must be retrieved to find a given key. For example, a B-tree of 10,000,000 keys with 50 keys per node never needs to retrieve more than 4 nodes to find any key. A B-tree is a special data structure format for an index that allows rapid access of the data in the index.One of the properties of this data structure is that the index is always balances.That means each node at the lowest level is equidistant from the top most node, or the root node of the tree.And each side of the index has the same number of nodes.The nodes at the lowest levels are known as leaf nodes.All other nodes are known as branch nodes.Branches points to other branches or leaf nodes.Leaf nodes store the values of the indexed columns and the rowid that points to the distinct row that has those values. The actual distribution will depend on the number of data values in each range of values in a B-tree with overall goal to reduce the number of required levels that must be traversed to get to a specific value. The advantage of a B-tree structure are:

搜索平衡树意味着所有的叶子都在相同的深度。没有跑道指针开销。事实上，即使更大的 B 树也可以保证必须检索少量节点才能找到给定的键。例如，每个节点有 50 个键的 10,000,000 个键的 B 树永远不需要检索超过 4 个节点来找到任何键。B树是索引的一种特殊的数据结构格式，它允许快速访问索引中的数据。这种数据结构的一个特性是索引总是平衡的。这意味着最底层的每个节点都是等距的从最顶部的节点，或树的根节点。索引的每一侧都有相同数量的节点。最低级别的节点称为叶节点。所有其他节点称为分支节点。分支点到其他分支或叶节点。叶节点存储索引列的值和指向具有这些值的不同行的 rowid。实际分布将取决于 B 树中每个值范围中的数据值数量，其总体目标是减少必须遍历以获得特定值的所需级别的数量。B树结构的优点是：

1. All leaf blocks are of the same depth(number of values).
2. The height of the B-tree is typically pretty small.In some cases,the root node is the only leaf node and the height is 1.As the tables get more rows inserted into it,the index must grow to accommodate this.But even in tables with over 1 million rows,the B-tree idex typically has a height 3.In the very largest of tables , the height may only be 4.This means that for even the largest tables,it only takes 4 blocks to find the rowid of the row you are looking for,This is extremely efficient.
3. In the cases of randomly entered data,the B-tree stays balances automatically.In fact, the B-tree stays balances no matter what data is entered to it.
4. All blocks of a B-tree index are three quarters full(on the average),allowing insertion without rebulid. 5.B-tree provide excellent performance for all types of selects. 6.Insert,update and deleted tend to be efficient in a B-tree structure. 7.B-tree performance stays optimal even when tables from small to large.

1. 所有叶块都具有相同的深度（值的数量）。
2. B 树的高度通常非常小。在某些情况下，根节点是唯一的叶节点，高度为 1。随着表中插入更多行，索引必须增长以适应这一点。但即使在超过 100 万行的表中，B 树 idex 通常具有 3 的高度。在最大的表中，高度可能只有 4。这意味着即使对于最大的表，也只需要 4 个块即可找到您要查找的行的rowid，这是非常有效的。
3. 在随机输入数据的情况下，B-tree会自动保持平衡。实际上，无论输入什么数据，B-tree都会保持平衡。
4. B 树索引的所有块都是四分之三满（平均），允许插入而无需重新构建。5.B-tree 为所有类型的选择提供出色的性能。6.插入、更新和删除在B树结构中往往是高效的。7.B-tree 性能保持最佳，即使表从小到大。

回答by Nilesh Barai

The simplified answer is, if your SQL is using a LIKE statement on that field then using BTREE index should outperform a Hash Index. If you are using equal to (=) statements against that field stay with Hash Index.

简化的答案是，如果您的 SQL 在该字段上使用 LIKE 语句，那么使用 BTREE 索引应该优于哈希索引。如果您对该字段使用等于 (=) 语句，请使用哈希索引。