Performance issue when updating/inserting massive data into InnoDB

innodb_flush_log_at_trx_commit - 在MySQL 5.5中,该值默认为1,此时在每次事务提交时,MySQL都会将log buffer中的内容写到日志文件,并将日志文件刷新到磁盘上去。所以如果磁盘不是SSD,在进行批量更新、插入的时候性能会非常差。

今天在Stage上生成所有的报表数据,其中一张源表大概有4千万行,结果运行速度非常慢,生成一天的报表数据要花上好几个小时。生成报表的存储过程需要操作(插入、更新)十来张表,但在存储过程中,我们却没有使用事务,也就是说,每一次insert/update后,MySQL都会将改动写入磁盘,而stage仅仅是一般的SATA硬盘,自然性能就会非常差。

解决的办法是改进存储过程,在其中加入事务控制,大概每1000条报表记录提交一次。测试后速度大大提高,生成一天的数据仅需要1到2分钟。

MySQL 查询优化

1. 索引按顺序排列存储

2. 选择索引
  搜索的索引列,不一定是所要选择的列。出现在ON,WHERE和GROUP BY后的列适合作索引。
  使用惟一索引
    对于惟一值的列,索引的效果最好。例如:存放年龄的列具有不同值,所以很容易区分各行;而用来记录性别的列,只有’M’和’F’,不论搜索哪个值,都会得出大约一半的行,所以对其进行索引没有多大用途。
  使用短索引
    如果对字符串类型的列进行索引,应该指定一个前辍长度。比如一个varchar(200)的列,最好指定索引为前10个或20个字符内。(短的索引节省存储空间,并可能使查询更快)
  利用最左前辍
    在创建一个包含n列的索引时,实际上是创建了MySQL可以使用的n个索引。
多列索引可起几个索引的作用,因为可利用索引中最左边的列来匹配行。这样的列的集称为最左前辍。

  比如在一个表中的state, city和zip三个列上创建索引,那么索引中的行是按state/city/zip的次序存放的。因此,索引中的行也会自动按state/city的顺序和state的顺序来存放。所以,该索引可以用来搜索下列的列的组合:
    state/city/zip
    state/city
    state

继续阅读“MySQL 查询优化”

MySQL 5.0 字符集(2)--数据库连接中的字符集

1. 客户端以什么字符集来发送SQL命令?
character_set_client

2. 当服务器接收到SQL命令时,它会将其转换成什么字符集?
character_set_connection and collation_connection
服务器将客户端发送的SQL命令从character_set_client转换到character_set_connection
在进行字符串比较时,collation_connection将起作用。
但是在进行column中字符串比较时,collation_connection将不起作用,因为column有自己的collation。

3. 服务器将以什么字符集发送 给果/错误信息 给客户端?
character_set_results

继续阅读“MySQL 5.0 字符集(2)--数据库连接中的字符集”

MySQL 5.0 字符集(1)--服务器端的字符集

自4.1以后,MySQL增强了对字符集的支持。

名词解释:
—————————————————–
字符集(character set)是一套符号和编码。
而校对(collation)是在该字符集下,用于比较字符的一个规则。
—————————————————–

对于数据库,有四种级别的编码和整理设定:
1. server
2. database
3. table
4. column

继续阅读“MySQL 5.0 字符集(1)--服务器端的字符集”

MySQL 存储引擎及比较

查看当前数据库中安装的存储引擎:

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | NO      | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | YES     | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.00 sec)

1. MyISAM存储引擎
MyISAM是默认存储引擎。它基于更老的ISAM代码,但有很多有用的扩展。

继续阅读“MySQL 存储引擎及比较”