首页

MySQL 的性能优化

— Performance optimization —

Haifeng Xu

(hfxu@yzu.edu.cn)

This slide is based on 刘增杰、张少军 《MySQL 5.5 从零开始学》

http://forums.mysql.com/

目录

优化简介

优化简介

优化 MySQL 数据库是数据库管理员(DBA)和数据库开发人员的必备技能.

• 找出系统的瓶颈, 提高 MySQL 数据库整体的性能;
• 研究合理的结构设计, 调整一些参数, 以提高用户操作响应的速度;
• 尽可能节省系统资源, 以便系统可以提供更大负荷的服务.

MySQL 数据库优化是多方面的, 原则是

• 减少系统的瓶颈;
• 减少资源的占用;
• 增加系统的反应速度.

例如:

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

查询数据库性能参数

查询数据库性能参数

在 MySQL 中, 可以使用 SHOW STATUS 语句查询一些 MySQL 数据库的性能参数.

优化数据库结构

优化数据库结构

分解

原则: 符合某种范式.

另一方面, 结合实际情况, 即根据字段(属性)的使用频率来分解. 一般的, 将字段很多的表分解成多个表. 特别的, 如果有些字段使用频率很低, 可以将这些字段分离出来形成新的表.

增加中间表

对于需要经常联合查询的表, 可以建立中间表以提高查询效率. 将需要经常联合查询的数据插入到中间表中, 然后将原来的联合查询改为对中间表的查询. 这里的中间表可以使用视图.

增加冗余字段

一般的, 冗余字段在范式中是不被建议的. 但是数据库的运行需要考虑实际情况, 有时合理地加入一些特定的冗余字段可以提高查询速度.

原因在于, 表的规范化程度越高, 表与表之间的联系也就越多, 从而需要联合查询的情况也越多.

冗余字段的问题

• 数据存储在不同的表中, 当其中一个表中的数据变动后, 另一张表中的相应数据应与其保持一致. (可以使用外键约束, 不能用外键时, 建议使用触发器.)
• 增加额外的存储空间, 不过相对于数据库性能的提高, 增加适当的存储空间不是问题, 是值得的.

是否通过增加冗余来提高数据库性能, 这要根据实际需求综合分析.

优化插入记录的速度

优化插入记录的速度

影响插入速度的因素主要有

• 索引
• 唯一性校验
• 一次插入记录条数

根据这些情况, 可以分别进行优化.

对于 MyISAM 表的优化方法

对于 MyISAM 表的优化方法

插入记录之前禁用索引, 插入完毕再开启索引.

对于非空表, 插入记录时, MySQL 会根据表的索引对插入的记录建立索引. 如果插入大量数据, 建立索引会降低插入记录的速度.

禁用索引

ALTER TABLE table_name DISABLE KEYS;

启用索引

ALTER TABLE table_name ENABLE KEYS;

对于空表批量导入数据, 则不需要进行禁止索引操作. 因为 MyISAM 引擎的表是在导入数据之后才建立索引的.

禁用唯一性检查

SET UNIQUE_CHECKS=0;

插入完毕后开启唯一性检查

SET UNIQUE_CHECKS=1;

使用批量插入

使用 LOAD DATA INFILE 批量导入

对于 InnoDB 引擎的表的优化方法

对于 InnoDB 引擎的表的优化方法

禁用唯一性检查

禁用外键检查

禁止自动提交

禁止自动提交

SET AUTOCOMMIT=0;

恢复自动提交

SET AUTOCOMMIT=1;

分析表、检查表和优化表

分析表、检查表和优化表

MySQL 提供了分析表、检查表和优化表的语句.

• 分析表主要是分析关键字的分布;
• 检查表主要是检查表是否存在错误;
• 优化表主要是消除删除或者更新造成的空间浪费.

分析表

语法:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[, tbl_name2, ...];

LOCAL 是 NO_WRITE_TO_BINLOG 关键字的别名, 都是指执行过程不写入二进制日志.

使用 ANALYZE TABLE 分析表的过程中, 数据库系统会自动给表加上一个只读锁.

在分析期间, 只能读取表中的记录, 不能更新和插入记录.

ANALYZE TABLE 语句可以分析 InnoDB, BDB, MyISAM 类型的表.

BDB 是指 Berkeley_DB. https://en.wikipedia.org/wiki/Berkeley_DB

检查表

作为例子, 我们分析 test_bar 数据库中的表 Bars.

mysql> use test_bar;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_test_bar |
+--------------------+
| bars               |
| beers              |
| drinkers           |
| frequents          |
| likes              |
| sells              |
+--------------------+
6 rows in set (0.01 sec)

mysql> ANALYZE TABLE bars;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test_bar.bars | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.02 sec)

• Table: 表示分析的表的名称.
• Op: 表示执行的操作, analyze 指分析操作.
• Msg_type: 表示信息类型, 取值通常为 status, info, note, warning, error.
• Msg_text: 表示信息的内容.

检查表

MySQL 中可以使用 CHECK TABLE 语句来检查表.

• CHECK TABLE 语句能够检查 InnoDB 和 MyISAM 类型的表是否存在错误.
• 对于MyISAM 类型的表, CHECK TABLE 语句还会更新关键字统计数据.
• CHECK TABLE 语句也可以检查视图是否有错误, 比如在视图定义中被引用的表是否已经不存在.

语法:

CHECK TABLE tbl_name [, tbl_name2 [, tbl_name3 ...]] [QUICK | FAST | MEDIUM | EXTENDED | CHANGED]

• QUICK: 不扫描行, 不检查错误的连接.
• FAST: 只检查没有被正确关闭的表.
• MEDIUM: 扫描行, 以验证被删除的连接是有效的. 也可以计算各行的关键字校验和, 并使用计算出的校验和来验证这一点.
• EXTENDED: 对每行的所有关键字进行一个全面的关键字查找. 这可以确保表是百分之百一致的, 但是花的时间较长.
• CHANGED: 只检查上次检查后被更改的表和没有被正确关闭的表.

注意, 这些选项只对 MyISAM 表有效, 对 InnoDB 表无效.

CHECK TABLE 语句在执行过程中也会自动给表加上只读锁.

mysql> CHECK TABLE bars;
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| test_bar.bars | check | status   | OK       |
+---------------+-------+----------+----------+
1 row in set (0.00 sec)

优化表

MySQL 中可以使用 OPTIMIZE TABLE 语句来优化表. 该语句对 MyISAM 表和 InnoDB 表都有效.

但是 OPTIMIZE TABLE 语句只能优化表中的 VARCHAR, BLOB 或 TEXT 类型的字段(属性).

OPTIMIZE TABLE 语句的基本语法:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name2 [...]]

• 与分析表中的一样, LOCAL 与 NO_WRITE_TO_BINLOG 的意义相同, 都是指执行过程不写入二进制日志.
• 通过 OPTIMIZE TABLE 语句可以消除由删除和更新操作所造成的文件碎片.
• OPTIMIZE TABLE 语句在执行过程中也会给表加上只读锁.

mysql> OPTIMIZE TABLE bars;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table         | Op       | Msg_type | Msg_text                                                          |
+---------------+----------+----------+-------------------------------------------------------------------+
| test_bar.bars | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test_bar.bars | optimize | status   | OK                                                                |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.10 sec)

mysql> desc bars;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | NO   | PRI | NULL    |       |
| addr    | varchar(255) | YES  |     | NULL    |       |
| license | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

以下参考自: https://blog.csdn.net/langkeziju/article/details/44977017

1. MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2. OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3. 在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
4. 默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

References

1. http://blog.51yip.com/mysql/1789.html

优化 MySQL 服务器

优化 MySQL 服务器

从两个方面来优化 MySQL 服务器:

• 硬件优化
• 对 MySQL 服务的参数进行优化

优化服务器硬件

服务器的硬件性能直接决定着 MySQL 数据库的性能.

• 配置较大的内存.
• 配置高速磁盘系统, 以减少读盘的等待时间, 提高响应速度.
• 合理分布磁盘 I/O, 把磁盘 I/O 分散在多个设备上, 以减少资源竞争, 提高并行操作能力.
• 配置多处理器. MySQL 是多线程的数据库, 多处理器可以同时执行多个线程.

优化 MySQL 的参数

MySQL 服务的配置参数都在 my.cnf 或者 my.ini 文件的 [mysqld] 组中.

• key_buffer_size:
• table_cache:
• query_cache_size:
• sort_buffer_size:
• read_buffer_size:
• read_rnd_buffer_size:
• innodb_buffer_pool_size:
• max_connections:
• innodb_flush_log_at_trx_commit:
• back_log:
• interactive_timeout:
• :
• thread_cache_size:
• wait_timeout:

End

Thanks very much!