决战圣地玛丽乔亚Day22---Mysql查询优化

1.元数据锁MDL（）

https://www.yht7.com/news/208573

DML：select、insert、update、delete等数据操作

DDL：create、drop、alter等定义和修改数据结构的操作

元数据锁是表锁。DDL加写锁，DML加读锁。

所以DML和DDL操作互斥。

我们的生产环境用的最多的还是CRUD，会加DML读锁，读锁不互斥。而ddl操作大多数可以online执行，即使写锁也会很快降级成读锁。所以DDL阻塞DML的概率小。

DML阻塞DDL的概率是有的。如果DML事务还在运行，DDL请求会被阻塞在锁队列，一旦进入锁队列，写锁又会阻塞读锁造成性能大量消耗。

元数据锁的使用可能会导致性能下降或死锁的原因是，当一个事务占用了某个元数据对象的锁时，其他事务必须等待该锁的释放才能访问或修改该对象，如果有大量的事务同时请求同一元数据对象的锁，可能会导致锁等待的队列变得很长，从而影响数据库的性能。

此外，如果某个事务同时请求多个元数据对象的锁，并且其他事务正在占用其中某个锁，就可能会导致死锁。例如：

• 事务A正在占用表T1的元数据锁，并请求占用表T2的元数据锁。
• 同时，事务B正在占用表T2的元数据锁，并请求占用表T1的元数据锁。
• 由于事务A和B互相等待对方释放锁，它们就会形成死锁，导致数据库无法继续执行其他事务。

为了避免元数据锁对性能和死锁的影响，可以采用以下几种方法：

• 尽量避免在高并发环境下频繁修改元数据对象，例如表结构、索引定义等。
• 将长时间的DDL操作拆分成多个小的操作，减少锁等待的时间。
• 使用合适的事务隔离级别，避免不必要的锁竞争。
• 对于需要大量修改元数据对象的操作，可以考虑在低峰期执行，或者使用主从复制等方式将操作分散到多个服务器上执行。

 

2.Online  DDL（）

https://blog.csdn.net/weixin_45238761/article/details/125343029

由于DML和DDL操作互相阻塞，所以用OnlineDDL来减少带来的影响。

传统的DDL操作通常需要占用较长时间的锁来防止并发修改和读取数据的操作干扰，这可能会导致数据库的长时间阻塞和性能下降。

而Online DDL则是通过一系列的优化技术来避免或最小化DDL操作对数据库的阻塞，从而实现在不影响业务运行的情况下完成DDL操作。

Online DDL的实现方式包括但不限于：

1. 基于副本的Online DDL

   基于副本的Online DDL是通过在一个副本上执行DDL操作，然后将更改应用到主副本上来实现的。在副本上执行DDL操作，不会阻塞主副本上的操作，因为主副本会继续运行，并在更新完成后与副本进行同步。在这种情况下，主副本可以继续处理更新，并且DDL操作的影响仅限于副本。

2. 基于InnoDB的Online DDL

   InnoDB存储引擎支持Online DDL，因为它提供了一种非阻塞的DDL机制，称为“InnoDB在线DDL”。InnoDB在线DDL的原理是通过在内部创建临时表，以及使用内部记录格式和一致性视图等技术来实现DDL操作的原子性和一致性，避免长时间的锁等待。

3. 基于Online Schema Change（OSC）工具的Online DDL

   Online Schema Change（OSC）是MySQL提供的一种工具，它可以在不锁定表的情况下执行表结构修改。它的原理是通过创建一个新表来存储修改后的表结构，然后将数据从旧表复制到新表。在此期间，旧表上的读写操作仍然可以继续执行，而新表则保持锁定状态，直到完成数据复制后替换旧表。

Online DDL的优点是可以最大限度地减少对数据库业务的干扰，避免长时间的锁等待和阻塞，从而提高了数据库的可用性和性能。但是需要注意的是，Online DDL也有一些限制和适用条件，需要根据具体情况进行选择和使用。

 

 

3.高性能Mysql   第六章关于查询优化的学习

(索引优化、写入变慢的解决、回表优化、大事务优化、DDL慢)

优化数据访问：

1.对数据库请求数据是否都是必要的查询？

1）多表关联避免select * ， 需要哪一列返回哪一列

2）重复性的请求，尽量使用缓存

2.是否为了返回这些数据，进行了多余的扫描？

1.响应时间是否合理？

2.扫描的行数（这个首先于很多，也不好判断：行数据的大小？磁盘还是内存中的行？）

这个可以用explain来进行分析，通过type等属性来判断是否合理和可以优化。

如果发现扫描了行数远超返回行数可以进行优化：

　1.覆盖索引

　2.单独起一个汇总表

　3.对复杂查询进行重写，让MYSQL优化器有更好的方式执行这个查询

 

重构查询方式：

1.把复杂的查询拆分成一个个的小查询。

2.分解关联查询。把关联查询进行分割，对每个表进行一次单表查询，然后将结果在应用程序进行关联。

应该把多个join拆分成不同的select 然后在代码进行关联的方式。

这样做：

缓存效率高。单表查询结果易缓存。如果是数据库层面的多表关联，如果某个表发生变化，缓存会失效。拆分多个单表查更容易缓存。

拆成单个查询锁竞争减少。

使用IN代替关联查询，让mysql根据id顺序查比随机查要效率高。

在代码层面做关联查询，某条记录可能只需要查一次。如果用数据库，可能会要重复访问一部分数据。

在代码层面做关联，更容易实现对数据库做拆分。