关于Mysql数据库这些你一定要懂的基础

1、事务并发会导致的问题

脏读

在一个事务中读取到另一个事务已经修改但没有提交的数据。

例如，事务A对数据进行了修改，但是还没有提交，这时事务B读取这个数据，然后事务A回滚，那么事务B取的数据无效。不符合一致性。

不可重复读

既不能读到相同的数据内容，事务A读取到了事务B已经提交的修改数据（一个事务范围内两个相同的查询却返回了不同数据）。

例如，事务A先读取数据，然后事务B对该同一数据修改并提交，那么事务A再次读取该数据时，由于事务B对该数据的修改，事务A两次读到的的数据可能是不一样的。不符合隔离性。

幻读

事务A读取到了事务B已经提交的新增数据。在一个事务中，两次查询的结果不一致(针对的insert操作) 。

例如，事务A对一个表中的数据进行了修改，同时，事务B也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，事务A的用户后面的操作发现表中还有没有修改的数据行，就好象发生了幻觉一样。

更新丢失

两个事务同时操作相同数据，后提交的事务会覆盖先提交的事务处理结果。能够通过乐观锁解决。

2、事务ACID

原子性

指一个事务要么全部执行，要么不执行，就是说一个事务不可能只执行了一半就停止了。

比如你从取款机取钱，这个事务可以分成两个步骤：划卡，出钱。不可能划了卡，而钱却没出来，这两步必须同时完成，要么就不完成。

一致性

指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。

如A给B转账300元钱，那么A的账户就必须是减少300元钱，B的账户就必须是增加300元钱。不能说是增加或减少了300元钱等，这里符合事务的原子性，但是不符合事务的一致性。

往实际业务中没有这么简单，往是类似买东西扣库存这类的逻辑，主表里有库存，库存表里有库存，SKU表里还有，然后就因为设计缺陷，就算加了事务还是出现了超卖、SKU库存对不上总库存的问题。对面试经验、软件、接口、自动化测试感兴趣可以175317069，群内会有不定期的免费资料链接发放。

隔离性

事务的独立性也有称作隔离性，是指两个以上的事务不会出现交错执行的状态，因为这样可能会导致数据不一致。

持久性

一旦事务提交或者回滚，这个状态都要持久化到数据库中，不考虑隔离性会出现的读问题。

3、Mysql提供的四种事务隔离级别

1、MySQL数据的四种隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

③ Read committed (读已提交)：可避免脏读的发生。

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

四种隔离级别最高级的是Serializable级别，最低的是Read uncommitted级别，级别越高，执行效率就越低。

像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。

在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)。而在Oracle数据库中，只支持 Serializable(串行化) 级别和 Read committed(读已提交) 这两种级别，其中默认的为 Read committed 级别。

2、在MySQL数据库中查看当前事务的隔离级别：

 select @@tx_isolation;

3、在MySQL数据库中设置事务的隔离 级别：

set  [glogal | session]  transaction isolation level 隔离级别名称;
set tx_isolation=’隔离级别名称;’

4、数据库的三种范式

第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。对面试经验、软件、接口、自动化测试感兴趣可以175317069，群内会有不定期的免费资料链接发放。

第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

5、varchar与char的区别，以及varchar(50)中的 50 所代表的涵义

1、varchar与char的区别
在单字节字符集下， char（N）在内部存储的时候总是定长， 而且没有变长字段长度列表中。 在多字节字符集下面，char（N）如果存储的字节数超过 N，那么 char（ N）将和 varchar（ N）没有区别。

在多字节字符集下面，如果存储的字节数少于 N，那么存储 N 个字节，后面补空格，补到 N 字节长度。 都存储变长的数据和变长字段长度列表。 varchar(N)无论是什么字节字符集，都是变长的，即都存储变长数据和变长字段长度列表。

2、varchar(50)中50的涵义
最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

6、sql执行慢的原因有哪些，如何进行sql优化

导致SQL执行慢的原因：

1、硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。

2、没有索引或者索引失效。
一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除，一是为了做数据分析,二是为了不破坏索引 。

3、数据过多（分库分表）

4、服务器调优及各个参数设置（调整my.cnf）

分析原因时，一定要找切入点：

1、先观察，开启慢查询日志，设置相应的阈值（比如超过3秒就是慢SQL），在生产环境跑上个一天过后，看看哪些SQL比较慢。

2、Explain和慢SQL分析。比如SQL语句写的烂，索引没有或失效，关联查询太多（有时候是设计缺陷或者不得以的需求）等等。

3、Show Profile是比Explain更近一步的执行细节，可以查询到执行每一个SQL都干了什么事，这些事分别花了多少秒。

4、找DBA或者运维对MySQL进行服务器的参数调优。

解析：
(1)explain出来的各种item的意义

id：每个被独立执行的操作的标志，表示对象被操作的顺序。一般来说， id 值大，先被执行。如果 id 值相同，则顺序从上到下。

select_type：查询中每个 select 子句的类型。

table：名字，被操作的对象名称，通常的表名(或者别名)，但是也有其他格式。

partitions：匹配的分区信息。

type：join 类型。

possible_keys：列出可能会用到的索引。

key：实际用到的索引。

key_len：用到的索引键的平均长度，单位为字节。

ref：表示本行被操作的对象的参照对象，可能是一个常量用 const 表示，也可能是其他表的 key 指向的对象，比如说驱动表的连接列。

rows：估计每次需要扫描的行数。

filtered：rows*filtered/100 表示该步骤最后得到的行数(估计值)。

extra：重要的补充信息。对面试经验、软件、接口、自动化测试感兴趣可以175317069，群内会有不定期的免费资料链接发放。

(2)profile的意义以及使用场景

Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候，需要用profile 来对 sql 进行更细致的分析，找出 sql 所花的时间大部分消耗在哪个部分，确认 sql的性能瓶颈。

(3)explain 中的索引问题

Explain 结果中，一般来说，要看到尽量用 index(type 为 const、 ref 等， key 列有值)，避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列，需要建立索引。
被驱动表的连接列，也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时，也可以考虑建立索引来达到直接排序和汇总的需求。

7、数据库优化的思路

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

2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

3）很多时候用 exists 代替 in 是一个好的选择

4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

2、索引优化
看上文索引

3、数据库结构优化
1）范式优化： 比如消除冗余（节省空间。。）

2）反范式优化：比如适当加冗余等（减少join）

3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

4）拆分其实又分垂直拆分和水平拆分：

案例： 简单购物系统暂设涉及如下表：
1.产品表（数据量10w，稳定）
2.订单表（数据量200w，且有增长趋势）
3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万

垂直拆分：
解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上

水平拆分：
解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺

方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)

4、服务器硬件优化
多花钱咯。