Mysql八股文

Mysql八股文

数据库的三范式是什么

• 第一范式：列不可再分
• 第二范式：行可以唯一区分，主键约束
• 第三范式：表的非主属性不能依赖与 其他表的非主属性 外键约束
• 且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式 建立第一第二范式上。

MySQL数据库引擎有哪些

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

• MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间 相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎
• Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持 外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些
• Memory:全表锁，存储在内存中，速度快，但会占用和数据量成正比的内存空间且数据在 mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表
• MERGE：是一组MYISAM表的组合

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

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

MySQL 索引类型有哪些

• 主键索引
  • 索引列中的值必须是唯一的，不允许有空值，一个表只能有一个主键。
• 唯一索引
  • 索引列中的值必须是唯一的，但是允许为空值，一个表可以允许多个列创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一 组合索引
• 普通索引
  • MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
  • 可以通过 ALTER TABLE table_name ADD INDEX index_name (column);创建普通 索引
  • 可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引。
• 全文索引
  • 只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普 通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以 使用全文索引。
  • 可以通过 ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引。
• 空间索引
  • MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这 方面遵循OpenGIS几何数据模型规则。
• 前缀索引
  • 在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不 能指定。

说说什么是 MVCC

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

• 实现原理
  • MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主 要是依赖记录中的 3 个隐式字段、undo 日志、Read View 来实现的。
• 解决的问题
  • 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

MySQL 数据库的锁

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

1. 共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
2. 排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用 户读取正在写入的资源。
3. 表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。
4. 行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现， 否则会自动锁全表，那么就不是行锁了）。

什么是锁升级

• MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加 在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index 和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。
• 当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的 内容大于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记 录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成 索引失效，行锁自然就会升级为表锁。

怎样尽量避免死锁的出现

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

索引失效的场景

• 在where子句中使用!=或<>操作符，将引起引擎放弃使用索引进而全表扫描，优化器无法通过索引确定将要命中的行数，因而需要搜索该表的所有行
• 在where子句中使用or作为连接条件
• 在where子句中对字段进行表达式操作
• 在where子句中对字段进行函数操作
• 复合（组合）索引遵循最左前缀原则
• 字段类型是字符串类型，查询时一定要加引号，否则索引失效
• like查询%不能在前面
• 表字段为NULL也是不可以使用索引的
• 如果mysql评估使用索引比全表扫描更慢，会放弃使用索引。可以在语句中强制走索引

主键与唯一索引有什么区别

• 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键
• 主键不允许为空值，唯一索引列允许空值
• 一个表只能有一个主键，但是可以有多个唯一索引
• 主键可以被其他表引用为外键，唯一索引列不可以
• 主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质区别

创建索引时需要注意什么

• 非空字段：应该指定列为 NOT NULL，除非你想存储 NULL。在 mysql 中，含有空值的 列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该 用 0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通 过 count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度 高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次 IO 操作获取 的数据越大效率越高。

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%
• 基于非唯一性索引的检索

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

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对 数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的 IO,会降低 增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询 MySQL 官方手册 得知删除数据的速度和创建的索引数量是成正比的

• 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
• 然后删除其中无用数据（此过程需要不到两分钟）
• 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
• 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑 了。

索引最左优先(前缀)原则

• 顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频 繁的一列放在最左边。

• MySQL组合索引（复合索引）的最左优先原则。最左优先就是说组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。只要组合索引最左边第一个字段出现在Where中，那么不管后面的字段出现与否或者出现顺序如何，MySQL引擎都会自动调用索引来优化查询效率。

• 根据最左匹配原则可以知道B-Tree建立索引的过程，比如假设有一个3列索引(col1,col2,col3),那么MySQL只会会建立三个索引(col1),(col1,col2),(col1,col2,col3)。

• 最左前缀匹配原则，非常重要的原则，mysql 会一直向右匹配直到遇到范围查询(>、<、 between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 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 的查询优化器会帮你优化成索引可以识别的形式。

什么是临时表，何时删除临时表

什么是临时表?MySQL 在执行 SQL 语句的过程中 通常会临时创建一些存储中间结果集的表，临 时 表只对当前连接可见，在连接关闭时，临时表会被删除并释放所有表空间。

临时表分为两种:一种是内存临时表，一种是磁盘临时表，什么区别呢?内存临时表使用的是 MEMORY 存储引擎，而磁盘临时表采用的是 MylSAM 存储引擎。 MySQL 会在下面这几种情况产生临时表。

• 使用 UNION 查询:UNION 有两种，一种是 UNION，一种是 UNION ALL，它们都用于联合查询; 区别是使用 UNION 会去掉两个表中的重复数据，相当于对结果集做了一下 去重(distinct)。使用 UNIONALL，则不会排重，返回所有的行。使用 UNION 查询会产生临时表。
• 使用 TEMPTABLE 算法或者是 UNION 查询中的视图。TEMPTABLE 算法是一种创建临时表的算 法，它是将结果放置到临时表中，意味这要 MySQL 要先创建好一个临时表，然后将结果放到临 时表中去，然后再使用这个临时表进行相应的查询。
• ORDER BY 和 GROUPBY 的子句不一样时也会产生临时表。
• DISTINCT 查询并且加上 ORDER BY 时
• SQL 中用到 SQL_SMALL_RESULT 选项时;如果查询结果比较小的时候，可以加上 SQL SMALL RESULT 来优化，产生临时表
• FROM 中的子查询;
• EXPLAIN 查看执行计划结果的 Extra 列中，如果使用 Using Temporary 就表示会用到临时表。

MyISAM 存储引擎的特点

在 5.1 版本之前，MyISAM 是 MySQL 的默认存储引擎，MylSAM 并发性比较差，使用的场景比 较少主要特点是:

• 不支持事务操作，ACID 的特性也就不存在了，这一设计是为了性能和效率考虑的，
• 不支持外键操作，如果强行增加外键，MySQL 不会报错，只不过外键不起作用
• MyISAM 默认的锁粒度是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易 发生死锁的情况
• MyISAM 会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是 frm(存储表定义)、 MYD(MYData，存储数据)、MYI(MyIndex，存储索引)。这里需要特别注意的是 MyISAM 只缓 存 索引文件，并不缓存数据文件
• MyISAM 支持的索引类型有全局索引(Full-Text)、B-Tree 索引、R-Tree 索引
  • Full-Text 索引:它的出现是为了解决针对文本的模糊查询效率较低的问题。
  • B-Tree 索引:所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节 点
  • R-Tree 索引:它的存储方式和 B-Tree 索引有一些区别，主要设计用于存储空间和多维数据的 字段做索引目前的 MySQL 版本仅支持 geometry 类型的字段作索引，相对于 BTREE,RTREE 的优势在于范围查找。
• 数据库所在主机如果宕机，MyISAM 的数据文件容易损坏，而且难以恢复
• 增删改查性能方面:SELECT 性能较高，适用于查询较多的情

InnoDB 存储引擎的特点

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

• 支持事务操作，具有事务 ACID 隔离特性，默认的隔离级别是可重复读(repetable-read)、通过 MVCC(并发版本控制)来实现的。能够解决 脏读 和 不可重复读 的问题。 InnoDB 支持外键操 作。
• InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。
• 和 MyISAM 一样的是，InnoDB 存储引擎也有 frm 文件存储表结构定义，但是不同的是， InnoDB 的表数据与索引数据是存储在一起的(聚簇索引)，都位于 B+数的叶子节点上，而 MylSAM 的表数据和索引数据是分开的（非聚簇索引）。
• InnoDB 有安全的日志文件undo、redo日志，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性
• InnoDB 和 MylSAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异
• 增删改查性能方面，果执行大量的增删改操作，推荐使用 InnoDB 存储引擎，它在删除操作时是 对行删除，不会重建表。