MySQL多维度性能优化

目录

一、四个优化维度

二、硬件配置

三、系统配置

3.1 系统选择

3.2 保证从内存读取

3.3 数据预热

3.4 降低磁盘的写入次数

三、表结构设计

3.1 设计中间表

3.2 设计冗余字段

3.3 拆表

3.4 主键优化

3.5 字段的设计

四、MySQL语句及索引

4.1 使用EXPLAIN分析SQL

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

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

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