InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分；
一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。
我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据；
聚簇索引（聚集索引）
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
聚簇索引的优缺点
优点：
1、 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2,聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
1、 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂, 严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2、 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3、 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
辅助索引（非聚簇索引）
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。
MySQL中备份计划，MySQLdump以及xtrabackup的实现原理？
备份计划
1、 视库的大小来定,一般来说100G内的库，可以考虑使用MySQLdump来做，因为MySQLdump更加轻巧灵活，备份时间选在业务低峰期，可以每天进行都进行全量备份(MySQLdump备份出来的文件比较小，压缩之后更小)。
2、 100G以上的库，可以考虑用xtrabackup来做，备份速度明显要比MySQLdump要快。一般是选择一周一个全备，其余每天进行增量备份，备份时间为业务低峰期。
备份恢复时问
1、 物理备份恢复快，逻辑备份恢复慢
2、 这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考
3、 20G 的 2 分钟(MySQLdump)
4、 80G 的 30 分钟(MySQLdump)
5、 111G 的 30 分钟(MySQLdump)
6、 288G 的 3 小时(xtra)
7、 3T 的 4 小时(xtra)
8、 逻辑导入时间一般是备份时间的5倍以上
备份恢复失败如何处理
首先在恢复之前就应该做足准备工作，避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错，再根据报错的提示来进行相应的调整。
MySQLdump 和 xtrabackup 实现原理
MySQLdump
MySQLdump属于逻辑备份。加入-single-transaction选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ),之后显式开启一个事务(START TRANSACTION /！40100 WITH CONSISTENTSNAPSHOT /),这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。如果加上- master-data=l的话，在刚开始的时候还会加一个数据库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时binlog的位置(showmaster status),马上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务
Xtrabackup：
xtrabackup属于物理备份，直接拷贝表空间文件，同时不断扫描产生的redo日志并保存下来。最后完成innodb的备份后，会做一个flush engine logs的操作(老版本在有bug,在5.6上不做此操作会丢数据)，确保所有的redo log都已经落盘(涉及到事务的两阶段提交概念，因为xtrabackup并不拷贝binlog,所以必须保证所有的redo log都落盘，否则可能会丢最后一组提交事务的数据)。这个时间点就是innodb完成备份的时间点，数据文件虽然不是一致性的，但是有这段时间的redo就可以让数据文件达到一致性(恢复的时候做的事情)。然后还需要flush tables with read lock,把myisam等其他引擎的表给备份出来，备份完后解锁。这样就做到了完美的热备。
Was this helpful?
0 / 0