MySQL Performance Optimization, Architecture, and High‑Availability Guide

Performance bottlenecks such as ultra‑high QPS/TPS, connection saturation, disk I/O limits, and network bandwidth are identified, and their effects on MySQL servers are explained.

Large tables can cause slow queries, DDL locks, and replication lag; solutions include sharding, partitioning, and archiving historical data.

Large transactions reduce concurrency due to extensive locking; best practices recommend breaking transactions into smaller units and removing unnecessary SELECTs.

Key hardware and OS factors influencing performance are detailed: CPU frequency and core count, memory size, disk type (HDD vs SSD vs RAID), network bandwidth, and OS‑level parameters (e.g., sysctl settings, file‑descriptor limits, I/O scheduler choices).

Storage‑engine characteristics are compared, covering MyISAM, InnoDB, CSV, Archive, Memory, and Federated engines, with their advantages, limitations, and configuration tips.

Replication mechanisms are described, including statement‑based (SBR), row‑based (RBR), mixed formats, and GTID‑based replication, together with setup steps, IO/SQL thread configuration, and multi‑threaded slave options.

High‑availability topologies such as one‑master‑many‑slaves, master‑master, cascade replication, and tools like MMM and MHA are discussed, highlighting their deployment steps, pros/cons, and failover procedures.

Read/write splitting and load‑balancing techniques using middleware (MySQL‑proxy, MaxScale) or hardware (LVS, HAProxy) are presented.

Indexing strategies for B‑Tree and Hash indexes are explained, including when to use each type, limitations, and optimization tactics such as column ordering, prefix indexes, covering indexes, and index maintenance.

SQL query optimization workflow is outlined: capturing slow queries via slow_query_log, using tools like mysqldumpslow and pt‑query‑digest, analyzing execution plans, and applying specific fixes (batch inserts, online schema changes, replacing NOT IN/<> with joins, refreshing statistics).

Sharding (horizontal partitioning) guidelines cover shard key selection, mapping, and global ID generation.

Monitoring recommendations include checking service availability (ping, telnet), configuration flags ( read_only), connection limits, replication health ( SHOW SLAVE STATUS), and server resource metrics.

+ 未提交读
+ 已提交读（Mysql不是默认，SQLServer和Oracle是默认）
+ 可重复读（InnoDB默认）
+ 可串行化

--auto-generate-sql
--auto-generate-sql-add-autoincrement
--auto-generate-sql-load-type
--auto-generate-sql-write-number
--concurrency=10
--engine=InnoDB
--no-drop
--iterations=5
--number-of-queries=1000
--number-int-cols=2
--number-char-cols=1
--create-schema=mydb
--query="SELECT ..."
--only-print

install:
wget ...
unzip ...
cd ...
./autogen.sh
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
make && make install

net.core.somaxconn=65535
net.ipv4.tcp_max_syn_backlog=65535
kernel.shmmax=4294967295
vm.swappiness=0
* soft nofile 65535
* hard nofile 65535

innodb_log_file_size=256M
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_doublewrite=1

stop slave;
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave;