MySQL总结

<不良>

已于 2022-04-19 23:01:17 修改

阅读量1k

点赞数

分类专栏：秋招面试资料整理文章标签： mysql 数据库 sql

于 2022-03-15 14:26:49 首次发布

本文链接：https://blog.csdn.net/wjq2020/article/details/121543336

版权

秋招面试资料整理专栏收录该内容

13 篇文章 0 订阅

订阅专栏

MySQL总结

1. 数据库范式

第一范式：数据库表中的所有字段都不可再分，都是单一属性。
第二范式：在第一范式的基础上，数据库表中不存在非关键字段对任一候选关键字的部分函数依赖。
第三范式：在第二范式的基础上，数据库表中不存在非关键字段对任一字段的传递函数依赖。
BC范式：在第三范式的基础上，数据表中不存在任意一字段对任一候选关键字的传递函数依赖。

2. 事务

2.1 简介

事务是一个不可分割的数据库操作序列，页式数据库并发控制的基本单位，其执行结果必须是数据库从一种一致性状态到另一种一致性状态。

2.2 事务四大特性（ACID）

原子性：事务所包含的一系列数据库操作要么全部成功执行，要么回滚到执行前的状态。
一致性：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。
隔离性：并发事务之间不能相互影响。
持久性：事务一旦提交，对数据库中数据的改变是永久的。

2.3 事务并发带来的问题

脏读：一个事务读取了另一个事务未提交的数据。
不可重复度：不可重复读的重点是修改，同样条件下两次读出的数据不同。
幻读：幻读的重点是新增和删除，同样条件下两次读出的记录数不同。

2.4 事务的隔离级别

事务的隔离性，当多个线程都开启事务操作时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性。

读未提交：3个问题均未解决。

如果一个事务已经开始写数据，则另一个事务不允许同时进行写操作，但可进行读操作，该级别可通过”排他写锁“实现。
读已提交：避免了脏读问题。

如果读事务，允许其他事务读写；如果写事务，不允许其他事务读写。
可重复读（MySQL默认的隔离级别）：避免了脏读和不可重复读问题。

一个事务在未结束时，不允许其他事务访问该数据（包括读写）。
序列化：避免了脏读、不可重复度和幻读问题。

事务按序执行，不能并发执行。

3. 键

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性或多个属性组合都可以作为一个超键，超键包括候选键和主键。
候选键：是最小的超键，既没有冗员字段的超键。
主键：一个主键唯一标识一个元祖，且不能重复不能null。
外键：在此表中存在的另一个表的主键称为此表的外键。

4. 连接

在这里插入图片描述

内连接
等值连接：在连接条件中使用 ”=“ 连接两表的值，包括重复值。
非等值连接：在连接条件中使用非等于号（> 、<、！=…）连接两表的值，包括重复值。
自然连接：在连接条件中使用 ”=“ 连接两表的值，不包括重复值。

例：select * from a_table a inner join b_table b on a.a_id = b.b_id;
左外连接
左外连接，就是无论on中的条件满不满足，都保留左表所有数据

例：select * from a_table a left join b_table b on a.a_id = b.b_id;
右外连接
右外连接，即无论on中的条件满不满足，都保留右表所有数据。

例：select * from a_table a right outer join b_table b on a.a_id = b.b_id;

5. 悲观锁与乐观锁

悲观锁的特点为先获取锁，再进行业务操作，即”悲观“的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功后再进行业务操作。
乐观锁的特点为先进行业务操作，在最后实际更新数据时进行检查数据是否要更新。

补：乐观锁更新规则：
在需要锁的数据上增加一个版本字段，读取数据时，将版本号一同读出，之后更新时，对此版本号加一。此时将提交数据的版本号与数据库中记录的版本号比较，如果提交的版本号大于数据库中的版本号，则予以更新，否则，认为是过期数据，不予更新。
应用场景
悲观锁：用于读少写多的情况；
乐观锁：用于读多写少的情况；

6. select的执行顺序

select的语法结构

SELECT clause
[ FROM clause ]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]

SELECT子句是必选的，其它子句如WHERE子句、GROUP BY等是可选的。

SELECT语句中，子句的顺序是固定的。例如GROUP BY子句不会位于WHERE子句的前面。
select的执行顺序
(1) FROM <left_table>

(2) <join_type> JOIN <right_table>

(3) ON <join_condition>

(4) WHERE <where_condition>

(5) GROUP BY <group_by_list> （如有聚集函数：GROUP BY后使用聚集函数进行计算）

(6) HAVING <having_condition>

(7) SELECT

(8) ORDER BY <order_by_list>

7. 索引

7.1 索引简介

说明
索引是为了加快查找表中数据的方法。索引可快速找到数据，且不用扫描整个数据表。
MySQL的索引的实现方式为B＋树。
分类
a. 唯一索引：索引列中的值必须是唯一的，但是允许为空值
b. 主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）

c. 聚集索引与非聚集索引
(聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致)
1. 聚集索引
  聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引一个表只会有一个。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。
2. 非聚集索引
  非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，一个表中可以拥有多个非聚集索。非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。
d. 稠密索引与稀疏索引
1. 稠密索引为数据记录文件的每一条记录都设一个键-指针对。
2. 稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续。

7.2 哪些字段可建索引？

适合建的字段：主键、外键，常用于分组何排序的字段、有对象或身份标识意义的小字段。

可建但慎用的字段：日期、区域、数值。

不合适建的字段：备注、大字段。

7.3 为什么索引会降低插入、删除、修改等维护任务的速度？

因为在插入（insert）和删除（delete）时，都需要对索引进行维护和更新；在更新（update）时，涉及到索引时页需进行维护，相对delete和insert开销要小一些。

8. MySQL存储引擎

8.1 存储引擎：Mysql用来存储组织数据的格式。

8.2 InnoDB和MyISAM的区别

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
InnoDB是聚集索引，B+树主键索引的叶子节点就是数据文件。数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了；
InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁。 InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上，即：如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

例如：
 
    t_user(uid, uname, age, sex) innodb;
 
    uid PK
    无其他索引
    update t_user set age=10 where uid=1;             命中索引，行锁。
 
    update t_user set age=10 where uid != 1;           未命中索引，表锁。
 
    update t_user set age=10 where name='chackca';    无索引，表锁。

8.3 InnoDB和MyISAM的结构

Innodb主索引搜索：

在这里插入图片描述
叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

InnoDB辅助索引：
在这里插入图片描述
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

MyISAM索引搜索：

在这里插入图片描述
索引由B+树构成，执行查询操作的时候会先搜索B+树，如果找到对应叶子结点会，根据叶子节点的值（地址），拿出整行数据。

8.4 为什么InnoDB推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

8.5 回表、索引覆盖、最左匹配、索引下推

回表：根据普通索引查询得到key值后，在根据key值得到所查记录；

例：假设有一张表table（id，name，age，sex），id主键索引，name普通索引。
select * from table where name='zs';在查询时，先根据name查询得到id，再根据id拆线呢得到整行记录，走了两次B+树。

索引覆盖：根据普通索引可直接查询得到主键索引和普通索引的值，直接返回即可，不需要再从主键索引查询数据。

例：table（id，name，age，sex），id主键索引，name普通索引
select id，name from table where name=‘zs’；查询的id，name时，根据name可直接得到id和name的值，直接返回即可。

最左匹配：使用组合索引时，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

假如建立联合索引（a,b,c）

1)、全值匹配查询时
```
select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1' 
```
用到了索引：where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序

2)、匹配左边的列时
```
select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'
```
都从最左边开始连续匹配，用到了索引
```
select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3' 
```
这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描。
```
select * from table_name where a = '1' and c = '3' 
```
如果不连续时，只用到了a列的索引，b列和c列都没有用到

3)、匹配列前缀

如果列是字符型的话，它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了
```
select * from table_name where a like 'As%'; //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As'//全表查询
select * from table_name where  a like '%As%'//全表查询
```
4)、匹配范围值
```
select * from table_name where  a > 1 and a < 3
```
可以对最左边的列进行范围查询
```
select * from table_name where  a > 1 and a < 3 and b > 1;
```
多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引，在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤

5)、精确匹配某一列并范围匹配另外一列

如果左边的列是精确查找的，右边的列可以进行范围查找
```
select * from table_name where  a = 1 and b > 3;
```
a=1的情况下b是有序的，进行范围查找走的是联合索引

索引下推（5.7之后加入的）
在这里插入图片描述

例： table（id，name，age，sex），id主键索引，name和age是组合索引
	select * from table where name='zs' and age=12;
不使用索引下推的执行过程：先根据name从存储索引中获取符合条件的数据，再在server层根据age过滤；
使用索引下推的执行过程：根据name和age直接在存储索引中获取数据；