MySql详解（五）--性能优化篇

最新推荐文章于 2024-05-11 14:57:51 发布

刘了个牛

最新推荐文章于 2024-05-11 14:57:51 发布

阅读量427

点赞数 1

分类专栏： Mysql 文章标签： mysql 数据库 Powered by 金山文档

本文链接：https://blog.csdn.net/qq_35105015/article/details/129396606

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL性能优化篇

服务器层面优化

将数据保存在内存中，保证从内存读取数据

设置足够大的innodb_buffer_pool_size ，将数据读取到内存中。建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5。

怎样确定 innodb_buﬀer_pool_size 足够大。数据是从内存读取而不是硬盘？

降低磁盘写入次数

对于生产环境来说，很多日志是不需要开启的，比如：通用查询日志、慢查询日志、错误日志

使用足够大的写入缓存 innodb_log_file_size。推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size。

设置合适的innodb_flush_log_at_trx_commit，和日志落盘有关系。

MySQL数据库配置优化

表示缓冲池字节大小。推荐值为物理内存的50%~80%

innodb_buffer_pool_size

用来控制redo log刷新到磁盘的策略。

innodb_flush_log_at_trx_commit=1

每提交1次事务同步写到磁盘中，可以设置为n

sync_binlog=1

脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。推荐值为25%~50%。

innodb_max_dirty_pages_pct=30

后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000

innodb_io_capacity=200

指定innodb共享表空间文件的大小。

innodb_data_file_path

慢查询日志的阈值设置，单位秒。

long_qurey_time=0.3

mysql复制的形式，row为MySQL8.0的默认形式。

binlog_format=row

调高该参数则应降低interactive_timeout、wait_timeout的值。

max_connections=200

过大，实例恢复时间长；过小，造成日志切换频繁。

innodb_log_file_size

全量日志建议关闭。默认关闭。

general_log=0

服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。

CPU的选择:

对于数据库并发比较高的场景，CPU的数量比频率重要。

对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好。

CentOS系统针对mysql的参数优化

内核相关参数(/etc/sysctl.conf)

以下参数可以直接放到sysctl.conf文件的末尾。

增加监听队列上限：

net.core.somaxconn= 65535

net.core.netdev_max_backlog= 65535

net.ipv4.tcp_max_syn_backlog= 65535

加快TCP连接的回收：

net.ipv4.tcp_ﬁn_timeout = 10

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

TCP连接接收和发送缓冲区大小的默认值和最大值:

net.core.wmem_default= 87380

net.core.wmem_max= 16777216

net.core.rmem_default= 87380

net.core.rmem_max= 16777216

减少失效连接所占用的TCP资源的数量，加快资源回收的效率：

net.ipv4.tcp_keepalive_time= 120

net.ipv4.tcp_keepalive_intvl= 30

net.ipv4.tcp_keepalive_probes= 3

单个共享内存段的最大值：

kernel.shmmax = 4294967295

这个参数应该设置的足够大，以便能在一个共享内存段下容纳整个的Innodb缓冲池的大小。

这个值的大小对于64位linux系统，可取的最大值为(物理内存值-1)byte，建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可。

控制换出运行时内存的相对权重：

vm.swappiness = 0

这个参数当内存不足时会对性能产生比较明显的影响。（设置为0，表示Linux内核虚拟内存完全被占用，才会要使用到交换区。）

Linux系统内存交换区：在Linux系统安装时都会有一个特殊的磁盘分区，称之为系统交换分区。使用 free -m 命令可以看到swap就是内存交换区。作用：当操作系统没有足够的内存时，就会将部分虚拟内存写到磁盘的交换区中，这样就会发生内存交换。

如果Linux系统上完全禁用交换分区，带来的风险：

降低操作系统的性能

容易造成内存溢出，崩溃，或都被操作系统kill掉

增加资源限制(/etc/security/limit.conf)

打开文件数的限制(以下参数可以直接放到limit.conf文件的末尾)：

* soft noﬁle 65535

* hard noﬁle 65535

*：表示对所有用户有效soft：表示当前系统生效的设置（soft不能大于hard ） hard：表明系统中所能设定的最大值 noﬁle：表示所限制的资源是打开文件的最大数目 65535：限制的数量

以上两行配置将可打开的文件数量增加到65535个，以保证可以打开足够多的文件句柄。注意：这个文件的修改需要重启系统才能生效。

磁盘调度策略

cfq (完全公平队列策略，Linux2.6.18之后内核的系统默认策略)

该模式按进程创建多个队列，各个进程发来的IO请求会被cfq以轮循方式处理，对每个IO请求都是公平的。该策略适合离散读的应用。

deadline (截止时间调度策略)

deadline，包含读和写两个队列，确保在一个截止时间内服务请求（截止时间是可调整的），而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象，deadline对数据库类应用是最好的选择。

noop (电梯式调度策略)

noop只实现一个简单的FIFO队列，倾向饿死读而利于写，因此noop对于闪存设备、RAM及嵌入式系统是最好的选择。

anticipatory (预料I/O调度策略)

本质上与deadline策略一样，但在最后一次读操作之后，要等待6ms，才能继续进行对其它I/O请求进行调度。它会在每个6ms中插入新的I/O操作，合并写入流，用写入延时换取最大的写入吞吐量。anticipatory适合于写入较多的环境，比如文件服务器。该策略对数据库环境表现很差。

查看调度策略的方法：

cat/sys/block/devname/queue/scheduler

修改调度策略的方法：

echo> /sys/block/devname/queue/scheduler

SQL设计层面优化

面对人群：

懂技术并且了解需求的程序员。

具体优化方案如下：

设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）

为减少关联查询，创建合理的冗余字段（考虑数据库的三范式和查询性能的取舍，创建冗余字段还需要注意数据一致性问题）

对于字段太多的大表，考虑拆表（比如一个表有100多个字段）

对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）

每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下）。

SQL语句优化（开发人员）

索引优化

为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据的业务场景：查询多还是增删多？

尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查询条件放到最左边。

尽量使用覆盖索引，SELECT语句中尽量不要使用*。order by、group by语句要尽量使用到索引。索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引。

索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。

order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

LIMIT优化

如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。

SELECT * FROM user WHERE username=’全力詹’; --username没有建立唯一索引

SELECT * FROM user WHERE username=’全力詹’LIMIT 1;

处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE；LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。

解决方案1：使用order by 和索引覆盖

原SQL（如果film表中的记录有10020条）：

SELECT id,name FROM user LIMIT 10000, 20;

优化的SQL：

SELECT id,name FROM user ORDER BY titleLIMIT 20;

解决方案2：使用子查询

解决方案3：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows

其他查询优化

小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须全扫描的，以少关联多就可以减少这个扫描次数。

避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描。在查询的时候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描

避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典型的场景就是数据量少的时候）

COUNT(*)\COUNT(1)\COUNT(列)，从索引使用情况来说，是一样的。

如果COUNT(非索引列)，那么MySQL会选择该表中，最小的那颗索引树，去进行统计。

COUNT(*)以行为统计，最终的结果是包含null值

COUNT(1)，会过滤NULL值

COUNT(列)，会过滤NULL

JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。

SELECT * FROM orders o LEFT JOIN user u on o.user_id = u.id

orders表中的user_id和user表中的id，类型要一致

WHERE条件中尽量不要使用1=1、not in语句（建议使用not exists）

不用 MYSQL 内置的函数，因为内置函数不会建立查询缓存。

SQL查询语句和查询结果都会在第一次查询只会存储到MySQL的查询缓存中，如果需要获取到查询缓存中的查询结果，查询的SQL语句必须和第一次的查询SQL语句一致。

SELECT * FROM user where birthday = now();

合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。