MySQL数据库知识整理归纳

╰半夏微凉° 2022-08-12 00:14 214阅读 0赞

# 数据库创建需要考虑的内容 #

## 1.配置文件的创建 ##

如下一个基础的配置文件：

![SouthEast][]

## 2.存储引擎的选择 ##

存储引擎是mysql数据库的核心，现在常用的两个是InnoDB和MyISAM

InnoDB存储引擎支持事务，行锁设计，支持外键，InnoDB可以通过MVCC来获得高并发性，对于表存储在表空间中（即.ibd文件，有索引和表数据）。

MyISAM存储引擎不支持事务，表锁和全文索引（对一个文件，首先定义一个词库，然后列出每个词中出现的频率与位置），表又MYI(索引文件),MYD（数据文件）

可以根据需要选择合适的存储引擎。

## 3.数据库表的设计 ##

（1）注意遵从范式，不能太高也不能太低，太高会产生诸多数据的冗余，太低会使得管理起来很困难，耦合度太大

如下是1NF,2NF,3NF,BCNF区别

1NF：每个属性是不可分割的，每一列都是不可分割的数据项

2NF:消除非主属性对候选键的部分依赖，例如(学号,课程名称)->(姓名，年龄，成绩，学分)就不属于2NF

3NF:消除非主属性对候选键的传递依赖，例如（学号）->(姓名，年龄，所在学院，学院地点，学院电话)不属于3NF

BCNF：消除每个属性对候选键的传递依赖（仓库ID,存储物品ID,管理员ID）->(数量) 存在（仓库）->(管理员ID) (管理员ID)->(仓库ID)

（2）数据库中还有一些功能辅助

约束：比如主键，外键，唯一等

触发器：在insert,delete,update之前或之后自动调用SQL命令或者存储过程

视图：是一个虚表，可以当做表用。

存储过程：一个经过编译好的SQL语句集合，直接调用就好。

## 4.创建高性能索引 ##

（1）只有独立的列才能使用索引，例如select actor\_id from sakila.actor where actor\_id +1 =5，此时mysql无法解析这个方程式，无法使用索引

（2）索引的选择性（不重复索引值与数据表的记录总数的比值，比如1000条记录，不同的有900条，还有100条是空的或者重复的）越高则查询效率越高

（3）在使用前缀索引时，如果前缀的选择性能够接近0.031（即取前缀后存在不同个记录条数/总的记录条数），基本上就可用了（前缀索引的创建alter table \* add key(id(7))）

（4）有时候在多个列上建立独立的单列索引并不能提高MySQL的查询性能，多列索引就是使用表上的多个单列索引来定位指定的行。创建多列索引时要注意索引的顺序：一般将选择性最高的列放到最前列，这样开始就能够最快地过滤出需要的行。（多列索引是如何查找的？）

对于使用多个单列索引的方法主要使用union

（5）聚簇索引：在InnoDB中实际上就是在同一个结构中保存了B-Tree索引和数据行，叶子页存放数据行，节点页只包含了索引列。一个表中只能有一个聚簇索引

（什么时候只使用聚簇索引，什么时候只使用二级索引）

（6）覆盖索引：与聚簇索引有点不同的是，它的叶子中包含的是所需要的字段的值（不只是主键），而不是全行数据，覆盖索引只需要对访问，而不仅仅在where中

（7）在where中使用like时，如果查询是以通配符开头的like查询，存储引擎就无法使用索引，索引只能做最左前缀匹配的like比较

除此之外还可以对SQL语句做一些优化。

# MySQL数据库中必备的知识 #

## 1.MVCC多版本并发控制 ##

主要是针对想InnoDB这些事务性存储引擎而是用的，它不是设置简单的行级锁，还实现了多版本并发控制

方法：通过在每行的记录后面保存两个隐藏列：行的创建时间，行的过期时间（存储的系统版本号），当开始一个事务，系统版本号递增然后与这两个隐藏行比较。

例如：

select：如果事务的版本号大于或等于行的创建时间，而且小于行的过期时间（包括过期时间为定义），则可以返回结果

insert：为新插入的每一行保存当前事务的版本号

delete：将行的过期时间设置成事务的版本号

update：将新插入的行保存当前事务的版本号，以前的行更新他的过期时间为当前事务的版本号

## 2.锁与锁的问题 ##

锁的使用是为了实现并发控制，在服务器层存在表锁，存储引擎层存在行级锁。锁分为共享锁和排他锁，共享锁是给查询操作使用的，排他锁是更新，修改时使用的。

服务器为alter table之类的语句默认使用表锁，忽略具体存储引擎使用的锁机制。

行级锁在存储引擎层上，一般可以不设置。

锁的粒度越大并发程度越低

对于MyISAM引擎使用表锁，不能使用行级锁。

对于InnoDB则可以使用行级锁，同时为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持意向锁，它是表级锁，如果要给一个节点加锁，首先要给这个节点的上层加意向锁。

对于锁产生的问题

通过锁来实现事务的隔离性，使得事务并发工作，但是会产生三个问题：丢失更新，脏读，不可重复读

丢失更新：指两个用户同时更新同一数据，则其中一个更新就会丢失

脏读：指两个事务运行过程中，一个事务能够读到另一个事务未提交的数据，可以通过将隔离级别设置成提交读来解决。

不可重复读：一个事务两次读取数据库，但是在期间执行的事务修改了它的数据，导致两次读的数据不一致。可以通过设置重复读隔离级别来解决。

脏读和不可重复读的区别是：脏读读的是未提交的数据，而不可重复读读的是提交了的数据。

## 3.MySQL一些文件 ##

.pid文件：当MySQL实例启动时，会将自己的进程ID写入pid文件中，该文件由pid\_file参数控制

.frm是存储数据表的框架结构(innodb也有)，表结构文件，还存放视图的定义，如果创建一个v\_a视图，则对应产生一个v\_a.frm文件

MyISAM存储引擎中:

.MYD是MyISAM表的数据文件的扩展名

.MYI是MyISAM表的索引的扩展名

InnoDB存储引擎文件：

表空间文件：默认下为ibdata1的文件，通过设置innodb\_data\_file\_path,会将所有基于InnoDB存储引擎的表的数据都记录到该文件内，如果设置innodb\_file\_per\_table则将每个基于InnoDB存储引擎的表单独产生一个表空间即.ibd（只存放表的数据，索引，和插入缓冲，其他的还是在默认文件中）

重做日志文件：ib\_logfile0和ib\_logfile1，记录了对于InnoDB存储引擎的事务日志，可以利用这个文件来恢复数

mysql的日志文件分为四种：

错误日志：对mysql的启动、运行、关闭过程进行记录（参数为log\_error）

慢查询日志：能为SQL语句的优化带来很好的帮助，设置一个阀值（long\_query\_time），将运行时间超过阀值的记录到该日志文件中，慢查询日志还可以记录没有使用索引的语句（通过设置log\_queries\_not\_using\_indexes）

查询日志：存放所有多MySQL数据库请求的信息，无论正确与否，默认文件名为：主机名.log

二进制日志：记录对数据库执行更改的所有操作，不包括select和show这样的操作，他具有恢复和复制功能，通过log-bin来启动二进制日志，默认下不会启动

二进制日志需要用mysqlbinlog工具来查找日志文件，其他日志文件使用cat，head，tail来查看

（注意，此处的二进制日志记录所有与Mysql有关的日志记录，但是InnoDB存储引擎的重做日志只记录有关其本身的日志，二进制日志文件是在事务提交前记录，，而在事务进行中，不断有重做日志条目被写入重做日志文件）

## 4.备份与恢复 ##

“热”备份不需要任何的服务停机时间

还原:意味着从备份文件中获取数据

恢复：意味着当某些异常发生后对一个系统或其部分的拯救。