数据库索引设计

索引的类型：
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 。下面我们通过图示来针对这两种索引的存放
形式做一个比较。

 

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