SQL常见面试题

article/2025/10/3 15:42:23

  • SQL常见面试题
      • 关系型数据库（SQL）
      • 非关系型数据库（NoSQL）
      • 数据库三大范式
      • 主键与外键
      • CHAR与VRCHAR数据类型
      • 临时表
      • 数据库函数、触发过程与存储器
    • SQL语句
      • SQL语言分类
      • DROP、TRUNCATE、DELETE的区别
      • sum、count(\*)、count(1)、count(column)的区别
      • 自然连接、内连接、外连接、交叉连接的区别
    • 事务
      • 什么是事务
      • 事务的特性（ACID）
      • 事务并发导致的问题
      • 事务的隔离级别
    • 数据库锁
      • 锁的级别
      • 数据库死锁
      • 悲观锁与乐观锁
        • 悲观锁
        • 乐观锁
        • 如何选择乐观锁或悲观锁
    • 存储引擎
      • InnoDB与MyISAM
      • 如何选择引擎
    • 索引
      • 索引是什么
      • 索引的优缺点
      • 添加索引的原则
      • 如何创建索引
      • 索引的类型
      • 聚簇索引与非聚簇索引
      • 索引的底层实现
        • Hash索引
        • B-树索引
        • B+树索引
      • 为什么索引结构默认使用B+树
      • 最左前缀匹配原则
      • 索引在什么情况下会失效
    • 优化
      • SQL语言的执行顺序
      • SQL优化方法有哪些
        • 索引使用技巧
        • 其他
      • 分析SQL执行情况

SQL常见面试题

关系型数据库（SQL）

关系型数据库是由二维表及其之间的联系所组成的一个数据组织，最典型的数据结构是表，即所有的数据都通过行和列的二元表现形式表示出来

常用的关系型数据库：mysql / oracle / sql server / sqlite

优点：
1、保持了数据的一致性（最大优势），可以进行事务处理
2、支持通用的SQL（结构化查询语言）语句
3、可以在一个表以及多个表之间进行复杂的数据查询，如join
4、二维表结构非常贴近逻辑世界的概念，更容易理解

缺点：
1、高并发读写能力差，一台数据库的最大连接数有限，硬盘 I/O 也有限，不能满足很多人同时连接
2、海量数据情况下读写效率低。对大数据量的表进行读写操作时，需要等待较长的时间
3、数据结构灵活度低，固定的表结构无法快速容纳新的数据类型

非关系型数据库（NoSQL）

非关系型数据库又称NoSQL（Not only SQL )，意为不仅仅是 SQL。其中数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定

常见的非关系型数据库：键值数据库Redis、文档数据库MongoDB、图形数据库InfoGrid、列族数据库Bigtable等

优点：
1、格式灵活，存储数据的格式可以是key-value形式、文档形式、图片形式等，而关系型数据库则只支持基础类型
2、查询速度快，NoSQL将数据存储于缓存之中，而关系型数据库将数据存储在硬盘中
3、数据之间没有耦合性，非常容易进行水平扩展
4、高并发、高稳定性、成本低廉、可以实现数据的分布式处理

缺点：
1、不支持 SQL 语句，学习和使用成本高
2、没有事务处理，无法保证数据的完整性和安全性，适合处理海量数据，但不一定安全
3、复杂表关联查询不容易实现

数据库三大范式

  • 第一范式：确保每列的原子性

    第一范式是最基本的范式，如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式

    第一范式的合理遵循需要根据系统的实际需求来定。例如：某些数据库系统中包含“地址”属性，本来直接将“地址”属性设计成一个数据库表的字段即可，但是如果系统经常访问“地址”属性中的“城市”部分，那么就需要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，才算满足了数据库的第一范式：

    编号姓名性别年龄联系电话省份城市详细地址
    1张红欣男260378-23459876河南开封朝阳区新华路23号
    2李四平女320751-65432584广州广东白云区天明路148号
    3刘志国男210371-87659852河南郑州二七区大学路198号

  • 第二范式：确保表中每列都与主键相关

    第二范式是指在关系表中，不是主键的列都必须完全依赖于主键，而不是主键的一部分（即消除部分依赖，主要是针对联合主键而言）

    依赖：对于X的每个值，Y都有一个值与之对应，反过来则不一定不成立，这叫做X函数决定Y，Y函数依赖X

    部分依赖：当主键由两个或两个以上字段构成，而表中的某些信息通过主键的一个字段就能唯一确定

    例如：在学生选课表中，通过学号和课程号可以唯一确定一条记录，因此用学号和课程号做联合主键。但是表中的姓名、专业通过主键中的学号就能唯一确定，而课程名通过课程号就能唯一确定，这就是部分依赖，这样的设计不符合第二范式：

    学号姓名专业课程号课程名成绩
    001张三电子C01C++80
    002李四电子C01C++86
    001张三电子C02操作系统90
    002李四电子C02操作系统88

    不符合第二范式会带来的问题：

    1. 数据信息冗余
    2. 增删改会出现问题，比如有一门《微机原理》没有人选，那么由于缺少学号（主键之一）那么这门课就不能出现在表里。

    解决方案：用关系分解的方法消除部分依赖，将上表改成三张表

    学号姓名专业
    001张三电子
    002李四电子
    课程号课程名
    C01C++
    C02操作系统
    学号课程号成绩
    001C0180
    002C0186
    001C0290
    002C0288

  • 第三范式：确保每列都和主键列直接相关

    第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。第三范式的目的是移除那些不是直接依赖于主键的属性，这些属性是借由另一个属性来依赖于主键的（即A依赖于B，B依赖于C，就可以说A依赖C，形成了传递依赖）

    例如：下表中有如下决定关系： 学号 –> 姓名，性别；系号 –> 系名；宿舍号 –> 宿舍电话，同时又有学号 –> 系名，学号 –> 宿舍电话，即存在着传递依赖。

    学号姓名性别系号系名宿舍号宿舍电话
    J001张三男X01计算机10313579
    D005李四男X02电子20524680
    S005王五男X03生物30912345

    这样设计表会带来数据冗余，操作异常等问题。同样可以用关系分解的分解的方法来消除传递依赖，将这张表分成三张表：

    系号系名
    X01计算机
    X02电子
    X03生物
    宿舍号宿舍电话
    10313579
    20524680
    30912345
    学号姓名性别系号宿舍号
    J001张三男X01103
    D005李四男X02205
    S005王五男X03309

主键与外键

  • 主键：主键是能确定一条记录的唯一标识，不可重复，不能为空，一张表只能有一个主键
  • 外键：外键用来建立与其他表的连接，是另一张表的主键，可以重复，可以为空，一张表可以有多个外键

CHAR与VRCHAR数据类型

  1. CHAR的长度是不可变的，VARCHAR的长度是可变的

    当定义的是char(10)，输入的是"abc"这三个字符时，它们所占空间是10个字节，其中包括7个空字节，当输入的字符长度超过指定的数时，char会截取超出的字符。在取数据时，char类型的要去除多余的空格，而对于varchar类型则不需要

  2. CHAR的存取速度比VARCHAR快，因为其长度固定，方便程序的存储与查找

    char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。varchar则是以空间效率为首位

  3. CHAR的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
    VARCHAR的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。

  4. 两者的存储数据都非unicode的字符数据

临时表

临时表只在当前连接可见，当关闭连接时会被自动删除并释放所有空间，因此可以在不同连接中创建同名的临时表

临时表的创建与语法为CREATE TEMPOPARY TABLE tmp_table

临时表也可以手动删除DELETE TEMPOPARY TABLE IF EXISTS tmp_table

数据库函数、触发过程与存储器

存储过程函数
参数类型可以使用IN、OUT、IN OUT三种模式的参数只有IN
返回值可以通过OUT、IN OUT参数返回零个或多个参数值必须有返回值，且必须为单一值或单一对象
是否可以单独执行可以必须通过execute执行
是否可以通过SQL语句
（DML或SELECT）调用		不可以可以，且可以位于FROM关键字的后面

数据库函数

数据库中的函数包括内置函数（如SUM()，COUNT()，AVG()）和自定义函数，具有以下特点：

  1. 函数只有一种参数模式（IN），只能有一条RETURN语句，只能返回单一值
  2. 函数可以在DML语句或SELECT子句中进行调用。由于函数可以返回一个表对象，因此它可以在查询语句中放在FROM后面

存储过程

存储过程是一组完成特定功能的SQL语句集，经编译后存储在数据库中，具有以下特点：

  1. 存储过程是预编译过的，执行效率高
  2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯
  3. 安全性高，执行存储过程需要有一定权限的用户
  4. 可以重复使用，可减少数据库开发人员的工作量
  5. 缺点是可移植性差

调用方法：

  1. 可以用一个命令对象 execute <过程名> 来调用存储过程
  2. 可以供外部程序调用，如 java 程序

触发器

触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致。

SQL语句

SQL语言分类

  • 数据库定义语句DDL（Data Definition Language）

    用于创建、修改和删除数据库对象

    DDL语句功能说明
    CREATE创建数据库结构
    ALTER修改数据库结构
    DROP删除数据库结构
    RENAME更改数据库对象的名称
    TRUNCATE删除表的全部内容

  • 数据查询语句DQL（Data Query Language）

    数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块

  • 数据库操纵语句DML（Data Maintain Language）

    用来处理数据库中的数据内容

    DML语句功能说明
    INSERT插入数据到表或视图
    UPDATE更新数据
    DELETE删除数据
    CALL调用过程
    MERGE合并（插入或修改）

  • 数据库控制语句DCL（Data Control Language）

    用于修改数据库结构的操作权限

    DCL语句功能说明
    CRANT授予其他用户对数据库结构的访问权限
    REVOKE收回用户访问数据库结构的权限
    COMMIT将当前事务所做的更改永久化（写入数据库）
    ROLLBACK取消上次提交以来的所有操作

DROP、TRUNCATE、DELETE的区别

用法类型功能
DROPDROP TABLE 表名DDL删除整个表的数据与结构
TRUNCATETRUNCATE TABLE 表名DDL删除整张表的数据，但保留表结构
DELETEDELETE TABLE 表名 WHERE 条件DML既可以删除整张表数据又可以删除单行
  • 执行速度：DROP > TRUNCATE > DELETE
  • DROP 与 TRUNCATE 是DDL语言，操作即生效，不能进行回滚，不能激活触发器
    DELETE 是DML语言，会被放入回滚片段中，待事务提交后才生效，如果有相应的触发器，将在执行时激活
  • TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同，均删除表中的全部行
    但 TRUNCATE 比 DELETE 速度快，且使用的系统和事务日志资源少
  • 使用 TRUNCATE 清空表后索将重新设置成初始大小，而 DELETE 则不能
  • 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句，这是因为TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器

sum、count(*)、count(1)、count(column)的区别

count 用于求行的个数；sum 用于累加求和

  • count(*) 对行的数目进行计算，包含NULL
  • count(1) 与 count(*) 效果相同
  • count(column) 对特定列的行数进行计算，不包含NULL

自然连接、内连接、外连接、交叉连接的区别

table1：

ABC
123
567

table2：

CDE
345
891

  • 自然连接（natural join）

    自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列

    SELECT * FROM table1 NATURAL JOIN table2
    CABDE
    31245

  • 内连接（INNER JOIN）

    内连接与自然连接类似，不同之处在于内连接不要求两属性列同名，而是使用using或on来指定某两列字段相同的连接条件

    SELECT * FROM table1 INNER JOIN table2 on  table1.A = table2.E
    ABCC(table2)DE
    123891
    567345

  • 左外连接（LEFT OUTTER JOIN，OUTER可以省略）

    返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值

    SELECT * FROM table1 LEFT JOIN table2 on  table1.C = table2.C
    ABCC(table2)DE
    123345
    567NULLNULLNULL

  • 右外连接（RIGHT OUTTER JOIN，OUTER可以省略）

    返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值

    SELECT * FROM table1 RIGHT JOIN table2 on  table1.C = table2.C
    ABCC(table2)DE
    NULLNULLNULL891
    123345

  • 全外连接（FULL OUTTER JOIN，OUTER可以省略，MySQL不支持）

    返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值

  • 交叉连接（CROSS JOIN）

    相当于笛卡尔积，它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

    ABCC(table2)DE
    123891
    567345
    123345
    567891

事务

什么是事务

事务（transaction）是作为一个单元的一组有序的数据库操作集合，用来保持数据的完整性和一致性，事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原

例如，小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

事务的特性（ACID）

  • 原子性（Atomicity）

    表示组成一个事务的多个操作是一个不可分割的原子单元，只有当事务中所有操作成功时，才对整个事务进行提交；事务中任何一步的失败都会导致整个事务失败，需要进行回滚来撤销已执行的操作，让数据库回到初始状态

  • 一致性（Consistency）

    事务开始前和结束后，数据库的完整性约束不能被破坏。例如A向B转账，不管操作成功与否，A和B账户的总和应该是不变的

  • 隔离性（Isolation）

    隔离性是指当有多个用户并发操作数据库时，数据库为每一个用户开启的事务不能被其他事务的操作所影响，也就是说多个并发事务之间要相互隔离。例如A要向某账户存钱，B要从该账户取钱，那么在A存钱的过程中B不能进行操作

  • 持久性（Durability）

    持久性是指当某一事务被提交后，那么它对于数据库的改变就是永久性的，即使在系统故障情况下也不会丢失

事务并发导致的问题

  • 脏读（Dirty Read）

    事务A读取了事务B更新但尚未提交的数据，然后事务B进行了回滚操作，那么事务A读取到的数据就是脏数据

  • 不可重复读（Non-repeatable read）

    事务A多次读取同一数据，而事务B在这个过程中对数据进行了更新并提交，导致事务A多次读取的结果不一致

  • 幻读（Phantom Read）

    事务A多次读取数据并统计读取数据的条数，在这个过程中事务B新增了数据行并提交，导致事务A多次读取得到的结果条数不一样

注：不可重复读与幻读的区别在于一个是读取了其他事务更新的数据，一个是读取了其他事务新增的数据。要避免不可重复读可以根据条件锁定行，而避免幻读则需要锁定整张表

事务的隔离级别

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，可以逐个解决脏读、不可重复读和幻读的问题

隔离级别脏读不可重复读幻读
读取未提交√√√
读取已提交×√√
可重复读××√
串行化×××

  • 读取未提交（READ-UNCOMMITED）

    最低隔离级别

  • 读取已提交（READ-COMMITTED）

    仅能够阻止脏读

  • 可重复读（REPEATED-READ）

    对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，该级别也是MySQL默认采取的隔离级别

  • 可串行化（SERIALIZABLE）

    最高隔离级别，完全符合ACID要求，即所有事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

注：隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，但在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

数据库锁

锁的级别

MySQL有三种级别的锁：表级、行级、页面级

  • 表级锁

    开销小，加锁快，不会发生死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低

  • 行级锁

    开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高

  • 页面级锁

    开销和加锁时间介于表级锁和行级锁之间，会出现死锁，锁定粒度和并发度一般

数据库死锁

死锁是指两个或两个以上的进程在执行过程中，因为争夺资源而造成的一种相互等待的现象，若无外界作用，它们都将无法推进下去，死锁的关键在于加锁的顺序不一致

解决办法：

  1. 查出线程并用kill杀死
  2. 设置锁的超时时间
  3. 指定获取锁的顺序

悲观锁与乐观锁

悲观锁

先获取锁，再进行业务操作

即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。

实现方式：

通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现。当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

注意：

不同的数据库对select for update的实现和支持都是有所区别的，

  • oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，MySQL就没有no wait这个选项。
  • MySQL还有个问题是select for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引，而不是全表扫描。

乐观锁

先进行业务操作，不到万不得已不去拿锁

乐观锁也叫乐观并发控制，它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，那么当前正在提交的事务会进行回滚。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。

实现方式：

乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号，或者时间戳

  1. SELECT data AS old_data, version AS old_version FROM …;

  2. 根据获取的数据进行业务操作，得到new_data和new_version

  3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

    if (updated row > 0) {

    ​ // 乐观锁获取成功，操作完成 }

    else {

    ​ // 乐观锁获取失败，回滚并重试 }

注意：

  • 乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能
  • 乐观锁还适用于一些比较特殊的场景，例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方。

如何选择乐观锁或悲观锁

悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法，大部分场景下差异不大，一些独特场景下有一些差别，一般我们可以从如下几个方面来判断。

  • 响应速度

    如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁

  • 冲突频率

    如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大

  • 重试代价

    如果重试代价大，建议采用悲观锁

存储引擎

MySQL有多种存储引擎，如MyISAM、InnoDB、MERGE、EXAMPLE、CSV、等等，但常用的只有MyISAM与InnoDB两个，其中InNoDB是MySQL的默认引擎

InnoDB与MyISAM

InnoDBMyISAM
事务支持不支持
锁行级锁表级锁
主外键支持不支持
索引类型聚簇索引非聚簇索引
缓存只缓存索引缓存索引和真实数据
表空间小大
  • InnoDB支持事务，而MyISAM不支持，这是两引擎最大的区别
    对于InnoDB来说，支持事务是以损失效率来换取的，InnoDB的默认事务隔离级别是可重复读
  • InnoDB使用的锁粒度为行级锁，可以支持更高的并发
    InnoDB的行锁是有条件的，只是在WHERE的主键是有效的，非主键的WHERE也会锁全表
    MyISAM最小的锁粒度是表锁，一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞
  • InnoDB支持外键，而MyISAM不支持
    对一个包含外键的 InnoDB 表转为 MYISAM 会失败
  • InnoDB是聚簇索引，MyISAM 是非聚簇索引
    聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB必须要有主键，通过主键索引效率很高
    MyISAM索引和数据是分开的，而且其索引经过压缩，可以更好地利用内存，所以它的查询性能更高
  • InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描
    MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
  • InnoDB不支持全文索引，MyISAM支持，这可以极大地优化LIKE查询的效率

如何选择引擎

如果考虑使用事务或读写密集型操作，则应选择INNODB引擎

对查询性能要求较高，应使用MyISAM

索引

索引是什么

索引是一种数据结构，数据库索引就是对数据库表中一列或多列的值进行排序的结构，可以帮助数据库实现快速的查询与更新。

索引可以理解为书的目录，字典的拼音、偏旁部首检索，都是为了方便查找内容而建立的

索引的优缺点

优点：

  • 可以大大加快数据的检索速度（最大的优点）
  • 可以加速表与表之间的连接
  • 在使用分组和排序子句进行索引时，同样能显著减少分组与排序的时间
  • 通过建立唯一性索引，可以保证表中数据的唯一性
  • 通过使用索引，可以在查询过程中使用优化隐藏器，提高系统性能

缺点：

  • 时间：创建索引和维护索引都需要耗费时间，具体来说，当对表中数据进行增删查改时，索引也需要动态地维护
  • 空间：索引需要占据一定的物理空间

添加索引的原则

应该创建索引的列：

  • 经常需要搜索的列

    显著加快搜索的速度

  • 作为主键的列

    强制该列的唯一性和组织表中数据的排列结构

  • 经常用作连接的列（通常是外键）

    加快表与表之间的连接速度

  • 经常需要根据范围搜索的列

    索引已经排序，其指定的范围是连续的

  • 经常需要排序的列

    索引已经排序，这样查询可以利用索引的排序，加快排序查询时间

  • 经常使用在WHERE子句上的列

    加快条件的判断速度

不应该创建索引的情况

  • 在查询中很少使用的列不应建立索引

    如果列很少被使用到，那么有索引并不能提高查询速度，反而会降低系统的维护速度和增加空间需求

  • 只有很少数据值的列不应建立索引

    如信息表中的性别列，只有两种结果值，对其建立索引并不能明显加快检索速度

  • 定义为text、image和bit数据类型的列不应该建立索引

    这些列的数据要么数据量相当大，要么取值很少

  • 当修改性能远大于检索性能时不应建立索引

    修改性能与检索性能是相互矛盾的，当增加索引时会降低修改性能

如何创建索引

索引创建有三种方式：

  1. 在执行 CREATE TABLE 时创建

    CREATE TABLE user_table(id INT NOT NULL PRIMARY KEY,name VARCHAR(20) NULL,sex VARCHAR(2) NULL,info VARCHAR(20) NULL,INDEX index_id(id),				-- 普通索引UNIQUE INDEX index_id(id),		 -- 唯一索引FULLTEXT INDEX index_id(id),	 -- 全文索引KEY index_id_name(id,name)		 -- 组合索引
);

  2. 使用 ALTER TABLE 增加索引

    ALTER TABLE user_table ADD INDEX index_id(id)				-- 普通索引
ALTER TABLE user_table ADD UNIQUE INDEX index_id(id)		 -- 唯一索引
ALTER TABLE user_table ADD FULLTEXT INDEX index_id(id)		 -- 全文索引
ALTER TABLE user_table ADD INDEX index_id_name(id,name)       -- 组合索引

  3. 使用 CREATE INDEX 命令创建

    CREATE INDEX index_id ON user_table(id)				-- 普通索引
CREATE UNIQUE INDEX index_id ON user_table(id)		 -- 唯一索引
CREATE FULLTEXT INDEX index_id ON user_table(id)	 -- 全文索引
CREATE INDEX index_id_name ON user_table(id,name)     -- 组合索引

索引的类型

从索引的存储形式划分：

  • BTree索引（包括B+Tree和B-Tree）
  • Hash索引
  • Full-index全文索引
  • RTree索引

从应用层次划分：

  • 主键索引：一张表中只能存在一个，不可重复，不能为NULL
  • 组合索引：由多个列值组成的索引
  • 唯一索引：不可重复，但可以为空，索引列的值必须是唯一的，如果是组合索引，则组合的列值必须是唯一的
  • 全文索引：对文本内容进行搜索
  • 普通索引：基本的索引类型

从数据与索引是否分开存储划分：

  • 聚簇索引：将数据与索引放在一起储存，索引的叶子结点保存了数据行
  • 非聚簇索引：将数据与索引分开储存，索引的叶子结点存储的是指向数据行的地址

聚簇索引与非聚簇索引

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。或者说，聚集索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块

聚集索引类似新华字典中的拼音排序索引，都是按顺序进行。比如，我们要查“安”字，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。
而非聚集索引则类似于偏旁部首索引，索引顺序和物理顺序并不是按顺序存放的。比如查“张”字，可以看到在查部首之后的检字表中“张”的页码是672页，而“张”的上面“驰”字页码是63页，“张”的下面是“弩”字页面是390页

索引的底层实现

Hash索引

Hash索引是基于哈希表实现的，对于每一行数据，都需要通过哈希函数计算其索引列值对应的哈希码，将哈希码的值作为哈希表的key，将指向数据行的指针作为value进行存储，这样查找一个数据的时间复杂度就是o(1)，一般多用于精确查找。

B-树索引

img

B-树（又称为B树）是一种平衡的多叉查找树，也就是说最多可以开m个叉（m>=2），具有以下特点：

  • 每个叶子结点的高度一样
  • 关键字集合分布在整颗树中
  • 任何一个关键字出现且只出现在一个节点中
  • 搜索有可能在非叶子节点结束
  • 其搜索性能等价于在关键字集合内做一次二分查找
  • B树在插入删除新的数据记录会破坏B-Tree的性质，因为在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质

B树的查询过程和二叉排序树比较类似，从根节点依次比较每个结点，如果查找失败，则会返回叶子节点，即空指针

例如查询图中字母表中的K：

  1. 从根节点P开始，K的位置在P之前，进入左侧指针
  2. 左子树中，依次比较C、F、J、M，发现K在J和M之间
  3. 沿着J和M之间的指针，继续访问子树，并依次进行比较，发现第一个关键字K即为指定查找的值

B+树索引

img

作为B树的加强版，B+树与B树的差异在于：

  • 有n棵子树的节点含有n个关键字（也有认为是n-1个关键字）
  • 所有的关键字全部存储在叶子节点上，且叶子节点本身根据关键字自小而大顺序连接
  • 非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字

B+树的查找过程与B树类似，只不过如果在非叶子节点上的关键字等于给定值时并不终止，而是继续沿着指针直到叶子节点位置。因此在B+树，不管查找成功与否，每次查找都是走了一条从根到叶子节点的路径。

B+树的特性如下：

  • 所有关键字都存储在叶子节上，且链表中的关键字恰好是有序的
  • 不可能非叶子节点命中返回
  • 非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层
  • 更适合文件索引系统

为什么索引结构默认使用B+树

B+树的优点：

  1. 磁盘读写代价更低
  2. 查询效率更加稳定
  3. 有利于对数据库的扫描

Hash索引的缺点：

  1. 只支持等值查询，如“=”，“IN”，而不能使用范围查询
  2. 因为不是按照索引值顺序存储的，因此不能利用索引完成排序
  3. 始终索引全部列的全部内容，不支持部分索引列的匹配查找
  4. 在有大量重复键值的情况下，效率会降低

最左前缀匹配原则

最左匹配原则就是最左优先**，在创建多列索引时，应根据业务需求，将WHERE子句中使用最频繁的一列放在最左边**。这是因为MySQL 建立联合索引时，会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。

匹配原则：

  • 对于联合索引，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 = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。

  • 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性

索引在什么情况下会失效

  • 使用!=、<>进行判断会导致索引失效

    例如select * from table_name where a != 1

    注意：<>符号是不等于的意思，和!=的功能是一样的

  • 数据类型的隐式转换会导致索引失效

    假设 select * from table_name where a = '1'会使用到索引，如果没有对字符串加引号，写成select * from table_name where a = 1则会导致索引失效。

  • 在索引中使用函数会导致索引失效

    例如select * from table_name where abs(a) = 1

  • 在索引上进行计算会导致索引失效

    例如select * from table_name where a + 1 = 2

  • 条件中有OR会导致索引失效

    例如select * from table_name where a = 1 or b = 3

    注意：并不是所有的OR都会导致索引失效，如果OR连接的是同一字段将不会导致失效

  • 使用模糊查询时以%开头会导致索引失效

    例如select * from table_name where name LIKE '%表'

优化

SQL语言的执行顺序

SQL 不同于与其他编程语言的最明显特征是处理代码的顺序。在大多数编程语言中，代码按编码顺序被处理，但是在SQL语言中，第一个被处理的子句是FROM子句，尽管SELECT语句第一个出现，但是几乎总是最后被处理

(8) SELECT(9) DISTINCT column,…
选择字段 、去重
(6) AGG_FUNC(column or expression),…
使用聚集函数进行计算
(1) FROM [left_table]
选择表
(3) <join_type> JOIN <right_table>
链接
(2) ON <join_condition>
链接条件
(4) WHERE <where_condition>
基于指定的条件对记录行进行筛选
(5) GROUP BY <group_by_list>
将数据划分为多个分组
(7) HAVING <having_condition>
筛选分组
(10) ORDER BY <order_by_list>
对结果集进行排序
(11) LIMIT count OFFSET count;
分页

每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回给调用者。如果没有在查询中指定某一子句，将跳过相应的步骤。

SQL优化方法有哪些

索引使用技巧

  • 避免在where子句中使用!=或<>操作符

  • 避免数据类型的隐式转换

    【反例】
SELECT * FROM student WHERE NAME=123【正例】
SELECT * FROM student WHERE NAME='123'

  • 避免在索引列上使用内置函数

    【反例】
SELECT * FROM student
WHERE DATE(birthday) = NOW()【正例】
SELECT * FROM student
WHERE  birthday = DATE(NOW())

  • 避免在索隐列上使用表达式计算

    【反例】
SELECT * FROM student WHERE id=+1-1+1【正例】
SELECT * FROM student WHERE id=1

  • 避免使用OR来连接过滤条件

    【反例】
SELECT * FROM student WHERE id=1 OR salary=30000【正例】
SELECT * FROM student WHERE id=1			# 方法一：使用union all
UNION ALL
SELECT * FROM student WHERE salary=30000	SELECT * FROM student WHERE id=1			# 方法二：分开两条sql写
SELECT * FROM student WHERE salary=30000

  • 避免在字段开头模糊查询

    【反例】
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'【正例】
SELECT id,NAME FROM student WHERE NAME LIKE '1%'

  • 尽量避免使用IN和NOT IN

    【反例】
SELECT * FROM t WHERE id IN (2,3)
SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)【正例】
SELECT * FROM t WHERE id BETWEEN 2 AND 3	# 如果是连续数值，可以用between代替
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)	#如果是子查询，可以用exists代替

  • 尽量避免进行NULL值的判断

    【反例】
SELECT * FROM t WHERE score IS NULL【正例】
SELECT * FROM t WHERE score = 0		# 可以给字段添加默认值0，对0值进行判断

  • 索引并不是越多越好，一个表的索引数最好不要超过5个

其他

  • 尽量避免使用SELECT *

    返回无用的字段会降低查询效率

  • 去重DISTINCT过滤字段要少

    当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较与过滤，需要占用时间

  • 批量插入数据

    插入是DML语句，默认每条都需要事务开启和事务提交；而批量处理只需要一次事务开启和提交

    多条提交：
INSERT INTO student (id,NAME) VALUES(4,'name1')
INSERT INTO student (id,NAME) VALUES(5,'name2')批量提交：
INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2')

  • 分批删除数据

    一次性删除太多数据可能造成锁表，建议分批操作

    【反例】
delete from student where id <100000;	# 一次删除10万+for（User user:list）{				# 采用单一循环操作，效率低，时间漫长delete from student;
}【正例】
for(){								# 分批进行删除，如每次500
delete student where id<500;
}

  • 提高GROUP BY语句的效率

    【反例】
select job，avg（salary） from employee		# 先分组，再过滤
group by job 
having job ='president' or job = 'managent';【正例】
select job，avg（salary） from employee		# 先过滤，后分组
where job ='president' or job = 'managent' 
group by job;

  • 优先使用inner join

    三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小，即小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

  • 尽量使用union all替代union

    union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复

    去重的过程同样会占用时间，而实际大部分应用中是不会产生重复的记录

分析SQL执行情况

在SQL语句前使用EXPLAIN关键字，可以查看该条SQL语句是否使用了索引

扫描类型TYPE性能由低到高依次为：

  • all：全表扫描，没有优化，最慢的方式
  • index：需要扫描索引上的全部数据，它仅比全表扫描快一点
  • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见于between、<、>等查询
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于多表查询或普通非唯一索引
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于多表查询时命中后表的主键或唯一索引
  • const：命中主键或者唯一索引，且查询条件是一个常量值
  • system：该表只有一行（相当于系统表）

http://chatgpt.dhexx.cn/article/nUQCfiEZ.shtml

相关文章

常见的SQL面试题：经典50例

常见的SQL面试题：经典50例

SQL基础知识整理 select 查询结果&#xff0c;如: [学号,平均成绩&#xff1a;组函数avg(成绩)] from 从哪张表中查找数据&#xff0c;如:[涉及到成绩&#xff1a;成绩表score] where 查询条件&#xff0c;如:[b.课程号0003 and b.成绩>80] group by 分组&#xff0c;如:…
阅读更多...
5个必考的大厂SQL面试题

5个必考的大厂SQL面试题

学Python的同学&#xff0c;SQL也一定要学习&#xff0c;SQL几乎是每个数据岗的必备题目&#xff0c;下面分享几个常见的大厂SQL习题。 &#xff08;1&#xff09;找出连续7天登陆&#xff0c;连续30天登陆的用户&#xff08;小红书笔试&#xff0c;电信云面试&#xff09;&am…
阅读更多...
7 大开源数据库利弊全对比

7 大开源数据库利弊全对比

1、CUBRID CUBRID 是一个很好的免费开源选择&#xff0c;专门针对 Web 应用程序进行优化&#xff0c;当复杂的 Web 服务需要处理大量数据并生成巨大的并发请求时&#xff0c;CUBRID 非常有用。这个解决方案是用 C 写的。 优点&#xff1a; 多粒度锁定 在线备份 用于开发语言…
阅读更多...
还在用Navicat？这款开源的数据库管理工具界面更炫酷！

还在用Navicat？这款开源的数据库管理工具界面更炫酷！

数据库管理工具&#xff0c;是后端程序员使用频率非常高的的工具。Navicat、DataGrip虽然很好用&#xff0c;但都是收费的。最近在逛Github的时候&#xff0c;无意间发现了一款开源的数据库管理工具Beekeeper Studio&#xff0c;界面非常炫酷推荐给大家&#xff01; Beekeeper…
阅读更多...
开源数据库管理系统现在比商业产品更受欢迎

开源数据库管理系统现在比商业产品更受欢迎

原文链接&#xff1a;https://db-engines.com/en/blog_post/86 2021年1月13日 作者&#xff1a;马蒂亚斯盖尔曼&#xff08;Matthias Gelbmann&#xff09; Matthias Gelbmann是奥地利维也纳Solid IT联合创始人&#xff0c;董事总经理兼顾问。 Matthias Gelbmann在维也纳学习了…
阅读更多...
你了解世界上功能最强大的开源数据库吗？

你了解世界上功能最强大的开源数据库吗？

如果不是领导强制要求&#xff0c;可能根本不会留意到这款号称世界上功能最强大的开源数据库——PostgreSQL。如果你不读这篇文章&#xff0c;或许也会错过一个跃跃欲试想挤进前三的优秀数据库。 为了能够熟练运用&#xff0c;特意买书研究&#xff0c;发现这款数据库还真有点…
阅读更多...
开源数据库列表

开源数据库列表

转载于&#xff1a;http://database.csdn.net/subject/databaseopen.htm 编辑导语 开源数据库最初的诞生和发展大都依靠自由软件开发者&#xff0c;但是&#xff0c;现在越来越多的IT公司开始把触角伸向了开源数据库。而早期投身于其中的IT厂商早已获利&#xff0c;比如Sleepy…
阅读更多...
TuGraph 开源数据库体验

TuGraph 开源数据库体验

TuGraph 开源数据库体验 文章目录 TuGraph 开源数据库体验1. 简单介绍2. 可视化界面体验&#xff1a;查询界面&#xff1a;数据建模&#xff1a;数据导入&#xff1a; 3. 体验心得&#xff1a; 1. 简单介绍 TuGraph 是蚂蚁集团自主研发的大规模图计算系统&#xff0c;提供图数…
阅读更多...
数据库与开源的未来

数据库与开源的未来

大家好&#xff0c;社区的小伙伴可能已经发现CnosDB已经全面拥抱Rust。我们一直高度关注行业趋势的发展&#xff0c;拥抱新兴的语言和前沿的技术。本期Jesse就想跟大家聊聊数据库与开源的未来。 本文仅代表个人观点&#xff0c;如有偏颇之处&#xff0c;还请海涵&#xff5e; …
阅读更多...
做了7年开源数据库开发，我学到了什么？

做了7年开源数据库开发，我学到了什么？

作者 | PHILIP OTOOLE&#xff0c;已获作者授权 译者 | 弯月 责编 | 欧阳姝黎 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 2016年4月9日&#xff0c;第一版rqlite&#xff08;https://github.com/rqlite/rqlite/releases/tag/v1.0&#xff09;正式发布&a…
阅读更多...
【数据库】什么是 PostgreSQL？开源数据库系统

【数据库】什么是 PostgreSQL？开源数据库系统

文章目录 前言什么是 PostgreSQL&#xff1f;PostgreSQL 中的 SQL服务器管理接口 PostgreSQL 用途通用 OLTP&#xff1a;联合中心&#xff1a;地理空间&#xff1a;LAPP 堆栈&#xff1a; 使用 PostgreSQL 有什么好处&#xff1f;开源许可证&#xff1a;易于扩展&#xff1a;可…
阅读更多...
这款免费开源的数据库工具，支持所有主流数据库！

这款免费开源的数据库工具，支持所有主流数据库！

Java技术栈 www.javastack.cn 关注阅读更多优质文章 DBeaver 是一个基于 Java 开发&#xff0c;免费开源的通用数据库管理和开发工具&#xff0c;使用非常友好的 ASL 协议。可以通过官方网站或者 Github 进行下载。 由于 DBeaver 基于 Java 开发&#xff0c;可以运行在各种操作…
阅读更多...
开源数据库管理系统DBeaver

开源数据库管理系统DBeaver

简介 DBeaver dbeaver是免费和开源&#xff08;GPL&#xff09;为开发人员和数据库管理员通用数据库工具。 易用性是该项目的主要目标&#xff0c;是经过精心设计和开发的数据库管理工具。免费、跨平台、基于开源框架和允许各种扩展写作&#xff08;插件&#xff09;。 它支持任…
阅读更多...
开源数据库的国际化思考与实践

开源数据库的国际化思考与实践

整理 | 小雨青年 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 在全球开源技术掌门人高峰论坛上&#xff0c;PingCAP 联合创始人兼CTO 黄东旭分享了《开源数据库的国际化思考与实践》。 开源已死&#xff1f;不&#xff0c;还差得远 可能因为TiDB 是开源的&a…
阅读更多...
一个比 ClickHouse 还快的开源数据库

一个比 ClickHouse 还快的开源数据库

开源分析数据库 ClickHouse 以快著称&#xff0c;真的如此吗&#xff1f;我们通过对比测试来验证一下。 ClickHouse vs Oracle 先用 ClickHouse&#xff08;简称 CH&#xff09;、Oracle 数据库&#xff08;简称 ORA&#xff09;一起在相同的软硬件环境下做对比测试。测试基准使…
阅读更多...
Java开源数据库引擎，数据库计算封闭性的一站式解决方案

Java开源数据库引擎，数据库计算封闭性的一站式解决方案

目录 前言引入一、数据库封闭性带来的问题&#xff1f;问题1: ETL变成ELT甚至LETETL&#xff1a;ELT&#xff1a; 问题2: 中间表带来的资源消耗和耦合问题3: 多样性数据源问题4: 存储过程带来的安全和耦合问题问题5: 大数据性能导致的尴尬 二、开放的SPL解决方式多样源直接计算…
阅读更多...
比较适合物联网的开源数据库

比较适合物联网的开源数据库

物联网产生大量的数据&#xff0c;包括流数据、时间序列数据、RFID数据、传感数据等。要有效地管理这些数据&#xff0c;就需要使用数据库。物联网数据的本质需要一种不同类型的数据库。以下是一些数据库&#xff0c;当与物联网一起使用时&#xff0c;会给出非常好的结果。 物联…
阅读更多...
阿里巴巴开源的免费数据库工具Chat2DB

阿里巴巴开源的免费数据库工具Chat2DB

Chat2DB 是一款由阿里巴巴开源的免费数据库工具&#xff0c;它为开发人员提供了一个强大且易于使用的平台&#xff0c;用于存储和查询数据。与传统的数据库工具相比&#xff0c;Chat2DB 具有以下特点和优势&#xff1a; 多数据库支持&#xff1a;Chat2DB 可以与多种类型的数据库…
阅读更多...
21款最优秀的开源数据库

21款最优秀的开源数据库

摘要&#xff1a;几乎所有软件项目的开发都需要数据库的支持&#xff0c;目前&#xff0c;随着开源技术的迅速发展&#xff0c;越来越多的数据供应商选择开源数据库&#xff0c;为开源事业添砖加瓦。 作为一名软件开发人员或DBA&#xff0c;其中一份必不可少的工作就是与数据库…
阅读更多...
一文带你了解开源数据库中的佼佼者 TOP 10

一文带你了解开源数据库中的佼佼者 TOP 10

当今&#xff0c;大多数应用程序都需要在某个地方存储数据。对于 Web 应用程序&#xff0c;数据库是关键的“齿轮”。 很多企业和开发者在选择数据库时&#xff0c;会主要考虑的几个因素——一是它的成本&#xff0c;二是托管服务提供商的灵活性和支持力度。 出于多种原因&…
阅读更多...
推荐文章

Copyright @ 2022~2023