MySQL多维度性能优化

最新推荐文章于 2023-03-01 10:04:47 发布

原创最新推荐文章于 2023-03-01 10:04:47 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

数据库同时被 2 个专栏收录

3 篇文章

订阅专栏

MySQL

1 篇文章

订阅专栏

本文详细介绍了MySQL的多维度性能优化策略，包括硬件配置、系统配置、表结构设计和SQL语句及索引优化。强调了内存使用、数据预热、主键优化、合理使用索引和避免全表扫描等关键点，旨在提升数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

4.4 当只需要一条数据的时候，使用limit 1

4.5 排序字段加索引

4.6 如果限制条件中其他字段没有索引，尽量少用or

4.7 尽量用union all代替union

4.8 区分in和exists、not in和not exists

4.9 使用合理的分页方式以提高分页的效率

4.10 避免使用%前缀模糊查询

4.11 避免在where子句中对字段进行表达式操作

4.12 避免隐式类型转换

4.13 必要时可以使用force index来强制查询走某个索引

4.14 使用联合索引时注意范围查询

4.15 使用JOIN的优化

一、四个优化维度

既然谈到优化，一定想到要从多个纬度进行优化，作为一个合格的程序员或者DBA，问题一定要多方位进行考虑。这里总结了四个维度，当然SQL语句相关的优化手段一定是最为重要的。

这里的优化维度有四个：SQL语句及索引、表结构设计、系统配置、硬件配置。

二、硬件配置

硬件方面的优化可以对磁盘进行扩容、将机械硬盘换为SSD。这个优化手段成本最高，带来的成本却是最高的。这个不再作过多的赘述。

三、系统配置

3.1 系统选择

系统通常使用Linux作为服务端的系统，本地开发的话可以随意。Linux系统版本和MySQL版本选择稳定的版本即可。

3.2 保证从内存读取

MySQL会在内存中保存一定的数据，通过LRU（最近最少使用）算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。

MySQL使用优化过后的LRU算法。

普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰

改进LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间 midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

LRU算法针对的是MySQL内存中的结构，这里有个区域叫Buffer Pool（缓冲池）作为数据读写的缓冲区域。把这个区域进行相应的扩大即可提升性能，当然这个参数要针对服务器硬件的实际情况进行调整。

通过以下命令可以查看相应的BufferPool的相关参数：

 show global status like 'innodb_buffer_pool_pages_%'

输入以下命令可以查看BufferPool的大小：

 show variables like "%innodb_buffer_pool_size%"

在这里我们可以修改这个参数的值，如果该服务器是MySQL专用的服务器，我们可以修改为总内存的 60%~80%，当然不能影响系统程序的运行。

这个参数是只读的，可以在MySQL的配置文件（my.cnf或my.ini）中进行修改。Linux的配置文件为my.cnf。

# 修改缓冲池大小为750M
innodb_buffer_pool_size = 750M

3.3 数据预热

数据预热相当于将磁盘中的数据提前放入BufferPool内存缓冲池内。一定程度提升了读取速度。

对于InnoDB，这里提供一份预热SQL脚本：

SELECT DISTINCT
	CONCAT(
		'SELECT ',
		ndxcollist,
		' FROM ',
		db,
		'.',
		tb,
		' ORDER BY ',
		ndxcollist,
		';'
	) SelectQueryToLoadCache
FROM
	(
		SELECT
			ENGINE,
			table_schema db,
			table_name tb,
			index_name,
			GROUP_CONCAT(
				column_name
				ORDER BY
					seq_in_index
			) ndxcollist
		FROM
			(
				SELECT
					B. ENGINE,
					A.table_schema,
					A.table_name,
					A.index_name,
					A.column_name,
					A.seq_in_index
				FROM
					information_schema.statistics A
				INNER JOIN (
					SELECT
						ENGINE,
						table_schema,
						table_name
					FROM
						information_schema. TABLES
					WHERE
						ENGINE = 'InnoDB'
				) B USING (table_schema, table_name)
				WHERE
					B.table_schema NOT IN (
						'information_schema',
						'mysql'
					)
				ORDER BY
					table_schema,
					table_name,
					index_name,
					seq_in_index
			) A
		GROUP BY
			table_schema,
			table_name,
			index_name
	) AA
ORDER BY
	db,
	tb;

将脚本保存为loadtomem.sql文件

执行命令：

mysql -uroot -proot -AN < /root/loadtomem.sql > /root/loadtomem.sql

在需要数据预热时（一般为重启MySQL服务）执行：

mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

3.4 降低磁盘的写入次数

①增大redolog，减少落盘次数：

redolog是重做日志，用于保证数据的一致。减少落盘相当于减少了系统IO操作。

innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

②通用查询日志、慢查询日志可以不开，bin-log开：

通用查询和慢查询日志也是要落盘的，可以根据实际情况开启，如果不需要使用的话就可以关掉。binlog用于恢复和主从复制，这个可以开启。

查看相关参数的命令：

# 慢查询日志
show variables like 'slow_query_log%'
# 通用查询日志
show variables like '%general%';
# 错误日志
show variables like '%log_error%'
# 二进制日志
show variables like '%binlog%';

③写redolog策略 innodb_flush_log_at_trx_commit设置为0或2：

对于不需要强一致性的业务，可以设置为0或2

0 ：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer-->OS cache，刷盘OS cache-->磁盘文件），最多丢失1秒数据

1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作

2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作

三、表结构设计

3.1 设计中间表

设计中间表，一般针对于统计分析功能，或者实时性不高的需求（报表统计，数据分析等系统）。

3.2 设计冗余字段

为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）。这里分库分表时较为常用。

3.3 拆表

对于字段太多的大表，考虑拆表（比如一个表有100多个字段）对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表。

3.4 主键优化

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

3.5 字段的设计

数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中， ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。能用数字的用数值类型 sex 1 0。

四、MySQL语句及索引

如果发现SQL查询比较慢，可以开启慢查询日志进行排查。

# 开启全局慢查询日志
SET global slow_query_log = ON;
# 设置慢查询日志文件名
SET global slow_query_log_file = 'slow-query.log';
# 记录未使用索引的SQL
SET global log_queries_not_using_indexes = ON;
# 慢查询的时间阈值，默认10秒
SET long_query_time = 10;

4.1 使用EXPLAIN分析SQL

这里简单以一个例子来说明，具体的explain分析和优化过程我想抽空再单独写一篇文章进行分析。

这里对上面的几个列名进行说明：

select_type：查询类型
- SIMPLE 简单查询
- PRIMARY 最外层查询
- UNION union后续查询
- SUBQUERY 子查询
type：查询数据时采用的方式
- ALL 全表（性能最差）
- index 基于索引的全表
- range 范围（< > in）
- ref 非唯一索引单值查询
- const 使用主键或者唯一索引等值查询
- NULL 不使用表
possible_keys：可能用到的索引
key：真正用到的索引
rows：预估扫描多少行记录
key_len：使用了索引的字节数
Extra：额外信息
- Using where 索引回表
- Using index 索引直接满足条件
- Using filesort 需要排序
- Using temprorary 使用到临时表

对于以上的几个列，我们重点关注的是type，最直观的反映出SQL的性能。

4.2 SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。如果数值较多，需要在内存进行排序操作，产生的消耗也是比较大的。

4.3 SELECT语句必须指明字段名称

SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性。

4.4 当只需要一条数据的时候，使用limit 1

limit相当于截断查询。

例如：对于select * from user limit 1; 虽然进行了全表扫描，但是limit截断了全表扫描，从0开始取了1条数据。

4.5 排序字段加索引

排序的字段建立索引在排序的时候也会用到

4.6 如果限制条件中其他字段没有索引，尽量少用or

4.7 尽量用union all代替union

4.8 区分in和exists、not in和not exists

如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

4.9 使用合理的分页方式以提高分页的效率

limit m n，其中的m偏移量尽量小。m越大查询越慢。

4.10 避免使用%前缀模糊查询

例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”，这种会使用到索引。

4.11 避免在where子句中对字段进行表达式操作

这种不会使用到索引：

select user_id,user_project from user_base where age*2=36;

可以改为：

select user_id,user_project from user_base where age=36/2;

4.12 避免隐式类型转换

where子句中出现的column字段要和数据库中的字段类型对应

4.13 必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。

4.14 使用联合索引时注意范围查询

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

4.15 使用JOIN的优化

使用小表驱动大表，例如使用inner join时，优化器会选择小表作为驱动表