网络营销的主要传播渠道,上海百度提升优化,wordpress要多大空间,新手怎么做电商运营提示#xff1a;文章写完后#xff0c;目录可以自动生成#xff0c;如何生成可参考右边的帮助文档 文章目录 前言一、知识体系二、Mysql-优化1. 优化-如何定位慢查询① 问题引入② 解决方案③ 问题总结④ 实战面试 2. 优化-sql执行很慢#xff0c;如何解决① 问题引入② 解… 提示文章写完后目录可以自动生成如何生成可参考右边的帮助文档 文章目录 前言一、知识体系二、Mysql-优化1. 优化-如何定位慢查询① 问题引入② 解决方案③ 问题总结④ 实战面试 2. 优化-sql执行很慢如何解决① 问题引入② 解决方案③ 问题总结④ 实战面试 3. 优化-索引概念① 问题引入② 数据结构对比③ 问题总结④ 实战面试 4. 优化-聚簇索引与非聚簇索引① 问题引入② 聚簇索引③ 问题总结④ 实战面试 5. 优化-覆盖索引① 问题引入② 覆盖索引③ Mysql超大分页处理④ 问题总结⑤ 实战面试 6. 优化-索引创建的原则① 问题引入② 索引创建的原则③ 问题总结④ 实战面试 7. 优化-什么情况下索引会失效① 问题引入② 什么情况下索引会失效③ 问题总结④ 实战面试 8. 优化-SQL优化的经验① 问题引入② sql优化经验③ 问题总结④ 实战面试 三、Mysql-其他面试题1. 事务的特性ACID① 问题引入② ACID是什么③ 问题总结④ 实战面试 2. 并发事务① 问题引入② 并发事务问题③ 事务隔离④ 问题总结⑤ 实战面试 3. 事务-undo log和redo log的区别① 问题引入② redo log重做日志③ undo log回滚日志④ 问题总结⑤ 实战面试 4. 事务-MVCC多版本并发控制难① 问题引入② MVCC多版本并发控制③ 问题总结④ 实战面试 5. Mysql主从同步原理① 问题引入② 主从同步原理③ 问题总结④ 实战面试 6. Mysql分库分表① 问题引入② 拆分策略③ 问题总结 引用说明 前言
本文主要记录mysql的查询、索引、sql优化、事务、主从同步、分库分表等问题的分析与面试回答示例。 提示以下是本篇文章正文内容下面案例可供参考
一、知识体系 二、Mysql-优化
1. 优化-如何定位慢查询
① 问题引入 聚合查询多表查询表数据量过大查询深度分页查询
表象页面加载过慢、接口压测响应时间过长超过1s)
② 解决方案
方案一开源工具 调试工具Arthas 运维工具Prometheus 、Skywalking 方案二Mysql自带慢日志 慢查询日志记录了所有执行时间超过指定参数(long_query_time单位:秒默认10秒的所有SQL语句的日志如果要开启慢查询日志需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息 配置完毕之后通过以下指令重新启动MySQL服务器进行测试查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
③ 问题总结 ④ 实战面试 2. 优化-sql执行很慢如何解决
① 问题引入 聚合查询新增临时表多表查询优化sql语句结构表数据量过大查询添加索引深度分页查询
② 解决方案
一个SQL语句执行很慢如何分析
可以采用EXPLAIN 或者DESC命令获取MySQL如何执行SELECT语句的信息 展示SQL执行的情况部分字段说明如下
possible_key 当前sql可能会使用到的索引key 当前sql实际命中的索引key_len 索引占用的大小Extra 额外的优化建议 type这条sql的连接的类型性能由好到差为NULL、system、const、 eq_ref、ref、range、index、all system查询系统中的表 const根据主键查询常用 eq_refsql的查询条件是主键索引查询或唯一索引查询返回一条数据 refsql的查询条件是索引查询返回多条数据 range索引范围查询 index全索引查询遍历整个索引数效率不高需要优化 all全盘扫描需要优化
③ 问题总结 可以采用MySQL自带的分析工具EXPLAIN
通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)通过type字段查看sql是否有进一步的优化空间是否存在全索引扫描或全盘扫描通过extra建议判断是否出现了回表的情况如果出现了可以尝试添加索引或修改返回字段来修复
④ 实战面试 3. 优化-索引概念
① 问题引入 索引(index) 是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足特定查找算法的数据结构B树)这些数据结构以某种方式引用 (指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。 维护树的数据结构提高查找效率减少IO的操作 B树、二叉树、红黑树、B树
② 数据结构对比
MySQL默认使用的索引底层数据结构是B树。再聊B树之前我们先聊聊二叉树和B树 B-TreeB树是一种多叉路衡查找树相对于二叉树B树每个节点可以有多个分支即多叉。以一颗最大度数(max-degree)为5(5阶)的b-tree为例那这个B树每个节点最多存储4个key BTree是在BTree基础上的一种优化使其更适合实现外存储索引结构InnoDB存储引擎就是用BTree实现其索引结构
B树与B树对比:
磁盘读写代价B树更低非叶子节点不存储数据;查询效率B树更加稳定数据都存储在叶子节点;B树便于扫库和区间查询叶子节点之间使用的双向指针
③ 问题总结 ④ 实战面试 4. 优化-聚簇索引与非聚簇索引
① 问题引入 什么是聚集索引什么是二级索引非聚集索引)什么是回表?
② 聚簇索引 聚集索引选取规则: 1.如果存在主键主键索引就是聚集索引。 2.如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引。 3.如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 回表查询先通过二级索引找到对应的主键值在用主键值在聚集索引中去找整行的数据 ③ 问题总结 ④ 实战面试 5. 优化-覆盖索引
① 问题引入 覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到。 判断下面的SQL哪些是覆盖索引为什么
select * from tb_user where id 1
是因为根据id查询的id默认是主键索引就是聚簇索引聚簇索引中对应的是整行的记录select idname from tb_user where name Arm
是因为通过name这个二级索引也可以找到id是一次性可以查询出来id和name的select id,namegender from tb_user where name Arm
不是需要通过回表查询才能获取到gender② 覆盖索引
覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到。
③ Mysql超大分页处理
在数据量比较大时如果进行limit分页查询在查询时越往后分页查询效率越低。 我们一起来看看执行limit分页查询耗时对比: 因为当在进行分页查询时如果执行limit 9000000,10此时需要MySQL排序前9000010记录仅仅返回9000000 - 9000010 的记录其他记录丢弃查询排序的代价非常大。
优化思路 一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化
④ 问题总结 ⑤ 实战面试 6. 优化-索引创建的原则
① 问题引入 先陈述自己在实际的工作中是怎么用的 主键索引 唯一索引 根据业务创建的索引(复合索引)
② 索引创建的原则 针对于数据量较大且查询比较频繁的表建立索引。单表超过10万数据 (增加用户体验) 针对于常作为查询条件(where)、排序order by)、分组(group by) 操作的字段建立索引。 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高比如一个地区字段大部分都是北京市就不适合创建索引。 如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引比如简介内容想要创建索引就采用前缀索引。 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。
③ 问题总结 ④ 实战面试 7. 优化-什么情况下索引会失效
① 问题引入 索引失效的情况有很多可以说一些自己遇到过的。 给tb_seller创建联合索引字段顺序: name,status,address 那快读判断索引是否失效了呢?执行计划explain
② 什么情况下索引会失效 违反最左前缀法则 如果索引了多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。匹配最左前缀法则走索引。 有效的情况如下图所示 失效的情况如下图所示违反最左前缀法则 范围查询右边的列不能使用索引。 根据前面的两个字段name , status查询是走索引的但是最后一个条件address没有用到索引。 不要在索引列上进行运算操作索引将失效。 字符串不加单引号造成索引失效。 由于在查询是没有对字符串加单引号MySQL的查询优化器会自动的进行类型转换造成索引失效。 以%开头的Like模糊查询索引失效。如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。
③ 问题总结 ④ 实战面试 8. 优化-SQL优化的经验
① 问题引入 表的设计优化 索引优化参考优化创建原则和索引失效 SQL语句优化 主从复制、读写分离 分库分表
② sql优化经验
表的设计优化(参考阿里开发手册《嵩山版》) a. 比如设置合适的数值(tinyint int bigint)要根据实际情况选择 b. 比如设置合适的字符串类型(char和varchar) char定长效率高.varchar可变长度效率稍低SQL语句优化 a. SELECT语句务必指明字段名称避免直接使用select* ) b. SQL语句要避免造成索引失效的写法 c. 尽量用union all代替unionunion会多一次过滤效率低 d. 避免在where子句中对字段进行表达式操作 e. Join优化能用inner join就不用left join right join如必须使用一定要以小表为驱动内连接会对两个表进行优化优先把小表放到外边把大表放到里边。left join或right join不会重新调整顺序主从复制、读写分离 如果数据库的使用场景读的操作比较多的时候为了避免写的操作所造成的性能影响可以采用读写分离的架构。 读写分离解决的是数据库的写入影响了查询的效率。
③ 问题总结 ④ 实战面试 三、Mysql-其他面试题
1. 事务的特性ACID
① 问题引入 事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。
② ACID是什么
原子性(Atomicity) :事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性(Consistency) :事务完成时必须使所有的数据都保持一致状态。隔离性(Isolation) :数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性(Durability) :事务一旦提交或回滚它对数据库中的数据的改变就是永久的。
③ 问题总结 ④ 实战面试 2. 并发事务
① 问题引入 ● 并发事务问题脏读、不可重复读、幻读 ● 隔离级别读未提交、读已提交、可重复读、串行化
② 并发事务问题 脏读一个事务读到另外一个事务还没有提交的数据。 不可重复读一个事务先后读取同一条记录但两次读取的数据不同称之为不可重复读。 幻读一个事务按照条件查询数据时没有对应的数据行但是在插入数据时又发现这行数据已经存在好像出现了“幻影”。
③ 事务隔离
对并发事务进行隔离有以下隔离级别如下图所示 注意事务隔离级别越高数据越安全但是性能越低。
④ 问题总结 ⑤ 实战面试 3. 事务-undo log和redo log的区别
① 问题引入 undo log和redo log都是mysql的日志文件但是功能不一样。
缓冲池buffer pool主内存中的一个区域里面可以缓存磁盘上经常操作的真实数据在执行增删改查操作时先操作缓冲池中的数据(若缓冲池没有数据则从磁盘加载并缓存)以一定频率刷新到磁盘从而减少磁盘IO,加快处理速度数据页page是InnoDB 存储引擎磁盘管理的最小单元每个页的大小默认为16KB。页中存储的是行数据
② redo log重做日志
redo log 重做日志记录的是事务提交时数据页的物理修改是用来实现事务的持久性。 该日志文件由两部分组成重做日志缓冲redo log buffer 以及重做日志文件redo log file 前者是在内存中后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中用于在刷新脏页到磁盘发生错误时进行数据恢复使用。每个一段时间会进行清理
③ undo log回滚日志
undo log 回滚日志用于记录数据被修改前的信息作用包含两个提供回滚和MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样它是逻辑日志。
可以认为当delete一条记录时undo log中会记录一 条对应的insert记录 反之亦然,当update一条记录时, 它记录一条对应相反的update记录。 当执行rollback时 就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。undo log可以实现事务的一致性和原子性
④ 问题总结 ⑤ 实战面试 4. 事务-MVCC多版本并发控制难
① 问题引入 锁排他锁(如一 个事务获取了一个数据行的排他锁其他事务就不能再获取该行的其他锁) mvcc多版本并发控制
② MVCC多版本并发控制
MVCC全称Multi-Version Concurrency Control多版本并发控制。指维护一个数据的多个版本使得读写操作没有冲突
MVCC的具体实现主要依赖于数据库记录中的隐式字段、undo log日志、readView。
例如下面有4个事务事务2、3、4对该记录都进行了操作那么在事务5中的两条查询语句是查询的哪个事务版本的记录呢
MVCC的实现原理 记录中的隐藏字段 undo log 回滚日志 a. 回滚日志在insert、 update、 delete的时候产生的便于数据回滚的日志。 b. 当insert的时候产生的undo log日志只在回滚时需要在事务提交后可被立即删除。 c. 而update、delete的时候 产生的undo log日志不仅在回滚时需要mvcc版本访问也需要不会立即被删除。 undo log 版本链 不同事务或相同事务对同一条记录进行修改会导致该记录的undo log生成一条记录版本链表链表的头部是最新的旧记录链表尾部是最早的旧记录。 readview ReadView (读视图)是 快照读 SQL执行时MVCC提取数据的依据记录并维护系统当前活跃的事务(未提交的) id。 a. 当前读读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。对于我们日常的操作如: select … lock in share mode(共享锁)select … for update、update、 insert、 delete(排他锁)都是一 种当前读。 b. 快照读简单的select (不加锁)就是快照读,快照读,读取的是记录数据的可见版本有可能是历史数据不加锁是非阻塞读。 Read Committed每次select, 都生成一个快照读。 Repeatable Read开启事务后第一个select语句才是快照读的地方。
举例说明 当前读不受隔离级别影响事务A的两次select都是读到的最新的数据 快照读不同的隔离级别不一样RC读出来不一样RR读出来都是相同的获得的结果就是快照读
ReadView包含的四个核心字段 不同的隔离级别生成ReadView的时机不同 ➢ READ COMMITTED 在事务中每一 次执行快照读时生成ReadView。 ➢ REPEATABLE READ仅在事务中第一 次执行快照读时生成ReagView后续复用该ReadView。
③ 问题总结 ④ 实战面试 5. Mysql主从同步原理
① 问题引入 主从架构主库用于写数据从库用于读数据 主库与从库如何进行同步的
② 主从同步原理
MySQL主从复制的核心就是二进制日志 二进制日志(bin log) 记录了所有的 DDL (数据定义语言增删改查表结构) 语句和 DML (数据操纵语言增删改查表中的数据) 语句但不包括数据查询 (SELECT、 SHOW) 语句。
复制分成三步:
Master 主库在事务提交时会把数据变更记录在二进制日志文件Binlog中。从库读取主库的二进制日志文件Binlog写入到从库的中继日志Relay Log。slave重做中继日志中的事件将改变反映它自己的数据。 ③ 问题总结 ④ 实战面试 6. Mysql分库分表
① 问题引入 主从架构读写分离分担了访问压力 分库分表的时机
前提项目业务数据逐渐增多或业务发展比较迅速单表的数据量达到1000W或20G以后优化已解决不了性能问题主从读写分离、查询索引…IO瓶颈磁盘IO、网络IO)、CP\瓶颈聚合查询、连接数太多
分库分表主要解决存储的压力
② 拆分策略
垂直拆分与水平拆分
垂直拆分之垂直分库以表为依据根据业务将不同表拆分到不同库中。 特点: 1.按业务对数据分级管理、维护、监控、扩展 2.在高并发下提高磁盘IO和数据量连接数 垂直拆分之垂直分表以字段为依据根据字段属性将不同字段拆分到不同表中。 特点: 1.冷热数据分离 2.减少IO过渡争抢两表互不影响 拆分规则 ● 把不常用的字段单独放在一张表 ● 把text、blob等大字段拆分出来放在附表中
水平拆分之水平分库将一个库中的数据拆分到多个库中。 特点: 1.解决了单库大数量高并发的性能瓶颈问题 2.提高了系统的稳定性和可用性 路由规则 ● 根据id节点取模 ● 按id也就是范围路由 节点1(1-100万),节点2(100万-200万)
水平拆分之水平分表将一个表的数据拆分到多个表中(可以在同一个库内)。 特点: 1.优化单一表数据量过大而产生的性能问题 2.避免IO争抢并减少锁表的几率
分库分表的策略有哪些
③ 问题总结
常用垂直分库和垂直分表
引用说明
https://www.bilibili.com/video/BV1yT411H7YK?p20vd_source98092b0aee05ae7c890b09fe07f13df4
