MySQL相关

一、数据库的三种范式

第一范式：数据库表的每一列都是不可分割的基本数据项。

第二范式：数据库表中的每个实例或行必须可以被惟一地区分，即主键。

第三范式：要求数据库表中不包含已在其它表中已包含的非主关键字信息，即外键。

二、存储过程

存储过程是一个可编程的函数，它在数据库中创建并保存。

优点有：

1、存储过程能实现较快的执行速度

2、存储过程允许标准组件式编程。

3、存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

4、存储过程可被作为一种安全机制来充分利用。

5、存储过程能够减少网络流量

存储过程和函数的异同点

相同点：存储过程和函数都是为了可重复的执行操作数据库的sql 语句的集合。

不同点：

1、标识符不同，函数的标识符是function，存储过程是proceduce。

2、函数中有返回值，且必须有返回值，而过程没有返回值，但是可以通过设置参数类型（in,out)来实现多个参数或者返回值。

3、函数使用select 调用，存储过程需要使用call 调用。

三、事务的特性

1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。

2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

3、隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。

4、持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。

四、事务的四种隔离级别

1、Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。出现脏读。

2、Read Committed（读取提交内容）

一个事务只能看见已经提交事务所做的改变。出现不可重复读。

3、Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。导致幻读 。

4、Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读：在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

五、union 与union all的区别

union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

union all 则会显示重复结果，只是简单的两个结果合并并返回。所以效率比union高，在保证没有重复数据的情况下用union all。

六、drop，delete与truncate的区别

drop直接删掉表。

truncate删除表中数据，再插入时自增长id又从1开始。一次性地从表中删除所有的数据并不把删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

delete删除表中数据，可以加where字句。每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

七、数据库索引

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B_TREE。B_TREE 索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

八、MySQL引擎

主要MyISAM 与InnoDB 两个引擎，其主要区别如下：

InnoDB 支持事务，MyISAM 不支持，即是否支持回滚；

InnoDB 支持外键，MyISAM 不支持；

InnoDB 支持行锁；

MyISAM 是默认引擎，InnoDB 需要指定；

InnoDB 不支持FULLTEXT 类型的索引；

InnoDB 中不保存表的行数，MyISAM 只要简单的读出保存好的行数即可；

对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在MyISAM 表中可以和其他字段一起建立联合索引；

清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表；

MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；

九、数据库优化

1、尽量避免全表扫描，首先考虑在where及order by涉及的列上建立索引

2、避免在where子句中使用null判断、!=或<>、or操作，否则引擎将放弃使用索引，进行全表扫描

3、update如果只更改少量字段，不要update全部字段

4、使用缓存，把经常访问到且不需要经常变化的数据放到缓存，节约磁盘IO

5、垂直分表，把一些不经常读的数据放在一张表里，节约磁盘IO

6、主从分离读写，采用主从复制把数据库的读操作和写操作分离开来

7、优化硬件，采用SSD，使用磁盘队列技术。采用更快的存储方式，使用NoSQL存储经常访问的数据。

十、数据库分区、分表

分表可以通过三种方式：Mysql 集群、自定义规则和merge 存储引擎。

分区有四类：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按RANGE 分区，区别在于LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY 分区：类似于按HASH 分区，区别在于KEY 分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

十一、SQL注入以及如何防止

程序开发过程中不注意规范书写sql 语句和对特殊字符进行过滤，导致客户端可以通过POST 和GET 提交一些sql 语句正常执行，产生Sql 注入。

防止SQL注入的办法：

1、过滤掉一些常见的数据库操作关键字

2、在PHP 配置文件中将Register_globals=off，开启PHP 安全模式：Safe_mode=on;

3、提高数据库命名技巧，对于一些重要的字段根据程序的特点命名

4、打开magic_quotes_gpc 来防止SQL 注入

5、关闭错误提示信息，将错误信息写到系统日志

十二、NoSQL与关系数据库的区别

1、SQL 数据存在特定结构的表中；NoSQL 则更加灵活和可扩展，存储方式可以是JSON 文档、哈希表或者其他方式。

2、SQL 中必须定义好表和字段结构后才能添加数据，在NoSQL 中，数据可以在任何时候任何地方添加，不需要先定义表。

3、SQL 中可以使用JOIN将多个关系数据表中的数据用一条简单的查询语句查询出来。NoSQL 无，也正因为此，性能提升。

4、SQL 中不允许删除已经被使用的外部数据，而NoSQL 中可以随时删除任何数据。

5、SQL 中如果多张表数据需要同批次被更新，如果其中一张表更新失败的话其他表也不能更新成功。这种场景可以通过事务来控制。而NoSQL 中没有事务这个概念，每一个数据集的操作都是原子级的。

十三、MySQL日志

1、错误日志：记录启动，运行或者停止mysql 时出现的问题

2、通用日志：记录建立的客户端连接和执行的语句

3、二进制日志：记录所有更改数据的语句

4、慢查询日志：记录所有执行时间超过long_query_time 秒的的语句都会被记录。

十四、SELECT语句的执行顺序

from->on->join->where->group by->聚集函数->having->计算表达式->select->distinct->order by->top