Mysql

慢查询如何优化？（实战慢查询）

1、若未开启慢查询，用“set global slow_query_log='ON';”开启慢；2、用“set global slow_query_log_file=路径”设置慢查询文件保存位置；3、用“subl 路径”查询文件即可。

show variables like 'slow_query_log';
show variableslike 'long_query_time';

SELECT * FROM `slow_log` where start_time > '2019/05/19 00:00:00';

首先在配置文件开启慢查询日志

然后使用explain进行查询语句的分析

1 如果没索引，加索引；有索引看是否失效？函数使用，最左原则，模糊查询等

2 分页查询，越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

此在进行分页查询的时候，我们会直接通过叶子节点的双向链表进行查询，问题就出在了这里，由于页码过大，而且查询字段过多，每次查询时候需要回表，所以链表在遍历的时候时间过长，造成了性能瓶颈。这个主要出现在了需要回表

应为查询的时候需要先查询出前面的所有记录，然后舍弃前面所有的，拿到后面的页。

所以最好的解决办法是使用覆盖索引，先查询出该页的所有主键，然后根据主键查询。

3 拆分关联查询

4 分库分表

5 ES中间件查询

索引下推

索引知识系列二：联合索引、索引覆盖和索引下推详解

索引

索引总结.drawio

行锁

MySQL 表锁和行锁机制分析

在执行DML操作时，会对涉及的行加锁，同时也会对该表加上意向锁。（意向共享锁：与表共享兼容，表拍他互斥。意向排它锁：都互斥。但是意向拍他之间，不会互斥，行锁可以保证的。）

后面对这张表加表锁的时候，会根据该表上的意向锁来判定是否可以加锁成功，而不用逐行在判断行锁。

总结：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

当你需要更新一张较大表的大部分甚至全表的数据时。而你又傻乎乎地用索引作为检索条件。一不小心开启了行锁(没毛病啊！保证数据的一致性！)。可MySQL却认为大量对一张表使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突问题，性能严重下降。所以MySQL会将行锁升级为表锁，即实际上并没有使用索引。

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：
共享锁：select * from tableName where … + lock in share more
排他锁：select * from tableName where … + for update
InnoDB和MyISAM的最大不同点有两个：一，InnoDB支持事务(transaction)；二，默认采用行级锁。加锁可以保证事务的一致性，可谓是有人(锁)的地方，就有江湖(事务)；我们先简单了解一下事务知识。

行锁：

间隙锁：防止其他事务在间隙insert，防止了了RR隔离级别的幻读。

临间锁：上俩综合。

程序员经典面试题，Mysql并发读写的时候，都是需要加锁的么？

在Mysql中有行锁，如果有多个事务同时修改同一行数据，那么需要加锁来防止并发问题。那么，如果有事务修改数据，又有事务读取这个数据，需要加锁么？

答案是不一定，事实上，Mysql的很多数据库引擎为了提升并发性能，都做了多版本并发控制，也就是我们常说的MVCC。

在Mysql的每一行数据中，除了我们定义的数据列，还有2个隐藏的列，一个是数据的变更时间，一个是这行数据的删除时间，当然，这个时间并不是简单的时间戳，而是一个严格递增的系统版本号。

当InnoDB发生Insert事件的时候，会插入当前行并且以取得的系统版本号作为数据版本号。

当InnoDB发生Delete时间的时候，不会删除当前行，而是把对应的行如果未删除，那么打上删除标记位为当前的版本号。

当InnoDB发生Select操作的时候，会取当前的系统版本号，然后到数据库中进行查询，他只会查询比自己当前版本号更小的，并且没有删除版本号或者删除版本号比当前版本号更小的数据。

当InnoDB发生Update事件的时候，不是直接更新旧的数据，而是插入一条新的数据，并且把版本号小于这条记录的并且没有被打上删除标记的同一主键的记录更新打上删除标记，删除版本号为当前的版本号。

很显然，在这样的一种数据中，同一行数据其实在数据库中是多行的存在。这本质上是一种空间换时间的方案，在多版本控制中，我们几乎可以做到所有的读操作都是无阻塞的，可以避免加锁，这与互联网业务中，多读少写是非常契合的。当然了，在Mysql的InnoDB引擎中，只有事务级别为可重复读跟读提交才可以使用。这是为什么呢？欢迎大家关注我，共同学习，共同进步。大家的支持是我继续唠嗑的动力。

mysql是单线程还是多线程？

mysql是多线程的。mysql是一个单进程多线程的数据库，在innodb中大概3种线程为：1、主线程Master Thread；2、IO Thread线程，用于异步处理写请求；3、purge Thread线程，用于删除undo日志。

主从复制的时候，从节点将从主节点read到的二进制日志通过IOThread写入到中继日志。

HAVING和WHERE的异同点

执行时机不同，一个在分组前，不满足where不参与分组，having对分组之后的结果进行过滤。

判断条件不同，where不能对聚合函数进行判断。

顺序：where > 聚合函数 > having。

语法上:where 用表中列名，having 用 select 结果别名。

影响结果范围:where 从表读出数据的行数，having 返回客户端的行数。

where 可以使用索引，having 不能使用索引，只能在临时结果集操作。

where后面不能使用聚集函数，having是专门使用聚集函数的。

SQL10 获取所有非manager的员工emp_no

MySQL官方文档有说明，in关键字适合确定数量的情况，一般效率较低，不推荐使用。能用in关键字的语句都可以转化为使用join的语句，推荐使用join关键字。

事务隔离级别(图文详解)

MYSQL的隔离级别是基于锁和MVCC共同实现的。

MySQL三大日志(binlog、redo log和undo log)详解

binlog、redolog、undolog

ifpRJK5vzec4jh6

redolog用于主机的重启恢复，会有一个二阶段提交过程（将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交），先会有redolog的准备节点，在binlog写入，再redolog的commit

binlog主要用于备份

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

ThdnPEwA2Su7vki

InnoDB存储引擎对MVCC的实现

在 InnoDB 存储引擎中，多版本控制 (multi versioning) 就是对非锁定读的实现。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)

所以说，insert是在提交事务后，undolog就能删除的，但是delete和update是需要mvcc的。

MVCC 的实现依赖于：隐藏字段、Read View、undo log。

MySQL数据库索引总结

索引

用B+，而不是B-（深度深），而不是HashMap（哈希冲突，顺序和范围查找）

而不是平衡二叉树（创建删除增加更麻烦，深度太深）。而不是普通二叉树（深度太深），而不是红黑树（深度太深，末端也没有双向链表）。

磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度。

dvIjm4FeM7VJXpG

锁的种类

MySQL记录锁、间隙锁、临键锁（Next-Key Locks）详解

XRK1gp

sql语句的执行流程

1、在打开客户端后，最初需要和sql服务器建立连接，账号认证和校验权限。

2、认证后，客户端发生查询sql脚本给服务器

3、服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

4、服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

5、MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。

6、将结果返回给客户端。

分库分表出现的问题

数据的的自增主键没办法满足生成的主键唯一了，所以需要用分布式ID。

列举几种数据倾斜的情况，并解释为什么会倾斜，以及如何解决？

PHP如何在两个大文件中找出相同的记录?

处理该问题的困难主要是无法将这海量数据一次性读内内存中.

一次性读不进内存中，那么是否可以考虑多次呢？如果可以，那么多次读入要怎么计算相同的值呢？

我们可以用分治思想，大而化小。相同字符串的值hash过后是相等的, 那么我们可以考虑使用hash取模, 将记录分散到n个文件中。这个n怎么取呢？ PHP 100M内存，数组大约可以存100w的数据, 那么按a,b记录都只有10亿行来算, n至少要大于200。

此时有200个文件，相同的记录肯定在同一个文件中，并且每个文件都可以全部读进内存。那么可以依次找出这200个文件中各自相同的记录，然后输出到同一个文件中，得到的最终结果就是a, b两个文件中相同的记录。

找一个小文件中相同的记录很简单了吧，将每行记录作为hash表的key, 统计key的出现次数>=2就可以了。

MySQL 不同隔离级别，都使用了什么锁？

MySQL串行化隔离级别（间隙锁实现）

读未提交

在「读未提交」隔离级别下，读写操作可以同时进行，但写写操作无法同时进行。与此同时，该隔离级别下只会使用行级别的记录锁，并不会用间隙锁。

读已提交

还是只有一个行级别的记录锁，并没有间隙锁。

其实他们的最大区别，就是「读已提交」解决了脏读的问题。

可重复读

当前读，记录锁+间隙锁

快照读：MVCC

next-key锁就是记录所和间隙锁的结合体

写：写一条的时候记录锁，写范围的时候 next-key锁。

可重复读级别下条件列有索引时会添加间隙锁，阻止在此间隙内的insert操作，防止出现幻读。

InnoDB引擎在执行查询语句SELECT时，不会加锁。但是update、insert、delete操作会加行锁限制其他事物更新

SQL语句在Mysql中的执行过程

总结

MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
查询语句的执行流程如下：权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎
更新语句执行流程如下：分析器—->权限校验—->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

图解MySQL：count(*) 、count(1) 、count(主键字段)、count(字段）哪个性能最好？

count(*) = count(1) ＞ count(主键字段）＞count(字段）

MySQL事务的保证机制

原子性是有undoLog来保证

持久性是由redolog来保证

隔离性是有锁机制和MVCC机制保证

一致性是有以上三个机制保证的。

SQL160 国庆期间每类视频点赞量和转发量

# select tag,dt,
#      sum(like_cnt) over(partition by tag order by t3.dt rows 6 preceding) total_like,
#      max(retweet_cnt) over(partition by tag order by t3.dt rows 6 preceding) max_retweet
# from (
#     select tag,
#     date(start_time) dt, 
#     sum(if_like) like_cnt,
#     sum(if_retweet) retweet_cnt
#     from tb_user_video_log t1 
#     inner join tb_video_info t2 
#     on t1.video_id=t2.video_id
#     where date(start_time) between '2021-09-25' and '2021-10-03'
#     group by tag,date(start_time)
#     ) t3

select * from (
select tag,
dt,

sum(like_cnt) 
over 
(partition by tag order by t1.dt rows between 6 preceding and current row) sum_like_cnt_7d,

max(retweet_cnt)
over
(partition by tag order by t1.dt rows between 6 preceding and current row) max_retweet_cnt_7d

from (
    select tag, 
    date(start_time) dt,
    sum(if_like) like_cnt, 
    sum(if_retweet) retweet_cnt
    from tb_user_video_log a
    join tb_video_info b
    on a.video_id = b.video_id
    # where date(start_time) between '2021-09-25' and '2021-10-03'
    group by tag, date(start_time)
) t1 ) t2

where dt between "2021-10-01" and "2021-10-03"
order by tag desc,
sum_like_cnt_7d asc;



# group by和窗口函数不能一起用吧
# 像这种需要1～3号的累加，但是累加需要1～3前面几号的，用子查询，先窗口查好，然后在外面用where,
# 而且，直接在外层where还不行，要再套一层注意上面的select*
# 注意 group by用了之后，select选择的字段要么是在groupby里面的，要是是用了聚合函数的

DISTINCT不能和order by一起使用的问题

select查询中如果没有需要order的，那么会出错，使用子查询；也可以使用group by 子句代替distinct解决(不一定对)。

# 利用子查询，但需要注意null的处理，最好使用NOT IN剔除自己喜欢的音乐，而不是<>。当自己没有喜欢的音乐时候，使用不等于进行剔除就可能出现问题。
# distinct不能写在外侧，就写在里面
select music_name
from music
where id in (
    select distinct music_id
    from music_likes
    where user_id in (
        select follower_id
        from follow
        where user_id = 1
    )
)
and id not in (
    select music_id
    from music_likes
    where user_id = 1
)
order by id

1
2
3

关系数据库     ⇒ 数据库 ⇒ 表    ⇒ 行    ⇒ 列(Columns)

Elasticsearch  ⇒ 索引(Index)   ⇒ 类型(type)  ⇒ 文档(Docments)  ⇒ 字段(Fields)

Author: Jcwang

Permalink: http://example.com/2022/07/16/%E5%85%AB%E8%82%A1mysql/

updated at 2022-10-15

# mysql

八股Mysql