目录
一.结论
索引的一个主要目的就是加快检索表中数据
索引是用来定位的
注:所有的索引都可以有多个字段,如联合索引,普通索引,主键,聚集索引
二.各类索引说明
1.唯一索引:(可以用来防止数据重复问题,用insert ignore into插入不报错,affect:0)
注:不能重复,可以有多个,可以有一个null值
- a.防止添加将在表中创建重复键值的新数据
- b.加快查询速度
2.主键索引:(主键和主键索引可以想象成是一个东西)
注:不能重复,只能有一个主键,但一个主键可以包含多个字段,不可以有null值,定义主键primarykey时也定义了主键索引
定义主键将自动创建主键索引,主键索引是唯一索引的特定类型
3.聚集索引(从某种程度上,聚集索引即数据)
注:数据库索引分为聚集索引和非聚集索引,非聚集索引分为普通索引,唯一索引,全文索引等
- a.一个表只能包含一个聚集索引
- b.聚集索引和非聚集索引只多了个唯一约束,其他没什么区别
- c.MySQL-innodb里主键就是聚集索引
- d.聚集索引的约束是唯一性,是否要求字段也是唯一的呢?不要求唯一!
- e.聚集索引可以创建在任何一列你想创建的字段上,这是从理论上讲
- f.聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。
三.各类主键比较区别
1.聚集索引和非聚集索引的主要区别:
(1.)数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同,一个表中就一个;索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以有多个
(2.)聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。
第三:是不是聚集索引就一定要比非聚集索引性能优呢?
例:如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?
答:否。既然只输出两列,我们可以在学分以及学生姓名上创建联合非聚集索引,此时的索引就形成了覆盖索引,即索引所存储的内容就是最终输出的数据,这种索引在比以学分为聚集索引做查询性能更好。
除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。
在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。
2.主键索引和主键的关系:创建主键的同时创建了主键索引
3.主键索引和唯一索引的区别:主键索引使用primarykey创建主键时自动创建,主键索引根据主键要求字段不重复不为空,唯一索 引要求字段不同,但可以有一个null值
4.唯一索引和普通索引的区别:唯一索引unique index,要求值唯一,普通索引index
5.主键和聚集索引关系:主键可分为聚集的主键和非聚集的主键。在创建主键时,如果不指定类型,则默认创建的是聚集的主键,mysql-innodb创建主键同时将主键变为聚集索引
四.补充
1.哪些字段应该加索引哪些不应该加?
注:这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。
应该加:where查询字段,order by子句,join字段
不应加:查询中很少使用或者参考的列不应该创建索引,小值字段如性别,text, image和bit数据类型,当修改性能远远大于检索性能时(常修改的字段)
2.多列索引和多个单列索引
多列索引即是一个索引包含多个字段的情况;
多个单列索引即是每个索引只包含一个字段,但是有多个索引。
考虑两种不同的建立索引方式:
case 1:对c1,c2,c3三列按此顺序添加一个多列索引;
case 2: 对c1,c2,c3分别建立三个单列索引;
问题1:按c1搜索时,哪种索引效率快?
答:case2
问题2:按C2搜索时,哪种索引效率快?
答:case2,并且,case1的索引无效
问题3:按C1,C2搜索时哪种效率快?
答:不知道
问题4:按C1,C2,C3搜索哪种效率快?
答:case1
问题5:按C2,C3,C1搜索时哪种效率快?
答:case2,因为没有按多列索引的顺序搜索,case1的索引没有使用到。
参考文章: