SQL

基本语句

select * from 表名;
update 表名 set 字段名 = 新值 where 字段名 = value;
delete from 表名 where 字段名 = value;
insert into 表名(字段名1, 字段名2, ....) value(值1, 值2, ....);

数据库分页语法

1 2	select * from student limit 5; # 从第一行开始返回前5行记录 select * from student limit 5, 5; # 从第5行开始，返回5行记录

Sql中的聚合函数

常用的聚合函数有COUNT(), AVG(), SUM(), MAX(), MIN()

`COUNT()`函数

count()函数统计表中所包含的记录的行数，或者根据查询结果返回列中包含的数据行数

count(*)计算表中总的行数，不管其列是否有数值或者为空值。
count(字段名)计算指定列下总的行数，计算时将忽略空值的行。
count函数可以和group by一起使用来计算每个分组的和。

1	select count(name) from student;

`avg()`函数

avg()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。

avg函数可以与group by一起使用，来计算每个分组的平均值。

1	select avg(age) from student;

`sum()`函数

sum()函数是求总和的函数，返回指定列值的总和

sum可以与group by一起使用，计算每个分组的总和。

1	select sum(age) from student;

`max()`函数

max()返回指定列的最大值

MAX()也可以和GROUP BY关键字一起使用，求每个分组中的最大值。
MAX()函数不仅适用于查找数值类型，也可应用于字符类型。

1	select max(age) from student;

`min()`函数

min()返回指定列的最小值

MIN()也可以和GROUP BY关键字一起使用，求出每个分组中的最小值。
MIN()函数与MAX()函数类似，不仅适用于查找数值类型，也可应用于字符类型。

1	select min(age) from student;

表和表是怎么关联的

常用的关联方式有两种：内连接，外连接。

内连接

内连接通过inner join来实现，他将返回两张表中满足连接条件的数据，不满足条件的数据不会查询出来。

1	select * from student inner join user on student.user_id = user.id;

内连接特点：只会把满足连接条件的查询结果返回，不满足的则不会返回。

外连接

外连接根据其查询特点又分为：左外连接左表 left [outer] join 右表 on 连接条件和右外连接左表 right [outer] join 右表 on 连接条件。

左外连接

简称左连接，会返回左表中所有的记录和右表中满足连接条件的记录。

1	select * from student left join user on student.user_id = user.id;

右外连接

简称右连接，会返回右表中所有的记录和左表中满足连接条件的记录。

1	select * from student right join user on student.user_id = user.id;

将一张表的部分数据更新到另一张表

可以采用关联更新的方式，将一张表的部分数据，更新到另一张表内。

1
2
3

update b set b.col = a.col from a, b where a.id = b.id;
update b set col = a.col from b inner join a on a.id = b.id;
update b set b.col = a.col from b left join a on b.id = a.id;

where 和 having的区别

where用于过滤数据行，having用于过滤分组。
where查询条件中不可以使用聚合函数，having查询条件中可以使用聚合函数。
where在数据分组前进行过滤，having在数据分组后进行过滤。
where针对数据库文件进行过滤，having针对查询结果进行过滤。也就是说，where根据数据表中的字段直接进行过滤。而having是根据前面已经查询出的字段进行过滤。
where查询条件中不可以使用字段别名。而having可以使用字段别名。

注意：having和where可以同时使用。但是产生作用的时间不同。可以看上面第三条。

示例：

select name, sex, age from student where age > 20;
select name, sex, age a from student where a > 20; # 可以使用别名，和上一条查询效果一致。

select name, sex from student where age > 20;  # 正确。
select name, sex from student having age > 20; # 报错。having是根据查询结果进行过滤，查询结果中没有age字段。

使用having查询的select语句执行顺序

where（数据查询） —> group by（分组查询） —> having（结果过滤） —> order by（排序）

索引

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

MySQL中索引的存储类型有两种：即BTREE和HASH

索引的优点

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以显著增加数据的查询速度。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

索引的缺点

索引的分类

普通索引和唯一索引unique

普通索引时Mysql中的基本索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引是一种特殊的唯一索引，不允许有空值。

单列索引和组合索引

单列即一个索引只包含单个列，一个表可以有多个单列索引。

组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

全文索引fulltext

在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

空间索引spatial

索引的创建及保存

mysql支持多种方法在单列或多个列上创建索引

创建表时创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

1	create table 表名 ([字段名字段类型], [unique\|fulltext\|spatial] [index\|key] [索引名] (字段名[length]) [ASC\|DESC])

length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引的长度.
ASC或DESC指定升序或降序的索引值存储.

例如:

1	create table t1(id int not null, name char(30) not null, unique index UniqueIdx(id));

在已存在的表上创建索引

在已经存在的表中创建索引可以使用alter table语句或者create...Index语句.

alter table

1	alter table 表名 add [unique\|fulltext\|spatial] [index\|key] [索引名] (字段名[length]) [ASC\|DESC]

例如:

1	alter table student add unique index UniqueIdx (id);

create ... index

1	create [unique\|fulltext\|spatial] [index\|key] [索引名] on 表名 (字段名[length]);

例如:

1	create unique index UniqueIdx on student (id);

MySql什么时候需要加索引

当唯一性是某个字段数据本身的特征时,需要指定唯一索引,使用唯一索引需能保证定义的列的数据完整性,以提高查询速度.
在频翻进行排序或者分组的列(即进行group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引.

只要创建了索引，就一定会使用索引吗？

答案是不一定。

在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。

举例，假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放，索引可以搜索id、（id,name）、（id, name, age）字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用该索引查询。

怎么判断索引有没有生效

可以使用explain语句查看索引是否正在使用。

例如：

1	explain select * from student where id = 2;

索引的设计原则

避免对经常更新的表进行过多的索引，并且索引中的列要尽可能的少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。

比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

索引越多越好？

索引并不是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，还会影响insert，delete，update等语句的性能，因为在表中的数据更改时，索引也会进行调整。

哪些字段不适合创建索引

频繁更新的字段不适合。
where条件中用不到的字段不适合
数据比较少的表不适合。
数据重复且分布均匀的字段不适合，例如性别，真假值
参与计算的列不适合。

索引的实现原理⭐

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM

MyISAM引擎中使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址。

InnoDB

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。区别如下：

InnoDB的数据文件本身就是索引文件。

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

主键索引

辅助索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

主键索引

辅助索引

数据库索引的重构过程

重建索引的时间

表上频繁发生update，delete操作。
表上发生了alter table…move操作。move操作导致了rowid变化。

怎么判断索引是否应该重建

一般看索引是否倾斜严重，是否浪费了空间，对索引结构进行结构分析。
1
analyze index index_name validate structure;
在相同的session中查询index_stats表：
1
select height, DEL_LF_ROWS/LF_ROWS from index_stats;
当查询的height>=4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2的情况下，就应该考虑重建该索引。

如何重建索引

drop原索引，然后再创建索引

1 2	drop index index_name; create index index_name on table_name (字段名);

直接重建索引

1 2	alter index index_name rebuild; alter index index_name rebuild online;

Mysql的索引为什么用B+树

B+树由B树和索引顺序访问方法演化而来。它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树种，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进行连接。

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

拓展知识⭐

2-3树就是3阶的B树。B树可以是n阶的。

B树

B树的阶：节点的最多子节点个数。比如2-3树的阶是3，2-3-4树的阶是4.
B树的搜索：从根节点开始，对节点内的关键字（有序）序列进行二分查找。如果命中则结束，否则进入查询关键字所属范围的子节点；重复，直到所对应的儿子指针为空或已经是叶子节点。
关键字集合分布在整棵树中，即叶子节点和非叶子节点都存放数据。
搜索有可能在非叶子节点结束。
其搜索性能等价于在关键字全集内做一次二分查找。

B+树

B+树是B树的变体，也是一种多路搜索树。

B+树的搜索与B树也基本相同，区别是B+树只有达到叶子节点才命中（B树可以在非叶子节点命中）。其性能也等价于在关键字全集做了一次二分查找。
所有关键字都出现在叶子节点的链表中。（即数据只能在叶子节点【也叫稠密索引】），且链表中的关键字（数据）恰好是有序的。
不可能在非叶子节点中命中。
非叶子节点相当于是叶子节点的索引（稀疏索引），叶子节点相当于是存储（关键字）数据的数据层。
更适合文件索引系统。
B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

B*树

B*树是B+树的变体，在B+树的非根和非叶子节点再增加指向兄弟的指针。

B树定义了非叶子节点关键字个数至少为`(2 / 3) M`，即块的最低使用率为2/3，而B+树的块的最低使用率为B*树的1/2；
从第一个特点我们可以看出，B*树分配新节点的概率要比B+树要低，空间使用率更高。

联合索引的存储结构

从本质上来说，联合索引还是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2，参考下图。

只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，所以使用联合索引时遵循最左前缀集合。

select in语句中如何使用索引

索引是否起作用，主要取决于字段类型：

如果字段类型为字符串，需要给in查询中的数值与字符串值都需要添加引号，索引才能起作用。
如果字段类型为int，则in查询中的值不需要添加引号，索引也会起作用。

IN的字段，在联合索引中，按以上方法，也会起作用。

select in语句

in常用于where表达式中，其作用是查询某个范围内的数据

1 2	select * from student where id in (1, 2, 3); select * from student where id not in (1, 2, 3);

in子查询，in列表项的值是不明确的，而可能是通过一个子查询得到的。

1	select * from student where use_id in (select id as use_id from user where status = 0);

模糊查询语句如何使用索引

mobile like '%5678'不能使用索引，如果需要根据手机号后四位进行模糊查询，可以进行如下改造

mobile_reverse like reverse('%5678')。加入了冗余列mobile_reverse。为 mobile_reverse 列建立索引，

模糊查询like

1	select * from student where name = "谢%";

事务

事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成，在事务中的操作，要么都不执行，要么都执行修改，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

只有DML语句（insert，update，delete）才会有事务这么一说，其他语句和事务无关！！！

事务需要遵循的四大特性

事务需要遵循ACID四个特性。

A（atomicity）：原子性，是指整个数据库事务是不可分割的工作单位。只有数据库事务中所有数据库操作都执行成功，整个事务才算执行成功。
C（consistency）：一致性，是指事务讲数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束之后，数据库的完整性约束并没有被破坏。
I（isolation）：隔离性：事务的隔离性要求每个读写事务的对象与其他事务的操作对象都能相互分离，即该事务提交前对其他事务不可见。通常使用锁来实现。
D（durability）：持久性，事务一旦提交，其结果就是永久性的，即使发生宕机等故障，数据库也能将数据恢复。保证了事务系统的高可靠性，而不是高可用性。

Mysql的事务隔离级别

读未提交（read uncommitted）

是事务之间最小限度的隔离，除了容易产生幻读和不能重复的读操作外，处于这个隔离级别的事务可以读到其他事务还没有提交的数据（脏读）。

读提交（read committed）

处于此级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一事务的多个select语句可能返回不同的结果。

可重复读（repeatable read）

当前正在执行事务的变化不能被外部看到。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一事务的多个select语句返回相同的结果

序列化（serializable）

如果隔离级别为序列化，则用户之间通过一个接一个顺序的执行当前的事务，这种隔离级别提供了事务之间最大限度地隔离。

隔离级别	脏读	不可重复读	幻读
读未提交RU	可能	可能	可能
读提交RC	不可能	可能	可能
可重复读RR	不可能	不可能	可能
序列化serializable	不可能	不可能	不可能

脏读

脏读又称无效数据读出，一个事务读取了另外一个事务还没有提交的数据。

例如：事务A中修改了表中某一行数据，但是还没有提交，此时事务B读取了事务A修改后的数据，之后事务A因为某种原因回滚了，则事务B读取到的数据就是脏数据。

事务A	事务B
`update student set age = 21 where id = 1;`
	`select * from student where id = 1;`
Roll back; commit;

不可重复读

不可重复读是指在同一个事务中，两次相同的查询返回了不同的结果。

例如：事务A会读取两次数据，在第一次读取数据之后，事务B修改了该数据并提交，则事务A第二次读取数据时，得到了和第一次读取不一样的结果。

幻读

幻读是指同样的查询在整个事务过程中多次执行后，查询所得到的结果集是不一样。

幻读也是指当事务不独立执行时，插入或删除另一个事务当前影响的数据而发生的一种类似幻觉的现象。

注意：不可重复读的重点是针对update，delete。而幻读的重点针对的是insert。

例如：事务A将数据库中所有数据都删除的时候，但是事务B就在这个时候新插入了一条记录，当事务A删除结束后发现还有一条数据，就像发生了幻觉一样。这就叫幻读。

设置&查看事务的隔离级别

-- 设置全局的隔离级别为read uncommitted
set global transaction isolation level read uncommitted;

-- 查看隔离级别
SELECT @@tx_isolation

Mysql的事务隔离级别

InnoDB支持四种隔离级别：

隔离级别	脏读	不可重复读	幻读
读未提交RU	可能	可能	可能
读提交RC	不可能	可能	可能
可重复读RR	不可能	不可能	不可能
序列化serializable	不可能	不可能	不可能

每种隔离级别的实现机制

RU & RC：

通过record lock算法实现了行锁，但RU允许读取未提交的数据，所以存在脏读问题。RC允许读取提交数据，所以不存在脏读问题，但存在不可重复读的问题。

使用next-key lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

serializable

对每个select语句后自动加上lock in share mode，即为每个读取操作加上一个共享锁，因此在这个事务的隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

如何实现可重复读&如何解决幻读问题

MySQL的InnoDB引擎，在默认的REPEATABLE READ的隔离级别下，实现了可重复读，同时也解决了幻读问题。它使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。

锁

什么是间隙锁

InnoDB存储引擎有三种行锁的算法，间隙锁（Gap lock）是其中之一，间隙锁用于锁定一个范围，但不包含记录本身，他的作用是为了阻止多个事务将记录插入到同一范围内，而这会解决幻读问题。

数据库在什么情况下会发生死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种相互等待的现象。若无外力作用，事务都将无法推进下去。

时间	会话A	会话B
1	begin;
2	select * from t where a = 1 for update;	begin;
3		select * from t where a = 1 for update;
4	select * from t where a = 2 for update;
5		select * from t where a = 1 for update; 会报错：deadlocak found when trying to get lock

拓展阅读

select ... for update在查询结束后，还要进行后续修改。因此在事务中会加锁。

解决死锁的办法

超时：当两个事务互相等待是，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚。另一个等待的事务就能继续进行。
InnoDB存储引擎采用了wait-for graph（等待图）的方式来进行死锁检测。是一种更主动的死锁检测方式。等待图要求数据库保存以下两种信息：
- 锁的信息链表
- 事务等待链表
通过上述链表可以构造出一张图，若图中存在回路，就代表存在死锁。因此资源间相互发生等待。若存在死锁，InnoDB存储引擎选择回滚undo量最小的事务。

优化

如何优化MySql的查询

使用索引

如果查询时没有使用索引，查询语句将扫描表中的所有记录，在数据量大的情况下，查询的速度会很慢。使用索引进行查询，可以根据索引快速定位到待查询记录，从而减少查询的记录数（相当于二分查找）。从而提高查询速度。

但是有的情况是不使用索引的或者说索引不起作用的。

使用like关键字的查询语句，当匹配字符串的第一个字符是%时，不使用索引，所以当%不在第一个位置时，才会使用索引。
使用多列索引查询语句。多列索引最多可以包含16个字段。只有当查询条件中使用了这些字段中的第一个字段时索引才会被使用。
使用or关键字的查询语句，只有当or前后的两个条件中的列都是索引时，查询中才会使用索引。

优化子查询

使用子查询可以进行select语句的嵌套查询。即一个select的查询结果作为另一个select语句的条件。在这个过程中，mysql会为内层查询语句查询结果建立一个临时表。所以执行效率并不高。

可以使用join连接代替子查询，连接查询不需要建立临时表，其速度比子查询要快。如果查询中有索引，性能会更好。

优化Mysql的插入

影响插入速度的主要是索引，唯一性校验，一次插入记录条数等。

对于MyISAM引擎的表，常见的优化方法如下：

禁用索引。可以在插入记录之前禁用索引，在插入完成后开启索引。
禁用唯一性检查：同样在插入之前禁用唯一性检查，插入完成后再开启。
使用批量插入：用一条INSERT语句插入多条记录的情形如下，而这种方式的插入速度更快。
1
insert into student values(1, 'xzt', 18), (2, 'qqy', 18);
使用load data批量导入：LOAD DATA INFILE语句导入数据的速度比INSERT语句快。
1

对于InnoDB引擎的表，常见的优化方法如下：

禁用唯一性检查
禁用外键检查
禁用自动提交：插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

其他

数据库设计的三大范式

目前关系型数据库有六种范式，一般来说，数据库只需要满足第三范式就行。

第一范式（1NF）：第一范式就是无重复的域。

所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）。

第二范式要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

第三范式是第二范式的一个子集，即满足第三范式必须满足第二范式。简而言之，第三范式要求一个关系中不包含已在其它关系已包含的非主关键字信息。

SQL

基本语句

数据库分页语法

Sql中的聚合函数

COUNT()函数

avg()函数

sum()函数

max()函数

min()函数

表和表是怎么关联的

内连接

外连接

左外连接

右外连接

将一张表的部分数据更新到另一张表

where 和 having的区别

使用having查询的select语句执行顺序

索引

索引的优点

索引的缺点

索引的分类

索引的创建及保存

创建表时创建索引

在已存在的表上创建索引

MySql什么时候需要加索引

只要创建了索引，就一定会使用索引吗？

怎么判断索引有没有生效

索引的设计原则

索引越多越好？

哪些字段不适合创建索引

索引的实现原理⭐

MyISAM

InnoDB

数据库索引的重构过程

重建索引的时间

怎么判断索引是否应该重建

如何重建索引

Mysql的索引为什么用B+树

拓展知识⭐

B树

B+树

B*树

联合索引的存储结构

select in语句中如何使用索引

select in语句

模糊查询语句如何使用索引

模糊查询like

事务

事务需要遵循的四大特性

Mysql的事务隔离级别

脏读

不可重复读

幻读

设置&查看事务的隔离级别

Mysql的事务隔离级别

每种隔离级别的实现机制

如何实现可重复读&如何解决幻读问题

锁

什么是间隙锁

数据库在什么情况下会发生死锁

拓展阅读

解决死锁的办法

优化

如何优化MySql的查询

使用索引

优化子查询

优化Mysql的插入

其他

数据库设计的三大范式

`COUNT()`函数

`avg()`函数

`sum()`函数

`max()`函数

`min()`函数