Mysql常用知识点

0、最常用的三大范式

第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

1、mysql的架构层次

通过概要图我们可以看到mysql的架构主要分为三层次
客户端client层、server层、存储引擎层。我们平时讲的事务、索引、锁机制等基本都是存储引擎层的。不同的存储引擎他的表现形式及实现原理都不一样。

2、执行计划

explain被称为执行计划，在语句之前增加explain关键字，mysql会在查询上设置一个标记，模拟mysql优化器来执行sql语句，执行查询时会返回执行计划的信息如下：

2.1、id

select查询序列号包含一组数字，表示查询中执行select子句或者操作表的顺序；

分为三种情况：

1、如果id相同，那么执行顺序从上至下

2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3、id相同和不同同时存在，相同的认为一组，从上至下顺序执行，在组中id值越大优先级越高，越先执行；

2.2、select_type

SIMPLE：简单 SELECT，不需要使用 UNION 操作或子查询。
PRIMARY：如果查询包含子查询，最外层的 SELECT 被标记为 PRIMARY。
UNION：UNION 操作中第二个或后面的 SELECT 语句。
SUBQUERY：子查询中的第一个 SELECT。
DERIVED：派生表的 SELECT 子查询。

2.3、table

2.4、type

12种type的可能性

ALL < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < ref_or_null < fulltext < ref < const < system

一般情况下，得保证查询至少达到range级别，最好能达到ref

2.5、possible_key

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用；

2.6、key

实际使用的索引，如果为null，则没有索引

2.7、key_len

索引中使用的字节数，可以通过key_len计算查询中使用的索引长度

2.8、ref

显示索引在哪一列中被使用了，如果可能的话，是一个常数

2.9、rows

根据表的统计信息及索引使用情况，大致估计找出所需记录需要读取的行数，这个很关键，一般越少越快；

2.10、extra

额外信息

--using filesort：说明mysql无法利用索引进行排序，只能利用排序算法进行排序

--using temporary：建立临时表来保存中间结果

--using index：查询时使用覆盖索引，直接从索引中读取数据，而不用额外数据表；

--using where：使用where进行条件过滤

--using join buffer：使用连接缓存

--impossible where：where语句的结果总是false

3、索引优化

3.1、分类

一般在进行索引分类时，可分为以下几大类
1、主键索引

2、唯一索引

3、普通索引

4、全文索引

5、组合索引

3.2、回表

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据

3.3、覆盖索引

索引覆盖和回表时相反的概念，在索引的叶子结点中如果能获取到查询的所有列数据，无须回表的过程叫做索引覆盖；

3.4、最左匹配

组合索引在进行查询的时候，遵循最左匹配原则，必须要先匹配第一行之后才能匹配第二列。

3.5、聚簇索引&非聚簇索引

聚簇索引：数据和对应的索引列紧凑的存储在一起

非聚簇索引：数据和索引时分开存储的

4、mysql中char和varchar有什么区别

1、char是固定长度的，而varchar是可变长度的；

2、char存储和检索效率高，而varchar存储和检索效率不高；

3、char占用存储空间，而varchar可以节省存储空间。

5、union和union all区别

• 重复记录。UNION操作符会去除结果集中的重复行，确保最终的结果集中没有重复数据；而UNION ALL操作符会将所有的结果全部显示出来，包括重复行。
• 排序方式。UNION操作符会对结果集进行默认规则的排序；而UNION ALL操作符不会对结果集进行排序，结果集的顺序由各个子查询的顺序决定。
• 性能。由于UNION需要进行去重和排序操作，其性能通常低于UNION ALL；而UNION ALL由于不需要进行去重和排序，因此通常具有更好的性能。

6、InnoDB存储引擎的三种行锁算法

Record Lock：单个索引记录上的锁；

Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；

Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身

InnoDB存储引擎层行锁的结构，在同一SQL语句在不同隔离级别下加锁情况是不一样的。一般情况下，部分企业为了提高并发，生产环境会采用RC隔离级别。在RR隔离级别下，对没有索引的字段进行操作时，即使查询1行记录，也都会加所有记录的Next-Key Lock，这样看来，加锁的代价较RC隔离级别还是很大的。