索引

1、定义

索引是存储引擎用于快速找到记录的一种数据结构。

2、优点

(1)提高查询速度:减少服务器需要扫描的数据量
(2)提高排序速度:帮助服务器避免临时排序和临时表
(3)提高分组统计速度:随机I/O变为顺序I/O,有利于分组统计

3、缺点

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

4、适合创建索引的字段

(1)表的主键、外键必须有索引
(2)数据量超过300的表应该有索引
(3)经常与其他表进行连接的表,在连接字段上应该建立索引
(4)经常出现在Where子句中的字段,特别是大表的字段,应该建立索引
(5)索引应该建在选择性高的字段上
(6)索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引
(7)复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替
(8)频繁进行数据操作的表,不要建立太多的索引
(9)删除无用的索引,避免对执行计划造成负面影响
(10)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的
(11)在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间

5、不适合创建索引的字段

(1)对于那些在查询中很少使用或者参考的列不应该创建索引

原因:既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求

(2)对于那些只有很少数据值的列也不应该增加索引

原因:由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度

( 3)对于那些定义为text, image和bit数据类型的列不应该增加索引

原因:这些列的数据量要么相当大,要么取值很少

(4)当修改性能远远大于检索性能时,不应该创建索

原因:修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引

6、创建索引注意事项

(1)索引不会包含NULL值的列

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

(2)使用短索引

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

(3)索引排序

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

(4)like语句操作

原因:一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引

(5)不要在列上进行计算

原因:select from users where YEAR(adddate)<2007
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成
select
from users where adddate<‘2007-01-01’

(6)尽量不使用NOT IN和<>操作

原因:NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id<>3则可使用id>3 or id<3来代替