数据库索引面试大全

索引面试大全"/>

数据库索引面试大全

索引有哪些类型

• 普通索引：最基本的索引，没有任何约束限制。
• 唯一索引：和普通索引类似，但是具有唯一性约束，可以有 null
• 主键索引：特殊的唯一索引，不允许有 null，一张表最多一个主键索引
• 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并
• 全文索引：对文本的内容进行分词、搜索
• 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行

索引失效

索引失效的情况

1、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。

2、不在索引列上做任何操作，会导致索引失效而导致全表扫描。

3、存储引擎不能使用索引中范围条件右边的列，范围之后索引失效。这写条件判断最后放到后面，先定位到小的范围再开始。

4、mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效。

5、mysql中使用is not null 或者 is null会导致无法使用索引。

6、mysql中like查询是以%开头，索引会失效变成全表扫描，覆盖索引。

7、mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。

8、如果mysql使用全表扫描要比使用索引快,则不会使用到索引。

覆盖索引

• 概念：覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
• 解释：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

聚簇索引与非聚簇索引

概念

• 聚簇索引：聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引
• 非聚簇索引：在聚簇索引之上创建的索引称之为辅助索引（非聚簇索引），辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行

优缺点比较

1. 通过聚簇索引查询可以直接获取数据，效率比较高，而非聚簇索引需要二次查询（先查到索引，再根据索引查找数据）
2. 聚簇索引便于进行范围查找，也适用于排序的场景，因为数据是排序的
3. 聚簇索引比较难维护
4. 聚簇索引适合使用自增id作为主键，使用uuid作为主键的话，数据存储会比较分散，这时聚簇索引效率很低

索引的建立原则, 如何避免索引失效

1. 最佳左前缀法则 - 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过复合索引中间列。
2. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
3. 存储引擎不能使用索引中范围条件右边的列。
4. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
5. mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
6. is null, is not null 也无法使用索引。
7. like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
8. 字符串不加单引号索引失效。
9. 少用or，用它来连接时会索引失效。