of {$slidecount} ½ {$title} ATZJG.NET {$author}

首页






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 中, 可以使用 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=0;

分析表、检查表和优化表

分析表、检查表和优化表

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

分析表

语法:

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

LOCALNO_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)

检查表

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

语法:

CHECK TABLE tbl_name [, tbl_name2 [, tbl_name3 ...]] [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, BLOBTEXT 类型的字段(属性).

OPTIMIZE TABLE 语句的基本语法:

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

优化 MySQL 服务器

优化 MySQL 服务器

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

优化服务器硬件

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

优化 MySQL 的参数

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

End






Thanks very much!