MySQL性能优化，sql优化有哪些，数据库如何优化设计（二）

引言： 整个专栏包括以下几个部分：数据库优化、接口性能优化、Java底层数据性能调优、中间件相关性能问题定位 以及 多线程高并发设计 等内容。

下面开启第二篇文章：接着第一篇文章开始讲解：查看第一篇文章

一、数据库优化

1. SQL优化的15个方面

如果某个线上接口，出现了性能问题，需要做优化。那么首先想到的很有可能是优化 sql 语句，因为它的改造成本相对于代码来说也要小得多。

高效的分页

列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，一般会对查询接口做分页处理。

1、在 mysql 中分页一般用limit关键字：select id,name,age from user limit 10,20;

问题：如果表中数据量少，用 limit 关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。比如现在分页参数变成了：select id,name,age from user limit 1000000,20;

分析：mysql 会查到 1000020 条数据，然后丢弃前面的1000000 条，只查后面的 20 条数据，这个是非常浪费资源的。那么，这种海量数据该怎么分页呢？

优化 sql：

select id,name,age from user where id > 1000000 limit 20;

先找到上次分页最大的 id，然后利用 id 上的索引查询。不过该方案，要求 id 是连续的，并且有序的。

2、还能使用 between 优化分页

select id,name,age from user where id between 1000000 and 1000020;

需要注意的是 between 要在唯一索引上分页，不然会出现每页大小不一致的问题。

用连接查询代替子查询

mysql 中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询 和 连接查询。

1、子查询：

select * from order where user_id in (select id from user where status=1)

子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个 select 语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。

优点：简单，结构化，如果涉及的表数量不多的话。

缺点：执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。

2、连接查询：

select o.* from order o inner join user u on o.user_id = u.id where u.status=1

join 的表不宜过多

根据阿里巴巴开发者手册的规定，join 表的数量不应该超过 3 个。

不过有些ERP系统，并发量不大，但业务比较复杂，需要 join 十几张表才能查询出数据。所以 join 表的数量要根据系统的实际情况决定，不能一概而论，尽量越少越好。

1、为什么 join 过多会影响性能？

① MySQL 在选择索引的时候会非常复杂，很容易选错索引

如果 JOIN 过多，优化器需要在多个表之间选择最佳的查询执行计划（比如：执行顺序、索引使用等）。由于查询涉及多个表的关联，索引选择变得复杂，MySQL 可能选错索引，导致查询效率下降。

② Nested Loop Join 复杂度高

MySQL 默认使用 嵌套循环连接（Nested Loop Join） 来执行 JOIN，其原理是：

• 从驱动表（通常是 JOIN 语句中的第一个表）读取一条数据。
• 在被驱动表中查找匹配的数据。
• 重复上述步骤，直到遍历完整个驱动表。

例如：

SELECT a.name, b.name, c.name, d.name
FROM a
INNER JOIN b ON a.id = b.a_id
INNER JOIN c ON c.b_id = b.id
INNER JOIN d ON d.c_id = c.id;

如果 a、b、c、d 每个表有 10000 行：

• a 表读取 10000 行
• b 表匹配 a 的数据，最多也是 10000 行
• c 表匹配 b 的数据，最多 10000 行
• d 表匹配 c 的数据，最多 10000 行

如果 JOIN 过多，查询效率会大幅下降。

2、如何解决？

① 尽量控制 join 表的数量

② 适当的数据冗余

什么是数据冗余

1、数据冗余：指在数据库设计中，为了减少查询复杂度和 join 计算开销，将一些 频繁需要查询的数据复制存储在多个表中，而不是每次查询时通过 join 从多个表中获取。

优点：

• 减少 JOIN 表的数量，提高查询效率；
• 降低数据库查询压力，避免不必要的 JOIN；
• 提升系统的响应速度，适合高并发业务场景。

缺点：

• 数据同步问题：如果源数据发生变更，冗余字段也需要更新；
• 存储空间增加：相同的数据在多个表中存储，会占用额外的空间。

2、举例：

假设一个电商订单系统，数据库有如下三张表：

现在需要查询订单的用户名称、订单金额以及订单详情，则 sql 语句如下：

select o.order_id, u.user_name, o.total_price, oi.product_id, oi.quantity, oi.price
from orders o
inner join users u on o.user_id = u.user_id
inner join order_items oi on o.order_id = oi.order_id
where o.order_id = 1;

问题：

• 该查询涉及 3 张表 join，查询复杂度高；
• 订单表 orders 每次都需要 join users 表来获取 user_name，查询慢；
• 如果用户表 users 很大（例如1亿条数据），那么 join 计算成本就会增加。

优化方案： 在 orders 表中 冗余 user_name 字段，即：

alter table orders add column user_name varchar(50);

然后，在插入订单时，直接把 users 表的 user_name 复制过来：

insert into orders (order_id, user_id, user_name, total_price) 
values (1, 101, '张三', 500);

冗余后的 orders 表：

这样，我们只需要查询 orders 和 order_items，不需要 JOIN users 表：

select o.order_id, u.user_name, o.total_price, oi.product_id, oi.quantity, oi.price
from orders o
inner join order_items oi on o.order_id = oi.order_id
where o.order_id = 1;

3、数据冗余适应场景

• 适当冗余的使用场景：

1. 高并发查询场景：比如订单查询、商品信息查询等，需要减少 JOIN。
2. 数据变更频率低的字段：如用户名称、产品名称等，变更少，冗余不会增加太多同步成本。
3. 报表、统计系统：某些统计数据（如 total_price）可以冗余，避免实时计算。

• 不适合冗余的场景：

1. 高变更字段（如用户余额）：如果数据更新频繁，冗余数据的同步成本高，可能导致数据不一致。
2. 大字段（BLOB、TEXT）：占用存储空间大，影响查询性能。

join 时需注意

join 使用最多的是 left join 和 inner join

• left join：求两个表的交集 外加 左表剩下的数据
• inner join：求两个表交集的数据

1、如果两张表使用 inner join 关联，mysql 会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。

2、如果两张表使用 left join关联，mysql 会默认用左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。

所以：用 left join 关联查询时，左边要用小表，右边用大表。如果能用 inner join 的地方，尽量少用 left join。

选择合理的字段类型

1、char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间。

varchar表示可变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。

2、举例：

• 如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成 char 类型，长度是11字节。

• 但如果是企业名称字段，假如定义成 char 类型，就有问题了。

如果长度定义得太长，比如定义成了200字节，而实际企业长度只有50字节，则会浪费150字节的存储空间。如果长度定义得太短，比如定义成了50字节，但实际企业名称有100字节，就会存储不下，而抛出异常。

所以建议将企业名称改成 varchar 类型，变长字段存储空间小，可以节省存储空间。

3、我们在选择字段类型时，应该遵循这样的原则：

• 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
• 尽可能使用小的类型，比如：用 bit 存布尔值，用 tinyint 存枚举值等。
• 长度固定的字符串字段，用 char 类型；长度可变的字符串字段，用 varchar 类型。
• 金额字段用 decimal，避免精度丢失问题。

提升 group by 的效率

很多业务场景需要使用group by，它主要的功能是去重和分组。通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。

反例：

select user_id, user_name 
from order
group by user_id having user_id <= 200;

问题：这种写法性能不好，它先把所有的订单根据用户 id 分组之后，再去过滤用户 id 大于等于 200 的用户。

思考：分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？

正例：

select user_id,user_name from order
where user_id <= 200
group by user_id

使用 where 条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。

其实这是一种思路，不仅限于 group by 的优化。sql 语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升 sql 整体的性能。

控制索引的数量

1、索引能够显著的提升查询 sql 的性能，但索引数量并非越多越好。索引是需要额外的存储空间的，而且还会有一定的性能消耗。

2、阿里巴巴开发者手册中规定：单表的索引数量应尽量控制在5个以内，并且单个索引中的字段数不超过5个。

问题：如果表中的索引太多，超过了 5 个该怎么办？

思考：

• 这个问题要辩证的看，如果你的系统并发量不高，表中的数据量也不多，其实超过 5 个也可以，只要不要超过太多就行。

• 但对于一些高并发的系统，请务必遵守单表索引数量不要超过 5 个的限制。

再次思考：那么，高并发系统如何优化索引数量？

• 能够建联合索引，就别建单个索引，可以删除无用的单个索引。

• 将部分查询功能迁移到其他类型的数据库中，比如：Elastic Seach、HBase 等，在业务表中只需要建几个关键索引即可。

索引优化

1、sql 优化中，有一个非常重要的内容就是：索引优化。sql 语句走了索引和没有走索引，执行效率差别很大，所以索引优化被作为 sql 优化的首选。

2、索引优化第一步：检查 sql 语句有没有走索引，那如何检查？----> 使用 explain 命令，查看 mysql 的执行计划

举个例子：

explain select * from order where code='002';

结果：通过这几列可以判断索引使用情况：

执行计划包含所有列的含义：

后续会详细讲解 explain 的详细用法。

sql 语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了。下面说说索引失效的常见原因：

此外，可能遇到过这样的情况：明明是同一条 sql，只有入参不同而已。有的时候走的索引 a，有的时候却走的索引 b？----> 没错，有时候 mysql 会选错索引。必要时可以使用 force index 来强制查询 sql 走某个索引。

至于为什么 mysql 会选错索引，后面会讲解。