MySQL性能调优

最新推荐文章于 2024-08-13 10:55:29 发布

CodeWarrior丶

最新推荐文章于 2024-08-13 10:55:29 发布

阅读量664

点赞数 28

文章标签： mysql 数据库性能优化

本文链接：https://blog.csdn.net/huhuhu_o/article/details/137563514

版权

本文详细阐述了MySQL性能调优的各个方面，包括连接池优化、MySQL架构改进、索引策略以及慢查询分析，提供实用的优化技巧，助力提升数据库性能和效率。

摘要由CSDN通过智能技术生成

摘要

本文将从连接池配置优化、MySQL架构优化、MySQL索引优化以及MySQL的慢查询和执行计划的使用四个方面，全面讲解MySQL性能调优的方法和技巧。旨在帮助读者理解MySQL的性能调优，提升数据库系统的整体性能。

正文

1. 连接池配置优化

在Java应用中，数据库连接池是连接数据库的关键组件。连接池可以复用数据库连接，避免频繁地创建和关闭连接所带来的开销，一个高效的数据库连接池可以显著提高数据库访问性能。以下是一些优化建议：

1.1 选择合适的数据库连接池

市面上有很多优秀的关系型数据库连接池，如HikariCP、Druid、C3P0等。根据应用场景和需求选择合适的数据库连接池。

1.2 合理设置连接池参数

主要包括以下关键参数：

最小连接数（Min Pool Size）：连接池中始终保持的最小空闲连接数。
最大连接数（Max Pool Size）：连接池中允许的最大连接数。
连接超时时间（Connection Timeout）：获取连接的等待时间。
空闲连接超时（Idle Timeout）：空闲连接在被关闭之前的最大存活时间。

这些参数需要根据实际业务需求和数据库服务器性能进行调整。

1.3 使用连接池监控

监控连接池的运行状态，如空闲连接数、活跃连接数、等待队列长度等，以便及时调整连接池参数。

2. MySQL架构优化

使用缓存（Redis、Memcached等），要注意缓存一致性及缓存击穿等问题；
选择合适的存储引擎：MySQL支持多种存储引擎，如InnoDB、MyISAM、MEMORY等。根据业务需求选择合适的存储引擎。例如，对于需要事务支持和索引的表，选择InnoDB存储引擎；
使用读写分离（主从复制）：对于高并发应用，可以采用读写分离策略，将读操作和写操作分开，分别由不同的数据库实例处理。
合理分区表：对于大型表，可以考虑使用分区表技术，将表分为多个分区，以提高查询性能。

3. MySQL索引优化

3.1 MySQL中的索引

索引的本质：索引是数据结构。
InnoDB存储引擎支持：B+树索引、全文索引、哈希索引。
HashMap不适合做数据库索引：
- hash表只能匹配是否相等，不能实现范围查找；
- hash表无法支持排序；
- 当数据量很大时，哈希冲突的概率也会很大。
B+树（多叉平衡查找树）：
- B+树的特征：
  - 相同节点数量的情况下，B+树的高度远低于平衡二叉树；
  - 非叶子节点只保存索引信息和下一个节点的指针信息，不保存数据记录；
  - 每个叶子页（LeafPage）存储了实际数据，叶子节点由小到大通过指针串联起来（单链表）。MySQL中与标准B+树不同的是，叶子节点是通过双向链表实现。

3.2 B+树索引

聚集索引/聚簇索引：
- 将表的主键构造一棵B+树，并将整张表的行记录存放在该B+树的叶子节点中。
辅助索引/二级索引：
- 叶子节点除了包含键值，在每个叶子节点的索引行中还包含了一个书签bookmark（存放主键索引）；
- 回表：通过辅助索引获得主键，然后再通过主键索引获得一条完整的行记录。
联合索引/复合索引：
- 将表中多个列组合起来进行索引；
- 排序：按照最左前缀法则，比如建立了一个索引（字段2,字段1），则优先按照字段2排序；
- 优化SQL：尽量减少索引的数量（减少回表），所以尽量使用联合索引。
覆盖索引：
- 不是索引，是优化的一种方式；
- 从辅助索引就可以得到查询的记录，而不需要回表查询聚集索引中的记录。
自适应哈希索引：
- MySQL针对于经常查询的热点数据，自动为这些热点数据建立了哈希索引（只需要查一次即可）；
- 自适应哈希索引是MySQL内部创建的，不能人为干预。

3.3 高性能的索引创建策略

一个select语句一般最多只能使用一个二级索引，为了避免多次回表；
创建索引应该选择选择性/离散性高的列；
- 索引的选择性/离散性是指，不重复的索引值和数据表的记录总数（N）的比值，范围从1/N到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
- ```
select COUNT(DISTINCT 字段名) / COUNT(*) from 表名; （使用该方式计算出选择性）
```

前缀索引
- 针对blob、text和很长的varchar字段，mysql不支持索引他们的全部长度，需要建立前缀索引；
- 无法应用 order by、group by 以及覆盖索引。
- ```
alter table tableName add key/index (column(X)); （X是字符数量）
```

多列索引
- 将选择性最高的列，放在索引最前列；
- 根据运行频率最高的查询，来调整索引的顺序；
- 优化性能时，需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。
三星索引
- 一星：索引将相关的记录放到一起；
- 二星：索引中的数据顺序和查找中的排列顺序一致；
- 三星：索引中的列包含了查询中的需要的全部列（最重要）。

3.4 高性能的索引使用策略

不要在索引列上做任何操作（表达式或函数）；
尽量全值匹配（针对联合索引）；
最佳左前缀法则；
范围条件放最后（针对联合索引）；
- 左边的列是精确查找，则右边的列可进行范围查找
- 中间有范围查询，会导致后面的索引列全部失效，无法充分利用联合索引
尽量使用覆盖索引（尽量不使用 * ）；
慎用不等于；
- 使用不等于的时候无法使用索引，会导致全表扫描
设计表时，字段尽量不可以为空；
字符类型加引号；
- 字符类型若不加引号，会导致索引失效，因为查询优化器会自动加入函数，把数字转为字符串，使用函数就会导致索引失效
使用like关键字要注意；
- 在字符串后加%，一般不会影响。在字符串前加%，一般会使索引失效（因为在索引中，只有后面几部分，顺序是乱的）
- 解决办法：使用覆盖索引
使用or关键字要注意；
- 如果or两边是同一个字段，一般不会影响。若是不同字段，一般会使索引失效
- 解决办法：可以使用 union all 把or两边不同条件的查询结果连接
排序要当心；
- ACS和DECS不要一起使用
- 联合索引中，要按照索引列顺序排序，倒过来也可以，但是不能穿插
主键尽量使用自增，或雪花算法（方便插入数据）

4. 慢查询和执行计划

4.1 慢查询基础

慢查询含义：查询花费大量时间的日志。
对于低效的查询，一般通过下面两个步骤分析：
- 确认应用程序是否在检索大量超出需要的数据，可能访问了太多行，或者太多列；
- 确认MySQL服务器是否在分析大量超出需要的数据行。

慢查询配置：

慢查询日志默认是关闭的，需要手动开启：

show VARIABLES like 'slow_query_log';  （value值中表示是否开启）
set GLOBAL slow_query_log=1;  （设置开启）

MySQL可以设定一个阈值，超过该值的SQL语句都会记录到慢查询日志中：

show VARIABLES like '%long_query_time%';
set GLOBAL long_query_time=0;  （默认是10秒）

若SQL语句中没有使用索引，也可以记录到慢查询日志中，控制参数为：
- ```
show VARIABLES like '%log_queries_not_using_indexes%';
```
查看日志存放位置：

show VARIABLES like '%slow_query_log_file%';

4.2 执行计划（每个字段的含义）

id：每个select关键字都对应一个id
select_type：select 关键字对应的那个查询的类型
table：表名
partitions：匹配的分区信息（不用关注）
type：针对单表的访问方法（system>const>eq_ref>ref>range>index>ALL）（一般情况下，要保证查询是range级别，最好达到ref）
- const：根据主键或者唯一二级索引列与常数进行等值匹配时（只匹配一条数据），对单表的访问方法就是const
- eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是eq_ref
- ref：当通过普通的二级索引列与常量进行等值匹配时，那么对该表的访问方法就可能是ref
- range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法（between、<，>、in）
- index：当使用了覆盖索引，那么就可能使用到index访问方法
possible_keys：可能用到的索引
key：实际使用的索引
key_len：实际使用到的索引长度
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows：预估的需要读取的记录条数
filtered：某个表经过搜索条件过滤后，剩余记录条数的百分比
Extra：一些额外信息
- Using where：使用了where从句来限制哪些行将与下一张表匹配或是返回给客户端
- Using index：使用了覆盖索引，不需要回表操作
- Using index condition：使用了索引下推