索引常见面试题
什么是索引
- 索引的定义就是帮助存储引擎快速获取数据的一种数据结构,形象的说就是索引是数据的目录。
- 存储引擎,说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。索引和数据就是位于存储引擎。
索引的分类
- 按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。
- 创建的主键索引和二级索引默认使用的是 B+Tree 索引。
- B+Tree 存储千万级数据只需要 3-4 层高度就可以满足,从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O。
- 先检索二级索引,找到对应的叶子节点获取主键值,然后通过聚簇索引中的B+Tree 树查询到对应的叶子节点,要查两个 B+Tree 才能查到数据,这个过程叫做回表。
- 在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」,也就是只需要查一个 B+Tree 就能找到数据。
- 按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引)。
- 主键索引的 B+Tree 的叶子节点存放的是实际数据,所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里。术语“聚簇”表示数据行和相邻的键值聚簇地存储在一起。
- 二级索引的 B+Tree 的叶子节点存放的是主键值,而不是实际数据。
- 按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。
- 主键索引就是建立在主键字段上的索引,通常在创建表的时候一起创建,一张表最多只有一个主键索引,索引列的值不允许有空值。
- 唯一索引建立在 UNIQUE 字段上的索引,一张表可以有多个唯一索引,索引列的值必须唯一,但是允许有空值。
- 普通索引就是建立在普通字段上的索引,既不要求字段为主键,也不要求字段为 UNIQUE。
- 前缀索引是指对字符类型字段的前几个字符建立的索引,而不是在整个字段上建立的索引,前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间,提升查询效率
- 按「字段个数」分类:单列索引、联合索引。
- 通过将多个字段组合成一个索引,该索引就被称为联合索引。
- 联合索引的最左匹配原则,在遇到范围查询(如 >、<)的时候,就会停止匹配
- 建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段越有可能被更多的 SQL 使用到。
什么时候需要 / 不需要创建索引?
-
索引也是有缺点的,比如:
-
需要占用物理空间,数量越大,占用空间越大;
-
创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增大;
-
会降低表的增删改的效率,因为每次增删改索引,B+ 树为了维护索引有序性,都需要进行动态维护
-
-
什么时候适用索引?
- 字段有唯一性限制的,比如商品编码。
- 经常用于
WHERE
查询条件的字段,这样能够提高整个表的查询速度,如果查询条件不是一个字段,可以建立联合索引。 - 经常用于
GROUP BY
和ORDER BY
的字段,这样在查询的时候就不需要再去做一次排序了,因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。
-
什么时候不需要创建索引?
WHERE
条件,GROUP BY
,ORDER BY
里用不到的字段,索引的价值是快速定位,如果起不到定位作用的字段通常是不需要创建索引的,因为索引是会占用物理空间的。- 字段中存在大量重复数据,不需要创建索引,MySQL 有一个查询优化器,查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。
- 表数据太少的时候,不需要创建索引。
- 经常更新的字段不用创建索引,比如不要对电商项目的用户余额建立索引,因为索引字段频繁修改,由于要维护 B+Tree的有序性,那么就需要频繁的重建索引,这个过程是会影响数据库性能的。
有什么优化索引的方法?
- 前缀索引优化:使用某个字段中字符串的前几个字符建立索引。可以减小索引字段大小,节省空间。可以增加一个索引页存储前缀索引值,提高索引查询速度。
- 前缀索引有一定的局限性,例如:
- order by 就无法使用前缀索引;
- 无法把前缀索引用作覆盖索引;
- 前缀索引有一定的局限性,例如:
- 覆盖索引优化:SQL 中查询的所有字段,在索引 B+Tree 的叶子节点上都能找得到的那些索引,从二级索引中查询得到记录,而不需要通过聚簇索引查询整行记录的所有信息,可以避免回表的操作。
- 主键索引最好是自增的:如果我们使用自增主键,那么每次插入的新数据就会按顺序添加到当前索引节点的位置,不需要移动已有的数据,当页面写满,就会自动开辟一个新页面。因为每次插入一条新记录,都是追加操作,不需要重新移动数据,因此这种插入数据的方法效率非常高。
- 如果我们使用非自增主键,可能产生页分裂。页分裂还有可能会造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率。
- 索引列最好设置为NOT NULL约束
- 第一原因:索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂,更加难以优化,因为可为 NULL 的列会使索引、索引统计和值比较都更复杂,比如进行索引统计时,count 会省略值为NULL 的行。
- 第二个原因:NULL 值是一个没意义的值,但是它会占用物理空间。
防止索引失效
发生索引失效的情况:
- 当我们使用左或者左右模糊匹配的时候,也就是
like %xx
或者like %xx%
这两种方式都会造成索引失效。- like %xx:查询后缀为xx的数据。
- 因为索引 B+ 树是按照「索引值」有序排列存储的,只能根据前缀进行比较。
- 索引列发生改变:当我们在查询条件中对索引列进行计算、使用函数,这些情况下都会造成索引失效。
- 因为索引保存的是索引字段的原始值,而不是经过计算后的值。
- MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。如果字符串是索引列,而输入的参数是数字的话,那么索引列会发生隐式类型转换,由于隐式类型转换是通过 CAST 函数实现的,等同于对索引列使用了函数,所以就会导致索引失效。
- 联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效。
- 索引下推优化(index condition pushdown), 可以在联合索引遍历过程中,对联合索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
- 在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。
- 因为 OR 的含义就是两个只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描。
LeetCode
-
leetcode494
既然为target,那么就一定有 left组合 - right组合 = target。
left + right = sum,而sum是固定的。right = sum - left
公式来了, left - (sum - left) = target 推导出 left = (target + sum)/2 。
target是固定的,sum是固定的,left就可以求出来。
假设加法总和为x,减法总和为sum - x。x = (target + sum) / 2。
问题转化为:装满容量x的背包,有几种方法?是一个组合问题。
-
dp数据及其下标含义:dp[j]表示装满 j 容积的背包,有dp[j]种装法。
-
确定递推公式:
所以,递推公式为:
dp[j] += dp[j - nums[i]]
。后面还会用到这种递推公式。 -
dp数组如何初始化:dp[0] = 1,因为当x为0时,有1种组合方案。
-
确定遍历顺序:与之前类似,外层遍历nums,内层倒序遍历x。
-
举例推导dp数组
class Solution { public int findTargetSumWays(int[] nums, int target) { /** //01背包问题:left - right = target。 right = sum - left left = (target + sum) / 2 */ int sum = 0; for(int i : nums){ sum += i; } if(sum < Math.abs(target)){ return 0; } if((target + sum) % 2 != 0){ return 0; } int size = (target + sum) / 2; int dp [] = new int [size + 1]; dp[0] = 1; for(int i = 0 ;i < nums.length ;i ++){ for(int j = size;j >= nums[i]; j --){ dp[j] += dp[j - nums[i]]; } } return dp[size]; } }
-
-
leetcode474
给定背包容量(可以装m个0和n个1)求背包可以装满的最大容量(最大子集)。strs[ i ] 看作是1个物品,只不过是将0和1两个不同的维度分开。该物品的价值为一个子集。最大容量(最大子集)为
dp[m][n]
。-
dp数组及下标含义:
dp[i][j]
表示i个0和j个1的strs的最大子集。 -
确定递推公式:
dp[i][j] = Math.max(dp[i][j] , dp[i - zeorNum][j - oneNum] + 1)
-
dp数组初始化:初始化为0即可。
-
遍历顺序:物品就是strs里的字符串,背包容量就是题目描述中的m和n。所以外层正序遍历zreoNum,内层倒序遍历oneNum。
-
举例推导:
-
class Solution {
public int findMaxForm(String[] strs, int m, int n) {
int dp [] [] = new int [m + 1][n + 1];
for(String s : strs){
int zeroNum = 0 , oneNum = 0;
for(int i = 0 ;i < s.length() ;i ++){
char c = s.charAt(i);
if(c == '0'){
zeroNum ++;
}else oneNum ++;
}
//外层for循环表示每次取物体放入背包。
//该for循环表示两个维度的具体计算。
for(int i = m ;i >= zeroNum ;i --){
for(int j = n ;j >= oneNum ; j --){
dp[i][j] = Math.max(dp[i][j] , dp[i - zeroNum][j - oneNum] + 1);
}
}
}
return dp[m][n];
}
}
完全背包理论基础
有N件物品和一个最多能背重量为W的背包。第i件物品的重量是weight[i],得到的价值是value[i] 。每件物品都有无限个(也就是可以放入背包多次),求解将哪些物品装入背包里物品价值总和最大。
完全背包和01背包问题唯一不同的地方就是,每种物品有无限件。
01背包的一维dp数组内层背包容量是从大到小遍历,因为每件物品只有一件,要保证只放入一次。而完全背包不同,每件物品有无数件,所以从小到大遍历即可。
for(int i = 0;i < n ;i ++){ //先遍历物品
for(int j = weight[i];j <= bagWeight; j ++){ //再遍历背包
dp[j] = Math.max(dp[j] , dp[j - weight[i]] + values[i]);
}
}
for(int j = 0;j <= bagWeight;j ++){ //先遍历背包
for(int i = 0;i < n;i ++){ //后遍历物品
if(j - weight[i] >=0) dp[j] = Math.max(dp[j] , dp[j - weight[i]] + values[i]);
}
}
LeetCode
-
leetcode 518
注意,该题是求组合数,所以用递推公式:
dp[j] += dp[j - coins[i]]
。在求装满背包有几种方案的时候,难点在于遍历顺序:
如果求组合数就是外层for循环遍历物品,内层for遍历背包。(正常遍历顺序,组合数不分数字先后)
如果求排列数就是外层for遍历背包,内层for循环遍历物品。
class Solution { public int change(int amount, int[] coins) { int n = coins.length; int dp [] = new int [amount + 1]; dp[0] = 1; for(int i = 0 ;i < n;i ++){ for(int j = coins[i]; j <= amount; j ++){ dp[j] += dp[j - coins[i]]; } } return dp[amount]; } }