MySql
2023-12-26 09:26:03 53 举报AI智能生成
MySql
作者其他创作
大纲/内容
数据定义语句（create drop alter）
DDL
all
any
子查询操作符
union
union all
intersect
minus
合并查询
数据操作语句（insert update delete）
DML
数据查询语句（select）
DQL
数据控制语句（grant revoke commit rollback）
DCL
SQL语句分类
显示数据库创建语句：SHOW CREATE DATABASE db_name
not null
unique
primarykey
foregin key
check
约束
触发器
函数
存过程
辅助操作
基本操作语句
备份
恢复
数据备份为sql文件及数据恢复
时间日期函数
字符串函数
数字函数
流程相关函数
其他函数
client：客户端字符集
connection：连接数据库的字符集，不指定则使用服务端默认字符集
database：指定数据库使用的字符集，若建库时不指定则使用服务器字符集
result：数据库给客户端返回的字符集，若不指定则使用服务器默认字符集
server：服务器安装时使用的默认字符集
system：数据库系统使用的字符集
mysql用到字符编码的地方
用户崩溃时恢复数据（WAL预写日志的方式，在数据提交前记录日志（日志文件顺序存储速度快））；Force Log at Commit（1.设定参数每隔一段时间落盘；2.事物提交时落盘）保证该日志落盘（从redo log buffer 到 redo log），以此来达到奔溃时数据可能恢复;。总的有两个日志空间交替使用，当空间满的时候出发数据文件落盘(Buffer Poll 到 数据文件)，数据文件落盘之后及可清除redo log日志文件
redo log
保证事物提交之后redo log已经持久化
force log at commit
操作系统磁盘最小单位4KB；mysql最小操作单位16KB；duble write 保证mysql的数据页要么全部成功，要么全部失败
用于提高数据库的可靠性，用于解决脏页落盘时部分写失效问题
Double write
用于对数据回滚时进行撤销，也会用于隔离性的多版本控制
undo log
原子性持久性一致性
0) start transaction //开始一个事务 \t1) savepoint 保存点名 //设置保存点2) rollback to 保存点名 //取消部分事务3) rollback //取消全部事务4) commit //提交事务.
控制台操作
当一个事务读取另一个事务尚未提交的修改时，产生脏读
读未提交（脏读）
同一查询在同一事务中多次进行，由于其他提交事务所做的修改，每次返回不同的结果集，此时发生非重复读
读已提交（不可重复读）
导致一条数据查询不存在，但是插入也不能成功（因为别的事物插入了该数据，而当前session不可见）
同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
MySQL中的幻读，你真的理解吗？
重复读（幻读）
序列化
1.查看当前会话隔离级别： select @@tx_isolation;
2.查看系统当前隔离级别：select @@global.tx_isolation;
3.设置当前会话隔离级别：set session transaction isolation level repeatable read;
4.设置系统当前隔离级别：set global transaction isolation level repeatable read;
5. mysql 默认的事务隔离级别是 repeatable read
相关操作指令
隔离性
事物
insert/update/delete
for update
innoDB存储引擎加行锁，font color=\
mysql使用行锁，首先得明确使用的是innoDB引擎
开销小，加锁快，不会出现死锁；粒度大，发生锁冲突概率大，并发低
适合以查询为主
特征
local table table_name read
现象：可以对表进行读操作；当前session对表进行CUD操作（报错）；其他session进行CUD操作（等待）
读锁
local table table_name write
现象：当前session可以进行CRUD操作；其他session CRUD操作（等待）
写锁
unlock tables
释放锁
表级锁
不允许其他session 再来加排他锁，但可以加读锁
select * from table_name where ** lock in share mode
不允许其他事物写和加锁，但允许读
select * from table_name where ** for update
开销大、加锁慢、会出现死锁；粒度小，发生锁冲突低，并发高
行锁必须要有索引才能实现，否则锁全表
CUD默认都会加行级锁
注意
行级锁
开销介于表级别锁与行级别锁之间；会出现死锁；粒度介与表级锁与行级锁之间；并发一般。
BDB引擎使用
页面锁
间隙锁
分类
锁
事务和锁
聚集索引（主键索引）
非聚集索引
show index from 表名
查看索引
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
创建索引
DROP INDEX [indexName] ON mytable;
删除索引
基本操作
索引
基本概念
表空间相关参数：innodb_file_per_table
系统表空间无法简单收缩文件大小
独立表空间可以使用optinized table 收缩文件大小
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
支持事物
行锁为索引加锁，如果where字段不是索引，将会锁全表
行级锁（默认），表级锁
MySQL 5.7 中文全文检索使用教程
5.6之后支持全文索引，对中文支持需要特殊配置处理
当需要把新页面存储到缓冲池中的时候，将淘汰最近最少使用的页面，并将新页面添加到旧子列表的头部。
innodb_old_blocks_time很重要，有了这1秒，对于全表扫描，由于是顺序扫描的，一般同一个数据页的数据都是在一秒内访问完成的，不会升级到新子列表中，一直在旧子列表淘汰数据，所以不会影响到新子列表的缓存。
`innodb_old_blocks_pct`：控制LRU列表中旧子列表的百分比，默认是37，也就是3/8，可选范围为5~95；`innodb_old_blocks_time` ：指定第一次访问页面后的时间窗口，该时间窗口内访问页面不会使其移动到LRU列表的最前面。默认是1000，也就是1秒。
默认 3/8缓冲池用于旧子列表；当新页面如缓冲池时，首先将其插入旧子列表头部；重复访问旧子列表的页面，将使其移动至新子列表的头部；随着数据库的运行，页面逐步移至列表尾部，缓冲池中未被方位的页面最终将被老化淘汰。
LRU
主内存中的一个区域，在InnoDB访问表数据和索引数据的时候，会顺便把对应的数据页缓存到缓冲池中；专用服务器上，通常将80%左右的物理内存分配给缓冲池;提高缓存管理效率，缓冲池把页面链接为列表，使用改进版的LRU算法将很少使用的数据从缓存中老化淘汰掉.
buffer pool(缓冲池)
文件IO
将数据刷新到InnoDB数据文件和日志文件的方法，这可能会影响I/O吞吐量
innodb_flush_method
当二级索引页(非唯一索引)不在缓冲池中时，它们会缓存这些更改 。当页面通过其他读取操作加载到缓冲池中时，再将由INSERT，UPDATE或DELETE操作（DML）产生的change buffer合并到buffer pool的数据页中。
索引不可以使用chage buffer
change bugger
自适应哈希索引功能由innodb_adaptive_hash_index变量启用 ，或在服务器启动时由--skip-innodb-adaptive-hash-index禁用。
自适应哈希索引
og buffer(日志缓冲区)用于保存要写入磁盘上的log file(日志文件)的数据。日志缓存区的内容会定期刷新到磁盘。日志缓冲区大小由innodb_log_buffer_size变量定义 。默认大小为16MB。较大的日志缓冲区可以让大型事务在提交之前无需将redo log写入磁盘。如果您有更新，插入或者删除多行的事务，尝试增大日志缓冲区的大小可以节省磁盘I/O。
控制如何将日志缓冲区的内容写入并刷新到磁盘
通过该参数获取更好的性能，但是会导致在系统崩溃的过程中导致数据丢失。
可选参数：0，事务提交之后，日志只记录到log buffer中，每秒写一次日志到缓存并刷新到磁盘，尚未刷新的日志可能会丢失；1，要完全符合ACID，必须使用该值，表示日志在每次事务提交时写入缓存并刷新到磁盘；2，每次事务提交之后，日志写到page cache，每秒刷一次到磁盘，尚未刷新的日志可能会丢失；
innodb_flush_log_at_trx_commit
控制日志刷新频率。可让您将日志刷新频率设置为N秒（其中N为1 ... 2700，默认值为1）
为了保证数据不丢失，请执行以下操作：如果启用了binlog，则设置：sync_binlog=1;innodb_flush_log_at_trx_commit=1;
innodb_flush_log_at_timeout
参数配置
Log Buffer
系统表空间
独立表空间
通用表空间
当`innodb_file_per_table`启用时，通常是将表存放在独立表空间中，这是默认配置；当`innodb_file_per_table`禁用时，则会在系统表空间中创建表；请使用 `CREATE TABLE … TABLESPACE`语法。
创建一个InnoDB表时，MySQL 在数据库目录中创建一个.frm文件
InnoDB它还在系统表空间内的自身内部数据字典中编码有关表的信息。MySQL删除表或数据库时，将删除一个或多个.frm文件以及InnoDB数据字典中的相应条目。
frm文件
在独立表空间创建的表，还会在数据库目录中生成一个 .ibd表空间文件
通用表空间中创建的表在现有的常规表空间 .ibd文件中创建
ibd文件
系统表空间文件，在 InnoDB系统表空间中创建的表在ibdata中创建。
ibdata文件
表空间
InnoBD引擎架构
行
页
区
段
undo 表空间
逻辑存储结构（五级）
插入缓冲（insert buffer）
double write
二次写（duble write）
维护索引叶页面中所有记录的索引键值(或键值前缀)到索引叶页面位置的Hash映射关系
自适应哈希索引（ahi）
预读（read ahead）
四大特性
数据和索引都存在同一个文件中.idb
innoDB
页级锁
BDB
事务安全型
并发高，支持表级锁
5.6之后innodb也支持全文索引，5.6之前只有myisam支持
支持全文检索
myisampack -b -f **.MYI
支持数据压缩
不支持事物
特性
非事务应用
只读型应用
空间类（空间函数、坐标）
适用场景
内部维护有数据计数器，select count(*)很快
MYISAM
是 MyISAM 类型的一种变种。合并表是将几个相同的 MyISAM 表合并为一个虚表。常应用于日志和数据仓库
MERGE
ISAM
所有数据保存在内存中
支持Hash索引和bTree索引
所有字段都是固定长度varchar(10)=char(10)
不支持blog和text等大字段
使用表级锁
最大大小以max_heap_table_size参数决定
用于保存数据产生的中间表
用于缓存周期性聚合数据的结果表
使用场景
HEAP(memory)
以zlib对表数据进行压缩，磁盘IO更少，数据存储在ARZ为后缀的文件中
只支持insert和delete操作
只允许在自增ID列加索引
日志和数据采集等
archive
如需要使用在启动前在配置文件中增加配置：federated=1
默认禁止
federated
文件存储内容：**.cvs
表结构：**.frm
文件存储表元数据（表状态，数据量）: **.csm
数据已文本方式存储
所有列不能为空，在创建表的时候需就需要指定
不支持索引，不适合大表，不适合在线索引
可以对数据文件进行直接编译
CVS
数据和索引分开存储：数据 .MYD 索引 .MYI
非事务安全型
ALTER TABLE `表名` ENGINE = 储存引擎;
修改存储引擎
show engines 查看当前提供的存储引擎
show variables like '%storage_engine%' 查看默认搜索引擎
相关操作
存储引擎
当前读
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；
快照读
读-读
为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
MVCC解决该问题
读-写
写-写
数据库并发场景
维持一个数据的多个版本，使得读写操作没有冲突
快照读就是 MySQL 实现 MVCC 理想模型的其中一个非阻塞读功能
DB_TRX_ID
DB_ROLL_PTR
DB_ROW_ID
删除字段
三个隐式字段
insert undo log
update undo log
undo日志
MVCC 模型在 MySQL 中的具体实现则是由 3 个隐式字段，undo 日志 ，Read View 等去完成的
purge
某个事务的font color=\"#e65100\
RR级别
RC级别
Read View(读试图)
MYSQL（04）-间隙锁详解
【MySQL笔记】正确的理解MySQL的MVCC及实现原理
MVCC（多版本并发控制）
错误日志文件（error log）
需要手动开启
二进制日志文件(bin log)【mysql服务】
通用查询日志(general query log)
慢查询日志(show query log)
主从复制时，从库从主库获取过来的bin log日志在本地保存为中继日志（relay log）
中继日志(relay log)
用于奔溃时回复数据，循环使用；根据checkpoint脏页落盘成功之后就可以清除该记录（当redolog空间不足也会触发脏页落盘）。
重做日志文件(redo log)
事物回滚
隔离性，多版本控制
回滚日志文件(undo log)【innodb存储引擎】
日志文件
ibdata
*.fmt
磁盘文件
数据（数据增删很频繁）
随机写文件
日志（写得快，都是增量）
顺序写文件
磁盘
检查被代理服务器的可用性
任意时间只有一个服务（获得与keepalived连接的服务）对外提供服务
被代理服务通过抢占的方式去争夺与keepalived的链接，一旦有连接成功，别的需等待该连接失效
keepalived
动态代理服务器，更多的类似nginx
HAProxy
keepalived/HAProxy
整体架构
1.先尝试从查询缓存(8.0以后不再有查询缓存)中查询该sql是否已经有缓存的结果了；2.分析器拿到sql之后会尝试对sql语句进行词法分析和语法分析，校验语法的正确性；3.优化器拿到分析器的sql之后，开始继续解析sql，判断需要走什么索引，根据实际情况重写sql，最终生成执行计划；4.执行器根据执行计划执行sql，执行之前会先进行操作权限校验；然后根据表存储引擎调用对应接口进行查询数据，这里的扫描行数就是指的接口返回的记录数，执行器拿到返回记录之后进一步加工；5.执行器根据sql条件依次筛选数据； 从磁盘数据中读取数据到内存Buffer Pool（数据页，索引页）中；
sql执行流程
查询流程
1.数据写入Buffer Pool的同时，先写入数据到redo log buffer中；2.redo log buffer 依据 Force log at Commit(按时或事物提交的时候)写入 redo log日志中；3.Buffer pool 依据check point 择时脏页数据落盘；（redo log日志满了也会触发该操作，成功之后会清理redo log日志）;4.Double write保证脏页数据不会出现部分落盘失败的情况。
CUD流程
1. 如果查询的两个表大小相当，使用in和exists差别不大；2. 如果两个表中一个较小，一个较大，则子查询表大的用exists，子查询小的使用in；3. not in和 not exists ：如果查询语句使用了not in， 那么内外表都进行全表扫描，没有用到索引；而notexists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in快。
in与exists效率
执行流程
查看连接池中建立好的连接
Time：表示这个连接多久没有动静了；默认地，如果超过8个小时还没有动静，连接器就会自动断开连接，可以通过wait_timeout参数进行控制
show processlist
操作命令
任何标准表最多可以创建16个索引列
限制
复杂
MySQL Cluster
维护成本高
drbd 磁盘镜像 网络raid
存在延时问题
缺点
记录语句
statement
记录影响的数据行
Row
混合statement 和 row
mix
bin log日志记录三种方式
1.主库记录bin log日志；2.主库推送bin log日志给从库；3.从库接受日志保存为中继日志(relay log)；4.从库线程从中继日志(relay log)还原数据。
操作步骤
从库多了之后，主库推送bin log日志压力会增大
一主多从
从库复制给从库，一级一级传递
多级复制
两个数据库互为主从
浮动IP-keepalived
需要的技术
双主复制
双主多级复制
常用复制架构
主库dump线程异步负责推送bin log日志给从库
在dump线程还未推送日志或推送过程中挂了
从库性能比主库差等原因，导致在使用数据的时候从库还没有从中继日志（relay log）中恢复数据
主从数据不一致
异步复制
半同步复制
主库bin log日志要推送在从库中并记录中继日志（relay log）之后才返回客户端成功
半同步复制，主从库都需要的插件
搭建时候需要插件支持
复制方式
SQL路由问题
核心问题
Mysql+keepalived实现双主集群
spring提供的多数据源解决方式
spring提供的解决方案
读写分离实战
应用层直接实现
定制JDBC协议
定制ORM框架
客户端分片
逻辑库
分片表
数据量不大的表，没必要分片
非分片表
解决跨库查询的问题（子表和父表存在同一个库，主从明细表等）
ER表
每个库都保存一个数据（例如字典表一些变更少，数据少的数据）
全局表（数据冗余）
逻辑表
分片节点
节点主机
哈希分片
时间分片
分片规则
采用一台服务器一次性申请多个ＩＤ放入本地缓存（保证效率）
基于数据库实现
64位（1位符号（0）+41位时间戳+10位机器标识+12位序列号）
雪花算法实现
雪花算法
持久顺序节点，异步删除不再使用的节点
zk实现
redis
全局序列号（分布式ID生成算法）
常见概念
Mycat
Cobar
ShardingSphere
TDDL
Mysql-proxy
Oneproxy
Atlas
Vitess
代理分片
方案
分库分表实现
MySQL复制
三种方式
集群架构
session来表示当前的连接的统计结果(默认)，global来表示自数据库上次启动至今的统计结果
show status like ‘Com_%’;
Connections：试图连接MySQL服务器的次数Uptime：服务器工作的时间（单位秒）\u000BSlow_queries：慢查询的次数 (默认是慢查询时间10s)
show [session|global] status [like '???']
1.通过show status了解各种SQL执行频率；
默认不记录慢查询日志，需要在服务启动时手动开启；通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL语句
常用配置
记录CRUD所有符合条件的语句
font color=\"#c41230\
mysqldumpslow
pt_query_digest
分析工具
慢查询
2.定位执行效率较低的SQL；
表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行id相同不同，同时存在
三种情况
id
主要是用于区别普通查询、联合查询、子查询等的复杂查询
select_type（查询类型）
显示这一行数据是关于哪个表
table
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计const：表示通过索引一次就找到了；const用于比较primary key或者unique索引eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描ref：非唯一性索引扫描，返回匹配某个单独值的所有行font color=\"#f15a23\
type
possible_key
key
key_len计算总结
表示索引使用的字节数表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
key_len
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
ref
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
rows
包含不适合在其他列中显示但十分重要的额外信息
extra
Explain + SQL语句
3.通过explain分析低效率SQL的执行情况；
查看索引的使用情况 show status like ‘Handler_read%’;\u000Bhandler_read_key:这个值越高越好，越高表示使用索引查询到的次数。\thandler_read_rnd_next:这个值越高，说明查询低效。
4.确定问题，并做相应处理。
一般操作步骤
选择合适的存储引擎
合适的数据类型
对表进行水平划分\t
对表进行垂直划分
innodb_additional_mem_pool_size = 64Minnodb_buffer_pool_size =1G
数据库参数配置
选择合适的编码
常用优化方式
1.数据库设计；
MyISAM：alter table table_name disable keys;loading data//insert语句; alter table table_name enable keys;
大批量插入数据
想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引
优化group by 语句
有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。
优化需要优化的Query定位优化对象性能瓶颈从Explain入手永远小结果集驱动大结果集#尽可能在索引中完成排序只取自己需要的Column仅仅使用最有效的过滤条件尽可能避免复杂的join和子查询
原则
2.sql优化
3.数据库参数设置；
4.恰当的硬件资源和操作系统
四个方面
联合索引，尽量吧联合字段都使用起来
尽量全值匹配
如果索引了多列，查询从索引的最左列开始并且不能跳过部分索引
最左匹配原则
不要在索引字段上做操（函数，类型转换，计算）作，会失效
不在索引上做任何操作
范围条件之后的索引字段会索引失效
范围条件放最后
尽量使用覆盖索引(查询列是索引列)，减少使用select *
使用不等于的时候会索引失效导致全表扫描
少用不等于
条件为is null 索引失效
条件is not null 索引失效
字段not null(所以字段都有值，此时进行null值判断没有任何意义，如一定要用可使用覆盖索引方式)
条件为is null 使用索引
条件为is not null 索引失效
字段为is null
Null、Not null可能有影响
like查询使用‘%具体字段’的方式会索引失效，全表扫描
类型不匹配会调用函数转类型，从而导致失效
字符串不加单引号会索引失效
union比or的效率更高
避免索引失效
优化
MySql
0 条评论
下一页
