B-tree目录和hash索引
日期：2014-05-16 浏览次数：20539 次
B-tree索引和hash索引
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.
1、The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
2、Supports equality and range searched,multiple attribute keys and partial key searches
3、Either a separate index or the basis for a storage structure
4、Responds to dynamic changes in the table
Hash index——problems
1、Does not support range search
2、Although it supports multiple attribute keys,it does not support partial key search.
3、Dynamically growing files produce overflow chains,which negate the efficiency of the algorithm.
Choosing An index
An index should support a query of the application that has the most impact on performance
Choice based on frequency of invocation、 execution time 、acquired locks、 table size
参考资料：
http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
http://stackoverflow.com/questions/7306316/btree-vs-hashtable
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.
1、The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
2、Supports equality and range searched,multiple attribute keys and partial key searches
3、Either a separate index or the basis for a storage structure
4、Responds to dynamic changes in the table
引用
Hash indexes have some what different characteristics from those just discussed:
*
They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.
*
The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
*
MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table.
*
Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
*
They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.
*
The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
*
MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table.
*
Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
Hash index——problems
1、Does not support range search
2、Although it supports multiple attribute keys,it does not support partial key search.
3、Dynamically growing files produce overflow chains,which negate the efficiency of the algorithm.
Choosing An index
An index should support a query of the application that has the most impact on performance
Choice based on frequency of invocation、 execution time 、acquired locks、 table size
参考资料：
http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
http://stackoverflow.com/questions/7306316/btree-vs-hashtable
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
