数据库基础之索引（一）

一 什么是索引

索引是对数据库一列值或多列值进行排序的一种结构。索引是一个单独的，物理的数据结构，它是表中一列值或若干列值得集合以及指向表中物理标识这些值的数据页的逻辑指针清单。
即索引=索引列集合+指向数据表的指针

二 索引有多少种

• 普通索引
  最基本的索引，没有任何限制。

直接创建：create index indexname on tablename(列名(length));如果是varchar,char类型，length可以小于实际长度，但是Blob和text类型要指定长度，超过一定值会存储到外存导致性能下降。

修改表结构创建：alter table tablename add index indexname (列名(length));

建表时创建：
create table test(
id int not null,
username varchar(255) not null,
index indexname (列名(length))
);

删除索引：drop index indexname on tablename

• 唯一索引
  索引列的值必须唯一，但允许有空值，组合索引的列值组合唯一

直接创建：create unique index indexname on tablename(列名(length));

修改表结构创建：alter table tablename add uniqueindexname (列名(length));

建表时创建：
create table test(
id int not null,
username varchar(255) not null,
unique indexname (列名(length))
);

• 主键索引
  特殊的唯一索引，不允许有空值，建表时建立
  create table test(
  id int not null,
  usernaem varchar(255) not null，
  primary key(id)
  );

• 组合索引
  CREATE TABLE mytable(

  ID INT NOT NULL,

  username VARCHAR(16) NOT NULL,

  city VARCHAR(50) NOT NULL,

  age INT NOT NULL
  );
  建立username,city,age的组合索引
  create index name_city_age on mytable (username,city,age);
  与建立三个单个索引相比，组合索引效率更高
  三列的组合索引相当于：

  username,city,age
  username,city
  username

而不会存在city,age这样的索引，因为组合索引遵循的是最左前缀原则，如果sql语句中第一个参数不是组合索引的最左列，sql查询则不会走索引

sql会走索引：
select * from mytable where username=?and city=?
select * from mytable where username=? and city=?andage=?

sql不走索引：
select * from mytable where city=? and age=?
select * from mytable where city=?

三 聚集索引和非聚集索引

• 聚集索引：数据行的物理顺序与属性列的逻辑顺序一致，一个表只能有一个聚集索引。
  创建聚集索引 alter table table_name add primary key(colum_name)
  
  一般通过主键来建立聚集索引，即主键索引属于聚集索引，聚集索引的叶子节点保存的是数据，一次查询就可以找到相应数据，而非聚集索引如果查询的列不是索引包含的列还需要进行二次查询，因为非聚集索引的叶子节点存放的是索引节点，保存指向数据块的指针。

• 非聚集索引：数据行的物理顺序与属性列的逻辑顺序不一致，一个表可以有多个非聚集索引
  
  非聚集索引的二次查询问题：如果要查询的列不在非聚集索引包含的列中，将会执行二次查询。利用第一次查询得到的索引节点中指向数据块的指针，找到对应的数据块，来获取非聚集索引列外的属性值。

四 聚集索引和非聚集索引的常见问题

第一：聚集索引的约束是唯一性，是否要求字段也是唯一的呢？不唯一
分析：聚集索引可以建立在任何一列上。如果设置主键时没有强制使用非聚集索引，数据库会默认在上面建立聚集索引，导致认为聚集索引的列一定唯一

第二：为什么聚集索引可以创建在任何一列上，如果此表没有主键约束，即有可能存在重复行数据呢？ 分析：如果未用unique属性建立聚集索引，数据库引擎会自动向表中添加一个四字节的uniqueifier列
当可能出现重复时，数据库引擎会自动向行里添加一个uniqueifier值，使每个键唯一。这个值对用户不可见

第三：是不是聚集索引就一定要比非聚集索引性能优呢?

如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？

分析：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好

第四：在数据库中通过什么描述聚集索引与非聚集索引的？
分析：索引是通过B树的形式进行描述的，聚集索引的节点存放的是数据节点，非聚集索引存放的是索引节点，包含指向数据页的指针。

第五：在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？
分析：由于主键的唯一性约束，在插入的时候要遍历所有行，由于聚集索引存放的是数据节点，而非聚集索引存放的是索引节点，遍历数据页要比遍历索引页耗时，所以聚集索引的插入要比非聚集索引的插入速度慢