大家好，我是温大大
前段时间大家在面试过程中，经常被问到数据库相关的问题。
像：sql 怎么优化，解释下数据库常见锁的，having 和 where 区别等等。
所以温大大爆肝 1 天 2 夜。
肝了「万字」从数据库基础知识、到数据索引、索、事务 以及 面试高频面试题。
包你从 sql 入门到入土，其他面试汇总：
建议可以先收藏，然后遇到有不会了查看目录，直接跳到该目录进行查阅。
目录：
表创建
drop table if exists test_a;
CREATE TABLE `test_a` (
`id` varchar(10) NOT NULL,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
drop table if exists test_a_description;
CREATE TABLE `test_a_description` (
`id` varchar(10) NOT NULL,
`age` varchar(10) ,
`address` varchar(50) ,
`parent_id` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据创建
insert into test_a values('1','小明','11');
insert into test_a values('2','宁宁','22');
insert into test_a values('3','敏敏','33');
insert into test_a values('6','生生','66');
insert into test_a_description values('1','10','aaa','1');
insert into test_a_description values('2','20','bbb','2');
insert into test_a_description values('3','30','ccc','3');
insert into test_a_description values('4','40','ddd','4');
内连接（inner join）
典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students 和 courses 表中学生标识号相同的所有行。
select * from 表 A inner join 表 B on 判断条件;
外连接
左外连接（left join）以左表为主表（查询全部）， 右表为辅表（没有的显示 null）
SQL：select * from 表 A left join 表 B on 判断条件;
右外连接（right join）
以右表为主表（查询全部）， 左表为辅表（没有的显示 null）
SQL：select * from 表 A right join 表 B on 判断条件;
全连接（full join）
两个表的所有数据都展示出来
SQL：select * from 表 A full join 表 B on 判断条件;
联合 (union / union all）
union 操作符合并的结果集，不会允许重复值，如果允许有重复值的话，使用 UNION ALL.
SQL：
select * from A
union
select * from B
第 1 范式
确保数据库表字段的原子性。
比如字段 userInfo: 广东省 10086' ，依照第一范式必须拆分成 userInfo: 广东省 userTel:10086 两个字段。
第 2 范式
首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。
举个例子。假定选课关系表为 student_course(student_no, student_name, age, course_name, grade, credit)，主键为 (student_no, course_name)。其中学分完全依赖于课程名称，姓名年龄完全依赖学号，不符合第二范式，会导致数据冗余（学生选 n 门课，姓名年龄有 n 条记录）、插入异常（插入一门新课，因为没有学号，无法保存新课记录）等问题。
可以拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。
第 3 范式
首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
假定学生关系表为 Student(student_no, student_name, age, academy_id, academy_telephone)，主键为"学号"，其中学院 id 依赖于学号，而学院地点和学院电话依赖于学院 id，存在传递依赖，不符合第三范式。
可以把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。
2NF 和 3NF 的区别
索引是存储引擎用于提高数据库表的访问速度的一种「数据结构」。
优点：
缺点：
B+ 树索引
B+ 树是基于「B 树」和「叶子节点」顺序访问指针进行实现，它具有 B 树的平衡性，并且通过「顺序访问指针」来提高区间查询的性能。
在 B+ 树中，节点中的 key 从「左到右递」增排列，如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。
进行查找操作时，首先在根节点进行「二分查找」，找到 key 所在的指针，然后「递归」地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的数据项。
MySQL 数据库使用最多的索引类型是「BTREE 索引」，底层基于「B+ 树｜数据结构来实现。
哈希索引
Hash 索引和 B+ 树索引的区别？
为什么 B+ 树比 B 树更适合实现数据库索引？
由于 B+ 树的数据都存储在「叶子结点」中，叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可
但是 B 树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次「中序遍历」按序来扫
所以 B+ 树更加适合在「区间查询」的情况，而在数据库中基于范围的查询是「非常频繁」的，所以通常 B+ 树用于数据库索引。
B+ 树的节点只存储「索引 key」值，具体信息的地址存在于「叶子节点」的地址中。
这就使以页为单位的索引中可以存放更多的节点，减少更多的「I/O 支出」。
B+ 树的查询「效率更加稳定」，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
1、主键索引：名为 primary 的唯一非空索引，不允许有空值。
2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。唯一索引和主键索引的区别是：唯一约束的列可以为 null 且可以存在多个 null 值。唯一索引的用途：唯一标识数据库表中的每条记录，主要是用来防止数据重复插入。
3、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时需遵循最左前缀原则。
4、全文索引：只有在 MyISAM 引擎上才能使用，只能在 CHAR、VARCHAR 和 TEXT 类型字段上使用全文索引。
设计原则
导致索引失效的情况
最左匹配原则
如果 SQL 语句中用到了组合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个组合索引去进行匹配。
当遇到范围查询 (>、<、between、like) 就会停止匹配，后面的字段不会用到索引。
对 (a,b,c) 建立索引，查询条件使用 a/ab/abc 会走索引，使用 bc 不会走索引。
对 (a,b,c,d) 建立索引，查询条件为 a = 1 and b = 2 and c > 3 and d = 4，那么 a、b 和 c 三个字段能用到索引，而 d 无法使用索引。因为遇到了范围查询。
InnoDB 使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。
聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。
对于 InnoDB 来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引。如果没有主键也没有合适的唯一索引，那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为 6 个字节，它的值会随着数据的插入自增。
select 的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于 innodb 表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。
不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以 MySQL 使用 b+ 树索引做覆盖索引。
对于使用了覆盖索引的查询，在查询前面使用 explain，输出的 extra 列会显示为 using index。
比如 user_like 用户点赞表，组合索引为 (user_id, blog_id)，user_id 和 blog_id 都不为 null。
explain select blog_id from user_like where user_id = 13;
explain select user_id from user_like where blog_id = 1;
有时需要在很长的字符列上创建索引，这会造成索引特别大且慢。使用前缀索引可以避免这个问题。
前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。
创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的数据行。
建立前缀索引的方式：
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
原因：索引不能提升性能时，引入分库分表
当单表的数据量达到 1000W 或 100G 以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。
数据切分可以分为两种方式：垂直划分和水平划分。
垂直划分
优点：行记录变小，数据页可以存放更多记录，在查询时减少 I/O 次数。
缺点：主键出现冗余，需要管理冗余列；会引起表连接 JOIN 操作，可以通过在业务服务器上进行 join 来减少数据库压力；依然存在单表数据量过大的问题。
水平划分
优点：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。
缺点：分片事务一致性难以解决，跨节点 join 性能差，逻辑复杂数据分片在扩容时需要迁移
分区表是一个独立的逻辑表，但是底层由多个物理子表组成。
当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。
分区表类型
CREATE TABLE test_range_partition(
id INT auto_increment,
createdate DATETIME,
primary key (id,createdate)
)
PARTITION BY RANGE (TO_DAYS(createdate) ) (
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
);
create table test_list_partiotion
(
id int auto_increment,
data_type tinyint,
primary key(id,data_type)
)partition by list(data_type)
(
partition p0 values in (0,1,2,3,4,5,6),
partition p1 values in (7,8,9,10,11,12),
partition p2 values in (13,14,15,16,17)
);
create table test_hash_partiotion
(
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by hash(year(create_date)) partitions 10;
分区的问题
例子：
当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，
这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，
会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、
LOAD DATA INFILE和一次删除多行数据
例子：
重组分区，会先创建一个临时分区，
然后将数据复制到其中，最后再删除原分区。
共享锁
例子：我们进入洗手间只是想洗手的话，我们一般不会锁门。而其他人也可以进来洗手、化妆等。但是，其他人是不可以进来上厕所的。这就是共享锁，也叫读锁。
就是只读不写。
用法
排他锁
用法
加锁原则
拿 MySql 的 InnoDB 引擎来说，对于 insert、update、delete 等操作。会自动给涉及的数据加排他锁；
对于一般的 select 语句，InnoDB 不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。
共享锁：SELECT ... LOCK IN SHARE MODE;
排他锁：SELECT ... FOR UPDATE;
事务特性 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
select a.* from A awhere exists(select 1 from B b where a.id=b.id)
select * from Awhere id in(select id from B)
相同
不同
MySQL 日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。 其中比较重要的是
bin log
redo log
undo log
DQL（Data Query Language）数据查询语言 DQL 由 SELECT 子句，FROM 子句，WHERE 子句组成
DML（Data Manipulation Language）数据操纵语言 DML 包含 INSERT，UPDATE，DELETE
DDL（Data Definition Language）数据定义语言 DDL 用来创建数据库中的各种对象 ----- 表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER DDL 操作是隐性提交的！不能 rollback
DCL（Data Control Language）数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括 GRANT、DENY、REVOKE 等语句，在默认状态下，只有 sysadmin、dbcreator、db_owner 或 db_securityadmin 等角色的成员才有权利执行数据控制语言。
查询语句的执行流程如下： 权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
举个例子，查询语句如下：
select * from user where id > 1 and name = '温大大';
更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare 状态）、binlog、redo log（commit 状态）
举个例子，更新语句如下：
update user set name = '温大大' where id = 1;
MySQL 主要分为
Server 层：
存储引擎：
主要负责数据的存储和读取。server 层通过 api 与存储引擎进行通信。 Server 层基本组件
连接器： 当客户端连接 MySQL 时，server 层会对其进行身份认证和权限校验。
查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
优化器： 优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。
简介
MyISAM 基于 ISAM 存储引擎，并对其进行扩展。它是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
MyISAM 拥有较高的插入、查询速度，但不支持事务。
MyISAM 表格可以被压缩，而且它们支持全文搜索。不支持事务，而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。在进行 updata 时进行表锁，并发量相对较小。如果执行大量的 SELECT，MyISAM 是更好的选择。
MyISAM 的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而 Innodb 是索引和数据是紧密捆绑的，没有使用压缩从而会造成 Innodb 比 MyISAM 体积庞大。
MyISAM 缓存在内存的是索引，不是数据。而 InnoDB 缓存在内存的是数据，相对来说，服务器内存越大，InnoDB 发挥的优势越大。
特性
简介
InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB 是默认的 MySQL 引擎。
InnoDB 采用 MVCC（多版本并发控制）来支持高并发，并实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁是的 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB 表是基于聚簇索引建立的。InnoDB 的索引结构和 MySQL 的其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（secondary index，非主键索引）中必须包含主键列，所以如果主键很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。
InnoDB 不创建目录，使用 InnoDB 时，MySQL 将在 MySQL 数据目录下创建一个名为 ibdata1 的 10MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件。
特性
简介
特性
简介
MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。
说白了，Merge 表就是几个相同 MyISAM 表的聚合器；Merge 表中并没有数据，对 Merge 类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的 MyISAM 表进行操作。
主要应用于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用 12 个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有 12 个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除 Merge 表，而不影响原来的数据，删除 Merge 表只是删除 Merge 表的定义，对内部的表没有任何影响。
特性
MERGE 数据表可以用来创建一个尺寸超过各个 MyISAM 数据表所允许的最大长度逻辑单元
你看一把经过压缩的数据表包括到 MERGE 数据表里。比如说，在某一年结束之后，你应该不会再往相应的日志文件里添加记录，所以你可以用 myisampack 工具压缩它以节省空间，而 MERGE 数据表仍可以像往常那样工作
MERGE 数据表也支持 DELETE 和 UPDATE 操作。INSERT 操作比较麻烦，因为 MySQL 需要知道应该把新数据行插入到哪一个成员表里去。在 MERGE 数据表的定义里可以包括一个 INSERT_METHOD 选项，这个选项的可取值是 NO、FIRST、LAST，他们的含义依次是 INSERT 操作是被禁止的、新数据行将被插入到现在 UNION 选项里列出的第一个数据表或最后一个数据表。
简介
不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：
后续：最近 3-4 月份面试的人挺多的，如果你也想抓住这次涨薪的机会， 关注我，加我好友拉你进面试群，一起讨论面试干货 / 套路， 大家一起升职加薪，关注公众号：测试猿温大大