长泰县建设局网站,企业网站微信建设,大兴西红门网站建设,网站建设校长信箱设计方案Mysql的知识点目录
重点#xff1a;架构,引擎,索引#xff0c;锁机制,事务机制,日志机制#xff0c;集群,调优
3、Mysql索引
索引概念 覆盖索引#xff1a; 条件列和结果列都在索引中索引下推#xff1a; 查询会先过滤条件列#xff0c;然后回表查数据最左前缀匹配架构,引擎,索引锁机制,事务机制,日志机制集群,调优
3、Mysql索引
索引概念 覆盖索引 条件列和结果列都在索引中索引下推 查询会先过滤条件列然后回表查数据最左前缀匹配 查询条件会从最左开始匹配索引列回表经过索引查询后不满足还需要通过ID查询所有数据 索引失效原因 or,!,not inlike等 创建索引原则 最左前缀原则读多写少创建索引写多的不适宜避免破坏索引的查询优先在原有基础上创建索引避免新建索引区分度低列外键不建索引删除不再使用进货很少用的索引
4、mysql锁机制
锁机制 乐观锁(MVCC机制),悲观锁锁粒度 全局锁表锁行锁叶索间隙锁兼容性共享锁(S锁)排他锁(X锁)锁的模式记录锁(行锁)间隙锁意向锁(分为读写插入意向锁),next-key锁自增所死锁的解决 互斥条件请求和保持条件环路等待条件不剥夺条件解决思路切断环路死锁与索引密不可分解决索引问题需要合理优化你的索引在同一个事务中尽可能做到一次锁定所需要的所有资源减少死锁产生概率
Mysql的主从复制读写分离 mysql主从同步延迟 原因主库在高并发写操作时由于某些SQL执行时间较长或者SQL锁表导致主库的SQL积压不能马上同步到从库导致场景高并发场景时修改数据库字段或者发生长事务时会导致主从延迟解决办法 从库读操作sync_binlog0 , 提交SQL执行效率或者使用更好的设备硬件上(1)使用更好的设备做从库(2)增加从库的机器数量(3)把某一台从库当做备用不处理查询操作 判断主从延迟通过show slave status通过Seconds_Behind_Master参数判断 主从复制延迟问题 mysql读写分离 应用层控制DML语句在主库操作–同步到从库应用层控制SQL语句在从库操作应用层在操作读写事件是可以要求强制读主库保证一致性 mysql主从延迟 主要根据业务需求要求强一致性读写全部在主库弱一致性一般读在从库事务读写都在主库最终一致性写在主库从在读库
Mysql分库分表
分库分表中间件方案 当当-shardingjdbc阿里-mycat阿里-tddl,阿里-cobar58同城-Oceanus,阿里-OneProxy谷歌-vitess 分库分表的问题 事务问题 方式一使用分布式事务简单有效但是性能代价高方式二将跨库分布式事务拆分成多个单库的小失误通过程序来控制小事务性能上优势但是破坏了耦合性 跨结点join问题 方式统一单表操作通过程序控制 跨结点count,orderby,groupby 方式和join类似在每个节点执行然后再做合并 id问题 Redis自增ID雪花算法生成ID数据库维护一个sequence 跨分片排序分页问题 尽量避免出现跨库的查询分页如果无法避免采用内存分页方式 数据迁移,容量规划,扩容问题 提前规划
主从延迟问题
主从同步步骤 主库发生更新写入到bin_log从库发起连接到主库主库创建一个binlog dump thread把binlog的内容发送到从库通过IO线程读取binlog内容并写入到relay log从库还会创建一个SQL线程从relay log里面读取内容并执行 原因 从库的机器性能差从库访问压力大大事务的执行主库的DDL(alter、drop、create)锁冲突从库的复制能力 解决办法: 主服务负责更新 安全性要高所以设置参数 例如sync_binlog1例如innodb_flush_log_at_trx_commit1 更好的设备作为从库或者设置更多的从库某台从库不提供查询专门提供bin_log同步到从库降低多线程大事务并发的概率优化业务逻辑优化SQL避免慢SQL减少批量操作建议写脚本以update-sleep这样的形式完成。尽量采用短的链路也就是主库和从库服务器的距离尽量要短提升端口带宽减少binlog传输的网络延时。实时性要求的业务读强制走主库从库只做灾备备份。
关于BufferPool缓冲池
mysql数据存储在磁盘会根据sql的需要通过索引等方式从磁盘中刷到缓冲池mysql的sql操作会现在磁盘中操作事务完成后通过Bin_log刷盘到磁盘。
数据表设计
数据表类型
第一类流水表日志表第二类状态型记录核心数据第三类配置表数据量少不需要优化
面试谈谈Mysql调优
1、调优过程
1、定位问题2、分析问题3、解决问题4、验证结果
第一步定位问题
通过服务监控找到服务卡顿时或服务超时的时间分析接口通过接口定位代码进一步定位到执行的SQL查看执行时长确认现场
第二步分析问题
通过数据库监控查看发生问题时DB集群的数据的CPU内存IO磁盘网络线程数等参数通过分析现场参数结合SQL判断问题所在例如以下 CPU过高检查SQL中是否有运算是否吞吐量过高等内存过高SQL是否有用到索引是否频繁回表IO磁盘过高是否有大表join等问题网络延迟高是否云服务网络问题线程问题是否长事务导致锁问题
第三步解决问题
系统负载过高进行DB扩容使用分库分表读写分离等手段应用层增加缓存等由于SQL原因进行SQL优化加索引使用覆盖索引删除冗余索引等一般通过explain分析优化长事务缩短事务流程优化应用程序例如加入分布式缓存以及DAO缓存等
第四步验证结果
设定期望例如减少请求响应时间降低系统负载等先在压测环境更新然后通过压测验证压测没有问题再部署到生产
Mysql的调优
Mysql的底层知识点
日志种类以及作用
general_log 一般日志error_log 错误日志slow_query_log 慢查询日志relay_log 中继日志–数据同步故障恢复起作用 relay log日志文件具有与bin log日志文件相同的格式relay log起到一个中转的作用slave先从主库master读取二进制日志数据写入从库本地后续再异步由SQL线程读取解析relay log为对应的SQL命令执行relay log起到一个中转的作用slave先从主库master读取二进制日志数据写入从库本地后续再异步由SQL线程读取解析relay log为对应的SQL命令执行 bin_log 归档日志–数据持久性中起作用 记录数据库所有的DDL和DML记录保证数据库数据完整性 undo_log 回滚日志–事务隔离性和原子性中起作用 undo log属于逻辑日志如其名主要起到回滚的作用它是保证事务原子性的关键。记录的是数据修改前的状态在数据修改的流程中同时会记录一条与当前操作相反的逻辑日志到undo log中。如果事务执行时提交rollback则会执行undo_log保证事务回滚 redo_log 重做日志–数据持久性中起作用 redo log属于MySQL存储引擎InnoDB的事务日志。作用类似于备份当出现宕机后恢复时会使用redo_log快速恢复数据
mysql底层知识点
MVCC原理
为了实现高并发事务场景下使用无锁化场景解决数据幻读的问题实现原理 关键隐藏字段当前读快照读事务快照redolog等配合完成1、隐藏字段db_row_id行ID,db_trx_id事务ID,db_roll_ptr回滚指针2、undo_log用作操作先备份数据如果出现异常后回滚数据3、当前读在读锁下读取最新数据,快照读不一定是最新数据类似于缓存4、事务快照readView 事务快照是表共享空间的建立的事务快照用于区分事务前后顺序readView是事务快照读产生的读视图如果读操作是在事务之前则可见如果在事务之后则不可见用于控制可见性
