MySQL学习笔记(9) MySQL性能优化

1. 背景

本文讨论一些性能优化的原则和方法。

2.知识

性能优化是通过合理安排资源，调整MySQL参数，服务器环境等手段使得MySQL 运行更快，更节省资源。

常见的优化方法：

• 查询优化
• 数据库表结构设计优化
• MySQL所在的服务器优化

可以从多个方面进行性能优化，原则是 尽量减少系统的瓶颈，减少资源的占用，加快系统的响应速度。比如：

• 优化系统的文件系统，以提高磁盘I/O的读写速度；
• 优化操作系统调度策略，以提高MySQL在高负荷情况下的负载能力；
• 优化表结构，索引，查询语句使得查询的响应更快。

2.1 如何分析

使用 SHOW STATUS 了解 MySQL 数据库的性能参数
示例：

SHOW STATUS LIKE '一些参数';

使用 EXPLAIN 分析查询语句

EXPLAIN select * from book where year_publication= 1990 \G;

分析和检查表

ANALYZE TABLE table1;

CHECK TABLE table1;

OPTIMIZE TABLE table1;

2.2 优化方法

使用索引提升查询速度

提高性能最有效的方式就是设计合理的索引。

使用索引能快速的定位到表中的某条记录，从而提高查询速度，提升性能。它能避免对数据记录行的逐行遍历。

一些注意事项：

• 使用 LIKE 关键字时，不要以 ”%“开头，它导致索引不起作用作用。
• 使用多列索引时，只有使用了 索引字段的第一个字段时，索引才起租用。
• 使用 OR 关键字时，只有 OR 前后的字段都有索引时，查询中才使用索引。

优化子查询

在 SELECT 中使用嵌套子查询时，一般子查询中的 SELECT 结果集将作为 主查询的查询条件。它虽然很灵活，但执行效率不高，MySQL 需要未内层查询语句的查询结果建立一个临时表，然后外层的查询语句从临时表里再次查询。查询完毕后，销毁这些临时表。
优化：尽量使用 连接JOIN 查询来代替子查询，连接查询不需要建立临时表，速度更快。

优化数据库结构

使用频率低的拆成新表
对于字段较多的表，可以将 使用频率低的字段分离出来形成新表。那么，当数据量大时就不用读取更多的使用频率低的字段了。

增加中间表
对于需要经常联合查询的表，可以建立一个中间表以提高效率。把需要经常联合查询的数据插入到中间表中，取代原来的联合查询改为查询中间表，以此来提高查询效率。

增加冗余字段
虽说一个原则是尽量减少冗余字段，而合理的使用冗余字段可以提高查询速度。比如员工的信息中部门名称经常一起显示，那么 员工信息表里除了记录下部门ID，也可以增加一个部门名称的冗余字段，而避免联合查询以提高查询速度。

优化 MySQL 服务器

硬件优化

• 配置较大内存
• 配置高速磁盘
• 合理分布磁盘I/O
• 多处理器等

优化MySQL配置文件的参数

MySQL配置参数在my.cnf ， my.ini 文件中，根据经验修改参数达到优化目的。

END