主页 / dba / 问题 / 17431
Accepted
jcho360
Asked: 2012-05-04 12:51:54 +0800 CST

InnoDB 和 MyISAM 哪个更快？

  • 772

MyISAM 怎么能比 InnoDB “快” 如果

  • MyISAM 需要对数据进行磁盘读取吗？
  • InnoDB 将缓冲池用于索引和数据，而 MyISAM 仅用于索引？
mysql innodb

4 个回答

  1. Best Answer

    在这种独特的情况下，MyISAM 可以比 InnoDB 更快的唯一方法

    MyISAM

    读取时，可以从 .MYI 文件中读取一次 MyISAM 表的索引并加载到 MyISAM 密钥缓存中（大小由key_buffer_size决定）。如何让 MyISAM 表的 .MYD 更快地阅读？有了这个：

    ALTER TABLE mytable ROW_FORMAT=Fixed;

    我在过去的帖子中写过这个

    InnoDB

    好的，InnoDB 呢？InnoDB 是否为查询执行任何磁盘 I/O？令人惊讶的是，确实如此！您可能认为我这么说很疯狂，但这是绝对正确的，即使对于 SELECT 查询也是如此。此时，您可能想知道“InnoDB 到底是如何为查询进行磁盘 I/O 的？”

    这一切都可以追溯到 InnoDB 作为ACID投诉事务存储引擎。为了使 InnoDB 成为事务性的，它必须支持Iin ACID，即 Isolation。维护事务隔离的技术是通过MVCC，Multiversion Concurrency Control完成的。简单来说，InnoDB 在事务尝试更改数据之前记录数据的样子。这是在哪里记录的？在系统表空间文件中，更好地称为 ibdata1。这需要磁盘 I/O。

    比较

    由于 InnoDB 和 MyISAM 都进行磁盘 I/O，哪些随机因素决定了谁更快？

    • 列的大小
    • 列格式
    • 字符集
    • 数值范围（需要足够大的 INT）
    • 跨块拆分的行（行链接）
    • DELETEs由和引起的数据碎片UPDATEs
    • 主键大小（InnoDB 有一个聚集索引，需要两个键查找）
    • 索引条目的大小
    • 名单还在继续……

    因此，在重读环境中，如果有足够的数据写入 ibdata1 中包含的撤消日志以支持事务行为，则具有固定行格式的 MyISAM 表的性能可能优于从 InnoDB 缓冲池中读取的 InnoDB强加于 InnoDB 数据。

    结论

    仔细规划您的数据类型、查询和存储引擎。一旦数据增长，移动数据可能会变得非常困难。只问脸书...

    • 77

  2. 在一个简单的世界中，MyISAM 的读取速度更快，InnoDB 的写入速度更快。

    一旦你开始引入混合读/写，InnoDB 的读取速度也会更快，这要归功于它的行锁定机制。

    几年前我写了一个 MySQL 存储引擎的比较，直到今天仍然如此，概述了 MyISAM 和 InnoDB 之间的独特差异。

    根据我的经验，您应该将 InnoDB 用于除读取繁重的缓存表之外的所有内容，其中由于损坏而丢失数据并不那么重要。

    • 23

  3. 为了补充此处涵盖两种发动机之间机械差异的响应，我提出了一项经验速度比较研究。

    就纯速度而言，MyISAM 并不总是比 InnoDB 快，但根据我的经验，PURE READ 工作环境的速度往往快 2.0-2.5 倍。显然，这并不适合所有环境——正如其他人所写的那样，MyISAM 缺少诸如事务和外键之类的东西。

    我在下面做了一些基准测试——我使用 python 进行循环，使用 timeit 库进行时间比较。出于兴趣，我还包括了内存引擎，尽管它只适用于较小的表（The table 'tbl' is full当您超过 MySQL 内存限制时，您会不断遇到），但它提供了全面的最佳性能。我看到的四种选择是：

    1. 香草选择
    2. 计数
    3. 条件选择
    4. 索引和非索引子选择

    首先，我使用以下 SQL 创建了三个表

    CREATE TABLE
    data_interrogation.test_table_myisam
    (
        index_col BIGINT NOT NULL AUTO_INCREMENT,
        value1 DOUBLE,
        value2 DOUBLE,
        value3 DOUBLE,
        value4 DOUBLE,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8

    在第二个和第三个表中，用“MyISAM”替换了“InnoDB”和“memory”。

     

    1) 香草精选

    询问：SELECT * FROM tbl WHERE index_col = xx

    结果：平局

    不同数据库引擎的 vanilla 选择比较

    这些的速度大致相同，并且正如预期的那样，与要选择的列数呈线性关系。InnoDB 似乎比 MyISAM稍快，但这确实是微不足道的。

    代码：

    import timeit
import MySQLdb
import MySQLdb.cursors
import random
from random import randint

db = MySQLdb.connect(host="...", user="...", passwd="...", db="...", cursorclass=MySQLdb.cursors.DictCursor)
cur = db.cursor()

lengthOfTable = 100000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)
    cur.execute(insertString3)

db.commit()

# Define a function to pull a certain number of records from these tables
def selectRandomRecords(testTable,numberOfRecords):

    for x in xrange(numberOfRecords):
        rand1 = randint(0,lengthOfTable)

        selectString = "SELECT * FROM " + testTable + " WHERE index_col = " + str(rand1)
        cur.execute(selectString)

setupString = "from __main__ import selectRandomRecords"

# Test time taken using timeit
myisam_times = []
innodb_times = []
memory_times = []

for theLength in [3,10,30,100,300,1000,3000,10000]:

    innodb_times.append( timeit.timeit('selectRandomRecords("test_table_innodb",' + str(theLength) + ')', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('selectRandomRecords("test_table_myisam",' + str(theLength) + ')', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('selectRandomRecords("test_table_memory",' + str(theLength) + ')', number=100, setup=setupString) )

     

    2) 计数

    询问：SELECT count(*) FROM tbl

    结果：MyISAM 获胜

    不同数据库引擎的计数比较

    这展示了 MyISAM 和 InnoDB 之间的一个很大区别——MyISAM（和内存）跟踪表中的记录数，所以这个事务很快并且 O(1)。InnoDB 计数所需的时间量随着表大小在我调查的范围内呈超线性增加。我怀疑在实践中观察到的 MyISAM 查询的许多加速是由于类似的影响。

    代码：

    myisam_times = []
innodb_times = []
memory_times = []

# Define a function to count the records
def countRecords(testTable):

    selectString = "SELECT count(*) FROM " + testTable
    cur.execute(selectString)

setupString = "from __main__ import countRecords"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('countRecords("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('countRecords("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('countRecords("test_table_memory")', number=100, setup=setupString) )

     

    3) 条件选择

    询问：SELECT * FROM tbl WHERE value1<0.5 AND value2<0.5 AND value3<0.5 AND value4<0.5

    结果：MyISAM 获胜

    不同数据库引擎的条件选择比较

    在这里，MyISAM 和内存的性能大致相同，在更大的表上比 InnoDB 高出约 50%。这是 MyISAM 的好处似乎被最大化的那种查询。

    代码：

    myisam_times = []
innodb_times = []
memory_times = []

# Define a function to perform conditional selects
def conditionalSelect(testTable):
    selectString = "SELECT * FROM " + testTable + " WHERE value1 < 0.5 AND value2 < 0.5 AND value3 < 0.5 AND value4 < 0.5"
    cur.execute(selectString)

setupString = "from __main__ import conditionalSelect"

# Truncate the tables and re-fill with a set amount of data
for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE test_table_innodb"
    truncateString2 = "TRUNCATE test_table_myisam"
    truncateString3 = "TRUNCATE test_table_memory"

    cur.execute(truncateString)
    cur.execute(truncateString2)
    cur.execute(truncateString3)

    for x in xrange(theLength):
        rand1 = random.random()
        rand2 = random.random()
        rand3 = random.random()
        rand4 = random.random()

        insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
        insertString3 = "INSERT INTO test_table_memory (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)
        cur.execute(insertString3)

    db.commit()

    # Count and time the query
    innodb_times.append( timeit.timeit('conditionalSelect("test_table_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('conditionalSelect("test_table_myisam")', number=100, setup=setupString) )
    memory_times.append( timeit.timeit('conditionalSelect("test_table_memory")', number=100, setup=setupString) )

     

    4) 子选择

    结果：InnoDB 获胜

    对于这个查询，我为子选择创建了一组额外的表。每一个都只是两列 BIGINT，一列有主键索引，另一列没有任何索引。由于表很大，我没有测试内存引擎。SQL 表创建命令是

    CREATE TABLE
    subselect_myisam
    (
        index_col bigint NOT NULL,
        non_index_col bigint,
        PRIMARY KEY (index_col)
    )
    ENGINE=MyISAM DEFAULT CHARSET=utf8;

    在第二个表中，“MyISAM”再次替换了“InnoDB”。

    在此查询中，我将选择表的大小保留为 1000000，而是改变子选择列的大小。

    不同数据库引擎的子选择比较

    在这里，InnoDB 轻松获胜。在我们得到一个合理的大小表之后，两个引擎都随着子选择的大小线性缩放。索引加快了 MyISAM 命令的速度，但有趣的是对 InnoDB 速度几乎没有影响。子选择.png

    代码：

    myisam_times = []
innodb_times = []
myisam_times_2 = []
innodb_times_2 = []

def subSelectRecordsIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString = "from __main__ import subSelectRecordsIndexed"

def subSelectRecordsNotIndexed(testTable,testSubSelect):
    selectString = "SELECT * FROM " + testTable + " WHERE index_col in ( SELECT non_index_col FROM " + testSubSelect + " )"
    cur.execute(selectString)

setupString2 = "from __main__ import subSelectRecordsNotIndexed"

# Truncate the old tables, and re-fill with 1000000 records
truncateString = "TRUNCATE test_table_innodb"
truncateString2 = "TRUNCATE test_table_myisam"

cur.execute(truncateString)
cur.execute(truncateString2)

lengthOfTable = 1000000

# Fill up the tables with random data
for x in xrange(lengthOfTable):
    rand1 = random.random()
    rand2 = random.random()
    rand3 = random.random()
    rand4 = random.random()

    insertString = "INSERT INTO test_table_innodb (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"
    insertString2 = "INSERT INTO test_table_myisam (value1,value2,value3,value4) VALUES (" + str(rand1) + "," + str(rand2) + "," + str(rand3) + "," + str(rand4) + ")"

    cur.execute(insertString)
    cur.execute(insertString2)

for theLength in [3,10,30,100,300,1000,3000,10000,30000,100000]:

    truncateString = "TRUNCATE subselect_innodb"
    truncateString2 = "TRUNCATE subselect_myisam"

    cur.execute(truncateString)
    cur.execute(truncateString2)

    # For each length, empty the table and re-fill it with random data
    rand_sample = sorted(random.sample(xrange(lengthOfTable), theLength))
    rand_sample_2 = random.sample(xrange(lengthOfTable), theLength)

    for (the_value_1,the_value_2) in zip(rand_sample,rand_sample_2):
        insertString = "INSERT INTO subselect_innodb (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"
        insertString2 = "INSERT INTO subselect_myisam (index_col,non_index_col) VALUES (" + str(the_value_1) + "," + str(the_value_2) + ")"

        cur.execute(insertString)
        cur.execute(insertString2)

    db.commit()

    # Finally, time the queries
    innodb_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString) )
    myisam_times.append( timeit.timeit('subSelectRecordsIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString) )
        
    innodb_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_innodb","subselect_innodb")', number=100, setup=setupString2) )
    myisam_times_2.append( timeit.timeit('subSelectRecordsNotIndexed("test_table_myisam","subselect_myisam")', number=100, setup=setupString2) )

    我认为所有这一切的关键信息是，如果您真的关心速度，您需要对正在执行的查询进行基准测试，而不是对哪个引擎更合适做出任何假设。

    • 17

  4. 哪个更快？要么更快。YMMV。

    你应该使用哪个？InnoDB——碰撞安全等。

    • 4

相关问题