MySQL百万数据优化总结 一

测试的数据库配置

数据库配置阿里云RDS

存储类型

ESSD PL1 云盘

数据库内存

1024 M

数据库类型

MySQL8.0

CPU

1 核

MySQL8.0 中文参考手册：MySQL8.0中文版参考手册-MySQL文档类资源-CSDN下载

测试数据表：

CREATE TABLE `user` (
  `id` int NOT NULL,
  `name` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sex` varchar(2) NOT NULL,
  `age` tinyint NOT NULL,
  `email` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `phone` varchar(13) NOT NULL,
  `address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

加入500w测试数据，以下数据，均随机生成，如有雷同纯属巧合，生成工具类在这里面（http://t.csdn.cn/WNnNz）

---

优化成本：硬件>系统配置>数据库表结构>SQL及索引

优化效果：硬件<系统配置<数据库表结构<SQL及索引

SQL优化策略适用于数据量较大的场景下，如果数据较小没必要画蛇添足。一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。

优化遵守五个原则

1. 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
2. 返回更少的数据：只返回需要的字段和数据分页处理 减少磁盘IO及网络IO
3. 减少交互次数：批量DML操作，函数存储等减少数据连接次数
4. 减少服务器CPU开销：尽量减少数据排序操作及其全表查询，减少CPU 内存占用
5. 利用更多资源：使用表分区，可以增加并行操作，更大限度利用CPU资源

总结到SQL优化中的三点：1.最大化利用索引；2.尽可能避免全表扫描；3.减少无效数据的查询；

---

SQL执行顺序

理解SQL优化原理，首先搞清楚SQL执行顺序：（从上往下看）

FROM<表名>

选取表，将多个表数据通过笛卡尔积变成一个表。

ON<筛选条件>

对笛卡尔积的虚表进行筛选

JOIN <join、left join 、right join…>

<join表>

指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中

WHERE<where条件>

对上述虚表进行筛选

GROUP BY<分组条件>

分组

<sum()等聚合函数>

用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的

HAVING<分组筛选>

对分组后的结果进行聚合筛选

SELECT<返回数据列表>

返回的单列必须在group by子句中，聚合函数除外

DISTINCT

数据除重

ORDER BY<排序条件>

排序

LIMIT<行数限制>

---

查询的过程

1. 客户端向 MySQL 服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
4. MySQL 根据执行计划，调用存储引擎的 API 来执行查询
5. 将结果返回给客户端，同时缓存查询结果
6. 注意：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了
7. 

---

EXPLAIN 分析SQL的执行计划

explain的字段：id、select_type、table、type、possible_keys、key、key_len、ref、rows、fitered、extra ；其中最重要的字段为：id、type、key、rows、Extra

启动执行计划：

explain?select?投影列?FROM?表名?WHERE?条件

每个字段介绍：

**ID：**查询执行的顺序：ID值相同时表示从上向下执行，并且被视为一组；如果是子查询，ID值会递增，ID值越高，优先级越高；ID为NULL 为最后执行

select_type：

**1.simple：**表示查询中不包含子查询或者 union

**2.primary：**当查询中包含任何复杂的子部分，最外层的查询被标记成 primary

3.derived**：**在 from 的列表中包含的子查询被标记成 derived

**4.subquery：**在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery

• • • 举例：1.set session optimizer_switch=‘derived_merge=off’; #关闭mysql5.7新特性对衍生表的合并优化
    • 2.explain select (select 1 from user where id = 1) from (select * from user where id = 1) user

• • • set session optimizer_switch=‘derived_merge=on’; #还原默认配置

**5.union：**两个 select 查询时前一个标记为 PRIMARY，后一个标记为 UNION。union 出现在 from 从句子查询中，外层 select 标记为 PIRMARY，union 中第一个查询为 DERIVED，第二个子查询标记为 UNION

explain select 1 union all select 1;

**6.unionresult：**从 union 表获取结果的 select 被标记成 union result 。

**table：**显示这一行的数据是关于哪张表的。当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为，1和2