首页 > 代码库 > 数据库索引设计

数据库索引设计

索引的类型:
normal:表示普通索引

unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique

full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。

总结,索引的类别由建立索引的字段内容特性来决定,通常normal最常见。


普通索引 添加INDEX
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
下面演示下给user表的name字段添加一个索引
mysql数据库如何创建索引
mysql数据库如何创建索引
2
主键索引 添加PRIMARY KEY
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
mysql数据库如何创建索引
mysql数据库如何创建索引
3
唯一索引 添加UNIQUE
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
mysql数据库如何创建索引
4
全文索引 添加FULLTEXT
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
mysql数据库如何创建索引
5
如何添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

索引建立的原则
基于合理的数据库设计,经过深思熟虑后为表建立索引,是获得高性能数据库系统的基础。而未经合理分析便添加索引,则会降低系统的总体性能。索引虽然说提高了数据的访问速度,但同时也增加了插入、更新和删除操作的处理时间。
是否要为表增加索引、索引建立在那些字段上,是创建索引前必须要考虑的问题。解决此问题的一个比较好的方法,就是分析应用程序的业务处理、数据使用,为经常被用作查询条件、或者被要求排序的字段建立索引。基于优化器对SQL语句的优化处理,我们在创建索引时可以遵循下面的一般性原则:

(1)为经常出现在关键字order by、group by、distinct后面的字段,建立索引。
在这些字段上建立索引,可以有效地避免排序操作。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
(2)在union等集合操作的结果集字段上,建立索引。其建立索引的目的同上。
(3)为经常用作查询选择的字段,建立索引。
(4)在经常用作表连接的属性上,建立索引。
(5)考虑使用索引覆盖。对数据很少被更新的表,如果用户经常只查询其中的几个字段,可以考虑在这几个字段上建立索引,从而将表的扫描改变为索引的扫描。

除了以上原则,在创建索引时,我们还应当注意以下的限制:

(1)限制表上的索引数目。
对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
(2)不要在有大量相同取值的字段上,建立索引。
在这样的字段(例如:性别)上建立索引,字段作为选择条件时将返回大量满足条件的记录,优化器不会使用该索引作为访问路径。
(3)避免在取值朝一个方向增长的字段(例如:日期类型的字段)上,建立索引;对复合索引,避免将这种类型的字段放置在最前面。
由于字段的取值总是朝一个方向增长,新记录总是存放在索引的最后一个叶页中,从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外,如果所建索引是聚集索引,表中数据按照索引的排列顺序存放,所有的插入操作都集中在最后一个数据页上进行,从而引起插入“热点”。
(4)对复合索引,按照字段在查询条件中出现的频度建立索引。
在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用。
因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。
(5)删除不再使用,或者很少被使用的索引。
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

索引建立原则二:
建立索引的原则:
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列;
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
7) 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
8) 对于定义为text、image和bit的数据类型的列不要建立索引。
9) 对于经常存取的列避免建立索引
9) 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。
10) 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

组合多个索引
一个单独的索引扫描只能用于这样的条件子句:使用被索引字段和索引操作符类中的操作符, 并且这些条件以AND连接。假设在(a, b)上有一个索引, 那么类似WHERE a = 5 AND b = 6的条件可以使用索引,但是像WHERE a = 5 OR b = 6的条件就不能直接使用索引。
一个类似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 这样的查询可以分解成四个在x上的独立扫描,每个扫描使用一个条件, 最后将这些扫描的结果OR 在一起,生成最终结果。另外一个例子是,如果我们在x 和y上有独立的索引,一个类似WHERE x = 5 AND y = 6 这样的查询可以分解为几个使用独立索引的子句,然后把这几个结果AND 在一起,生成最终结果。

在大多数最简单的应用里,可能有多种索引组合都是有用的,数据库开发人员必须在使用哪个索引之间作出平衡。有时候多字段索引是最好的,有时候创建一个独立索引并依靠索引组合是最好的。比如,假如你的查询有时候只涉及字段x,有时候只涉及字段y,有时候两个字段都涉及, 那么你可能会选择在x和y上创建两个独立的索引, 然后依靠索引组合来处理同时使用两个字段的查询。你也可以在(x, y)上创建一个多字段索引, 它在同时使用两个字段的查询通常比索引组合更高效,但是对那些只包含y的查询几乎没有用,因此它不能是唯一一个索引。一个多字段索引和y上的独立索引可能会更好。因为对那些只涉及x的查询, 可以使用多字段索引,但是它会更大,因此也比只在x上的索引更慢。最后一个选择是创建三个索引, 但是这种方法只有在表的更新远比查询少,并且所有三种查询都很普遍的情况下才是合理的。如果其中一种查询比其它的很多,那么你可能更愿意仅仅创建两种匹配更常见查询的索引。

索引方法的区别:
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 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下


2. B-Tree索引

B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型,除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此,实际上在其他的很多数据库管理系统中B-Tree 索引也同样是作为最主要的索引类型,这主要是因为 B-Tree 索引的存储结构在数据库的数据检 索中有非常优异的表现。
一般来说, MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的,也就是所有实际需要的数据都存放于 Tree 的 Leaf Node ,而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的,所以我们大家都称之为 B-Tree 索引当然,可能各种数据库(或 MySQL 的各种存储引擎)在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree ,也就是在 B-Tree 数据结构的基础上做了很小的改造,在每一个
Leaf Node 上面出了存放索引键的相关信息之外,还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息,这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。
在 Innodb 存储引擎中,存在两种不同形式的索引,一种是 Cluster 形式的主键索引( Primary Key ),另外一种则是和其他存储引擎(如 MyISAM 存储引擎)存放形式基本相同的普通 B-Tree 索引,这种索引在 Innodb 存储引擎中被称为 Secondary Index 。下面我们通过图示来针对这两种索引的存放
形式做一个比较。

 

忘了引用哪位大神的博客了,如有侵权,请联系我删除(本博客仅作为自己学习使用)

数据库索引设计