mysql索引

Chapter 4. Indexes 索引有助于MySQL在浩如烟海的数据中迅速找到和取得正确的记录。 4.1 Indexing Basics 4.1.1 Index Concepts 索引是按照某种规则排序的。索引也就是牺牲一些空间和cpu的开销来使你的查询跑得更快点。另外提醒的是，对于MySQL来讲，术语“Key”和 “Index”是可以互相替换的。 4.1.1.1 Partial indexes 索引是以空间换取性能。但有时候不想耗费太多的空间的话，MySQL有一系列控制索引空间的方法。例如你可以控制索引字节，采取部分索引的方法，当然这样性能会降低一些。 ALTER TABLE phonebook ADD INDEX (lastname(4)) 4.1.1.2 Multicolumn indexes 使用多列索引的原因在于：MySQL will only ever use one index per table per query。 4.1.1.3 Index order MySQL无需人工干预索引的排序，它会处理得很好。 4.1.1.4 Indexes as constraints 唯一索引（unique index）标明在给定的列里特值只能出现一次。唯一索引有两个作用：一个就是帮助查询迅速定位；二就是当插入或更新时，保证列里的值的唯一性。这就意味着，在这里，唯一索引起到了约束的作用。 ALTER TABLE phonebook ADD UNIQUE (phonenumber) 4.1.1.5 Clustered and secondary indexes 聚集索引（clustered index）将主键和记录直接挂钩，而且记录是根据主键来排序的。每一个表只能有一个聚集索引，这很好理解，因为记录只能用一种方法来排序。当你通过主键来查询数据的时候，聚集索引非常快。因为它只要一次查询就可以得到结果，而标准的MyISAM的索引需要两次查询，先查到索引，然后通过索引得到的位置再去查数据。 4.1.1.6 Unique indexes versus primary keys 主键其实就是一种不能包括数值NULL的唯一索引。主键对于MyISAM引擎不是必需的，但对于InnoDB 和BDB引擎来讲是必需的，如果你不声明，他们会隐含添加一个主键 4.1.1.7 Indexing NULLs 4.2 Index Structures 这一节作者不会深究细节，他只想解决这几个问题：那种索引类型速度最快？那种灵活性最好？那种占用的空间最少？ 4.2.1 B-Tree Indexes B-Tree（balance tree）是目前最流行的索引类型，因为它在灵活性，空间占用和综合性能都比较优异。 B-Tree顾名思义就是树形结构，每个节点按照索引的数值来安排。因为是balance吗，所以在添加和去除节点后，B- Tree也不会失去平衡，它会自动调节。这种结构的好处就在于即使在最坏的情况下仍然可以得到比较好的性能。 4.2.2 Hash Indexes 哈希索引比较像一个哈希表，相对于树形结构来讲它显得比较“平”。它先将Key经过哈希函数处理，然后将处理过的哈希值除一个很大的质数（比如：35149），再根据余数将key分别归到不同的容器里。它的缺点在于缺乏灵活性和可预见性。这很容易理解，因为即使两个非常相似的key经过哈希函数处理以后，它们的值也大为不同。 4.2.3 R-Tree Indexes R-Tree索引在MySQL4.1版本中才出现，它主要用于空间和多维数据。 4.3 Indexes and Table Types 这一节主要将各种索引在不同存储引擎下的实现和一些应当注意的优化。 4.3.1 MyISAM Tables 除了B-Tree带来的好处之外，MyISAM还提供了两个重要的功能：prefix compression 和packed keys。 4.3.1.1 Delayed key writes MyISAM提供的一个提高性能的功能，它可以延迟将索引数据写到磁盘上的操作。这将在相当程度上提高在繁忙INSERT, UPDATE,和DELETE活动下 Mysql的性能。它的缺点在于当MySQL崩溃时，索引数据不会同步，这样的话之后将要重建索引，这会多花一些时间。 4.3.2 Heap Tables Heap Tables起初只支持哈希索引，现在4.1.0版本也支持B-Tree了。将B-Tree的灵活性和Heap Tables的速度结合起来，这样的性能无人能敌。 4.3.3 BDB Tables BDB Tables只提供B-Tree索引。 4.3.4 InnoDB Tables InnoDB Tables提供B-Tree索引。 4.3.5 Full-Text Indexes 全文索引是一个特殊类型的索引，它可以迅速定位一个字段中不同字的位置。MySQL在MyISAM中提供全文索引支持。全文索引基于表中的文本字段（例如：VARCHAR, TEXT等等）。全文索引存于表的MYI文件中。 4.3.6 Index Limitations 索引不是万能的，让我们这节来看看他的弱点。 4.3.6.1 Wildcard matches 先看个例子： select phonenumber from phonebook where last_name like “%son%” 这种通配符匹配，MySQL只能搜索每一列来得到答案，这时候索引也不会起到作用。 4.3.6.2 Regular expressions 正则表达式和通配符匹配遇到同样的问题。 4.3.6.3 Poor statistics or corruption 好像是废话，索引损坏了要及时修复的。 4.3.6.4 Too many matching rows 如果MySQL相信大约有30%的列匹配的话，他就不会用索引而去进行全表检索。当然也有例外，具体的可以去看第五章。 4.4 Index Maintenance 治大国若烹小鲜，同样，索引不需要特别的维护。 4.4.1 Obtaining Index Information 一些基本信息的获取 SHOW CREATE TABLE SHOW INDEXES FROM 4.4.2 Refreshing Index Statistics 对于MyISAM来讲，OPTIMIZE TABLE可以重建表的索引，并且是索引处于一个良好的状态。但是这种优化要花一些时间，此时表处于写锁定状态。对于BDB 和InnoDB来讲，他们不需要这些调整。

 

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。 可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。 （1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。 （2）Hash 索引无法被用来避免数据的排序操作。 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算； （3）Hash 索引不能利用部分索引键查询。 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。 （4）Hash 索引在任何时候都不能避免表扫描。 前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。 （5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下