优化 | learning

优化

为什么不要使用长事务

innodb的隔离级别”读提交”,”可重复读”使用快照读,基于MVCC控制读取到的数据,如果一个事务过长,这个记录上所做的修改都保存在回滚段里面,占用存储空间和锁资源

select now(),trx_started,(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx_started)) from information_schema.innodb_trx; #查看长事务

为什么删除一半数据,空间还没减少?

innodb_file_per_table=ON
#因为innodb删除数据,只是标记删除,等插入数据时候空间可复用,数据的索引也还在,只有重建才可以
alter table T engine=InnoDB #重建表

mysql 为什么有时候会”抖”一下

如果一个查询请求,需要刷脏页,那么mysql就需要将数据merge到磁盘,如果脏页太多,查询过程就会停顿

innodb_io_capacity=20000 #刷脏页的io速度,一般设置为磁盘iops,可以使用fio工具测
innodb_flush_neighbors=0 #是否刷邻居脏页 0不需要 1需要 一般ssd不需要

count(*) count(1) Count(id) count(字段)

count(id) :遍历整张表,把每一行的id取出来,返回,server判断不为空,加1 count(1) :遍历整张表,不取值,server对于每一行,加1 count(字段):遍历整张表,返回对应的字段,如果不为null,加1 count(*) :遍历整张表,不取值,直接加1,mysql优化器做的优化

按照效率:count(字段)<Count(id)<count(1)<count(*),建议count(*)

redo log一般设置多大?

设置4个文件,每个1G

order by设置优化

sort_buffer_size=2097152 #排序可使用的内存
max_length_for_sort_data=1024 #每一行的数据长度

排序过程:
1. 初始化`sort_buffer`,确定排序字段,如果选取的字段小于`max_length_for_sort_data`的长度,就取出所有字段进行全字段排序,否则进行rowid排序
2. 根据条件,选择满足条件的行的字段,放入`sort_buffer`
3. 重复步骤2
4. 对`sort_buffer`中数据按照排序字段进行排序,如果数据大于`sort_buffer_size`,需要使用外部排序,归并排序

```sql
#排序语句
select city,name,age from t where city='杭州' order by name limit 1000  ;
#增加联合索引,不需要排序步骤,因为数据已经有序
alter table t add index city_user(city, name);
#增加所有字段的联合索引,不需要回表,使用覆盖索引
alter table t add index city_user_age(city, name, age);

如何保证crash_safe

innodb_flush_log_at_trx_commit=1 #每次redo log刷新到磁盘
sync_binlog=1 #每次binlog到磁盘

主从复制

设置主从复制跳过错误

slave_skip_errors="1032,1062" #1032删除数据时找不到行 1062唯一键冲突

GTID:全局事务id(GTID=server_uuid:gno)

#开启gtid
gtid_mode=on
enforce_gtid_consistency=on

限制并发查询

#进入锁等待的线程,不计数
innodb_thread_concurrency=0 #0:不限制 一般设置:64~128来减少上下文切换

主备健康检查

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();
/* 开启监控redo log */
mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';
/* 设置阈值200ms */
mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;
/* 清空 */
mysql> truncate table performance_schema.file_summary_by_event_name;

误删

防范:sql_safe_updates=on delete操作必须加where

误删除行,使用Flashback
误删库/表全量备份,binlog恢复,没gtid,使用start,stop,有gtidset gtid_next=gtid1;begin;commit;

mysql是边读边发的

net_buffer_length=8192意味着数据达到8k,server端就会发送一次数据给client Sending to client表示客户端处理阻塞 Sending Data表示服务端正在处理数据

赋权和收权

grant all privileges on *.* to 'ua'@'%' with grant option;
revoke all privileges on *.* from 'ua'@'%';

datetime、timestamp 的区别

时间范围
- timestamp:是 UTC 时间’1970-01-01 00:00:01’ UTC - ‘2038-01-19 03:14:07’ UTC
- datetime:1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
时区
- timestamp:存储的是 UTC 时间，所以会有时区的概念,会将客户端插入的时间从当前时区转换为 UTC 再进行存储；查询时，会从 UTC 转换回客户端当前时区再进行返回.
- datetime:没有时区概念，客户端传什么时间就存什么时间，省去了转换时区的步骤.
字节数
- 存储的都是二进制而不是字符串
- timestamp:4 个字节
- datetime:5 个字节（有些教程会写 8 个，但官方文档目前 mysql8 中 datetime 是 5 个字节进行存储）它们在保存小数秒时，都将使用额外的空间（1-3 个字节）.

join优化

参考文章

常见优化

如果明确知道只有一条结果返回，limit 1能够提高效率,明确告诉数据库，让它主动停止游标移动
避免全表扫描
只返回需要的列，能够大大的节省数据传输量，与数据库的内存使用量
项目上线前,将慢查询日志打开,并把long_query_time=0,查看所有的日志

group by 的优化

如果对group by 语句的结果没排序要求,加上order by null
尽量让group by 使用索引,确认方法是explain没有Using temporary Using filesort
如果group by需要统计的数据量不大,使用内存表,修改参数tmp_table_size`
如果数据量大,使用SQL_BIG_RESULT来告诉优化器使用排序算法等到结果
show status like "%innodb_rows%"; 查看实际扫描的行数

全表扫描原因

强制类型转换
负向查询(！= 或 <>时)
左模糊查询
is null查询
or条件2边一个有索引,一个没有
函数操作

关系型数据库的缺点

行记录,无法存储数据结构 (redis)
schema 扩展很不方便,比如数据多的情况下,添加列,锁表 (mongodb)
大数据场景下,i/o高 (hbase)
全文检索弱 (elasticsearch)

learning_notes

学习笔记

优化