- 多个配置文件
- SHOW VARIABLES查看系统变量(VARIABLES) & 状态变量(STATUS)
- SET/SET @@设置系统变量
- 比较运行配置和配置文件中配置的差别
- 【关键参数】Mysql关键的性能参数
- sql_mode:处理sql的方式
- Flush
- Buffer
- binlog_format
- innodb_file_per_table
- max_length_for_sort_data【控制order by算法】
- sql_safe_updates:没有where无法删除
- 其他
多个配置文件
- 以最后一个配置文件为准
- 以后面的组为准
- —defaults-file=/xxx.txt :只搜索配置文件xxx.txt
- —default-extra-file:增加配置文件
SHOW VARIABLES查看系统变量(VARIABLES) & 状态变量(STATUS)
- SHOW VARIABLES:显示系统变量
- show variables like ‘datadir’;
- 系统变量:SHOW @@变量名
- 用户变量:SHOW @变量名
-
SET/SET @@设置系统变量
session可省略
- @@就是将空格变成点。
SET PERSIST
比较运行配置和配置文件中配置的差别
【关键参数】Mysql关键的性能参数
sql_mode:处理sql的方式
MySQL的sqlmode解析与设置美意如此-CSDN博客
是否报错取决于sql_mode
5.7已经默认严格模式:
STRICT_ALL_TABLES vs STRICT_TRANS_TABLES - H0t-A1r-B4llo0n
Flush
innodb_flush_log_at_trx_commit:【提交时提交时提交时redo_log写入磁盘机制】
sync_binlog:【binlog写入磁盘机制】
Buffer
【buffer_pool】innodb_buffer_pool_size
buffer_pool 大小
建议:设置为机器的50%~60%
innodb_buffer_pool_instances【控制buffer pool 数量】
innodb_buffer_pool_chunk_size【buffer pool 被拆为chunk】【动态调整buffer pool大小】
【change_buffer】innodb_change_buffer_max_size
innodb_change_buffer_max_size:表示change buffer在buffer pool中的最大占比,默认25%,最大50%
【rnd_buffer】read_rnd_buffer_size:回表用【核心是查范围,要查多个主键】
Multi-Range Read (MRR)优化
回表索引字段排序用(根据索引A获取到的主键是乱序的,主键排序后回表,提高速度)
开启:optimizer_switch=”mrr_cost_based=off”
如果使用MRR explain extra字段会显示using MRR。
read_rnd_buffer_size=1MSET GLOBAL read_rnd_buffer_size = 1024*1024;
目的:根据索引a查数据。
步骤:
- 根据索引a先找到主键索引id;
- 排序找到的id
- 拿着排序好的id批量回表。速度快。不用一个id一次回表。
join也可以利用 以上思想。
关联两个表就是拿一堆key 去另一个表中匹配。
思想一样,前提是有索引
原来一次join匹配一个值,怎样让join一次去匹配多个值呢?使用join_buffer
【join_buffer】join_buffer_size:【join 用】
两个地方用:
- BNL(相对于Simple nested loop join 就是加了 join buffer)
将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数. - index nested loop join BKA算法用
查看read_rnd_buffer_size参数。
join_buffer是 index nest loop join(NLJ,注意和BNL(Block nest loop join)不一样) 结合上面的MRR(回表排序批量读取)的产物。
BNL用不了,因为没有用到索引。纯粹的硬遍历。
给BNL被驱动表加上索引就可以使用BKA(Batched Key Access)算法了。
NLJ是通过index来join。所有将驱动表查到的index放入buffer中,排序后去查join表。
启动 BKA(Batched Key Access)算法,BKA算法依赖MRR
set optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on’;
被驱动表建立索引,BNL算法转为BKA。
【sort_buffer】sort_buffer_size & innodb_sort_buffer_size
MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer。sort_buffer_size(sort_buffer)的大小。
- innodb_sort_buffer_size
这个参数只会在创建索引的过程中被使用,不会用在后面的维护操作;在索引创建完毕后innodb_sort_buffer会被释放。
binlog_format
innodb_file_per_table
innodb表空间
MySQL 5.6.6 之后默认 innodb_file_per_table = on
max_length_for_sort_data【控制order by算法】
using filesort & using temporary
sql_safe_updates:没有where无法删除
设置为on
delete update 没有where 或where中没有索引字段,报错。
其他
prompt设置提示符【显示用户和库名】
设置密码
- 5.6需要使用password函数,5.7不用