"\u003ch1 id=\"一mvcc简介\"\u003e一、MVCC简介\u003c/h1\u003e\n\u003cp\u003eMVCC (Multiversion Concurrency Control)，即多版本并发控制技术。InnoDB数据库的事务隔离级别就是通过UNDO和MVCC来实现的(ACID特性)，旧数据存储在UNDO中，再通过DB_ROLL_PTR 回溯查找历史版本。\u003c/p\u003e\n\u003ch1 id=\"二mvcc原理\"\u003e二、MVCC原理\u003c/h1\u003e\n\u003cp\u003e1、通过DB_ROLL_PT 回溯查找数据历史版本2、通过read view判断行记录是否可见\u003c/p\u003e\n\u003cp\u003e理解这一块之前，我们必须先了解一下row的内部存储格式\u003c/p\u003e\n\u003cp\u003e\u003cimg src=\"https://blog--static.oss-cn-shanghai.aliyuncs.com//uploads/2023/09/image-20230904183143992.png\" alt=\"image-20230904183143992\"\u003e\u003c/p\u003e\n\u003cp\u003e字段\b说明：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e\u003cstrong\u003eDB_ROW_ID：\u003cstrong\u003e长度6个字节。此值由\u003c/strong\u003eInnoDB自动生成\u003c/strong\u003e，聚集索引时使用。如果用户未显式指定表主键时，\u001b表优先使用\u003cstrong\u003e第一个非null的唯一索引\u003c/strong\u003e作为主键.否则使用DB_ROW_ID的值作为主键ID，聚集索引会使用此值。如果指定了表主键的话，则聚集索引使用指定的值。\u003c/li\u003e\n\u003cli\u003e\u003cstrong\u003eDB_TRX_ID：\u003cstrong\u003e6个字节的事务ID。标记了最后\u003c/strong\u003e更新\u003c/strong\u003e此记录的事务ID，每开起一个新事务，其值自动+1\u003c/li\u003e\n\u003cli\u003e\u003cstrong\u003eDB_ROLL_PTR：\u003cstrong\u003e7字节的回滚指针。指向当前记录项的\u003c/strong\u003eundo log\u003c/strong\u003e记录，找之前版本的数据需通过此指针。\u003c/li\u003e\n\u003c/ul\u003e\n\u003cp\u003e\u003cstrong\u003eMySQL中的MVCC原理\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003e\u003ca href=\"https://blog.haohtml.com/wp-content/uploads/2018/09/mysql_mvcc_3.24.44.png\"\u003e\u003cimg src=\"https://blog--static.oss-cn-shanghai.aliyuncs.com//uploads/2023/09/mysql_mvcc_3.24.44.png\" alt=\"\"\u003e\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e首次 \u003ccode\u003einsert\u003c/code\u003e\u003c/strong\u003e …\u003c/p\u003e"

"\u003cp\u003eInnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）。为了提供更好的并发，InnoDB提供了非锁定读：不需要等待访问行上的锁释放，读取行的一个快照。该方法是通过InnoDB的一个特性：MVCC来实现的。\u003c/p\u003e\n\u003ch1 id=\"innodb有三种行锁的算法\"\u003e\u003cstrong\u003eInnoDB有三种行锁的算法\u003c/strong\u003e\u003c/h1\u003e\n\u003cp\u003e1，Record Lock：单个行记录上的锁。\u003c/p\u003e\n\u003cp\u003e2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。\u003c/p\u003e\n\u003cp\u003e3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e锁的是索引，并不是记录。\u003c/strong\u003e\n记录锁（Record Lock): 单个索引行记录上的锁\n间隙锁（Gap Lock）:一般是针对非唯一索引而言的.\n后码锁（Next-Key Lock）:记录锁和间隙锁的结合，对于InnoDB中，更新\u003cstrong\u003e非唯一索引\u003c/strong\u003e对应的记录，会加上Next-Key Lock。在RR下如果where未使用索引会使用全表扫描，这个时候会有Next-Key  Lock。如果更新记录为空，就不能加记 …\u003c/p\u003e"

"\u003cp\u003emyisam的主索引和次索引都指向物理行，下面来进行讲解\u003c/p\u003e\n\u003cp\u003einnodb的主键下存储该行的数据，此索引指向对主键的引用\u003c/p\u003e\n\u003cp\u003emyisam的索引存储图如下，可以看出，无论是id还是cat_id，\u003cstrong\u003e下面都存储有存储物理地址的值。通过主键索引或者次索引来查询数据的时候，都是先查找到\u001b数据地址，然后再到物理位置上去寻找数据\u003c/strong\u003e。\u003c/p\u003e\n\u003cp\u003e[\u003cimg src=\"https://blog--static.oss-cn-shanghai.aliyuncs.com//uploads/2023/09/myisam-index-struct.png\" alt=\"\"\u003e][1]\u003c/p\u003e\n\u003cp\u003einnodb的索引存储图如下，我们会发现，\u003cstrong\u003e主键索引下面直接存储有数据，而次索引下，存储的是主键的id(不同于MyISAM，存储的是内容数据的物理地址）\u003c/strong\u003e。通过主键查找数据的时候，就会很快查找到数据，但是通过次索引查找数据的时候，需要先查找到对应的主键id，然后才能查找到对应的数据。\u003c/p\u003e\n\u003cp\u003e[\u003cimg src=\"https://blog--static.oss-cn-shanghai.aliyuncs.com//uploads/2023/09/innodb-index-struct.png\" alt=\"\"\u003e][2]\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e总结：\u003c/strong\u003e\nInnoDB的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对\u003cstrong\u003e主键的引用\u003c/strong\u003e.\nMyisam中, 主索引和次索引,都指向物理行(\u003cstrong\u003e磁盘位置\u003c/strong\u003e).\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e注意:对InnoDB来说,\u003c/strong\u003e\n1: 主键索引 既存储索引值,又在叶子中存储行的数据\n2: 如果没有主键, 则会Unique key做主键\n3: 如果没有unique,则系统生成一个内部的rowid做主键.\n4: \u003cstrong\u003e像innodb …\u003c/strong\u003e\u003c/p\u003e"

"\u003cp\u003e要说表空间，Mysql的表空间管理远远说不上完善。换句话说，事实上Mysql根本没有真正意义上的表空间管理。Mysql的Innodb包含两种表空间文件模式，默认的共享表空间和每个表分离的独立表空间。只要在my.cnf里面增加innodb_file_per_table=1就可以从共享表空间切换到独立表空间。当然对于已经存在的表，则需要执行alter table MY_TABLE engine=innodb命令迁移数据。\u003c/p\u003e\n\u003ch1 id=\"共享表空间方式\"\u003e\u003cstrong\u003e共享表空间方式\u003c/strong\u003e\u003c/h1\u003e\n\u003cp\u003e由于是默认的方式，就暂且理解为Mysql官方推荐的方式。相对而言所有的数据都在一个（或几个）文件中，比较利于管理，而且在操作的时候只需要open这一个（或几个）文件即可，相对来说代价很低。\u003c/p\u003e\n\u003cp\u003e但问题是在数据达到以G为单位来计算的时候优劣逆转。一个大小惊人的文件很不利于管理，而且对于一个如此巨大的文件来说，读写它需要耗费的资源一样巨大。更加令人费解的是，MySQL竟然将索引和数据保存于同一个文件中，索引和数据之间尚存在资源争用，不利于性能的提升。你当然可以通过innodb_data_file_path的配置规划多个表空间文件，但MySQL的逻辑是“用满后增 …\u003c/p\u003e"

"\u003cp\u003e本次，我们通过对比，明明白白的知道索引、提交频率对InnoDB表写入速度的影响，了解有哪些需要注意的。\u003c/p\u003e\n\u003cp\u003e先直接说几个结论吧：\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003e1、关于索引对写入速度的影响：\na、如果有自增列做主键，相对完全没索引的情况，写入速度约提升 3.11%；\nb、如果有自增列做主键，并且二级索引，相对完全没索引的情况，写入速度约降低 27.37%；\n\u003c/code\u003e\u003c/pre\u003e\u003cp\u003e因此，\u003cstrong\u003eInnoDB表最好总是有一个自增列做主键\u003c/strong\u003e。\u003c/p\u003e\n\u003cp\u003e2、关于提交频率对写入速度的影响（以表中只有自增列做主键的场景，一次写入数据30万行数据为例）：\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003ea、等待全部数据写入完成后，最后再执行commit提交的效率最高；\nb、每10万行提交一次，相对一次性提交，约慢了1.17%；\nc、每1万行提交一次，相对一次性提交，约慢了3.01%；\nd、每1千行提交一次，相对一次性提交，约慢了23.38%；\ne、每100行提交一次，相对一次性提交，约慢了24.44%；\nf、每10行提交一次，相对一次性提交，约慢了92.78%；\ng、每行提交一次，相对一次性提交，约慢了546.78%，也就是慢了5倍；\n\u003c/code\u003e\u003c/pre\u003e\u003cp\u003e因此，\u003cstrong\u003e最好是等待所有事务结束后再批量提交，而不是每执行完一个SQL就提交一次\u003c/strong\u003e。\u003c/p\u003e\n\u003cp\u003e曾经 …\u003c/p\u003e"

"\u003cp\u003e我们先了解下InnoDB引擎表的一些关键特征：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003eInnoDB引擎表是基于B+树的索引组织表(IOT)；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e每个表都需要有一个聚集索引(clustered index)；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e所有的行记录都存储在B+树的叶子节点(leaf pages of the tree)；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e基于聚集索引的增、删、改、查的效率相对是最高的；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ul\u003e\n\u003cp\u003e综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：\u003c/p\u003e\n\u003cul\u003e\n\u003cli\u003e\n\u003cp\u003e使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003e该表不指定自增列做主键，同时也没有可 …\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e"

"\u003cp\u003e作/译者：叶金荣（imysql#imysql.com\u0026gt;），来源： \u003ca href=\"http://imysql.com/\"\u003ehttp://imysql.com\u003c/a\u003e，欢迎转载。\u003c/p\u003e\n\u003cp\u003e起因：在innodb表上做count(*)统计实在是太慢了，因此想办法看能不能再快点。\u003c/p\u003e\n\u003cp\u003e现象：先来看几个测试案例，如下\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e一、 sbtest 表上的测试\u003c/strong\u003e\u003c/p\u003e\n\u003cpre tabindex=\"0\"\u003e\u003ccode\u003eshow create table sbtest\\G\n*************************** 1. row ***************************\nTable: sbtest\nCreate Table: CREATE TABLE `sbtest` (\n`aid` bigint(20) unsigned NOT NULL auto_increment,\n`id` int(10) unsigned NOT NULL default \u0026#39;0\u0026#39;,\n`k` int(10) unsigned NOT NULL default \u0026#39;0\u0026#39;,\n`c` char(120) NOT NULL default \u0026#39;\u0026#39;,\n`pad` char(60) NOT NULL …\u003c/code\u003e\u003c/pre\u003e"

"\u003cp\u003e官方教程： \u003ca href=\"http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb\"\u003ehttp://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#innodb\u003c/a\u003e\u003c/p\u003e\n\u003cp\u003e3.6   InnoDB存储引擎文件\u003c/p\u003e\n\u003cp\u003e之前介绍的文件都是MySQL数据库本身的文件，和存储引擎无关。除了这些文件外，每个表存储引擎还有其自己独有的文件。这一节将具体介绍和InnoDB存储引擎密切相关的文件，这些文件包括重做日志文件、表空间文件。\u003c/p\u003e\n\u003cp\u003e\u003cstrong\u003e3.6.1   表空间文件\u003c/strong\u003e\u003c/p\u003e\n\u003cp\u003eInnoDB存储引擎在存储设计上模仿了Oracle，将存储的数据按表空间进行存放。默认配置下，会有一个初始化大小为10MB、名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file）。你可以通过参数innodb_data_file_path对其进行设置。格式如下：\u003c/p\u003e\n\u003cblockquote\u003e\n\u003c/blockquote\u003e\n\u003cblockquote\u003e\n\u003cp\u003einnodb_data_file_path=datafile_spec1[;datafile_spec2]…\u003c/p\u003e\u003c/blockquote\u003e\n\u003cp\u003e你也可以用多个文件组成一个表空间，同时制定文件的属性，如：\u003c/p\u003e\n\u003cblockquote\u003e\n\u003c/blockquote\u003e\n\u003cblockquote\u003e\n\u003cp\u003e[mysqld]\u003c/p\u003e\u003c/blockquote\u003e\n\u003cp\u003einnodb_data_file_path = …\u003c/p\u003e"