Mysql--基础知识点--000--Mysql

1 数据类型1.1 DTAETIME、TIMESTAMP的区别DATETIME或TIMESTAMP的区别小结2 sql基础2.1 内连接、左外连接、右外连接、全外连接、笛卡尔积sql连接内连接就是等值连接2.2 等值连接、自连接、自然连接MySQL–基础知识点–64–等值连接/自连接/自然连接/外连接中的 1、2、3、52.3 SQL查询语句的执行顺序优先级 低Mysql面试题 中的 1.102.4 三大范式优先级 低Mysql面试题 中的 1.22.5 in vs exist优先级 低Mysql–基础知识点–94–in vs exist3 查询优化3.1 SQL查询优化的一些方法Mysql–基础知识点–86–慢查询 中 2.13.4 请简述项目中优化SQL语句执行效率的方法从哪些方面SQL语句性能如何分析Mysql经典面试题(建议收藏) 中 274 性能分析4.1 processlistMysql–基础知识点–91.2–processlist4.2 慢查询日志Mysql–基础知识点–91.1–慢查询日志4.3 profileMySQL–基础知识点–53–profile分析SQL语句性能消耗4.4 explainMysql–基础知识点–91–explain4.5 traceMYSQL 索引分析工具——trace4.7 慢查询4.7.1 排查详细排查过程Mysql–基础知识点–86–慢查询 中 34.7.2 慢查询解决方案Mysql–基础知识点–86–慢查询 中 24.8 cpu飙升排查与处理排查过程1 使用 top 命令观察确定是 mysqld 导致还是其他原因。2 如果是 mysqld 导致的show processlist查看 session 情况确定是不是有消耗资源的 sql 在运行。3 找出消耗高的 sql看看执行计划是否准确索引是否缺失数据量是否太大。处理kill 掉这些线程同时观察 cpu 使用率是否下降进行相应的调整比如说加索引、改 sql、改内存参数重新跑这些 SQL。其他情况也有可能是每个sql消耗资源并不多但是突然之间有大量的session连进来导致cpu飙升这种情况就需要跟应用一起来分析为何连接数会激增再做出相应的调整比如说限制连接数等。6 索引6.1 索引不选择二叉排序树、红黑树、b树、跳表、哈希表而选择b树的原因二叉排序树树的高度过高时间复杂度为log₂N且可能退化成单链表范围查询需中序遍历效率低。红黑树二叉平衡树每个节点只有一个数据树的高度仍较高I/O性能不如B树范围查询需中序遍历效率低。B树多叉平衡树时间复杂度为logₘN同B树一样但每个节点有多个索引和数据(不像B树非叶子节点只有索引)一次I/O放入内存的索引没有B树多I/O性能不如B树范围查询效率不如B树非叶子节点存储数据指针可能需要回溯。跳表由多层构成底层存储索引和数据其余上层存储索引每一上层是邻接下层索引的1/2相对于B树高度较高时间复杂度为log₂N。难以实现事务和数据持久性跳表的更新操作可能涉及多个层级实现事务和数据持久性要求更复杂。空间开销较大跳表需要额外的指针来连接不同层级占用的内存空间较多。B树多差平衡树时间复杂度为logₘN非叶子节点只有索引一次I/O可以将更多的索引放入内存I/O效率高叶子节点既有索引也有数据且是双向链表利于范围查询。6.2 哈希索引使用场景适合使用哈希索引适合的情况6.3 哈希索引解决冲突的方法链地址法6.4 建立索引时要注意些什么MySQL进阶之六索引的使用与设计原则 中的6.56.5 索引失效的场景Mysql索引失效的场景7 锁7.1 mysql里有哪些锁[1] Mysql面试题 中的 5.1[2 ] MySQL 的 AUTO-INC 锁对[1] 的补充[1]中没有说明AUTO-INC锁7.2 innodb 间隙锁加锁详解一文掌握MySQL唯一索引是如何加锁的mysql data_lock表lock_mode取值范围需要自己动手测一下印象更深刻7.3 数据库的表锁和行锁的区别Mysql面试题 中的 5.27.4 MySQL两个线程的update语句同时处理一条数据会不会有阻塞优先级低Mysql面试题 中的 5.37.5 两条update语句处理一张表的不同的主键范围的记录一个10一个15会不会遇到阻塞底层是为什么的优先级低Mysql面试题 中的 5.47.6 如果2个范围不是主键或索引还会阻塞吗优先级低Mysql面试题 中的 5.57.7 一张表自增主键最大id10事务1 插入id11的数据行但还未提交然会事务2 查询select会被阻塞吗优先级低情况1普通的select 不会阻塞普通的select是快照读不会加锁因为不会加锁所以不会阻塞情况2select … lock in share mode / for update 会被阻塞该查询是当前读会加锁因为会加锁所以会阻塞只有当事务1 commit后事务2才会继续执行。7.7 一张表自增主键最大id10事务1 插入id11的数据行但还未提交然后事务2 插入id12的数据行会被阻塞吗优先级高Mysql–基础知识点–21.1–一张表自增主键最大id20事务1 插入id21的数据行但还未提交然后事务2 插入id22的数据行是否会被被阻塞7.8 如何避免死锁优先级高MySQL–基础知识点–21–锁表锁、行锁共享锁排它锁间隙锁与避免死锁 中的 47.9 讲一下乐观锁悲观锁什么场景用落实到代码上代码又是怎么实现的优先级高MySQL–基础知识点–19–乐观锁/悲观锁8 事务8.1 事务的四大特性原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败一致性Consistency事务完成时必须使所有的数据都保持一致状态。隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的8.2 事务的四大特性基于什么实现持久性是通过 redo log 重做日志来保证的原子性是通过 undo log回滚日志 来保证的隔离性是通过 MVCC多版本并发控制 或锁机制来保证的一致性则是通过持久性原子性隔离性来保证8.3 脏读、不可重复读、幻读是什么Mysql面试题 中的 2.58.4 事务隔离级别Mysql面试题 中的 2.68.5 MVCCMysql面试题 中的 3.1.5MVCC ReadView 隐藏字段 undo log8.6 事务隔离机制的原理8.6.1 读未提交select不会加读锁update、insert、delete会加记录写锁存在脏读、不可重复读、幻读8.6.2 读已提交记录锁 MVCC 整个事务期间每次select的快照读ReadView解决了脏读但没解决不可重复读、幻读8.6.3 可重复读next-key lock[记录锁gap锁] MVCC 整个事务期间可见第一次select的快照读ReadView解决了脏读、不可重复读解决了一定程度的幻读 但 并没完全解决幻读。没有完全解决幻读的原因8.6.4 串行化是通过行级锁来实现的序列化隔离级别下普通的 select 查询是会对记录加 S 型的 next-key 锁其他事务就没没办法对这些已经加锁的记录进行增删改操作了普通的 select * from a 也会加读锁解决了脏读、不可重复读、幻读8.7 可重读隔离级后怎么保证不发生幻读尽量在开启事务之后马上执行 select … for update 这类锁定读的语句因为它会对记录加 next-key lock从而避免其他事务插入一条新记录就避免了幻读的问题。8.8 滥用事务或者一个事务里有特别多sql的弊端事务的资源在事务提交之后才会释放的比如存储资源、锁。如果一个事务特别多 sql锁定的数据太多容易造成大量的死锁和锁超时。回滚记录会占用大量存储空间事务回滚时间长在MySQL (opens new window)中实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值通过回滚操作都可以得到前一个状态的值sql 越多所需要保存的回滚数据就越多。执行时间长容易造成主从延迟主库上必须等事务执行完成才会写入binlog再传给备库。所以如果一个主库上的语句执行10分钟那这个事务很可能就会导致从库延迟10分钟。9 两阶段提交9.1 两阶段提交的过程优先级 高Mysql–基础知识点–93–两阶段提交 中的19.2 为什么要两阶段提交呢直接提交不行吗优先级 高Mysql–基础知识点–93–两阶段提交 中的29.3 崩溃恢复时的处理优先级 高Mysql–基础知识点–93–两阶段提交 中的310 日志10.1 mysql中日志类型优先级 高Mysql–基础知识点–88–各种mysql日志中的110.2 binlog是server层的还是引擎层优先级 高binlog日志是server层处理的10.3 redo log 磁盘文件组优先级 低Mysql–基础知识点–88–各种mysql日志中的2.110.4 redo log 刷盘策略优先级 低Mysql–基础知识点–88–各种mysql日志中的2.210.5 undo log 删除机制优先级 低Mysql–基础知识点–88–各种mysql日志中的3.210.6 undo log 的存储与刷盘时机优先级 低Mysql–基础知识点–88–各种mysql日志中的3.310.7 bin log 三种日志类型优先级 低Mysql–基础知识点–88–各种mysql日志中的4.110.8 bin log 刷盘时机优先级 低由sync_binlog参数控制10.9 怎么分析slow log优先级 低Mysql–基础知识点–88–各种mysql日志中的610.10 有了undolog为啥还需要redolog优先级 高Mysql–基础知识点–88–各种mysql日志中的8.110.11 能不能只用 binlog 不用redo log优先级 高Mysql–基础知识点–88–各种mysql日志中的8.211 分库分表分区11.1 分库、分表、分区是什么分库将一个数据库中的数据分到多个库中分表同一张表中的数据分到多个表中分区同一张表分多个区但还是在同一个表中11.2 分库、分表、分区的场景https://blog.csdn.net/Chasing__Dreams/article/details/14020301811.3 分库、分表、分区策略分库水平分库所有库中的表结构都是相同的然后将原库中的各表数据按一定策略分到各库中的各表中分库策略hash、range …垂直分库将一个库中不同的表根据不同的业务分到不同的库中分表水平分表将表按行拆分分表策略hash、range …垂直分表列的访问频率将经常被查询的列如用户的基本信息用户名、邮箱与不常访问的列如用户的详细描述、历史记录分开存储。列的数据类型和大小将占用存储空间较大的列如 BLOB、TEXT 类型拆分到单独的表中以减少主表的存储开销。列的更新频率将不常更新的列与频繁更新的列分开存储以减少锁竞争和更新开销。列的敏感性将敏感数据如密码、信用卡信息与普通数据分开存储以提高安全性。业务逻辑需求根据业务模块的需求将相关列拆分为独立的表以支持更灵活的业务扩展。分区范围分区Range Partitioning按字段值范围划分例如按日期范围分区。列表分区List Partitioning按字段值的列表划分例如按地区划分。哈希分区Hash Partitioning按字段值的哈希值划分例如按用户ID哈希分区。各种策略的具体实现11.4 分库分表常用的工具sharding-jdbc‌Mycat‌11.5 分库分表带来的问题优先级 低事务问题‌需要使用分布式事务。跨库JOIN问题‌需要在业务代码中关联或数据异构。跨节点操作问题‌count、order by、group by等操作需要特殊处理。数据迁移和扩容问题‌需要规划数据迁移和扩容策略。ID问题‌需要保证全局唯一ID。运维12 架构12.1 mysql体系结构优先级 高MySQL(一)整体架构12.2 Innodb逻辑存储架构优先级 低【MySQL】InnoDB 基本了解存储结构中的内存结构和磁盘结构整个内存架构是为了在 增删改查时尽可能低的访问磁盘进行的IO操作。buffer pool 是可以减少在 增删改查时尽可能低的访问磁盘进行的IO操作 包含数据页、索引页、undo log 页chang buffer 对修改含有非唯一二级索引数据行 场景时的进一步优化在该场景进一步减少访问磁盘的操作log buffer 中只有redo log。bin log 在server层不在引擎层。12.3 自适应哈希索引优先级 低Mysql–基础知识点–85.1–Innodb自适应哈希索引自适应哈希索引是对buffer pool中高查询频率的页的哈希哈希表键为索引值的前缀值为数据页的物理地址13 部署相关13.1 主从复制的具体过程https://blog.csdn.net/Chasing__Dreams/article/details/11635237513.2 什么时候主从复制什么时候分库分表分区主从复制场景读写分离当读操作远多于写操作时通过主从复制实现读写分离减轻主库压力。高可用性主库故障时可以快速切换到从库实现故障转移。备份与灾难恢复从库可以作为数据的备份防止数据丢失。数据分析从库可以用于离线数据分析避免影响主库性能。分库分表分区场景看本文 11.213.3 GTID优先级 高MySQL–基础知识点–79–GTID13.4 几种集群架构Docker–基础知识点–27–部署MySQL集群 中的 513.5 双主架构如何避免回环复制优先级 高Mysql–基础知识点–79.1–双主架构如何避免回环复制13.6 主从复制延迟的原因及解决方案主从同步延迟的原因一个服务器开放N个链接给客户端来连接的这样有会有大并发的更新操作但是从服务器的里面读取binlog的线程仅有一个当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致主服务器的SQL大量积压未被同步到从服务器里。这就导致了主从不一致也就是主从延迟。主从同步延迟的解决办法两个角度考虑在有一定主从延迟的情况下如何读取到最新数据写操作后的读操作指定发给数据库主服务器例如注册账号完成后登录时读取账号的读操作也发给数据库主服务器。这种方式和业务强绑定对业务的侵入和影响较大如果哪个新来的程序员不知道这样写代码就会导致一个bug。读从机失败后再读一次主机这就是通常所说的二次读取二次读取和业务无绑定只需要对底层数据库访问的API进行封装即可实现代价较小不足之处在于如果有很多二次读取将大大增加主机的读操作压力。例如黑客暴力破解账号会导致大量的二次读取操作主机可能顶不住读操作的压力从而崩溃。关键业务读写操作全部指向主机非关键业务采用读写分离例如对于一个用户管理系统来说注册登录的业务读写操作全部访问主机用户的介绍、爰好、等级等业务可以采用读写分离因为即使用户改了自己的自我介绍在查询时却看到了自我介绍还是旧的业务影响与不能登录相比就小很多还可以忍受。加快主从复制主服务器要负责更新操作对安全性的要求比从服务器要高所以有些设置参数可以修改比如sync_binlog1innodb_flush_log_at_trx_commit 1 之类的设置等。选择更好的硬件设备作为slave。把一台从服务器当度作为备份使用 而不提供查询 它的查询负载下来了 执行relay log 里面的SQL效率自然就高了。增加从服务器喽这个目的还是分散读的压力从而降低服务器负载。13.7 不停机扩容的实现实际上不停机扩容实操起来是个非常麻烦而且很有风险的操作当然面试回答起来就简单很多。第一阶段在线双写查询走老库建立好新的库表结构数据写入久库的同时也写入拆分的新库数据迁移使用数据迁移程序将旧库中的历史数据迁移到新库使用定时任务新旧库的数据对比把差异补齐第二阶段在线双写查询走新库完成了历史数据的同步和校验把对数据的读切换到新库第三阶段旧库下线旧库不再写入新的数据经过一段时间确定旧库没有请求之后就可以下线老库14 引擎相关14.1 InnoDB 与 MyIsam的区别InnoDBMyIsam事务支持不支持锁行级锁表级锁聚集索引/非聚集索引主键聚集索引其余索引都是非聚集索引所有索引都是非聚集索引全文索引mysql5.6之前不支持全文索引支持主键强制有键[聚集索引]非强制有主键[有的话是非聚集索引]外键支持不支持存储文件.ibd.MYI .MYD适用场景写密集型电商、支付系统尤其在高并发事务场景读密集型如CMS、数据分析尤其在无事务要求的场景崩溃恢复有完善的崩溃恢复机制可能需要人工干预修复表15 百万级别数据15.1 百万级别以上数据的删除方法关于索引由于索引需要额外的维护成本因为索引文件是单独存在的文件所以当我们对数据的增加修改删除都会产生额外的对索引文件的操作这些操作需要消耗额外的IO会降低增/改/删的执行效率。所以在我们删除数据库百万级别数据的时候查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引然后删除其中无用数据删除完成后重新创建索引创建索引也非常快15.2 百万千万级大表添加字段的方法当线上的数据库数据量到达几百万、上千万的时候加一个字段就没那么简单因为可能会长时间锁表。大表添加字段通常有这些做法通过中间表转换过去创建一个临时的新表把旧表的结构完全复制过去添加字段再把旧表数据复制过去删除旧表新表命名为旧表的名称这种方式可能回丢掉一些数据。用pt-online-schema-change pt-online-schema-change 是 percona 公司开发的一个工具它可以在线修改表结构它的原理也是通过中间表。先在从库添加 再进行主从切换如果一张表数据量大且是热表读写特别频繁则可以考虑先在从库添加再进行主从切换切换后再将其他几个节点上添加字段。16 触发器优先级 低MySQL–基础知识点–81.3–TRIGGER17 存储过程、存储函数17.1 存储过程 vs 存储函数优先级 低MySQL–基础知识点–81.1–存储过程 vs 存储函数18 事件优先级 低MySQL–基础知识点–81.1–EVENT