Mysql
mysql三种日志总结 binlog redo undo 看懂就能明白 https://my.oschina.net/javazhiyin/blog/4517915
mysql事务的流程 https://www.imooc.com/article/247122
InnoDB引擎支持事务，进而有了undo/redo日志，binlog日志是mysql server的日志 https://blog.csdn.net/qq_42651904/article/details/104549645 一条SQL语句更新的流程？ 1、加载磁盘文件到 Buffer Pool 缓冲池中 2、将未修改的数据写入undo日志中，作用：便于数据回滚 3、更新缓存Buffer Pool 的数据 4、写入redo日志，redo日志则是记录了对哪个数据做了修改 5、准备提交事务将redo日志写入磁盘 6、准备提交事务将binlog日志写入磁盘（binlog日志记录对哪个数据做了修改，修改结果是什么） 7、将binlog更新的文件名和更新文件的位置写入redo，并在redo log添加commit标记 8、IO线程随机将 Buffer Pool 缓冲池中的数据刷入磁盘 https://blog.csdn.net/qq_39088066/article/details/102651838 1、undo日志 当更新一条数据的时候，将未修改的数据写入undo日志中 作用：便于数据回滚 2、redo日志 redo日志记录了修改后的值，防止数据丢失 假如我们修改了Buffer Pool缓存中的值，还没有来得及刷新磁盘中的数据，数据库宕机了，那么Buffer Pool缓存中的数据丢失不是让磁盘中这一条数据成了脏数据吗？ 因为有redo日志，可以通过redo日志恢复所以是不要紧的。 redo可以设置以下几种刷盘策略，他是通过参数innodb_flush_log_at_trx_commit配置的 当参数为0时，提交事务不会将redo日志强制刷入磁盘 当参数为1时，提交事务成功一定会将redo日志写入磁盘 当参数为2时，提交事务时将redo日志写入os cache缓存中，1秒后写入磁盘 3、binlog日志 binlog日志也有参数控制刷盘策略，sync_binlog，默认为0表示不是直接写入磁盘，也是先写入os cache缓存中，所以这里建议是吧sync_binlog参数设置为1，强制在提交事务的时候写入磁盘 应用场景：故障恢复 应用场景：主从同步 4、为什么最后redo日志要写入commit标记？ 为了保证binlog日志和redo日志的一致性 假设redo日志刚刚写入磁盘文件后mysql宕机了，不会因为redo日志有数据而binlog没有数据产生数据不一致问题，因为redo日志文件没有最终标记commit，所以这次事务是提交失败的 5、redo log和bin log的区别？ redo log是物理日志，记录了在数据页上做了什么修改；bin log是逻辑日志，记录了sql的原始逻辑 redo log是InnoDB引擎特有的，bin log是mysql server层实现的，所有引擎都会有 redo log是循环写的，有固定空间，bin log是追加写入的,日志文件会切分固定块大小 6、可不可以只要bin log，不要redo log? 不可以 redo log 是循环写不能保证所有的历史数据，这些历史数据只能在 binlog 中找到； binlog 是高可用的基础，高可用的实现原理就是 binlog 复制。 7、事务执行期间，还未提交，如果发生crash，redo log丢失，是否会导致主备不一致？ 不会 这时候 binlog 也还在 binlog cache 里，没发给备库，crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。
mysql慢查询 1，cpu负载高 一般查询语句带有计算逻辑，检查 2，io负载高 一般是查询了没建索引的列
可以设置查询时长，过滤查询时长特别长的SQL
有些SQL虽然出现在慢查询日志中，但未必是其本身的性能问题，可能是因为锁等待，服务器压力高等等。 需要分析SQL语句真实的执行计划，而不是看重新执行一遍SQL时，花费了多少时间 由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的SQL，然后使用Explain工具来逐步调优 了解 MySQL 在执行这条数据时的一些细节，比如是否进行了优化、是否使用了索引等等。 基于 Explain 的返回结果我们就可以根据 MySQL 的执行细节进一步分析是否应该优化搜索、怎样优化索引。
关于sql调优，个人特别推荐美团点评技术团队的几点总结
最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整； 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录； 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
基于本文的思路，关于SQL慢查询的解决可以按照以下的步骤执行：
- 打开慢日志查询，确定是否有SQL语句占用了过多资源，如果是，在不改变业务原意的前提下，对insert、group by、order by、join等语句进行优化。
- 考虑调整MySQL的系统参数： innodb_buffer_pool_size、innodb_log_file_size、table_cache等。
- 确定是否是因为高并发引起行锁的超时问题。
- 如果数据量过大，需要考虑进一步的分库分表
生产环境碰到的实际问题： 线上数据库建立索引导致锁表
https://juejin.cn/post/6844903988375224328 MySQL引擎： InnoDB 引擎(MySQL5.5以后默认使用) 1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。 3）InnoDB支持外键，MyISAM不支持 5）InnoDB不支持FULLTEXT类型的索引 6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。 7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。 8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。 9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’
https://juejin.cn/post/6844904126585765902
索引（在MySQL中也叫做键
MySQL索引底层实现 索引也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考， 哪个数据结构更适合从磁盘中读取数据，或者哪个数据结构能够提高磁盘的IO效率。
Mysql中的索引是用B+树实现 为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何Mysql独独喜欢B+树？ 哈希表：不支持范围查询 完全平衡二叉树：相比于B树，不够矮 B树：B树中的一个节点可以存储多个元素。 B+树：B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。
B+树的优势： B树的优点：一个节点可以存储多个元素，相对于完全平衡二叉树所以整棵树的高度就降低了，磁盘IO效率提高了。 而，B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。
B+树中一个节点到底存多少个元素合适？ B+树中一个节点为一页或页的倍数最为合适。因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出1页，造成资源的浪费； 如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费； 所以为了不造成浪费，所以最后把一个节点的大小控制在1页、2页、3页、4页等倍数页大小最为合适。
B+树中的一个节点存储的内容是： 非叶子节点：主键+指针 叶子节点：数据
Innodb中的主键索引和实际数据时绑定在一起的，也就是说Innodb的一个表一定要有主键索引 https://juejin.cn/post/6844903792861790222 什么是聚集索引? LnnoDB的主键索引就是一个聚集索引(索引和主键对应的数据放在同一个B+树)
mysql事务 https://www.cnblogs.com/exceptioneye/p/5451960.html MySQL 本身不提供事务支持，而是开放了存储引擎接口，由具体的存储引擎来实现，具体来说支持 MySQL 事务的存储引擎就是 InnoDB。
存储引擎实现事务的通用方式是基于 redo log 和 undo log。
简单来说，redo log 记录事务修改后的数据, undo log 记录事务前的原始数据。
所以当一个事务执行时实际发生过程简化描述如下：
先记录 undo/redo log，确保日志刷到磁盘上持久存储。 更新数据记录，缓存操作并异步刷盘。 提交事务，在 redo log 中写入 commit 记录。 在 MySQL 执行事务过程中如果因故障中断，可以通过 redo log 来重做事务或通过 undo log 来回滚，确保了数据的一致性。
这些都是由事务性存储引擎来完成的，但 binlog 不在事务存储引擎范围内，而是由 MySQL Server 来记录的。
那么就必须保证 binlog 数据和 redo log 之间的一致性，所以开启了 binlog 后实际的事务执行就多了一步，如下：
先记录 undo/redo log，确保日志刷到磁盘上持久存储。 更新数据记录，缓存操作并异步刷盘。 将事务日志持久化到 binlog。 提交事务，在 redo log 中写入commit记录。 这样的话，只要 binlog 没写成功，整个事务是需要回滚的，而 binlog 写成功后即使 MySQL Crash 了都可以恢复事务并完成提交。
要做到这点，就需要把 binlog 和事务关联起来，而只有保证了 binlog 和事务数据的一致性，才能保证主从数据的一致性。
所以 binlog 的写入过程不得不嵌入到纯粹的事务存储引擎执行过程中，并以内部分布式事务（xa 事务）的方式完成两阶段提交。
mysql事务隔离级别
ACID
隔离级别从弱到强 https://developer.ibm.com/zh/technologies/databases/articles/os-mysql-transaction-isolation-levels-and-locks/
读未提交 Read Uncommitted
该隔离级别的事务会读到其它未提交事务的数据，此现象也称之为 脏读 。
读已提交 Read Committed rc
一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果，此现象称为不可重复读问题，Oracle 和 SQL Server 的默认隔离级别。
可重复读 Read Committed rr
MySQL 默认的隔离级别 在同一个事务里， select 的结果是事务开始时时间点的状态，因此，同样的 select 操作读到的结果会是一致的，但是，会有 幻读 现象。
串行化 Serializable
在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。
mysql中的锁 InnoDB 实现了两种类型的行级锁： 共享锁 （也称为 S 锁）：允许事务读取一行数据。 可以使用 SQL 语句 select * from tableName where… lock in share mode; 手动加 S 锁。 独占锁 （也称为 X 锁）：允许事务删除或更新一行数据。 可以使用 SQL 语句 select * from tableName where… for update; 手动加 X 锁。 S 锁和 S 锁是 兼容 的，X 锁和其它锁都 不兼容
为了实现多粒度的锁机制，InnoDB 还有两种内部使用的 意向锁 ，由 InnoDB 自动添加，且都是表级别的锁。 意向共享锁 （IS）：事务即将给表中的各个行设置共享锁，事务给数据行加 S 锁前必须获得该表的 IS 锁。 意向排他锁 （IX）：事务即将给表中的各个行设置排他锁，事务给数据行加 X 锁前必须获得该表 IX 锁。 意向锁的主要目的是为了使得 行锁 和 表锁 共存。
innoDB 行锁的实现算法
mysql出现死锁怎么办？ InnoDB 引擎采取的是 wait-for graph 等待图的方法来自动检测死锁，如果发现死锁会自动回滚一个事务。
mysql中锁的优化方法？ 1，合理设计索引，让 InnoDB 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他 Query 的执行。 2，尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。 3，尽量控制事务的大小，减少锁定的资源量和锁定时间长度。 4，在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少 MySQL 因为实现事务隔离级别所带来的附加成本 。
https://tech.meituan.com/2014/08/20/innodb-lock.html mysql中锁的种类有很多，行锁、表锁、也有新加入的Metadata Lock 表锁是对一整张表加锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做ddl处理时使用。 行锁则是锁住数据行，这种加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，MySQL一般都是用行锁来处理并发事务。
rc：在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。 如果更新的字段没有索引，mysql会把整张表锁住，然后过滤不满足条件的记录，所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。
不可重复读和幻读的区别是什么？ 不可重复读重点在于update和delete，而幻读的重点在于insert。 在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据。 当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。 需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
像行锁、表锁这种都是悲观锁的概念，处于性能上的考虑，Mysql等采用以乐观锁理论为基础的MVCC（多版本并发控制）来解决上述问题
乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？ 即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。 此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。
MVCC在MySQL的InnoDB中的实现 在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。
在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。 对于这种读取历史数据的方式，我们叫它快照读 (snapshot read) 而读取数据库当前版本数据的方式，叫当前读 (current read)
为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。Next-Key锁是行锁和GAP（间隙锁）的合并 InnoDB使用的是聚集索引,维护一个索引字段和主键id的树状结构， 如果使用的是索引字段，update class_teacher set class_name=‘初三四班’ where teacher_id=30;不仅用行锁，锁住了相应的数据行；同时也在两边的区间，（5,30]和（30，positive infinity），都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。 受限于这种实现方式，Innodb很多时候会锁住不需要锁的区间。 如果使用的是没有索引的字段，比如update class_teacher set teacher_id=7 where class_name=‘初三八班（即使没有匹配到任何数据）’,那么会给全表加入gap锁。同时，它不能像上文中行锁一样经过MySQL Server过滤自动解除不满足条件的锁，因为没有索引，则这些字段也就没有排序，也就没有区间。除非该事务提交，否则其它事务无法插入任何数据。
行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。
serializable 这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。
这里要吐槽一句，不要看到select就说不会加锁了，在Serializable这个级别，还是会加锁的！
聚簇索引就是索引 key 与数据行在一起，而非聚簇索引的索引 key 对应的值是聚簇索引的值。 索引的数据结构 哈希索引 b+树
索引 知识点 https://juejin.cn/post/6879426606239055885
回表 对于普通索引（非聚集索引），索引数叶子节点存放的是主键值，当查询语句需要查询除主键 id 及索引列之外的其他字段时，需要根据主键 id 的值再回到主键索引树中进行查询，得到主键 id 对应的整个数据行 当数据量十分庞大时，回表是一个十分耗时的过程，所以我们应该尽量避免回表发生，这就引出了下一个问题：使用覆盖索引避免回表。
覆盖索引 当索引数包含的字段覆盖需要查询的字段时，不需要再回表，这就是覆盖索引
最左前缀原则 范围查询时，在全字段匹配且为范围查询的情况下，也能使用联合索引，但只能使用到联合索引中第一个出现范围查询条件的字段。 like 必须要求是左模糊匹配才能用到索引，因为字符类型字段的索引树也是有序的。 between 并不一定是范围查询，它相当于使用 in 多值精确匹配，所以 between 并不会因为是范围查询就让联合索引后面的索引列失效。
查询条件为函数或表达式
查询影响行数大于全表的25% 查询条件使用 <>(!=), not in, is not null in 查询条件中值数据类型不一致，MySQL 会将所有值转化为与索引列一致的数据类型，从而无法使用索引
索引下推 在 MySQL5.6 版本中对这样的情况进行优化，引入索引下推技术：在搜索索引树的过程中，就算没能用到联合索引的其他字段，也能优先对查询条件中包含且索引也包含的字段进行判断，减少回表次数，提高查询效率。 select * from demo where a > ‘1’ and b=’1’;
b树和b+树的区别 https://www.cnblogs.com/ivictor/p/5849061.html 区别有以下两点：
- B+树中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
- B+树中所有叶子节点都是通过指针连接在一起，而B树不会。
B+树的优点：
- 非叶子节点不会带上ROWID，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
- 叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。
B树的优点： 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。
mysql为什么默认使用rr隔离级别？ 有优化的方法吗？ https://www.cnblogs.com/rjzheng/p/10510174.html
实现mysql分库分表中间件？ https://www.cnblogs.com/xuwc/p/14054327.html https://www.jianshu.com/p/92a565a8eb37
mysql left join实现原理？ https://blog.csdn.net/cl2010abc/article/details/105253579
联合索引的使用场景是什么？ 能形成索引覆盖,提高where语句的查询效率
Leave a comment