Mysql
2021-04-16 14:53:53 41 举报AI智能生成
登录查看完整内容
一图了解MYSQL
作者其他创作
大纲/内容
Mysql
参数
服务器参数
应用类型
获取信息路径
mysqld_safe --datadir=/data/sql_data
配置文件
参数作用域
全局参数
set global 参数名=参数值
set @@global.参数名:=参数值
会话参数
set[session]参数名=参数值
set@@session.参数名:=参数值
I/O参数
Innodb
日志文件的个数
Innodb_log_files_in_group
Innodb_log_file_size
MyISam
内存参数
确定可以使用的内存上限
每个连接使用的内存
连接缓冲区的大小
join_buffer_size
读缓冲区的大小
read_rnd_buffer_size
排序缓冲区的大小
sort_buffer_size
全表扫描时候的大小
read_buffer_size
确认为操作系统保留的内存数
如何为缓存池分配内存
Innodb_buffer_pool_size
总内存-（每个线程所需要的内存*连接数）-系统保留内存
缓存池
InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等
内存中可以直接访问数据
特性
预读
提升IO效率
安全配置
指定自动清理binlog的天数
expire_logs_days
控制Mysql可以接收的包的大小
max_allowed_packet
禁用DNS查找
skip_nam_resolve
确保当前时间返回确定性日期
sysdate_is_name
禁止非super权限的用户写权限
read_only
禁用Slave自动恢复
skip_slave_start
数据库结构设计
过分的反范式化为表建立太多的列
过分的范式化造成太多的表关联
在OLTP环境中使用不恰当的分区表
分区键
使用外键保证数据的完整性
优化
目的
1.减少数据冗余
2.尽量避免数据维护中出现更新，插入和删除异常
节约数据存储空间
提高查询速度
设计步骤
1.需求分析
存储需求
数据处理需求
数据的安全性和完整性
2.逻辑设计
1.设计数据的逻辑存储结构
2.实体与实体建的逻辑关系
3.物理设计
根据数据库特点进行表结构设计
命名规范
存储引擎
数据类型
4.维护优化
数据库设计范式
第一范式
任何属性都是原子性，不可再分
第二范式
只有一个业务主键
第三范式
对任何非主属性都不传递依赖于候选键
巴斯 - 科德范式
第四范式
第五范式
反范式
空间换时间
基准测试
针对系统设置的一种压力测试
直接、简单，评估服务的处理能力
步骤
1.计划和设计基准测试
对整个系统还是某个组件
什么样的数据
数据库索引
Tree索引
B+数索引特点
加快数据查找
适合范围查找
适合场景
全值匹配
匹配最左前缀
列前缀
排序
限制
不按索引最左列开始查找
联合索引不能跳过索引中的列
not in 和 <>
Hash索引
特点
基于Hash表实现，适合精确匹配Hash索引中的所有列
只存储了Hash码
必须进行二次查找
无法用于排序
不支持部分索引和范围查找
Hash冲突
作用
索引减少存储引擎需要扫描的数据量
索引帮助我们进行排序以避免使用临时表
把随机I/O变为顺序I/o
过多索引
增加写的操作成本
增加查询优化器的选择
索引优化策略
索引列上不能使用表达式或函数
to_days(date)
Innodb中所有长度不要超过767bytes
前缀索引和索引列的选择
尽量选择不重复的值作为索引
联合索引
顺序
常用的列优先
选择性高的列优先
宽度小的列优先
最左原则，例如xyz联合索引，当y使用了>等其他符号，后面按的索引就失效了
覆盖索引
优化缓存，减少磁盘IO
减少随机IO，变随机为顺序操作
避免对Innodb主键索引的二次查询
避免MyIsam表进行系统调用
what
帮助数据库管理系统高效获取数据的数据结构
索引造成效率低
1.小表
2.字段重复度大
索引片
窄索引
包含索引列数为1或2
宽索引
包含的所有列数大于2
通过宽索引避免回表
列数越多开销越大
过滤因子
过滤能力越强，满足条件的记录数就越少，减少扫描的索引片
回表
就是数据库根据索引找到了数据行之后，还需要通过主键再次到数据表中读取数据的情况
理想索引设计
三星索引
在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列
最小化碎片，强过滤
2.将 GROUP BY 和 ORDER BY 中的列加入到索引中
避免排序
3.将 SELECT 字段中剩余的列加入到索引片中
避免回表
缺点
三星索引会让索引片变宽，如果数据量很大，会造成磁盘空间的问题
增加了所以维护的成本
增加页数量
没有理想的索引，只能合理设计索引，需要权衡索引效率和维护成本
自适应Hash索引
show variables like '%adaptive_hash_index';
索引中的索引
分库分表
MVCC
对数据行进行多版本并发控制
好处
1.读写互相不阻塞
2.降低死锁的概率，采用了乐观锁
3.解决一致性读问题
当前读
被锁上的sql都属于当前读
快照读
查历史
Innodb中的mvcc
事务版本号
自增ID
行记录的隐藏列
db_row_id 隐藏的行ID
db_trx_id 操作这个事务的ID
db_roll_ptr 回滚指针
Undo Log
保存历史版本
Read View
来决定数据是否显示
Undo Log+Read View
1.首先获取事务自己的版本号，也就是事务 ID；
2.获取 Read View；
3.查询得到的数据，然后与 Read View 中的事务版本号进行比较；
4.如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
5.最后返回符合规则的数据。
解决幻读
Next-key锁+MVCC
InnoDB 三种行锁
记录锁：针对单个行记录添加锁。
间隙锁（Gap Locking）：可以帮我们锁住一个范围（索引之间的空隙），但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。
Next-Key 锁：帮我们锁住一个范围，同时锁定记录本身，相当于间隙锁 + 记录锁，可以解决幻读的问题。
读未提交
所有更新都可见，所以不需要MVCC
读已提交
每一次ReadView都重新生成，如果操作已提交，他就会从ReadView中移除
可重复度
事务只有在第一次进行读操作时才会生成一个ReadView，后续重复使用
影响数据库性能
网卡流量
避免
减少从服务器的数量
进行分级缓存
避免使用select *
分离业务网络和服务器网络
磁盘IO
风险
磁盘IO性能突然下降
使用更快的磁盘设备
大量使用消耗磁盘性能的计划任务
做好磁盘维护
数据库硬件
大表
慢查询
建立索引可能会造成锁表，5.5版本
造成主从延时
解决方案
分表
难度
分表后主键选择
分表后跨分区
历史数据归档
难点
归档时间的选择
如何进行归档操作，可能会造成主从延迟或者阻塞
数据库结构设计和SQL语句
SQL查询速度
QPS和TPS过高
高并发导致数据库连接占满
高CPU导致CPU资源耗尽
服务器系统
Windows
Liunx
FreeBSD
Solaris
数据库参数配置
CPU资源
64位的CPU一定要工作在64位的系统下
磁盘选择
存储容量
传输速度
访问时间
主轴转速
物理尺寸
存储设备
固态磁盘
场景
大量随机I/O的场景
单线程负载的I/O瓶颈
网络性能
延迟
吞吐量
网络存储
SAN
NAS
网络连接
数据库备份
影响
带宽
IO子系统
PCIE
SSD
Raid10
磁盘
内存选择
主板所能使用的最高频率的内存
尽可能大
可用内存大小
文件系统
事务
ACID
处理大事务
避免处理太多的数据
移除不必要的select
MyISAM
组成
MYD
MYI
FRM
并发性
表级索
表损坏修复
check
repair
索引
全文索引
压缩
myisampack
非事务性应用
只读类应用
空间类应用
GPS数据
InnoDB
数据存储
innodb_file_per_table
事务性存储引擎
支持ACID
日志
Redo Log
实现事务的原子性
支持行级锁
存储引擎层实现
管理共享资源的并发访问
实现事务的隔离性
type
共享锁(读锁)
独占锁(写锁)
粒度
表级锁
lock table mytable
子主题
状态检查
show engine innodb status
事务的场景
大多数OLTP应用
CSV
以文本方式存储在文件中
.CSV文件存储表内容
.CSM文件存储表的元数据
CSV的所有列都是不能为NULL
不支持索引
不适合大表，和在线处理
数据交换的中间表
Archive
以zlib对表数据进行压缩，磁盘I/O更少
只允许在自增ID上建索引
日志和数据采集
Memory
保存在内存中，数据容易造成丢失
只是HASH索引和BTree索引，默认HASH
所有字段都为固定长度
不支持大字段
支持表级锁
查找或者映射表
数据分析产生的中间表
缓存周期性聚合数据的结果
Federated
提供远程Mysql服务器上表的方法
本地不存储数据，数据全部放在远程服务器上
本地需要保存表结构和远程服务器的连接信息
偶尔的统计分析及手工查询
选择
崩溃恢复
备份
存储引擎的特点特性
架构设计
复制
二进制日志增量
记录对表结构的修改事件
查看方式
格式
段的格式binlog_format=STATEMENT
优点
日志记录量相对较小，节约磁盘及网络I/O
必须要记录上下文
可能会主从数据库数据不一致
基于行的格式binlog_format=row
使Mysql主从复制更加安全
对每行数据的修改比段的复制更高
记录日志较大，对磁盘I/O的影响
基于混合日志格式binlog_format=MIXED
在基于段和基于行的日志格式中进行选择
数据量的大小由所执行的SQL语句决定
实现数据读取的负载均衡
工作方式
1.主将变更写入二进制日志
2.从读取主的二进制日志变更并写入到relay_log中
基于日志点
基于GTID
3.在从上重放relay_log中的日志
基于SQL段的日志是从库上重新执行记录的SQL
基于行的日志则是在从库上直接应用对数据库行的修改
影响主从延迟的因素
写入时间
事务的大小
二进制日志传输时间
使用MIXED日志
如何使用多线程复制
版本5.6之后
1.set slave
2.set global slave_parallel_type='logical_clock'
3.set global slave_parallel_workers=4
4.start slave
问题
数据损坏或丢失所引起的主从复制错误
主或从库意外宕机引起的错误
主库上的二进制日志损坏
备库上的中继日志损坏
高可用架构
查询优化
微观反馈
用户反馈性能存在问题
慢查询日志
启动停止记录慢查询日志
slow_query_log
指定慢查询日志的存储路径
slow_query_log_file
指定记录慢查询日志sql的阈值
long_query_time
是否记录未使用索引的sql
log_queries_not_using_indexes
工具
mysqldumpslow
pt-query-digest
sql的解析
查询缓存对sql的影响
Hash
需要对缓存加锁
设置缓存是否可用
query_cache_type
off
设置查询缓存的内存大小
query_cache_size
0
设置查询缓存可用存储的最大值
query_cache_limit
设置数据表被锁后是否返回缓存中的数据
query_cache_wlock_invalidare
设置查询缓存分配的内存块最小单位
query_cache_min_res_unit
生成错误执行计划的原因
执行计划中的成本估算不等同于实际的执行计划
统计信息不准确
不考虑其他比并发的查询
基于固定的规则
不考虑不受控制的成本
各个阶段所消耗的时间
profile
set profiling = 1
show profiles
show profile for query 1
performance_schema
调优目标
让数据库运行的更快．也就晌应的时间更快，同时吞吐量更大
方法
1.选择合适的DBMS
2.表设计优化，优先使用第三范式，减少字段的冗余，适当进行反范式设计
3.逻辑查询优化
优化器可优化的SQL类型
重新定义表的关联顺序
将外链接转化为内连接
使用等价变换规则
子查询优化
变为关联查询
提前终止查询
对in条件进行优化
4.物理优化
确认访问路径
单表扫描：全表扫描，局部扫描
表的关联
5.缓存
6.库级优化
1.主从复制
通过切分的方式进行表的优化
1.分表分库
2.Mysql自带的分区
成本
缓冲池
默认8个
innodb_buffer_pool_instances
大小
innodb_buffer_pool_size
为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，磁盘最小化
减小IO消耗，降低访问成本
如何处理数据不一致
checkpoint 的机制，释放脏页
查询成本
last_query_cost
数据页加载方式
1.内存读取
2.随机读取
3.顺序读取
观察分析状态
S1观察服务器的状态
S2开启慢查询定位慢的SQL
S3利用EXPLAIN来分析执行计划，利用profiling来查看每个步骤的成本
优化方案
A1当属于周期性波动，例如双11，加缓存，更改缓存失效策略
A2Sql等待时间长，调优服务器参数
A3SQL执行时间长，进行SQL优化，索引优化，表结构优化
A4性能瓶颈到达，利用读写分离，分库分表
数据库监控
Nagios
Zabbix
对服务可用性监控
mysqladmin -umonitor_user -p -h ping
telnet ip db_port
对外提供服务
是否可以读写
read_only是否为off
selet @@version
数据库的连接数
查看最大连接数
show variables like ’max_connections‘
显示线程连接的状态
show global status like ’Threads_connected‘
报警阈值
Threads_connected/max_connections > 0.8
性能监控
并发线程请求数量
show global status like ’Threads_running‘
QPS和TPS
阻塞和IO
阻塞
主从复制监控
链路状态
主从复制延迟
show slave status
数据是否一致
pt-table-checksum
服务器资源监控
空间被占满
CPU
Swap分区使用情况
网路IO
存储过程
概念
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。
优劣势
优势
1) 封装性
2) 可增强 SQL 语句的功能和灵活性
3) 可减少网络流量
4) 高性能
5) 提高数据库的安全性和数据的完整性
6) 使数据独立
劣势
1) 难以调试存储过程
2) 开发与维护不容易
3) 可移植性差
关键语法
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
存储过程开始和结束符号:BEGIN .... END
变量定义: DECLARE medinsCode varchar(40);
变量赋值:SET @medinsCode=1
游标
定义游标
DECLARE list CURSOR FOR select..
开启游标
open list
循环游标
xx:Loop
fetch list into medinsCode
关闭游标
close xx
条件语法
if-then-else
case
循环语法
1.while 条件 do...endwhile
2.repeat ...until 循环条件 end repeat;
3. loop ...endloop
0 条评论
回复 删除
下一页
