MySQL数据库(小结)

前言

本篇博客将根据现有知识对MySQL数据库做以小结。

本篇博客将简单介绍MySQL的基本操作命令，以及数据库索引事务的相关原理和特性，及MySQL锁与MySQL相关引擎的知识。

以下博客仅作为个人学习过程的小结，如能对各位博友有所帮助不胜荣幸，后期随学习深入还会补充修改。

数据库

概念：

数据库顾名思义就是存放数据的一个集合，其官方定义为：“按照一定数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

引入的目的：

数据库是相比较本地文件存储而言的，对于文件存储，其数据可能存在安全性、查询和管理效率低、不利海量数据存储、文件在程序中操作复杂等问题，为了解决这些问题，引入了一个更加利于管理数据的软件——数据库。
同时数据库还支持远程服务，即通过远程连接使用数据库，将这种方式成为数据库服务器

数据库的分类

数据库大体可分为两类：关系型数据库和非关系型数据库

关系型数据库：是指采用了关系模型来组织数据的数据库。 简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。
常用的关系型数据库：

• Oracle：甲骨文公司产品，适合大型项目
• MySQL：甲骨文公司产品，作为轻量级数据库，开源
• SQL Server：微软产品，适用于中大型项目

非关系型数据库：不规定基于SQL实现，多数指noSQL数据库

mySQL组成

直观上mySQL可以创建多个库，每个库下含多张表，每个表中存放各种数据

多个库

一个库下含多张表

一张表中可以存放多个数据

基本操作

库操作和表操作

• 库操作相关指令

显示当前数据库：

show databases；

创建数据库：

create databases [if not exists] db_name(数据库名称)[create_specification [,create_specification] …]；

[ ] 中的为可选项
create_specification 中可设置采用的字符集编码，及制定字符集的校验规则

使用库：

use db_name(数据库名)；

删除库：

drop databases [if exists] db_name(数据库名);

• 表操作相关指令

创建表：

create table table_name(表名称)(
field(字段类型) datatype(字段名称),
field(字段类型) datatype(字段名称),
…，
);

查询表结构：

desc table_name(表名称)
表结构示例：

删除表：

drop [temporary] table [if exists] table_name [,table_name];

数据的增删查改

新增：

insert [into] table_name(字段1，…，字段N) values
(value1，value2，…，valueN),
(value1，value2，…，valueN)，
…；

查询：

– 全列查询
select * from table_name(表名称);
– 指定列查询
select 字段1，字段2… from table_name(表名称);
– 查询表达式字段
select 字段100，字段2+字段3 from table_name(表名称);
– 别名
select 字段1 别名1，字段2 别名2 from table_name(表名称);
– 去重
select distinct 字段 from table_name(表名称);
– 排序
select * from table_name(表名称) order by 排序字段;
– 按条件查询
– (1) 比较运算符 (2) between … and … (3) in (4) is null (5)模糊查询 like (6) and (7) or (8) not
select * from table_name(表名称) where 条件

修改：

update table_name(表名称) set 字段1=value1，字段2=value2… where 条件

删除：

delete from table_name(表名称) where 条件

MySQL有关权限的表

• user表：记录允许连接到服务器的用户账户信息，里面权限是全局级别
• db表：记录各个账号在各个数据库上的操作权限
• table_priv表：记录数据表级的操作权限
• columns_priv表：记录数据列级的操作权限
• host表：配合db权限表对给定主机上数据库级操作权限作更细致的控制

主键和外键

主键：主键指的是在一个属性组中能够唯一标识一条记录的属性或属性组。在一个表中只能有一个主键而且不能够重复，也不能为空值。
在数据库管理系统中对主键自动生成唯一索引，因此主键是一个特殊的索引

示例：

– 设计一个主键为id的学生表
create table student(
id int not null primary key auto_increment,
sn int unique,
name varchar(20),
qq_mail varchar(20)
);

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1,一次达到唯一标识的目的

外键：外键是用于建立与另一张表的关联，是确定另一张表中记录的字段。外键是另外一张表的主键，可以有多个且重复，也可以是空值。外键的目的在于使表中的数据保持一致性

示例：

– 创建一个成绩表，其内有一个外键 student_id 与 student 表的主键 id 关联
create table score(
id int primary key auto_increment,
goal int,
name varchar(20),
student_id int,
foreign key (student_id) references student(id)
);

外键用于关联其他表的主键或唯一键

主键与外键的区别

• 主键时能确定一条记录的唯一标识，不能有重复，不允许为空，用来保证数据的完整性，一个表主键只能有一个
• 外键用于于另一张表关联，表的外键时另一张表的主键，外键可以重复，可以是空值，用来和其他表建立关联，一个表外键可以有多个

联合查询

• 内连接

–语法
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件
select 字段 from 表1 别名1，表2 别名2 where 连接条件 and 其他条件

–示例，查询张三同学的成绩
select sco.goal from student stu，score sco where stu.id = sco.student_id and stu.name = ‘张三’;

• 外连接
  外连接分为左外连接和右外连接，如果联合查询，左侧的表完全显示就说是左外连接，右侧的表完全显示就说是右外连接

–语法，左外连接
select 字段 from 表1 left join 表2 on 连接条件；
–语法，右外连接
select 字段 from 表1 right ioin 表2 on 连接条件；

示例，查询所有学生的考试成绩
–左外连接
select * from student stu left join score sco on stu.id = sco.student_id;
–右外连接
select * from score sco right join student stu on stu.id = sco.student_id;

• 自连接
  自连接是值嵌入在其他sql语句中的select语句

–示例，查询与张三班级相同的同学
select name from student where class_id=(select class_id from student where name = ‘张三’) ;

drop、truncate、delete三者的区别

• delete 可以根据条件删除表中满足删除条件的数据，如果不指定where字句，就会删除整张表的记录
• truncate 会删除表中所有记录，并且重置所有索引，对于外键约束的表，不能使用truncate table，应该使用不带where字句的delete
• drop 会删除表中所有的记录和结构，并将表所占用的空间全部释放，换言之直接删除整张表

在速度上：一般来说drop > truncate > delete

如果要删除部分数据，使用delete 配合where字句

如果要删除表，使用drop

如果要保留表而删除表的所有数据，与事务无关用truncate、与事务有关用delete

inner join，left join，right join之间的区间

• inner join是等值连接，只返回两张表中连接字段相等的行
• left join是左连接，返回包括左表中的所有记录和右表中连接字段相等的行
• right join是右连接，返回包括右表中的所有记录和左表中连接字段相等的行

数据库三大范式

第一范式：保证每列具有原子性，列不可再分
表跟表之间一对一关系，外键设计为两个表中任一都可以

第二范式：保证表中每一列都和主键相关，非主键字段依赖主键
每个从表用外键与主表主键关联，主表对于从表之间为一对多的关系，从表对于主表是一对一的关系

第三范式：数据库中的每列都与主键有直接关系，不存在传递依赖
表与表直接为多对多的关系，通常需要设计一张中间表，设置两个外键，分别与其他两个表的主键建立关联

索引

概念

• 索引是一中特殊的文件，包含者对数据库表里面所有记录的引用指针
• 索引是一种数据结构，数据库索引是数据库管理系统中一个排序的数据结构，以协助快速查询，更新数据库表中的数据
• 索引的实现通常使用B树或B+树
• 通俗的将索引就相当于目录，一张表相当于书，数据相当于书的内容，通过索引在表中查询需要的数据
• 索引是一个文件，也需要占据物理空间

作用与使用方法

作用：更加快速的检索到需要查询的数据

使用方法：1、定义/创建索引 2、条件查询时候，where条件使用创建索引的字段

–查看索引
show index from 表名；

–创建索引
对于非主键、非唯一约束、非外键约束的字段，可以创建普通索引
create index 索引名 on 表名(字段名)；

–删除索引
drop index 索引名 on 表名

–查询
select 字段名 from 表名 where 条件(已创建索引的字段)

底层实现

MySQL索引结构——B+树
B+树源于B树，B树结构如下：

B树的特点：每个节点都可以存储多个数据，这些多个数据就划分出了一段区间，进一步数据结合者这些区间摆放，在搜索的时候就不用访问全部结点，而是直接在更加细化的区间里面去查找，从而降低查找效率

而作为MySQL的索引结构——B+树则是由B树变形得到的
B+树结构如下：

特点：

• B树中的非叶子节点也可能存储数据，但B+树的数据一定是存放在叶子节点上，非叶子节点只用来辅助进行查找
• 每一层兄弟节点之间都是相互联通的(类似于链表)，这树得遍历起来更加方便，尤其是指定区间进行查找的时候

为什么MySQL的索引底层要使用B+树

• B+树相比哈希表而言，可以处理模糊匹配的场景
• B+树相比二叉搜素树而言，深度更低，执行效率更高
• B+树相比较B树而言

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索
• 单一节点可以存储更多数据，降低查询的IO次数
• 所有的查询都需要找到叶子节点，使得查询性能更稳定
• 兄弟节点之间相连形成有序链表，便于范围内查找，增删效率更高

索引的使用场景

• 数据量大，且经常对这些列进行条件查询
• 索引的使用会降低插入和删除的效率，所以适用于数据库的插入操作以及对这些列修改操作频率低的场景
• 索引会占用额外的磁盘空间，并且创建索引的过程也相对费时，所以针对经常查询的字段创建索引

事务

概念

事务时一个不可分割的数据库操作序列，也就是数据库并发控制的基本单位；事务时逻辑上的一组操作，这组操作的各个单元要不全部执行成功，要不全部执行失败。

事务的四大特性

1. 原子性：事务是最小的执行单元，不允许分割，其中包含的多条sql语句，要不全部都执行成功，要不全部执行失败
2. 一致性：事务执行前后，数据要保持在一种合法的情况。(无论事务最后执行成功或者失败，最终数据库中的数据都是满足实际生活中的逻辑的)

如场景 用户A账户上有4000元，用户B账户上有2000元，此时A向B转账1000元，则此场景中无论事务是否执行成功，都要满足转账前后，A和B的账户金额总和为6000

3. 持久性：一个事务一旦被提交成功，对于数据库的修改就是永久的，即使数据库发生故障也不会对其有任何影响
4. 隔离性：多个事务并发执行时，事务的内部操作和其他事务的内部操作时相互隔离的，互不影响

数据库的使用

–使用
开启事务(start transaction)
try{
执行多条sql语句
提交事务(commit) 数据持久化到本地文件中，其他客户端才可见
chatch(Exception e){
事务回滚(rollback) 数据还原到开启事务的时候，开启事务之后执行的操作都会丢失
}

脏读、幻读、不可重复读

• 脏读：指一个事务A读取了另一个事务B没有提交的数据，由于某些原因，B对数据进行了修改，则A读到的数据就是不正确的
  为解决脏读问题，可以给写操作加锁，一个事务在没有提交数据之前，不能被读取
• 不可重复读：一个事务先后两次读取到的数据不一致，可能是两次读取过程中插入了一个事务原有的数据
  为解决不可重复读，可以给读操作加锁，一个事务在没有读完之前不能修改数据
• 幻读：同一个事务中，多次查询返回的结果集不一样，例如：有一个事务查询了几列数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的
• 为解决幻读，要进一步提高隔离性，即“串行化”

什么是事务的隔离级别

• Read Uncommitted(读取未提交)：最低的隔离级别，允许读取未提交的数据，可能会产生脏读、不可重复读、幻读
• Read Committed(读取已提交)：允许事务读取已提交的数据，相当于写加锁，能够避免脏读，但可能会产生不可重复的和幻读
• Repeatable Read(可重复读)：保证一个事务不会修改已经由另一个事务读取的数据，相当于读加锁和写加锁，可以避免脏读和不可重复读，但是不能避免幻读
• Serializable(串行化)：最高的隔离级别，所以的事务逐个执行，可以避免脏读、不可重复读和幻读，但资源消耗很大

MySQL的默认事务隔离级别是Repeatable Read(可重复读)

MySQL的锁

从锁的类别上：

• 共享锁：又叫读锁，当用户要进行数据的读取时，对数据加上共享锁，共享锁可以加多个
• 排它锁：又叫写锁，当用户要进行数据的写入时，对数据加上排它锁，排它锁只可以加一个，他和其他的排他锁，共享锁都互斥

从锁的粒度上：

• 行级锁：行级锁时MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁，行级锁可以大大减少数据库操作的冲突，其粒度最小，但加锁的开销也最大，行级锁分为共享锁和排它锁
  特点：开销大，加锁慢；会出现死锁；锁的粒度最小，发生锁冲突概率最低，并发度也高

• 表级锁：表级锁时MySQL中锁定粒度最大的一种，表示**对当前操作的整张表加锁，**它实现简单，资源消耗较小。
  特点：开销小，加锁块；不会出现死锁；锁的粒度大，发生锁冲突的概率最高，并发度低

• 页及锁：页级锁时MySQL中介于行级锁和表级锁之间的一种锁，因为行级锁和表级锁的优缺点，所以取折中的页级表，每次锁定相邻的一组记录
  特点：开销介于上两种锁之间，会出现死锁，粒度也介于上两种锁之间

MySQL中的死锁

• 死锁指两个或两个以上的事务互相持有对方的资源，又不主动释放造成陷入恶性循环的现象
• 解决MySQL死锁的办法：

如果不同的程序会并发的存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁的发生概率

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁发生的概率

对于非常容易产生死锁的业务部分，可以使用实际锁定粒度，通过表级锁来减少死锁发生的概率

数据库的乐观锁和悲观锁

• 悲观锁：悲观锁认为总会发生冲突，所以每次操作时会直接加锁，知道本次操作完成，事务提交。实现方式：使用数据库的锁机制
• 乐观锁：乐观锁认为不会发生冲突，所以每次操作都会执行，等到提交时检查是否违反了数据库一致性。在修改数据的时候把事务锁起来。实现方式：一般采用版本号的机制或CAS实现
• 使用场景：乐观锁适用于低冲突高并发场景，悲观锁适用于高冲突低并发场景

MySQL引擎

常见的MySQL存储引擎

• Innodb引擎：Innodb引擎提供了对数据库事务的支持；并且还提供了行级锁和外键的约束，他的设计目的是处理大数据容量的数据库系统
• MyISAM引擎：(原本MySQL默认引擎)不提供事务服务，也不支持行级锁和外键
• MEMORY引擎：所以数据都在内存中，数据的处理速度块，但安全性不高

MyISAM和Innodb的区别

1. Innodb支持事务，MyISAM不支持
2. Innodb支持外键，MyISAM不支持
3. Innodb是聚簇索引，数据文件和索引绑定在一起，必须要有主键，通过主键索引效率高(辅助索引需要查询两次，先查询到主键，再根据主键查询数据)，而MyISAM是非聚簇索引，数据文件是分离，索引保存的是数据文件的指针
4. Innodb不保存表的具体行数，select count(*) from table 的话需要全表扫描，而MyISAM用一个变量保存了整个表的行数，执行上面这个语句只需要读出变量即可
5. Innodb不支持全文索引，MyISAM支持全文索引，查询效率比较高
6. Innodb支持行级锁，MyISAM只支持表级锁
7. 如果要支持事务、外键或者是并发量高的场景，或者要保证数据完整性的场景，就要使用Innodb
8. 如果大多数都是读场景，或者插入操作，就使用MyISAM

存储引擎的选择

如果没有特别的需求一般都选择Innodb引擎

MyISAM：适用于以读写插入为主的应用，比如博客系统、新闻门户网站

Innodb：适合更新(删除)操作频率较高，或者要保证数据的完整性的场景；
Innodb的并发量高，支持事务和外键

聚簇索引和非聚簇索引

• 聚簇索引：将数据存储与索引放在一起，找到索引也就找到了数据
• 非聚簇索引：将数据存储与索引分开，索引结构的叶子结点指向数据所在行，myISAM通过key_buffer把索引缓存到内存中，当需要访问数据时，在内存中直接搜索索引，然后通过索引找到磁盘对应的数据

联合索引

MySQL中可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果要命中索引，需要按照建立索引时的字段顺序依次比对，否则无法命中索引

原因：MySQL使用索引的时候需要索引有序，假设现在建立了“name、age、school”的联合索引，那么索引排序为：先按照name排序，再把name都相等的行按age排序，再把age都相等的行按照school排序

MySQL优化

优化的八种方式

1. 选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般来说，数据库中表越小，执行的查询越快。因此在创建表的时候，为了获取更好的性能，尽量将表中字段的属性设置小一点。
例如：在定义图书类型时，如果设置成varchar(255)，那就会造成很大的浪费，可以考虑减小varchar的长度，甚至varchar都是多余的，可以将图书类型封装成一个类，因为类型的数量是固定有限的

2. 使用连接(join)来代替子查询

直接使用连接效率更高一些是因为，不使用子查询(将查询到的数据在作为一张临时表，对其进行进一步查询)的话，MySQL就不用创建临时表来完成这个逻辑

3. 使用联合(UNION)来代替手动创建的临时表

在使用UNION来创建查询的时候，可以把需要使用临时表的两条或者多条select查询合并成一个查询，在查询会话结束时，临时表会被自动删除，从而保证数据库整齐、高效。

4. 事务

为了防止操作中的某个语句块出现错误，尽量使用事务来保证

5. 锁定表

如果有成千上万的用户同时访问数据库系统，那么事务的独占性会严重影响到系统的性能，所有这种情况要自行手动加锁

6. 外键

为保证数据之间的关联性，就要用到外键

7. 使用索引

恰当的使用索引可以大幅提高查询速度

8. 优化查询语句

索引可以大量的提高查询速度，但要配合恰当的SQL语句使用

• 表记录太少时不适合用索引
• 数据重复很多的时候不适合用索引
• 经常插入删除修改操作的表减少索引的使用
• text等单个数据量的类型不适合用索引
• SQL语句中or、not in、!= (<>)、类型转换。以通配符开始的like(where A like ‘%ike’)等都会使索引失效

表数据过大如何优化

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

1. **限定数据的范围：**务必禁止不带任何限制数据范围条件的查询语句
2. **主从复制与读写分离：**将数据库拆分，主库负责写，从库负责读
3. **缓存：**使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存
   以及通过分库分表的方式优化，垂直分表和水平分表

慢查询如何优化

首先要分析原因，没有命中索引、load了不需要的数据列还是数据量过大

• 分析语句，查看是否load了额外数据，可能是查询了多余的行并且抛弃带掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写
• 分析语句的执行计划，获得其使用索引情况，之后修改语句或修改索引，尽可能提高索引命中率
• 当语句无法优化是，考虑表中数据量是否过大，过大采用横向纵向分表

为什么要尽量设定主键

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议以添加，在后续删改查时可以更快以及确保操作数据范围安全

主键使用自增ID还是UUID

自增ID

因为在Innodb存储引擎中，主键索引是作为聚簇索引存在的，即主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按顺序)，如果主键索引是自增ID，只需要不断向后插就行，而UUID是随机的，无法保证顺序需要不断的移动，导致性能下降

关于主键是聚簇索引，如果没有主键，Innodb会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式主键

以上便是对MySQL数据库的知识点小结，随着后续学习的深入还会同步的对内容进行补充和修改，如能帮助到各位博友将不胜荣幸，敬请斧正