数据库的索引

什么是数据库索引？

答：索引是定义在table基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项组成，每一种索引项由索引字段和行指针构成。

索引的好处？

1. 通过创建索引,可以在查询的过程中,提高系统的性能；
2. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性；
3. 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间；

索引的坏处？

1. 创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大；
2. 索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大；
3. 在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护；

什么是稠密索引和稀疏索引？

• 稠密索引：对于主文件中每一个记录都对应一个索引项；
  • 候选键属性的稠密索引：先查索引，然后再依据索引读主文件；
  • 非候选键属性的稠密索引：
    • 主文件按索引字段排序，索引文件中的索引字段值无重复；
    • 主文件索引字段未排序，但索引文件中的索引字段值是有重复的；
    • 主文件索引字段未排序且索引文件中的索引字段值无重复，这时可以引入指针桶来处理；
• 稀疏索引：对于主文件中部分记录有索引项和它对应（要求主文件必须是按对应索引字段属性排序存储）；

什么是主索引和辅助索引？

• 主索引：对每个存储块有一个索引项，每个存储块的第一个记录叫锚，通常建立在有序文件的基于主码的排序字段上，属于稀疏索引。
• 辅助索引：是定义在主文件的任一或多个非排序字段上的辅助存储结构，属于稠密索引。

     补充：一个主文件可以有一个主索引，但可以有多个辅助索引。

什么是聚簇索引和非聚簇索引？

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据，主文件按照对应字段排序存储，索引文件无重复排序存储。
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，主文件并没有按照对应字段排序存储，索引文件有重复排序存储。

补充：

1. 如果主文件的某一排序字段不是主码，则该字段上每个记录取值便不唯一，此时该字段被称为聚簇字段，聚簇索引通常是定义在聚簇字段上。
2. 聚簇索引通常是对聚簇字段上每一个不同值有一个索引项。
3. 一个主文件只能有一个聚簇索引文件，但可以有多个非聚簇索引文件。
4. 主索引通常是聚簇索引，辅助索引通常是非聚簇索引。
5. 主索引/聚簇索引是能够决定记录存储位置的索引，而非聚簇索引则只能用于查询，不能改变物理位置。

为什么主键通常建议使用自增id？

答：聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页。

mysql中聚簇索引的设定？

答：聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

什么是倒排索引和正排索引？

• 倒排索引：一个词汇包含在哪些文档中，倒排索引主要由两个部分组成：“单词词典”和“倒排文件”，应用于搜索引擎。
• 正排索引：一个文档包含了哪些词汇。

其他结构的索引？

• 多级索引：对索引再建立索引，如B书/B+树等；
• 多属性索引：索引字段由多个属性组合一起形成的索引；
• 散列索引：使用散列技术组织的索引；
• 网络索引：使用多索引字段进行交叉联合定位与检索；

---

B+树

B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。

二叉排序树：左子树的键值总是小于根的键值，右子树的键值总是大于根的键值，因此可以通过中序遍历得到键值的排序输出。

平衡二叉树：首先复合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1.(平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。)

m阶B-树：树中每个结点至多m棵子树、非叶子结点的根结点至少有两棵子树、除根结点外的非叶子结点至少有m/2棵子树、所有的叶子结点出现在同一层，并且不带信息，通常称为失败结点、树中每个结点的关键字有序，且关键字的左子树中的关键字均小于它，右子树均大于它。（单个节点可以存储多个键值和数据的平衡树）

m阶B+树：与B-树差异在于有n棵子树的结点中含有n个关键字、所有的叶子节点包含了全部关键字的信息，以及指向这些关键字记录的指针、所有的非终端结点可以看成是索引部分，结点中仅含有其子树中最大的关键字。

B+树相比B-树的优先？

1. B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。
2. B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

---

Mysql索引

• 普通索引

最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON 表名(列名(length));  //创建索引
ALTER 表名 ADD INDEX [indexName] ON (列名(length))  //修改表结构

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL,INDEX [indexName] (username(length)) ); //创建表时直接创建索引

DROP INDEX [indexName] ON 表名;  //删除索引

• 唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

CREATE UNIQUE INDEX indexName ON 表名(列名(length)) 

ALTER 表名 ADD UNIQUE [indexName] ON (列名(length)) 

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);  

• 主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引，即具有primary key约束的字段。

• 组合索引

ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2，列名3);

建立索引的时机是什么？

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

使用索引的注意事项？

• 索引不会包含有NULL值的列

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

• 使用短索引

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

• 索引列排序

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

• like语句操作

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

• 不要在列上进行运算
• 不使用NOT IN和<>操作