一、简述
将MySQL数据库的要点知识进行系统化的梳理总结,便于学习和应用时的查找使用,主要分数据库的基础知识、常用操作的学习、分析调优的思路、存储引擎的理解、高可用方案的了解等等,内容梳理的维度较为宽泛,仅供参考,对细节点的了解还需要借助工具书或其官网文档。
内容较宽泛 后续研究中会增补修订部分内容
二、思维导图
![](https://img-blog.csdnimg.cn/img_convert/16f74ed50c00925a4cab60d21afede5b.png)
三、知识要点
1、索引策略
![](https://img-blog.csdnimg.cn/img_convert/62204c053341aad08260af8eeed828d3.png)
2、日志
错误日志: 记录服务的启动、运行或停止时出现的问题
查询日志:记录建立的客户端连接和执行的语句
二进制日志:记录所有更改数据的语句,可以用于数据复制
慢查询日志: 记录所有执行时间超过long_query_time 的所有查询或不使用索引的查询
3、优化子查询
子查询可以一次性完成很多逻辑上需要多个步骤才能完成得SQL操作,但是需要为子查询结果建立临时表,在数据量大时速度受影响,相对的 使用JOIN连接替代子查询,可以利用查询中的索引,有时效果会更好
4、优化MySQL 服务器
服务器硬件: 选较大内存、配置高速磁盘、合理分布磁盘IO、配置多处理器
参数调优:
key_buffer_size 索引缓冲区的大小
table_cache 同时打开的表的个数
query_cache_size 查询缓冲区大小
sort_buffer_size 排序缓冲区大小
read_buffer_size 每个线程连续扫描时未每个表分配的缓冲区的大小
read_rnd_buffer_size 为每个线程保留的缓冲区的大小
innodb_buffer_pool_size 表和索引的最大缓存
max_connections 最大连接数
innodb_flus_log_at_trx_commit 表示何时将缓冲区的数据写入日志文件 每秒 、每次提交 、 2 每次写日志隔一秒写磁盘
back_log 短时间的新请求的数量
interactive_timeout 关闭前等待的秒数
thread_cache_size 可以复用的线程的数量
wait_timeout 关闭一个连接时等待的秒数 默认 28800
5、复制功能
1、主服务器将数据的改变记录到二进制日志 binary log中
2、从服务器将主服务器的binary log events 复制到它的中继日志 relay log 中
// 包含日志及bin-log的位置 -- 写入到slave 的masterinfo 中
3、从服务器重做中继日志中的事件,将数据的改变与主服务器保持同步
参数
master_host\ master_port\ master_user\ master_password\ master_log_file \ master_log_pos
log_slave_updates 默认不打开, 链式复制时使用
master_connect_retry
read_only
slave_skip_errors 错误号
指定复制的数据库或者表
replicate-do-db \ replicate-do-table \replicate-ignore-db \replicate-ignore-table
replicate-while-do-table
切换主从
1、确保从库relay_log 全部更新 Show processlist ; 看 state "has read all relay log "
2、停止slave , 执行reset master
3、然后配置新的从库 ,如果原先主库能恢复,也可配置成从库
多源复制 : 8.0 新增了一个从库可以从多个主库进行数据的复制,相当于能做数据的归集汇总
6、MySQL Proxy
实现读写分离的中间层
--proxy-backend-addresses 后端mysql服务器地址和端口 主机
--proxy-read-only-backend-addresses 只读的 (从机器
--proxy-skip-profiling 是否禁用查询性能分析
--proxy-lua-script
--deamon
--admin-address 管理端口
--proxy-address 代理监控端口
7、MySQL架构
客户端 :
连接线程处理:处理请求、身份验证、安全性验证
查询缓存和查询分析器:查询的解析、优化、缓存、函数、存储过程、触发器、视图等
优化器:负责存储和获取所有存储在MySQL中的数据
存储引擎:
文件 :
日志文件 (上文中四种) 加 InnoDB 引擎在线Redo日志
数据文件:
.frm 表结构定义
.myd .myi MyISAM 的数据 和索引
.ibd .ibdata innoDB 的数据和索引 共享表空间和独享表空间
其他文件: 系统配置 my.cnf , pid文件 socket 文件
8、隔离级别
9、锁的分析
10、MVVC 多版本并发控制
四、应用场景
五、 参考资料
1、《MySQL 8 从入门到精通》 王英英 著