1. 不使用MyISAM
set persist default storage engine "InnoDB";
set persist only disabled storage engines="MyISAM";2. 并行导出数据
util.loadDump("/opt/dump",{threads:32})
util.dumpInstance("/opt/dump/",{threads: 32})
util.loadDump()
util.dumpTables()
util.dumpSchemas()
util.dumpInstance()3. 快速导入数据
start mysqld with --disable-log-bin
alter instance disable innodb redo_log;
set global innodb_extend_and initialize=0FF;
set global innodb_max_dirty_pages_pct=10;
set global innodb_max_dirty_pages_pct_lwm=10;4. 绕过中间存储引擎
-
将数据从A实例复制到B实例，可使用copy绕过dump和load中间存储；
util.copyInstance('mysql://elf@192.168.0.123',
{"compatibility":["force innodb","skip invalid accounts"],
threads: 32}
)5. 始终使用主键
-
规避contention问题(dict sys→mutex)和replication lag(复制滞后)；
-
为表定义好的主键，此也影响二级索引，而DBA，请启用GlPK模式；
set persist sql_generate_invisible_primary_key=1;6. 检查索引
-
不维护未使用的索引，因会减慢写入并加载Optimizer以创建QEP(查询执行计划)；
重复索引也是如此，可能还会错过导致全表扫描的索引，通过sys schema查询；
6.1. Unuse Index
select
database_name,table_name, t1.index_name,
format_bytes(stat_value * @@innodb_page_size) size
from sys.schema_unused_indexes t2
join mysql.innodb_index_stats t1
on object_schema=database_name
and object_name=table_name
and t2.index_name=t1.index_name
where stat_name='size'
order by stat_value desc;6.2. Dumplicate Index
select
t2.*,format_bytes(stat_value * @@innodb_page_size) size
from mysql.innodb_index_stats t1
join sys.schema_redundant_indexes t2
on table_schema=database_name
and t2.table_name=t1.table_name
and t2.redundant_index_name=t1.index_name
where stat_name='size'
order by stat_value desc7. 不可见索引
-
总怪网络，Invisible Index，让索引不可见比删除索引更合适；
alter table large_tbl drop index strange_idx;
alter table large_tbl alter index strange_idx invisible;8. 调整并行索引创建
-
InnoDB使用的并行线程数量由innodb_ddl_threads控制，此新变量新变量
innodb_ddl_buffer_size缓冲区大小耦合，若有快速存储和多CPU核，
那调整这些变量可加快二次upsecondary索引的创建速度；
调整并行索引创建
alter table booking
add index idx_2(flight_id,seat,passenger_id);
# 默认设定：
innodb ddlthreads=4
innodb ddlbuffer size=148576
innodb parallel read threads =4
# 查询cpu核心数：
select count from information_schema.innodb_metrics
where name = 'cpu_n';
# 设置并行线程数：
set innodb_ddl_threads = 8;
set innodb_parallel_read_threads =8;
set innodb_ddl_buffer_size= 1048576000;
# 重新添加索引，查看执行时间：
alter table booking add index idx_2(flight_id,seat,passenger_id);10. Ugly Duckling
select
schema_name,exec_count,query_sample_text,
format_pico_time(total_latency)tot_lat,
format_pico_time(total_latency/exec_count) latency_per_call
from sys.x$statements_with_runtimes_in_95th_percentile as t1
join performance_schema.events_statements_summary_by_digest as t2
on t2.digest=t1.digest
where schema_name not in('performance_schema','sys')
order by(total_latency/exec_count) desc limit 111. InnoDB Setting
-
在专用MySQL服务器，最好让InnoDB决定BuferPool
和Redo Log Capacity，my.cnf配置如下：
innodb_dedicated_server=112. warmBuffer
-
生产服务器请使用warmBuffer Pool，
InnoDB缓冲池的内容Dump到Disk，并在启动时Load -
也可在发生crash时，每隔一段时间dump缓冲池，
然后清空以load旧dump，只需创建event：
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
create event automatic_bufferpool_dump
on schedule every 1 hour
do
set global_innodb_buffer_pool_dump_now=on;13. Disable AHI
-
大多工作负载不会从自适应哈希索引中收益，最好禁用，除非只执行select和
数据fully cached in buffer pool，否则AHI是巨大的性能瓶颈；
set persist_innodb_adaptive_hash_index = 0;15. Check Ratio
select
concat(format(b.num*100.0 / a.num,2),'%')DiskReadRatio
from(
select
variable_value num
from performance_schema.global_status
where variable_name ='Innodb_buffer_pool_read_requests')a,
(select variable_value num from performance_schema.global_status
where variable_name ='Innodb_buffer_pool_reads')b;