InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
InnoDB存储引擎当前存在的缺点有哪些
1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB 中不保存表的具体行数 ...
renxiao2003 发表于 2010-12-02 14:10
我还看到有人说select count(1)这类的操作慢
是的,InnoDB比MyISAM是慢,但是对于SQL Server,Oracle,D ...
qlks 发表于 2010-12-06 17:01
Sander,
>I'm thinking of switching to InnoDB, however - my application does a few
>COUNT(*) .... WHERE .... queries on large tables (somewhere between 50K
>and 2M rows)
>
>I've read up on InnoDB and its issues with COUNT(*) on entire tables,
>but is there a reason to assume that InnoDB is also slower when there is a
WHERE
>clause present?
no, in the case of COUNT(*) of an entire table MyISAM
is very fast because it keeps a table row count internally.
In the case of COUNT(*) ... WHERE ...
both MyISAM and InnoDB have to really count the matching
rows. Then the speed depends mainly on the amount of disk
i/o needed to access the rows.
Actually, the reason I did not originally put a row count to
InnoDB tables was that I thought most real applications
would ask queries like COUNT(*) ... WHERE. Only later
I have learnt that a plain COUNT(*) is quite common .
在 MySQL 3.23.7(在Windows上是3.23.25)以后,在 MyISAM 表中只要没有冲突的 Insert 操作,就可以无需使用锁表自由地并行执行 Insert 和 Select 语句。也就是说,可以在其它客户端正在读取 MyISAM 表记录的同时时插入新记录。如果数据文件的中间没有空余的磁盘块的话,就不会发生冲突了,因为这种情况下所有的新记录都会写在数据文件的末尾(当在表的中间做删除或者更新操作时,就可能导致空洞)。
首先,我没有一味地说innodb的count(*)慢
现在讨论的情景是带where条件的count统计innodb和myi ...
starzhestarzhe 发表于 2010-12-20 17:18
InnoDB加了where之后,count就会慢了
欢迎光临 Chinaunix (http://bbs.chinaunix.net/) | Powered by Discuz! X3.2 |