MySQL InnoDB第五章：聚集/辅助/B+树索引、Cardinality 值

小鱼儿 2021-12-19 00:07 274阅读 0赞

### **5.4.1 聚集索引** ###

1、InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。  
2、聚集索引（clustered index）是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。（索引组织表中数据也是索引的一部分）  
    同B+树结构一样，每个数据页都通过一个双向链表进行链接。

3、实际的数据页只能按照一棵B+树进行排序，所以每张表只能拥有一个聚集索引，但是可以有多个辅助索引  
4、聚集索引是按照顺序物理的存储数据吗？  
    不是，如果按照特定顺序存储，维护成本太高。聚集索引的存储并不是物理上连续的，而是逻辑上连续的。  
    ①页通过双向链表链接，页按照主键的顺序排序  
    ②每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储

### **5.4.2 辅助索引** ###

1、辅助索引的叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签告诉InnoDb存储引擎哪里可以找到与索引相对应的行数据。  
    由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键（通过这个书签可以查询到聚集索引树）  
2、通过辅助索引来寻找数据，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录  
    查询访问的顺序：从辅助索引树-->聚集索引树  
    逻辑IO访问得到数据页的次数：辅助索引树高度 + 聚集索引树高度

3、SQLServer有称为堆表的表类型，即行数据的存储按照插入的顺序存放（类似MyISAM引擎）  
    堆表的特性决定了堆表上的索引都是非聚集的，主键与非主键的区别只是是否唯一且非空，因此这时书签是一个行标识符，可以用“文件号：页号：槽号”的格式来定位实际的行数据  
4、为什么在SQLServer数据库上还要使用索引组织表？  
    答案：取决于应用，不存在哪个更优  
    不使用：①堆表的书签使非聚集查找可以比主键书签方式更快，并且非聚集可能在一张表中存在多个，我们需要对多个非聚集索引进行查找  
            ②对于非聚集索引的离散读取，索引组织表上的非聚集索引会比堆表上的聚集索引慢一些  
            ③有些情况下堆表的确比索引组织表更快  
    使用：①存在OLAP在线分析处理的应用  
            ②表中数据是否需要更新，并且更新是否影响到物理地址的变更  
            ③对于排序和范围查找，索引组织表通过B+树的中间节点就可以找到要查找的所有页，然后进行读取，而堆表的特性决定了无法实现这个  
            ④非聚集索引的离散读，数据库可以通过实现预读（read ahead）技术来避免多次的离散读操作

### **5.4.4 B+树索引的管理** ###

1、SHOW INDEX命令得到结果的含义：  
    Table：索引所在的表名  
    Non\_unique：如果显示的值是0，说明这个字段（列）是唯一索引，显示1表示非唯一索引  
    Key\_name：索引的名字，用户可以通过这个名字来执行DROP INDEX  
    Seq\_in\_index：索引中该列的位置，单独一个列的索引就是1，如果复合索引就是从1开始递增  
    Column\_name：索引列的名称（字段名）  
    Collation:列以什么方式存储在索引中，可以是A或者NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里的值就是NULL。  
            因为Hash根据Hash桶存放索引数据，而不是对数据排序  
    **Cardinality（重点△）：表示索引中唯一值的数目的估计值（只是一个大概的值，具体看下面解释），Cardinality表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否删除此索引  
            优化器会根据这个值来判断石佛使用这个索引，但是这个值不是实时更新的，更新的SQL:analyze table xxx(表名)\\G  
            在一个非高峰时间，对应用程序下的几张核心表做analyze table操作，可以使优化器和索引更好的为你工作**  
    Sub\_part（有疑问△）：是否是列的部分被索引。如果这个索引包含了整个列（字段），那么这里显示的值为NULL。如果显示了一个数字，那么就只所以小于这个数字的部分  
            执行SQL时候，如果只索引了100个值，WHERE子句中却筛选了超过100的范围，那么这个时候优化器如何处理？  
    Packed：关键字然后被压缩，如果没有被压缩则为NULL  
    NULL：是否索引的列含有NULL值，如果显示为Yes，则该列允许为NULL，如果留空没有值，则该列不允许NULL  
    Index\_type：索引的类型，InnoDB存储引擎只支持B+树索引，都显示BTREE  
    Comment：列的注释

2、Fast Index Creation(FIC)：快速索引创建  
    1）FIC只限定于辅助索引，对于主键的创建和删除同样需要重建一张表  
    2）对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，因此速度和数据库的可用性提高了  
                    删除，InnoDB存储引擎只需更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义  
    3）临时表的创建路径是通过参数tmpdir进行设置的，用户必须保证tmpdir有足够的空间可以存放临时表，否则会导致创建索引失败

3、Online Schema Change(OSC)：在线架构改变  
    1）OSC只是一个PHP脚本，有一定的局限性  
        要求进行修改的表一定要有主键，且表本身不能存在外键和触发器  
        在进行OSC过程中，允许SET sql\_bin\_log=0,因此所做的操作不会同步slave服务器，可能导致主从不一致的情况

**4、Online DDL(在线数据定义)：FIC的升级版**  
    1）允许辅助索引创建的同时，还允许DML操作，如INSERT、UPDATE、DELETE  
    2）以下几类DDL操作也可以通过“在线”方式操作：辅助索引的创建与删除、改变自增长值、添加或删除外键约束、列的重命名  
    3）索引的创建方式

ALTER TABLE xxx(表名)
    | ADD {INDEX|KEY} [index_name] [index_type] [index_col_name,...] [index_option] ...
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

解释：ALGORITHM指定了创建或删除索引的算法  
            COPY：按照MySQL 5.1版本之前的工作模式，即创建临时表的方式  
            INPLACE：索引创建或删除操作不需要创建临时表  
            DEFAULT：根据参数old\_alter\_table来判断是通过INPLACE还是COPY的算法，old\_alter\_table的默认值为OFF，表示采用INPLACE方式  
            LOCK部分为索引创建或删除时对表添加锁的情况，可选值为  
                NONE：对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会受到阻塞，因此这种模式可以获得最大的并发度  
                SHARE：类似FIC，对目标表加上一个S锁。并发的读事务依然可以进行，但是遇到写事务，就会发生等待操作。如果存储引擎不支持该模式，会返回一条错误信息  
                EXCLUSIVE：对目标加上一个X锁。读写事务不能同时进行，因此会阻塞所有的线程，类似COPY方式运行得到的状态，但是不需要像COPY方法一样创建一张临时表  
                DEFAULT：判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。  
                        即DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式

4）InnoDB存储引擎实现Online DDL的原理：在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。  
        待完成索引创建后再将重做应用到表上，以此达到数据的一致性。缓存的大小由参数innodb\_online\_alter\_log\_max\_size控制，默认大小为128MB。  
        如果发生错误该缓存参数不够大，可以调大该参数或者设置ALTER TABLE的模式为SHARE，这样在执行过程中不会有写事务发送，因此不需要进行DML日志的记录  
    5）由于Online DDL在创建索引完成后再通过重做日志达到数据库的最终一致性，所以在索引创建过程中，SQL优化器不会选择正在创建中的索引

### **5.5 Cardinality 值** ###

1、低选择性：对于性别、地区、类型字段，可取的范围很小  
    高选择性（添加B+树索引）：某个字段的取值范围很广，几乎没有重复，如姓名字段  
2、Cardinality的统计是放在存储引擎层进行的，通过采样的方法来完成，过程如下：  
    ①取得B+树索引中叶子节点数量，记为A  
    ②随机取得B+树索引中的8个叶子节点，统计每个页不同记录的个数，即为P1,P2...p8  
    ③根据采样信息给出Cardinality的预估值：Cardinality=(p1+p2+...+p8)\*A/8  
    因为Cardinality是通过对8个叶子节点预估而得的，所以不是一个实际精确的值，另外每次都是随机取得8个节点，所以每次得到Cardinality的值可能是不同的。  
    只有当表足够小，表的叶子节点小于或者等于8个时，Cardinality的值才是相同的。

3、当INSERT、UPDATE操作次数满足以下条件，Cardinality统计信息就会更新：  
    ①表中1/16的数据发生过变化  
    ②stat\_modified\_counter>2 000 000 000：发生变化的次数  
4、Cardinality统计相关的4个参数  
    innodb\_stats\_persistent：是否将命令anyalyze table计算得到的Cardinality值存放到磁盘上。若是，好处是可以减少重新计算每个索引的Cardinality值，如MySQL数据库重启时。  
        此外，用户也可以通过命令CREATE TABLE和ALTER TABLE的选项STATS\_PERSISTENT来对每张表进行控制  
        默认值：OFF  
    innodb\_stats\_on\_metadata：通过命令SHOW TABLE STATUS、SHOW INDEX及访问INFORMATION\_SCHEMA架构下的表TABLES和STATISTICS时，是否需要重新计算索引的Cardinality值  
        默认值：OFF  
    innodb\_stats\_persistent\_sample\_pages：若参数innodb\_stats\_persistent设置为ON，该参数表示ANALYZE TABLE更新Cardinality值时每次采样页的数量  
        默认值：20  
    innodb\_stats\_transient\_sample\_pages：该参数用来取代之前版本的参数innodb\_stats\_sample\_pages，表示每次采样页的数量  
        默认值：8