MySQL

1、数据库的三范式是什么

  • 第一范式：列不可再分
  • 第二范式：行可以唯一区分，主键约束
  • 第三范式：表的非主属性不能依赖于其他表的非主属性 外键约束

三范式一级一级依赖的

2、MySQL数据库引擎有哪些

mysql> show engines;

InnoDB、CSV、MEMORY、ARCHIVE、federated、performance_schema....

mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间

相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎

Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持

外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些

Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在

mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于

那些内容变化不频繁的代码表

MERGE：是一组MYISAM表的组合

3、说说InnoDB与MyISAM的区别

  • InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提

交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务

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

  • InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。**

但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该

过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，

索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

  • InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用

    一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

  • Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高

4、数据库的事务

数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性(Durabiliy)。简称ACID。

  • 原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，

    整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始

    状态。

  • 一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。

    如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的

  • 隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干

    扰

  • 持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

5、索引是什么

是帮助MySQL高效获取数据的数据结构 即加快数据库的查询速度

索引往往是存储在磁盘上的文件中的

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引

6、SQL优化手段有哪些

  1. 查询语句中不要使用select *****

  2. 尽量减少子查询，使用关联查询（left join,right join,inner join）替代

  3. 减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

  4. or 的查询尽量用 union或者union all 代替(不要去重的话用union all会更好）

  5. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

  6. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表

    扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有

    null值，然后这样查询： select id from t where num=0

7、简单说一说drop、delete与truncate的区别

delete和truncate只删除表的数据不删除表的结构 速度,一般来说: drop> truncate >delete delete

delete语句是dml，操作会放到rollback segement中,事务提交之后才生效; 如果有相应的trigger,执行的时候将被触发

truncate,drop是ddl，操作立即生效,原数据不放到rollback segment中,不能回滚.操作不触发trigger

8、什么是视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

9、 什么是内联接、左外联接、右外联接？

  • 内联接（Inner Join）：匹配2张表中相关联的记录。

  • 左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示。

  • 右外联接（Right Outer Join）：除了匹配2张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示。在判定左表和右表时，要根据表名出现在Outer Join的左右位置关系。

10、并发事务带来哪些问题?

  • 脏读（Dirty read）: (一个人在修改事务，另一个在访问事务，因为事务还没提交，另一个人访问的就是脏数据)
  • 丢失修改（Lost to modify）：（两个事务都在访问一个数据，并对数据修改，第一个事务的修改就丢失了）
  • 不可重复读（Unrepeatableread）：（一个事务多次访问一个数据，在这期间另一个事务访问该数据并且修改了，导致第一个事务两次读取的数据不一致）
  • 幻读（Phantom read）：（与不可重复读类似，只是不可重复度重点在修改，幻读重点在增删）

11，事务隔离级别有哪些?MySQL的默认隔离级别是?

有四个：

  1. READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
  3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
  4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

【但是MySQL的 InnoDB 使用的是Next-Key Lock 锁算法，达到了可串行化标准】

12，大表如何优化？

  1. 限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句

  2. 读/写分离：经典的数据库拆分方案，主库负责写，从库负责读

  3. 垂直分区：简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

    1. 优点：查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
    2. 缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

  4. 水平分区：指数据表行的拆分。 水平拆分最好分库 。

    1. 优点：能够支持非常大的数据量存储，应用端改造也少
    2. 缺点：分片事务难以解决分片事务难以解决为拆分会带来逻辑、部署、运维的各种复杂度

13、分库分表之后,id 主键如何处理？

需要一个全局唯一的 id来支持。

  1. UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标识比如文件的名字
  2. 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
  3. 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
  4. Twitter的snowflake算法
  5. 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。

14、 说说在 MySQL 中一条查询 SQL 是如何执行的？

  1. 取得链接，使用到 MySQL 中的连接器。
  2. 查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。 MySQL 8.0 版本后不存在此功能。
  3. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。
  4. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。
  5. 执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回。

15、索引有什么优缺点？

  • 优点：
    • 提高检索速度、降低排序成本
  • 缺点：
    • 占用存储空间，降低更新表的速度

16、 MySQL 中 varchar 与 char 的区别？varchar(30) 中的 30代表的涵义？

char：不可变长度类型，varchar则是可变长度类型

varchar(30) 中 30 的涵义最多存放 30 个字符

varchar(30) 和 (130) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存

对效率要求高用 char，对空间使用要求高用 varchar

17、 int(11) 中的 11 代表什么涵义？

int(11) 中的 11，不影响字段存储的范围，只影响展示效果。 也就是说最大显示宽度是11个字符

（并不代表 int 数据类型所占用的存储空间，而是表示该字段的显示宽度）

18、 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比MyISAM 慢？

因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM内部维持了一个计数器，预存了结果，所以直接返回即可

19、说说 InnoDB 与 MyISAM 有什么区别？

MyISAM 不支持行级锁，换句话说，MyISAM 会对整张表加锁，而不是针对行。同时，MyISAM 不支持事务和外键。MyISAM 可被压缩，存储空间较小，而且 MyISAM 在筛选大量数据时非常快。

InnoDB 是事务型引擎，当事务异常提交时，会被回滚。同时，InnoDB 支持行锁。此外，InnoDB 需要更多存储空间，会在内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 支持自动奔溃恢复特性。

20、MySQL 索引类型有哪些？

主键索引：索引列中的值必须是唯一的，不允许有空值

普通索引：无限制，允许重复和空

唯一索引：索引列中的值必须是唯一的，但是允许为空值

全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。

空间索引：L在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引：在文本类型CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定

其他（按照索引列数量分类）：

  1. 单列索引
  2. 组合索引：要遵循最左前缀匹配原则（最左匹配原则）

21、什么时候不要使用索引？

  1. 经常增删改的列不要建立索引；
  2. 有大量重复的列不建立索引；
  3. 表记录太少不要建立索引。

22、说说什么是 MVCC？

多版本并发控制：是一种用来解决读 - 写冲突的无锁并发控制。是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读

23、MVCC 可以为数据库解决什么问题？

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数

据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新

丢失问题。

24、说说 MVCC 的实现原理

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主

要是依赖记录中的 3 个隐式字段、undo 日志、Read View 来实现的。

25、MySQL 事务隔离级别？

未提交读、可提交读、可重复读、可串行化。

26、 请说说 MySQL 数据库的锁？

有共享锁和排它锁，也就是读锁和写锁。

  1. 共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
  2. 排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用

户读取正在写入的资源。

  1. 表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。
  2. 行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现，

否则会自动锁全表，那么就不是行锁了）。

27、说说什么是锁升级？

  • MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondaryindex 查找的时候，也是通过找到对应的 primary，再找对应的数据行。

28、说说悲观锁和乐观锁

悲观锁：

说的是数据库被外界（包括本系统当前的其他事务以及来自外部系统的事务处理）修改保持着保守

态度，因此在整个数据修改过程中，将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机

制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实

现了加锁机制，也是没有办法保证系统不会修改数据。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务

无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据

乐观锁：

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机

制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事

务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

29、怎样尽量避免死锁的出现？

  1. 设置获取锁的超时时间，至少能保证最差情况下，可以退出程序，不至于一直等待导致死锁；
  2. 设置按照同一顺序访问资源，类似于串行执行；
  3. 避免事务中的用户交叉；
  4. 保持事务简短并在一个批处理中；
  5. 使用低隔离级别；
  6. 使用绑定链接。

30、使用 MySQL 的索引应该注意些什么？

  1. 避免在where子句中对字段进行表达式操作
  2. 避免在where中使用 or
  3. 避免在where中使用 !=和<>操作
  4. 避免在where子句中对字段进行函数操作
  5. 不要在where子句中的=左边进行函数、运算等
  6. 复合索引遵循最左前缀原则
  7. MySQL评估使用索引比全盘扫描更慢，会放弃，可以通过语句强制索引
  8. 列是字符串的话查询是要给值加引号
  9. LIKE查询，%不能在前面
  10. 表字段为NULL也不可以使用索引

31、CHAR 和 VARCHAR 的区别？

char不可变，varchar可变 ：char追求效率，varchar追求空间

32、主键和候选键有什么区别？

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被

指定为主键，并且可以用于任何外 键引用。

(主键唯一，但候选键不唯一；候选键列可以为null）

33、主键与索引有什么区别？

主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；

主键不允许为空值，唯一索引列允许空值；

一个表只能有一个主键，但是可以有多个唯一索引；

主键可以被其他表引用为外键，唯一索引列不可以；

主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质区别

34、 MySQL 如何做到高可用方案？

  1. MySQL 高可用：分库分表，通过 MyCat 连接多个 MySQL
  2. MyCat 也得高可用：Haproxy，连接多个 MyCat
  3. Haproxy 也得高可用：通过 keepalived 辅助 Haproxy

MySQL ----->MyCat-------->Haproxy----->keepalived

posted on 2023-09-07 23:30  紫宁不嗣音  阅读(19)  评论(0)  编辑  收藏  举报

导航