主页 / dba / 问题 / 5974
Accepted
Rick James
Asked: 2011-09-21 00:57:11 +0800 CST

最好的 MyISAM 和 InnoDB

  • 772

由于 RAM 的限制，是否可以使 InnoDB 使用与 MyISAM 相同的索引而不是聚集索引，同时获得其并发性能的好处？

mysql performance

3 个回答

  1. Best Answer

    InnoDB 引擎盖下的gen_clust_index（聚集索引）包含主键条目和 rowid。使用 gen_clust_index 的有趣之处在于，您创建的任何非唯一索引将始终具有表的 gen_clust_index 对应的 rowid。因此，总是存在双索引查找，一个用于二级索引，一个用于 gen_clust_index。

    由于 gen_clust_index，任何改进表或主键布局的尝试都会被取消，或者至少是边缘结果。

    例子

    有些人试图按 PRIMARY KEY 顺序对 MyISAM 进行排序。根据MySQL 数据库设计和调优，第 236 页第 7 段，在“以索引顺序存储表”小标题下：

    如果您经常从表中检索大范围的索引数据或对同一索引键的结果进行一致的排序，您可能需要考虑使用 --sort-records 选项运行 myisamchk。这样做会告诉 MySQL 以与索引相同的物理顺序对表的数据进行排序，并有助于加快此类操作的速度。或者，您可以将 ALTER TABLE 语句与 ORDER BY a specific column 选项结合起来，以获得相同的结果。

    当然，这对MyISAM有效且有效。您可以针对 InnoDB 执行 ALTER TABLE ... ORDER BY col1,col2,...,coln，其中列可能是也可能不是 PRIMARY KEY 的列。这不会为 InnoDB 产生更快的结果，因为……没错……您必须每次都查阅 gen_clust_index。

    有些人可以使用 FIXED 将表格的行格式设置为 FIXED，ALTER TABLE mydb.mytb ROW_FORMAT=Fixed;并且可以在不进行任何其他更改的情况下将读取性能提高 20%。这对MyISAM有效且有效。这不会为 InnoDB 产生更快的结果，因为……没错……您必须每次都查阅 gen_clust_index。

    您可以在名为 mydb.mytb 的 InnoDB 表上执行以下操作：

    CREATE TABLE mydb.mytc LIKE mydb.mytb;
INSERT INTO mydb.mytc SELECT * FROM mydb.mytb ORDER BY col1,col2,...coln;
ALTER TABLE mydb.mytb RENAME mydb.mytd;
ALTER TABLE mydb.mytc RENAME mydb.mytb;
DROP TABLE mydb.mytd;

    这将在 gen_clust_index 中按 rowid 顺序放置表。这可能最多对 InnoDB 产生边际结果，因为......这是正确的......您必须每次都查阅 gen_clust_index。

    现在，让我们有点荒谬。有一个 NoSQL 接口用于查询（仅限 SELECT）MyISAM 和 InnoDB，称为HandlerSocket（以前称为 HANLDER）接口。这使您可以访问数据，从而绕过所有 SQL、ACID和MVCC协议。尽管有可能，恕我直言，编码和维护太复杂了。AFAIK 没有任何内容说明 HandlerSocket 接口是否与 gen_clust_index 交互。

    总之，有很多方法可以给猫剥皮。在这种情况下，您无法抓住猫（gen_clust_index）。我想这就是为什么 MyISAM 因其读取性能、表格排序的灵活性、表格行格式以及支持它的工具而继续存在的原因。InnoDB 将继续围绕其符合 ACID 的特性进行设计，直到某个勇敢的灵魂采用 InnoDB 源代码并将其转换为同时具有 MyISAM 和 InnoDB 最好的东西。

    • 14

  2. 聚集索引可能是 InnoDB 在传统旋转驱动器上的并发性能的原因。

    通过聚集索引访问行速度很快，因为行数据位于索引搜索引导的同一页上。如果表很大，与使用与索引记录不同的页面存储行数据的存储组织相比，聚集索引架构通常会节省磁盘 I/O 操作。（例如，MyISAM 将一个文件用于数据行，将另一个文件用于索引记录。）

    磁盘 I/O 很昂贵。因此，减少它对于提高并发性是一个巨大的好处。

    如果磁盘 I/O 开始变得更便宜并且不再是瓶颈（例如，随着 SSD 技术变得更加稳定），Oracle 可能会决定改变 InnoDB 索引的工作方式。它更有可能保持不变，因为相同的技术将使“RAM 的限制”不再是一个问题。

    • 3

  3. 简短的回答：没有。

    InnoDB 通过主键进行集群，在没有主键的情况下，它会选择第一个唯一索引。在没有唯一索引的情况下，它会创建一个隐藏的 6 字节键用于聚类。

    当您拥有隐藏的 6 字节键时，任何二级索引都会引用该键，而不是指向行位置的精确指针（如在 MyISAM 中），因此您最终会进行辅助键遍历，然后进行主键遍历以查找您的记录.

    从您的问题中推断出一点，我假设您担心内存适合树，因为要有效地搜索，所有根节点都应该在内存中，因为您总是必须走这条路才能找到叶页？

    这是真的，但令人欣慰的是，商业数据库试图让他们的树尽可能地肥大，而不是深。尝试在您的数据上运行xtrabackup --stats以查看。例如：

    <INDEX STATISTICS>
  table: test/table1, index: PRIMARY, space id: 12, root page 3
  estimated statistics in dictionary:
    key vals: 25265338, leaf pages 497839, size pages 498304
  real statistics:
     level 2 pages: pages=1, data=5395 bytes, data/pages=32%
     level 1 pages: pages=415, data=6471907 bytes, data/pages=95%
        leaf pages: recs=25958413, pages=497839, data=7492026403 bytes, data/pages=91%

    有 497839 个叶子页面（~8GB），但上面只有 416 个页面（6.5MB）。我已经在生产数据上运行了几次这个命令，当我有数百万条记录并且只有 1-3 级页面 + 叶页时，它总是让我感到惊讶。

    • 3

相关问题