MySQL优化

调优之前的考虑

在准备MySQL数据库优化前，一定要注意，系统瓶颈来自于哪部分，因为sql的优化带来的收益，可能远远比不上在架构部分的优化，架构层面的优化带来的效果会比在数据库的优化来的高，在足够了解系统后，决定在数据库层面优化后，可参考优化的部分。

架构层面的建议

可参考

• 分库分表
• 分布式集群
• 读写分离
• 业务拆分
• 分级缓存

MySQL优化

MySQL版本>=5.7，Innodb存储引擎之上
在单台MySQL服务中，读写能力IOPS的上限基本固定，不同配置的机器上表现不同，sql调优是在该基础上，趋近于机器性能的上限。

• SQL语句的优化
• 索引优化
• 硬件和OS调优

索引优化

三星索引系统原则：索引设计初步往第三星靠拢
一星：索引将相关的记录放在一起。即在一系列必要的列上建立索引，不必为where条件里的所有得列建立索引。
二星：索引中数据的顺序和排序要求的数据的顺序一致。通常将选择性更高的列放在索引列的最前面。
三星：索引中的列包含查询所需要的所有列。因为索引中已经包含查询所需的全部字段
我们列举一张数据表展示，建表语句以及插入数据

# 建表语句
CREATE TABLE `employees` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`) -- 主键索引,
 KEY `idx_age` (`age`) USING BTREE -- 针对age字段的索引
 KEY `idx_position` (`position`) USING BTREE -- 针对position字段的索引
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE -- 联合索引
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

## 插入一些示例数据
DROP PROCEDURE IF EXISTS insert_emp;
DELIMITER ;;
CREATE PROCEDURE insert_emp()
BEGIN
  DECLARE i INT;
  DECLARE rand_age INT;
  DECLARE rand_position VARCHAR(7);
  SET i = 1;
  WHILE (i <= 100000) DO
    SET rand_age = FLOOR(RAND() * (60 - 10 + 1)) + 10; -- 生成随机年龄，范围在10到60之间
    SET rand_position = CASE FLOOR(RAND() * 2)
                          WHEN 0 THEN 'dev'
                          ELSE 'manager'
                        END; -- 随机选择职位为 'dev' 或 'manager'
    INSERT INTO employees (name, age, position) VALUES (CONCAT('test', i), rand_age, rand_position);
    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;
CALL insert_emp();

• 让我们来仔细看看索引包含的信息

SHOW INDEX FROM employees;

Cardinality列为该索引的基数,反映了索引列的离散性
索引优化部分，分2种情况

• 一种是在索引失效的情况下

有时候自己设置了索引，但是再实际查询的时候还是非常慢，那么可以使用EXPLAIN语句查询

EXPLAIN SELECT * FROM employees WHERE name > 'test2' AND age > 22 AND position ='dev';

• 一种是在索引生效的情况下，尽量优化走的索引树

Innodb引擎层优化

• buffer poll
• 隔离级别

硬件调优

磁盘调优

磁盘调度算法 （noop，deadline，cfq，mq-deadline，kyber）
os文件系统

启用磁盘阵列

分散磁盘IO

裸磁盘设备

ps：每日更新