目录
MySQL数据库索引结构
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，MySQL存储引擎MyISAM、InnoDB文章中，我们讲到了两者在存储结构上的差异。下面主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。
MyISAM
每个MyISAM在磁盘上存储成三个文件：表结构、表索引、表数据空间。
- .frm文件存储表定义
- .MYD (MYData)文件存储表的数据
- .MYI (MYIndex)文件存储表的索引
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：
这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：
同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：
这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
InnoDB 共享表空间 & 独立表空间
InnoDB采用按表空间(tablespace)的方式进行存储数据，默认配置情况下会有一个初始大小为10MB， 名字为ibdata1的文件，ibdata1是InnoDB的共有表空间，默认情况下会把表空间存放在一个文件ibdata1中，会造成这个文件越来越大。用户可以通过参数innodb_data_file_path对其进行设置，可以有多个数据文件，如果没有设置innodb_file_per_table的话， 那些Innodb存储类型的表的数据都放在这个共享表空间中，而系统变量innodb_file_per_table=1的话，那么InnoDB存储引擎类型的表就会产生一个独立表空间。
共享表空间
某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 InnoDB共享表空间文件ibdata1中存储了以下几部分信息:
- Data dictionary
- Double write buffer
- Insert buffer
- Rollback segments
- UNDO space
- Foreign key constraint system tables
因此，我们在初始化ibdata1时，最好设置大一些，这样就可以避免因为在高并发情景下导致ibdata1急剧增大，大大影响性能。
独立表空间
每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。
开启独享表空间后，并不是说就不需要ibdata1了，因为在ibdata1中还保存着下面这些数据：
- InnoDB表的元数据
- Buffer
- UNDO日志
共享表空间的优缺点
优点：
- 可以将表空间分成多个文件存放到各个磁盘上。用innodb_data_file_path选项可以配置：innodb_data_file_path = /disk1/ibdata1:2G;/disk2/ibdata2:2G:autoextend。这样配置就把数据文件分散在了disk1和disk2两个路径下,第一个文件固定2G大小,第二个文件初始化2G,可以自增长。（数据文件大小不受表大小的限制,如一个表可以分布在不同的数据文件上）
- 数据和文件放在一起方便管理。
缺点：
- 所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，表做了大量删除操作后表空间中将会有大量的空隙，对于经常删除操作的这类应用最不适合用共享表空间。
- 共享表空间分配后不能回缩。ibdata1 只会一个劲的增长：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了，进行数据库的冷备很慢。
独立表空间的优缺点
优点：
- 每个表有独立的表空间来存放其数据和索引。
- 可以快速实现表在不同数据库间的移动。
- 对表进行大量删除操作后，能针对该表进行空间回收。
- 对于TRUNCATE操作能更快完成
- 能在创建表时指定该表的绝对路径(CREATE TABLE … DATA DIRECTORY = absolute_path_to_directory)
- 能对单表进行跨实例或跨服务器迁移或恢复(ALTER TABLE … DISCARD TABLESPACE + ALTER TABLE … IMPORT TABLESPACE )
缺点：
- 无法充分利用多块存储来提升IO性能。
- 使用更多的文件描述符
- 每个表有自己的未使用空间，如管理不当，容易造成空间浪费。
- MySQL必须为每个打开的表保留文件句柄，当打开大量表时可能存在性能问题。
判别数据表是独立表空间还是共享表空间
- 物理查看，通过ibd文件判别，如果表的存储引擎是InnoDB，而且表空间(tablespace)是共享表空间的话，那么数据库对应目录下面是没有”表名.ibd”文件的。独立表空间的表的话，则有”表名.ibd”文件。
- 命令行查看：show variables like ‘innodb_file_per_table’
innodb_file_per_table设置
命令行设置：SET GLOBAL innodb_file_per_table=ON;
备注：原来库中的表中的数据会继续存放于ibdata1中，新建的表才会使用独立表空间。如果需要修改历史表，这需要执行：ALTER TABLE <tablename> ENGINE=InnoDB
参考链接：
