MySQL精简基础知识

三大范式

• 第一范式：每个列都不可以再拆分。

• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

2NF依据是非主键列是否是完全依赖于主键

3NF依据是非主键列是否直接依赖于主键

事务

ACID， A原子性， C一致性， I隔离性，D持久性

• 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
• 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。
• 隔离性。跟隔离级别相关，如read committed，一个事务只能读到已经提交的修改。
• 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务隔离级别

• 脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
• 不可重复读是指在对于数据库中的某行记录，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，另一个事务修改了数据并提交了。
• 幻读是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录，主键为id，在新增之前执行了select，没有发现id为xxx的记录，但插入时出现主键冲突，这就属于幻读，读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了，但当前事务不可见。

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

四种隔离级别

• Serializable (串行化)：通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。
• Repeatable read (可重复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可重复读的问题。
• Read committed (读已提交)：一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
• Read uncommitted (读未提交)：所有事务都可以看到其他未提交事务的执行结果。

生产环境大多使用RC。为什么不是RR呢？

可重复读(Repeatable Read)，简称为RR 读已提交(Read Commited)，简称为RC

缘由一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！ 缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!

也就是说，RC的并发性高于RR。

并且大部分场景下，不可重复读问题是可以接受的。毕竟数据都已经提交了，读出来本身就没有太大问题！

字符集

• 而char又是固定长度，为了能放下2个utf8mb4的字符，char会默认保留2*4（maxlen=4）= 8个字节的空间。如果是utf8mb3，则会默认保留 2 * 3 (maxlen=3) = 6个字节的空间。也就是说，在这种情况下，utf8mb4会比utf8mb3多使用一些空间。

MySQL有关权限的表都有哪几个？

MySQL服务器通过权限表来控制用户对数据库的访问，由mysql_install_db 脚本初始化。

• user 权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

• db权限表：记录各个帐号在各个数据库上的操作权限。

• table_priv 权限表：记录数据表级的操作权限。

• columns_priv 权限表：记录数据列级的操作权限。

• host 权限表：配合 db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT和REVOKE 语句的影响。

MySQL 的 Binlog 有有几种录入格式？分别有什么区别？

• statement 模式下，每一条会修改数据的 SQL都会记录在 Binlog中。不需要记录每一行的变化，减少了 Binlog日志量，节约了 IO，提高性能。这种方式在处理大规模数据操作时尤其有效。由于sql 的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

• row 级别下，不记录 SQL语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

• mixed，一种折中的方案，普通操作使用 statement记录，当无法使用statement 的时候使用row。

MySQL 存储引擎 MyISAM 与 InnoDB 区别

• 锁粒度方面：由于锁粒度不同，InnoDB比MyISAM支持更高的并发;InnoDB的锁粒度为行锁、MyISAM的锁粒度为表锁、行锁需要对每一行进行加锁，所以锁的开销更大，但是能解决脏读和不可重复读的问题，相对来说也更容易发生死锁

• 可恢复性：由于 InnoDB是有事务日志的，所以在产生数据库崩溃等条件后，可以根据日志文件进行恢复。而 MyISAM 则没有事务日志。

• 查询性能:MylSAM要优于InnoDB 因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行;而 MyISAM 可以直接定位到数据所在的内存地址，可以直接找到数据。

• 表结构文件:MyISAM的表结构文件包括:frm(表结构定义),.MYI(索引),.MYD(数据);而 InnoDB的表数据文件为:ibd和frm(表结构定义)。

MyISAM 索引与 InnoDB 索引的区别？

• InnoDB 索引是聚簇索引，MyISAM 索引是非聚簇索引。

• InnoDB 的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

• MyISAM 索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

• InnoDB 非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引有哪些优缺点？

索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；

• 空间方面：索引需要占物理空间。

索引有哪几种类型？

• 主键索引:数据列不允许重复，不允许为 NULL，一个表只能有一个主键。

• 唯一索引:数据列不允许重复，允许为 NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引。

• 普通索引:基本的索引类型，没有唯一性的限制，允许为 NULL值。

  • 可以通过 ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

  • 可以通过 ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引。

• 全文索引：是目前搜索引擎使用的一种关键技术。

• 可以通过 ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引。

当order by字段出现在where条件中时，会利用该字段的索引避免排序

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。

Hash索引和B+树索引的区别？

• 哈希索引不支持排序，因为哈希表是无序的。
• 哈希索引不支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

为什么B+树比B树更适合实现数据库索引？

• 由于B+树的数据都存储在叶子结点中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。
• B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。
• B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引的设计原则？

• 对于经常作为查询条件的字段，应该建立索引，以提高查询速度
• 为经常需要排序、分组和联合操作的字段建立索引
• 索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
• 避免给”大字段”建立索引。尽量使用数据量小的字段作为索引。因为MySQL在维护索引的时候是会将字段值一起维护的，那这样必然会导致索引占用更多的空间，另外在排序的时候需要花费更多的时间去对比。
• 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
• 索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
• 频繁增删改的字段不要建立索引。假设某个字段频繁修改，那就意味着需要频繁的重建索引，这必然影响MySQL的性能
• 利用最左前缀原则。

导致索引失效的情况：

• 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引
• 以%开头的like查询如%abc，无法使用索引；非%开头的like查询如abc%，相当于范围查询，会使用索引
• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效
• 判断索引列是否不等于某个值时
• 对索引列进行运算
• 查询条件使用or连接，也会导致索引失效

MySQL 中有哪几种锁？

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

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

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

• read uncommited：读到未提交数据

• read committed：脏读，不可重复读

• repeatable read：可重读

• serializable：串行事物

char 和 varchar 的区别？

• char 和 varchar 类型在存储和检索方面有所不同

• char 列长度固定为创建表时声明的长度，长度值范围是 1 到 255

• 当 char 值被存储时，它们被用空格填充到特定长度，检索 char 值时需删除尾随空格

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

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以被任何外键引用。

如何在 Unix 和 MySQL 时间戳之间进行转换？

UNIX_TIMESTAMP 是从MySQL 时间戳转换为 Unix时间戳的命令 FROM_UNIXTIME是从Unix 时间戳转换为 MySQL时间戳的命令。

MyISAM 表类型将在哪里存储，并且还提供其存储格式？

每个 MyISAM 表格以三种格式存储在磁盘上：

• “.frm”文件 存储表定义

• 数据文件具有“.MYD”（MYData）扩展名

• 索引文件具有“.MYI”（MYIndex）扩展名

MySQL 里记录货币用什么字段类型好

NUMERIC和DECIMAL 类型被MySQL实现为同样的类型，这在 SQL92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

例如：salary DECIMAL(9,2)在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。因此，在这种情况下，能被存储在 salary 列中的值的范围是从-9999999.99到9999999.99。

使用索引查询一定能提高查询的性能吗？为什么

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的 INSERT，DELETE，UPDATE将为此多付出 4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的 30%

• 基于非唯一性索引的检索

百万级以上的数据如何删除

• 分批删除

  SET @done = 0;
  WHILE @done = 0 DO
      DELETE FROM your_table WHERE condition LIMIT 10000;
      SET @done = ROW_COUNT();
      -- 添加延迟以减少锁竞争
      DO SLEEP(0.5);
  END WHILE;

• 整表删除用truncate命令

什么是最左前缀原则？什么是最左匹配原则

顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。最左前缀匹配原则，非常重要的原则，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 andd = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。=和in可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。

什么是聚簇索引？何时使用聚簇索引与非聚簇索引

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer 把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的原因。

MySQL 查询缓存

MySQL在得到一个执行请求后首先去查询缓存中查找，是否执行过这条SQL语句，之前执行过的语句以及结果会以 key-value对的形式，被直接放在内存中。key是查询语句，value是查询的结果。如果通过key能够查找到这条 SQL语句，就直接妾返回 SQL 的执行结果。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果就会被放入查询缓存中。可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，效率会很高。

MySQL 分析器

如果没有命中查询，就开始执行真正的 SQL语句。

• 首先，MySQL 会根据你写的SQL 语句进行解析，分析器会先做词法分析，你写的 SQL就是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串是什么，代表什么。

• 然后进行语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL语句是否满足 MySQL语法。如果 SQL语句不正确，就会提示 You have an error in your SQL syntax。

MySQL 优化器

经过分析器的词法分析和语法分析后，你这条 SQL就合法了，MySQL就知道你要做什么了。但是在执行前，还需要进行优化器的处理，优化器会判断你使用了哪种索引，使用了何种连接，优化器的作用就是确定效率最高的执行方案。

MySQL 执行器

MySQL通过分析器知道了你的 SQL语句是否合法，你想要做什么操作，通过优化器知道了该怎么做效率最高，然后就进入了执行阶段，开始执行这条 SQL语句在执行阶段，MySQL首先会判断你有没有执行这条语句的权限，没有权限的话，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。对于有索引的表，执行的逻辑也差不多。

谈谈 SQL 优化的经验

• 查询语句无论是使用哪种判断条件等于、小于、大于，WHERE 左侧的条件查询字段不要使用函数或者表达式
• 使用 EXPLAIN 命令优化你的 SELECT 查询，对于复杂、效率低的 SQL 语句，我们通常是使用 explainsql 来分析这条 SQL 语句，这样方便我们分析，进行优化。
• 当你的 SELECT 查询语句只需要使用一条记录时，要使用 LIMIT 1。不要直接使用 SELECT*，而应该使用具体需要查询的表字段，因为使用 EXPLAIN进行分析时，SELECT”使用的是全表扫描，也就是 type =all
• 为每一张表设置一个 ID属性。
• 避免在 MHERE 字句中对字段进行 NULL
• 判断避免在 WHERE中使用!或>操作符
• 使用 BETWEEN AND 替代 IN
• 为搜索字段创建索引
• 选择正确的存储引擎，InnoDB、MyISAM、MEMORY等
• 使用 LIKE%abc%不会走索引，而使用 LIKE abc%会走索引。
• 对于枚举类型的字段(即有固定罗列值的字段)，建议使用 ENUM 而不是VARCHAR，如性别、星期、类型、类别等。
• 拆分大的 DELETE 或INSERT 语句
• 选择合适的字段类型，选择标准是尽可能小、尽可能定长、尽可能使用整数。
• 字段设计尽可能使用 NOT NULL
• 进行水平切割或者垂直分割

什么叫外链接？

左外连接:又称为左连接，这种连接方式会显示左表不符合条件的数据行，右边不符合条件的数据行直接显示 NULL。右外连接:也被称为右连接，他与左连接相对，这种连接方式会显示右表不符合条件的数据行，左表不符合条件的数据行直接显示 NULL。

使用 union 和 union all 时需要注意些什么？

通过union 连接的 SQL分别单独取出的列数必须相同。使用union 时，多个相等的行将会被合并，由于合并比较耗时，一般不直接使用union 进行合并，而是通常采用 union all 进行合并。

InnoDB 存储引擎的特点

自从MySQL5.1 之后，默认的存储引擎变成了 InnoDB存储引擎，相对于MylSAM，InnoDB 存储引擎有了较大的改变，它的主要特点是

• 支持事务操作，具有事务 ACID隔离特性，默认的隔离级别是可重复读(repetable-read)、通过MVCC(并发版本控制)来实现的。能够解决 脏读和 不可重复读 的问题。 InnoDB 支持外键操作。

• InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。

• 和MyISAM 一样的是，InnoDB存储引擎也有 frm文件存储表结构定义，但是不同的是，InnoDB的表数据与索引数据是存储在一起的，都位于 B+数的叶子节点上，而 MylSAM的表数据和索引数据是分开的。

• InnoDB 有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。

• InnoDB 和 MylSAM支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。

• 增删改查性能方面，果执行大量的增删改操作，推荐使用 InnoDB存储引擎，它在删除操作时是对行删除，不会重建表。

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。

优点：支持事务和崩溃修复能力；引入了行级锁和外键约束。

缺点：占用的数据空间相对较大。

适用场景：需要事务支持，并且有较高的并发读写频率。

MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

优点：访问速度快。

缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

适用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY存储引擎

MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

优点：访问速度较快。

缺点：

1. 哈希索引数据不是按照索引值顺序存储，无法用于排序。
2. 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
3. 只支持等值比较，不支持范围查询。
4. 当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

ARCHIVE存储引擎

ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。

MyISAM和InnoDB的区别？

1. 存储结构的区别。每个MyISAM在磁盘上存储成三个文件。文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
2. 存储空间的区别。MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
3. 可移植性、备份及恢复。MyISAM数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。对于InnoDB，可行的方案是拷贝数据文件、备份 binlog，或者用mysqldump，在数据量达到几十G的时候就相对麻烦了。
4. 是否支持行级锁。MyISAM 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。而InnoDB 支持行级锁和表级锁，默认为行级锁。行锁大幅度提高了多用户并发操作的性能。
5. 是否支持事务和崩溃后的安全恢复。 MyISAM 不提供事务支持。而InnoDB 提供事务支持，具有事务、回滚和崩溃修复能力。
6. 是否支持外键。MyISAM不支持，而InnoDB支持。
7. 是否支持MVCC。MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。
8. 是否支持聚集索引。MyISAM不支持聚集索引，InnoDB支持聚集索引。
9. 全文索引。MyISAM支持 FULLTEXT类型的全文索引。InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
10. 表主键。MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。
11. 表的行数。MyISAM保存有表的总行数，如果select count(*) from table;会直接取出该值。InnoDB没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where条件后，MyISAM和InnoDB处理的方式都一样。