mysql

1、第一范式、第二范式、第三范式

第一范式：表中的字段都是单一属性的，不可再分。

第二范式：消除了非主属性对码的部分函数依赖

函数依赖
我们可以这么理解（但并不是特别严格的定义）：若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y。 eg：学号 → 系主任

完全函数依赖
在一张表中，若 X → Y，且对于 X 的任何一个真子集（假如属性组 X 包含超过一个属性的话），X ’ → Y 不成立，那么我们称 Y 对于 X 完全函数依赖

记作 X F→ Y。（那个F应该写在箭头的正上方） eg：（学号，课名） F→ 分数

部分函数依赖
假如 Y 函数依赖于 X，但同时 Y 并不完全函数依赖于 X，那么我们就称 Y 部分函数依赖于 X，记作 X P→ Y。 eg：（学号，课名） P→ 姓名

传递函数依赖
假如 Z 函数依赖于 Y，且 Y 函数依赖于 X （严格来说还有一个X 不包含于Y，且 Y 不函数依赖于Z的前提条件），那么我们就称 Z 传递函数依赖于 X ，记作 X T→ Z。

码
设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（这个“完全”不要漏了），那么我们称 K 为候选码，简称为码。在实际中我们通常可以理解为：假如当 K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定，那么 K 就是码。一张表中可以有超过一个码。（实际应用中为了方便，通常选择其中的一个码作为主码）

主属性

包含在任意一个码中的属性称为主属性。

非主属性
不包含在任何一个码中的属性称为非主属性。

2NF可以减少插入异常，删除异常和修改异常。

第三范式：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。

eg：对于学生表，主码为学号，主属性为学号，非主属性为姓名、系名和系主任。因为 学号 → 系名，同时 系名 → 系主任，所以存在非主属性系主任对于码学号的传递函数依赖，所以学生表的设计，不符合3NF的要求。

2、事务

事务：事务是一组操作的集合，它是一个不可分割的工作单位，这些操作要么同时成功，要么同时失败。

2.1、事务的特性

数据库事务特性：原子性(Atomic)、一致性(Consistency)、隔离性(Isolation)、持久性 (Durabiliy)。简称ACID

• 原子性：组成一个事务的多个操作是一个不可分割的原子单元，只有所有操作都成功， 整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。
• 一致性：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰
• 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

2.2、并发事务带来哪些问题?

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对 同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提 交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是 还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可 能是不正确的。 （一个事务读到另外一个事务还没有提交的数据。）

• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结 束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务 的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。 （一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。）

• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会 发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。 （一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 “幻影”。）

• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也 读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。 （两个事务读取修改一个数据，第一个事务修改的结果就会丢失）

  不可重复读和幻读区别：

  不可重复读的重点是修改 比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增 或者删除 比如多次读取一条记录发现记录增多或减少了。

2.3、事务隔离级别有哪些？MySQL的默认隔离级别是？

SQL 标准定义了四个隔离级别： （注意：事务隔离级别越高，数据越安全，但是性能越低。）

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是 幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身 事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐 个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读 以及幻读。

隔离级别	脏读	不可重复读	幻读
读取未提交	√（会发生）	√	√
读取已提交	×	√	√
可重复度	×	×	√
可串行化	×	×	×

3、索引

索引是帮助MySQL高效获取数据的数据结构。（数据库索引好比是一本书 前面的目录），能加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

红黑树：由于红黑树也是一颗二叉树，所以也会存在一个缺点： 大数据量情况下，层级较深，检索速度慢。

hash结构：（哈希索引就是将键值转化为新的hash值，映射到对应的位置，存储到hash表中；如果多个键值映射到同一槽位，会发生hash碰撞，通过增加链表解决）

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
• 无法利用索引完成排序操作
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

B-树：B树是一种多路平衡查找树（左小，右大），B树每个节点可以有多个分支，即多叉。特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

B+数：最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
• 

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点 的指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。（双向循环链表）

优点：

• 提高数据检索的效率，降低数据库 的IO成本 。
• 通过索引列对数据进行排序，降低 数据排序的成本，降低CPU的消耗。

缺点：

• 索引列也是要占用空间的。
• 索引大大提高了查询效率，同时却也降低更新表的速度， 如对表进行INSERT、UPDATE、DELETE时，效率降低。
  

3.1 为什么InnoDB存储引擎选择使用B+tree索引结构？

• 相对于二叉树，层级更少，搜索效率高；
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储 的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
• 相对Hash索引，B+tree支持范围匹配及排序操作；

3.2 什么时候不要使用索引？

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引；
• 表记录太少不要建立索引。

3.3 索引分类

主键索引	索引列中的值必须是唯一的，不允许有空值。（只能有一个）	Primary
唯一索引	索引列中的值必须是唯一的，但是允许为空值。（可以有多个）	UNIQUE
普通索引	MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
全文索引	只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。	FULLTEXT
前缀索引	当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。 此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。 create index 索引名称 on 表名(字段名称(字段前缀长度)) 前缀索引长度：由索引的（区分度）选择性（不重复的索引值（基数）/数据表的记录总数）确定。
覆盖索引	尽量使用覆盖索引，减少使用select *。 查询使用了索引，并且需要返回的列，在该索引中能够全部找到。 eg：tb_user中有一个联合索引idx_user_pro_age_sta，该索引关联了三个字段，如果返回的字段都在这个索引中，会直接走二级索引返回数据。如果超出这个范围，先走二级索引，得到主键的id，再去扫描聚簇索引，再去获得额外的数据（回表）。 所以直接使用select * 很容易造成回表，减慢性能。 select id, name from name = ‘aaa’ 直接走二级索引并返回结果

3.4 聚簇索引和二级索引

MYSQL引擎分为：

• InnoDB：Mysql5.5之后默认引擎，DML操作支持ACID模型；行级锁，提高并发访问的性能；支持外键约束，保证数据的完整性和正确性。
• MyISAM：Mysql早期引擎，不支持事务，不支持外键；支持表锁，不支持行锁；访问速度快。
• Memory：引擎的表数据存放在内存当中，只能将表作为临时表使用。默认使用hash索引。

在InnoDB存储引擎中，根据索引的存储形式，又可以分为：

分类	含义	特点
聚簇索引	将数据存储与索引放在一块，索引结构的叶子结点保存了行数据	必须有，且只能一个
二级索引	将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键	可以存在多个

聚簇索引选取规则：

• 存在主键，则主键为聚簇索引。
• 不存在主键，将第一个唯一（UNIQUE）索引作为聚簇索引。
• 表没有主键且没有合适的唯一索引，InonoDB会自动生成一个行id（rowid）作为隐藏的聚簇索引。

1、a：SELECT * FROM USER WHERE NAME = ‘TEST’

流程：（回表查询）

• 根据name字段到二级索引中进行查询，查询会返回对应的主键。
• 再到聚簇索引中根据主键获取整行数据。

b：SELECT * FROM USER WHERE ID = 4;

2、a语句和b语句那个执行效率高？

b的效率高，b会直接走聚簇索引，根据id查找，直接返回整行数据。

3.5 索引语法

# 创建索引   
# 注：联合索引，1、遵守最左前缀法则：查询索引从索引字段的第一个开始（必须存在），诶个查询，如果第一个索引字段为null，则索引失效。
#             2、联合索引中出现范围查询（>，<），范围查询的右侧索引字段失效。使用（>=,<=）查询时，索引可以生效。
create [unique | fulltext] index 索引名称 on 表名称(字段名称1 asc, 字段名称2 desc,...);
# 查看表中的索引
show index from 表名;

3.6 索引失效

• 不要在索引上进行运算操作（函数运算），否则索引失效

• 字符串类型字段使用时不加引号，索引失效

• 头部模糊查询（like ‘%工程’)，索引失效；尾部模糊查询不会（like ‘工程%’）。

• 用or分割的条件，都必须有索引，否则一方的索引失效。

• 数据分布的影响：如果mysql评估索引比全表更慢，放弃使用索引。索引是用来索引少量数据的，如果索引返回大量数据，速度会比全表扫描慢。

  eg：如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就要回表查询一次对应的行，即：不能使用覆盖索引的情况

3.7 单列索引和联合索引（推荐）

# 在Sql语句中加入认为的提示来达到优化操作的目的。
use index(索引名称)：建议MySQL使用哪一个索引完成此次查询。  SELECT * FROM TABLE_USER use index(idx_user_pro) where profession = '软工';
ignore index(索引名称): 忽略指定的索引
force index(索引名称): 强制使用索引

3.8 索引设计原则

• 针对数据量比较大，查询频繁的表建立索引。
• 针对常作为where、order by、group by操作的字段建立索引。
• 尽量使用区分度高的列作为索引，尽量建立唯一索引，区分度越高，查询效率越快。
• 字符串比较长，可以针对字符串的特点，建立前缀索引。
• 索引不是越多越好，索引越多，维护索引的代价越大，会影响增删改的效率。
• 当索引列不能存储null时，创表时用not null约束。当优化器知道每一列是否包含NULL值时，可以更好地确定使用那个索引。

4、SQL优化手段有哪些

4.1 select优化

• show status; //查看当前数据库的insert、update、delete、select的访问频率。 show global status like ‘Com_ _ _ _ _ _ _’

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑

不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

如说是以查询为主，我们可以借助于慢查询日志对执行比较慢的查询语句进行优化。（可以在配置文件中开启慢查询，并设置慢查询的执行时间阈值）

找到特定的sql语句后，可以通过 explain + sql语句，查看select语句执行的信息（索引字段，执行查询的行数等）

4.2 insert优化

4.2.1 insert

• # 多次单条数据的插入---> 一次插入多条数据
  insert into 表名 values(字段1,字段2),(字段1,字段2),(字段1,字段2);
  

• # 多次单条数据的插入 ----> 手动控制事务
  start transaction;
  insert into 表名 values(字段1,字段2);
  insert into 表名 values(字段1,字段2);
  insert into 表名 values(字段1,字段2);
  commit;
  

• # 主键顺序插入，性能要好于乱序插入
  主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
  主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
  

4.2.2 大批量插入数据

​ 如果要一次性插入大批量数据（几百万条），使用insert插入性能比较低，此时可以使用MySQL提供的load指令插入。

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

4.3 主键优化

• 满足需求的同时，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
• 尽量不要使用UUID作为主键
• 业务操作时，避免对主键进行修改

4.4 order by优化

索引排序前提

• 只有当索引列顺序和ORDER BY的列顺序完全一致，并且排序方式都是一样的情况时（MySQL8后支持反向扫描索引）才能使用索引对结果做排序
• 如果存在表关联，则只有当ORDER BY引用的字段全部为第一个表时，才能使用索引排序
• 同时ORDER BY使用索引排序的时候是符合和查询语句一样的最左前缀法则的，否则MySQL还是需要执行排序操作，无法利用索引排序
• 多字段排序，一个升序一个降序，需要创建联合索引（创建规则同样为一个字段升序，一个字段降序）

4.5 group by优化

在分组操作时：

• 通过索引来提高效率
• 索引的使用也是符合最左前缀法则的

4.6 limit优化

问题：分页操作通常会使用limit加上偏移量的办法实现，同时再加上合适的order by子句。但这会出现一个常见问题：当偏移量非常大的时候，它会导致MySQL扫描大量不需要的行然后再抛弃掉。

解决：

• 使用索引覆盖+子查询优化

  select * from tb_sku limit 2000000,10
  # 优化后：
  select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
  

• 起始位置重定义：记住上次查找结果的主键位置，避免使用偏移量 offset（前提为：不能跳页查询）

4.7 count优化

count(*) ≈ count(1) > count(主键id) > count(字段) 效率最高

4.8 更新优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

# 当我们开启多个事务，在执行下述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能大大降低。
update course set name = 'springBoot' where name = 'PHP'; 
# 下面sql语句不会影响
update course set name = 'test' where id = 1;

1、查询语句中不要使用select *

2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代

3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时， union all会更好)

5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表 扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有 null值，然后这样查询： select id from t where num=0

4.1 大表如何优化

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

• 限定数据的范围 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们 可以控制在一个月的范围内；

• 读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；

  一般来说在主从读写分离机制中，我们将一个数据库的数据拷贝为一份或者多份，并且写入到其它的数据库服务器中，原始的数据库我们称为主库，主要负责数据的写入，拷贝的目标数据库称为从库，主要负责支持数据查询。

• 垂直分区

  根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信 息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

  简单来说垂直拆分是把一张列比较多的表拆分为多张表。 如下图所示，这样来 说大家应该就更容易理解了。

  优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外， 垂直分区可以简化表的结构，易于维护。

  缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层 进行Join来解决。此外，垂直分区会让事务变得更加复杂；

• 水平分区 保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据 拆成多张表来存放。

  举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单 一表数据量过大对性能造成影响。

  水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。 水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能 较差，逻辑复杂。

  《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来 逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有 太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

  下面补充一下数据库分片的两种常见方案： 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网 的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现 在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

4.2 分库分表之后,id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

• UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯 一的名字的标示比如文件的名字。
• 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式 生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
• 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系 统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
• Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
• 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋 势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据 库、Zookeeper等中间件。感觉还不错。

5、MySQL数据库的锁

1. 表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。（每次操作锁住整张表。）
2. 行锁（每次操作锁住对应的行数据。）：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现， 否则会自动锁全表，那么就不是行锁了）。
   1. 共享锁（读锁）：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
   2. 排它锁（写锁）：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
3. 全局锁：锁定数据库中的所有表。

5.1 全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

flush tables with read lock;
unlock tables;

5.2 表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 表锁（读锁、写锁）

  # 加锁
  lock table 表名 read/write
  # 释放锁
  unlock tables;
  

  读锁：不阻塞其他客户端的读，但会阻塞写。

  写锁：阻塞其他客户端的读和写。

• 元数据锁（meta data lock）

  元数据（表结构），系统自动控制，在访问一张表的时候会自动加上去。

  作用：为了避免DML与DDL的冲突，保证读写的正确性。（某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。）

• 意向锁

  避免在执行DML时，行锁和表锁的冲突。在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

  eg：在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。而其他客户端，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁（有，不用加表锁），而不用逐行判断行锁情况了。

5.3 行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。

5.3.1 行锁

• 共享锁
• 排他锁

InnoDB实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

Insert、update、delete自动加入排他锁；SELECT（正常） 不加任何锁

5.3.2 锁升级

MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。原因是 InnoDB 是将 primary key 、index 和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。 当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现当非唯一索引相同的 内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记 录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成 索引失效，行锁自然就会升级为表锁。

5.4 说说悲观锁和乐观锁

从性能上分为乐观锁和悲观锁

对数据库操作类型分，分为读锁（共享锁）和写锁（排它锁）(都属于悲观锁)

从数据操作的颗粒度，分为行锁、页锁、表锁

悲观锁

说的是数据库被外界（包括本系统当前的其他事务以及来自外部系统的事务处理）修改保持着保守态度，因此在整个数据修改过程中，将数据处于锁状态。

悲观的实现往往是依靠数据库提供的锁机制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实现了加锁机制，也是没有办法保证系统不会修改数据。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务 无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。

乐观锁，大多是基于数据版本（Version）记录机制实 现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，

一般是通过 为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对 此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果 提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

6、SQL中的drop、delete、truncate的区别

drop、delete、truncate都表示删除，但是三者有一些差别

delete和truncate只删除表的数据不删除表的结构

速度,一般来说: drop（删除库）> truncate >delete

delete 语句是dml,这个操作会放到rollback segement中,事务提交之后才生效; 如果有相应的trigger,执行的时候将被触发.

truncate,drop是ddl, 操作立即生效，原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

7、什么是视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易， 相比多表查询。

8、主键和候选键有什么区别？

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

超键：始终能够确保在关系中能唯一标识元组

不含有多余属性的超键称为候选键

9、主键与索引有什么区别？

• 主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键； 主键不允许为空值，唯一索引列允许空值；
• 一个表只能有一个主键，但是可以有多个唯一索引；
• 主键可以被其他表引用为外键，唯一索引列不可以；
• 主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本质的区别

10、MySQL 如何做到高可用方案？

MySQL 高可用，意味着不能一台 MySQL 出了问题，就不能访问了。

1. MySQL 高可用：分库分表，通过 MyCat 连接多个 MySQL （MyCat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器）
2. MyCat 也得高可用：Haproxy，连接多个 MyCat（HAProxy是一个提供高可用性、负载均衡以及基于TCP(第四层)和HTTP(第七层)应用的代理软件。）
3. Haproxy 也得高可用：通过 keepalived 辅助 Haproxy

11、char和varchar的区别

char 与 varchar 都可以描述字符串。

char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。

而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。

12、Sql语句

//设计一张员工信息表，要求如下：
1. 编号（纯数字）
2. 员工工号 (字符串类型，长度不超过10位)
3. 员工姓名（字符串类型，长度不超过10位）
4. 性别（男/女，存储一个汉字）
5. 年龄（正常人年龄，不可能存储负数）
6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
7. 入职时间（取值年月日即可）

create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    entrydate date comment '入职时间'
) comment '员工表';

13、DDL 数据定义语言

定义数据库对象（数据库、表、字段），操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。

13.1 数据库操作

# 查询所有数据库
show databases;
# 创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
# 删除数据库
drop database [if exists] 数据库名;
# 切换数据库
use 数据库名;

13.1 表操作

# 查询表结构
desc 表名;
# 查询创建表语句
show create table 表名;
# 修改表名
alter table 表名 rename to 新表名;
# 创建表结构 
create table 表名(
	字段名1 字段类型  [comment 注释],
	字段名1 字段类型  [comment 注释],
	字段名1 字段类型  [comment 注释],
	字段名1 字段类型  [comment 注释]
)[comment 表注释];
# 删除表
drop table if exists 表名;
# 删除表并创建一个新的（清空数据）
truncate table 表名; 

13.2 字段操作

# 字段类型
	# 数值类型
	tinyint  1byte -128,127
	tinyint unsigned 1byte 0-255
	smallint 2bytes(16位) 
	mediumint 3bytes
	int      4bytes          float   4bytes
	bigint   8bytes          double  8bytes   eg:double(10,1) 长度10位，小数点后1位
	# 字符串类型
	char 0-255bytes 定长        eg: phone char(11)  gender char(1)
	varchar 0-65535bytes 变长       username varchar(50)
	# 日期时间类
	date  YYYY-MM-DD 日期值    eg: birthday date
	datetime  YYYY-MM-DD HH:MM:SS 日期+时间  eg: createtime datetime
# 添加字段
alter table 表名 add 字段名 类型长度;
# 修改字段类型
alter table 表名 字段名 字段新类型;
# 修改字段名称及类型
alter table 表名 旧字段名 新字段名 字段类型;
# 删除字段
alter table 表名 drop 字段;

14、DML数据操作语言

• 添加数据（insert）
• 修改数据（update）
• 删除数据（delete）

14.1 添加数据

# 给指定字段添加数据
insert into 表名(字段1, 字段2, 字段3) values(value1, value2, value3);
# 给全部字段添加数据
insert into 表名 values(value1, value2, value3);
# 批量添加数据
insert into 表名 values (value1,value11), (value2, value22);

14.2 修改数据

# 修改数据
update 表名 set 字段名1 = 值1, 字段名2 = 值2 [where 条件]; 

14.3 删除数据

# 删除数据（如果删除整个表，会保留表的结构，并且可以进行回滚）
delete from 表名 [where 条件];
# 内连接时删除某一条数据
delete p1 from Person p1, Person p2 where p1.Email = p2.Email and p1.id > p2.id

使用上：

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 Where 子句的 Delete 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

TRUNCATE TABLE 不能用于参与了索引视图的表。

想删除部分数据行用delete，注意带上where子句. 回滚段要足够大.

想删除表,当然用drop

想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete.

如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据

15、DQL 数据查询语言

查询关键字：select

查询过程中会设计到条件、分页、排序等操作。

# DQL查询操作基本语法
select 
	字段列表
from 
	表名列表
where 
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

• 基础查询

  # 查询多个字段
  select * from 表名  # 少用，效率低
  select 字段1, 字段2, 字段3 from 表名
  # 字段设置别名
  select 字段1 as 别名1, 字段2 as 别名2 from 表名
  # 去重
  select distinct 字段1, 字段2 from 表名
  

• 条件查询（where）

  # 基础语法
  select 字段列表 from 表名 where 条件列表;
  

  比较运算符	功能	逻辑运算符	功能
  >、>=、<、<=、=、!= [<>]	大于	and 或者 &&	多个条件同时成立
  between…and	在某个范围之内，含最大值和最小值	or 或者 ||	多个条件任意一个成立
  in(…)	选择in之后列表内的值，多选1	not 或者 !	非
  like 占位符	模糊匹配( _匹配单个字符，%匹配任意个字符)
  is null	是空

• 聚合函数（count、max、min、avg、sum）

  将一列数据作为一个整理，进行纵向计算，空值不参与运算（除了count(*)、count(1) 统计所有表中的行数)

  # 语法
  select 聚合函数(字段列表[*]) from 表名;
  

• 分组查询（group by）

  where 和 having 的区别：

  • 执行的时机不同，where是在分组之前进行过滤，不满足where条件不参与分组；而having是分组之后对结果进行过滤
  • 判断条件不同，where不能对聚合函数进行判断，而having可以

  注意事项：

  • 执行顺序where > group by > 聚合函数 > having
  • 分组之后，查询的字段一般为聚合函数和分组字段
  • 支持多字段分组，具体语法为：group by column A, column B

  # 语法
  select 字段列表 from 表名 [where 条件列表] group by 分组字段名 [having 分组后过滤条件]
  

• 排序查询（order by）

  升序：ASC（默认）

  降序：DESC

  # 语法
  select 字段列表 from 表名 order by 字段1 排序方式, 字段2 排序方式;
  

• 分页查询（limit）

  • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示的记录数
  • 分页查询是数据库的方言，不同数据库有不同的实现，MYSQL中使用LIMIT
  • 如果查询的是第一页的数据，直接简写成Limit 10。

  # 语法
  select 字段列表 from 表名 limit 起始索引, 查询记录数;
  

16、DCL数据控制语言

管理数据库用户、控制数据库访问权限

• 主机可以用 % 通配符。
• MYSQL中通过 用户名@主机名 唯一标识一个用户。

16.1 管理用户

# 查询用户   查询结果主要包括host（用户访问时的ip地址、user表示访问该数据库时的用户名，二者联合唯一标识一个用户）
select * from mysql.user
# 创建用户
create user '用户名'@'主机名' identified by '密码';
# 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
# 删除用户
drop user '用户名'@'主机名'

16.2 权限控制

权限	说明	权限	说明
ALL、ALL PRIVILEGES	所有权限	create	创建数据库/表
insert、delete、update、select	增删改查（数据）权限	drop	删除数据库/表/视图
alter	修改表

• 多个权限间用 ，号分割
• 授予时，数据库名和表名可以用 * 进行通配，表示所有 eg：.

# 查询权限
show grants for '用户名'@'主机名';
# 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
# 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

17、函数

17.1 字符串函数

函数	功能	函数	功能
concate(s1,s2,s3)	字符串拼接	lpad(str, n, pad)	左填充，使用字符串pad填充str，直到str.length == n
lower(s1) upper(s1)	大小写转换	rpad(str, n , pad)	右填充
trim(str)	去掉首尾空格	substring(str, start, len)	截取字符串，从start开始，长度为len

17.2 数值函数

函数	功能	函数	功能
cell(x)	向上取整	rand()	0 - 1之间的随机数
floor(x)	向下取整	round(x,y)	对参数x四舍五入，保留y位小数
mod(x,y)	x % y

17.3 日期函数

函数	功能	函数	功能
curdate()	返回当前日期	year(date)	返回date的年份
curtime()	返回当前时间	month(date)	返回date的月份
now()	返回当前日期+时间	day(date)	返回date的天数
date_add(date, interval expr type)	date + expr date_add(date, interval 10 year)	datediff(date1, date2)	返回结束时间date1和date2之间相差的天数 date1 - date2

17.4 流程函数

流程函数可以在SQL语句中实现条件筛选，从而提高语句效率

函数	功能	函数	功能
if(value, t, f)	如果value为true，返回t，否则返回f	case when [val] then [res] when [val1] then [rest1] else [default] end	如果val == true，返回res，否则继续判断val1 == true，是返回val1，否则返回default
ifnull(value1, value2)	如果value1 != null，返回value1，否则返回value2	case [expr] when [val] then [res] when [val1] then [res1] … else [default] end	如果expr == val 返回 res，否则继续判断 expr == val1，是返回res1，否则返回default

18、约束

• 作用于表中字段上的规则，用于限制存储在表中的数据。保证数据的正确性。
• 可以在创建表/修改表的时候添加约束。

约束	描述	关键字
非空约束	限制该字段不能为空	not null
唯一约束	保证该字段所有数据是唯一的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	foreign key
检查约束(8.0.16版本后)	保证字段满足某一条件	check
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default

create table tb_user(
    id int auto_increment primary key comment 'ID唯一标识',
    name varchar(10) unique not null comment '姓名',
    age int check(age > 0 && age <= 120) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别',
) comment '用户表';

18.1 外键约束

# 创建外键
create table 表名(
    字段名 数据类型,
    [constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名) 
);

alter table 表名 add [constraint 外键名称] foreign key (外键字段名) references 主表（主表列名)
# 删除外键
alter table 表名 drop foreign key 外键名称;

18.11 删除/更新

添加了外键之后，再删除父表数据时产生的约束行为称为删除/更新行为。

行为	说明	行为	说明
NO ACTION、Restrict	在父表删除/更新行为时，若该记录对应有外键不允许删除/更新。	set null	有外键设置子表中外键值为null（前提外键允许为null）
cascade	在父表删除/更新行为时，若该记录对应有外键，则删除/更新外键在子表中的记录。	set default	父表有更新时，设置子表中外键列的值为默认值

# 语法
alter table add [constraint] [外键名称] foreign key(外键名) references 主表(主表列名) on update cascade on delete cascade

19、多表查询

表结构关系

• 一对多

• 多对多

  # 创表语句
  create table student_course(
      id int auto_increment comment '主键' primary key,
      studentid int not null comment '学生ID',
      courseid int not null comment '课程ID',
      constraint fk_courseid foreign key (courseid) references course (id),
      constraint fk_studentid foreign key (studentid) references student (id)
  )comment '学生课程中间表';
  

• 一对一

  适用于单表拆分 eg：用户与用户间的详细信息，把一张表的基础字段放在一张表中，其他详细字段放在另一张表中，提高效率。

  实现方式：在任意一方添加外键，关联另一方的主键，并且设置外键为唯一（unique）

  # university varchar(50) comment '大学',
  create table tb_user(
      id int auto_increment primary key comment '主键ID',
      name varchar(10) comment '姓名',
      age int comment '年龄',
      gender char(1) comment '1: 男 , 2: 女',
      phone char(11) comment '手机号'
  ) comment '用户基本信息表';
  
  create table tb_user_edu(
      id int auto_increment primary key comment '主键ID',
      degree varchar(20) comment '学历',
      major varchar(50) comment '专业',
      primaryschool varchar(50) comment '小学',
      middleschool varchar(50) comment '中学',
      userid int unique comment '用户ID', # unique
      constraint fk_userid foreign key (userid) references tb_user(id)
  ) comment '用户教育信息表';
  

  19.1 笛卡尔积

  笛卡尔积：数学中，集合A和集合B的所有组合情况 select * from 表1, 表2(有无效的记录)

  ​ A：C D

  ​ B：E F --> C E | C F | D E | D F
  

此时，要消除无效的记录，给多表查询加上连接查询的条件即可。

select * from 表1, 表2 where 表1.外键 = 表2.主键

19.1 分类

19.1.2 内连接

内连接查询的是两个集合中交集的部分。

• 隐式内连接

  # 语法
  select 字段列表 from 表1, 表2 where 条件;
  

• 显式内连接

  # 语法
  select 字段列表 from 表1 [inner] join 表2 on 连接条件;
  

  注：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能使用别名来指定字段。

19.1.3 外连接

外连接分为：左外连接和右外连接。

• 左外连接：相当于查询左表的所有数据。

  select 字段列表 from 表1 left [outer] join 表2 on 条件;
  

• 右外连接：相当于查询右表的所有数据。

  select 字段列表 from 表2 right [outer] join 表1 on 条件;
  

19.1.4 自连接

• 自连接查询：把一张表连接多次查询（可以是内连接或外连接）

  # 语法
  select 字段列表 from 表1 as 别名1 join 表2 as 别名2 on 条件;
  

  注：自连接必须要为表起别名

• 联合查询：union查询，就是把多次查询的结果行合并起来，形成一个新的查询结果集

  # 语法
  select 字段列表 from 表1 ...
  UNION [ALL]
  select 字段列表 from 表2 ...;
  

  注：联合查询多张表的列数以及字段类型必须一致。UNION ALL 会直接对数据合并；UNION 会对合并后的数据进行去重。

19.1.5 子查询

SQL语句嵌套SELECT语句，称为嵌套查询，称为子查询。

# 语法
select * from 表1 where column1 = (select column1 from 表2);

注：子查询外部语句可以是INSERT / UPDATE / DELETE / SELECT。

分类：

根据子查询的结果不同分为：

• 标量子查询（结果为单个值） 常用操作符：= >= <= !=

• 列子查询（结果为一列）常用操作符：in、not in、any（子查询列表中任意一个满足）、some（等同any）、all（子查询返回列表所有值必须满足）

• 行子查询（结果为一行）常用操作符：=、!=、in、not in

  # 查询与 "张无忌" 的薪资及直属领导相同的员工信息
  select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌')
  

• 表子查询（结果为多行多列）常用操作符：in

根据子查询的位置分为：

• where之后

• from之后

  # 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
  select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
  

• select之后

  # 查询所有的部门信息, 并统计部门的员工人数
  select d.id, d.name, (select count(*) from emp e where e.dept_id = d.id) from dept d;