【面试准备DAY1】mysql相关（1）

开一个面试准备专题，记录下自己的准备过程，欢迎补充指正~
（非原创，仅对自己有的资料进行汇总，侵删

数据库三范式是什么

1、第一范式（1NF）：字段具有原子性，不可再分。简而言之就是每一列只有一个值，姓名就是姓名，性别就是性别，不能出现“姓名-性别”这样的组合字段；
2、第二范式（2NF）：建立在第一范式的基础上，要求数据库表中的每个实例或行必须可以被唯一区分。（就是要有主键，保证数据的唯一性）；
3、第三范式（3FN）：建立在第二范式的基础上，要求一个数据库表不包含已在其他表中已包含的非主关键字信息。即每一个表都不包含其他表已经包含的非主关键字信息。

常用的索引类型

1、普通索引：即针对数据库表创建索引；
2、唯一索引：与普通索引类似，不同的就是：MySQL 数据库索引列的值必须唯一，但允许有空值；
3、主键索引: 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引；
4、组合索引: 为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。即将数据库表中的多个字段联合起来作为一个组合索引。
（TODO 关于索引的内容还有很多，可能会单独写一篇整理一下索引问题）

自增主键问题

例如：一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？
这个问题分两种情况
（1）如果表的类型是 MyISAM，那么是 18，因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大
ID 也不会丢失；
（2）如果表的类型是 InnoDB，那么是 15，InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行OPTIMIZE 操作，都会导致最大 ID 丢失。

mysql 中 MyISAM与 InnoDB的区别

1、事务支持
MyISAM强调的是性能，每次查询具有原子性，执行速度比InnoDB快，但不提供事务支持；
InnoDB提供事务支持、外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表；
2、锁
InnoDB支持行级锁，MyISAM支持表级锁（用户在操作MyISAM时，select，update，delete，insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插入新的数据）
3、MVCC
InnoDB支持MVCC（多版本并发控制），MyISAM不支持。MVCC具体内容参考：innodb中的MVCC
4、外键
InnoDB 支持外键，而 MyISAM 不支持；
5、主键
MyISAM允许没有主键和索引的表存在；
InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键（用户不可见）
6、全文索引
InnoDB 不支持全文索引，而 MyISAM 支持。（这是目前唯一一个MyISAM有而InnoDB没有的，但全文索引不常用问的也不多，感兴趣可参考：MySQL 之全文索引）
7、存储结构
MyISAM：每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。
8、可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以
在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作；
InnoDB：免费的方案可以是拷贝数据文件、备份binlog，或者用 mysqldump。

mysql 中 varchar 与 char 的区别

1、 varchar 与 char 的区别：char 是一种固定长度的类型，varchar 则是一种可变长度的类型；
2、varchar(50)中 50 的涵义 : 最多存放 50 个字节
3、char 列长度固定为创建表时声明的长度，长度值范围是 1 到 255，当 char 值被存储时，它们被用空格填充到特定长度，检索 char 值时需删除尾随空格。

四种事务隔离级别

1、Read Uncommitted读未提交
所有事务都可以看到其他未提交事务的执行结果。读取未提交的数据，也被称为脏读（Dirty Read）
2、Read Committed读已提交
大多数数据库系统的默认隔离级别（不是MySQL 的默认）。只能看见已经提交事务所做的改变。因为同一事务的其他实例在本实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果，所以造成不可重复读（Nonrepeatable Read）；
3. Repeatable Read可重读
MySQL 的默认事务隔离级别，确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。可能产生幻读（Phantom Read）。当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现出现新的“幻影” 行。

InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control 间隙锁）机制解决了不可重复读和幻读的问题。注：其实多版本只是解决不可重复读问题，而加上间隙锁（并发控制）解决了幻读问题。

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

mysql 当记录不存在时 insert存在时 update怎么写？

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

mysql 查询是否区分大小写？

不区分

SELECT VERSION(), CURRENT_DATE;
SeLect version(), current_date;
seleCt vErSiOn(), current_DATE;

所有这些例子都是一样的，Mysql 不区分大小写。

BLOB 和 TEXT 有什么区别？

BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写，对 TEXT值不区分大小写。

BLOB 是一个二进制对象，可以容纳可变数量的数据。有四种类型的 BLOB（TINYBLOB、BLOB、、MEDIUMBLOB 、LONGBLOB）它们只能在所能容纳价值的最大长度上有所不同。
TEXT 是一个不区分大小写的 BLOB。四种 TEXT 类型（TINYTEXT、TEXT、MEDIUMTEXT 、LONGTEXT）它们对应于四种 BLOB 类型。

mysql 如何优化 DISTINCT

DISTINCT 在所有列上转换为 GROUP BY，并与 ORDER BY 子句结合使用。
例：

SELECT DISTINCT t1.a FROM t1,t2 where t1.b=t2.b;

优化：

SELECT t1.a FROM t1,t2 where t1.b=t2.b GROUP BY t1.a;

常用的SQL函数

数学函数
abs（num）求绝对值
floor（num）向下取整
ceil（num）向上取整
字符串函数
insert (s1,index,length,s2) 替换函数

s1 表示被替换的字符串
s2 表示将要替换的字符串
Index 表示被替换的位置, 从 1 开始
length表示被替换的长度

upper（str），ucase（str）将字母改为大写 lower（str），lcase（str）将字母改为小写
left（str，length）返回 str 字符串的前 length 个字符
right（str，length）返回 str 字符串的后 length 个字符
substring（str，index，length）返回 str 字符串从 index 位开始长度为
length 个字符（index 从 1 开始）
reverse（str）将 str 字符串倒序输出
日期函数
curdate（）、current_date( ) 获取当前日期
curtime（）、current_time( ) 获取当前日期
now（）获取当前日期和时间
datediff（d1、d2）d1 和 d2 之间的天数差
adddate（date，num）返回 date 日期开始，之后 num 天的日期
subdate（date，num）返回 date 日期开始，之前 num 天的日期
聚合函数
count（字段）根据某个字段统计总记录数（当前数据库保存到多少条数据）
sum（字段）计算某个字段的数值总和
avg（字段）计算某个字段的数值的平均值
max（字段）、min（字段）求某个字段最大或最小值

mysql 优化

优化数据类型

• 避免使用 NULL，NULL 需要特殊处理, 大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。不要以为 NULL 不需要空间，其需要额外的空间，并且，在进行比较的时候，处理逻辑会更复杂。
• 仅可能使用更小的字段，MySQL 从磁盘读取数据后是存储到内存中的，然后使用 cpu 周期和磁盘 I/O 读取它，这意味着越小的数据类型占用的空间越小

小心字符集转换
客户端或应用程序使用的字符集可能和表本身的字符集不一样，这需要MySQL 在运行过程中隐含地进行转换

优化子查询
遇到子查询时，MySQL 查询优化引擎并不是总是最有效的，所以经常将子查询转换为连接查询，要注意的一点是，要确保连接表 (第二个表) 的连接列是有索引的，在第一个表上 MySQL 通常会相对于第二个表的查询子集进行一次全表扫描，这是嵌套循环算法的一部分

优化 UNION

• UNION 从两个互不关联的表中返回数据，这就意味着不会出现重复的行，同时也必须对数据进行排序，我们知道排序是非常耗费资源的，特别是对大表的排序
• UNION ALL 可以大大加快速度，如果已经知道数据不会包括重复行，或者不在乎是否会出现重复的行，在这两种情况下使用UNION ALL 更适合。还可以在应用程序逻辑中采用某些方法避免出现重复的行，这样 UNION ALL 和 UNION 返回的结果都是一样的，但 UNION ALL 不会进行排序

查询缓存
大多数的 MySQL 服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被 MySQL 的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。
注意：某些查询语句会让 MySQL 不使用缓存

select username from user where signup_date >= CURDATE(); 

上面的sql使用了CURDATE() ，MySQL 的查询缓存对这个函数是不起作用的。所以，像 NOW() 和 RAND() 或是其它的诸如此类的 SQL 函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。可以用一个变量来代替 MySQL 的函数，从而开启缓存。

使用EXPLAIN
可以使用 EXPLAIN 关键字了解MySQL 是如何处理SQL语句的（例如的索引主键被如何利用的，数据表是如何被搜索和排序的）。可以帮我们分析查询语句或是表结构的性能瓶颈。

当只要一行数据时使用 LIMIT 1
当查询表的有些时候，我们知道结果只会有一条结果，这个时候加上 LIMIT 1 可以增加性能。MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

为搜索字段建索引
索引并不一定就是给主键或是唯一的字段。如果某个字段经常用来做搜索，为其建立索引会大大提高搜索效率。

在 join表的时候使用相同类型的字段，并将其索引
如果有很多 join查询，应该确认两个表中 join的字段是被建过索引的。这样，MySQL 内部会启动优化 join的 SQL 语句的机制。而且，这些被用来 join的字段，应该是相同的类型的。例如：如果要把DECIMAL 字段和INT 字段 join在一起，MySQL 就无法使用它们的索引。对于那些 STRING 类型，还需要有相同的字符集才行。(两个表的字符集有可能不一样)

千万不要 ORDER BY RAND()
RAND()函数很耗 CPU 时间，如果需要打乱返回的数据行可以使用其他方法

避免 SELECT*
从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果数据库服务器和 WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。需要什么就取什么即可。

使用 ENUM 而不是 VARCHAR
类似“性别”，“国家”，“民族”，“状态”或“部门”，这些字段的取值是有限而且固定的，推荐使用 ENUM而不是 VARCHAR。ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。

使用PROCEDURE ANALYSE()
PROCEDURE ANALYSE() 会让 mysql帮我们去分析数据库字段和实际的数据，并会提供一些建议。例如，如果创建了一个 INT 字段作为主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议把这个字段的类型改成 MEDIUMINT 。或是使用了一个 VARCHAR 字段，因为数据不多，mysql可能会建议把它改成 ENUM。

固定长度的表会更快
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static”或 “fixed-length”。 例如，表中没有 VARCHAR，TEXT，BLOB这些字段。只要包括了其中一个可变长字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。固定长度的表会提高性能，因为 MySQL 搜寻得会更快，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论用不用，他都是要分配那么多的空间。

垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。
示例一：
在 Users 表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且在数据库操作的时候除了个人信息外，并不需要经常读取或是改写这个字段。那么，可以他放到另外一张表中，这样会让表有更好的性能。
示例二：
有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，可以把这个字段放到另一个表中，这样就不会影响对用户 ID，用户名，用户角色的不停地读取了，查询缓存会增加很多性能。
需要注意的是，这些被分出去的字段所形成的表，必须不会经常性地去 Join 他们，不然的话，这样的性能会比不分割时还要差。

拆分大的 DELETE 或 INSERT 语句
这两个操作是会锁表的，表一锁住了，别的操作都进不来了。可以使用 LIMIT 分批执行。

[SELECT *] 和[SELECT 全部字段]的 2 种写法有何优缺点?

• 前者要解析数据字典，后者不需要
• 结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。
• 表字段改名，前者不需要修改，后者需要改
• 后者可以建立索引进行优化，前者无法优化
• 后者的可读性比前者要高

mysql 中有哪几种锁

MyISAM 支持表锁，InnoDB 支持表锁和行锁，默认为行锁
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量
最低
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

DAY1结束 明天继续