数据库常见面试题——索引

最新推荐文章于 2024-09-16 20:17:25 发布

lumaomao_

最新推荐文章于 2024-09-16 20:17:25 发布

阅读量9.1k

点赞数 3

分类专栏： java

本文链接：https://blog.csdn.net/lumaomao_/article/details/97896065

版权

java 专栏收录该内容

58 篇文章 0 订阅

订阅专栏

如何设计一个关系型数据库

索引模块

为什么要使用索引

快速查询数据

什么样的信息能够成为索引

主键、唯一键以及普通键等

索引的数据结构

生成索引，建立二叉查找树进行二分查找树（平衡二叉树、红黑树）
生成索引，建立B-Tree结构进行查找
生成索引，建立B+-Tree结构进行查找（MySQL）
生成索引，建立Hash结构进行查找

B-Tree

定义：

根节点至少包括两个孩子
树中每个节点最多含有m个孩子(m>=2)
除根节点和叶节点外,其他每个节点至少有ceil(m/2)个孩子
所有叶子节点都位于同一层

B-Tree举例

B+-Tree

非叶子节点的子树指针与关键字个数相同
非叶子节点的子树指针p[i]，指向关键字值[k[i],k[i+1])子树
非叶子节点仅用来索引，数据都保存在叶子节点中
所有叶子节点均有一个链指针指向下一个叶子节点

B+Tree更适合用来做存储索引

B+树的磁盘读写代价更低
B+树的查询效率更加稳定
B+树更有利于对数据库的扫描

Hash索引

缺点：

仅仅能满足“=”，“IN”，不能使用范围查询
无法被用来避免数据的排序操作
不能利用部分索引键查询
不能避免表扫描
遇到大量Hash值相同的情况后性能并不一定就会比B+Tree高

BitMap（了解）

所以主流的数据库采用的是B+树做索引

密集索引和稀疏索引的区别

密集索引文件中的每个搜索码值都对应一个索引值（叶子节点保存的不仅仅是键值还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的排列顺序，一个表只能有一个排列顺序，所以一个表只能有一个密集索引）
稀疏索引文件只为索引码的某些值建立索引项

MySQL主要有两种存储引擎：InnoDB（有且仅有一个密集索引，索引和数据存储在一个文件）和MyISAM（全部是稀疏索引，索引和数据存储在不同文件）

InnoDB

若一个主键被定义,该主键则作为密集索引
若没有主键被定义,该表的第一个唯一非空索引则作为密集索引
若不满足以上条件, innodb內部会生成一个隐藏主键(密集索引)
非主键索引存储相关键位和其对应的主键值,包含两次查找

索引部分常见面试题

如何定位并优化慢查询Sql

（1）根据慢日志定位慢查询sql

慢日志的定义：用来记录我们执行的比较慢的SQL

我们可以通过如下语句查询MySQL对数据库慢查询的设置

show variables like '%query%'

查询结果：

查询慢查询的数量：

show status like '%slow_queries%'

我们修改一下设置：

set global slow_query_log = on;--设置慢查询日志为开启状态
set global long_query_time = 1;--设置慢查询日志时间阈值为1s

注意：这样修改只会在本次服务器开启有用，服务器重启之后又会变为默认值，我们将配置文件修改了就可以永久生效了

这样我们就会记录下所有运行时间超过1s的SQL语句，可以在上述的慢日志存储路径中查看

我们先来制造一个慢查询用到的数据库（共40W条数据）

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

/**
 * @author lmm E-mail:violet_mmhh@163.com
 * @time 时间：2019年7月31日
 * @function 功能：制造10万条数据库用于测试SQL慢查询
 */
public class SlowqueryTest {
	public static Connection getConnection() {
		Connection conn;
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
			String user = "root";
			String pwd = "数据库密码";
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (SQLException e) {
			e.printStackTrace();
			conn = null;
		}
		return conn;
	}

	public static void main(String[] args) {
		Connection conn = getConnection();
		String sql = null;
		Random rand = new Random();
		try {
			Statement stat = conn.createStatement();
			for (int i = 0; i < 200000; i++) {
				System.out.println(i);
				int x = rand.nextInt(100000);
				// sql = "insert into slowquerytest values(default,'光头" + i +
				// "')";
				// sql = "insert into slowquerytest values(default,'有头发" + i +
				// "')";
				sql = "insert into slowquerytest values(default,'流浪" + x + "')";
				stat.execute(sql);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}

	}
}

我们尝试形成一个慢查询记录：

select name from slowquerytest order by name desc

（2）使用 explain等工具分析sql

explain select name from slowquerytest order by name desc

Explain关键字段：

id：表示SQL的执行顺序，id越大越先执行（复合查询里面的语句id大、先执行）
type：表示MySQL找到需要的数据行的方式（system>const>eq_ref>ref>fulltext>ref_or_null>index_merge
>unique_subquery>index_subquery>range>index>all，其中index和all表示本次查询是全表扫描，因此很有可能可以（需要）优化）
extra：如下图所示

（3）修改sql或者尽量让sql走索引

我们把查询语句改为使用索引来看一下效果

select id from slowquerytest order by id desc

运行结果

可以看到查询效率直接有1.495秒变成了0.187秒，提升了87.5%的效率（TQL）

可惜的是，我们有时候查的必须要是按name来排序，这是我们可以考虑给字段加索引！

我们尝试以下语句

alter table slowquerytest add index index_name(name)
--给name字段加索引，这条语句虽然执行慢但不是查询语句，不会进入慢查询记录

再次查询name，结果如下

注意其他一些内容：

explain select count(id) from slowquerytest force index (primary)--force index 强制使用指定索引

联合索引的最左匹配原则的成因

什么是联合索引的最左匹配原则

参考

最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a=3 and b=4 and c>5 and d=6 如果建立(a,b,c,d)顺序的索引,d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以仼意调整。
=和in可以乱序，比如a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,mysq的查询优化器会帮你优化成索引可以识别的形式