MySQL

最新推荐文章于 2023-09-05 07:16:27 发布

奄奄不息

最新推荐文章于 2023-09-05 07:16:27 发布

阅读量3.3k

点赞数

分类专栏：笔记文章标签： mysql 数据库缓存

本文链接：https://blog.youkuaiyun.com/qq_41209741/article/details/121011203

版权

笔记专栏收录该内容

1 篇文章

订阅专栏

文章目录

MySQL特征：
MySQL逻辑架构
- 一条SQL语句的执行过程
1. 数据库优势，为啥需要数据库？
2. 数据库的三大范式
- 2.1 范式的优点和缺点
- 2.2 反范式的优点和缺点
- 2.3 混用范式化和反范式化
3. 数据库中有权限的表
4. SQL语言
- 4.1 SQL语句分类
- 4.2 超键，候选键，主键外键
- 4.3 关联查询
- 4.4 子查询
- 4.4 表的约束
- 4.5 drop，delete，truncate的区别
- 4.6 常见SQL语句
mysql中常用的三种插入数据的语句:
5. 数据库的数据格式
- 5.1 整数类型
- 5.2 小数类型
- 5.3 日期类型
- 5.4 CHAR和VARCAHR
- 5.5 ENUM枚举
- MySQL存储IP地址
6. 索引
- 6.1 索引算法？
- 6.2 建立索引原则
- 6.3 B+树在满足聚簇索引和覆盖索引时不需要进行回表查询
- 6.3 如何避免回表查询
- - 联合索引
- 6.4 索引的数据结构
- 6.5 索引分类
- 6.6 InnoDB和MyISAM的区别
- - 适用范围
7. 引擎
- innodb引擎的四大特性
8. 事务
- 8.1 事务的ACID属性
- 事务无隔离性的问题
- 事务的隔离机制
MVCC
- 快照读和当前读
- 基本概念：undo log，版本链，readview
- - ReadView
- 可重复读级别解决幻读
事务ACID的底层原理
- 原子性
- 持久性
- 隔离性
- 一致性
9. 锁
- 9.1 锁和隔离级别的关系
- 9.2 锁的分类
- 9.4 InnoDB存储引擎的锁算法
- 9.5 什么是死锁，怎么解决
- 9.6 悲观锁和乐观锁
10. SQL优化
- 10.1 执行计划
- 10.2 慢查询日志
- 10.3 具体优化步骤
- 10.4 为什么要设置主键，自增id还是uuid
- 10.5 为什么不推荐使用外键
- 10.6 字段要设置not null，并提供默认值
11. 数据库表优化
- 11.1 将字段很多的表分解成多个表
- 11.2 增加中间表
- 11.3 增加冗余字段
- 11.4 MySQL数据库cpu飙升处理方法
- 11.5 大表优化
- - 分库分表的方式进行优化
  - - 垂直分区
    - 水平分区
12. MySQL复制原理以及流程
- 12.1 主从复制的作用
- 12.2 主从复制解决的问题
- 12.3 MySQL主从复制工作原理

MySQL特征：

MySQL是开源的
支持大型数据库。可以处理拥有上千万条记录的大型数据库
MySQL可以用于多个系统，并且支持多种语言
MySQL支持定制，可以自己修改源码来开发自己的MySQL

MySQL逻辑架构

数据库逻辑结构共分为4层：连接层、业务逻辑处理层、存储引擎层、数据存储层

外部程序
连接层
- 提供客户端和连接服务，管理缓冲用户连接、线程处理等需要缓存的需求
业务逻辑处理层
- SQL接口：接受用户的SQL命令，并返回用户需要查询的结果
- 解析器：SQL命令被解析器解析验证，解析器是一个很长的脚本，将SQL命令分解成数据结构，并将这个结构传递到后续步骤，以后的SQL语句的传递和处理就是基于这个结构，如果在分解过程中不合理就说明这个SQL语句是错误的。
- 查询优化器：对SQL语句进行查询优化，它使用“选取–投影–连接”策略进行查询。
- 缓存和缓冲池：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个而过缓存机制由一系列小缓存组成。比如表缓存、记录缓存、Key缓存、权限缓存等
存储引擎层：数据的存储和提取，默认是InnoDB
数据存储层：将数据存储到磁盘上，并协同存储引擎对数据进行读写操作。

插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离，这种架构可以根据业务的需求来选择合适的存储引擎。

一条SQL语句的执行过程

应用程序与数据库服务器建立一个连接，权限校验和身份验证
SQL接口接收用户的SQL语句
解析器解析验证SQL语句
查询优化器对SQL语句进行优化
执行器执行语句，从存储引擎返回数据

1. 数据库优势，为啥需要数据库？

数据存储在内存，读取方便，但是不能永久存储。
数据存储在文件，可以永久保存，但是有频繁的IO操作，数据读取不方便。
数据存储在数据库，可以永久保存，SQL语句查询方便效率高，管理数据方便。

2. 数据库的三大范式

第一范式：每一列都不可再拆分
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能依赖主键列的一部分
第三范式：在第二范式的基础上，非主键列只依赖于主键，而不能依赖其他非主键列

2.1 范式的优点和缺点

优点

重复数据很少或者没有，修改的数据也更少
表通常更小，可以更好地放在内存里，执行操作会比较快
很少有多余的数据意味着检索列表时更少的使用DISTINCT或者GROUP

缺点：范式的缺点通常是需要关联

2.2 反范式的优点和缺点

优点：所有的数据都在一张表里，不需要关联
缺点：表比较大，更新数据比较麻烦，会有很多重复的数据，占用的内存会比较多

2.3 混用范式化和反范式化

在不同的表中存储相同的特定列

3. 数据库中有权限的表

user表：记录可以连接这个服务器的用户信息
db表：记录各个用户对于各个数据库的操作权限信息
table_priv表：记录数据表级的操作权限
columns_priv表：记录数据列级的操作权限
host表：和db包配合，对给定服务器上的数据库操作权限做更细致的控制

4. SQL语言

4.1 SQL语句分类

数据定义语句(DDL)：drop，create，alter，truncate
数据操作语言(DML)：insert，delete，update
数据查询语言(DQL)：select
数据控制语言(DCL)：grant，remoke，commit，rollback

4.2 超键，候选键，主键外键

超键：在关系中能唯一标识一个元组的属性集称为超键，超键可以是一个属性，也可以是多个属性组合在一起。超键包含候选键和主键。
候选键：最小超键，没有冗余元素的超键
主键：唯一且不为空的属性，一个关系中只能有一个主键
外键：一个关系中存在另一个表的主键

4.3 关联查询

交叉连接（CROSS JOIN）
SELECT * FROM A,B,C;或者SELECT * FROM A CROSS JOIN B CROSS JOIN C#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义。返回的结果是所有表的行数的乘积。
内连接（INNER JOIN）
很少使用内连接（INNER JOIN）SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN
内连接又分为等值连接，不等值连接和内连接，内连接就是两边的表都是自己
外连接(LEFT/RIGHT JOIN)
外连接分为左连接和右连接
- 左连接又称为左外连接（LEFT OUT JOIN）。返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。
- 右连接又称为右外连接（RIGHT OUT JOIN）。恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值。
联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B
就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并，UNION ALL不会合并重复的记录，UNION ALL效率更高

全连接（FULL JOIN）返回左表和右表中的所有行。当某行在另一表中没有匹配行，则另一表中的列返回空值。

4.4 子查询

子查询是单行单列，父查询用=，<，>等运算符
子查询是多行单列，父查询用in
子查询是多行多列，不能用于where条件，用于select子句中作为子表

-- 1) 查询出2011年以后入职的员工信息
-- 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。
select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    

-- 使用表连接：
select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'

4.4 表的约束

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY：用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，必须是它指向的那个表中那一列的值之一
CHECK：用于控制字段的范围

4.5 drop，delete，truncate的区别

	delete	truncate	delete
类型	DML	DDL	DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表中的全部或者部分数据行	表结构还在，删除表中的所有数据	表结构已不在，删除表中的全部数据
删除速度	慢，逐行删除	快	最快

4.6 常见SQL语句

show processlist

可以用来查看有哪些用户连接了数据库，如果有异常用户可能代表着你的数据库被入侵了

查看表结构

desc table_name;
创建表
create table users (
id int,
name varchar(20) comment ‘用户名’,
password char(32) comment ‘密码是32位的md5值’,
birthday date comment ‘生日’
) character set utf8 engine MyISAM;

在表中插入记录

insert into users values(1,‘a’,‘b’,‘1982-01-04’),(2,‘b’,‘c’,‘1984-01-04’);
假设主键冲突，可以使用更新操作：

insert into 表名 values(值列表) on duplicate key update 字段1=值, 字段2=值

mysql中常用的三种插入数据的语句:

insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，
或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；

添加字段

alter table users add assets varchar(100) comment ‘图片路径’ after birthday;

修改表中是name，将其长度改为60

alter table users modify name varchar(60);

删除表中某个字段

alter table users drop password;

修改表名

alter table users rename to emploee;

表中属性改名

alter table emploee change name xingming varchar(60);
新字段需要完整定义

删除表

drop table emploee;

修改数据库
alter语句，对数据库的修改主要指的是修改数据库的字符集，校验规则。

alter database mytest charset=gbk;

删除数据库

drop database db_name;

执行删除之后的结果:

数据库内部看不到对应的数据库
对应的数据库文件夹被删除，级联删除，里面的数据表全部被删

5. 数据库的数据格式

5.1 整数类型

整数类型：tinyint，smallint，mediumint，int，bigint，分别代表1字节，2字节，3字节，4字节和8字节的整数类型，都可以加上unsigned属性。
整数类型可以被指定长度，只会影响显示字符的个数，不会限制值的合法范围
bit:bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1
bit字段按ASCII码显示

5.2 小数类型

小数类型：float（4字节，存储最多8位十进制数），double（8字节，存储最多18位十进制数），decimal（最大65位数）。
decimal能存储比BIGINT还大的整型，能存储精确的小数。
decimal每4个字节存9位，decimal(18,9)，前面9位用4个字节，小数点用一个字节，小数点后9位用4个字节

5.3 日期类型

日期类型：year，date，time，datetime，timestamp
timestamp（4字节）
datetime（8字节）

5.4 CHAR和VARCAHR

文本二进制类型：char，varchar，text，blob
char(len)：len最大255
varchar(len)：len最大65535，有1-3个字符用来记录数据长度，有效字节数是65532，在utf8中一个字符占3个字节，最大len= 65532/3=21844（windows实测21842，linux是21844），如果编码是gbk，一个字符占2个字节，65532/2=32766（实测32764）。
在这里插入图片描述

varchar与char的区别

char的特点

char表示定长字符串，长度是固定的；
如果插入数据的长度小于char的固定长度时，则用空格填充；
因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
空格表示占位符
对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

varchar表示可变长字符串，长度是可变的；
插入的数据是多长，就按照多长来存储；
varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
空格按一个字符存储
对于varchar来说，最多能存放的字符个数为65532

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

varchar(50),50的含义

最大存放50个字符，varcahr(50)和varchar(200)中存放“hello”所占空间一样，但后者在排序是会消耗更多内存，因为order by col采用fixed_length计算col长度。早期MySQL版本中50表示字节数，现在表示字符数

文本选择策略

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。因为VARCAHR还要一个记录长度的额外字节。
使用时要注意只分配需要的空间，更长的列在排序或操作时会消耗更多内存。因为MySQL通常会分配固定大小的内存块来保存内部值。
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。会使用MyISAM磁盘临时表，如果不得不使用BLOB，有一个技巧就是在用到BLOB字段的地方都使用SUBSTRING(column,length)将列值转换成字符串，这样就可以使用内存临时表，但是截取的长度不能过长，超过了某个限制会将内存临时表转换为MyISAM磁盘临时表

5.5 ENUM枚举

ENUM用来代替常用的字符串类型，根据列表值的数量压缩到1到2个字节中，在内部会将每个值再列表中的位置保存为整数，在.frm文件中保存“数字–字符串”的映射关系。排序会根据内部存储的整数来排序。

MySQL存储IP地址

create table IP(addr int unsigned);
insert into IP values(INET_ATON(‘192.168.0.1’));
select INET_NTOA(addr) from IP;

在这里插入图片描述

6. 索引

索引是一种数据结构，实现索引的数据结构有B树，B+树，hash表。InnoDB默认使用B+树。它将数据库所有记录的引用指针以文件的形式存储在磁盘上。

以空间换时间（查询的更快，但是增改删的效率更低），在where/order by/join on涉及到的字段建立索引能够提高效率你发

6.1 索引算法？

Btree算法和hash算法

Btree算法
MySQL默认的算法，用于=,>,>=,<,<=,between,like操作符，除了以通配符开头的常量
Hash算法
只能用于对等比较，例如=,<=>。Btree索引需要从根节点到枝节点，需要多次IO。Hash是一次定位数据，速率比B树快

索引每次从磁盘读取16K，读取16K就是32个扇区，扇区512字节，索引键值对（id int（8字节），地址（8字节）），512/16=32，每一个扇区可以存储32个键值对，
假设一次磁盘IO，1个根节点，31个第二层节点，31*32=992 行记录，还要考虑聚簇索引。

6.2 建立索引原则

索引尽量少建，浪费空间（多一个B+树），降低性能（插入数据的时候需要去维护索引）
索引尽量建主键索引，普通索引回进行回表查询，除非发生了索引覆盖。
索引在int还是char？如果char比较小，可以在char上建，一般在int上建索引，B+树的每个非叶子节点都要存储这个字段，如果字段越大，一次IO读取的节点接越少，那么这个B+树就越深，而且每个节点占的内存空间也会比较大，如果索引比较大的话会增加IO次数
最左匹配原则
频繁查询的子段才去建立索引
更新频繁的字段不适合建立索引
重复度高的字段不适合建立索引（例如性别，年龄）
尽量扩展索引，而不是新建
定义有外键的列一定要建立索引
定义为text，image和bit的数据列不要建立索引
建立索引的列应该要指定为NOT NULL，MySQL中含有空值的列很难进行查询优化，因为它使索引，索引的统计信息以及比较运算更加复杂。

在这里插入图片描述

6.3 B+树在满足聚簇索引和覆盖索引时不需要进行回表查询

聚集索引和主键索引很相似，是因为

在InnoDB中，如果主键被定义了，这个主键索引就被作为聚簇索引，
没有主键时会选择第一个唯一非空索引来建立聚簇索引，
没有合适的唯一索引时，InnoDB内部会生成一个隐藏主键作为聚集索引，这个隐藏主键是一个6字节的列，随数据插入而自增
自增主键会把数据自动向后插入，避免了插入过程中的聚簇索引排序问题。聚簇索引的排序必然会导致大范围数据的物理移动，此处带来的IO性能消耗很大。如果聚簇索引可以修改，也会导致物理磁盘的移动，于是会出现page分裂，表碎片横生。所以也不应该修改聚簇索引。

6.3 如何避免回表查询

联合索引

联合索引是索引覆盖的一种落地方案

alter table table_name add index ‘idx_name_age’ (name,age);

索引覆盖
如果要查询的每个字段都建立了索引，那么引擎会直接在索引表中查询而不会访问原始数据，只要有一个字段没有建立索引也会进行全表扫描，这就叫索引索引覆盖。在select后只写必要的查询字段，增加索引覆盖的概率。

6.4 索引的数据结构

B树可以在内部节点存放键和值，将频繁访问的节点放在靠近根节点处可以大大提高热点数据的访问效率。

B+树索引

每个非叶子节点存储多少个元素，由一次IO读取多少的数据量以及每个节点占用的空间大小决定
B+树的高度有限制，一般也就3-4层

B+树相对B树的优点：

B树的每个节点都存储了键和值，而B+树非叶子节点只存放了键，值都放在了叶子节点里面。而索引一般都是很大的，不可能一次性将所有的索引全部读取到内存中，要分批次的读取，而B+树的非叶子节点只存储了键，一次性就能读取更多的索引，减少了IO读写次数，而IO读写次数是影响索引检索效率的最大因素。
B树适合随机检索，B+树适合随机检索和顺序检索，B树元素遍历效率低下，而B+树叶子节点有指针连起来，只遍历叶子节点就可以实现整棵树的遍历
B+树的查询效率更加稳定，每次查询都需要从根节点走到叶子节点，查询效率相当。B树的查询相当于做一次二分查找，可能找到非叶子节点就返回了
增删记录时B+树效率更高，B+树叶子节点包含所有关键字，并且以有序链表结构存储，这样可以很好的提高增删效率
B+树解决了回旋查找的问题

hash

hash等值访问很快，但是不支持范围查询和顺序查询
hash每次都必须进行回表查询
当某个键值有大量重复时，会产生hash冲突，降低效率
hash不能进行模糊匹配以及多列索引的最左前缀匹配，hash函数的不可预测性

6.5 索引分类

主键索引
保证唯一性，字段应该短

创建方式：

创建表的时候在字段的最后直接指定primary key
创建表的时候在最后primary key(字段名)
alter table 表名 add primary key(字段名)

主键索引特点：一张表只能有一个主键索引，非空不可重复，一般设置为自增型id

唯一索引

创建方式：和主键索引创建的方式类似，只是把Primary key 换成unique
唯一索引特点：一张表可以有多个唯一索引，不可重复但是可以为空，如果指定了not null属性可以等价于主键

普通索引

创建方式：

在表的最后加上index(字段名)
alter table 表名 add index(字段名)
create index index_name on table_name(字段名)

唯一索引和普通索引的字段名可以多个，建立联合索引。
CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

全文索引
在MySQL中使用全文索引，存储引擎必须使用MyISAM，但是不支持中文，如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)

alter table table_name add fulltext(字段名)

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’);

6.6 InnoDB和MyISAM的区别

	InnoDB	MyISAM
事务	支持事务，事务安全	事务不安全，不支持事务
锁	支持行级锁	只支持表级锁
全文索引	不支持全文索引	支持全文索引
存储结构	frm存表结构，数据和索引存放在idb	每张表存储在三个文件中，frm存表结构，myd存数据，myi存索引
表行数	一个特殊变量保存	不保存，count(*)需要全表扫描
hash索引	支持	不支持
索引区别	innodb是聚簇索引，主键索引的叶子结点存储着行数据（高效），非主键索引的叶子结点存储的主键和索引列数据，做到覆盖索引会很高效	非聚簇索引，叶子结点存储的行数据地址，需要进一步寻址得到数据

适用范围

没有特别的需求，一般默认innodb。
MyISAM：效率快，适用于小型应用，跨平台性能好（表被存储成文件的形式），大量的select情况下（高速存储和检索能力还有全文索引，保证查询速度足够快），读写插入操作多
InnoDB：支持事务，如果update delete这些操作比较多，并发量高，保证数据完整性

7. 引擎

innodb引擎的四大特性

四大特性

插入缓冲
二次写
自适应hash索引
预读

8. 事务

事务是一串DML语句组成，这些语句在逻辑上有一定的相关性，这一组DML语句要么成功，要么失败，是一个整体。

8.1 事务的ACID属性

原子性 Atomicity，事务是应用最小的执行单位，不可分割，事务的执行结果要么成功要么失败
一致性 Consistency，事务执行的结果必须从一个一致性状态转换到另一个一致性结果。当数据库只包含事务提交成功的状态时，数据库处于一致性状态。如果系统运行发生中断，导致某个事务尚未完成而被迫中断，该未完成的事务结果提交到数据库中，此时的数据库就处于一种不正确的（不一致）的状态，因此数据库的一致性是由原子性保证的。
隔离性 Isolation，各个事务的执行互不干扰，任何事务内部的操作对于其他事务来说都是隔离的
持久性 Durability，事务一旦成功提交，对数据库所做的改变都要记录到永久存储中

事务无隔离性的问题

脏读

事务1更新了数据，事务2读取了更新后的数据，但事务1由于某些原因回滚了，事务2读取的数据就是脏读

不可重复读

事务1读取了数据，此时可能有其他事务来更新了这个数据，事务1再次读取的时候发现数据已经不一样了

幻读

比如事务1对数据进行了统计或者全局性的更新，事务2此时来增加了一条新的数据，事务1再次来查看的时候就会发现统计结果不一样或者还有一条数据没有被更新，就像出现了幻觉一样

不可重复读主要是由update引起的，幻读主要是由insert和delete引起的

事务的隔离机制

隔离级别	脏读	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
可串行化	×	×	×

读未提交：忽略其他事务放置的锁，可以读取到其他事务未提交的数据。
读已提交：可以读取到其他事务已提交的数据。指定语句不能读取其他事务已改动但是尚未提交的数据，在当前事务各个语句运行之间，其他事务仍能增删改（不可重复读的重点是修改）数据并提交。
可重复度：MySQL的默认隔离级别，包含了读已提交，而且另外指定了在当前事务提交之前，会禁止其他事务的写操作（update和delete，不包括insert，也就是说第二次读的数据肯定包含第一次读到的数据），因此还存在幻读的问题（MVCC来实现）
可串行化：事务只能一个一个执行，不能并发执行（读操作使用共享锁，可以并发读，但是不能并发写），可以避免上述问题，但是性能较差。该级别包含了可重复度，并添加了在事务完毕之前，其他事务不能向事务已读的范围插入新行的限制

MVCC

MVCC就是多版本并发控制，主要是为了在读写的时候不用取竞争锁，提高数据库的并发性能。

同一行数据平时发生读写请求时，会上锁阻塞。但是MVCC提供了更好的方式去处理读写请求，做到在读-写请求冲突时不用加锁。

MVCC主要是处理读请求读，此时的读指读是快照读，而不是当前读，当前读是一种加锁操作，是一种悲观锁。

MVCC指的就是在读已提交和可重复读这两种隔离级别的事务在执行普通的select操作时访问记录的版本链的过程，这样可以使不同事务的读-写/写-读操作并发执行，从而提升系统性能

快照读和当前读

快照读就是普通的不加锁select读（隔离级别不是串行化）。快照读的实现基于多版本并发控制，即MVCC，既然是多版本，那么快照读读取到的数据不一定是当前最新的数据，有可能是之前历史版本的数据

当前读是一种悲观锁，需要去加锁。它读区的数据库记录都是当前最新的版本。如下操作都是当前读：

select lock in share mode（共享锁）
select for update（排他锁）
update（排他锁）
insert（排他锁）
delete（排他锁）
串行化事务隔离级别

在MySQL的InnoDB引擎中，读已提交和可重复读这两种隔离级别下的select操作都是通过MVCC实现了读-写，写-读的并发执行，提升了系统的性能。

基本概念：undo log，版本链，readview

在InnoDB引擎中，聚簇索引记录中必有两个隐藏列：

trx_id
每次对某条聚簇索引记录进行修改的时候的事务ID（自增）
roll_pointer
回滚指针，用来回滚到上一个版本用的。每次对某条聚簇索引记录有修改的时候，都会把老版本写入undo log中，这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息

整个就构成了一个版本链

ReadView

ReadView用来存储系统中当前活跃着的读写事务，也就是begin了但是还未commit的事务。主要有这几个数据结构

m_ids：表示在生成readview时当前系统中活跃的读写事务的‘事务ID’列表
min_trx_id：活跃读写事务中最小的‘事务ID’值，也就是m_ids中的最小值
max_trx_id：表示生成ReadView时，系统中应该分配给下一个事务的id值
creator_trx_id：生成该ReadView的事务的事务ID

ReadView判断版本链中哪个版本对当前事务可见

trx_id == creator_trx_id：可以访问这个版本
trx_id < min_trx_id：可以访问这个版本
trx_id > max_trx_id：不可以。（当前事务ID是ReadView生成之后创建的事务的ID）
min_trx_id <= trx_id <= max_trx_id：如果trx_id还在m_ids中时不可访问这个版本的，反之可以

读已提交的隔离级别下的事务在每次select的时候都会重新生成一个独立的ReadView，两次seelct中间可能会有其他事务对这条记录有修改并且提交，因此有不可重复读的问题。

可重复读的隔离级别下的事务只在第一次select的时候生成ReadView，后续的select查看的都是同一份ReadView，因此不存在不可重读的问题。

可重复读级别解决幻读

InnoDB的可重复读隔离界别默认开启了间隙锁，锁住一定的范围，假设事务1查询id>2的记录，查询成功之后就会对id>2对这一段范围全部上锁，上锁之后就不存在幻读读问题了

事务ACID的底层原理

undo log和redo log是事务日志

原子性

原子性通过undo log实现，如果SQL语句执行失败的话，会执行回滚，回滚到执行之前到状态。当事务对数据库表进行修改的时候，innoDB会生成undo log，会记录SQL执行的相关信息。事务执行成功则删除undo log，执行失败则进行回滚，根据undo log去做相反的工作。

持久性

持久性通过redo log实现，修改数据的时候，除了把数据写入内存中，还会记录在redo log中，如果MySQL宕机了，导致数据还没有同步到数据库，重启的时候根据redo log进行数据重做。

redo log是预写式日志，将所有的修改先写到redo log，再更新到内存中，保证了数据不回丢失，这样就保证了持久性。

redo log也需要把事务提交的日志写入到磁盘，比直接将内存中的数据写入磁盘快

内存中的数据是随机写的IO，每次修改的数据位置都是随机的，但是redo log是追加模式的，在文件尾部去追加，时一种顺序IO的操作
内存持久化数据是以数据页page为单位的，默认大小为16K，一个页上一个小小的修改都需要把整个页的数据写入，redo log只需要写入真正需要的部分就ok了。

redo log什么时候同步到磁盘呢？
没有同步之前是在缓冲区中，叫做redo log缓冲区。就算宕机了也没有关系，此时数据库没有提交，可以回滚。
redo log同步到磁盘是由一个变量控制的：innodb_flush_log_at_trx_commit

0表示当提交事务时，并不将缓冲区的redo 日志写入磁盘的日志文件，而是等待主线程每秒刷新
1表示在事务提交时将hua你冲去的redo日志同步写入到磁盘，保证一定会写入成功
2表示在事务提交时将缓冲区的redo日志异步写入到磁盘中，即不能完全保证commit时肯定会写入到redo 日志文件，只是有这个动作

隔离性

隔离性分为两种情况：

写-写操作：锁
读-写操作：MVCC

通过如下指令查看当前锁状态：

elect * from information_schema.innodb_locks;

事务1:

begin;
update person set name = ‘aa’ where id = 1;

事务2:

begin;
update person set name = ‘bb’ where id = 1;

此时查看锁的状态就会发现有两个行锁
在这里插入图片描述
x表示排他锁，record表示行锁

一致性

指事务执行之后数据库的完整性约束没有被破坏，事务执行前后都是合法的，一个数据的完整性主要体现在：主键唯一，字段类型大小合法，长度符合要求，外键约束要符合要求。一致性是事务追求的最终目标，原子性，持久性，隔离性哦读诗味了保证数据库最终状态的一致性，如果这三个性质无法保证，那么一致性肯定也无法保证

9. 锁

在数据库有并发事务的时候，用来维护访问数据次序的机制。

9.1 锁和隔离级别的关系

读未提交：读取数据不需要加锁，忽略其他事务放置的锁
读已提交：读取数据加共享锁，已读数据的共享锁在每条语句结束时释放
可重复度：读取数据加共享锁，已读数据的共享锁在整个事务间持有，而不是在每一条语句结束时释放
可串行化：该级别锁定整个范围的键，并一直持有锁，直到事务完成

9.2 锁的分类

按照锁的粒度分

表级锁：MySQL中锁粒度最大的一种锁，开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最小。
页级锁：锁定粒度介于行级锁和表级锁中间，锁定相邻的一组记录。会出现死锁，并发度一般
行级锁：MySQL中锁帝都最小的一种锁，开销大，加锁慢；会出现死锁；锁定粒度小，发出锁冲突的概率最小，并发度最高。

行级锁和表级锁都有共享锁和排他锁。

InnoDB支持行锁和表锁，默认行锁。MyISAM只支持表锁。

MySQL中InnoDB引擎的行锁如何实现

基于索引来实现

select * from tab where id=1 for update;
for update可以根据条件来完成行级锁定，如果id不是索引键，那么InnoDB将完成表锁，并发将无从读起。

按照锁的类别分：

共享锁：又叫读锁，读数据时加上共享锁，读锁可以加多个
排他锁：又叫写锁，写数据时加上排他锁，写锁只能加一个，与其他读锁和写锁都排斥。

9.4 InnoDB存储引擎的锁算法

Record lock/Gap lock/Next-key lock

9.5 什么是死锁，怎么解决

死锁是指两个或多个事务在同一资源上相互占有，并请求锁定对方持有的资源，从而导致恶性循环的现象。
死锁的四个必要条件

互斥条件：一个资源只能被一个执行流持有
请求与保持：一个执行流因请求被阻塞的时候，对已拥有的资源保持不释放
不可剥夺：一个执行流如果已经获得了某个锁，除非它自己释放，其他执行流不能剥夺
循环与等待：若干执行流形成一种头尾相接的资源等待关系

常见死锁解决办法：

约定加锁顺序
资源一次性分配
升级锁定粒度，使用表级锁来减少死锁产生的概率

如果业务处理不好可以用分布式事务锁或者使用乐观锁

9.6 悲观锁和乐观锁

乐观并发控制和悲观并发控制是并发控制主要采用的技术手段

悲观锁：
假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。
实现方式：使用数据库中的锁机制
使用场景：写多

乐观锁
假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。
实现方式：乐观锁一般会使用版本号机制或CAS算法实现。
使用场景：读多

10. SQL优化

如何定位低效sql语句，对可能低效的原因做排查，先从索引着手，如果索引没有问题，考虑数据访问，长难查询句，还有一些特定类型优化的问题

10.1 执行计划

对于查询语句，最重要的优化方式就是使用索引，执行计划可以显示数据库引擎对于SQL语句的执行的详细情况

id：表示一个查询中各个子查询的执行顺序，id越大越先被执行，id相同时执行顺序从上往下；id为null时表示一个结果集，不需要使用它查询，长出现在UNION等查询语句中
select_type：每个子查询的查询类型
type：访问字段
- ALL 扫描全表
- index 遍历索引，索引物理文件全扫描
- range 索引范围查找
- ref 使用非唯一索引查找数据，普通索引
- eq_ref在join查询中使用PRIMARY KEY 或者是UNIQUE NOT NULL索引关联
- const：用到了主键索引或者唯一索引放到where当中查询，单表中最多一个匹配行
- system：表中只有一行数据
possible_keys：可能使用到的索引
key：实际使用的索引
key_length：索引长度，在不损失精度的情况下，越小越好
rows：大致估算说要找到所需记录需要读区等行数，并不是一个准确的值，rows越小越好
ref：关联到字段，如果使用常数等值查询则会显示const，如果是连接查询，就显示关联到字段
extra
- using index
- using where
- using where, using index
- using index

利用延迟关联或者子查询优化超多分页场景。

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

10.2 慢查询日志

使用

show variables like ‘slov_query_log’

查看慢查询日志是否开启，如果是OFF，可以使用

set GLOBAL slow_query_log = on

来开启，它会在datadir下产生一个xxx-slow.log的文件

修改临界时间，单位秒

set long_query_time=0.5

实操时应该从长时间设置到短的时间，即将最慢的SQL优化掉

查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

慢查询的优化
慢查询的优化首先要搞明白慢的原因是什么，是没有命中索引？load了不需要的数据？还是数据量太大？

首先分析语句，是否多写了其他不必要的字段
分析语句的执行计划，查看语句使用索引的情况，之后再修改语句或者修改索引
如果语句已经无法进行优化，就可以考虑是不是数据量太大，从而考虑分表的问题

水平分表

垂直分表

10.3 具体优化步骤

避免全表扫描，检查是否有索引
没有索引的情况考虑是否建立索引（经常查询/需要排序）
索引是否生效

不满足最左匹配原则
like以百分号开头（解决办法：%后移，使用索引覆盖）
<、>右边索引失效
or也会导致索引失效(在or的两边都是主键索引才不会失效(亲测5.7.36)，或者使用union（union all）代替or)

在这里插入图片描述

where语句使用负向查询，包括：NOT、!=、<>、!<、!>、NOT IN 、NOT LIKE也会导致索引失效（并不绝对，优化器会判断走索引或者全表扫描哪个成本低）
在where子句中使用表达式操作（where age-1=9）、内置函数操作，应该尽量在程序中进行计算和转换

优化数据访问
检索太多行或列会导致查询性能下降

避免使用select *，只查询必须的字段，增加覆盖索引的概率
多表关联返回全部列：指定列名
重复查询相同的数据：缓存数据，下次直接读取缓存

SQL语句的结构优化

尽量减少子查询（子查询会创建临时表，查询完毕删除临时表）
调整where子句的连接顺序，过滤数据多的条件放前面，最快速度缩小结果集
尽量减少联表查询，联表查询是笛卡尔乘积的形式，检索的数据几何倍上升
一次查询的结果最好不要过大，可以使用分页查询
count(*)会忽略所有的列，直接统计所有列数，不要使用count(列名)，MyISAM中没有where条件的count(*)非常快

10.4 为什么要设置主键，自增id还是uuid

主键是数据行在整张表中唯一性的保障，设定主键后，在后续的删改查的时候可能更加快速。

推荐使用自增ID，在innodb中，主键索引是作为聚簇索引存在的，主键索引的叶子结点上按顺序存储了主键和全部数据。自增ID的话，插入数据只需要不断地向后排列；uuid的话，由于到来的ID与原来的ID大小不确定，会造成大范围的数据物理移动，page分裂，表碎片横生的情况，最终就是插入性能下降。

10.5 为什么不推荐使用外键

外键好处：数据一致性，完整性更高，减少代码量
外键坏处：增删改操作都会通过外键约束来检查数据一致性，增大了数据可的压力，十分影响性能；水平拆分和分库情况下，外键是无法生效的。不使用外键，将数据间关系的维护放在代码中，后面的分库分表可以减少很多麻烦

10.6 字段要设置not null，并提供默认值

null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化。
null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。
null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识。
对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=‘shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录。

索引字段可以为null，单个索引字段，使用is null或is not null时，可以命中索引：
在这里插入图片描述

11. 数据库表优化

需要考虑数据冗余，查询和更新速度，字段类型是否合理等多方面的内容

11.1 将字段很多的表分解成多个表

使用频率少的字段会拖慢查询速度，可以将这些字段分离出来形成新表

11.2 增加中间表

建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

11.3 增加冗余字段

合理地加入冗余字段可以提高查询速度，冗余字段的值在一个表中修改了，在另一个表中也要修改，否则会导致数据不一致的问题。

11.4 MySQL数据库cpu飙升处理方法

先用top命令查看是否是mysqID占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

11.5 大表优化

限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。；
读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；
缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

分库分表的方式进行优化

垂直分区

根据数据库里面数据表的相关性进行拆分，指数据表列的拆分。

优点：行数据变小，在查询的时候减少读取的Block数，减少IO次数；垂直分区可以简化表的结构，便于维护
缺点：主键出现冗余，需要管理冗余列，并且会引起Join操作，可以通过在应用层进行Join解决问题。此外，垂直分区会让事务变得更加复杂。依赖应用层的逻辑算法，如果应用层的逻辑算法改变，整个分表逻辑都会改变，扩展性较差。

适用场景：