Hash索引和BTREE索引

时间:2022-09-21 19:03:45

索引是数据库中用来提高性能的最常用工具。所有MySql列类型都可以被索引。索引用于快速找出在某个列中有一特定值的行。如果不使用索引,MYSQL必须从第一条记录开始然后读完整个表直到找出相关的行。常用的包括BTREE索引和HASH索引。

创建的语句:

 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] 
on tbl_nam(index_col_name,……)

删除索引语句:

DROP INDEX index_name ON tbl_name

设计索引的规则:

  1. 最适合索引的列是出现在where子句中的列,或连接子句中指定的列,而不是出现在select关键字后的选择列表中的列。
  2. 索引的列的基数越大,索引的效果越好。
  3. 尽量使用短索引。能够节省大量索引空间,也可能使查询更快。
  4. 不要过度索引。索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内容时,索引必须进行更新,有时可能需要重构。
BTREE索引和HASH索引的比较

1、B-Tree索引

索引存储的值按索引列中的顺序排列。可以用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。如果使用索引,必须保证按索引最左边前缀进行查询。由于B树中节点是顺序存储的,可以对查询结果进行order by。

限制:

1)查询必须从索引的最左边的列开始

2)不能跳过某一索引列。

3)存储引擎不能使用索引中范围条件右边的列。

例如,如果你的查询语句为WHERE last_name=”Smith” AND first_name LIKE ‘J%’ AND dob=’1976-12-23’,则该查询只会使用索引中的前两列,因为LIKE是范围查询。

2、Hash索引
MySQL中只有Memory存储引擎显示支持hash索引,是默认索引类型,它也支持B-Tree索引。

如果多个值有相同的hash code,索引把它们的行指针用链表保存到同一个hash表项中。
因为索引自己仅仅存储很短的值,所以索引非常紧凑,hash值不取决于列的数据类型,
int列的索引和长字符串列的索引一样大。

3、总结

  1. HASH索引适合等式比较的操作,不能用来加速order by操作,也不能确定在两个值之间大约有多少行,会影响一些查询的执行效率。而且只能使用整个关键字来搜索一行。
  2. BTREE索引,使用大于,小于,BETWEEN,不等于,LIKE等操作符的时候都可以用。对索引字段进行范围查询的时候,只有BTREE索引可以通过索引访问。HASH索引实际上是全表扫描的。

如果对多列进行索引,列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效查找。

假设存在组合索引it1c1c2(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够
使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。
但是,查询语句select * from t1 where c2=2不能够使用该索引,
因为没有组合索引的引导列,即,要想使用c2列进行查找,必需出现c1等于某值。

索引是在存储引擎中实现的,不是在服务器层中实现的。所以,每种存储引擎的索引都不
一定完全相同,并不是所有的存储引擎都支持所有的索引类型。

高性能索引策略

1、聚簇索引
聚簇索引保证关键字的值相近的元组存储的物理位置也相同(所以字符串类型不宜建立聚簇索引,
特别是随机字符串,会使得系统进行大量的移动操作),且一个表只能有一个聚簇索引。
因为由存储引擎实现索引,所以,并不是所有的引擎都支持聚簇索引。
目前,只有solidDB和InnoDB支持。

2、覆盖索引

索引的使用

1)索引不会包含有null值得列

2)尽量使用短索引,对串列进行索引,如果可能应该指定一个前缀长度。短索引不仅可以提高查询速度而且可以节省磁盘空间和IO操作

3)like语句操作:like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

4)不要在列上进行运算
例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。

使用限制:

1)由于索引仅包含hash code和记录指针,所以,MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的,不会对性能造成太大影响

2)不能使用hash索引排序。

3)Hash索引不支持键的部分匹配,因为是通过整个索引值来计算hash值的。

4)Hash索引只支持等值比较,例如使用=,IN( )和<=>。对于WHERE price>100并不能加速查询。