MySQL笔记

数据库

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来

数据库管理系统（DataBase Management System，DBMS）

MySQL登录

MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录。

格式1：cmd> mysql –u用户名 –p密码
例如：mysql -uroot –proot

格式2: mysql –u用户名 –p
请输入密码: root

格式2：cmd> mysql --host=ip地址 --user=用户名 --password=密码
例如：mysql --host=127.0.0.1 --user=root --password=root

SQL

数据定义语言：简称DDL(Data Definition Language)：create，alter，drop等

数据操作语言：简称DML(Data Manipulation Language)：insert，delete，update等

数据查询语言：简称DQL(Data Query Language)：select，from，where等

数据控制语言：简称DCL(Data Control Language)：定义数据库的访问权限和安全级别，及创建用户

SQL语句可以单行或多行书写，以分号结尾。

注释形式：

-- 单行注释内容
/* 多行注释 */
# 单行注释内容

NOTE: 常用功能查 AI 效率得多，NICE

注意点

删除表中所有记录使用【delete from 表名】，还是用【truncate table 表名】？

delete ：一条一条删除，不清空 auto_increment 记录数。

truncate ：直接将表删除，重新建表，auto_increment 将置为零，从新开始

原则上，互联网应用一般删除是添加 deleted 标记，实际上数据保留。

有些场景，IoT数据采集，可以适当删除数据。

约束

• 主键约束 primary key
• 唯一性约束 unique
• 非空约束 not null
• 外键约束 foreign key

查询

条件查询

having 和 where 的区别:

• 对查询结果进行分组前，将不符合 where 条件的记录过滤掉，然后再分组。

  where 后面，不能再使用聚合函数。

• 筛选满足条件的组，分组之后过滤数据。

  having 后面，可以使用聚合函数。

范围查询

模糊查询

分组聚合 group by

分页查询 limit [offset, length]

逻辑分页：将数据库中的数据查询到内存之后再进行分页。

物理分页：通过 LIMIT 关键字，直接在数据库中进行分页，最终返回的数据，只是分页后的数据。

子查询

子查询允许把一个查询嵌套在另一个查询当中。

备份与恢复

mysqldump  -u用户名 -p密码 数据库名>生成的脚本文件路径

mysql -uroot -p密码 数据库名 < 文件路径

或者在 mysql 命令行外：
source SQL脚本路径

关系

一对一：比如，一个男的只能取一个女的当老婆。

#外键唯一
ALTER TABLE husband ADD wid INT UNIQUE;
#外键约束
alter table husband add foreign key (wid) references wife(id);

一对多：比如，客户与订单，一个客户可以在商城中下多个订单。

唯一区别就是外键不唯一。

多对多：比如，学生与课程，一个学校有很多学生，学生都可以学很多课程。

需要中间表去完成多对多关系的创建，多对多关系其实就是两个一对多关系的组合。

连接

笛卡尔积

-- 查询的时候
-- 左边表中的每一条记录和右边表的每一条记录都进行组合了
select * from worker, department;

有一些数据其实是无用的，只有满足 worker.depart_id = department.id 这个条件，过滤出来的数据才是我们想要的最终结果。

select * from worker, department where worker.depart_id = department.id;

• 隐式内连接（使用 where 关键字来指定条件）

• 显示内连接（使用 inner..join..on 语句）

• 左外连接: select 字段 from 左表 left outer join 右表 .. on 条件

  ​ 先保证左边的数据全部展示，再去找右表中的数据。

• 右外连接: select 字段 from 左表 right outer join 右表.. on 条件

  ​ 保证右边表中的全部数据都展示

事务

要么成功，要么不成功回滚

当我们在 MySQL 中，如果开启了事务，那么你所有的操作都会临时被保存在事务日志中，只有遇上 commit（提交）命令的时候，才会同步到数据表中。如果遇上 rollback 和 断开连接，那么它都会去清空事务日志。

第一步：开启事务
第二步：执行你的 SQL 语句
第三步：提交事务
第四步：如果出现问题的话，则回滚事务（数据）

第一步：开启事务
start transaction;
第二步：执行你的 SQL 语句
update bankCount set money = money - 500 where name = 'cuihua';
update bankCount set money = money + 500 where name = 'banban';
第三步：如果出现问题的话，则回滚事务（数据）
commit;
// rollback;

特性

• 原子性（操作）：Atomicity 在每一个事务中，都可以看成是一个整体，不能将其再度分解，所有的操作，要么一起成功，要么一起失败。
• 一致性（数据）：Consistency 在事务执行前数据的所有状态跟执行后的数据状态应该是一样的。比如，转账前两个账户的金额总和应该跟转账后两个账户的总金额都是一样的。
• 隔离性（事务）：Isolation 多个事务之间不能相互影响，必须保证其操作的单独性，否则会出现一些串改的情况，执行的时候应该保持隔离的状态。
• 持久性（结果）：Durability 如果我们的事务执行成功之后，它将把数据永久性存储到数据库中，哪怕设备关机之后，也是能够保存下来的。

不同隔离级别的问题

• 脏读：其中一个事务读取到了另外一个事务中的数据（尚未提交的数据）。
• 不可重复读：一个事务中两次读取数据的时候，发现数据的内容不一样。一般是 update 操作引发。
• 幻读：一个事务中，两次读取数据的时候，发现数据的数量不一样。一般是 insert 或者 delete 操作引起。

隔离级别

• read uncommitted 读未提交
• read committed 读已提交：解决脏读
• repeatable read 可重复读（默认）：解决不可重复读
• serializable 串行化：解决幻读

用户设置

创建、授权、修改等操作。

MySQL 结构

MySQL从物理结构上可以分为：

• 日志文件
• 数据索引文件

MySQL在Linux中的数据索引文件和日志文件通常放在 /var/lib/mysql 目录下。

日志

常用的日志文件包括：

1. 错误日志: 错误日志记录了运行过程中遇到的所有严重的错误信息，以及 MySQL 每次启动和关闭的详细信息。
2. 二进制日志: binlog记录了数据库所有的ddl语句和dml语句
3. 查询日志：记录用户的所有操作，影响使用性能，一般不启用
4. 慢查询日志：记录执行时间超过long_query_time秒的所有查询
5. 事务Redo日志
6. 中继日志

数据索引

ibdata 文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。

InnoDB存储引擎的数据文件

• .frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息
• .ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。

数据处理：

• 联机事务处理 OLTP (On-Line Transaction Processing)：基本使用，非海量数据
• 联机分析处理 OLAP (On-Line Analytical Processing)：数据量大，实时性要求不高

存储形式：

• 行式：不是适合海量数据
• 列式（各列独立存储，利于压缩）：低延迟、查找高效，不适合删除更新频繁的场景

架构

SQL的执行逻辑：

缓存的伏笔

查询缓存默认是关闭的状态，多数情况下，不建议使用MySQL查询缓存，为什么呢？

因为查询缓存往往弊大于利。

• 成本高：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。

• 命中率不高：对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

• 有更好的缓存工具：redis、memcache...

InnoDB

Buffer

Buffer Pool 中数据以页为存储单位，其实现数据结构是以页为单位的单链表。默认大小为128M。LRU 算法缓冲热点数据。

Change Buffer 用于加速非热点数据中二级索引的写入操作。由于二级索引数据的不连续性，导致修改二级索引时需要进行频繁的磁盘 IO 消耗大量性能，Change Buffer 缓冲对二级索引的修改操作，同时将写操作录入 redo log 中，在缓存到一定量或系统空闲时进行 merge 操作将修改写入磁盘中，从而达到减少磁盘 I/O 的目的。

二级索引就是辅助索引，除了聚簇索引之外的所有索引都是二级索引。

聚簇索引是也叫聚集索引，有的也叫索引组织表。

InnoDB 使用 Log Buffer 来缓冲日志文件的写入操作。事务提交的时候必须将操作写入日志中，此时日志文件若未落盘而系统崩溃，则相关操作将丢失而无法恢复。

InnoDB 提供三种 Log Buffer 数据落盘方式

0：按秒写，按秒刷。每秒调用 write() 写入 OS Buffer 并调用 flush() 刷入磁盘。

1：实时写，实时刷。每次事务提交都调用 write() 写入 OS Buffer 并调用 flush() 刷入磁盘。

2：实时写，延迟刷。每次事务提交都调用 write() 写入 OS Buffer，但每秒调用 flush() 刷入磁盘。

磁盘文件

在磁盘中，InnoDB 将所有数据都存放在一个空间中，称为表空间（Tablespace）。表空间由段 （Segment）、区（extent）、页（Page）组成。

区（Extend）是由连续的页组成的空间，大小固定为 1MB，由于默认页大小为 16K，因此一个区默认存储 64 个连续的页。

页（Page）是 InnoDB 的基本存储单位，每个页大小默认为 16K。操作系统管理磁盘的最小单位也是页，是操作系统读写磁盘最小单位，Linux中页一般是4K。所以InnoDB从磁盘中读取一个数据页时，操作系统会分4次从磁盘文件中读取数据到内存。写入也是一样的，需要分4次从内存写入到磁盘中。

表空间是 InnoDB 物理存储中的最高层，目前的表空间类别包括：

• 系统表空间（System Tablespace）

• 独立表空间（File-per-table Tablespace）

• 通用表空间（General Tablespace）

• 回滚表空间（Undo Tablespace）

• 临时表空间（The Temporary Tablespace）

自适应哈希索引(AHI)

是建立在索引之上的索引。AHI 所作用的目标是索引频繁查询的数据页和索引页，而由于数据页是聚簇索引的一部分，因此 AHI 是建立在索引之上的索引。

数据落盘

修改数据库中页时，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时都触发，而是通过一种称为CheckPoint的机制刷新回磁盘。

通过定期批量写入磁盘的方式提高写入效率减少磁盘 IO。

内存里缓冲池中的数据页要完成持久化通过两个流程来完成：

• 脏页落盘：要写的数据
• redo log落盘：回滚操作记录

InnoDB采用了Write Ahead Log（WAL）策略和Force Log at Commit机制实现事务级别下数据的 持久性：

• Force Log at Commit机制：要求当一个事务提交时，所有产生的日志都必须刷新到磁盘上。
• Write Ahead Log（WAL）策略：要求数据的变更写入到磁盘前，首先必须将内存中的日志写入到磁盘。

为了确保每次日志都写入到redo日志文件，在每次将redo日志缓冲写入redo日志后，调用一次 fsync 操作，将缓冲文件从文件系统缓存中真正写入磁盘。

Checkpoint 检查什么？

• 缩短数据库的恢复时间：当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的redo日志进行恢复，这样就大大缩短了 恢复的时间。
• 缓冲池不够用时，将脏页刷新到磁盘。
• redo日志不可用时，刷新脏页

何为双写？

• 写到内存中的double write buffer
• 写到物理磁盘上共享表空间中连续的128个页，即2个区（extent），大小同样为2MB

崩溃恢复流程：

索引

Hash表：

• 不支持范围快速查找，范围查找时还是只能通过扫描全表方式。
• 数据结构比较稀疏，不适合做聚合，不适合做范围等查找。

使用场景，对查询并发要求很高，K/V内存数据库，缓存。

二叉查找树：

• 查找退化问题，IO次数很多

红黑树：

• 时间复杂度和树高相关：树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作
• 平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率极差。

B树，多叉树优化：

• 由于每个节点都存储数据，缓存空间占用大
• 叶子节点也不是相连的，所以不支持范围查询

B+树，只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最 底层的叶子节点形成了一个双向有序链表。因此优化了B树的缺点。

数据段称为 Leaf node segment，索引段称为 Non-Leaf node segment

索引优化可选方法

• 覆盖索引

• Index Condition Pushdown

• 频繁出现在where条件中的列，建议创建组合索引

• 遵循索引最左前缀匹配原则，容易匹配的小范围匹配条件写左边，组合索引应该把频繁用到的列、区分度高的值放在左边

• 表记录很少不需创建索引

• 一个表的索引个数不能过多

• 频繁更新的字段不建议作为索引

• 区分度低的字段，不建议建索引

• 在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段：主键索引树一个页节点就 16K

• 不能使用无序的值作为索引：

• 更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间

• 尽量创建组合索引，而不是单列索引：1个组合索引等同于多个索引效果，节省空间；并且可以使用覆盖索引优化。

• LIMIT优化：一般会先排序，然后 limit 限制记录行数

• 可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询 快。

• 小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数

• 避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。如果对索引使用不等于的操作将会导致索引失效，进行全表扫描。

• JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。

• WHERE条件中尽量不要使用not in语句（建议使用not exists）

• 合理利用慢查询日志、explain执行计划查询、show profile（Query Profiler工具）查看SQL执行时的资源使用情况。

MySQL 锁

按功能分：

• 共享锁(shared lock)，即读锁(read lock)
• 排他锁(exclusive lock)，也叫写锁(write lock)

按粒度分：

• 全局锁：锁整database，由MySQL的SQL layer层实现的。
• 表级锁：锁某table，由MySQL的SQL layer层实现的。
• 行级锁：锁某行数据，也可锁定行之间的间隙，由某些存储引擎实现（InnoDB）

InnoDB行锁是通过给索引上的索引项加锁来实现的。

因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。

性能

影响因素：

• 数据库表结构【对性能影响最大】
• 低下效率的SQL语句
• 超大的表
• 大事务
• 数据库配置：最大连接数，连接超时，线程缓存，查询缓存，排序缓存，连接查询缓存
• 数据库整体架构

指标：

• 响应时间：用户从客户端发出请求，并得到响应，以及展示出来的整个过程的时间
• 吞吐量 (TPS) ：每秒事务数。一个事务是一个客户端向服务器发送请求，然后服务器做出响应的过程。在没有遇到性能瓶颈时，TPS = 并发数/响应时间
• 并发用户数：同一时间点，可请求服务器的用户数
• 资源使用率：CPU 占用率、内存使用率、负载、网络IO

响应时间越短、同时承受的并发数越多、吞吐量会越大、占用的资源越少，则表明系统性能越好。

表结构优化：

• 将字段很多的表分解成多个表
• 因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
• 增加中间表
• 增加冗余字段：合理的加入冗余字段可以提高查询速度。表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

服务器优化：

• 设置足够大的 innodb_buffer_pool_size ，将数据读取到内存中，物理内存的50%~80%
• 使用足够大的写入缓存 innodb_log_file_size，降低磁盘写入次数
• 关闭不需要的log（通用查询日志、慢查询日志、错误日志），降低磁盘写入次数
• 每提交1次事务同步写到磁盘中，sync_binlog=1，可以设置为n
• 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。innodb_max_dirty_pages_pct=30，推荐值为25%~50%。
• 后台进程最大IO性能指标。innodb_io_capacity=200，默认200，如果SSD，调整为5000~20000。
• 全量日志建议关闭。默认关闭。

my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：

# 01-缓冲区，将数据保存在内存中，保证从内存读取数据。建议innodb_buffer_pool_size设置为
总内存大小的3/4或者4/5.
innodb_buffer_pool_size=
# 02-降低磁盘写入次数。推荐 innodb_log_file_size 设置为 0.25 *
innodb_buffer_pool_size
innodb_log_file_size=
# 03-表示缓冲池字节大小。推荐值为物理内存的50%~80%。
innodb_buffer_pool_size=
# 04-用来控制redo log刷新到磁盘的策略。
innodb_flush_log_at_trx_commit=1
# 05-每提交1次事务同步写到磁盘中，可以设置为n。
sync_binlog=1
# 06-脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。
innodb_max_dirty_pages_pct=30
# 07-后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200
# 08-指定innodb共享表空间文件的大小。
innodb_data_file_path
# 09-慢查询日志的阈值设置，单位秒。
long_qurey_time=3
# 10-mysql复制的形式，row为MySQL8.0的默认形式。
binlog_format=row
# 11-调高该参数则应降低interactive_timeout、wait_timeout的值。
max_connections=200
# 12-过大，实例恢复时间长；过小，造成日志切换频繁。
innodb_log_file_size
# 13-全量日志建议关闭。默认关闭。
general_log=0

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能（数量大于频率）等。

对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好。