数据库索引

数据库索引

1.索引的优缺点

索引的优点 ： 可以加快检索速度，使用索引，在查询的过程中，使用优化隐藏起，提高系统的性能。

索引的缺点 ： 创建索引和维护索引要耗费时间，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增删改的执行效率。索引需要额外的空间。

2.索引使用的场景

where ： 根据id查询记录，id字段仅建立了主键索引，SQL执行可选的索引，如果有多个，最终会选一个较优的作为检索的依据。

order by ： 使用order by 将查询结果按照字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序，这个操作会从磁盘分批读取到内存使用内部排序，最终合并排序结果，这个操作是十分影响性能的，如果对字段建立索引，由于索引本身是有序的，直接按照索引的顺序和映射关系逐条取出数据即可。

join ： 对join语句匹配关系涉及的字段建立索引能够提高效率。

索引覆盖 ： 如果要查询的字段都建立过索引，那么引擎会字节在索引表中查询而不会访问原始数据，这就是索引覆盖，我们需要尽可能在select后只写必要的查询字段，增加索引股改的几率。

3.索引有哪几种类型？

主键索引 ： 数据列不允许重复，不允许为NULL，一个表只能有一个主键

唯一索引 ： 数据列不允许重复，允许为NULL ，一个表允许多个列创建唯一索引，可以通过ALTER TABLE table_name ADD UNIQUE(column)创建唯一索引

普通索引 ： 基本的而索引类型，没有唯一性的限制，允许为NULL，可以通过ALTER TABLE table_name ADD INDEX index_name(column)创建普通索引

全文索引 ： 可以通过ALTER TABLE table_name ADD FULLTEX(colume) 创建全文索引

4.什么是聚簇索引？如何使用聚簇索引与非聚簇索引？

聚簇索引 ： 将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引 : 将数据存储于索引分开结构，索引结构的叶子结点指向了数据的对应行，MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时，在内存直接搜索引擎，然后通过索引找到磁盘相应的数据。

这里需要注意的是，InnoDB中，在聚簇索引上创建的索引称为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引，辅助索引叶子结点存储的不再是行的物理位置，而是主键值

5.联合索引是什么？

MySQL可以使用多个字段同时建立一个索引叫做联合索引，在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。一般情况下，将查询需求频繁或者字段选择性高的列放在前面，此外可以根据特例查询或者表结构及逆行单独的调整。

6.索引的数据结构

MySQL中使用较多的索引有Hash索引，B+树索引等。InnoDB存储引擎的默认索引实现为：B+树索引，对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快，其余大部分常见，选择B+树索引。

B+树索引

基本所有的存储引擎都支持BTree索引，B+Tree的性质：

• n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引
• 所有的叶子结点中包含了全部关键字的信息，以及指向含这些关键字记录的指针，而且叶子结点本身依关键字的大小顺序链接
• 所有的非终端结点可以看成是索引部分，结点中仅含其子树中最大或最小的关键字
• B+树中，数据对象的插入和删除尽在叶节点上进行
• B+树有两个头指针，一个是树的根节点，一个是最小关键码的叶结点
• BTree算法是mysql的默认算法，不仅可以用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量

哈希索引

在MySQL中使用哈希索引时，主要就是通过Hash算法（直接定址法，平方取中法，折叠法，除数取余法，随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一起存入Hash表的对应位置，如果发生Hash冲突，则在对应Hash键下以链表的形式存储。

Hash 索引只能用于对等比较，比如=,<=,>=操作符，由于是一次定位数据，不像BTree索引需要从根节点到枝结点，最后才能访问到叶结点这样多次IO访问，所以检索效率远高于BTree索引

对比Hash索引和B+树的区别和优劣

hash索引底层是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获取到实际数据，B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点触发，查找到叶子结点方可以获得所查找键值，然后根据查询判断是否需要回表查询数据

hash索引进行等值查询你更快，但是无法进行范围查询，因为hash函数的不可预测性

hash索引不支持使用索引进行排序

hash索引不支持模糊查询以及多列索引的最左前缀匹配

hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件，比如聚簇索引和覆盖索引的时候只通过索引完成查询

hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键存在大量重复时，发生hash碰撞，效率可能极差，B+树的效率比较稳定，对于所有的查询都是从根节点到叶子结点，且树的高度相对较低

7.创建索引的原则

• 最左前缀匹配原则，这是组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就会停止匹配
• 较频繁作为查询条件的字段才会创建索引
• 更新频繁字段不适合创建索引
• 尽量扩展索引，不要新建索引
• 定义外键的数据列一定要建立索引
• 对于哪些查询中很少涉及的列，重复值比较多的列不要建立索引
• 对于定义为text、image和bit的数据类型的列不要建立索引

创建索引的三种方式：

• 在执行CREATE TABLE时创建索引
• 使用ALTER TABLE命令去增加索引
• 使用CREATE INDEX命令创建

8.百万级别或以上的数据如何删除？

• 先删除索引
• 删除其中的无用数据
• 创建新的索引

9.什么是最左前缀原则？

在创建多列索引时，根据业务需求，where子句中使用最频繁的一列放在最左边

最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询（<,>,between,like)就停止匹配，比如a=1 and b=2 and c>3 and d=4如果建立（a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

=和in可以乱序，比如a=1 and b=2 and c=3建立(a,b,c)索引可以任意排序，mysql的查询优化器会帮你优化成索引可以识别的形式