Mysql相关整理

MySQL

1 说一下数据库三范式特点？

第一范式：数据表中的每一列（每个字段）都不可以再拆分。例如用户表，用户地址还可以拆分成国家、省份、市，这样才是符合第一范式的。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。例如订单表里，存储了商品信息（商品价格、商品类型），那就需要把商品 ID 和订单 ID 作为联合主键，才满足第二范式。

第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。例如订单表，就不能存储用户信息（姓名、地址）

2 varchar和char的区别？

char:

char 表示定长字符串，长度是固定的；

如果插入数据的长度小于 char 的固定长度时，则用空格填充；

因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

对于 char 来说，最多能存放的字符个数为 255，和编码无关

varchar:

varchar 表示可变长字符串，长度是可变的；

插入的数据是多长，就按照多长来存储；

varchar 在存取方面与 char 相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

对于varchar来说，最多能存放的字符个数为65532

日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。

3 drop，delete与truncate的区别？

drop DDL语句，删除表结构

delete DML 语句，删除表记录， 而且是一条一条删除。 可以提供where条件，删除符合条件的记录

truncate DDL语句， 删除表中的所有记录，速度比delete快。但不能设置条件删除部分记录

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

MySQL 的连接主要分为内连接和外连接，外连接常用的有左连接、右连接

inner join内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

left join在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录

5 UNION 与 UNION ALL 的区别？

如果使用UNION，会在表链接后筛选掉重复的记录行

如果使用UNION ALL，不会合并重复的记录行

从效率上说，UNION ALL要比UNION快很多，如果合并没有刻意要删除重复行，那么就使用UNION All

6 MySQL的HAVING子句和WHERE子句的区别？

HAVING子句和WHERE子句都用于过滤数据，但它们的应用场景和时机不同：

WHERE子句：用于过滤行数据，发生在数据分组之前。它不能与聚合函数一起使用。

HAVING子句：用于过滤分组后的数据集，通常与聚合函数一起使用。

7 count(1)、count(*)与count(列名)的区别?

执行效果：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者 0，而是表示 null）的计数，即某个字段值为 NULL 时，不统计。

执行速度：

列名为主键，count(列名)会比 count(1)快

列名不为主键，count(1)会比 count(列名)快

如果表多个列并且没有主键，则 count(1)的执行效率优于 count(*)

如果有主键，则 select count(主键)的执行效率是最优的

如果表只有一个字段，则 select count(*)最优。

8 解释MySQL中的主键与唯一键的区别。

主键（Primary Key）是表中用于唯一标识每条记录的列或列的组合。一个表只能有一个主键，且主键列的值必须是唯一的，不允许为NULL。

唯一键（Unique Key）也确保列的值唯一，但一个表可以有多个唯一键，并且唯一键的列可以包含NULL值

9 什么是索引？如何提高查询性能的？是不是越多越好？

索引是数据库对象，可以提高数据检索的速度。

索引类似于书的目录，使数据库能够快速定位并检索数据，而不必扫描整个表。索引尤其在处理大量数据时显著提高查询性能。

索引并不是越多越好，索引会占据磁盘空间，另外索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件

10 创建索引有哪些注意点？

索引虽然是sql性能优化的利器，但是索引的维护也是需要成本的，所以创建索引，也要注意：

1. 索引应该建在查询应用频繁的字段。在用于where判断、order排序和join的（on）字段上创建索引。
2. 索引的个数应该适量。索引需要占用空间；更新时候也需要维护。
3. 区分度低的字段，例如性别，不要建索引。离散度太低的字段，扫描的行数降低的有限。
4. 频繁更新的值，不要作为主键或者索引维护索引文件需要成本；还会导致页分裂，I0次数增多。
5. 组合索引把散列性高（区分度高）的值放在前面为了满足最左前缀匹配原则
6. 创建组合索引，而不是修改单列索引。组合索引代替多个单列索引（对于单列索引，MySQL 基本只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
7. 过长的字段，使用前缀索引。当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
8. 不建议用无序的值（例如身份证、UUID）作为索引。

11 索引哪些情况下会失效呢？

1. 查询条件包含or，可能导致索引失效
2. 如果字段类型是字符串，where 时一定用引号括起来，否则会因为隐式类型转换，索引失效
3. like通配符可能导致索引失效。
4. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
5. 在索引列上使用mysql的内置函数，索引失效。
6. 对索引列运算（如，+、-、*、/），索引失效。
7. 索引字段上使用（!= 或者<>，not in）时，可能会导致索引失效。
8. 索引字段上使用is null，is not nul，可能导致索引失效。
9. 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

12 如何判断索引是否失效？

可以查看执行计划

直接在select 语句之前增加explain关键字，根据会返回执行计划的信息，判断索引是否失效

如：根据type ， possible_keys , keyi , ref , Extra 等信息

13 介绍一下MySQL索引的存储结构？

MySQL的默认存储引擎是InnoDB，它采用的是B+树结构的索引。

B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表

B+相比较 B树，更有优势：

1. 它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。
2. 扫库、扫表能力更强

如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据。

1. B+ Tree的磁盘读写能力相对于B Tree来说更强，IO次数更少

根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多，IO次数更少。

1. 排序能力更强

因为叶子节点上有下一个数据区的指针，数据形成了链表。

1. 效率更加稳定

B+Tree 永远是在叶子节点拿到数据，所以IO次数是稳定的

B+树的存储能力

1. 假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节，这样一共 14 字节。非叶子节点（一页）可以存储16384/14=1170个这样的 单元（键值+指针），代表有1170个指针。
2. 树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据为 1170117016=2190 2400。
3. 在查找数据时一次页的查找代表一次10，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

14 聚簇索引与非聚簇索引的区别？

聚簇索引不是一种新的索引，而是一种数据存储方式。

聚簇表示数据行和相邻的键值紧凑地存储在一起。我们熟悉的两种存储引擎–MyISAM 采用的是非聚簇索引，InnoDB采用的是聚簇索引。

索引的数据结构是树，聚簇索引的索引和数据存储在一棵树上，树的叶子节点就是数据，非聚簇索引索和数据不在一棵树上。

一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。

聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；

非聚簇索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

聚簇索引：物理存储按照索引排序；

非聚集索引：物理存储不按照索引排序；

15 什么是回表？

在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

16 说说InnoDB与MyISAM的区别？

存储结构：每个MyISAM在磁盘上存储成三个文件；InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB.

事务支持：MyISAM不提供事务支持；InnoDB提供事务支持事务，具有事务（commit）、回滚（rollback）和崩溃修复能力（crash recovery capabilities）的事务安全特性。

最小锁粒度：MyISAM只支持表级锁，更新时会锁住整张表，导致其它查询和更新都会被阻塞，InnoDB支持行级锁。

索引类型：MyISAM的索引为非聚簇索引，数据结构是B+树；InnoDB的索引是聚簇索引，数据结构是B+树。

主键必需：MyISAM 允许没有任何索引和主键的表存在；InnoDB 如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键（用户不可见），数据是主索引的一部分，附加索引保存的是主索引的值。

表的具体行数：MyISAM保存了表的总行数，如果select count(*) from table；会直接取出该值；InnoDB没有保存表的总行数，如果使用 select count(*) from table，就会遍历整个表；但是在加了wehre 条件后，MylSAM 和InnoDB 处理的方式都一样。

外键支持：MyISAM不支持外键；InnoDB支持外键。

17 什么是数据库事务？

数据库事务是一个作为单个逻辑工作单元执行的一系列操作。事务具有ACID属性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这意味着事务内的操作要么全部成功，要么全部失败，保持数据完整性，并且独立于其他事务运行。

原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。

一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。

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

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

18 事务的隔离级别有哪些？

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

19 什么是脏读，幻读，不可重复读？

事务 A、B 交替执行，事务 A 读取到事务 B 未提交的数据，这就是脏读。

在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入/删除了数据，并静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

20 binlog和redo log有什么区别？

bin log 会记录所有与数据库有关的日志记录，包括 InnoDB、MyISAM 等存储引擎的日志，而 redo log 只记InnoDB 存储引擎的日志。

记录的内容不同，bin log 记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而 redo log 记录的是关于每个页（Page）的更改的物理情况。

写入的时间不同，bin log 仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有 redo ertry 被写入 redo log 中。

写入的方式也不相同，redo log 是循环写入和擦除，bin log 是追加写入，不会覆盖已经写的文件。

21说说什么是 MVCC？

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

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题

22 简述MVCC实现原理？

MVCC的核心思想是保存数据的不同版本，使得在读操作时不需要锁定（lock）数据行，这样就可以提高系统的并发性能。在MVCC中，每个读操作都会看到一个一致性的数据视图，不会看到正在被另一个事务修改的数据。

以下是MVCC的一种实现方式：每行数据保存几个必要的版本信息，如创建版本号和删除版本号。

1. 插入操作：为插入的每行数据设置创建版本号，该版本号是事务ID。
2. 删除操作：为删除的每行数据设置删除版本号，该版本号是事务ID。
3. 读操作：只读取创建版本号早于或等于当前事务ID的数据行，并且删除版本号为空或大于当前事务ID的数据行。

举例来说，假设有一个事务ID为10的事务正在执行：

事务开始时，ID为10的事务开始运行。

插入操作：插入一行数据，并设置其创建版本号为10。

查询操作：只会看到创建版本号小于或等于10的行，即这行数据；同时，不会看到删除版本号为10的行，因为这表示这行数据在事务ID为10的事务中已被删除。

如果有另一个ID为11的事务进行插入或删除操作，那么ID为10的事务的读操作不会看到这些变更，因为创建或删除版本号不满足条件。

这样，MVCC通过数据版本管理，避免了读写操作之间的锁竞争，提高了数据库的并发性能。

23 MySQL多有哪些锁？

如果按锁粒度划分，有以下 3 种

表锁：开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低；不会出现死锁。

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

页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

如果按照兼容性，有两种：

共享锁（S Lock），也叫读锁（read lock），相互不阻塞。

排他锁（XLock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据。

23 介绍一下什么是数据库读写分离？

读写分离的基本原理是将数据库读写操作分散到不同的节点上，其基本实现：

1. 数据库服务器搭建主从集群，一主一从、一主多从都可以。
2. 数据库主机负责读写操作，从机只负责读操作。
3. 数据库主机通过复制将数据同步到从机，每台数据库服务器都存储了所有的业务数据。
4. 业务服务器将写操作发给数据库主机，将读操作发给数据库从机。
5. 说一下主从复制的原理？
6. master 数据写入，更新 binlog
7. master 创建一个 dump 线程（转存线程）向 slave 推送 binlog
8. slave连接到master的时候，会创建一个IO线程接收binlog，并记录到relay log中继日志中
9. slave 再开启一个 sql 线程读取 relay log 事件并在 slave 执行，完成同步slave 记录自己的 binglog
10. 什么是分库分表？

分库分表是一个数据库优化技术，它包括两个独立的概念：分库和分表。

分表：指的是将一个大的数据表拆分成多个较小的数据表。这样做可以减少单个表的字段数量（垂直分表）或者减少单个表中的数据行数量（水平分表）。垂直分表通常是将不常用的字段和常用字段分开存储，而水平分表则是将数据按照一定的规则分散到不同的表中，例如按时间或ID范围。

分库：是指将一个数据库拆分成多个独立的数据库。这样做可以将数据散落在多个数据库中，以分担单个数据库的压力，提高系统的并发处理能力和响应速度。

在实际应用中，分库和分表通常会同时进行，以优化整个数据库系统的性能。分库分表后的各个表可以位于同一个数据库中，也可以分布在多个数据库中。这种技术常用于处理数据量非常大的情况，以提高查询效率和系统稳定性

26 MySQL数据库cpu飙升的话，要怎么处理呢？

排查过程：

（1）使用 top 命令观察，确定是 mysqld 导致还是其他原因。

（2）如果是 mysqld 导致的，show processlist，查看 session 情况，确定是不是有消耗资源的 sql 在运行。

（3）找出消耗高的 sql，看看执行计划是否准确，索引是否缺失，数据量是否太大。

处理：

（1）kill 掉这些线程（同时观察 cpu 使用率是否下降），

（2）进行相应的调整（比如说加索引、改 sql、改内存参数）

（3）重新跑这些 SQL。

其他情况：

也有可能是每个sql消耗资源并不多，但是突然之间，有大量的session连进来导致cpu飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

27 大表如何优化？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

1. 读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

1. 垂直分区

例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

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

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

1. 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。

水平拆分可以支撑非常大的数据量。水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。

举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库