MYSQL技术内幕 InnoDB ch5索引与算法

ch5 索引与算法

索引太多，应用性能受影响；索引太少，查询性能有影响。某台mysql server iostat显示磁盘使用率一直100%，经分析发现是太多索引，删除一些后恢复。
　　InnoDB支持下述索引-

• B+树
• 全文
• 哈希

哈希索引是自适应的，不能人为干预
　　B+树索引并不能找到一个给定键值具体行。B+树索引能找到被查找数据行所在页，把页读入内存，再在内存中查找数据

平衡二叉树，任何节点两个子树高度最大差1。查找性能高，最好的性能需建立一棵最优二叉树，但最优的建立/维护需要大量操作，因此一般只需一颗平衡二叉树，维护一颗平衡二叉树代价也非常大。平衡二叉树多用于内存结构对象，因此维护开销还好

5.3 B+树

B+树由B树和索引顺序访问方法（ISAM，MyISAM最初参考的数据结构）演化而来，现实已经不使用B树了

B+树是为磁盘/直接存取设备设计的平衡查找树，所有记录节点按键值大小顺序存放在同一层叶子节点上，由各叶子节点指针连接


　　为了保持平衡对新插入键值可能需要做大量拆分页操作。因为主要用于磁盘，页拆分意味着磁盘操作应该尽可能减少页的拆分。因此提供了旋转。旋转发生在leaf page满，左右兄弟节点未满

5.4 B+树索引

B+树索引本质就是B+树在db中的实现。B+索引在db中的特点是高扇出性，因此在db中树高度一般在2 ~4层，查找键值行记录最多需要2 ~ 4次IO。一般机械磁盘每秒可做100次IO，2~4次查询只需要0.02 ~0.04s
　　B+树可分为聚集索引和辅助索引，都是B+树，叶子节点存放所有数据，不同的是，叶子节点存放的是否是一整行的信息
　　聚集索引就是按主键构造一颗B+树，叶子节点存放整张表的行记录，叶子节点成为数据页。决定了索引组织表中数据也是索引的一部分。每个数据页通过双向链表链接
　　数据页上存放的是完整行记录，索引页存放的仅仅是键值和指向数据页的偏移量
　
　　聚集索引存储不是物理连续，而是逻辑连续：页通过双向链表链接，页按主键顺序排序；每个页中的记录也通过双向链表维护，物理存储可以同样不按主键存储
　　辅助索引叶子节点不包含行记录全部数据。叶子节点除了包含键值，还包含一个bookmark。该bookmark告诉Innodb在哪里找到行数据。因为表是索引组织表，所以辅助索引书签是相应行数据聚焦索引键
　　
　　通过辅助索引寻找数据时，InnoDB会遍历辅助索引并通过叶级别指针获得指向主键索引的主键，再通过主键索引找到完整行记录
　　
　　索引创建删除两种，ALTER TABLE，CREATE/DROP INDEX
　　可以对整个列数据索引，也可以只索引一个列的开头部分数据

SHOW INDEX。Cardinality表示索引中唯一值数目估计值，/行数应尽可能接近1，如果非常小，需考虑删除索引
　　 更新索引信息，使用ANALYZE TABLE

Cardinality

在访问表中很少一部分时使用B+树索引才有意义，对性别等可取值范围很小，称为低选择性
　　按性别查询可能会得到50%的数据，这时索引完全没意义。如果某字段取值很广几乎不重复属于***高选择性，则适合B+树索引***
　　怎么看索引是否有高选择性？SHOW INDEX Cardinality观察。C表示索引中不重复记录数量预估值，不是准确值。实际中，C/n_rows_in_table应尽可能接近1，如果非常小需要考虑创建索引必要。在访问高选择属性字段并从表中取出很少数据时，索引很有必要：
　　
　　
　　不同存储引擎对B+树索引实现各不相同，Cardinality统计放在存储引擎层进行
　　所以更新很频繁，统计Cardinality有负担。db对C的统计通过采样完成
　　InnoDB中C更新发生在两个操作中，INSERT/UPDATE
　　

5.6 B+树索引使用

DB中存有两类应用。OLTP和OLAP。OLTP查询只获取一小部分数据，B+树索引使用应该通过该索引取得表中少部分数据。
　　OLAP，访问表中大量数据，根据数据产生查询结果，查询多面向分析，为决策者提供支持，如每个用户消费情况，销售额同比、环比增长
　　联合索引键值不是1，而是大于等于2
　　
　　键值是排序的
　　对a/b可用索引，对a也可以用，但对b不能用，因为b显然不是排序的
　　联合索引第二个好处是对第二个键值进行了排序处理
　　
　　这个SQL既可以使用userid也可以使用联合索引，优化器使用了联合，因此buy_date已经排序好了，无需额外排序操作
　　
　　所以覆盖，从辅助索引中就可以获取查询记录

某些情况，执行EXPLAIN时发现没有选择索引，而是扫描聚集索引，也就是直接全表，多发生于范围查找，join操作
　　对于不能进行索引覆盖的情况，通过辅助索引查找数据是少量的，利用顺序读替换随机读。如果随机读足够快，可以FORCE INDEX 强制使用索引

索引提示INDEX HINT，显示告诉优化器用哪个索引

• 优化器错误使用某索引导致sql慢，这种情况极少见，优化器在大部分情况下工作有效正确
• sql可选择索引非常多，优化器选择执行计划时间开销可能大于SQL本身

USE INDEX告诉优化器可以选择某索引，实际优化器会再根据自己判断选择。可靠的是FORCE INDEX

5.7 哈希

InnoDB使用哈希对字典进行查找，冲突机制采用链表，除法散列。m取值为略大于2倍缓冲池页数量的质数
　　自适应的。对字典类型查找很快，对范围查找不行，SHOW ENGINE INNODB STATUS看到当前自适应哈希索引使用状况
　　只能用于搜索等值查询

5.8 全文索引

支持前缀：

SELECT * FROM blog WHERE content like 'xxx%'

但更多情况我们需要进行关键词搜索，如：

SELECT * FROM BLOG WHERE CONTENT LINKE '%XXX%'

根据B+树特性，即便添加了B+树索引也需要扫描索引得到结果。类似需求还有搜索引擎根据输入关键字进行全文查找，电商需根据用户查询条件在商品详细介绍中查找，这些不是B+树索引能很好完成的
　　全文检索是将存储于DB中的整本书中任意内容查找出来的技术，可以根据需要获得全文有关章/段/句/词信息，也可以进行各种统计和分析
　　在之前MYSQL中，INNODB不支持全文检索。大多用户转向MYISAM，这可能需要表拆分，并将需进行全文检索的数据存储为MYISAM表。但这样丧失了INNODB的事务性
　　从INNODB 1.2.x开始支持全文检索，支持MYISAM全部功能，还支持其他一些特性

全文检索通常用倒排索引inverted index实现。
　　在辅助表中存储了单词与单词在一个或多个文档中所在位置之间的映射，通过利用关联数组实现，有两种表现形式：

• inverted file index {单词，单词所在文档ID}
• full inverted index {单词，（单词所在文档ID，具体文档中位置）}

　　inverted file index
　　
　　full inverted index
　　
　　full 存储了单词所在位置。相比之下，占用更多空间，能更好地定位数据。

INNODB采用full inverted index的方式，将（documentId，Position）视为一个ilist。在全文检索的表中，有2个列，一个是word，一个是ilist，在word上设有索引。因为在ilist存放了position，可以进行proximity search，MYISAM不支持
　　倒排索引需要将word存放到一张表中，成为辅助表，为提高全文检索的并行性能，共有6张
　　存放在磁盘上，还有全文检索索引缓存，提高性能
　　 FTS INDEX CACHE是一个红黑树，根据（word，ilist）排序