innodb_buffer_pool_size 约物理内存的50%-70%

innodb_data_file_path 初始化大小至1G

innodb_log_file_size 1G以上

innodb_flush_log_at_trx_commit 0:最快数据最不完全 1:最慢最安全 2:折中

innodb_max_dirty_pages_pct 25%-50%为宜

innodb_io_capacity 普通机械键盘:1000左右 SSD:10000左右 PCIE-SSD:20000以上

key_buffer_size 32M以下

sync_binlog 0:最快数据最不完全,系统自己决定刷新binlog的频率 1:最慢最安全,每个event刷新一次 N:每N个事务刷新一次

long_query_time 建议设置小于0.5秒

open_files_limit & innodb_open_files 建议65535

max_connections 突发最大连接数的80%为宜,过大容易导致全部卡死

thread_handling = “pool-of-thread” 启动线程池

query_cache_size & query_cache_type 绝大多数情况下是鸡肋,最好关闭 QC锁是全局锁,每次更新QC的内存块锁代价高,很容易出现 waiting for query cache lock状态

ibdata1文件都存储了些什么内容
Data dictionary
Double write buffer
Insert buffer
Rollback segments
UNDO space
Foreign key constraint system tables

ibdata1文件暴增的原因
大量事务,产生大量的undo log
有旧事务长时间未提交,产生大量旧undo log
file i/o性能差,purge进度慢
32bit系统下有bug

ibdata1文件暴增解决
设置独立undo表空间
增加purge线程数,innodb_purge_threads
提高file i/o能力
事务及时提交,不要积压
默认打开autocommit = 1
检查开发框架,确认autocommit=0的地方,事务结束后都有提交和回滚

连接数过高 too many connections
常规的做法:想办法杀掉多余的连接,加大连接数
其实应该是这样:限制连接数,设定max_user_connections 设定额外端口extra-port(便于DBA在数据库最大连接数占满后仍然能连接数据库进行操作)
建议:定时检查,干掉慢查询,避免阻塞,自我保护

查看是否安装 (5.7+默认安装)
select * from sys.version;

便于人类阅读 结果集类似：background | 2143 | 266.48 ms
select * from host_summary_by_file_io;

便于工具处理 加上”x$” 结果集类似：background | 2148 | 266558291670
如果需要使用”order by”之类的操作的话不能显示 如：266.48 ms
select * from x$host_summary_by_file_io;

谁使用了最多的资源
select * from user_summary;

大部分连接来自哪里
select * from host_summary;

在哪个文件产生了最多的IO
select * from io_global_by_file_by_bytes

哪张表被访问的最多
select * from sys.schema_table_statistics;

哪些语句延迟较大，这些延迟较大的语句有哪些特征
select * from statement_analysis

哪些SQL语句使用了磁盘临时表
select * from statements_with_temp_tables

哪张表占用了最多的buffer pool空间
select * from innodb_buffer_stats_by_table

索引的好处
通过索引过滤，减少需要扫􏰁的记录数量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变为顺序IO
通过索引覆盖，加快查询

索引的坏处
占用磁盘空间
增加了记录的修改(插入、删除、修改)代价

快速查看每张表的索引情况
select * from schema_index_statistics;

索引的使用需要恰到好处，充分利用索引的优势，避免无用索引、冗余索引等

查看重复索引
select * from schema_redundant_indexes;

查看无用索引
SELECT * FROM schema_unused_indexes;

案例
a字段无索引 全表扫描
select a from t1 where a = 1;

给a字段添加索引
alter table t1 add index idx_a(a);

存在综合排序 不能命中索引
SELECT a,b FROM t1 WHERE a > 666 ORDER BY a, b;

添加组合索引
alter table t1 add index idx_a_b(a, b);

a索引重复 删除a索引
ALTER TABLE t1 DROP INDEX idx_a;