数据库（总结自小林coding）|索引失效的场景、慢查询、原因及如何优化？undo log、redo log、binlog 作用、MySQL和Redis的区别

说一下索引失效的场景？

索引失效意味着查询操作不能利用索引进行数据检索，而是使用全表扫描，从而导致性能下降，下面一些场景会发生索引失效

• 对索引使用左或者左右模糊匹配

  • 因为索引 B+ 树是按照索引值 有序排列存储的，只能根据前缀进行比较。

  • 如果使用 name like ‘%林’ 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询

• 对索引使用函数

  • 因为索引 保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了
  • 不过，从 MySQL 8.0 开始，索引特性增加了 函数索引，也就是可以针对 函数计算后的值建立一个索引，该索引的值是函数计算后的值，所以 就可以通过扫描索引来查询数据

• 对索引进行表达式计算

  • 因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引

• 对索引隐式类型转换

  • 对索引的隐式类型转换或者叫自动类型转换，效果和对索引使用函数类似，而这样会导致索引失效
  • 比如索引原来是字符串类型，而我们输入一个整形，这样的后果就是 索引会执行自动类型转换，也就是等效于对索引使用函数，使索引失效。如果索引使整形，而输入字符串，只会对输入的字符串进行自动类型转换，对索引本身不会任何改变，所有这样不会导致索引失效

• 联合索引不满足最左匹配

  • 对 主键字段 建立的索引叫 聚簇索引，对 普通字段 建立的索引叫 二级索引。那么 多个普通字段 组合在一起创建的索引就叫做 联合索引
  • 最左匹配原则，也就是按照 最左优先的方式进行索引的匹配。比如创建了一个 (a, b, c) 联合索引，在where条件中必须要带有 a字段的值
  • 在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序

• WHERE 子句中的 OR

  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列只是普通列，而不是索引列，那么索引会失效
  • 因为 OR 的含义就是 两个只要满足一个即可，因此 只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描

什么是慢查询？原因是什么？可以怎么优化？

数据库查询的执行时间超过指定的超时时间时，就被称为 慢查询。

原因：

• 查询语句比较复杂：查询涉及多个表，包含复杂的连接和子查询，可能导致执行时间较长。
• 查询数据量大：当查询的数据量庞大时，即使查询本身并不复杂，也可能导致较长的执行时间。
• 缺少索引或索引失效：如果查询的表没有合适的索引，需要遍历整张表才能找到结果，查询速度较慢。
• 数据库表设计不合理：数据库表设计庞大，查询时可能需要较多时间。
• 并发冲突：当多个查询同时访问相同的资源时，可能发生并发冲突，导致查询变慢。
• 硬件资源不足：如果MySQL服务器上同时运行了太多的查询，会导致服务器负载过高，从而导致查询变慢

优化：

1. 分析查询语句：
   • 使用 EXPLAIN命令分析 SQL执行计划，找出慢查询的原因，比如是否使用了全表扫描，是否存在索引未被利用的情况等，并根据相应情况对索引进行适当修改。
2. 查询优化：
   • **避免使用SELECT ***，只查询真正需要的列；
   • 使用 覆盖索引，即索引包含所有查询的字段；
   • 联表查询最好要以小表驱动大表，并且被驱动表的字段要有索引。最好通过冗余字段的设计，避免 联表查询。
3. 创建或优化索引：
   • 根据 不同查询条件创建合适的索引，特别是经常用于 WHERE子句的字段、orderby 排序的字段、Join 连表查询的字典、 group by的字段
   • 如果查询中经常涉及多个字段，考虑创建 联合索引，使用联合索引要符合最左匹配原则，不然会索引失效
   • 不要用左模糊匹配、函数计算、表达式计算等等，防止索引失效
4. 分页优化：
   • 针对 limit n,y 深分页的查询优化，可以把Limit查询转换成某个位置的查询：select * from tb_sku where id>20000 limit 10，该方案适用于主键自增的表
5. 优化数据库表：
   • 如果单表的数据超过了千万级别，最好将大表拆分为小表，减轻单个表的查询压力。
   • 也可以将字段多的表 分解成多个表，有些字段使用频率高，有些低，数据量大时，使用频率低的字段会导致 变慢，可以考虑将两者分开
6. 使用缓存技术：
   • 引入缓存层，如Redis，存储热点数据和频繁查询的结果，但是要考虑缓存一致性的问题，对于读请求会选择旁路缓存策略，对于写请求会选择先更新 db，再删除缓存的策略

undo log、redo log、binlog 有什么用

• undo log：
  • 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
  • 实现 **MVCC（多版本并发控制）**关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行select 语句的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。
• redo log：
  • redo log 是 物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条 物理日志
  • 相比于undo log，redo log 记录了此次事务**「修改后」的数据状态，记录的是更新之后的值**，主要用于事务崩溃恢复，保证事务的持久性
  • 将 写入磁盘的操作 从**「随机写」变成了「顺序写」**，提升 MySQL 写入磁盘的性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上
• binlog ：
  • 与刚才两个日志不同，它是Server 层生成的日志，记录了所有 数据库表结构 变更和 表数据 修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作，主要用于数据备份和主从复制

MySQL和Redis的区别是什么

• mysql是关系型数据库，使用 表 来组织数据。主要用于存放持久化数据，将数据存储在硬盘中，读取速度较慢；而redis是非关系型数据库，也是缓存数据库，即将数据存储在缓存中，缓存的读取速度快，能够大大的提高运行效率，但是保存时间有限
• MySQL 基于磁盘，读写速度没有Redis快，但是不受空间容量限制，性价比高；Redis基于内存，读写速度快，也可做持久化，但是内存空间有限，当数据量超过内存空间时，需扩充内存，而内存成本较高；
• Redis不使用SQL，而是使用自己的命令集，MySQL使用SQL来进行数据查询和操作。
• Redis以 高性能和低延迟为目标，适用于读多写少的应用场景，适合处理高速、高并发的数据访问，以及需要复杂数据结构和功能的场景，而MySQL 适用于需要支持 复杂查询、事务处理、拥有大规模数据集 的场景。在实际应用中，很多系统会同时使用 MySQL 和 Redis