【面试-八股文】SQL 万字总结，助你吊打面试官

亲爱的小伙伴们，为了能及时收到文章的推送，可以点击上方蓝字关注测试猿温大大，并设为星标就可以第一时间收到推送哦！

感谢您抽出

.

.

阅读本文

大家好，我是温大大

前段时间大家在面试过程中，经常被问到数据库相关的问题。

像：sql怎么优化，解释下数据库常见锁的，having 和 where区别等等。

所以温大大爆肝1天2夜。

肝了「万字」从数据库基础知识、到数据索引、索、事务 以及 面试高频面试题。

包你从sql入门到入土。

建议可以先收藏，然后遇到有不会了查看目录，直接跳到该目录进行查阅。

目录：

基础

索引

锁

事务

关键词

MySQL 底层原理

引擎

0 关联0.0 数据准备

表创建

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');

0.1 关联

内连接（inner join）

外连接

全连接（full join）

联合(union / union all）

select * from A   
union
select * from B

0.2 数据库的三大范式

第1范式

第2范式

第3范式

2NF和3NF的区别

1 索引1.1 什么是索引

索引是存储引擎用于提高数据库表的访问速度的一种「数据结构」。

1.2 索引的优缺点

优点：

缺点：

1.3 索引的作用？1.4 索引的数据结构

B+树索引

哈希索引

Hash索引和B+树索引的区别？

为什么B+树比B树更适合实现数据库索引？

1.5 索引的分类1.6 索引的设计原则

设计原则

1.7 索引的失效原则

导致索引失效的情况

1.8 哪些场景 能 建立索引1.9 哪些场景 不能 建立索引1.10 什么是最左匹配原则？

最左匹配原则

1.11 什么是聚集索引？1.12 什么是覆盖索引？

explain select blog_id from user_like where user_id = 13;

explain select user_id from user_like where blog_id = 1;

1.13 什么是前缀索引？

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

1.14 什么是分库分表？

原因：索引不能提升性能时，引入分库分表

垂直划分

水平划分

1.15 什么是分区表？

分区表类型

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和一次删除多行数据

例子：
重组分区，会先创建一个临时分区，
然后将数据复制到其中，最后再删除原分区。

2 锁2.1 共享锁和排他锁是什么

共享锁

用法

排他锁

用法

加锁原则

事务3.1 事务四大特性

事务特性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

3.2 事务隔离级别有哪些

问题

区别

事务隔离就是为了解决上面的问题

4 关键词4.1 having 和 where区别？4.2 exist 和 in的区别？

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)

4.3 truncate、delete与drop区别？

相同

不同

4.4 bin log/redo log/undo log 有什么区别？

MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是

bin log

redo log

undo log

4.5 int(10)和char(10)的区别？4.6 preparedStatement和statement的区别？4.7 union 和union all的区别？4.8 数据库查询语言 DQL/DML/DCL 区别？5 mysql执行原理5.1 查询执行流程

查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查询语句如下：

select * from user where id > 1 and name = '温大大';

5.2 更新执行流程

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）

举个例子，更新语句如下：

update user set name = '温大大' where id = 1;

5.3 MySQL架构

MySQL主要分为

Server 层：

存储引擎：

6 引擎6.1 MyISAM

简介

特性

6.2 InnoDB

简介

特性

6.3 MEMORY

简介

特性

6.4 MERGE

简介

特性

6.5 Archive

简介

6.6 引擎选择

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：

后续：最近3-4月份面试的人挺多的，如果你也想抓住这次涨薪的机会

关注我，加我好友拉你进面试群重做系统 逻辑分区会没有嘛，一起讨论面试干货 / 套路 / 刷面试题重做系统 逻辑分区会没有嘛，大家一起升职加薪