数据库索引--面试知识点

1.什么是索引

索引是一种基于表的数据结构,通过它可以加速数据的检索,类似书的目录，可以提高查询速度。

索引分单列索引和组合索引。

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

• 组合索引，即一个索引包含多个列。

2.索引类型

• 普通索引

最基本的索引，它没有任何限制

#表已存在的时候创建索引
CREATE INDEX indexName ON mytable(username(length)); 

#修改表结构
ALTER mytable ADD INDEX [indexName] ON mytable(username(length))

#建表的时候直接指定字段作为索引
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  

#删除索引
DROP INDEX [indexName] ON mytable; 

• 唯一索引

索引列的值必须唯一，但允许空值。如果是组合索引，则列值的组合必须唯一。

#表已存在的时候创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)); 

#修改表结构
ALTER mytable ADD UNIQUE INDEX [indexName] ON mytable(username(length))

#建表的时候直接指定字段作为索引
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

#删除索引
DROP INDEX [indexName] ON mytable; 

• 主键索引

特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

CREATE TABLE mytable(  
    
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
 
PRIMARY KEY(ID)  
 
);  

• 组合索引

为了进一步提高MySQL的效率，就要考虑建立组合索引。就是将 多个字段建到一个索引里

表结构：

CREATE TABLE mytable(  

ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
city VARCHAR(50) NOT NULL,  
age INT NOT NULL 
 
);  

#建立组合索引
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age  
 
usernname,city  
 
usernname  

为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。

3.索引原理

数据结构：

• 平衡树 B树或B+树（主流RDBMS）
• 哈希桶（少）
• 全文索引，只有MyISAM引擎支持，但只有在char，varchar，text列使用

聚集索引 ：

没有主键	有主键
数据无序存储在磁盘上，一行一行挨着存储	树状结构，整个表成为一个索引，就是所谓的聚集索引

不使用索引	使用
O(N)	O(logmN)

其中，N代表数据总数，m为B树阶数

非聚集索引：、

非聚集索引和聚集索引一样， 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段。如果给表中多个字段加上索引 ， 那么就会出现多个独立的索引结构，每个索引（非聚集索引）互相之间不存在关联。 如下图

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

查询时先从对应字段的索引开始查询，查询到主键值再用主键索引查询具体数值。

非聚集索引和聚集索引的区别：

非聚集索引	聚集索引
查到对应主键值，再使用主键的聚集索引查数据	直接查到数据

覆盖索引(复合索引/多字段索引)：

不使用聚集索引就能查询出所需要的数据的非聚集索引（不需要回表查询）

多个字段建立一个索引，查询时直接查询一个非聚集索引，直接得到想要数据

create index index_birthday_and_user_name on user_info(birthday, user_name);

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容，然而， 叶节点中除了有user_name表主键ID的值以外， user_name字段的值也在里面， 因此不需要通过主键ID值的查找数据行的真实所在， 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式， 可以省略不使用覆盖索引查找的后面两个步骤， 大大的提高了查询性能，如下图

4.索引的缺点

• 增大了查询速度但降低表的更新速度，对表的insert，update和delete
• 建立索引会创建索引文件，占用磁盘空间

5.使用时注意事项

• 索引字段不能包含NULL值（针对复合索引）
• 使用短索引，提高查询速度节省磁盘空间
• 索引列排序，MySQL查询中只能使用一个索引，如果where字句中已经使用了索引，那么order by字句中就不能使用索引了，所以尽量不使用d多个列排序或者创建复合索引
• like语句，一般不使用，like “%aaa%”不能使用索引，但是like “aaa%”可以使用
• 不要在列上运算，将导致索引失效而进行全表扫描

6.什么情况下应不建或少建索引

1. 表记录太少
2. 经常插入、删除、修改的表
3. 数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
4. 经常和主字段一块查询但主字段索引值比较多的表字段

原文链接：

深入浅出数据库索引原理

数据库索引原理，及MySQL索引类型