• SELECT * 会消耗更多的 CPU。
• SELECT * 无用字段增加网络带宽资源消耗，增加数据传输时间，尤其是大字段（如 varchar、blob、text）。
• SELECT * 无法使用 MySQL 优化器覆盖索引的优化（基于 MySQL 优化器的“覆盖索引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式）
• SELECT <字段列表> 可减少表结构变更带来的影响。

阿里巴巴《Java 开发手册》中有这样一段描述：

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致;多表关联查询时，保证被关联 的字段需要有索引。

join 的效率比较低，主要原因是因为其使用嵌套循环（Nested Loop）来实现关联查询，以前常见的实现效率都不是很高：

• Simple Nested-Loop Join ：直接使用笛卡尔积实现 join，逐行遍历/全表扫描，效率最低。
• Block Nested-Loop Join (BNL) ：利用 JOIN BUFFER 进行优化。注意：在 MySQL 8.0.20 及更高版本中，BNL 已被 Hash Join 取代，Hash Join 通常能将非索引列关联的复杂度从 O(M*N) 降低到接近 O(M+N)。
• Index Nested-Loop Join ：在必要的字段上增加索引，性能得到进一步提升。

实际业务场景避免多表 join 常见的做法有两种：

1. 单表查询后在内存中自己做关联 ：对数据库做单表查询，再根据查询结果进行二次查询，以此类推，最后再进行关联。
2. 数据冗余，把一些重要的数据在表中做冗余，尽可能地避免关联查询。很笨的一种做法，表结构比较稳定的情况下才会考虑这种做法。进行冗余设计之前，思考一下自己的表结构设计的是否有问题。

更加推荐第一种，这种在实际项目中的使用率比较高，除了性能不错之外，还有如下优势：

1. 拆分后的单表查询代码可复用性更高 ：join 联表 SQL 基本不太可能被复用。
2. 单表查询更利于后续的维护 ：不论是后续修改表结构还是进行分库分表，单表查询维护起来都更容易。

不过，如果系统要求的并发量不大的话，我觉得多表 join 也是没问题的。很多公司内部复杂的系统，要求的并发量不高，很多数据必须 join 5 张以上的表才能查出来。

深度分页问题的根本原因在于：当 LIMIT 的偏移量过大时，MySQL 需要扫描并跳过大量记录才能获取目标数据，查询优化器可能放弃索引而选择全表扫描。此时即使有索引，也无法避免大量的回表操作，导致查询性能急剧下降。

本文介绍了四种常见的深度分页优化方案，各方案的特点及适用场景对比如下：

方案选择建议：

• 优先使用延迟关联：对于大多数需要支持传统 LIMIT offset, size 翻页逻辑的场景，延迟关联是性能和可维护性较好的选择。
• 考虑范围查询（游标分页）：如果业务允许使用"下一页"式的游标翻页（如社交媒体 feed 流、无限滚动），范围查询性能最佳且稳定。
• 覆盖索引作为补充：当查询字段固定且数量不多时，可配合其他方案建立覆盖索引进一步优化。

注意事项：

• 无论采用哪种方案，都应注意监控实际执行计划（EXPLAIN），确保优化器按预期使用索引。
• 对于超深分页（如百万级偏移量），应从业务层面评估是否真的需要支持，考虑限制最大翻页数或采用其他检索方式（如搜索引擎）。

详细介绍可以阅读这篇文章：深度分页介绍及优化建议。

阿里巴巴《Java 开发手册》中有这样一段描述：

不得使用外键与级联，一切外键概念必须在应用层解决。

网络上已经有非常多分析外键与级联缺陷的文章了，个人认为不建议使用外键主要是因为对分库分表不友好，性能方面的影响其实是比较小的。

存储字节越小，占用也就空间越小，性能也越好。

a.某些字符串可以转换成数字类型存储比如可以将 IP 地址转换成整型数据。

数字是连续的，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

• INET_ATON() ： 把 IPv4 转为无符号整型（4 字节，32 位）。对于 IPv6，可使用 INET6_ATON() 转为 16 字节（128 位）的二进制字符串。
• INET_NTOA() :把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

b.对于非负型的数据 (如自增 ID,整型 IP，年龄) 来说,要优先使用无符号整型来存储。

无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647
UNSIGNED INT 0~4294967295

c.小数值类型（比如年龄、状态表示如 0/1）优先使用 TINYINT 类型。

d.对于日期类型来说， 一定不要用字符串存储日期。可以考虑 DATETIME、TIMESTAMP 和 数值型时间戳。

这三种种方式都有各自的优势，根据实际场景选择最合适的才是王道。下面再对这三种方式做一个简单的对比，以供大家实际开发中选择正确的存放时间的数据类型：

MySQL 时间类型选择的详细介绍请看这篇：MySQL 时间类型数据存储建议。

e.金额字段用 decimal，避免精度丢失。

decimal 用于存储有精度要求的小数比如与金钱相关的数据，可以避免浮点数带来的精度损失。

在 Java 中，MySQL 的 decimal 类型对应的是 Java 类 java.math.BigDecimal 。

BigDecimal的详细介绍请参考这篇：BigDecimal 详解。

f.尽量使用自增 id 作为主键。

如果主键为自增 id 的话，每次都会将数据加在 B+树尾部（本质是双向链表），时间复杂度为 O(1)。在写满一个数据页的时候，直接申请另一个新数据页接着写就可以了。

如果主键是非自增 id 的话，为了让新加入数据后 B+树的叶子节点还能保持有序，它就需要往叶子结点的中间找，查找过程的时间复杂度是 O(lgn)。如果这个也被写满的话，就需要进行页分裂。页分裂操作需要加悲观锁，性能非常低。

不过， 像分库分表这类场景就不建议使用自增 id 作为主键，应该使用分布式 ID 比如 uuid 。

相关阅读：数据库主键一定要自增吗？有哪些场景不建议自增？。

g.不建议使用 NULL 作为列默认值。

NULL 跟 ''(空字符串)是两个完全不一样的值，区别如下：

• NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
• ''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
• NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
• 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作，更耗时，更消耗 CPU 资源。

UNION ALL 不会再对结果集进行去重操作，获取到的数据包含重复的项。

不过，如果实际业务场景中不允许产生重复数据的话，还是可以使用 UNION。

对于数据库中的数据更新，如果能使用批量操作就要尽量使用，减少请求数据库的次数，提高性能。

# 反例
INSERT INTO `cus_order` (`id`, `score`, `name`) VALUES (1, 426547, 'user1');
INSERT INTO `cus_order` (`id`, `score`, `name`) VALUES (1, 33, 'user2');
INSERT INTO `cus_order` (`id`, `score`, `name`) VALUES (1, 293854, 'user3');

# 正例
INSERT into `cus_order` (`id`, `score`, `name`) values(1, 426547, 'user1'),(1, 33, 'user2'),(1, 293854, 'user3');

为了更精准定位一条 SQL 语句的性能问题，需要清楚地知道这条 SQL 语句运行时消耗了多少系统资源。 SHOW PROFILE 和 SHOW PROFILES 展示 SQL 语句的资源使用情况，展示的消息包括 CPU 的使用，CPU 上下文切换，IO 等待，内存使用等。

MySQL 在 5.0.37 版本之后才支持 Profiling，select @@have_profiling 命令返回 YES 表示该功能可以使用。

 mysql> SELECT @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set (0.00 sec)

注意 ：SHOW PROFILE 和 SHOW PROFILES 已经被弃用，未来的 MySQL 版本中可能会被删除，取而代之的是使用 Performance Schema。在该功能被删除之前，我们简单介绍一下其基本使用方法。

想要使用 Profiling，请确保你的 profiling 是开启（on）的状态。

你可以通过 SHOW VARIABLES 命令查看其状态：

也可以通过 SELECT @@profiling命令进行查看：

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

默认情况下， Profiling 是关闭（off）的状态，你直接通过SET @@profiling=1命令即可开启。

开启成功之后，我们执行几条 SQL 语句。执行完成之后，使用 SHOW PROFILES 可以展示当前 Session 下所有 SQL 语句的简要的信息包括 Query_ID（SQL 语句的 ID 编号） 和 Duration（耗时）。

具体能收集多少个 SQL，由参数 profiling_history_size 决定，默认值为 15，最大值为 100。如果设置为 0，等同于关闭 Profiling。

如果想要展示一个 SQL 语句的执行耗时细节，可以使用SHOW PROFILE 命令。

SHOW PROFILE 命令的具体用法如下：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

在执行SHOW PROFILE 命令时，可以加上类型子句，比如 CPU、IPC、MEMORY 等，查看具体某类资源的消耗情况：

SHOW PROFILE CPU,IPC FOR QUERY 8;

如果不加 FOR QUERY {n}子句，默认展示最新的一次 SQL 的执行情况，加了 FOR QUERY {n}，表示展示 Query_ID 为 n 的 SQL 的执行情况。

为了优化慢 SQL ，我们首先要找到哪些 SQL 语句执行速度比较慢。

MySQL 慢查询日志是用来记录 MySQL 在执行命令中，响应时间超过预设阈值的 SQL 语句。因此，通过分析慢查询日志我们就可以找出执行速度比较慢的 SQL 语句。

出于性能层面的考虑，慢查询日志功能默认是关闭的，你可以通过以下命令开启：

# 开启慢查询日志功能
SET GLOBAL slow_query_log = 'ON';
# 慢查询日志存放位置
SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log';
# 无论是否超时，未被索引的记录也会记录下来。
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 慢查询阈值（秒），SQL 执行超过这个阈值将被记录在日志中。
SET SESSION long_query_time = 1;
# 慢查询仅记录扫描行数大于此参数的 SQL
SET SESSION min_examined_row_limit = 100;

设置成功之后，使用 show variables like 'slow%'; 命令进行查看。

| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_launch_time    | 2                                    |
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/ranking-list-slow.log |
+---------------------+--------------------------------------+
3 rows in set (0.01 sec)

我们故意在百万数据量的表(未使用索引)中执行一条排序的语句：

SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;

确保自己有对应目录的访问权限：

chmod 755 /var/lib/mysql/

查看对应的慢查询日志：

 cat /var/lib/mysql/ranking-list-slow.log

我们刚刚故意执行的 SQL 语句已经被慢查询日志记录了下来：

# Time: 2022-10-09T08:55:37.486797Z
# User@Host: root[root] @  [172.17.0.1]  Id:    14
# Query_time: 0.978054  Lock_time: 0.000164 Rows_sent: 999999  Rows_examined: 1999998
SET timestamp=1665305736;
SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;

这里对日志中的一些信息进行说明：

• Time ：被日志记录的代码在服务器上的运行时间。
• User@Host：谁执行的这段代码。
• Query_time：这段代码运行时长。
• Lock_time：执行这段代码时，锁定了多久。
• Rows_sent：慢查询返回的记录。
• Rows_examined：慢查询扫描过的行数。

实际项目中，慢查询日志通常会比较复杂，我们需要借助一些工具对其进行分析。像 MySQL 内置的 mysqldumpslow 工具就可以把相同的 SQL 归为一类，并统计出归类项的执行次数和每次执行的耗时等一系列对应的情况。

找到了慢 SQL 之后，我们可以通过 EXPLAIN 命令分析对应的 SELECT 语句：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

比较重要的字段说明：

• select_type ：查询的类型，常用的取值有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
• table ：表示查询涉及的表或衍生表。
• type ：执行方式，判断查询是否高效的重要参考指标，结果值从差到好依次是：ALL < index < range ~ index_merge < ref < eq_ref < const < system。
• rows : SQL 要查找到结果集需要扫描读取的数据行数，原则上 rows 越少越好。
• ……

关于 Explain 的详细介绍，请看这篇文章：MySQL 执行计划分析。另外，再推荐一下阿里的这篇文章：慢 SQL 治理经验总结，总结的挺不错。

正确使用索引可以大大加快数据的检索速度（大大减少检索的数据量）。

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

SELECT * 查询（成本权衡）

• SELECT * 不会直接导致索引失效。如果 WHERE 条件符合索引规则，索引依然会被使用。
• 它会导致回表成本增加。如果查询需要的字段不在索引中（非覆盖索引），数据库需要拿着主键回聚簇索引查数据。当数据量较大时，优化器会对比“索引查找 + 回表”与“直接全表扫描”的成本，若前者成本过高，优化器会主动放弃索引选择全表扫描。
• SELECT * 还会网络传输和数据处理的浪费。尽量只查询需要的字段，利用覆盖索引减少回表。

违背最左前缀原则

• 最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。
• 最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。
• MySQL 8.0.13 版本引入了索引跳跃扫描（Index Skip Scan，简称 ISS），它可以在某些索引查询场景下提高查询效率。在没有 ISS 之前，不满足最左前缀匹配原则的联合索引查询中会执行全表扫描。而 ISS 允许 MySQL 在某些情况下避免全表扫描，即使查询条件不符合最左前缀。不过，这个功能比较鸡肋， 和 Oracle 中的没法比，MySQL 8.0.31 还报告了一个 bug：Bug #109145 Using index for skip scan cause incorrect result（后续版本已经修复）。个人建议知道有这个东西就好，不需要深究，实际项目也不一定能用上。

失效示例：

-- 索引：(sname, s_code, address)
WHERE s_code = 1;                  -- 跳过最左列 sname，失效
WHERE sname = 'A' AND address = 'Shanghai'; -- 跳过中间列 s_code，仅 sname 走索引
WHERE sname = 'A' AND s_code > 1 AND address = 'Shanghai'; -- 范围查询后，address 失效

在索引列上进行计算、函数或类型转换

• 索引存储的是字段的原始值。对字段进行操作后，数据库无法利用索引树的有序性，只能全表扫描后计算。
• MySQL 8.0 支持函数索引，可针对计算后的值建索引，但使用场景有限，首选还是优化 SQL 写法。

失效示例：

WHERE height + 1 = 170;            -- 对索引列进行计算
WHERE DATE(create_time) = '2022-01-01'; -- 对索引列使用函数

优化建议：

WHERE height = 169;                -- 将计算移到等号右边
WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59';

LIKE 模糊查询以通配符开头

• LIKE 查询必须以具体字符开头才能利用索引有序性，例如 WHERE sname LIKE 'Guide%'; 。
• 这是因为B+ 树是从左到右排序的。前缀通配符（%）破坏了有序性，无法定位起始点。

失效示例：

WHERE sname LIKE '%Guide';          -- 前缀模糊，全表扫描
WHERE sname LIKE '%Guide%';         -- 前后模糊，全表扫描

OR 连接条件使用不当

• 如果 OR 两边的列中有一列没有索引，通常会导致整个查询放弃索引，走全表扫描。
• 确保 OR 两边的列都建有索引，或改写为 UNION ALL。

失效示例：

-- 假设 sname 有索引，address 无索引
WHERE sname = '学生 1' OR address = '上海'; -- 索引失效，全表扫描

N / NOT IN 使用不当

• IN：当 IN 列表中的值太多（通常超过 200 个，由 eq_range_index_dive_limit 参数决定）或查询范围覆盖了太多行，会导致索引失效。
• NOT IN：在大多数情况下会引发全表扫描，因为它需要证明“不属于”某个集合，这在 B+ 树中通常需要遍历所有叶子节点。

失效示例：

WHERE s_code IN (1, 2, 3 ... 500); -- 列表过长可能失效
WHERE s_code NOT IN (1, 2, 3);     -- 通常失效

隐式类型转换

这是开发中最隐蔽的坑，转换的方向决定了索引的生死。

• 字段类型为字符串，查询条件未加引号（如 varchar 字段查 WHERE col = 123）；或字段类型为数字，查询条件加了引号且字符集不匹配。
• MySQL 会自动进行类型转换，导致索引列值发生变化，无法匹配索引树。
• 详细介绍：MySQL隐式转换造成索引失效 。

ORDER BY 排序优化陷阱

即使 WHERE 条件精准，如果 ORDER BY 处理不好，依然会出现慢查询。

• 如果查询走了索引 A，但排序要求字段 B，或者需要回表的数据量太大导致优化器放弃索引排序，就会触发 Using filesort（内存/磁盘排序）。
• 利用覆盖索引同时满足 WHERE 和 ORDER BY。例如索引为 (name, age)，查询 SELECT name, age FROM users WHERE name = 'A' ORDER BY age 是极其高效的。

最后，总结一个口诀

• 全值匹配我最爱，最左前缀不能改。
• 范围之后全失效，函数计算索引败。
• 模糊首位莫加百分号，类型转换要避开。
• OR 连接需谨慎，覆盖索引避回表。

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用

• MySQL 8.2 Optimizing SQL Statements：https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html
• 为什么阿里巴巴禁止数据库中做多表 join - Hollis：https://mp.weixin.qq.com/s/GSGVFkDLz1hZ1OjGndUjZg
• MySQL 的 COUNT 语句，竟然都能被面试官虐的这么惨 - Hollis：https://mp.weixin.qq.com/s/IOHvtel2KLNi-Ol4UBivbQ
• MySQL 性能优化神器 Explain 使用分析：https://segmentfault.com/a/1190000008131735
• 如何使用 MySQL 慢查询日志进行性能优化 ：https://kalacloud.com/blog/how-to-use-mysql-slow-query-log-profiling-mysqldumpslow/