索引概念

1、什么是索引？

索引用来快速地寻找那些具有特定值的记录。

索引是加速查询主要手段，索引是快速定位数据的技术。

索引是一种特殊的文件（innoDB(事务性数据库的首选引擎)数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

索引：一种特殊的目录，聚集索引和非聚集索引

聚集索引：如同字典中按照字母查询，我们把这种正文内容本身就是一种按照一定规则排列的目录称为聚集引。

非聚集索引：如同字典中按照偏旁来查询某个字，我们把这种目录纯粹是目录，正文纯粹是正文的排序。

       主键就是聚集索引，sqlserver默认是在主键上建立聚集索引的。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效，过多的索引甚至会导致索引碎片。95%的数据库性能问题都可以采用索引技术得到解决。

通常情况下，我们会在每个表中建立一个id列，以区分每条数据，并且这个id列是自动增长的，步长一般都是

1.此时，我们将这个列设为主键，sql server会将此列默认为聚集索引。这样做有好处，就是可以让您的

数据在数据库中按照id进行物理排序，但是意义不是很大。

2、索引分类

（1）直接创建索引和间接创建索引

 直接创建索引：CREATE INDEX mycolumn_index ON mytable (myclumn)

   间接创建索引：定义主键约束或者唯一性键约束，可以间接创建索引
（2）普通索引和唯一性索引
    普通索引：CREATEINDEX mycolumn_index ON mytable (myclumn)
   唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用
    CREATEUNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
（3）单个索引和复合索引
   单个索引：即非复合索引
   复合索引：又叫组合索引，在索引建立语句中同时包含多个字段名，最多16个字段
    CREATE INDEXname_index ON username(firstname,lastname)
（4）聚簇索引和非聚簇索引(聚集索引，群集索引)
   聚簇索引：物理索引，与基表的物理顺序相同，数据值的顺序总是按照顺序排列
    CREATECLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
   ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
   非聚簇索引：CREATE UNCLUSTERED INDEXmycolumn_cindex ON mytable(mycolumn)

什么时候用？

应该只为最经常查询和最经常排序的数据列建立索引。

只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引，无论表中是否有数据。

可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。

        索引列不会包含有null值，只要列中包含有null值都将不会被包含有索引中，复合索引中只有一个列含有null值，那么这一列对于此

复合索引就无效了，所以我们在数据设计时不要让字段默认值为null。

       索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert，update，delete，因为更新表

时，mysql不仅要保存数据，还要保存一下索引文件。

MySQL把同一个数据表里的索引总数限制为16个。

许多sql命令都有一个delay_key_write项，这个选项的作用是暂时限止mysql在该命令每插入一条记录和每

修改一条现有记录之后立刻对索引进行刷新，对索引的刷新将等到全部记录插入、修改完毕之后再进行，

在需要把许多新记录插入某个数据表的场合，delay_key_write选项的作用将非常明显。

两种情况下不建议建索引：

第一种是表记录比较少。

第二种索引选择性较低。选择性是指不重复的索引值与表记录数的比例。

使用索引注意：

1、order by中的列是不会使用索引的。

2、通配符%在字符串的开端使得索引无法使用如 name like '%zhang' 而name like'zhang%'就可以。

3、or会引起全表扫描。

4、in的作用相对于or

如：

select * from table where id in(2,3)

等于

select * from table where id = 2 or id =3;如果id上有索引，则索引会失效。

5、尽量少用not。

6、exists 和 in 的执行效率是一样的。

7、union并不绝对比or的执行效率高。

如：

select id,name from table where time='2012-01-01' or age =22

等于

select id,name from table where time ='2012-01-01' union select id,name from table where age = 22

但是如果or两边的查询条件是一样的话，那么用union则反倒速度差很多。虽然这里union扫描的是索引，而

or扫描的是全表。

如：select * from table where id = 2 or id = 3;

8、字段提取要按照“需多少、提多少”的原则，避免“select *”

9、count(*)不比count(字段)慢

10、orderby按聚集索引列排序效率最高,如果按照某个字段进行排序的时候，无论是倒序还是顺序，速度是相当的。

11、charindex

查询a字段中包含b的所有行

select * from table where a like '%b%'

等于

select * from table where charindex('b',a)>0  这种方法比like的形式速度上要快很多。

12、请选择与业务无关的自增字段作为主键。

这样就会形成一个紧凑的索引结构，近似顺序填满，由于每次插入时也不需要移动已有数据，因此效率很高，

也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证或学号），由于每次插入主键的值近似于随机，因此每次新记录都要被

查到现有索引页的中间某个位置。此时mysql不得不为了将新记录插到合适位置而移动数据，甚至目标页面

可能已经被写到磁盘上而从缓存中清掉，此时也要从磁盘上读回来，这增加了很多开销，同时频繁的移动，

分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过optimize table来重建表

并优化填充页面。

13、每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加贵。聚集索引的优点：迅速缩小查询范围，避免全表扫描。