mysql使用了索引还是慢_即使使用索引,MySQL语句也非常慢

最新推荐文章于 2024-09-26 08:38:55 发布

100offer

最新推荐文章于 2024-09-26 08:38:55 发布

阅读量146

点赞数

文章标签： mysql使用了索引还是慢

本文链接：https://blog.csdn.net/weixin_28952471/article/details/113603400

版权

It may be to do with the buffering, InnoDb does not cache the index it

caches into memory the actual data rows, because of this for what

seems to be a simple scan it is not loading the primary key index but

all the data into RAM and then running your query on it. This may take

some time to work – hopefully if you were running queries after this

on the same table then they would run much faster.

MyIsam loads the indexes into RAM and then runs its calculations over

this space and then returns a result, as an index is generally much

much smaller than all the data in the table you should see an

immediate difference there.

Another option may be the way that innodb stores the data on the disk

– the innodb files are a virtual tablespace and as such are not necessarily ordered by the data in your table, if you have a

fragmented data file then this could be creating problems for your

disk IO and as a result running slower. MyIsam generally are

sequential files, and as such if you are using an index to access data

the system knows exactly in what location on disk the row is located –

you do not have this luxury with innodb, but I do not think this

particular issue comes into play with just a simple count(*)

==================== 07001

explains this:

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If your table does not change often, using the MySQL query cache is a good solution. SHOW TABLE STATUS also can be used if an approximate row count is sufficient. See Section 14.2.11, “InnoDB Performance Tuning Tips”. =================== todd_farmer:It actually does explain the difference – MyISAM understands that COUNT(ID) where ID is a PK column is the same as COUNT(*), which MyISAM keeps precalculated while InnoDB does not.