mysql优化技巧

一、mysql在线ddl

例如建索引，常规建索引会发生锁表的情况，特别是大表建索引会持续很久，一直锁表是肯定不行的，因此就有了在线建索引
SQL如下：

ALTER TABLE tb_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM 指定 DDL 执行的算法：

• ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。
• ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。
• ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。

LOCK指定DDL期间的锁控制：

• LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。
• LOCK=EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、
• LOCK=SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和
• LOCK=DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）
  不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以查看:
  Mysql 5.7 Online DDL Operations
  Mysql 8.0 Online DDL Operations

二、虚拟列

平时开发中部分业务场景的查询需要使用到函数来实现，尽管我们尽量应该避免使用函数来查询，因为这会导致索引失效，系统性能急剧下降。
然而在某些情况下，我们接触的是一套老的系统，系统中已经有这样的情况，系统改造成本过大，我们就可以选择另一种方式去优化sql性能，这就是虚拟列。

1. 首先是建一个虚拟列：
   SQL如下：

ALTER TABLE 表名称 add column 虚拟列名称 虚拟列类型 [GENERATED ALWAYS] as (表达式) [VIRTUAL | STORED];

比如对一个ifnull函数建虚拟列

ALTER TABLE tb_name add column name_v INT GENERATED ALWAYS AS (ifnull(wechat_name, name)) VIRTUAL ;
如果是mysql8可以使用下面的sql，避免加锁，速度也很快：
ALTER TABLE tb_name add column name_v INT GENERATED ALWAYS AS (ifnull(wechat_name, name)) VIRTUAL,algorithm =instant ,lock =none;

具体情况可查看Mysql CREATE TABLE and Generated Columns
2. 建好虚拟列以后再对虚拟列建索引：
ALTER TABLE ADD INDEX idx_name（name_v） ON tb_name;