mysql慢查询怎么优化

  1. 检查是否走了索引，如果没有，优化 SQL 利用索引
  2. 检查 利用的索引是否是最优索引，没有的话，尽量用最优索引
  3. 检查所查字段是否是必须的，是否有多余数据【不要查多余数据】
  4. 表中数据是否过多，是否分库分表
  5. 检查数据库实例所在的机器性能配置，是否太低，是否应该适当增加资源

https://www.bilibili.com/video/BV1zL4y1e7YN?p=2&spm_id_from=pageDriver

索引的本质

索引是帮助 MYSQL 获取数据的排好序的数据结果【内部已经是有序了，最左前缀匹配原则 就是 索引分布的顺序】

聚集索引和非聚集索引

聚集索引是 数据放在 索引的叶子节点， 非聚集索引 是 索引和数据分离，索引有指针 指向那个数据

作者：路人zhang 链接：https://www.nowcoder.com/discuss/744436?channel=-1&source_id=profile_follow_post_nctrack 来源：牛客网

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

  • 聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。
  • 非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址。

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。

可以从非常经典的两张图看看它们的区别(图片来源于网络)：

https://uploadfiles.nowcoder.com/images/20210915/115285789_1631663662791/E8B5126C6C720C4643F2543CB83781CB

https://uploadfiles.nowcoder.com/images/20210915/115285789_1631663675751/70BCE6DF348B8AAF62246C6079F56E09

重点

一个表只能有一个聚集索引 ， 而非聚集索引可以有很多个，

InnoDB 会创建一个因此 的 row-id 作为聚集索引 【主键】

如果表定义了 pk, pk 就是聚集索引，否则用隐藏的 row-id 作为索引

myisam 使用的是非聚集索引，数的叶子节点上 的 data 不是数据本身， 而是数据存放的地址。 InnoDB 使用的是聚集索引，叶子节点上的data就是数据本身。

覆盖索引和回表：

  1. 如果只需要在一颗索引树上获取 SQL 所需要的所有列，就不需要再回表查询， 这样查询速度就可以更快。
  2. 实现索引覆盖的最简单简单的方式就是要查询的字段，全部建立到联合索引当中。
    1. user(PK id, name sex)
    2. select count (name) from user : -> name 字段简历一个索引
    3. select id ,name,sex from user -> name 索引升级为 (name,sex )的联合索引。
    4. 索引页不是越多越好，因为数据更新还要去修改索引，索引越多，更新越慢。

回表和覆盖索引

覆盖索引意思是 ，查询的字段是索引的一部分，并且 sql 查询的时候 是利用的这个索引，那么 可以将数据之间从索引中取出【速度快】， 回表的话就是 有一部分数据是索引中没有存的，那么就要 利用这个索引 去定位到对应的数据表中，

回表重点

重点 必背

  1. 普通索引和主键索引的区别【 主键聚餐索引，一次查询， 普通索引存的是主键索引， 用普通索引就会有个回表
  2. 覆盖索引， 如果查询的数据都是索引的数列，并且使用了这个索引，可以直接从索引中读取，就一次查询，就不需要回表操作了。

Innodb 如何实现事务

Innodb 通过 Buffer Pool ,LogBuffer , redo log ，undo log 实现事务，以一个 update语句为例

  1. innodb在收到一个 update 语句后，会根据条件找到数据所在的也，并将该也缓存在 buffer pool 中

  2. 执行 update 语句，修改 buffer pool 也就是内存中的数据

  3. 针对 update 语句 生成一个 redo log 对象，并存入 logBuffer 中

  4. 针对update 语句生成 undolog 日志，用于事务回滚

  5. 如果事务提交，那么则把 redolog 对象进行持久化，后序还有其他机制将 buffer pool 中所修改的数据也持久化到磁盘中

    1. 后续会有个 purge 线程 将 buffer pool 的脏页持久化到磁盘中。

  6. 如果事务回滚，则用 undolog 日志进行回滚。

索引的种类

  1. b+ 树索引
  2. 哈希索引：
    1. 原理：把不固定的输入转为固定的输出。

两种索引的区别： b树索引可以模糊查询， 哈希索引只能等值查询。

mysql集群搭建

  1. 主从节点
  2. 主节点 将 binlog 推送给 slave 节点，slave io线程 通过生成 中继日志 relay log ， 由一个线程将 relay log 同步数据。

mysql 只从 主节点 发送给从节点，由此保证数据的读写分离。

慢sql优化 会考虑哪些

作者：路人zhang 链接：https://www.nowcoder.com/discuss/744436?channel=-1&source_id=profile_follow_post_nctrack 来源：牛客网

慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。

相关参数：

  • slow_query_log：是否开启慢日志查询，1表示开启，0表示关闭。
  • slow_query_log_file：MySQL数据库慢查询日志存储路径。
  • long_query_time：慢查询阈值，当SQL语句查询时间大于阈值，会被记录在日志上。
  • log_queries_not_using_indexes：未使用索引的查询会被记录到慢查询日志中。
  • log_output：日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。

如何对慢查询进行优化？

  • 分析语句的执行计划，查看SQL语句的索引是否命中
  • 优化数据库的结构，将字段很多的表分解成多个表，或者考虑建立中间表。
  • 优化LIMIT分页。

B+ 树和 B树区别

  1. B树特点：
    1. 节点排序
    2. 一个节点可以存多个元素，多个元素也是排序的。
      • 这里注意问：最左前缀匹配原则 怎么理解【被问过2次】
  2. B+ 树的特点：
    1. 拥有B树的特点
    2. 叶子节点之间有指针 【数据主要存放在叶子节点】
    3. 非叶子节点中的元素是冗余数据， 也就是叶子节点上存储了所有的元素，并且是排好序的

Mysql 索引使用的是 B+ 树， 因为索引是用来加快查询的， 而B+树通过对数据 进行排序所以是可以提高查询速度， 然后通过节点中可以存储多个元素，从而可以使得B+树的高度不会太高， 在 Mysql 中一个 Innodb 页就是 一个 B+树节点， 一个 innodb页面默认 是 16KB, 所以一般情况下 一颗两层的 B+树可以存2000万行左右的数据，然后通过B+ 树叶子节点中存储了所有的数据并且进行了排序，并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等SQL语句。

一般用explain 解析 sql

然后 根据 type 字段的反馈 来优化 sql

反馈字段：

  1. type: sql优化的重要手段： 判断sql性能和优化程度的重要指标。
    • const： 通过索引一次命中， 匹配一行数据
    • System： 表中 只有一行记录，相当于系统表。
    • eq_ref : 唯一索引，只有一条记录和它匹配
    • ref: 非唯一索引扫码，返回匹配某个值的所有。
    • range: 范围索引，使用一个索引来匹配行 ，一般用于 between ， < , >
    • index; 只遍历索引树 【遍历了整个索引，范围算是及其低了 O(N) 】
      • 注意： 这种和 ALL 相比也是另一种形式的全表扫描，只不过它是根据索引扫描表，然后回表取数据， 和 all 相比，都是取得全表的数据
      • 官方说 它比 ALL 快的原因可能是 **按照索引扫描全表的数据是有序的。**这样一来，结果不同，也就没法比效率的问题了。
    • ALL : 全表扫描，这个类型查询性能最差
  2. 一般来说，得保证查询至少达到range级别，最好能达到ref。

Extra

如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。

如果是where used，就是使用上了where限制。

如果是impossible where 表示用不着where，一般就是没查出来啥。

如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

锁

间隙锁【 MVCC RR ， 解决幻读原理】

mysql 的 mvcc RR 解决了幻读，默认开启了间隙锁解决了这个问题

行锁（Record Lock）：锁直接加在索引记录上面。 间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。 Next-Key Lock：行锁与间隙锁组合起来用就叫做Next-Key Lock。

什么是间隙锁？

https://img2020.cnblogs.com/blog/1885839/202101/1885839-20210128112714117-529285989.png

正常等值条件 并且值存在的情况下加的是行锁

如果等值条件 值不存在的情况下加的是间隙锁，或者范围查询，加的也是间隙锁

举个例子：

根据主键id，不只是有五个行锁，还会有六个间隙锁，左开右闭原则，（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+supernum]

例如 select * from table where id = 10 for update; 等值条件，id是存在的，加行锁就可以了

select * from table where id = 7 for update; 等值条件，id不存在，加（5，10] 间隙锁，这范围间不允许插入数据，直到这个事务提交完成释放锁

select * from table where id > 24; 范围条件，加间隙锁

通过行锁+间隙锁的机制保证了事务A select之后，其他事务相应的insert操作会阻塞

什么是undolog?

undolog存放不同事务版本下的不同数据，

用于 1.历史恢复 通过undolog恢复之前版本的数据 2. 读老版本 根据条件读旧版本的数据

每次数据变更都会产生undolog记录，undolog记录分为 insert undo_log 和 update undo_log

insert操作属于insert undo_log，只针对当前事务，在insert操作后产生undo_log记录，在事务提交后删除undo_log记录，说白了就是给当前事务自己看的.

update 和 delete操作属于update undo_log，会根据隔离级别不同事务版本的数据可见性不同

什么是readView?

快照 存放了当前活跃的一些事务版本号，以及上一个版本的地址. 用来做可见性判断

readview根据生成时间不同，产生了RC,RR两种可见性

RC：每条select创建一个新的readview ，所以导致读提交 读到的都是最新提交的！

RR：事务开始的时候创建一个readview, 一直到事务结束都用的这个readview，也就避免了不可重复读

当前读与快照读

单条普通的select语句属于快照读

select for update , insert, update, delete 属于当前读

快照读由mvcc+undolog实现

当前读由行锁+间隙锁实现

缓存篇

缓存的常见问题

作者：路人zhang 链接：https://www.nowcoder.com/discuss/744436?channel=-1&source_id=profile_follow_post_nctrack 来源：牛客网

缓存穿透：指缓存和数据库中都没有的数据，所有请求都打在数据库上，造成数据库短时间承受大量请求而挂掉

解决方法：

  • 增加接口校验，过滤一些不合法请求，比如大量订单号为-1的数据
  • 从缓存和数据库都不能获取到的数据，可以先对空的结果进行缓存，比如key-null，缓存有效期要设置的短一些
  • 采用布隆过滤器，过滤掉一定不存在的数据

缓存击穿：指缓存中没有但数据库中有的数据，一般是在高并发的情况下，某些热门key突然过期，导致所有请求直接打到数据库上

解决方法：：

  • 设置热点数据永不过期
  • 加互斥锁

缓存雪崩：大量缓存在一段时间内集中过期，导致查询的数据都打在数据库上，和缓存击穿的区别是缓存过期的数量

解决方法：

  • 将缓存的过期时间设置随机，避免大量缓存同时过期
  • 服务降级或熔断

其他笔记

[[post/02.数据库/01.MySQL/4.事务和锁|事务和锁]]