跳至主要內容

Mysql手册(基础)

bugcode大约 36 分钟面试MYSQL面试mysql

Mysql数据库

mysql架构

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1640582860809
1640582860809

连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等

引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

MySQL 的查询流程具体是？or 一条SQL语句在MySQL中如何执行的？

客户端请求 ---> 连接器（验证用户身份，给予权限） ---> 查询缓存（存在缓存则直接返回，不存在则执行后续操作） ---> 分析器（对SQL进行词法分析和语法分析操作） ---> 优化器（主要对执行的sql优化选择最优的执行方案方法） ---> 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口） ---> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

1640583028230
1640583028230

Mysql常用的存储引擎有哪些，他们都有什么特点？

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

使用哪一种引擎可以灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能 。

MySQL服务器使用可插拔的存储引擎体系结构，可以从运行中的 MySQL 服务器加载或卸载存储引擎 。

目前通常使用的有两种存储引擎：

InnoDB

  • InnoDB是MySQL的默认存储引擎，支持事务、行锁和外键等操作。

MyISAM

  • MyISAM是MySQL5.1版本前的默认存储引擎，MyISAM的并发性比较差，不支持事务和外键等操作，默认的锁的粒度为表级锁。

查看存储引擎

-- 查看支持的存储引擎
SHOW ENGINES

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'

--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename

--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

设置存储引擎

我们在进阿里一张表的时候，可以设置表使用不同的存储引擎。

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

默认情况下，每当 CREATE TABLE 或 ALTER TABLE 不能使用默认存储引擎时，都会生成一个警告。为了防止在所需的引擎不可用时出现令人困惑的意外行为，可以启用 NO_ENGINE_SUBSTITUTION SQL 模式。如果所需的引擎不可用，则此设置将产生错误而不是警告，并且不会创建或更改表

常见的存储引擎

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。

InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键

文件存储结构对比

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为 数据表名.frm，如user.frm。

查看MySQL 数据保存在哪里：show variables like 'data%'

MyISAM 物理文件结构为：

  • .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
  • .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
  • .MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

  • .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
  • .ibd 文件或 .ibdata 文件： 这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
  • 独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件， 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

面试中如何回答这些问题？

事务方面

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

是否支持外键

InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；

聚簇索引和非聚簇索引

InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

是否保存表的行数

InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

锁粒度

InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，而 InnoDB 支持。

使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

🌈 拓展一下：

  • MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
  • MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是 REPEATABLE-READ ）。
  • 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

两种引擎对比：

1631753040455
1631753040455
1640656100727
1640656100727

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；

如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

哪个存储引擎执行 select count(*) 更快，为什么?

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

  • 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
  • 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

锁机制与 InnoDB 锁算法

MyISAM 和 InnoDB 存储引擎使用的锁：

  • MyISAM 采用表级锁(table-level locking)。
  • InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

  • 表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
  • 行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 存储引擎的锁的算法有三种：

  • Record lock：记录锁，单个行记录上的锁
  • Gap lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身

数据类型

主要包括以下五大类：

  • 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
  • 浮点数类型：FLOAT、DOUBLE、DECIMAL
  • 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
  • 日期类型：Date、DateTime、TimeStamp、Time、Year
  • 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等
1640656330188
1640656330188
1640656346954
1640656346954

CHAR 和 VARCHAR 的区别？

char是固定长度，varchar长度可变：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间

相同点：

  1. char(n)，varchar(n)中的n都代表字符的个数
  2. 超过char，varchar最大长度n的限制后，字符串会被截断。

不同点：

  1. char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。
  2. 能存储的最大空间限制不一样：char的存储上限为255字节。
  3. char在存储时会截断尾部的空格，而varchar不会。

char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。

列的字符串类型可以是什么？

字符串类型是：SET、BLOB、ENUM、CHAR、TEXT、VARCHAR

BLOB和TEXT有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB

TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。

BLOB 保存二进制数据，TEXT 保存字符数据。

数据库的三大范式

数据冗余直观上可以说就是一张表里不同位置有大量重复的数据，这种冗余不仅仅增加了存储量，也使得我们会更容易遇到三种异常（插入异常，更新异常和删除异常）。

第一范式

  • 第一范式：确保每列保持原子性，数据表中的所有字段值都是不可分解的原子值。在任何一个关系型数据库中，满足第一范式是最基本的要求，
    • 1NF是对属性的原子性，要求属性具有原子性，不可再分解；
    • 如学生（学号，姓名，性别，出生日期），可以看到每一个列属性都是不可在分割的单位，这就是原子性的含义。
    • 解决方法：对可以再分的列字段，进行拆分，划分为多个表。
1640651441705
1640651441705

但是，就算是这样符合1NF的表仍然会有很多，因为1NF其实是最为基本的要求，不满足1NF的数据库甚至不一定能建立成功。

比如，上面提到的三个异常:

  1. 插入异常。如果学校建了新系，但还没有招生，这个系就不能被插入数据表里
  2. 更新异常。如果Akon转系了，那在上表中需要更改两行院系&课程记录
  3. 删除异常。如果所有学生记录被删除，院系记录和课程也就不复存在了

1NF解决的是数据冗余问题。

第二范式

我们先来看看什么是依赖：

对于一个表来说，如果通过其中一个属性可以找到唯一对应的一条记录，那么我们可以说它为本表的主键(Primary Key)。 比如下表中，每个学生的学号是存在且唯一的，但是名字可能会有重名存在，学号为主键（Primary Key），而姓名就不是。 同时，我可以通过【学号】查到任何一行的任何一列属性，比如通过【学号】查【院系】，通过【学号】查【课程】，通过【学号】查【姓名】。这时我们可以说，其他的这三个属性依赖于学号。

那什么是部分依赖呢？
候选键（Candidate Key）就是，当两个属性结合在一起可以唯一确定任何一条记录的情况。比如在一张学生成绩表中。

1640655170465
1640655170465

【学号+课程号】 一起可以确定任何一条分数或是学号或是教师，所以 【学号+课程号】 就是本表的候选键。
一张表可以有多个键（key），一般我们会选择其中一个作为主键。
这个时候我们可以看到，教师这一属性其实只由课程号决定，而课程号只是 候选键 的一部分，因此这时我们就说，教师属性存在部分函数依赖。

  • 第二范式：确保表中的每列都和主键相关
    • 2NF是对记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖，必须是完全依赖，也就是所有的非主属性必须完全依赖于主属性；
    • 如表：（学号，课程号，姓名，学分），这个表明显说明了两个事务:学生信息, 课程信息;由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖与学号，所以不符合二范式。所以我们要对表进行拆分，让其符合第二范式，学生：Student(学号, 姓名)；课程：Course(课程号, 学分)；选课关系：StudentCourse(学号, 课程号, 成绩)。
    • 解决方法：一般是把表拆分为两个表，一张表只管理一件事。
    • 如果不符合第二范式，可能会存在的问题：
      • 数据冗余:，每条记录都含有相同信息；
      • 删除异常：删除所有学生成绩，就把课程信息全删除了；
      • 插入异常：学生未选课，无法记录进数据库；
      • 更新异常：调整课程学分，所有行都调整。

拆分表

  1. 先找出所有的非主属性（不是主键也不是候选键包含部分的属性），在这个例子里，键为【学号+课程号】，那么他俩为主属性，剩下的都是非主属性
  2. 检查这些非主属性是否存在部分函数依赖。【姓名】只依赖于【学号】，存在；【分数】非得要【学号+课程号】一起才能确定，不存在；【教师】只依赖于【课程】号，存在
    1. 将这些存在部分函数依赖的属性分出去建立满足2NF的新表，切分方法并不唯一。

分数表去掉 【姓名】 和 【教师】 属性：

1640655497010
1640655497010

为 姓名 建立学生表：

1640655526626
1640655526626

为 教师 建立 课程表：

1640655547943
1640655547943

第二范式存在的异常：

  • 插入异常。招新生的话，学生信息可以单独插入，有改进。
  • 更新异常。如果L1号课换老师了，只用修改一次，有改进。
  • 删除异常。如果删除所有的学生信息，教师信息还在，分数信息也还在；但是如果我从教师表里删掉课程L1的记录，教师Mr.x以及CS院系信息就不复存在了，这是个大问题。

满足2NF的前提是必须满足1NF。

2NF解决的是部分函数依赖问题。

数据冗余问题变少了。

第三范式

比如上面一节提到，如果删除教师表里课程L1的信息，教师Mr.x以及CS院系信息就不复存在了，同时，如果一名新来的教师还没有被分配到任何课，他就不能被加入到教师表里。

这是因为，在教师表里：

  1. 课程号可以决定教师. A → B
  2. 教师不能决定课程号，因为一个教师可以教多门课. B not→ A
  3. 教师决定院系，因为一个教师只能属于一个院系. B → C

这时我们就发现，非主属性 【院系】，也依赖于另一个非主属性 【教师】，这种情况就叫做传递依赖。
而3NF的条件，就是要去除这种传递依赖。

  • 第三范式：确保每列都和主键列直接相关而不是间接相关
    • 3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖，不存在属性对主键的传递依赖。；
    • 表: 学号, 姓名, 年龄, 学院名称, 学院电话，因为存在依赖传递: (学号) → (学生)→(所在学院) → (学院电话) 。所以对表进行拆分，消除传递函数依赖，学生：(学号, 姓名, 年龄, 所在学院)；学院：(学院, 电话)。
    • 可能会存在问题：
      • 数据冗余:有重复值；
      • 更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况 。
      • 插入异常：学生未选课，无法记录进数据库；
      • 删除异常:删除所有学生成绩，就把课程信息全删除了；

拆分表

解决方法有多种，这里可以将院系信息分表。

课程表只有课程号和教师信息：

1640655793724
1640655793724

而教师表 只有教师和院系信息：

1640655818425
1640655818425

这样我们再检查上面的问题， 删除L1课程信息，Mr.X老师的信息仍然保存的很好，有改进。 新老师Mr.Z可以被插入教师表，哪怕他还没有被分配任何课程。

3NF解决的是传递依赖关系

满足3NF的前提是必须满足2NF。

3NF解决了插入异常。

Mysql的数据类型都有哪些？

整数

  • TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别占用8、16、24、32、64位存储空间。值得注意的是，INT(10)中的10只是表示显示字符的个数，并无实际意义。一般和UNSIGNED ZEROFILL配合使用才有实际意义，例如，数据类型INT(3)，属性为UNSIGNED ZEROFILL，如果插入的数据为3的话，实际存储的数据为003。

浮点数

  • FLOAT、DOUBLE及DECIMAL为浮点数类型，DECIMAL是利用字符串进行处理的，能存储精确的小数。相比于FLOAT和DOUBLE，DECIMAL的效率更低些。FLOAT、DOUBLE及DECIMAL都可以指定列宽，例如FLOAT(5,2)表示一共5位，两位存储小数部分，三位存储整数部分。

字符串

  • 字符串常用的主要有CHAR和VARCHAR，VARCHAR主要用于存储可变长字符串，相比于定长的CHAR更节省空间。CHAR是定长的，根据定义的字符串长度分配空间。
    • 应用场景：对于经常变更的数据使用CHAR更好，CHAR不容易产生碎片。对于非常短的列也是使用CHAR更好些，CHAR相比于VARCHAR在效率上更高些。一般避免使用TEXT/BLOB等类型，因为查询时会使用临时表，造成严重的性能开销。

日期

  • 比较常用的有year、time、date、datetime、timestamp等，datetime保存从1000年到9999年的时间，精度位秒，使用8字节的存储空间，与时区无关。timestamp和UNIX的时间戳相同，保存从1970年1月1日午夜到2038年的时间，精度到秒，使用四个字节的存储空间，并且与时区相关。
    • 应用场景：尽量使用timestamp，相比于datetime它有着更高的空间效率。

索引的使用场景有哪些？

  • 对于中大型表建立索引非常有效，对于非常小的表，一般全部表扫描速度更快些。
  • 对于超大型的表，建立和维护索引的代价也会变高，这时可以考虑分区技术。
  • 如果表的增删改非常多，而查询需求非常少的话，那就没有必要建立索引了，因为维护索引也是需要代价的。
  • 一般不会出现再where条件中的字段就没有必要建立索引了。
  • 多个字段经常被查询的话可以考虑联合索引。
  • 字段多且字段值没有重复的时候考虑唯一索引。
  • 字段多且有重复的时候考虑普通索引

索引的设计原则有哪些？

  1. 最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面的选择列表中的列。
  2. 索引列的基数越大，索引的效果越好，换句话说就是索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是三种，大多不是男性就是女性。
  3. 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，并且索引高速缓存中的块可以容纳更多的键值，会使得查询速度更快。尽量利用最左前缀。
  4. 不要过度索引，每个索引都需要额外的物理空间，维护也需要花费时间，所以索引不是越多越好。

如何对索引进行优化

  1. 对索引的优化其实最关键的就是要符合索引的设计原则和应用场景，将不符合要求的索引优化成符合索引设计原则和应用场景的索引。
  2. 除了索引的设计原则和应用场景那几点外，还可以从以下两方面考虑。
  3. 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，因为这样无法使用索引。例如 select * from table_name where a + 1 = 2。
  4. 将区分度最高的索引放在前面
  5. 尽量少使用select*。

索引的使用场景、索引的设计原则和如何对索引进行优化可以看成一个问题。

如何创建删除索引？

创建索引：

  1. 使用CREATE INDEX 语句
CREATE INDEX index_name ON table_name (column_list);
  1. 在CREATE TABLE时创建
CREATE TABLE user(
  id INT PRIMARY KEY,
  information text,
  FULLTEXT KEY (information)
);
  1. 使用ALTER TABLE创建索引
ALTER TABLE table_name ADD INDEX index_name (column_list);

删除索引

  1. 删除主键索引
alter table 表名 drop primary key
  1. 删除其他索引
alter table 表名 drop key 索引名

使用索引查询时性能一定会提升吗？

不一定，前面在索引的使用场景和索引的设计原则中已经提到了如何合理地使用索引，因为创建和维护索引需要花费空间和时间上的代价，如果不合理地使用索引反而会使查询性能下降 。

什么是前缀索引

前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

  • 使用场景：前缀的区分度比较高的情况下。
  • 建立前缀索引的方式
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

这里面有个prefix_length参数很难确定，这个参数就是前缀长度的意思。通常可以使用以下方法进行确定，先计算全列的区分度。

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

然后在计算前缀长度为多少时和全列的区分度最相似。

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name

不断地调整prefix_length的值，直到和全列计算出区分度相近。

什么是最左匹配原则

最左匹配原则：从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。

例如建立索引(a,b,c)，大家可以猜测以下几种情况是否用到了索引

第一种

select * from table_name where a = 1 and b = 2 and c = 3
select * from table_name where b = 2 and a = 1 and c = 3 -- 调动索引字段顺序不会影响查询效率

上面两次查询过程中所有值都用到了索引，where后面字段调换不会影响查询结果，因为MySQL中的优化器会自动优化查询顺序。

第二种

select * from table_name where a = 1
select * from table_name where a = 1 and b = 2
select * from table_name where a = 1 and b = 2 and c = 3

答案是三个查询语句都用到了索引，因为三个语句都是从最左开始匹配的。

第三种

select * from table_name where b = 1
select * from table_name where b = 1 and c = 2

答案是这两个查询语句都没有用到索引，因为不是从最左边开始匹配的 ,建立的索引a没有生效，所以索引b,c都失效。

第四种

select * from table_name where a = 1 and c = 2

这个查询语句只有a列用到了索引，c列没有用到索引，因为中间跳过了b列，不是从最左开始连续匹配的,这里索引c失效。

第五种

select * from table_name where a = 1 and b < 3 and c < 1

这个查询中只有a列和b列使用到了索引，而c列没有使用索引，因为根据最左匹配查询原则，遇到范围查询会停止。

第六种

select * from table_name where a like 'ab%';
select * from table_name where a like '%ab'
select * from table_name where a like '%ab%'

对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描 .

对于字符串两端都有%的情况，可以使用覆盖索引。

索引在什么情况下会失效？

在上面介绍了几种不符合最左匹配原则的情况会导致索引失效，除此之外，以下这几种情况也会导致索引失效。

  1. 条件中有or，例如 select * from table_name where a = 1 or b = 3
  2. 在索引上进行计算会导致索引失效，例如 select * from table_name where a + 1 = 2
  3. 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设select * from table_name where a = '1' 会使用到索引，如果写成 select * from table_name where a = 1 则会导致索引失效。
  4. 在索引中使用函数会导致索引失效，例如 select * from table_name where abs(a) = 1
  5. 在使用like查询时以%开头会导致索引失效
  6. 索引上使用！、=、<>进行判断时会导致索引失效，例如 select * from table_name where a!= 1
  7. 索引字段上使用 is null/is not null判断时会导致索引失效，例如 select * from table_namewhere a is null

Mysql查询

count(*) 和 count(1)和count(列名)区别 ps：这道题说法有点多

执行效果上：

  • count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
  • count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
  • count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

  • 列名为主键，count(列名)会比count(1)快
  • 列名不为主键，count(1)会比count(列名)快
  • 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
  • 如果有主键，则 select count（主键）的执行效率是最优的
  • 如果表只有一个字段，则 select count(*) 最优。

MySQL中 in和 exists 的区别？

  • exists：exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false
  • in：in查询相当于多个or条件的叠加
SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

UNION和UNION ALL的区别?

UNION和UNION ALL都是将两个结果集合并为一个，两个要联合的SQL语句 字段个数必须一样，而且字段类型要“相容”（一致）；

  • UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
  • UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；

SQL执行顺序

  • 手写

    SELECT DISTINCT <select_list>
FROM  <left_table> <join_type>
JOIN  <right_table> ON <join_condition>
WHERE  <where_condition>
GROUP BY  <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
复制代码

  • 机读

    FROM  <left_table>
ON <join_condition>
<join_type> JOIN  <right_table> 
WHERE  <where_condition>
GROUP BY  <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
复制代码

  • 总结

1640658451255
1640658451255

mysql 的内连接、左连接、右连接有什么区别？

什么是内连接、外连接、交叉连接、笛卡尔积呢？

join图

1640658483243
1640658483243

Sql基础

SQL语句主要分为哪几类

  1. 数据据定义语言DDL（Data Definition Language）：主要有CREATE，DROP，ALTER等对逻辑结构有操作的，包括表结构、视图和索引。
  2. 数据库查询语言DQL（Data Query Language）：主要以SELECT为主
  3. 数据操纵语言DML（Data Manipulation Language）：主要包括INSERT，UPDATE，DELETE
  4. 数据控制功能DCL（Data Control Language）：主要是权限控制能操作，包括GRANT，REVOKE，COMMIT，ROLLBACK等。

SQL约束有哪些

  • 主键约束：主键为在表中存在一列或者多列的组合，能唯一标识表中的每一行。一个表只有一个主键，并且主键约束的列不能为空。
  • 外键约束：外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。只有主表的主键可以被从表用作外键，被约束的从表的列可以不是主键，所以创建外键约束需要先定义主表的主键，然后定义从表的外键。
  • 唯一约束：确保表中的一列数据没有相同的值，一个表可以定义多个唯一约束。
  • 默认约束：在插入新数据时，如果该行没有指定数据，系统将默认值赋给该行，如果没有设置没默认值，则为NULL。
  • Check约束：Check会通过逻辑表达式来判断数据的有效性，用来限制输入一列或者多列的值的范围。在列更新数据时，输入的内容必须满足Check约束的条件

什么是子查询

子查询：把一个查询的结果在另一个查询中使用。

子查询可以分为以下几类：

  • 标量子查询：指子查询返回的是一个值，可以使用 =,>,<,>=,<=,<>等操作符对子查询标量结果进行比较，一般子查询会放在比较式的右侧。
SELECT * FROM user WHERE age = (SELECT max(age) from user) //查询年纪最大的人
  • 列子查询：指子查询的结果是n行一列，一般应用于对表的某个字段进行查询返回。可以使用IN、ANY、SOME和ALL等操作符，不能直接使用
SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)
  • 行子查询：指子查询返回的结果一行n列
SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")
  • 表子查询：指子查询是n行n列的一个数据表
SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) --在学生表中找到班级在1班的学生

了解MySQL的几种连接查询吗？

MySQl的连接查询主要可以分为外连接，内连接，交叉连接

外连接

  • 外连接主要分为左外连接(LEFT JOIN)、右外连接(RIGHT JOIN)、全外连接。
  • 左外连接：显示左表中所有的数据及右表中符合条件的数据，右表中不符合条件的数据为null。
1631770721000
1631770721000
  • 右外连接：显示左表中所有的数据及右表中符合条件的数据，右表中不符合条件的数据为null。
1631770758813
1631770758813

MySQL中不支持全外连接。

  • 内连接：只显示符合条件的数据
1631770802566
1631770802566

  • 交叉连接：使用笛卡尔积的一种连接。

    笛卡尔积，百度百科的解释：两个集合X和Y的笛卡尔积表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员 。例如：A={a,b}，B={0,1,2}，A × B = {(a,0)，(a,1)，(a,2)，(b,0)，(b,1)，(b,2)}

**举例如下：有两张表分为L表和R表。 **

1631770886115
1631770886115

左外连接 ： select L.*,R.* from L left join R on L.b=R.b

1631770941267
1631770941267

右外连接： select L.*,R.* from L right join R on L.b=R.b

1631770987482
1631770987482

内连接： select L.*,R.* from L inner join R on L.b=R.b

1631771057312
1631771057312

交叉连接： select L.*,R.* from L,R

1631771090537
1631771090537

mysql中in和exists的区别？

in和exists一般用于子查询。

  • 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
  • in在内表查询或者外表查询过程中都会用到索引。
  • exists仅在内表查询时会用到索引
  • 一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询寻得结果集较小，外表较大时，使用in效率更高。
  • 对于not in和not exists，not exists效率比not in的效率高，与子查询的结果集无关，因为not in对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

varchar和char的区别？

  • varchar表示变长，char表示长度固定。当所插入的字符超过他们的长度时，在严格模式下，会拒绝插入并提示错误信息，在一般模式下，会截取后插入。如char(5)，无论插入的字符长度是多少，长度都是5，插入字符长度小于5，则用空格补充。对于varchar(5)，如果插入的字符长度小于5，则存储的字符长度就是插入字符的长度，不会填充。
  • 存储容量不同，对于char来说，最多能存放的字符个数为255。对于varchar，最多能存放的字符个数是65532。
  • 存储速度不同，char长度固定，存储速度会比varchar快一些，但在空间上会占用额外的空间，属于一种空间换时间的策略。而varchar空间利用率会高些，但存储速度慢，属于一种时间换空间的策略。

MySQL中int(10)和char(10)和varchar(10)的区别？

int(10)中的10表示的是显示数据的长度，而char(10)和varchar(10)表示的是存储数据的大小。

drop、delete和truncate的区别？

1631771405632
1631771405632

一般来讲，删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate

UNION和UNION ALL的区别？

union和union all的作用都是将两个结果集合并到一起。

  • union会对结果去重并排序，union all直接直接返回合并后的结果，不去重也不进行排序。
  • union all的性能比union性能好。
分布式
bugcode