温故而知新墙

2024/5/12

禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

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

避免使用 JOIN 关联太多的表

阿里巴巴开发手册

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

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

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

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

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

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

避免使用子查询，可以把子查询优化为 join 操作

通常子查询在 in 子句中，且子查询中为简单 SQL(不包含 union、group by、order by、limit 从句) 时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因： 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询

拆分复杂的大 SQL 为多个小 SQL

大 SQL 逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL
MySQL 中，一个 SQL 只能使用一个 CPU 进行计算
SQL 拆分后可以通过并行执行来提高处理效率

建议不要使用外键和级联

优先选择符合存储需要的最小的数据类型

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

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

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

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

INET_ATON()：把 ip 转为无符号整型 (4-8 位)
INET_NTOA() :把整型的 ip 转为地址

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

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

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

1 2	SIGNED INT -2147483648~2147483647 UNSIGNED INT 0~4294967295

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

避免使用 TEXT,BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据

a. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中。

MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必

须使用磁盘临时表进行。而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差，但是不是说一定不能使用这样的数

据类型。

如果一定要使用，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时一定不要使用 select *而只需要取出必要的列，不需要

TEXT 列的数据时不要对该列进行查询。

2、TEXT 或 BLOB 类型只能使用前缀索引

因为 MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的

同财务相关的金额类数据必须使用 decimal 类型

非精准浮点：float,double
精准浮点：decimal

批量操作

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

#反例
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，我们首先要找到哪些SQL语句执行速度比较慢。

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

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

#开启慢查询日志功能
SET GLOBAL slow_query_1og = 'ON' ;
#慢查询日志存放位置
SET GLOBAL slow_query_log_file = '/var/1ib/mysq1/ranking-1ist-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;

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

1	SELECT `score , name~FROM cus_order` ORDER BY`score’DESC;

查看对应的慢查询日志:

1	cat /var/1ib/mysq1/ranking-list-s1ow . log

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

如何处理慢SQL？

优化索引：
根据EXPLAIN的输出，确定是否需要添加、修改或删除索引。索引可以显著提高查询性能，但也会增加插入、更新和删除的开销，因此需要谨慎使用。
优化查询：
修改查询以使用更有效的连接、过滤和排序条件。避免在查询中使用复杂的子查询或不必要的嵌套查询。
考虑表结构：
检查表的设计是否合理，是否可以进行优化。例如，你可以考虑将大表拆分为多个小表，或者使用归档策略来管理旧数据。
硬件和配置优化：
如果查询仍然很慢，可能需要考虑硬件升级或MySQL配置优化。例如，增加内存、使用更快的存储设备或调整MySQL的缓存设置。
使用查询缓存：
如果查询是重复的并且结果集不会经常变化，你可以考虑使用MySQL的查询缓存来缓存查询结果。但是请注意，查询缓存可能在高并发或频繁数据变更的场景下变得不太有效。

正确使用索引的一些建议

选择合适的字段创建索引

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

被频繁更新的字段应该慎重建立索引

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

限制每张表上的索引数量

索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能

尽可能的考虑建立联合索引而不是单列索引

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

联合索引

注意避免冗余索引

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

字符串类型的字段使用前缀索引代替普通索引

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

避免索引失效

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

使用 SELECT * 进行查询; SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
创建了组合索引，但查询条件未遵守最左匹配原则;
在索引列上进行计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询比如 LIKE '%abc';;
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
发生隐式转换open in new window;

删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

知道如何分析语句是否走索引查询

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 的输出格式如下：

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)

各个字段的含义如下：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

id

SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。

id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

SIMPLE：简单查询，不包含 UNION 或者子查询。
PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
SUBQUERY：子查询中的第一个 SELECT。
UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
UNION RESULT：UNION 查询的结果。

table

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

<unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
<derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
<subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type（重要）

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
ALL：全表扫描。

possible_keys

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要)

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
**Using join buffer (Block Nested Loop)**：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。