MySQL基础总结

文章目录

MySQL基础总结0、安装MySQL环境1、数据库基本知识2、操作数据库的基本命令3、数据库操作3.1 SQL语句分类DDL: 数据库操作DDL: 表操作DML：操作表数据DQL: 数据查询 3.2 事务 4、索引5、页6、锁机制7、MySQL日志模块8、MySQL优化8.1 存储引擎8.2 索引优化8.3 查询优化8.4 分表8.5 架构优化参考文献：

0、安装MySQL环境

https://mp.weixin.qq.com/s?__biz=Mzg2NTAzMTExNg==&mid=2247484182&idx=1&sn=6a04b10ebac95da1a66abcefa1fa442c&scene=19#wechat_redirect

1、数据库基本知识

字段数据类型

2、操作数据库的基本命令

net start mysql # 启动mysql(服务名称)服务 net stop mysql # 关闭服务 # 修改密码 update user set password = password('123456')where user='root'; flush privileges; #刷新数据库 show databases; #显示所有数据库 use dbname; #打开某个数据库 show tables; #显示当前数据库下的所有表 describe user;显示表mysql数据库中的user列信息 create database name; #创建数据库 exit; #退出MySQL ? 命令关键词 #寻求帮助 -- 表示注释

3、数据库操作

3.1 SQL语句分类

DDL(数据定义语言) ：CREATE、DROP、ALTER（定义和管理数据对象）

DML(数据操作语言)：INSERT、UPDATE、DELETE（用于操作数据库对象中包含的数据）

DQL(数据查询语言)：SELECT（用于查询数据库对象）

DCL(数据控制语言)：GRANT、COMMIT、ROLLBACK（用于管理数据库的语言，包括管理权限和数据更改）

DDL: 数据库操作

create database [if not exists] 数据库名; drop database [if exists] 数据库名; show databases; use 数据库名;

DDL: 表操作

# 创建表 create table [if not exists] `表名`( `字段名1` 列类型 [属性][索引][注释], `字段名2` 列类型 [属性][索引][注释], #... `字段名n` 列类型 [属性][索引][注释] )[表类型][表字符集][注释]; # 修改表 ALTER TABLE 旧表名 RENAME AS 新表名; #修改表名 ALTER TABLE 表名 ADD 字段名 列属性[属性]; #添加字段 ALTER TABLE 表名 MODIFY 字段名 列类型[属性] ALTER TABLE 表名 MODIFY 旧字段名 新字段名 列属性[属性] #修改字段 ALTER TABLE 表名 DROP 字段名; #删除字段

MySQL的数据表类型（Engine）：MyISAM, InnoDB

MyISAM: 节约空间及相应速度；

InnoDB: 安全性、事务处理和多用户操作表；

设置数据表字符集：

通过命令设置：CHARSET=utf8根据MySQL数据库配置文件my.ini中的参数设定

DML：操作表数据

外键概念：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。

主表：以另一个关系的外键做主关键字的表；从表：有此外键的表

外键作用：保持数据的一致性，完整性，使两张表形成管理。

# 添加数据 INSERT INTO 表名[(字段1，字段2，...)] VALUES("值1"，"值2"，...) # 修改数据 UPDATE 表名 SET 列名='值' [WHERE 条件] # 删除数据 DELETE FROM 表名 [WHERE 条件] # 清空表，用于完全清空表数据，但表结构、索引、约束等不变 TRUNCATE [TABLE] 表名;

DQL: 数据查询

SELECT 语法

SELECT [ALL | DISTINCT] -- 去重复 {* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]} FROM table_name [as table_alias] [left | right | inner join table_name2] -- 联合查询 [WHERE ...] -- 指定结果需满足的条件 [GROUP BY ...] -- 指定结果按照哪几个字段来分组 [HAVING] -- 过滤分组的记录必须满足的次要条件 [ORDER BY ...] -- 指定查询记录按一个或多个条件排序 [LIMIT 0,5]; -- 指定当前页面起始记录，页面的总记录数 # 指定查询字段 SELECT * FROM 表名; SELECT 列1,列2,FROM 表名;

AS: 取别名；

DISTINCT: 去除重复项；

WHERE：条件查询

条件运算符：>、>=、<、<=、=、<=>[判断等于，还可以判断NULL]、!=、<>[判断不等于]

逻辑运算符：

模糊查询：比较操作符

LIKE通配符：%（代表0到任意个字符），_(代表一个字符)

连接查询：如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询

SELECT 查询列表 FROM 表1 别名1 【连接类型】 JOIN 表2 别名2 ON 连接条件 【where 分组前筛选条件】 【group BY 分组列表】 【having 分组后筛选条件】 【order BY 排序列表】 ;

内连接 inner join 查询两个表中的结果集中的交集外连接 outer join 左外连接 left join 以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充右外连接 right join 以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充 通过等值条件连接和非等值条件连接两（多）个表。

子查询：数据表与自身进行连接查询。

需求：数据表中必须有关联的父id和子id，将表通过父子id进行关联查询。

排序：ORDER BY 指定列或结果集进行排序

SELECT 查询列表 FORM 表 [where 筛选条件] ORDER BY 排序列表 [ASC | DESC]; ASC：升序DESC：降序排序列表可以是单个字段、多个字段、别名、函数、表达式

分页：LIMIT 0, 5

公式：limit (pageNo-1)*pageSzie,pageSzie

单行函数

SELECT 函数名(参数列表) [FROM 表]; sum：求和avg：平均值max：最大值min：最小值count：计算个数

3.2 事务

一条语句或多条语句组成一个执行单元，一组sql语句要么都执行要么都不执行

事务概念

事务就是将一组SQL语句放在同一批里执行。如果全部语句执行成功，则事务提交，否则事务将回滚。MySQL事务处理只支持InnoDB和BDB数据表类型。

事务ACID原则：原子性、一致性、隔离性、持久性

原子性：一个事务的所有操作要么全部完成，要么全部不完成。一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的。隔离性：并发访问数据库时，一个用户不被其他事务所干扰，各并发事务之间数据库是独立的。持久性：一个事务提交后，该事务对数据库所做的更改要持久保存到数据库中。

并发事务带来问题：

脏读：当一个事务访问数据并且对数据进行修改，而且还没提交到数据库中，这时另一个事务也访问了这个数据，并使用了该数据。因为这个数据是还没有提交的数据，另一个事务读到的这个数据是“脏数据”。丢失修改：指在一个事务读取一个数据时，另一个事务也访问了该数据，第一个事务修改了这个数据，另一个也修改了这个数据，这样第一个事务的修改结果就被丢失，因此称为丢失修改。不可重复读：指在一个事务内多次读同一数据，在这个事务还没有结束时，另一个事务也访问了该数据，在第一个事务的两次访问中间，第二个事务的访问修改导致第一个事务两次读取的数据不一样，因此称为不可重复读。幻读：一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样

不可重复读与幻读的区别：

不可重复读：重点是修改。幻读：重点在与新增或删除。

事务的隔离级别

设置隔离级别来解决并发问题。

读未提交：允许脏读取 。当一个事务开始写操作，则另一个事务不允许同时进行写操作，但允许读此行数据。读已提交：允许不可重复读取，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。可重复读：不允许不可重复读和脏读取，但是有时可能出现幻读。读取数据的事务会禁止写事务（但允许读事务），写事务禁止其他任何事务。序列化：提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个的执行，但不能并发执行。

注：事务的隔离等级越高，数据的完整性和一致性越有保证，但对并发操作影响也越大。MySQL事务的默认隔离级别是可重复读。

视图

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。

特点：简化sql语句、提高sql的重用性、保护基表的数据提高安全性。

CREATE VIEW 视图名 AS 查询语句;

4、索引

索引是帮助MySQL高效获取数据的一种有序的数据结构。

索引类型：哈希表、完全平衡二叉树、B树、B+树

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据表中的特定信息，如同一本书的目录一样，可以加快查询的速度。InnoDB存储引擎的所有底层实现数据结构是B+树，所有数据都存储在B+树中。

MySQL索引使用的数据结构

BTree索引：使用的是B树中的B+树。

哈希(hash)索引：使用哈希表，单条记录查询时，使用哈希索引，查询性能最快。

为什么底层数据结构使用B+树，而不是B树

B+树是B树的变种，B+树的非叶子节点只用来存储索引，不存储数据，所有数据都保存在叶子节点；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。B+树的内部节点没有指向关键字具体信息的指针，因此其内部节点相对B树更小，同样的空间可以读入更多的节点，所有B+树的磁盘读写代价更低。

聚簇索引和非聚簇索引

聚簇索引：也称为主键索引，其索引树的叶子节点中存储的是整行数据，表中行的物理顺序与键值的索引顺序相同。非聚簇索引（普通索引）：叶子节点内容是主键的值，非主键索引也被称为二级索引。

各种索引的区别

普通索引(INDEX)：最基本的索引，没有任何限制唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。主键索引(PRIMARY)：它是一种特殊的唯一索引，不允许有空值。全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。用于优化模糊查询效率。联合索引：为了更多的提高mysql效率可建立组合索引，遵循"最左前缀"原则。 索引的缺点 1.虽然索引大大提高了查询速度，同时却会降低更新表的速度， 如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件 2.建立索引会占用磁盘空间的索引文件。 一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引， 索引文件的会膨胀很快。

MySQL回表

对应普通索引查询时，需要先搜索普通索引树，得到其对应的主键值，在通过主键索引树搜索一次，找到结果，可以通过覆盖索引，避免回表。

覆盖索引

索引包含所有需要查询的字段的值。

如果普通索引树上的查询已经可以直接提供结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

索引的最左前缀原则

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀（可以是联合索引的最左N个字符，也可以是字符串索引的最左M个字符），就可以利用索引加快查询的速度。是常见的MySQL性能优化手段。

注：联合索引又叫复合索引，即一个覆盖表中两列或者以上的索引

索引下推

在MySQL5.6前，只能根据最左前缀查询到ID开始一个个回表，到主键索引上找出数据行，再对比字段值。MySQL5.6引入索引下推优化，联合索引前提下可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤不满足条件的记录，减少回表次数，提高查询效率。

MySQL常见的存储引擎

InnoDB：支持事务、支持行级锁和表级锁（默认行级锁）、支持外键、可以处理巨大数据量时的最大性能设计的存储引擎、不支持全文索引（FULLTEXT）。MyISAM：不支持事务、不支持外键、支持表级锁、支持全文索引。

MySQL基本逻辑架构图

​ MySQL逻辑架构包括Server层和存储引擎层。其中Server层包括连接器、分析器、优化器、执行器；存储引擎包括多种支持的存储引擎：

连接器：验证客户端权限，建立和断开MySQL连接；分析器：进行SQL语句的语法分析；优化器：选择索引，生成具体的SQL语句执行计划；执行器：操作存储引擎，执行SQL，返回执行结果；存储引擎层：各个不同的存储引擎都提供一些读写接口来操作数据库。

5、页

MySQL的基本存储结构是页（记录都存在页里面）

页的特性

（1）各个数据页可以组成一个双向链表

（2）每个数据页中的记录可以组成一个单向链表

（3）每个数据页都会为存储在里面的记录生成一个页目录，在通过主键查找某条记录时可以在页目录中使用二分法快速定位到对应的槽，然后遍历该槽对应分组中的记录即可快速找到指定的记录；以非主键作为搜索条件时，只能从最小记录开始依次遍历单链表中的每条记录。

一条普通的查询语句在页中搜索过程

遍历数据页组成的双向链表，找到所在的页：

主键：二分法查找相应的记录

非主键：遍历所在页的单链表，查找相应的记录

6、锁机制

MyISAM和InnoDB存储引擎使用的锁：

MyISAM：表级锁（table-level locking）InnoDB：行级锁（row-level locking）和表级锁，默认行级

表级锁和行级锁区别：

表级锁：对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低。行级锁：只针对当前操作的行进行加锁，加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行锁又可以分为独占锁和共享锁。 独占锁（排他锁：Exclusive Lock）：共享锁（S锁：Shared Lock）

InnoDB存储引擎的锁算法

Record lock：单个记录上的锁Gap lock：间隙锁，锁定一个范围，不包括记录本身Next-key lock：record+gap锁定一个范围，包含记录本身

悲观锁和乐观锁

悲观锁：利用数据库的锁机制，在整个数据处理过程中都加入了锁，以保证排他性。乐观锁：在操作数据的时候进行一个比较，按照当前事务中的数据和数据库表中的该数据是否一致来决定是否执行本次操作。

乐观锁的ABA问题：

问题描述: 指在当前事务读取该行数据时为A，经过别的事务修改为B，但是当前事务要更新数据的时候，该行数据又被其他事务修改为A，事实上该行数据是发生改变的，存在并发问题。

解决：通过基于数据版本记录机制来解决，也就是为数据增加一个版本标识，读取数据时，将版本号一同读出，之后更新时，对此版本号加1，根据当前事务的版本号与数据库中的版本号对比，来决定是否更新数据。

也可通过时间戳机制来解决ABA问题，通过时间戳来记录当前数据行变化。

池化设计思想

数据库连接的本质是一个Socket连接，数据库服务端需要维护一些缓存和用户权限信息，因此会占用一些内存。我们可以将数据库连接池看做是维护数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。

在连接池中，创建连接后，将其放在池中，再次使用时，不需要建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。连接池减少了用户必须等待建立与数据库的连接的时间。

7、MySQL日志模块

在MySQL的使用中，更新操作是很频繁的，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录的成本都很高。

日志模块：我们的更新操作是先写日志，在合适的时间才回去写磁盘，日志更新完就将执行的结果返回给客户端。

MySQL的日志模块主要有redo log（重做日志）和binlog（归档日志）

redo log：InnoDB存储引擎特有的日志模块，是物理日志，记录某个数据页上做了哪些修改。InnoDB的redo log是固定大小的。binlog：Server层自带的日志模块，binlog是逻辑日志，记录本次修改的SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。binlog日志文件的格式： statement格式：binlog记录的是完整的SQL语句，优点是日志文件小、性能较好；缺点是准确性差，例如函数now()row格式：binlog中记录的是数据行的实际数据变更，优点是数据记录准确，缺点是日志文件较大。mixed格式：前两种的混合模式。

日志模块更新数据库：

通过redo log和binlog的两阶段提交，可以确保数据库异常崩溃之后数据的正常恢复。

8、MySQL优化

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

8.1 存储引擎

Innodb ：数据完整性，并发性处理，擅长更新，删除。

MyISAM：高速查询及插入。擅长插入和查询。

8.2 索引优化

关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。

普通索引,index：对关键字没有要求。 唯一索引,unique index：要求关键字不能重复。同时增加唯一约束。 主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。 全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。 索引的最左前缀原则：让选择性最强的索引列放在前面。在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀（可以是联合索引的最左N个字符，也可以是字符串索引的最左M个字符），就可以利用索引加快查询的速度。覆盖索引：索引包含所有需要查询的字段的值。如果普通索引树上的查询已经可以直接提供结果，不需要回表操作，直接返回结果，可以提高查询效率。

索引使用条件

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；对于中到大型的表，索引就非常有效；但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术

索引的优点

大大减少了服务器需要扫描的数据行数。将随机 I/O 变为顺序 I/O。

8.3 查询优化

使用Explain进行查询语句分析

注：Explain执行计划 可以通过在sql语句前使用 explain，来获取该查询语句的执行计划， 而不是真正执行该语句。分析你的查询语句或是表结构的性能瓶颈。 通过explain我们可以获得以下信息： 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 每张表有多少行被优化器查询 比较重要的字段： - select_type : 查询类型，有简单查询、联合查询、子查询等 - key : 使用的索引 - rows : 扫描的行数 优化数据访问，减少请求的数据量、减少服务器端扫描的行数。重构查询方式，切分大查询、分解大连接查询

8.4 分表

水平切分

水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分

垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

8.5 架构优化

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Hxwb30NB-1601883388154)(MySQL-数据库.assets/image-20201005152859154.png)]

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QuwZ1iMB-1601883388155)(MySQL-数据库.assets/image-20201005153003142.png)]

参考：MySQL优化十大技巧

参考文献：

1、【狂神说Java】MySQL最新教程通俗易懂

2、https://gitee.com/SnailClimb/JavaGuide/blob/master/docs/database/MySQL.md

3、牛客专刊：我是祖国的花朵

4、学习MySQL这一篇就够了