浅谈mysql的索引设计原则以及常见索引的区别

索引定义：是一个单独的，存储在磁盘上的数据库结构，其包含着对数据表里所有记录的引用指针.

数据库索引的设计原则：

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的？

1．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。

4．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文
检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。

6．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8．小表不应建立索引；包含大量的列并且不需要搜索非空值的时候可以考虑不建索引

----------------------------------------------------------

mysql索引相关小常识：

一、 经常被用来过滤记录的字段。

1. primary key 字段, 系统自动创建主键的索引；
2. unique key 字段，系统自动创建对应的索引;
3. foreign key 约束所定义的作为外键的字段;

4. 在查询中用来连接表的字段;

5. 经常用来作为排序(order by 的字段)基准的字段;

二、索引会占用磁盘空间，创建不必要的索引只会形成浪费。

三、索引的创建必须考虑数据的操作方式。

1.内容很少变动,经常被查询,为它多创建几个索引無所谓;

2.经常性,例行性变动的表而言,则需要谨慎地创建确实必要的索引;

四、primary key 和 unique key的区别

1. 作为Primary Key的域/域组不能为null。而Unique Key可以。

2. 在一个表中只能有一个Primary Key，而多个Unique Key可以同时存在。

更大的区别在逻辑设计上, Primary Key一般在逻辑设计中用作记录标识，这也是设置
Primary Key的本来用意, 而Unique Key只是为了保证域/域组的唯一性。

五、复合索引和单一索引

复合索引是指多字段联合索引,查询时经常需要这几个字段组合一起为条件再查询

唯一索引主要是用主键ID索引,存储结构顺序与物理结构一致

如：create  index  idx  on  tbl(a,b) 

先按a排序, a相同的按b排序,所以当你查a或ab的时候,

能够利用到这个索引.但当你只查b的时候,索引对你的帮助不大.可能可以跳跃查找.

---------------------------------------------

添加和删除索引的情况：

1、表的主键、外键必须有索引；

2、数据量超过300w的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；

以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大