mysql运维巡检那些内容 mysql日常运维

转载

墨舞天涯 2024-06-04 11:15:19

文章标签 mysql运维巡检那些内容 mysql 字段服务器数据 文章分类 MySQL 数据库

范式和反范式

范式和反范式是库表设计过程中的概念
目前关系数据库有六种范式，越高的范式数据库冗余越小
范式化可以较少冗余，从而减少了在更新数据时一致性方面的开销
反范式化由于冗余的数据，在复杂的查询场景下，可以避免联合查询和子查询，提高查询的效率
根据业务场景，选择合适的范式等级进行库表设计，是日常运维中的第一步

查询优化

使用索引

索引的使用，遵循最左前缀原则
使用覆盖索引，可以减少反表操作，大大提高效率，order/group by 也可以使用覆盖索引
varchar 等字符串类型，可以使用前缀索引提高查询效率
like 匹配也遵循最左前缀原则，例如 ‘aaa%’ 格式才能使用索引
NULL 类型对索引不友好，字段默认应该设置为 NOT NULL
where 字句里面，如果字段标识符使用了函数运算，会导致该字段无法使用索引
避免冗余和重复索引

多表查询的类型

子查询(exist/in)
内连接(inner join)
外连接(left/right/full join)

多表查询中的驱动表和被驱动表

情况1： select * from (select id,name from t1 where t1.type=3) as t3, t2 where t2.name = t3.name and t2.age>20;
情况2： select * t1 left join t2 on t1.name=t2.name where t2.age>20 and t1.type=3;
情况3： select * from t1 where t1.tape=3 and t1.name in (select t2.name from t2 where t2.age>20)
上述三种情况中，t1 是驱动表，t2 是被驱动表，查询的步骤是：
    1. 根据筛选条件，获取 t1 表相关的结果集
    2. 使用 t1 中的关联字段，去遍历 t2 表

多表查询的优化原则

情况 3 中子查询是用不到索引的，优化方式是改为情况 1 的写法，利用联合查询提高效率
小表驱动大表，指的是驱动表要尽量走索引，使结果集尽量小，上面三种情况中要求 t1.type 必须有索引，并且过滤度要高
连接字段索引，被驱动表上的关联字段必须有索引，上面三种情况中要求 t2.name 必须有索引

可以使用 show profile 查看查询执行时在每个阶段的开销
可以使用 explain 查看查询的执行路径
可以使用 hint 强制使用某个索引或者不适用索引

其他优化

delete 清理过期数据一定要使用 limit 分片，否则可能导致

锁住很多数据
占满整个事务日志
耗尽系统资源
阻塞重要查询
-DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000;

使用 truncate 清理所有数据的操作可以用下列操作代替，从而做到“灰度”、“可回滚”

create table_name_tmp like table_name;
rename table_name to test.dbname_tablename_time;
rename table_name_tmp to table_name;

online ddl, 不锁表的 dd

通过新建一个镜像表，在新表上做 ddl
做 ddl 过程中使用触发器复制数据
最后后，在业务低峰期，使用rename替换掉原来的表
核心思想是，空间换取时间

optimize table 优化表空间

delete 数据并不会释放表空间
某些表经过长时间的积累，会导致大量的空间浪费

运行状态

查看当前工作线程 show [full] processlist;
全局状态查询 show status;，常用的参考状态如下：

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。 
Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。 
Connections 试图连接MySQL服务器的次数。 
Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。 
Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。 
Delayed_writes 用INSERT DELAYED写入的行数。 
Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。 
Flush_commands 执行FLUSH命令的次数。 
Handler_delete 请求从一张表中删除行的次数。 
Handler_read_first 请求读入表中第一行的次数。 
Handler_read_key 请求数字基于键读行。 
Handler_read_next 请求读入基于一个键的一行的次数。 
Handler_read_rnd 请求读入基于一个固定位置的一行的次数。 
Handler_update 请求更新表中一行的次数。 
Handler_write 请求向表中插入一行的次数。 
Key_blocks_used 用于关键字缓存的块的数量。 
Key_read_requests 请求从缓存读入一个键值的次数。 
Key_reads 从磁盘物理读入一个键值的次数。 
Key_write_requests 请求将一个关键字块写入缓存次数。 
Key_writes 将一个键值块物理写入磁盘的次数。 
Max_used_connections 同时使用的连接的最大数目。 
Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。 
Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。 
Open_tables 打开表的数量。 
Open_files 打开文件的数量。 
Open_streams 打开流的数量(主要用于日志记载） 
Opened_tables 已经打开的表的数量。 
Questions 发往服务器的查询的数量。 
Slow_queries 要花超过long_query_time时间的查询数量。 
Threads_connected 当前打开的连接的数量。 
Threads_running 不在睡眠的线程数量。 
Uptime 服务器工作了多少秒。