一.EXPLAIN 关键字

1.explain 使用?

EXPLAIN是 MySQL 中用于分析查询执行计划的关键字。它不会执行实际的查询，而是返回一个描述查询执行计划的结果集，帮助你理解 MySQL 是如何执行你的查询的，以便进行性能优化。以下是如何使用EXPLAIN：

基本语法：

EXPLAIN SELECT * FROM users WHERE age > 25;

你需要将你要分析的查询放在EXPLAIN后面。

查看执行计划：

执行上述EXPLAIN查询后，MySQL 将返回一个结果集，其中包含了关于查询执行计划的信息。这个结果集包括多列，常见的列包括：

• id：查询操作的标识符，通常按顺序分配。
• select_type：查询的类型，如简单查询、联接查询等。
• table：查询涉及的表。
• type：访问表的方式，如全表扫描、索引扫描等。
• possible_keys：可能用于查询的索引列表。
• key：实际用于查询的索引。
• key_len：索引中使用的字节数。
• rows：估计扫描的行数。
• Extra：附加信息，如使用临时表、使用文件排序等。

分析执行计划：

通过查看EXPLAIN的输出，你可以了解查询的执行过程和优化情况。例如，你可以检查是否正确使用了索引，是否发生了全表扫描，以及估计的行数是否与预期相符。这些信息有助于你识别和解决性能问题。

优化查询：

根据EXPLAIN的输出，你可以调整查询语句、添加索引、重写查询，以改进性能。你的目标是使查询执行计划更高效，减少不必要的资源消耗。可以使用 force index 强制⾛正确的索引。

请注意，EXPLAIN只是一个分析工具，不会实际执行查询，因此不会影响数据库中的数据。它是优化查询性能的有用工具，特别是在处理复杂查询或大型数据集时。

2.explain 各字段含义

Explain 执行计划中各个字段的含义:

字段	含义
id	select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序(id 相同，执行顺序从上到下；id 不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE 之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、 index、all 。不一定是多表才显示，单表也显示.
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为 NULL，则没有使用索引。
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
rows	MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，可能并不总是准确的。
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
Extra	额外信息

3.详细说一下 id 字段

id 详解规则

• id 相同，执行顺序从上到下
• id 值越大执行优先级越高

在 MySQL 中，当你执行EXPLAIN语句来分析查询的执行计划时，其中的id字段表示查询中的每个操作步骤的标识符，通常以数字表示。这个字段用于帮助你理解查询的执行顺序和关系，以及优化查询性能。

以下是EXPLAIN语句的输出中id字段的一些常见取值和它们的含义：

1. 简单查询：

   • id为 1 表示查询中只有一个操作步骤，通常是一个简单的SELECT查询。

2. 复杂查询：

   • id值大于 1 表示查询中有多个操作步骤，这些操作步骤按照从上到下的顺序执行。
   • id值较小的操作通常在执行时先执行，然后将结果传递给具有较大id值的操作。

3. 子查询：

   • 如果查询包含子查询，子查询的id值通常大于包含它的查询的id值。
   • 子查询的结果会传递给父查询来执行进一步的操作。

4. 联接操作：

   • 当查询涉及到表的联接时，id字段可以帮助你理解联接的执行顺序。
   • 典型的联接顺序是从左到右，即从第一个表到最后一个表。

5. 派生表：

   • 派生表（Derived Table）是一种在查询中生成临时结果集的方式。这些派生表通常具有较大的id值，因为它们在查询执行的后期阶段生成。

6. 子查询与主查询：

   • 如果查询中包含子查询，主查询通常具有较小的id值，而子查询具有较大的id值。主查询的结果用于驱动子查询。

通过观察id字段，你可以更好地理解 MySQL 优化器是如何执行你的查询的，以便进行性能调整和优化。你可以进一步分析EXPLAIN输出的其他列，如type、table、key、rows等，以深入了解查询执行计划的细节。

4.select_type 详解

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

• SIMPLE:简单的 select 查询，查询中不包含子查询或 union 查询。
• PRIMARY:查询中若包含任何复杂的子部分，最外层查询为 PRIMARY，也就是最后加载的就是 PRIMARY。
• SUBQUERY:在 select 或 where 列表中包含了子查询，就为被标记为 SUBQUERY。
• DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外面的查询
• DERIVED:在 from 列表中包含的子查询会被标记为 DERIVED(衍生)，MySQL 会递归执行这些子查询，将结果放在临时表中。
• UNION:若第二个 select 出现在 union 后，则被标记为 UNION，若 union 包含在 from 子句的子查询中，外层 select 将被标记为 DERIVED。
• DEPENDENT UNION：UNION 中的第二个或后面的 SELECT 语句，取决于外面的查询
• UNION RESULT:从 union 表获取结果的 select。

5.type 详解

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• ALL（全表扫描）；
• index（全索引扫描）；
• range（索引范围扫描）；
• ref（非唯一索引扫描）；
• eq_ref（唯一索引扫描）；
• const（结果只有一条的主键或唯一索引扫描）;
• system (系统表);

在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

6.Extra 字段详解

Extra：该列包含 MySQL 解决查询的详细信息。

• Using filesort：MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。效率低，要避免这种问题的出现。
• Using temporary：为了解决查询，MySQL 需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的 GROUP BY 和 ORDER BY 子句时。效率低，要避免这种问题的出现。
• Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。使用了覆盖索引，避免了回表操作，效率不错。
• Distinct：MySQL 发现第 1 个匹配行后，停止为当前的行组合搜索更多的行。
• Not exists：MySQL 能够对查询进行 LEFT JOIN 优化，发现 1 个匹配 LEFT JOIN 标准的行后，不再为前面的的行组合在该表内检查更多的行。
• range checked for each record (index map: #)：MySQL 没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL 检查是否可以使用 range 或 index_merge 访问方法来索取行。
• Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果 Extra 值不为 Using where 并且表联接类型为 ALL 或 index，查询可能会有一些错误。
• Using sort_union(...)， Using union(...)， Using intersect(...)：这些函数说明如何为 index_merge 联接类型合并索引扫描。
• Using index for group-by：类似于访问表的 Using index 方式，Using index for group-by 表示 MySQL 发现了一个索引，可以用来查询 GROUP BY 或 DISTINCT 查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

7.rows 为什么是个近似值?

MySQL 中数据的单位都是页,MySQL 又采用了采样统计的方法，采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

我们数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。

在 MySQL 执行EXPLAIN查询计划时，rows列中的值通常是一个估算值，而不是准确的行数。这是因为 MySQL 的查询优化器在生成查询计划时需要做出一些估算，以便快速决定最优的执行计划。以下是一些导致rows是估算值的原因：

1. 统计信息不准确：MySQL 使用表的统计信息来估算查询的行数。这些统计信息包括表的行数、索引的唯一值数量、数据分布等。然而，这些统计信息并不总是完全准确，特别是在表中有大量更新、插入或删除操作时。因此，查询优化器只能依赖于已有的统计信息来估算行数。

2. 动态数据：表中的数据通常是动态变化的，这意味着查询优化器无法在执行EXPLAIN时准确知道查询时刻的实际行数。因此，它必须依赖于历史统计信息和启发式规则来估算。

3. 查询条件的复杂性：查询可能包含复杂的条件、连接、子查询等，这使得准确估算行数更加困难。MySQL 的优化器在处理这些复杂情况时通常会采用一些近似和启发式方法。

虽然rows列中的值是估算的，但它通常对于查询性能优化仍然非常有用。查询优化器的目标是选择一个执行计划，使查询尽可能快速地执行。通过估算行数，优化器可以评估不同执行计划的成本，并选择最有可能最优的计划。虽然rows值不一定是准确的，但它可以帮助 DBA 和开发人员识别潜在的性能问题并进行优化。

如果你需要更准确的行数估算，可以考虑使用ANALYZE TABLE命令来更新表的统计信息，或者使用专门的性能分析工具来获取更详细的查询性能信息。

8.possible_keys 和 key

在 MySQL 数据库中，当执行查询时，可能会看到两个重要的性能参数：possible_keys（可能的索引键）和 key（使用的索引键）。这些参数涉及到数据库查询优化和性能调优。

possible_keys（可能的索引键）：

• possible_keys 是一个估计的字段，它列出了在查询中可能被用于加速检索的索引键的列表。
• 这个字段列出了数据库查询优化器认为可以用于查询的索引，但不一定会真正使用。查询优化器会根据查询的条件、表的结构和统计信息来决定哪些索引可能会提供更好的性能。
• possible_keys 字段的目的是帮助开发人员识别是否存在合适的索引，以便在需要时优化查询性能。

key（使用的索引键）：

• key 是实际用于执行查询的索引键。它指示数据库实际上选择了哪个索引来加速查询。
• 当查询优化器决定使用了哪个索引后，key 字段会显示相应的索引名称，以及在查询中被选中的索引。
• 通常，你希望在 key 字段中看到一个合适的索引名称，因为这意味着查询正在有效地使用索引来提高性能。

通常，会希望看到 possible_keys 中列出了可能的索引键，并且 key 中指示了实际使用的索引键。如果查询性能不理想，可以考虑创建新索引或优化现有索引以改善查询性能。

二.性能调优

1.单表优化经验?

• 最佳左前缀法则：带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死。

• 尽量选择区分度⾼的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不

  重复的⽐率，⽐率越⼤我们扫描的记录数就越少。

• like 百分加右边，加左边导致索引失效，解决方法：使用覆盖索引。

• 尽量使用覆盖索引，减少 select *

• 使用枚举或整数代替字符串类型

• VARCHAR 的长度只分配真正需要的空间

• 字符串不加单引号导致索引失效

• 避免使用 NULL字段，很难查询优化且占用额外索引空间,可以给 NULL 设置默认值

• 用整型来存 IP

• 不做列运算 SELECT id WHERE age+1=10，任何对列的操作都将导致表扫描

• 对于连续数值，使用 BETWEEN 不用 IN:SELECT id FROM t WHERE nUm BETWEEN 1 AND 5

• 列表数据不要拿全表，要使用 LIMIT 来分页，每页数量也不要太大

• OR 改写成 IN:OR 的效率是 n 级别，IN 的效率是 log(N)级别 in 的个数建议控制在 200 以内

• 尽量使用 TINYINT、SMALLINT、MEDIUM INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED

2.SQL 优化流程

1. 预发跑 sql explain
2. 看一下行数对不对 不对可以用 analyze table t 矫正
3. 添加索引 索引不一定是最优的 force index 强制走索引 不建议用 关注 type 类型
4. 覆盖索引避免回表，不要*
5. 最左前缀原则 按照索引定义的字段顺序写 sql
6. 合理安排联合索引的顺序
7. 5.6 之后 索引下推 减少回表次数
8. 索引字段不要做函数操作，会破坏索引值的有序性，优化器会放弃走树结构
9. 如果触发隐式转换 那也会走 cast 函数 会放弃走索引
10. 可通过开启慢查询日志来找出较慢的 SQL

3.什么时候加索引，什么时候不加索引？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

• 需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
• 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

所以，索引不是万能钥匙，它也是根据场景来使用的。

什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree 的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

3.Mysql 在代码层的优化?

在业务代码中进行 MySQL 优化是提高数据库性能和响应时间的关键步骤之一。以下是一些您可以在业务代码中实施的 MySQL 优化方案：

1. 合理的查询设计：

   • 使用适当的索引：确保数据库表上的关键字段有合适的索引，以加快查询速度。
   • 避免全表扫描：确保查询语句能够充分利用索引，以避免全表扫描和性能问题。

2. 批量操作：

   • 批量插入、更新和删除：在需要插入、更新或删除多行数据时，使用批量操作，减少单个操作的开销。
   • 批量提交事务：将多个数据库操作放在一个事务中，然后进行批量提交，以减少事务的开销。

3. 分页查询优化：

   • 使用分页查询时，避免跳过大量行，可以根据分页条件和索引进行优化。
   • 考虑使用游标（Cursor）来处理大量数据的分页查询，而不是一次性取出全部数据。

4. 减少网络开销：

   • 限制返回数据量：只选择所需的字段，避免不必要的数据传输。
   • 避免重复查询：在一个请求内对同一数据进行多次查询，可以考虑合并为一个查询。

5. 事务管理：

   • 控制事务的范围：尽量缩小事务的范围，以减少锁定的时间和冲突。
   • 避免长事务：长时间的事务可能导致锁定问题和资源争用，需要谨慎处理。

6. 缓存：

   • 缓存常用数据：使用缓存存储频繁访问的数据，减少对数据库的查询压力。

7. 连接管理：

   • 使用连接池：确保使用连接池来管理数据库连接，避免频繁地创建和关闭连接。

8. 异步处理：

   • 将一些不需要立即完成的操作异步处理，以减少前端请求的等待时间。

9. 避免 N+1 查询问题：

   • 在一对多关系中，避免在循环中执行多次查询，可以使用 JOIN 或者批量查询。

10. 数据库分片：

    • 对于大规模应用，考虑数据库分片以分散负载，但需要在业务代码中处理分片逻辑。

11. 使用 Explain 分析：

    • 使用 MySQL 的EXPLAIN语句来分析查询的执行计划，以识别潜在的性能问题。

12. 定期维护：

    • 定期清理无用数据、优化表结构、重新建立索引，以保持数据库的良好性能。

MySQL 优化是一个综合性的工作，需要根据具体的业务情况和性能问题来确定优化策略。持续的监控和性能测试可以帮助您了解优化的效果，并做出必要的调整。

4.深分页和浅分页

例如您的订单表数据量达到一定程度，例如 50 万条以上时，如果还是使用常规分页查询（例如：limit 470000，10 这样的查询）（一般页面端的分页控件有最后一页，如果点击这个按钮就会重现），查询速度会非常慢，会长达几秒或十几秒才能返回结果，这就是深分页问题。

MySQL 在执行 limit n，m 时，工作原理就是先读取前面 n 条记录，然后抛弃前 n 条，读后面 m 条想要的，所以 n 越大，偏移量越大，性能就越差。

5.带排序的分页查询优化

1. 浅分页可以给 order by 字段添加索引
2. 深分页可以给 order by 和 select 字段添加联合索引
3. 可以通过手动回表，强制去走索引
4. 从业务方着手，去限制他的分页查询或者修改前后端交互（将每页最后一条数据的 id 和分数传递过来）

分页查询

SELECT id FROM t LIMIT 10000， 10;

改为:

SELECT id FROM t WHERE id > 10000 LIMIT 10;

6.表分区优化?

mysql 只支持水平分区，不支持垂直分区，当前 MySQL 数据库支持以下几种类型的分区。

分区的类型:

• RANGE 分区:基于属于一个给定连续区间的列值，把多行分配给分区
• LIST 分区:类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择
• HASH 分区:基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式
• KEY 分区:类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分.如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。分区中对 NULL 值的处理为，放在最左边，相当于最小。

7.使用函数一定影响性能?

EXPLAIN
SELECT MAX(sales)
FROM sales_amount_02;

#Extra字段显示为Select tables optimized away

表示 innodb 已经在系统表中把 max 存储起来了，不用走索引再查，所以使用函数不一定对性能有坏的影响。

8.count(*)和 count(1)性能?

count(\*)和 count(1)比较：

• myisam 中有表直接存储，汇总 count 的值会存储起来。
• 官方文档，在 innodb 存储引擎中，count(1)与 count(*)性能是一样的
• innodb: count(*) = count(1) > count(主键) > count(col)
• myisam: count(*) >= count(1) > count(col)

9.小表驱动大表?

类似循环嵌套，小的在外层，大的在内层。如果小的循环在外层，对于数据库连接来说就只连接 5 次，进行 5000 次操作，如果 1000 在外，则需要进行 1000 次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

in 和 exists 的原理也是基于小表驱动大表。优化原则：小表驱动大表，即小的数据集驱动大的数据集。

#当B表的数据集必须小于A表的数据集时，用in优于exists。
select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id = B.id

#当A表的数据集系小于B表的数据集时，用exists优于in。
select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

10.Show Profile

Show Profile 是 mysql 提供的可以用来分析当前会话中 sql 语句执行的资源消耗情况的工具，可用于 sql 调优的测量。默认情况下处于关闭状态，并保存最近 15 次的运行结果。

开启 Show Profile 功能，默认该功能是关闭的，使用前需开启。

select *from tb_emp_bigdata group by id%10 limit 150000;
select *from tb_emp_bigdata group by id%20 order by 5;

-- 使用show profile对sql语句进行诊断。
-- Query_ID为#3步骤中show profiles列表中的Query_ID
show profile cpu，block io for query Query_ID;

show profile 的常用查询参数。

• ①ALL：显示所有的开销信息。
• ②BLOCK IO：显示块 IO 开销。
• ③CONTEXT SWITCHES：上下文切换开销。
• ④CPU：显示 CPU 开销信息。
• ⑤IPC：显示发送和接收开销信息。
• ⑥MEMORY：显示内存开销信息。
• ⑦PAGE FAULTS：显示页面错误开销信息。
• ⑧SOURCE：显示和 Source_function，Source_file，Source_line 相关的开销信息。
• ⑨SWAPS：显示交换次数开销信息。

日常开发需注意的结论。

• ①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
• ②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
• ③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
• ④locked。

如果在 show profile 诊断结果中出现了以上 4 条结果中的任何一条，则 sql 语句需要优化。

11.什么是 idel conn?

在 MySQL 中，"idel conn"是指空闲连接（idle connections），也被称为闲置连接或空闲会话。这是指已经建立的与 MySQL 数据库服务器的连接，但目前没有进行任何数据库操作或查询的连接。

当应用程序与 MySQL 数据库建立连接后，连接会一直保持打开状态，即使在一段时间内没有进行任何数据库操作。这种情况下，连接就会被认为是空闲连接。

空闲连接可能会对数据库服务器的性能产生负面影响，因为每个连接都会占用一定的资源，包括内存和处理器资源。如果有大量的空闲连接积累，它们将占用宝贵的资源，并可能导致数据库性能下降。

为了管理空闲连接，通常会采取以下措施：

1. 连接池管理：使用连接池来管理数据库连接，连接池可以在需要时分配连接，并在连接不再使用时将其放回池中。这样可以避免频繁地打开和关闭连接，提高性能并减少空闲连接的数量。
2. 超时设置：通过设置连接的超时时间，可以确保空闲连接在一段时间内没有被使用后自动关闭。这样可以释放资源并减少空闲连接的数量。
3. 监控和优化：定期监控数据库服务器的连接数和性能指标，识别并优化可能导致空闲连接过多的瓶颈或性能问题。

通过合理管理和优化空闲连接，可以提高数据库服务器的性能和资源利用率，并确保连接数与实际需求相匹配，从而提供更好的数据库服务。

12.索引负优化

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 2

在MySQL中LIMIT与ORDER BY是特殊的组合，尤其是当ORDER BY中的存在BTREE索引的情况下。

普通的查询是根据条件进行筛选，然后在结果集中排序，然后获取LIMIT条数的数据，但是在具备上述条件的特殊 sql 中执行逻辑是这样的，根据ORDER BY字段的 B+树索引来查找满足条件的数据，直到凑满LIMIT设定的数值为止，这就存在一个问题，在结果集中的数据大于LIMIT的场景下，这个性能固然是非常棒的，但是如果最后的结果集中的数据小于LIMIT，就会存在永远凑不满的情况，所以最终这个MySQL的性能优化就会变成全表扫描的“负优化”。

13.内存低但 cpu 高的原因?

内存使用比较低，cpu使用比较高:

MySQL 产生较高 CPU 负载可能是由于大量查询请求的产生导致的，一些复杂的查询语句可能会导致 SQL 优化失败，导致服务器需要耗费较长时间处理查询请求，从而导致 CPU 占用率较高。因此，对查询语句进行优化是减少 MySQL CPU 占用率的首要措施。

不恰当的MySQL配置也可能导致 CPU 占用率的上升，例如将缓存池设得过小，每次查询都要从硬盘读取数据，这样就容易导致CPU占用率增加。而如果将缓存池设得太大，虽然查询速度会更快，但也会增加内存负载，产生其他问题。

14.如何存储 IP 地址？

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

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

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

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

15.对身份证做前缀索引?

1. 前面 6 位是区号，重复的概率是很大的，因此如果要在做前缀索引的话，至少是要 12 位的。但是这样的浪费了太多空间，一个数据页存放的数据就变少了。
2. 存储身份证的时候倒过来，这样后 6 位的区分度就很高了。
3. 数据类型选择：身份证号码是一个固定长度的字符串，通常为 18 位，所以可以选择适当的数据类型来存储。推荐使用 CHAR(18)或 VARCHAR(18)来存储身份证号码。
4. 索引：如果身份证号码是用于查询和检索的字段，可以为该字段创建索引。索引可以大大提高查询效率，尤其是在大数据量的情况下。
5. 唯一性约束：考虑将身份证号码设为唯一性约束，这样可以确保数据库中不会存在重复的身份证号码。
6. 数据校验：在应用层对输入的身份证号码进行校验，确保只有合法的身份证号码才能进入数据库。这可以防止无效或错误的数据污染数据库。
7. 数据加密：如果安全性要求较高，可以考虑对身份证号码进行加密存储。这样即使数据库被未经授权的访问，也不会泄露敏感信息。
8. 数据分区：如果数据库中的数据量很大，可以考虑将数据分区。将身份证号码的相关数据放在单独的分区中，可以提高查询效率和维护性。
9. 压缩存储：如果身份证号码的数据量很大，可以考虑使用压缩算法来存储数据，减少数据库占用空间。
10. 有限长度字段：如果你知道身份证号码的前几位是有规律的，可以将其拆分为两个字段，一个存储前几位，一个存储后几位，这样可以节省空间并提高查询效率。

16.批量插入优化?

问 MySQL 多条记录插入，不准拼接成一条记录，不准工具 orm 啥的批量插入，让设计方案?

使用 Java 中的 PreparedStatement 和 addBatch 方法批量插入多条记录到 MySQL 数据库，而不使用拼接 SQL 字符串，可以按照以下步骤进行操作：

1. 创建连接和 PreparedStatement： 首先，创建与数据库的连接并准备一个 PreparedStatement 对象，该对象包含插入语句，并使用占位符代替实际的值。

Connection connection = DriverManager.getConnection("jdbc:mysql://your_database_url", "username", "password");
String insertQuery = "INSERT INTO your_table_name (column1, column2, column3) VALUES (?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);

2. 为多条记录设置参数并添加到批处理： 然后，为每条记录设置参数并将它们添加到 PreparedStatement 的批处理中，而不是执行单独的 SQL 语句。

preparedStatement.setString(1, value1_1);
preparedStatement.setString(2, value1_2);
preparedStatement.setString(3, value1_3);
preparedStatement.addBatch();

preparedStatement.setString(1, value2_1);
preparedStatement.setString(2, value2_2);
preparedStatement.setString(3, value2_3);
preparedStatement.addBatch();

// 添加更多记录...

3. 执行批处理： 一旦您添加了所有记录到批处理中，就可以执行批处理操作。

preparedStatement.executeBatch();

4. 关闭资源： 最后，记得关闭 PreparedStatement 和数据库连接。

preparedStatement.close();
connection.close();

这种方法允许您批量插入多条记录，而不必手动拼接 SQL 字符串，从而提高了性能和安全性。此外，使用占位符可以防止 SQL 注入攻击。

17.如何优化点赞流程?

点赞流程的优化可以提高用户体验和系统性能。以下是一些常见的点赞流程优化策略：

1. 缓存点赞信息：

   • 使用缓存来存储已点赞的信息，以减轻数据库负载。例如，可以使用内存缓存（如 Redis）来存储用户对文章的点赞状态，以及点赞数量。
   • 使用缓存还可以降低访问数据库的频率，从而提高响应时间。

2. 批量操作：

   • 允许用户批量点赞或取消点赞多篇文章，减少单次请求的数量，降低服务器负载。
   • 在服务端实现批量操作时，可以一次性更新多篇文章的点赞数，而不是逐篇文章更新。

3. 异步处理：

   • 引入异步任务队列，将点赞操作异步化。当用户点赞或取消点赞时，将任务放入队列中，由后台异步处理。
   • 这可以降低用户等待时间，同时允许服务器按照自己的速度处理点赞操作，提高系统的并发性能。

4. 分布式存储：

   • 如果点赞数据非常大或需要高度扩展性，可以考虑将点赞数据存储在分布式数据库或存储引擎中，以分散负载并提高可用性。

5. 使用合适的数据结构：

   • 为点赞信息选择合适的数据结构，以支持高效的点赞操作。例如，使用集合数据结构来存储用户点赞的文章 ID，以便快速检查用户是否已经点赞。

6. 缓存失效策略：

   • 实现缓存失效策略，以确保缓存中的点赞状态和点赞数量与数据库中的数据保持一致。可以使用定期刷新缓存或在点赞操作发生时更新缓存。

7. 负载均衡：

   • 如果点赞流程非常频繁，可以考虑使用负载均衡来分发点赞请求，以确保请求均匀地分布到多个服务器上。

8. 监控和优化：

   • 使用监控工具来追踪系统性能和瓶颈。通过监测系统的性能，可以及时发现问题并进行优化。

9. 安全性和防止滥用：

   • 实施安全措施，以防止滥用点赞系统。例如，限制用户对同一篇文章的点赞次数，实施身份验证和授权机制等。

10. 水平扩展：

    • 如果点赞流程的负载不断增加，考虑水平扩展系统，添加更多的服务器来处理点赞请求，以应对高并发情况。

综合考虑这些策略，可以根据具体的应用需求和性能要求来优化点赞流程，提高系统的响应性能和可伸缩性。