MySQL知识点汇总

1.数据库设计三大范式汇总

作用：

        数据库设计范式是数据库在设计需要遵守的数据准则，满足范式的数据库结构清晰，不会发生操作异常，可以避免数据冗余。

第一范式(1NF)：

        每个字段都是最小字段，具有原子性不可再分；

第二范式(2NF)：

        每张表必须存在主键，并且其他字段必须和主键存在直接依赖，不能仅仅与主键的某一部分依赖（例如：联合主键）；

第三范式(3NF)：

        每个字段不能与主键间接依赖，即每张表只能存一种数据，表和表之间使用外键关系关联。

2.什么是SQL注入?

• 攻击者通过用户输入的字符串内容中加入SQL语句，与程序中的原语句进行拼接，形成恶意查询，非法命令等攻击方式；
• 避免SQL注入的方法：过滤输入内容和使用参数化传值(?占位符)；

3.SQL语句的执行流程是什么？

1.  首先通过MySQL连接器建立 TCP 链接，从数据库连接池中返回一个空闲线程。在执行SQL语句前进行授权认证检查用户密码，角色权限；
2. 权限认证通过后，将SQL语句交给Parser分析器进行语法解析和语义分析，生成语法分析树，判断SQL语句是否满足语法；
3. 对SQL语句进行预处理(SQL预编译)
4. 执行查询优化器，获取SQL语句最优执行路径，产生SQL执行计划；
5. 按照SQL执行计划，调用数据库存储引擎，通过索引查询或全表扫描；

4.内连接和外连接有什么区别？左连接和右连接有什么区别？

1.  内连接是保证两个表所有行都满足连接条件，连接结果仅包含符合连接的行，参与连接的两个表都应符合连接条件。
2. 外连接不仅包含符合连接条件的行，还包括左表，右表或两个连接表中的所有数据行。外连接分为：左外连接，右外连接和全外连接。

• 左外连接：left outer join 或者 left join ，左边表数据行全部保留，右边表保留符合连接条件的行；
• 右外连接：right outer join 或者 right join ，右边表数据行全部保留，左边表保留符合连接条件的行；
• 全外连接：full outer join 或者 full join ，保留所有行。

5.Union和Union all 有什么区别？

1. Union：对两个结果集进行并集操作，不包括重复行，相当于distinct，同时进行默认规则的排序；
2. Union all：对两个结果集进行并集操作，包括重复行，即所有结果全部显示，同时对结果进行排序。      

6.MySQL如何取差集？

1. 当对比字段存在索引时，使用 NOT ESISTS 效率比较高

   SELECT *
   FROM basic_category_info AS a
   WHERE NOT EXISTS (SELECT 1 FROM device_info AS b WHERE a.category=b.device_category_id)

2.  对比字段没有索引时，使用 LEFT JOIN 或 RIGHT JOIN 效率比较高

   SELECT *
   FROM basic_category_info AS a
   LEFT JOIN device_info AS b ON a.category=b.device_category_id
   WHERE b.device_id IS null

     

 7.Delete和Truncate有什么区别？

Delete 和 Truncate 都可以清空表中所有数据，但存在以下区别：

1. Delete后面可以跟条件，Truncate不可以；
2. Delete语句是逐条记录删除，删除的每条记录都会写入日志；而Truncate一次性删掉整个数据页，日志里只记录页释放；
3. Delete删空表后会保留一个空表，而Truncate在表中不会留任何数据页；
4. 当使用锁执行Delete语句时，将锁定表中的各行数据以便删除；Truncate始终锁定表和页，而不是锁定行；
5. 如果有identity产生的自增id列，Delete后仍然从上次的数开始增加，即种子不变，而Truncate后，种子会恢复初始；

8.count(*)和count(1)有什么区别？

1. 执行方式：count(1)计算当前查询结果存在多少个常量值"1"，count(*)会把星号替换为所有字段名，用于计算查询结果中每个字段存在多少个值。；
2. 执行效率：如果存在主键，count(主键)效率更高，其次count(1) > count(*)。

9.MyISAM 和 InnoDB 的区别？ 

• MySQL 5.5版本之前，MyISAM 引擎是 MySQL的默认存储引擎，但是 MyISAM 不支持行级锁和事务锁，最大缺陷是崩溃后无法安全恢复；
• MySQL 5.5版本之后，MySQL 引入InnoDB 是事物型数据库引擎并设置为默认存储引擎。可以使用Commit和Rollback语句。
• 主要存在以下几个方面的差距：

1. 事务：MyISAM 不支持事务；InnoDB是事物型数据存储引擎，可以使用 Commit 和 Rollback 语句；
2. 并发：InnoDB 只支持表级锁(table-level locking)，而 InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁；
3. 外键：InnoDB不允许创建外键，而 InnoDB 支持外键；
4. 备份：InnoDB 支持在线热备份；
5. 崩溃恢复：MyISAM 崩溃后造成的概率比 InnoDB高很多，而恢复的速度也很慢。

10.表级锁和行级锁

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

 11.InnoDB存储引擎的三种行级锁

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

12.谈谈你对事务的理解 

• 事务是数据库的一种特性，用于确保一个执行过程中的所有步骤全部成功或全部失败，是数据库操作的最小单元；
• 事务包括ACID四个特点：

1. 原子性(Atomicity)：事务具备原子性，代表事务是数据库的最小执行单元，事务的原子性确保数据库操作过程中的步骤全部成功或全部失败；
2. 一致性(Consistency)：事务对数据进行能修改操作，要求数据修改前后状态保持一致。例如银行转账事务是否成功，转账者和收款者金额总和不变。
3. 隔离性(Isolation)：一个事务的执行不会被其他事务干扰，一个事务内部的操作和使用的数据对并发中的事务是隔离的；
4. 持久性(Durability)：一个事务一旦被提交，他对数据库中的数据改变是永久的。

13.谈谈数据库事务的实现原理

• MySQL InnoDB 引擎使用 redo log (重做日志) 保证事务的持久性，使用 undo log(回滚日志)来保证事务的原子性；
  • redo log 是 InnoDB 存储引擎层的日志，又称重做日志文件，用于记录实务操作的变化，记录的是数据改变之后的值。当MySQL意外宕机，InnoDB 存储引擎会使用 redo log 恢复，以此来保证数据的持久性；
  • undo log 保证了是无法生前的数据的一个版本，用于回滚。
•  MySQL InnoDB 引擎通过锁机制、MVCC(多版本并发控制)等手段来保证事务的隔离性；
  • MVCC是行级锁的一个变种但是它在很多情况下避免了加锁操作，因此开销更低。大多数的MVCC都实现了非阻塞的读操作，写操作也之锁定必要的行。
•  保证了事务的持久性，原子性，隔离性之后，一致性才能得到保障。

14.什么是 MVCC ? 

• multi-version-concurrent-control
• MVCC是行级锁的一个变种但是它在很多情况下避免了加锁操作，因此开销更低。大多数的MVCC都实现了非阻塞的读操作，写操作也之锁定必要的行。
• MVCC是一种用来解决 读-写 冲突的并发控制，为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与实务时间戳关联，也就是每个事务都有一个对应版本的快照，快照版本按照单项增长的时间戳来决定先后顺序。
• 读操作，只需要读该事务开始前的数据库快照，并不去读取整在修改的数据仅读取事务开始前的最新版本。