# 优化Linux内核参数
$ vim /etc/sysctl.conf

# 添加以下参数
# 最大文件句柄数
fs.file-max = 6815744

# 网络参数
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 15

# 内存参数
vm.swappiness = 10
vm.overcommit_memory = 1

# 应用参数
kernel.sem = 250 32000 100 128
kernel.shmmax = 68719476736
kernel.shmall = 16777216

# 生效参数
$ sysctl -p

# 优化系统资源限制
$ vim /etc/security/limits.conf

# 添加以下参数
* soft nofile 65536
* hard nofile 65536
* soft nproc 65536
* hard nproc 65536

# 检查磁盘IO性能
$ iostat -x 1

# 优化磁盘调度算法
$ echo deadline > /sys/block/sda/queue/scheduler

# 禁用磁盘写缓存
$ hdparm -W 0 /dev/sda

# 优化文件系统
$ mkfs.ext4 -O dir_index,extent,huge_file,flex_bg,uninit_bg,dir_nlink,extra_isize /dev/sda1

# 挂载选项优化
$ vim /etc/fstab

# 添加以下挂载选项
UUID=xxx /polardb/fgdata ext4 defaults,noatime,nodiratime,barrier=0,data=writeback 0 0

# 优化MySQL参数
$ vim /etc/my.cnf

# 添加以下参数
[mysqld]
# 基本参数
user = mysql
datadir = /polardb/fgdata
socket = /var/lib/mysql/mysql.sock
port = 3306

# 性能参数
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_max_dirty_pages_pct = 75
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_thread_concurrency = 0

# 连接参数
max_connections = 2000
max_connect_errors = 10000
wait_timeout = 300
interactive_timeout = 300

# 查询参数
query_cache_type = 0
query_cache_size = 0
table_open_cache = 4096
table_definition_cache = 4096
max_heap_table_size = 64M
tmp_table_size = 64M

# 日志参数
binlog_format = ROW
sync_binlog = 1

# 重启MySQL
$ systemctl restart mysqld

# 查看表结构
mysql> SHOW CREATE TABLE fgedudb.fgedu_user;

# 创建索引
mysql> CREATE INDEX idx_name ON fgedudb.fgedu_user(name);
Query OK, 0 rows affected (0.01 sec)

# 查看索引
mysql> SHOW INDEX FROM fgedudb.fgedu_user;

# 分析索引使用情况
mysql> EXPLAIN SELECT * FROM fgedudb.fgedu_user WHERE name = ‘test1’;

# 优化索引
# 删除无效索引
mysql> DROP INDEX idx_name ON fgedudb.fgedu_user;
Query OK, 0 rows affected (0.01 sec)

# 创建复合索引
mysql> CREATE INDEX idx_name_age ON fgedudb.fgedu_user(name, age);
Query OK, 0 rows affected (0.01 sec)

# 查看表结构
mysql> SHOW CREATE TABLE fgedudb.fgedu_user;

# 优化表结构
mysql> ALTER TABLE fgedudb.fgedu_user MODIFY COLUMN name VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.01 sec)

# 分区表
mysql> ALTER TABLE fgedudb.fgedu_user PARTITION BY RANGE (id) (
-> PARTITION p0 VALUES LESS THAN (1000),
-> PARTITION p1 VALUES LESS THAN (2000),
-> PARTITION p2 VALUES LESS THAN (3000),
-> PARTITION p3 VALUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.01 sec)

# 分表
# 创建分表
mysql> CREATE TABLE fgedudb.fgedu_user_1 LIKE fgedudb.fgedu_user;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE fgedudb.fgedu_user_2 LIKE fgedudb.fgedu_user;
Query OK, 0 rows affected (0.01 sec)

# 查看慢查询日志
mysql> SHOW VARIABLES LIKE ‘slow_query_log%’;

# 启用慢查询日志
mysql> SET GLOBAL slow_query_log = ‘ON’;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL slow_query_log_file = ‘/polardb/fgdata/slow.log’;
Query OK, 0 rows affected (0.01 sec)

mysql> SET GLOBAL long_query_time = 1;
Query OK, 0 rows affected (0.01 sec)

# 分析慢查询
$ mysqldumpslow -s t /polardb/fgdata/slow.log

# 优化SQL语句
# 原SQL
mysql> SELECT * FROM fgedudb.fgedu_user WHERE age > 20;

# 优化后SQL
mysql> SELECT id, name, age, email FROM fgedudb.fgedu_user WHERE age > 20;

# 避免使用SELECT *
# 原SQL
mysql> SELECT * FROM fgedudb.fgedu_user;

# 优化后SQL
mysql> SELECT id, name, age, email FROM fgedudb.fgedu_user;

# 避免使用不必要的JOIN
# 原SQL
mysql> SELECT * FROM fgedudb.fgedu_user u JOIN fgedudb.fgedu_order o ON u.id = o.user_id;

# 优化后SQL
mysql> SELECT u.id, u.name, o.order_id, o.amount FROM fgedudb.fgedu_user u JOIN fgedudb.fgedu_order o ON u.id = o.user_id;

# 创建存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE fgedudb.get_user(IN user_id INT)
BEGIN
SELECT id, name, age, email FROM fgedudb.fgedu_user WHERE id = user_id;
END //
mysql> DELIMITER ;

# 优化存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE fgedudb.get_user_optimized(IN user_id INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_id INT;
DECLARE v_name VARCHAR(50);
DECLARE v_age INT;
DECLARE v_email VARCHAR(100);
DECLARE cur CURSOR FOR SELECT id, name, age, email FROM fgedudb.fgedu_user WHERE id = user_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop:
LOOP
FETCH cur INTO v_id, v_name, v_age, v_email;
IF done THEN
LEAVE read_loop;
END IF;
SELECT v_id, v_name, v_age, v_email;
END LOOP;
CLOSE cur;
END //
mysql> DELIMITER ;