mysql参数设置及建议
获取配置文件读取顺序
sudo mysqld --verbose --help | grep -A 1 'Default options'
-- 设置全局参数
set global 参数名=参数值
set @@gloabl.参数名:=参数值
-- 设置会话参数
set session 参数名=参数值
set @@session.参数名:=参数值
内存参数
以下为每个连接使用内存上线参数
sort_buffer_size 排序内存
join_buffer_size 连接查询内存
read_buffer_size 读内存
read_rnd_buffer_size 索引内存
innodb_buffer_pool_size：总内存-（每个线程所需内存*连接数）-系统保留内存，重启生效 配置文件或启动参数设置
key_buffer_size myIsam索引缓存
I/O
innodb_log_file_size: 单个事务日志大小（记录一小时事务日志）
innodb_log_file_in_group: 事务日志文件个数（循环写入，多个对性能无影响可不设置)
事务日志大小=innodb_log_file_size*innodb_log_file_in_group
innodb_log_buffer_size: 事务日志缓冲器大小（一般每秒刷新，32M-128M）
innodb_flush_log_at_trx_commit 事务日志缓存刷新模式
- 0: 每秒进行一次log写入cache, 并flush log到磁盘
- 1(默认): 在每次事务提交执行log写入cache, 并flush log到磁盘,最安全，性能差.
- 2(建议): 每次事务提交, 执行log数据写入到cache, 每秒执行一次flush log到磁盘.
innodb_flush_method=O_DIRECT
不缓存不预读， 关闭操作系统缓存
innodb_file_per_table=1
1 每个表单独表空间
innodb_doublewrite = 1
启用双写缓存，防止数据损坏
delay_key_write myIsam 控制关键字缓冲的脏块何时刷新到磁盘文件中
- OFF：最安全选项，性能最差；
- ON：只对在键表时指定了delay_key_write选项的表使用延迟刷新
- ALL：注意，如果服务器发生崩溃，且缓存中有块没有写入到磁盘文件中时，会造成MyISAM表索引文件的损坏，需要进行修复（repair table）
query_cache 查询缓存
mysql8.0已经删除查询缓存， 8.0之前版本建议关闭
have_query_cache=no 表示当前版本不支持query_cache
have_query_cache_type=off 关闭query_cache
最大连接数
show variables like '%max_connection%'; -- 查看最大连接数
set global max_connections=1000; -- 设置连接数
show status like 'Threads%';
Threads_connected ：这个数值指的是打开的连接数.
Threads_running ：这个数值指的是激活的连接数，这个数值一般远低于connected数值.
Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数
安全相关
expire_logs_days 自动清理binlog.单位是 天
max_allowed_packet 控制MySql可以接收包的大小，主从一致
skip_name_resolve 禁用DNS查找，开启这个配置，需要对一些IP进行授权才能访问数据库.
read_only slave服务器，禁止非super权限的用户写权限
skip_slave_start 禁用slave自动恢复，奔溃后自动恢复不安全
一般再从数据库中使用，从数据库中的数据只允许主数据库的进行写的权限.
这个配置保证了主从数据库的一致性.
sysdate_is_now 确保sysdate()返回确定性日期建议添加这个参数
ql_mode 设置mysql所使用的sql模式
-
ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
-
NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
-
STRICT_TRANS_TABLES
在该模式下，如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制
-
NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零
-
NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
-
ERROR_FOR_DIVISION_BY_ZERO
在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL
-
NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
-
NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
-
PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似
-
ANSI_QUOTES
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
其他参数
sync_binlog 控制mysql 如何向磁盘刷新binlog
- 默认0 mysql并不会主动刷新有操作系统自己决定刷新
- 大于0 两次刷新binlog间隔时间
- 1 代表每次有事务提交就刷新磁盘，建议主db设置为1
tmp_table_size max_heap_table_size 一起使用
控制内存表临时表大小， 保存一致，不要太大以防溢出（超过物理内存）
max_connections 控制允许的最大连接数 一般 2000 更大（根据实际环境决定、服务器配置、网络、、、）
语句优化
分析
查询索引使用次数
SELECT
object_schema,object_name,index_name, count_star, b.`TABLE_ROWS`
FROM
performance_schema.table_io_waits_summary_by_index_usage a
JOIN
information_schema.tables b
ON a.`OBJECT_SCHEMA`=b.`TABLE_SCHEMA`
AND a.`OBJECT_NAME`=b.`table_name`
WHERE index_name is not null
order by object_schema, object_name;
慢查询日志
设置
set global slow_query_log=on;
set global long_query_time=0; -- 执行时间阈值，单位秒，最小单位微妙（如：0.001s）
set global slow_query_log_file='/show.log'; -- 保存位置
set global log_queries_not_using_indexes=on; -- 记录未使用索引的查询语句
分析工具
mysqldunmslow
mysqldunmslow -s r -t 10 slow-mysql.log
-s 排序
c:总次数
t:总时间
l:锁时间
r:总行数
at al ar: t,l,r 平均数
-t top 顶部几条
pt-query-digest
// 安装
yum install -y perl-CPAN perl-Time-HiRes
pt-query-digest --explain h=127.0.0.1,u=root,p=p@sdfsa show-mysql.log > result.rep
--explain 是否包含执行计划
实时性能分析
select * from information_schema.processlist where time > 60; -- time 执行时间 单位s
确定查询处理各阶段所消耗时间
profile (已经不提倡使用，即将移除 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead)
- set profiling=1 只有session级起作用
- 执行语句
- show profiles;查看每个查询消耗总时间
- show profile for query N; N profiles的query id 查询每个阶段所消耗时间
- show profile cpu for query N; 查看每阶段cup信息；
Performance Schema
use performance_schema;
-- 启动相关监控
update performance_schema.`setup_instruments` set enabled='YES', timed='YES' where name like 'stage%';
update performance_schema.`setup_consumers` set enabled='YES' where name like 'events%';
-- 查询语句执行时间
select a.`THREAD_ID`, `SQL_TEXT`, c.`EVENT_NAME`, (c.`TIMER_END` - c.`TIMER_START`) /1000000000 as `DURATION (ms)` from
events_statements_history_long a
join threads b on a.`THREAD_ID` = b.`THREAD_ID`
join events_stages_history_long c on
c.`THREAD_ID` = b.`THREAD_ID`
and
c.`EVENT_ID` between a.`EVENT_ID` and a.`END_EVENT_ID`
order by a.`THREAD_ID`, c.`EVENT_ID`;
sql优化
批量表数据修改
- 通过存储过程修改
DELIMITER $$
use `[DATABASE]`$$ --要修改的 schema
DROP PROCEDURE IF EXISTS `p_delete_row`
CREATE DEFINER=`root`@·127.0.0.1· PROCEDURE `p_delete_row`()
BEGIN
DECLARE v_rows INT;
SET v_rows = 1;
WHILE v_rows > 0; --通过循环分批处理
DO
DELETE FROM [table] WHERE id > 9000 AND id < 190000 LIMIT 5000; -- 批量修改语句
SELECT ROW_COUNT() INTO v_rows;
SELECT SELLP(5) --执行完一批休息一段时间，保证主从同步
END WHILE;
END$$
DELIMITER;
- 也可通过其他语言程序根据情况修改修改，注意分批和休眠（保证完成主从同步）
修改表结构
直接修改表结构会锁表，影响业务，可以通过pt-online-schema-change工具修改
工具会新建表，通过触发器同步旧表数据，然后删除旧表触发器
pt-online-schema-change 命令参数
--user= 连接mysql的用户名
--password= 连接mysql的密码
--host= 连接mysql的地址
P=3306 连接mysql的端口号
D= 连接mysql的库名
t= 连接mysql的表名
--alter 修改表结构的语句
--execute 执行修改表结构
--charset=utf8 使用utf8编码，避免中文乱码
--no-version-check 不检查版本，在阿里云服务器中一般加入此参数，否则会报错
pt-online-schema-change --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host} P=3306,D=${cnn_db},t=$table --alter
"${alter_conment}" --execute
为方便可以用shell脚本
#!/bin/bash
table=$1
alter_conment=$2
cnn_host='127.0.0.1'
cnn_user='user'
cnn_pwd='password'
cnn_db='database_name'
echo "$table"
echo "$alter_conment"
pt-online-schema-change --charset=utf8 --no-version-check --user=${cnn_user} --password=${cnn_pwd} --host=${cnn_host} P=3306,D=${cnn_db},t=$table --alter
"${alter_conment}" --execute
not in和<>优化
not in和<> 尽量改为关联查询
形如select ... where ... not in （select ... from table）的子查询改为join的关联查询
统计类sql优化
sum、count、avg等统计类sql
对实时性要求不敏感的可以通过定时更新汇总表的方式实现
对实时性要求高的可以通过缓存增量统计的方式实现，定时写入统计汇总表
