作者：我没有三颗心脏
链接：https://www.jianshu.com/p/71927a377dc6
来源：简书
1. left join 与 inner join的区别
sql的left join 、right join 、inner join之间的区别
- left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
- right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
- inner join(等值连接) 只返回两个表中联结字段相等的行
left join sql语句如下:
select * from Aleft join B on A.aid = B.bid
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aid = B.bid)。B表不符合查询要求的地方均为NULL。
right join sql语句如下:
select * from A right join B on A.aid = B.bid
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不符合连接要求的地方用NULL填充.
inner join sql语句如下:
select * from A inner join B on A.aid = B.bid
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录。不符合要求的则舍去。
2.事务的简单理解
事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败；作为单个逻辑工作单元执行的一系列操作，满足四大特性：
- 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行
- 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行
- 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存
并发性问题:
- 丢失更新：一个事务的更新覆盖了另一个事务的更新；
- 脏读：一个事务读取了另一个事务未提交的数据；
- 不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
- 幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。
3.事务隔离级别
隔离级别决定了一个session中的事务可能对另一个session中的事务的影响。ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是：
- 读未提交（READ UNCOMMITTED）：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没 commit 的数据，这样可能会提高性能，但是会导致脏读问题；
- 读已提交（READ COMMITTED）：在一个事务中只允许对其它事务已经 commit 的记录可见，该隔离级别不能避免不可重复读问题；
- 可重复读（REPEATABLE READ）：在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务 commit 或 rollback。但是，其他事务的 insert/delete 操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复 select 的结果一样，除非本事务中 update 数据库。
- 序列化（SERIALIZABLE）：最高级别的隔离，只允许事务串行执行。
MySQL默认的隔离级别是可重复读（REPEATABLE READ)
MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：
- MyISAM：不支持事务，用于只读程序提高性能；
- InnoDB：支持ACID事务、行级锁、并发；
- Berkeley DB：支持事务。
4.事务提交模式
- 自动提交模式
默认方式，每执行完一条SQL语句就自动提交事务，每条SQL语句的执行都被单独提交，因此一个事务只有一条SQL语句组成。 - 手动提交模式
通常一个新的数据库连接产生，便意味着一个新的事务生成。我们要将一个整体操作设置为一个事务，而不是一条SQL语句当成一个事务。
Conncetion.setAutoCommit(false)：设置为手动提交模式
Connection.commit()：提交事务
Connection.rollback()：回滚事务
一个事务中可以包含若干条SQL语句
// 开启事务,对数据的操作就不会立即生效。
connection.setAutoCommit(false);
// A账户减去500块
String sql = "UPDATE a SET money=money-500 ";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
// 在转账过程中出现问题
int a = 3 / 0;
// B账户多500块
String sql2 = "UPDATE b SET money=money+500";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
// 如果程序能执行到这里，没有抛出异常，我们就提交数据
connection.commit();
// 关闭事务【自动提交】
connection.setAutoCommit(true);
} catch(SQLException e) {
try {
// 如果出现了异常，就会进到这里来，我们就把事务回滚【将数据变成原来那样】
connection.rollback();
// 关闭事务【自动提交】
connection.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
}
}
5.分布式事务其中一个事务出错
XA模式
6.三大范式
- 第一范式： 列不可分
1NF（第一范式）是对属性具有原子性的要求，不可再分 - 第二范式： 消除非主属性对码的部分函数依赖
2NF（第二范式）是对记录有唯一性的要求，即实体的唯一性，不存在部分依赖，每一列与主键都相关 - 第三范式： 消除非主属性对码的传递函数依赖
3NF（第三范式）对字段有冗余性的要求，任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在依赖传递
7.索引
- 什么是索引
索引会影响到where后面的查找和order by后面的排序 - 聚集索引和非聚集索引
- 非聚集索引:字典的检索或者书的目录(数据与索引分开)
- 聚集索引:页码下面的数字(页码在需要查找的数据的里面)
- 底层数据结构是什么，为什么使用这种数据结构？
MyIsam使用的是B+tree(多路查找树)索引，既有索引列的值也有指向数据行的指针，使用二分法查找，时间复杂度为O(logn),构建出的索引树如图(非聚集索引)
MyIsam在物理上有三个文件组成:
- MYI:存储索引
- MYD:存储数据
-
FRM:表结构
因此MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
image.png
image.png
InnoDB使用的是B+tree做的索引。InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
InnoDB的数据文件本身要按主键聚集(只有一个聚集索引)，所以InnoDB要求表必须有主键。默认会拿主键(id)作为聚集索引。如果没有主键会取非空唯一的作为聚集索引。如果都没有InnoDB会自己维护一个唯一id来作为聚集索引。
InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
- 索引的优缺点,建索引的原则
优点: 提高检索速度，降低磁盘的IO次数
缺点: 索引需要存储，需要空间，索引实际上是一张表，字段更新会有性能损耗
适合:
- 频繁作为where条件的字段(where id = ?)
- 关联字段可以建索引,例如外键字段
- Order by 的 column，Group by 的 column(排序)
不适合:
- Where条件中用不到的字段
- 频繁更新的字段
- 数据值分布均匀(男,女,真假值)
- 表数量可以确定行数,且数据量不是很大
原则:
一个最重要的原则是最左前缀原理，在提这个之前要先说下联合索引，MySQL中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列。另外，单列索引可以看成联合索引元素数为1的特例。
- 那么查询的时候，如果查询【A】【A，B】 【A，B，C】，那么可以通过索引查询
- 如果查询的时候，采用【A，C】，那么C这个虽然是索引，但是由于中间缺失了B，因此C这个索引是用不到的，只能用到A索引
- 如果查询的时候，采用【B】 【B，C】 【C】，由于没有用到第一列索引，不是最左前缀，那么后面的索引也是用不到了
- 如果查询的时候，采用范围查询，并且是最左前缀，也就是第一列索引，那么可以用到索引，但是范围后面的列无法用到索引
索引失效:
Select * from user order by age 索引生效
Index(name,age) 复合索引(建索引最好用复合索引)
Select * from user oder by age 索引失效
因为符合索引由name,age组成前面部分是name,不能跨越name直接到age
Select * from user oder by name,age 索引生效
Select * from user where age = 18 and name = 'zsj'索引失效
Index(age,name) 复合索引
Select * from user where age>18 and name = 'zsj' age用上索引后面失效
- 行锁和表锁
在执行sql语句,如
update table set c1 = ? where c2 = ? 会检索出一行或者几行数据
在InnoDB中检索出的这几行会被锁住,此刻其他线程只能等待,提交后其他线程才能访问(行锁)
8.什么是视图?使用视图的场景
视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
- 不希望访问者获取整个表的信息，只暴露部分字段给访问者，所以就建一个虚表，就是视图。
- 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样也可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表所带来的差异。
注意：这个视图是在数据库中创建的 而不是用代码创建的。
9.DDL与DML
DML数据操纵语言：就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作。
DDL数据库定义语言：其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上。
truncate table：
属于DDL（Data Definition Language，数据库定义语言）
不可回滚
不可带 where
表内容删除
删除速度快
保留表而删除所有数据的时候用truncate
10.数据库悲观锁和乐观锁
数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
-
MySQL InnoDB中使用悲观锁：
要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。 set autocommit=0;
//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;
上面的查询语句中，我们使用了 select…for update 的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。
上面我们提到，使用 select…for update 会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。
优点与不足：
悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数
2.乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
乐观锁是一种不会阻塞其他线程并发的控制，它不会使用数据库的锁进行实现，它的设计里面由于不阻塞其他线程，所以并不会引起线程频繁挂起和恢复，这样便能够提高并发能力，所以也有人把它称为非阻塞锁。一般的实现乐观锁的方式就是记录数据版本。
数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。
实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。
优点与不足：
乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。
11.SQL约束有几种
- NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
- UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
- PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
- FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
- CHECK: 用于控制字段的值范围。
12.MySQL存储引擎中的MyISAM和InnoDB区别详解
在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。虽然MyISAM性能极佳，但却有一个显著的缺点：不支持事务处理。不过，MySQL也导入了另一种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM。
InnoDB是MySQL的数据库引擎之一，其由Innobase oy公司所开发，2006年五月由甲骨文公司并购。与传统的ISAM、MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能，类似于PostgreSQL。目前InnoDB采用双轨制授权，一是GPL授权，另一是专有软件授权。具体地，MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括：
13.varchar和char的区别
-
char是一种固定长度的类型，varchar是一种可变长度的类型，
例如：
定义一个char[10]和varchar[10]，如果存进去的是 'test'，那么char所占的长度依然为10，除了字符 'test' 外，后面跟六个空格，varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的 -
char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储于查找
char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率。 - varchar是以空间效率为首位。
char的存储方式是：对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节。
varchar的存储方式是：对每个英文字符占用2个字节，汉字也占用2个字节。
两者的存储数据都非unicode的字符数据。
14. IN 和 EXISTS 的区别及应用
select * from Awhere id in(select id from B)
以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.它的查询过程类似于以下过程
List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}return resultSet;
可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.
如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历100001000000次,效率很差.
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000100次,遍历次数大大减少,效率大大提升.结论:in()适合B表比A表数据小的情况
select a.* from A a where exists(select 1 from B b where a.id=b.id)
以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.
它的查询过程类似于以下过程
List resultSet=[];
Array A=(select * from A)
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) {
执行select 1 from B b where b.id=a.id是否有记录返回
resultSet.add(A[i]);
}
}return resultSet;
当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行。
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.
结论:exists()适合B表比A表数据大的情况当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.
15.优化MySQL
- 通过explain查询和分析SQL的执行计划：
使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay，则往往表示SQL需要优化了。
多数来源：
简书：我没有三颗心脏
掘金：吴德宝AllenWu MySQL索引和SQL调优
