算不上最全，但都是必备——MySQL这些不会不行啊_update 的where字段没有索引就会进去表锁码

MySQL篇

update 没加索引会锁全表？

说个前提，接下来说的案例都是基于 InnoDB 存储引擎，且事务的隔离级别是可重复读。

假设有两个事务的执行顺序如下：

可以看到，事务 A 的 update 语句中 where 是等值查询，并且 id 是唯一索引，所以只会对 id = 1 这条记录加锁，因此，事务 B 的更新操作并不会阻塞。

但是，在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

因此，当在数据量非常大的数据库表执行 update 语句时，如果没有使用索引，就会给全表的加上 next-key 锁， 那么锁就会持续很长一段时间，直到事务结束，而这期间除了 select ... from语句，其他语句都会被锁住不能执行，业务会因此停滞，接下来等着你的，就是老板的挨骂。

update 语句的 where 带上索引就能避免全表记录加锁了吗？

并不是。

关键还得看这条语句在执行过程种，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

• 我们可以打开 MySQL sql_safe_updates 参数，这样可以预防 update 操作时 where 条件没有带上索引列。
• 如果发现即使在 where 条件中带上了列索引列，优化器走的还是全标扫描，这时我们就要使用 force index([index_name]) 可以告诉优化器使用哪个索引

---

索引下推

索引下推 (lndex Condition ushdown，简称ICP)是MySQL 5.6 版本引入的-种索引优化技术，它可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

MySQL架构

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

• 存储引擎读取索引记录；
• 根据索引中的主键值，定位并读取完整的行记录；
• 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

• 存储引擎读取索引记录（不是完整的行记录）；
• 判断条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
• 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
• 存储引擎把记录交给层，层检测该记录是否满足条件的其余部分。

联合索引：【name + age + position】
SELECT 
	*
FROM
	employees
WHERE
	name like 'wei66%' 
AND 
	age =22 
AND 
	position ='manager

对于联合索引(name,age,position)，正常情况按照最左前缀原则，以上SQL语句只会走name字段索引。因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

• 在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是wei66开头的索引，然后拿这些索引对应的主键逐个回 表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。
• 但是在MVSQL5.6及之后的版本使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是wei66开头的索引之后，同时还会在索引里过滤age和position这两个字段，如下图，明显是使用到了索引的这3个字段。最后拿着过滤完剩下的索引对应的主键id再回表查整行数据。

当一条SQL 使用了索引下推技术后，在explain 执行计划中，Etra 列中出现 Using index condition 的信息。

• 可以参考下这篇文章：五分钟搞懂MySQL索引下推 (baidu.com)

---

从磁盘IO的角度聊聊 B 树和 B+ 树

Mysql 中采用 B+树来做索引结构，在介绍B树和B+树前，我们先来了解前置知识

磁盘 IO 工作原理

首先系统会把数据逻辑地址传给磁盘，磁盘控制电路按照寻址逻辑把逻辑地址翻译成物理地址，也就是确定要读取的数据在哪个磁道，哪个扇区。

为了读取这个扇区的数据，需要把磁头放在这个扇区的上面，为了实现这一个点，磁盘会不断旋转，把目标扇区旋转到磁头下面，使得磁头找到对应的磁道，这里涉及到寻道事件以及旋转时间

很明显，磁盘IO这个过程的性能开销是非常大的，特别是查询的数据量比较多的情况下。

所以在InnoDB中，干脆对存储在磁盘块上的数据建立一个索引，然后把索引数据以及索引列对应的磁盘地址，以B+树的方式来存储。当我们需要查询目标数据的时候，根据索引从 B+树中查找目标数据即可

树的高度影响磁盘IO数

在一个多级存储结构中，数据通常存储在磁盘上，而计算机的操作是在内存中进行的。当我们需要访问存储在磁盘上的数据时，计算机必须先将所需数据从磁盘读取到内存中，然后才能进行进一步的操作。

树是一种常用的数据结构，我们可以使用树来组织存储在磁盘上的数据。树的高度表示从根节点到叶子节点的层数。当树的高度较高时，从根节点到叶子节点的路径就会变长，需要经过更多的中间节点。

当计算机查找存储在磁盘上的数据时，它需要按照树的结构从根节点开始，逐级地在各个节点中查找，直到找到目标数据所在的叶子节点。每一次从磁盘读取一个节点的数据，都需要进行一次磁盘 IO 操作，即将数据块从磁盘读取到内存中。

因此，当树的高度较高时，也就意味着从根节点到叶子节点的路径会更长，需要进行更多次的磁盘 IO 操作。相反，当树的高度较低时，路径较短，需要进行的磁盘 IO 操作次数相对较少。

所以，我们通常希望树的高度尽可能地低，以减少计算机进行磁盘 IO 操作的次数，从而提高数据的读取速度和效率。

B树的优势

二叉查找树，在二叉树的基础上增加了一个规则，左子树的所有节点的值都小于它的根节点，右子树的所有子节点都大于它的根节点。，二叉查找树会出现 斜树 问题，导致时间复杂度增加，因此又引入了一种平衡二叉树，它具有二叉查找树的所有特点，同时增加了一个规则：”它的左右两个子树的高度差的绝对值不超过 1“。平衡二叉树会采用左旋、右旋的方式来实现平衡。

B 树是一种多路平衡查找树，它满足平衡二叉树的规则，但是它可以有多个子树，子树的数量取决于关键字的数量（关键字数+1），因此从这个特征来看，在存储同样数据量的情况下，B 树的高度要小于平衡二叉树，磁盘IO的次数更少，数据的读取速度和效率更高

B+树

B+树，其实是在 B 树的基础上做的增强，二者最大的区别有两个：

• B 树的数据存储在每个节点上，而 B+树中的数据是存储在叶子节点，并且通过双向有序链表把叶子节点中的数据进行连接
• B+树的子路数量等于关键字数

---

为什么MySQL要用B+树而不用B树呢？

• B+树非叶子节点不存储数据，所以每一层能够存储的索引数量会增加，意味着 B+树在层高相同的情况下存储的数据量要比 B 树要多，使得磁盘 IO 次数更少。
• 在 Mysql 里面，范围查询是一个比较常用的操作，而 B+树的所有存储在叶子节点的数据使用了双向链表来关联，所以在查询的时候只需查两个节点进行遍历就行，而 B 树需要获取所有节点，所以 B+树在范围查询上效率更高。
• 在数据检索方面，由于所有的数据都存储在叶子节点，所以 B+树的 IO 次数会更加稳定一些。
• 因为叶子节点存储所有数据，所以 B+树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但是 B 树需要遍历整个树。
• 基于 B+树维护的有序链表结构，如果采用自增的整型数据作为主键，还能更好的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。

---

为什么MySQL要用B+树而不用跳表呢?

数据分部的更均匀。因为B+树比跳表的检索效率更高，

跳表是通过 二路分治 的方式实现logN。

B+树是通过 多路分治 的方式实现logN。

• 当数据表的数据足够多的时候，B+树的根节点任何一块叶子节点的路径是固定的。而跳表的头节点目标节点的路径是不固定的。所以检索的value越大，跳表的路径就越深，磁盘的io次数就越多
• B+树的所有叶子节点构成了一个双向循环链表，每一块叶子节点可以存储一条或者多条数据。这种结构不管是一条记录、还是多条记录查询都能节省磁盘IO
• 跳表的每一个节点只存储一条记录，对于一条记录的查询是比较节省磁盘io，对于多条记录的查询，跳表的磁盘10次数会比B+树要多。

---

InnoDB存储引擎 + 事务底层原理 + MVCC原理

这一块好多东西要背，专门整理出一篇文章了

---

简述查询语句执行流程

举个例子，查询语句如下：

select * from user where id > 1 and name = 'wzx';

1. 首先检查权限，没有权限则返回错误；
2. MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
3. 词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
4. 两种执行方案，先查 id > 1 还是 name = 'wzx'，优化器根据自己的优化算法选择执行效率最好的方案；
5. 校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

简述更新语句执行过程

举个例子，更新语句如下：

update user set name = 'wzx' where id = 1;

1. 先查询到 id 为1的记录，有缓存会使用缓存。
2. 拿到查询结果，将 name 更新为wzx，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中 （并不会直接更新外存，而是缓存在InnoDB的 Change Buffe中） ，同时记录redo log （确保持久性） ，此时redo log进入 prepare状态。
3. 执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为commit状态。
4. 更新完成。

为什么记录完redo log，不直接提交，而是先进入prepare状态？

假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

---

数据库 CPU 飙升怎么处理？

定位问题

• 使用 top 命令，找到 cpu 占用过高的进程是否是 mysqld
• 如果是，可以在 mysql 中通过 show processlist 查看当前的会话情况，确定是否有消耗资源的 SQL 正在运行
• 通过慢查询日志，找到消耗过高的 SQL，通过执行计划 explain 进行具体的分析

处理问题

• QPS过高： 找到对应的接口或SQL，针对性的限流或降级。
• SQL写的腊鸡：看下面的SQL优化方案
• 优化索引结构：尽量采用联合索引触发覆盖索引避免回表
• 机器拉跨：加钱升级 / 搭建集群，读写分离提高并发度
• 存储数据量过大：分库分表，水平扩容

定位慢查询

慢查询：页面加载过慢、接口压测响应时间过长（超过1s）的SQL

第三方开源工具

• 调试工具：Arthas
• 运维工具：Prometheus 、Skywalking

MySQL自带慢日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志，默认未开启。long_query_time 默认为10 秒，最小为 0， 精度可以到微秒。

如果需要开启慢查询日志，需要在MySQL的配置文件 /etc/my.cnf 中配置如下参数：

#慢查询日志
slow_query_log=1
 
#执行时间参数
long_query_time=2

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以配置如下参数进行开启记录。

#记录执行较慢的管理语句
log_slow_admin_statements =1
 
#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes = 1
 
#指定的存储位置
slow_query_log_file = /var/log/mysql/mysql-slow.log

上述所有的参数配置完成之后，都需要重新启动MySQL服务器才可以生效。

---

EXPLAIN ：一个SQL语句执行很慢, 如何分析

可以采用 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息

• possible_key 当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引占用的大小

通过key + key_len 两个字段查看是否可能会命中索引

• Extra 额外的优化建议

• type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all

  • system：查询系统中的表
  • const：根据主键查询
  • eq_ref：主键索引查询或唯一索引查询
  • ref：索引查询
  • range：范围查询
  • index：索引树扫描
  • all：全盘扫描

如果一条sgl执行很慢的话，我们通常会使用mysal自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sal是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了可以尝试添加索引或修改返回字段来修复

---

一张表影响查询效率的主要原因

• 磁盘 IO： 数据量大意味着需要从磁盘中读取更多的数据，而磁盘 IO 速度是相对较慢的，因此会影响查询效率。
• 索引失效： 索引是提高查询效率的重要手段，但是如果索引失效，就会导致查询效率下降。
• 数据分页： 当需要查询大量数据的时候，数据库需要进行数据分页，而数据分页的过程需要占用大量的 CPU 资源，因此也会影响查询效率。
• 锁竞争： 当多个事物同时对同一个表进行读写操作时，就会产生锁竞争，而锁竞争会导致查询效率下降。
• 内存使用： 当表数据量大的时候，需要占用更多的内存空间来缓存数据，而如果内存不足，就会导致数据库频繁地进行磁盘 IO，从而影响查询效率。

---

InnoDB 如何解决幻读的问题

InnoDB解决幻读问题方案

• 在RR隔离级别下通过MVCC + 间隙锁
• 可串行化隔离级别

MVCC保证读操作一致性

仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

我们看到，在RR隔离级别下，只是在事务中第一次快照读时生成ReadView，后续都是复用该ReadView，那么既然ReadView都一样， ReadView的版本链匹配规则也一样， 那么在一个事务中，最终快照读返回的结果也是一样的。

为什么只靠MVCC不足以解决幻读问题

MVCC只是保证了读操作是没有问题的，但是你看到没问题不代表你就可以为所欲为，眼见不一定为实！举个栗子：在【3，7】这个区间中，我通过MVCC可以保证我返回的快照读结果都是一样的对吧，哪怕有人在5下标位置插入元素我也看不到，而如果我看快照读显示5下标是空的，那我在当前事务插入元素在5下标位置，那么不就over了嘛，所以说，我们读到的区间得加个锁，确保他不会在我这个区间中插入或修改数据，这样子实际情况就可以和我快照读结果一致，从而避免幻读问题

间隙锁保证写操作可行性

间隙锁只存在于RR / 序列化隔离级别下

间隙锁可以在事务读取数据时锁定间隙，从而防止其他事务在该间隙内插入新的数据，避免了幻读的问题。

注意: 间隙锁目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

可串行化保证并发隔离

串行化是最高级别的隔离级别，它将事务彼此完全隔离，每个事务按顺序逐个执行。当一个事务执行时，MVCC会对所有锁定的行进行排他锁，确保其他事务无法对这些行进行修改。因此没有了并发的概念，就没有事务的问题了

---

exist和in的区别？

exists用于对外表记录做筛选。exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。

select * from Awhere id in(select id from B)

子查询的表比较大的时候，使用exists可以有效减少总的循环次数来提升速度；当外查询的表比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度。

---

truncate、delete与drop区别？

相同点：

• truncate和不带where子句的delete、以及drop都会删除表内所有数据。
• drop、truncate都是DDL语句（数据定义语言），执行后会自动提交。

不同点：

• truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；
• 一般来说，执行速度: drop > truncate > delete。

在数据库操作中，TRUNCATE和DELETE是可恢复的操作，而DROP是不可恢复的操作。

---

having和where的区别？

• WHERE在查询之前对行进行筛选，而HAVING在分组后对结果进行筛选。
• WHERE可以在任何SELECT查询中使用，而HAVING通常与GROUP BY一起使用。
• WHERE对单个行进行筛选，HAVING对分组后的结果进行筛选。

假设我们有一个名为orders的表，包含以下列：order_id、customer_id、order_date 和 total_amount。我们想要根据销售总额分组，并找出销售总额大于1000的顾客。

使用WHERE子句进行筛选：

SELECT customer_id, SUM(total_amount) AS total_sales FROM orders WHERE total_amount > 1000 GROUP BY customer_id;

在这个例子中，WHERE子句用于在查询之前对行进行过滤，筛选出销售总额大于1000的订单。然后，根据顾客ID进行分组，并计算每个顾客的销售总额。

使用HAVING子句进行筛选：

SELECT customer_id, SUM(total_amount) AS total_sales FROM orders GROUP BY customer_id HAVING total_sales > 1000;

在这个例子中，我们先将订单表按顾客ID分组，然后计算每个顾客的销售总额。然后，在HAVING子句中，我们筛选出销售总额大于1000的顾客。需要注意的是，HAVING子句在分组之后进行筛选。

---

聚簇索引（聚集索引）

回表查询

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

通常都是采用覆盖索引来避免回表查询

覆盖索引

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到，主打的就是个避免回表查询

---

深分页问题

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。我们一起来看看执行limit分页查询耗时对比：

select * from tb sku limit 0,10 (耗时0.00sec) select * from tb sku limit 9000000,10 (耗时11.05sec)

当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL不通过索引来排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大 。

其实以下几种优化方案的设计都是基于触发索引的理念，最好能触发主键索引，再差也得是个覆盖索引，尽量避免回表

覆盖索引 + 子查询

select * from tb_sku t, (select id from tb_sku order by id limit 9000000,10) a ——>这个子查询的结果为一张表 where t.id = a.id;

记录上次查询的位置

在进行分页查询时，我们可以记录上次查询的最后一个ID，然后在下次查询时，直接从这个ID开始查询，避免检索起始位置之前的数据

SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT 10;

分库分表

基于深分页问题的产生缘由，通过将一个表进行水平拆分为多个子表，这样子可以减少每一张表的数据量，降低过滤数据的消耗

---

索引创建原则

• 针对于数据量较大，且查询比较频繁的表建立索引。
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）、联接操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。（哒咩性别这种重复度高的设置索引）
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 索引列不能参与计算：带函数的查询不建议参与索引。
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引需要额外占用物理空间

对创建了索引的表进行数据的增加、修改、删除时，会同步动态维护索引，这个部分会造成性能的影响

---

索引失效

判断索引是否失效——>执行计划explain

违反最左前缀法则

最左前缀法则：查询从索引的最左前列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效

意思就是说，如果你绑定了ABC三项作为一个联合索引的话，那你查询的过程中。where语句后面，他第一个查询条件必须是A相关的，如果不是A，那么该索引就会失效，如果你的查询语句是以A开头，且包含了C但没有B的话，那么B和C的查询都不会调用到索引，只有A调用到了索引。

索引列运算

不要在索引列上进行运算操作，否则索引将失效

select * from tb user where substring(phone,10,2)= '15'; // substring()属于运算操作

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效

select * from tb user where phone = 17799990015; // phone字段是varchar型

隐式类型转换

SELECT * FROM users where username = 123; -- 索引生效 SELECT * FROM users where username = '123';

以%开头的Like模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。有头有尾也失效

or连接的字段没有全都具备索引

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。只有or前后的列都具备索引才能保证索引的正常使用

开区间范围查询

联合索引中，出现范围查询(> or <)，范围查询右侧的列索引失效

解决方法很简单，尽量不要单是用一个">"或"<"，而是搭配一下"="，这样子用">="或者"<="就可以解决了

三大范式

第一范式（1NF）：

第二范式（2NF）： 确保表中的每个数据都与主键直接相关，而非间接相关。一个表可能有多个字段构成主键，当一个表的非主键字段与主键只相关于其中一部分时，就需要进行拆分。这样可以避免数据冗余和插入异常，保证数据的一致性和准确性。

第三范式（3NF）： 确保表中的非主键字段之间没有传递依赖关系。当一个表中存在非主键字段之间的传递依赖时，应将其拆分成多个表。这样可以进一步避免数据冗余和更新异常，提高数据的灵活性和可维护性。

---

SQL优化

架构优化

• 搭建主从，读写分离（提高并发读）
• 分库分表（提高存储量和单位查询效率）

表优化

• 设置主键自增id，确保有序性，减少页分裂页合并带来的性能损耗
• 外键约束关联，确保数据准确性
• 全局数据抽离成表给予外键约束（eg：图片表）
• 字段类型根据实际业务进行选择，可变用varchar，不可变用char等等
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）、联接操作的字段建立索引。
• 根据业务查询的需求，自定义联合索引，触发业务接口的覆盖索引，避免回表
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引优化

针对索引的优化详细看<索引创建原则>

SQL语句优化

Insert优化

批量插入

如果需要向表中插入大量数据，可以考虑使用批量插入。通过将多个insert语句合并为一个较长的SQL语句一次性执行，有效降低了网络传输和数据库访问的次数，提高了插入操作的效率。

insert into tb test values(1,'Tom'),(2,'Cat'),(3,erry')

手动提交事务

频繁的开启和提交事务会产生很大的资源开销，我们尽量在一个时间内开启事务，专门进行某些操作，最后提交，这样子就减少资源消耗

start transaction; 
insert into tb test values(1,'Tom'),(2,'Cat'),(3,'erry'); 
insert into tb test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); 
insert into tb test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;

• where 子句中避免is null /is not null

• SELECT语句务必指明字段名称（避免直接使用select * ）

• SQL语句要避免造成索引失效的写法尽量用union all代替union ，union会多一次过滤，效率低

• 避免在where子句中对字段进行表达式操作Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动

  • 内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

MD，好多，不想统计了，自己在网上找找

---

MySQL主从同步原理

在主从复制中，有一个主数据库（Master） 和 *一个或多个从数据库（Slave） *。主数据库负责处理事务操作（INSERT、UPDATE、DELETE） ，并将这些操作的日志（binlog） 传送给从数据库。从数据库通过解析主数据库的日志并执行相同的操作，在从库上实现数据的同步。

MySQL主从复制的核心就是二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

主从复制实现步骤

1. 主库将数据的事务操作（DML） 记录到一个二进制日志中（即：binary log），也就是配置文件中指定的log-bin指定的文件就是日志文件。
2. 从库会监听主库的binary log日志文件变化，当发生数据变化后，将主库的日志文件拷贝到他的中继日志即配置文件中指定的relay log日志文件中，I/O线程去请求主库的bin-log日志，并将日志写入到relay log中继日志中，此时主库会生成 *一个log dump线程 *，用来给从库I/O线程传输bin-log日志文件。
3. 从库会更新relay log文件中的操作，将数据的改变在从库中进行数据重演即重新执行一次，即SQL线程执行操作，将日志文件中的记录变为数据操作行为再次执行，以达到主从数据最终一致性的目的。

---

Mysql 主从集群同步延迟问题怎么解决

本节内容节选：MySQL 主从同步及延迟原因分析_mysql主从同步问题-CSDN博客

主从数据同步涉及到网络数据传输，由于网络通信的延迟以及从库数据处理的效率问题，就会导致主从数据同步延迟的情况。

主从延迟分析

正常情况下，在排除网络延迟或丢包的情况下，最直接的影响主从延迟就是从库上sql_thread执行中转日志（relay log），而造成原因一般是以下几种：

1. 从库的机器性能比主库要差

比如将20台主库放在4台机器，把从库放在一台机器。这个时候进行更新操作，由于更新时会触发大量读操作，导致从库机器上的多个从库争夺资源，导致主从延迟。

不过，目前大部分部署都是采取主从使用相同规格的机器部署。

2. 从库的压力大

按照正常的策略，读写分离，主库提供写能力，从库提供读能力。将进行大量查询放在从库上，结果导致从库上耗费了大量的CPU资源，进而影响了同步速度，造成主从延迟。

对于这种情况，可以通过一主多从，分担读压力；也可以采取binlog输出到外部系统，比如Hadoop，让外部系统提供查询能力。

3. 大事务的执行

一旦执行大事务，那么主库必须要等到事务完成之后才会写入binlog。

比如主库执行了一条insert … select非常大的插入操作，该操作产生了近几百G的binlog文件传输到只读节点，进而导致了只读节点出现应用binlog延迟。

因此，DBA经常会提醒开发，不要一次性地试用delete语句删除大量数据，尽可能控制数量，分批进行。

4. 主库的DDL(alter、drop、create)

1. 只读节点与主库的DDL同步是串行进行，如果DDL操作在主库执行时间很长，那么从库也会消耗同样的时间，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么从节点上也会耗费10分钟。
2. 从节点上有一个执行时间非常长的的查询正在执行，那么这个查询会堵塞来自主库的DDL，表被锁，直到查询结束为止，进而导致了从节点的数据延迟。

5. 锁冲突

锁冲突问题也可能导致从节点的SQL线程执行慢，比如从机上有一些select … for update的SQL，或者使用了MyISAM引擎等。

6. 从库的复制能力

一般场景中，因偶然情况导致从库延迟了几分钟，都会在从库恢复之后追上主库。但若是从库执行速度低于主库，且主库持续具有压力，就会导致长时间主从延迟，很有可能就是从库复制能力的问题。

从库上的执行，即sql_thread更新逻辑，在5.6版本之前，是只支持单线程，那么在主库并发高、TPS高时，就会出现较大的主从延迟

因此，MySQL自5.7版本后就已经支持并行复制了。可以在从服务上设置slave_parallel_workers为一个大于0的数，然后把slave_parallel_type参数设置为LOGICAL_CLOCK

主从延迟处理方案

主从同步问题永远都是一致性和性能的权衡，得看实际的应用场景，若想要减少主从延迟的时间，可以采取下面的办法：

1. 降低多线程大事务并发的概率，优化业务逻辑
2. 优化SQL，避免慢SQL，减少批量操作，建议写脚本以update-sleep这样的形式完成
3. 提高从库机器的配置，减少主库写binlog和从库读binlog的效率差
4. 尽量采用短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时
5. 实时性要求的业务读强制走主库，从库只做灾备，备份
6. 通过并行复制解决从库复制延迟的问题

实现MySQL并行复制策略的关键在于使用多个线程同时从主库上读取不同的二进制日志，并将其应用到不同的从库中。为了实现这个目标，需要在MySQL的配置文件中添加一些参数来指定并行复制的参数和线程数。

---

数据量达到多少的时候要开始分库分表

• 单表数据量：如果单个表的数据量已经非常大，例如超过了百万级别，就需要开始考虑分表。
• 数据库性能：当单个数据库的性能无法满足业务需求时，就需要考虑分库。
• 数据访问频率：如果某些表的数据访问频率非常高，单个数据库节点无法满足高并发请求，就需要考虑将这些表分到不同的库或表中，以提高性能。
• 业务拆分：当系统的业务逻辑越来越复杂，不同的业务之间的数据耦合度越来越低，就需要考虑对系统进行拆分，以方便管理和扩展。

---

二进制文件binlog有几种格式？

• statement

  • 记录的是 SQL 的原文。好处是，不需要记录每一行的变化，减少了binlog 日志量，节约了 IO，提高性能。由于 sql 的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

  这个格式的binlog有可能导致数据的不一致性，因为对于一些实时性的SQL，如带有Date()函数的SQL，当他在从库重新执行的时候，主从记录的时间数据是不同的

• row【推荐】

  • 不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，由于是对全表进行更新，也就是每一行记录都会发生变更，如果是一个100 万行的大表，则日志中会记录 100 万条记录的变化情况。日志量大大增加，因此这种模式的文件保存的信息太多，日志量太大。

• mixed

  • 一种折中的方案，普通操作使用 statement 记录，当无法使用 statement的时候使用 row

  一样有不一致性