索引利弊·规则·技巧 整理

目录
一、索引利弊
二、索引建立规则
三、索引使用技巧和注意事项
四、MySQL如何利用索引优化ORDER BY排序语句
五、索引和主键的一些区别与联系




一、索引利弊

◆为什么要创建索引呢？因为创建索引可以大大提高系统的性能。
 
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 
第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。


◆但是为表中的每一个列都增加索引是非常不明智的。因为，增加索引也有许多不利的一个方面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。




二、索引建立规则


索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

◆一般来说，应该在这些列上创建索引，例如：

第一，在经常需要搜索的列上，可以加快搜索的速度；在经常用作过滤器的字段上建立索引；索引应该建在选择性高的字段上； 

第二，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
 
第三，在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 用于联接的列（主健/外健）上建立索引；经常与其他表进行连接的表，在连接字段上应该建立索引；

第四，在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
 
第五，在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序加快排序查询时间；在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；

第六，在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

第七，在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；

第八，当对表的查询操作比更新操作频率更高时，对该表建立索引；对于查询操作中频繁使用的列建立索引，不对很少或从来不作为查询条件的列建立索引

第九， 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引
   A.如：含有有限数目（不是很少）唯一的列
   B.进行大范围的查询，充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索
注：簇索引是物理索引它会影响数据的物力存储，所以簇索引是唯一的，其他索引都是逻辑的，他利用指针查询数据；


◆再一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。只有某些时候的LIKE才需建立索引，是因为在以通配符%和_开头作查询时，MySQL不会使用索引。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

◆同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。
因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。 

第二，对于那些只有很少数据值的列也不应该增加索引。在不同值较少的字段上不必要建立索引，如性别字段。
因为，由于这些列的取值很少，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
 
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。
因为，这些列的数据量要么相当大，要么取值很少。 

第四，当修改性能远远大于检索性能时，不应该创建索引。对于经常存取的列避免建立索引。
因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。因此，当修改性能远远大于检索性能时，不应该创建索引。

第五，建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

◆索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

当然合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。 另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。 

总的来说，小型表肯定不建索引；或者数据库记录在亿条数据级以上，还是建议使用非关系型数据库；还有些特殊字段的数据库，比如BLOB，CLOB字段肯定也不适合建索引。

三、索引使用技巧和注意事项

◆使用索引的注意事项,使用索引时，有以下一些技巧和注意事项：

1.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.不要在列上进行运算
select * from users where YEAR(adddate)<2007; 
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;

5.在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。只有某些时候的LIKE才需建立索引，是因为在以通配符%和_开头作查询时，MySQL不会使用索引.


6.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

7.不使用NOT IN和<>操作

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

9.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： 
  A、正确选择复合索引中的主列字段，一般是选择性较好的字段； 
  B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引； 
  C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引； 
  D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段； 
  E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引； 

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

11.对存取大量不同值的列建立索引，不对只有少量不同值的列建立索引



四、MySQL如何利用索引优化ORDER BY排序语句 

◆通过索引优化来实现MySQL的ORDER BY语句优化：

1、ORDER BY的索引优化。如果一个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多个字段ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

◆MySQL Order By不能使用索引来优化排序的情况

1、 对不同的索引键做 ORDER BY ：(key1,key2分别建立索引)
SELECT * FROM t1 ORDER BY key1, key2;

2、 在非连续的索引键部分上做 ORDER BY：(key_part1,key_part2建立联合索引;key2建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

3、 同时使用了 ASC 和 DESC：(key_part1,key_part2建立联合索引)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

4、 用于搜索记录的索引键和做 ORDER BY 的不是同一个：(key1,key2分别建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

5、 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

◆特别提示:
1>mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。
2>在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。

五、索引和主键的一些区别与联系

关系数据库依赖于主键，它是数据库物理模式的基石。主键在物理层面上只有两个用途： 惟一地标识一行；作为一个可以被外键有效引用的对象。 
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

◆下面是索引和主键的一些区别与联系

1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。 

所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。数据库在设计时，主键起到了很重要的作用。 

主键可以保证记录的唯一和主键域非空，数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。 

2. 一个表中可以有多个唯一性索引，但只能有一个主键。 

3. 主键列不允许空值，而唯一性索引列允许空值。 

4. 索引可以提高查询的速度。 

其实主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中，主键一般都要建，主要是用来避免一张表中有相同的记录，索引一般可以不建，但如果需要对该表进行查询操作，则最好建，这样可以加快检索的速度。