MySQL

MySQL #

集群方案 #

MySQL Replication + MHA #

MHA方案

MHA只负责主库的高可用。主库发生故障时，MHA会选择一个数据最接近原主库的候选主节点作为新的主节点，并补齐和之前宕机主库差异的Binlog。补齐之后，再将写VIP漂移到新主库上。

MHA脑裂问题

问题点：网络抖动触发MHA切换后，VIP会漂到了新主库上。当网络抖动恢复后，由于VIP并没有及时从原主库上摘除，因此2台机器同时拥有VIP，同时提供服务，产生了脑裂。

解决方案：为MHA Manager加入向其他物理机进行探测的功能，让Manager通过对比更多的信息来判断是网络故障还是单机故障。

MySQL Cluster #

。。。

基于Paxos的MGR版本的MySQL #

。。。

基本用法 #

连接器 #

查看当前所有连接

show processlist;

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间由参数 wait_timeout 控制的，默认为 8 小时。

 show variables like 'wait_timeout’;

单位为秒。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果就需要重连，再执行请求。

客户端级别的读写分离 #

  1. 声明多个Datasource。
  2. Dao层进行CRUD操作时通过ThreadLocal为当前线程注入枚举值。
  3. 实现AbstractRoutingDataSource接口，在determineCurrentLookupKey()方法中根据ThreadLocal保存的枚举值返回对应的Datasource。

基数统计 #

通过基数统计可以判断当前索引添加是否合理。

show index from <table_name>；

返回结果

image

  • Cardinality：基数表示字段的区分度，为采样统计。当表中变更的行数超过阀值时会触发重新采样。区分度越高索引越可能被MySQL优化器采用。

MySQL误用索引 #

四种方式修正索引，推荐顺序由上到下。

  • 通过explain命令进行分析，如果扫描行数rows和实际行数区别大，就执行analyze table <table_name> 对基数进行重新采样。
  • 修改语句。
  • 新建一个更合适的索引，让优化器做选择。或者允许的情况下删除误用的索引。
  • 强制使用force_index。

添加索引或字段 #

方法一、大表时优先考虑 gh-ost 或者pt-online-schema-change这样的第三方方案，更加稳妥。

原理：

  1. gh-ost创建与原始表相似的幽灵表“源表名_gho”。
  2. 增量地将数据从原始表分批次插入复制到幽灵表中（默认1000条一次，–chunk-size=1000）。
  3. 与此同时，另一个线程读取binlog将正在进行的更改操作同步到幽灵表中。
  4. 最后锁定原始表，待binlog完全追上后，将原始表替换为ghost表。

方法二、MySQL 5.6 以后，使用Online DDL。

ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE;

方法三、MySQL8.0以后，alter语句加时间。非高峰期执行，失败后间隔一段时间继续重试。

alter table table_nam wait 10 add column`

方法四、在从库上执行，然后主从切换。属于传统方案，容易操作失误，但效率最高。

方法五、建空表后通过触发器等刷新数据。

# 创建新表
CREATE TABLE main_table_new LIKE main_table;
# 在新表上添加字段或索引
ALTER TABLE main_table_new ADD COLUMN location VARCHAR(256);
# 复制旧表数据到新表
INSERT INTO main_table_new SELECT *, NULL FROM main_table;
# 重命名新表和旧表
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
# 删除旧表
DROP TABLE main_table_old;

健康检查 #

select 1 语句返回成功只能说明数据库的进程还在。另外，当日志满了后，数据库还是能提供查询功能。所以查询语句也不能用来判断。

最好的解决方法是自建系统表

update health_check set t_modified=now();

如果是双主架构，为了避免双主同步时造成冲突，可以在表上再添加server_id字段作为标识。

SQL优化 #

explain #

通过 explain 分析 SQL 执行计划，返回的字段含义如下所述：

  • id：每个执行计划都有一个 id。联合查询会有多个 id。
  • select_type：查询类型。包含SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
  • table：查询的表名。如果给表起别名，则显示别名信息。
  • partitions：访问的分区表信息。
  • type：从表中查询到行所执行的方式。查询方式的结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
    • system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。
    • eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
    • ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。
    • range：遍历一段范围的索引，常见于使用了 <，>，between 等操作。
    • index：遍历了整个索引树。
    • ALL：全表扫描。
  • possible_keys：可能使用到的索引。
  • key：实际使用到的索引。
  • key_len：当前使用的索引的长度。
  • ref：关联 id 等信息。
  • rows：扫描的行数。
  • filtered：结果集占总扫描记录数的比例。
  • Extra：额外信息。
explain 语句仅停留在分析阶段，不表示真实执行状态。

show profile(已弃用) #

和explain纸上谈兵不同，show profile语句用于分析sql的具体执行性能。

# 开启性能分析
set profiling=0;
   
# 查询性能分析
show profiles；
   
# 查看具体的SQL执行步骤
show profile [type [, type] ... ]
   [FOR QUERY n]
   [LIMIT row_count [OFFSET offset]]

type参数：

  • ALL：显示所有开销信息
  • BLOCK IO：阻塞的输入输出次数
  • CONTEXT SWITCHES：上下文切换相关开销信息
  • CPU：显示CPU的相关开销信息
  • PC：接收和发送消息的相关开销信息
  • MEMORY ：显示内存相关的开销，目前无用
  • AGE FAULTS ：显示页面错误相关开销信息
  • SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数)
  • WWAPS：显示swap交换次数的相关开销信息

show profiles 默认情况下只显示最近执行的15 条SQL语句，可以通过设置profiling_history_size来增大该值，最大值为 100。不过该功能实际已被弃用，将来会被移除。官方推荐用performance_schema库的event_statment_**相关表替代，里面有87张表，部分功能默认开启。

performance_schema库 #

performance_schema库可用于分析SQL执行性能，找出长事务和慢查询。

查询长事务

可以在innodb_trx 这个表中查询长事务。比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

慢查询日志 #

通过以下命令行查询是否开启了记录慢 SQL 的功能，以及慢查询的阀值：

Show variables like 'slow_query%';
Show variables like 'long_query_time';

开启慢查询日志

# 开启慢查询日志
set global slow_query_log='ON'; 
# 指定慢查询日志地址
set global slow_query_log_file='/var/lib/mysql/test-slow.log';
# 设置慢查询阀值
set global long_query_time=1;

pt-query-digest #

建设中

处理慢SQL #

方案一、定时检测

上线一个定时监控和杀掉慢 SQL 的脚本。脚本每分钟执行一次，检测有没有执行时间超过指定阀值（如一分钟）的慢 SQL。如果发现，直接杀掉这个会话。

方案二、降级

请求超时进行降级。

历史数据清理 #

为什么删除数据后表空间没有释放 #

和 InnoDB 的物理存储结构有关系。逻辑上虽然每个表是一颗 B+ 树，但是物理上每条记录都是存放在磁盘文件中，这些记录通过一些位置指针来组织成一颗 B+ 树。当 MySQL 删除一条记录的时候，只是把文件的这块区域标记为空闲，然后再修改 B+ 树中相关的一些指针，完成删除。实际被删除的记录还是存在。

解决方案 #

方法一、建立新表

将数据复制到新表，然后改名。速度最快，但是需要停服，

方法二、在线迁移。

分批删除历史数据，采用主键删除可以提高删除效率。

方法三、释放表空间

通过 OPTIMIZE TABLE 重建表，释放存储空间，执行期间会锁表。重建过程中，索引也会重建，所以表数据和索引数据都会更紧凑，查询效率也会提升。但该优化有个前提条件，MySQL 的配置必须是每个表独立一个表空间（innodb_file_per_table = ON）。

集群优化 #

从库延迟原因 #

延迟的最直接表现是从库消费relay log的速度远低于主库生产binlog日志的速度。可能原因如下：

  • 主从机器性能差异。
  • 读写分离时没有关注读库的性能。
    • 可以多接几个读库或者让读库多接几个从库分摊压力。
  • 大事务。主库事务执行多长，从库之后也要执行多长时间。
    • 不要一次性删除大量数据，特别是历史数据，要分多个事务分批批删。
  • 在大表上执行DDL。
    • 使用gh-ost等方案。

监控延迟程度 #

  • 手动监控：在从库上执行show slave status可以看到从库落后主库多少秒（seconds_behind_master）。
  • 实时监控：使用pt-heartbeat 工具。

手动主从切换 #

  1. 从库 readonly=true，只读。
  2. 在从库查看 seconds_behind_master，低于5秒则执行下一步，否则重试。
  3. 设置主库 readonly=true，只读。
  4. 在从库查看 seconds_behind_master，等于0则执行下一步，否则重试。
  5. 把从库 readonly=false。
  6. 把业务切换到从库。

手动一主多从切换 #

例如主机A和备机A`互备，从机B、C、D作为主机A的从机，提供只读功能。

使用GTID进行切换

MySQL 5.6 版本引入的 GTID 模式。GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。

格式是：GTID=server_uuid:gno

server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

GTID 模式的启动也很简单，我们只需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on 就可以了。