mysql执行好慢

zhouwf0726

MyISAM引擎INSERT 1000000 条数据花费时间20多秒：
mysql> call p_tst_isam();
ERROR 1305 (42000): PROCEDURE mytest.p_tst_isam does not exist
mysql> call p_test_isam();
Query OK, 1 row affected (22.95 sec)


二InnoDB引擎INSERT 1000 条数据花费34秒：
mysql> call p_test();
-> //
Query OK, 1 row affected (34.48 sec)


当然了，InnoDB是比较适合事务处理的，这个我将马上来研究、学习。呵呵。

zhouwf0726

看MYSQL文档对MyISAM引擎的描述：

如果数据文件中间的表没有自由块了，在其它线程从表读的同时，你可以INSERT新行到表中。（这被认识为并发操作）。自由块的出现是作为删除行的结果，或者是用比当前内容多的数据对动态长度行更新的结果。当所有自由块被用完（填满），未来的插入又变成并发。

个人理解，select的时候锁定，t锁定范围应该是该表所在文件的开始地址到最高地址，如果中间没有删除的行，不影响其他session的insert，如果表的中间有delete的数据，其他session执行insert的时候需要等待执行select语句的session释放锁，但是效果不是这样？



mysql> delete from test_isam limit 1000;
Query OK, 1000 rows affected (0.05 sec)

mysql> select count(*) from test_isam;
+----------+
| count(*) |
+----------+
|   999023 |
+----------+
1 row in set (0.00 sec)

session 2:
mysql> select * from test_isam;
这个过程很长。。。

[/COLOR]
mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from test_isam;
+----------+
| count(*) |
+----------+
|   999034 |
+----------+
1 row in set (0.00 sec)

mysql> insert into test_isam(id,mc) values(1,'1');
Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from test_isam;
+----------+
| count(*) |
+----------+
|   999035 |
+----------+
1 row in set (0.00 sec)

zhouwf0726

以前通过对2种引擎的速度测试，发现差异很大，很差异MYSQL的innodb为何处理速度如此差，今天查了些资料发现和autocommit属性还有很大关系。


测试发现innodb引擎中非自动提交（事务处理）情况下速度和myisam差距不是很大。


--测试还是使用的以前的测试存储过程

mysql> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)

mysql> call p_test();
Query OK, 1 row affected (3.09 sec)[/COLOR]

mysql> set autocommit=1;
Query OK, 0 rows affected (0.03 sec)

mysql> call p_test();
Query OK, 1 row affected (31.25 sec)[/COLOR]

mysql> select table_name,engine from information_schema.tables where table_name=
'test';
+------------+--------+
| table_name | engine |
+------------+--------+
| test       | InnoDB |
+------------+--------+
1 row in set (0.19 sec)

zhouwf0726

innodb没有显式事务控制的情况下，速度更快。


mysql> commit;
Query OK, 0 rows affected (0.06 sec)

mysql> truncate table test;
Query OK, 360 rows affected (0.06 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_test();
Query OK, 1 row affected (0.17 sec)[/COLOR]

mysql> commit;
Query OK, 0 rows affected (0.06 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.27-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)

zhouwf0726

我们可以在初始化参数中给出相应设置，使得所有连接都使用非自动提交，这样即提高了速度，也使得MYSQL数据库和ORACLE有相似的功能。对于长期使用ORACLE的用户来说非常方便：

parameter配置文件”my.cnf”或”my.ini”（WINDOWS系统）

[mysqld]
init_connect='set autocommit=0'

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.05 sec)

drew

我帮斑竹顶一拍。mysql原来是可以支持事务的。

zhouwf0726

MYSQL中只有INNODB和BDB类型的表才能支持事务处理，其他的类型是不支持的。

fffqqqyyy

mysql> set autocommit=0|
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test;
    -> |
Query OK, 1000 rows affected (0.00 sec)

mysql> commit|
Query OK, 0 rows affected (0.03 sec)

mysql> call p_test()|
Query OK, 1 row affected (0.02 sec)

mysql> commit|
Query OK, 0 rows affected (0.05 sec)

mysql> select count(*) from test|
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.02 sec)

zhouwf0726

多谢大家的回复。

zhouwf0726

前几天没有时间，今天接着研究了下MYSQL服务器的系统变量和状态变量，发现一个和INSERT速度有关的参数，先记录下来，以后再进行测试。

innodb_flush_log_at_trx_commit

是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。
默认值是 1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷
新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，
尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把
日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘
中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了，
不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 指
挥丢失刷新到操作系统缓存的那部分事务。