Hash索引和BTREE索引2

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

创建的语句：

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

• 1
• 2
• 3

删除索引语句：

DROP INDEX index_name ON tbl_name

• 1
• 2

设计索引的规则：

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并不能加速查询。