mysql 索引与算法

匣子
109 阅读23分钟

索引

  • innodb 存储引擎支持一下几种常见的索引：
    • B+树索引 ：B+树索引构造类似于二叉树，根据键值快速找到数据。B+树是关系型数据库查找最为常用和有效的索引。
      B+树索引并不是找到一个给定键值的具体行。B+树只能找到数据行所在的具体页。然后数据库通过页读入到内存，再在内存中进行查找，最后得到数据。
    • 全文索引 ：
    • 哈希索引 ：innodb支持的哈希索引是自适应的，innodb会根据表的使用情况自动为表生成哈希索引，不可认为干预是否在一张生成。

相关算法

  • 二分查找法：
    • 二分查找法也称为折半查找法，用来查找以至于有序的记录数组中的某一记录。
    • 基本思想：将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找到元素值小于该中点元素，则将待查序列缩小为左半部分，否则为右半部分。通过一次比较将查询区间缩小一般。
  • 二叉查找树 ：B+数是通过二叉查找树，再由平衡二叉树，b树演化而来，在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。
  • 平衡二叉树（avl树）：平衡的二叉查找树。平衡二叉树的查找性能是比较高的，只是接近最高性能。

B+树

  • B+树：B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。同时考虑节点增加层数策略。

  • B+树的插入：插入必须保证插入后叶子节点中的记录依然排序 ，B+树并不会急于去拆分叶子节点，而是去做旋转操作。

  • 删除操作： B+树使用填充因子来控制是的删除变化，50%是填充因子课设的最小值。删除操作同样保证删除后的叶子节点中的记录依然排序，同时考虑删除节点策略。

B+树索引

B+树索引在数据库中有一个特点是高扇出性，因此在数据库中，一般高度在2-4层，也就是说寻找某一键值的记录时最多需要2到4次IO。
聚集索引

  • innodb存储引擎表是索引组织表，即表中数据按照主键顺序存放。 而聚焦索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。
    这个特性也决定了索引组织表中数据也是索引的一部分。每个数据页都是通过一个双向链表进行链接的

  • 每张表只能拥有一个聚集索引。在查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
    此外由于定义了数据的逻辑顺序，聚集索引能够特别快地针对范围值的查询。

  • 聚集索引的特点： 主键的排序查找和范围查找效率非常快
    主键排序查找：针对orderBY对记录进行排序时，并不进行filesort操作，这也是聚集索引的特点。
    范围查询:找寻主键某一范围内的数据通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

  • 数据页上存放的是完整的每行的记录而非在数据页的索引页中，存放的仅仅是键值及数据也得偏移量，而不是完整的一行记录。

  • 聚集索引并不是物理上的连续的，而是逻辑上连续的。
    原因有两点： 1数据页通过双向链表链接，页是按照主键的顺序排序；2每个页中的记录也是通过双向链表进行维护的，物理上可以同样不按照主键存储。

辅助索引(非聚集索引)

  • 叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引还包含了一个书签。书签用来告诉innodb哪里可以找到索引相对应的航数据。书签相当于行数据的聚集索引键。

  • 通过辅助索引来寻找数据时，innodb存储引擎会遍历辅助索引并通过页级别的指针获得只想主键索引的主键，然后通过主键索引来找到一个完整的记录。

    延伸： SQL Server有一种称为堆表的表类型，即行数据的存储按照插入的顺序存放。

  • 对于非聚集索引的离散读取，索引组织表上的非聚集索引会比堆表上的聚集索引慢？
    索引组织表通过B+树的中间节点就可以找到要查找的所有页，然后进行读取，而对标的特性决定了这对其是不能实现的。最后非聚集索引的离散读，的确存在上述情况，但是一般数据库都通过预读技术来避免多次的离散读操作。

  • 索引添加操作命令 ： alter table T addkey idx_c(c)

B+树索引的分裂
 innodb存储引擎的page header中有以下几个部分用来存储插入的顺序信息：
    PAGE_LAST_INSERT ，PAGE_DIRECTION ，PAGE_N_DIRECTION通过这些参数来决定是向左还是向右分裂，同时决定讲分裂点记录为哪一个。
B+树索引的管理

  • 索引建立： 一种是alter table 一种是create/drop index
    方法1：ALTER TABLE table_name | ADD {INDEX_KEY}[INDEX_NAME] [INDEX_TYPE] {INDEX_COL_NAME,....}[index_option]
    方法2：CRATE [UNIQUE] INDEX index_name [INDEX_TYPE] ON TABLE_NAME (INDEX_COL_NAME,....)
                 // DROP INDEX index_name ON TABLE_NAME

  • 索引查看： SHOW INDEX FROM TABLE

    • table ：索引所在表
    • non_unique : 非唯一索引，可以看到primary key是0 ，因为必须是唯一的
    • key_name : 索引名称
    • seq_in_index : 索引出现在该列的位置
    • column_name : 索引列名称
    • collation : 列以什么方式存储在索引中。可以是A或者null。B+树索引总是A，即排序的。如果使用了heap存储引擎，并且建立了hash索引，这里就回显示null。hash桶排序后就不用对数据进行排序了。
    • cardinality ： 非常关键的值，表示索引中唯一值的估计值。cardinality表的行数尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。数值为概值，需要更新盖数值，可以使用analyze table命令。
    • sub_part : 是否是列的部分被索引。如果看到idx_b这个索引，这里显示100，表示只对b列的钱100字符进行索引。
    • packed ： 关键词如何被压缩。
    • null ： 是否缩阴的列含有null值。
    • index_type : 索引的类型。innodb只有B+树索引。
    • comment ： 注释

  • cardinality为null ，在某些情况下可能会发生索引建立了却没有用的情况。或者对两条基本一样的语句执行explain，但最终出来的结果不一样：一个使用索引，一个使用全表扫描。

Fast Index Creation
从innodb1.0x开始innodb支持 fast index creation 的索引创建方式 简称FIC

  • 解决问题： myql5.5前数据库对于索引的添加或者删除的这类DDL操作，mysql数据库过程为

    • 首先创建一张新的临时表，表结构为通过命令alter table新定义的结构
    • 然后把原表中数据导入到临时表。
    • 接着删除原表
    • 最后把临时表改名为原来的表名
      这样操作下来效率低，而且会阻塞正在运行的事务。

  • 对辅助索引的创建，innodb存储引擎会对创建索引的表加一个s锁。删除辅助索引操作就更简单了，只需要更新内部视图，并将辅助索引的空间标记为可用，同事删除mysql数据库内部视图上对该表的索引定义即可。必须保证tmp_dir有空间存放临时表。

  • 如果大量事务需要对目标表进行大量的写操作，那么数据库的服务同样不可用，

  • FIC 只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。

Oline Schema Change
在线架构改变(简称OSC) 最早是由facebook实现一种在先DDL的方式。通过php脚本来实现OSC的。
Oline DDL
mysql5.6开始支持Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如INSERT,UPDATE,DELETE这类DML操作，这极大的提高了mysql数据库在生产环境中的可用性。
  • 以下类型的DDL操作也可以通过“在线”的方式进行操作：
    • 辅助索引的创建与删除
    • 改变自增长值
    • 添加或者删除外键约束
    • 列的重命名
  • 用法句式：

    ALTER TABLE tbl_name

    | ADD {INDEX| KEY} [INDEX_NAME]

    {INDEX_TYPE} (INDEX_COL_NAME,...)[INDEX_OPTION]...

    ALGORITHM [=]{DEFAUT |COPY|INPLACE}

    LOCK [=]{DETAULT|NONE|SHARED|EXCLUSIVE}
  • ALGORITHM指定了创建或删除的算法
    • copy标识按照mysql5.1版本前的工作模式，即创建临时表的方式
    • INPLACE表示索引创建或删除操作不需要创建临时表
    • DEFAULT表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法，参数为off标识采用INPLACE。默认为OFF。
  • LOCK部分为索引创建或删除时对表添加锁的情况：
    • none 表示对目标表不添加任何锁，即事务仍然可以进行读写操作不会阻塞以获得最大的并发高度。
    • share 对目标表添加一个S锁，读事务可以并行，但是写操作需要等待，存储引擎不支持share模式，则会报错
    • exclusive 对目标表加上一个X锁，读写事务都不能进行，和copy方式得到的状态类似，但是不需要创建临时表
    • default 会判断当前操作是否可以使用none模式，不能则判断是否可以使用share模式，最后判断可以使用exclusive模式，default会通过判断事务来最大并发性来判断执行DDL的模式。
  • innodb实现Online DDL的原理是在执行或者删除操作的同时，将INS ，UPD,DEL这类操作日志写到一个缓冲中。等完成索引的创建后再将重做应用到表上，以此到达数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制默认128mb。
  • Online DDL在创建完成后通过缓冲日志达到数据的一致性，也就意味着在索引过程中sql优化器会不选择正在创建中的索引。
Cardinality值

  • 对于什么时候添加B+树索引，一般的经验都是在访问表中很少一部分时使用B+树索引才有意义。对于性别字段，地区字段，类型字段，他们的取值范围很小称为地选择性
    如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，此时使用B+树时最合适的。

  • 怎么查看索引是否具有高选择性？ 可以通过show index结果中的cardinality值来观察。cardinality表示索引中不重复记录数量的预估值。cardinality/n_rows_in_table应尽可能接近1。如果非常小，需要考虑是否有必要建立整个索引。

  • cardinality统计信息的更新发生在两个操作中： INSERT和UPDATE。
    更新策略为：
    1.表中1/16的数据已发生变化。
    2.stat_modified_counter>2000000000 (发生数据变化的次数)

  • cardinality 是通过采样来进行信息的统计。
    采样的统计方法：
    1.取得B+树索引中叶子节点的数量，计为A。
    随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2....P8
    通过采样信息给出cardinality的预估值：Cardinality=（P1+P2+....P8）*A/8

  • 当执行SQL语句ANALYZE TABLE,SHOW TABLES STATUS,SHOW INDEX以及访问INFORMATION_SCHEMA架构下的表tables和statistics会导致innodb存储引擎去重新计算索引的cardinality值。若表非常大，执行计算过程会非常慢。

  • innodb1.2版本提供了更多的参数对cardinality进行设置
    innodb_stats_persistent :是否将ANALYZE　TABLE算到cardinality值存放到磁盘上。
    innodb_stats_on_metadata：　TABLES STATUS,SHOW INDEX以及访问INFORMATION_SCHEMA架构下的表tables和statistics是否重新计算cardinality值。
    innodb_stats_persistent_sample_pages：若参数１设置为on该参数表示analyze table　
    更新cardinality值时采样页的数量
    innodb_stats_transient_samples_pages：每次采样页的数量。

联合索引

联合索引时指对表上的多个列进行索引。
  • 本质上来说，联合索引也是一棵B+树，不同的是联合索引的键值是大于等于2。
  • 联合索引自身会对第二个键值进行排序处理。避免查询的时候多一次排序操作，因为叶子节点已经排序了。
  • 延伸： using filesort ，即需要额外的一次排序操作才能完成查询。
  • 联合索引的最左前命中原则:
    索引 （a,b）
    where a='XXX' 可以使用索引
    a='XXX'and b='XXX' 可以使用索引
    b='XXX' AND a='xxx' 可以使用索引，
                                       mysql优化器在执行前会自行调整顺序以适应索引
    where b='XXX' 使用不到索引

覆盖索引

即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

  • 使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，所以大小远小于聚集索引，可以大量减少IO操作。

  • 另一个好处就是对某些统计问题而言的。 count(*) 这类语句优化器会选择辅助索引或者联合索引这些减少IO操作，using index 在这表示为 使用覆盖索引

  • 如果用户选取的数据是整行数据信息的话，索引不能覆盖到我们要查询的信息是，因此查询到指定数据后，还需要一次书签访问来查找整行数据的信息。

  • 对于不能进行索引覆盖的情况，优化器选择辅助索引的情况，通过辅助索引查找的数据是少量的。这是相对硬盘特性来决定的，传统的是利用顺序读来替换随机读的查找。固态的话，随机读操作非常快，同时有足够的自信来确认 使用辅助索引带来更好的性能的话 可以使用 FORCE INDEX来强制使用某个索引。
    句式： select * from tbl_name FORCE INDEX(key_name) where .....

索引提示

mysql 数据库支持索引提示，显式地告诉优化器使用哪个索引。
  • 大致两种情况可能需要INDEX HINT：
    1.mysql优化器错误地选择了某个索引导致很慢，这时候DBA可以强制优化器使用某个索引，新版mysql极少出现这种情况
    2.sql语句可选择的索引过多，这时优化器选择执行计划时间的开销可能大于sql语句本身。
  • INDEX HINT 用法： tbl_name [ as alias] [index_hint_list]....

Multi-Range Read 优化

MySQL5.6开始支持Multi-Range Read （MMR）优化。

MMR优化的目的就是为了减少磁盘的随机访问，并且随机访问转化为顺序的数据访问，

这对IO-bound类型的sql查询语句带来性能极大的提升。

MMR优化可是用于range，ref，eq_ref类型的查询。

  • MRR优化的好处：

  • MRR在查询辅助索引是，首先根据得到的查询结果，按照主键进行排序，并且按照主键顺序的顺序进行书签查找。

  • 减少缓存池中页被替换的次数

  • 批量处理对键值的查询操作

  • 对innodb和myisam的范围查询和join查询操作，MRR的工作方式：

  • 将查询的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的

  • 将缓存中的键值根据Rowid进行排序

  • MRR可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是在拆分过程中，直接过滤一些不符合查询条件的数据

  • 启用MMR优化，优化器可以通过参数optimizer_switch中的标记来控制。当mrr为on时，标识启用MMR优化。mrr_cost_based标记标识是否通过cost based方式来选择是否开启mrr.mrr若设置为off则总是开启MRR优化。
    设置语句：SET @@optimizer_switch='mrr_cost_based=off';

  • 参数read_rnd_buffer_size用来控制键值的缓冲区大小，当大于该值时，执行起对已经缓存的数据根据RowID进行排序，并通过Rowid来取得行数据，该数值莫认为256k。

Index Condition Pushdown（icp）优化

和MRR一样 ICP也是MySQL5.6开始支持的一种根据索引进行查询的优化方式。

ICP开启后，数据库会在取出索引的同事判断是否可以进行where条件过滤，

也就是where的部分过滤操作放在了存储引擎层。

在某些查询下，可大大减少上层SQL层对记录的索取，从而提高数据库的整体性能。

ICP优化支持range，ref，eq_ref,ref_or_null 类型的查询,

当前只支持Myisam，和innodb存储引擎。
  • 开启ICP优化后在索引取出时，就会进行where条件的过滤，然后再去获取记录。这就极大地提高查询的效率。

哈希算法

哈希算法是一种常见算法。时间复杂度为O（1），且不只存在于索引中，每个数据库应用中都存在该数据结构。

哈希表

哈希表也称散列表，有直接寻址表改进而来的。在哈希方式下，元素属于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域U映射到哈希表T[0,m-1]的槽位上。
  • 哈希碰撞：两个关键字可能映射到同一个槽上。在数据库中一般采用最简单的碰撞解决解决技术，这种技术称为链接法。
  • 在链接法中，把散列到同一槽中的所有元素都放在一个表中，槽J有一个指针，它指向由所有散列到J的元素构成的链表的头，如果不存在，则J中卫null。
  • 一般来说都将关键字转换成自然数，然后通过除法散列，乘法散列或全域散列来实现。数据库中一半采用除法散列的方法。
  • 哈希函数的除法散列法中，通过取k除以m的余数，将关键字k映射到m个槽中的某一个去，即哈希函数为：h（k）= k mod m

innodb的哈希算法

innodb 存储引擎使用哈希算法对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的page页都有一个chain指针，它指向相同哈希值的页。而对除法散列，m的取值略大于2倍的缓冲池页数量的质数。缓冲池页一半为1280个槽，而缓冲池页内的哈希表则为1399，所以在启动时分配1399个槽的哈希表，用哈希查询所在缓冲池中的页。
  • 自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速。但是范围查找是无能为力的。
  • 自适应索引是由innodb存储引擎自己控制的，不过可以通过参数innodb_adaptive_hash_index来启用或者禁用启动此特性，默认为开启。

全文检索

将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。在MySQL数据库中，innodb不支持全文检索技术。从innodb1.2开始支持部分全文检索的功能。

倒排索引

全文检索通过使用倒排索引来实现，倒排索引同B+树索引一样也是一种索引结构。它在辅助表中存储了与单词自身在一个或多个文档中所在的位置之间映射。
  • 通常利用关联数组实现，其拥有两种表现形式：
    • inverted file index 其表现形式为{ 单词，单词所在的文档id }
    • full inverted index 其表现型是为 {单词， （单词所在的文档ID，在具体文档中的位置） }

innodb全文检索

在innodb中，将（documentId，position）视为一个‘ilist’。因此在全文检索的表中，有两个列，一个是word字段，另一个是Ilist字段，并且在word字段上设有索引。此外innodb在ilist中存放了position信息，故可以进行proximity search，而myisam不支持此特性。

  • 辅助表（auxiliary table） ：倒排索引需要将word存放到另外一张表中，这个表便称为辅助索引。为了提高并行性能 innodb提供了六张辅助表。

  • auxiliary table 是持久表，村放入磁盘中的。而且还具有FTS index cache （全文检索索引缓存）来提高性能。 FTS是一个红黑树结构，其根据（word，ilist）进行排序。这意味着插入的数据已经更新了对应的表，但是辅助表不一定能及时更新。innodb会批量对辅助表进行更新。

  • innodb允许用户查看指定倒排索引的辅助表中分词的信息，通过innodb_ft_aux_table来观察倒排索引的辅助表。 语句： SET GLOBAL innodb_ft_aux_table='test/fts_a';

  • innodb总是在事务提交时将分词写入到FTS index cache，然后通过批量更新写入到磁盘。如果数据库发声宕机是，在下次重启数据库时，当用户对表进行全文检索（查询或者插入操作）时，innodb会自动读取未完成的文档，然后再进行分词操作，再将粉刺的结果放入到FTS index Cache中。FTS index cache 默认为32m，缓存满时，会将其中的（word ，ilist）分词信息同步到磁盘的辅助表中。

  • FTS documentid 是另外一个重要的概念，在innodb中，为了支持全文检索，必须有一个列与word进行映射，在innodb中这个列被命名为FTS_DOC_ID，其类型必须为bigint unsigned not null，并且在innodb中会自动在列上家入一个名为FTS_DOC_ID_INDEX的唯一索引。

  • 对于删除操作，在事务提交时，不删除辅助表的记录，而只是删除FTS cache Index中的记录。对于辅助表中被删除的记录，innodb会记录其FTS document id，并将其保存在DELETED 辅助表中。

  • 由于文档的删除不删除索引中的数据还会增加DELETED表中的记录，所以允许用户手工将已经删除的记录从索引中彻底删除，改命令就是OPTIMIZE_TABLE。该命令还会重新统计cardinality值。如果删除的文档非常多，命令会占用比较多的时间，影响程序的并发性。用户可以通过innodb_ft_num_word_optimize来限制每次实际删除的分词数量。默认为2000.

  • 彻底删除的文档id会记录到innodb_ft_being_deleted表中。文档id一旦删除，则不允许插入这个文档id，否则会抛出异常。

  • stopword 列表：表示该列表中得word不需要对其进行索引分词操作。innodb有一张默认的stopword列表，其在information_schema架构下，表名为innodb_ft_default_stopword.默认为36个stopword。

  • 此外用户还可以通过参数innodb_ft_server_stopword_table来自定义stopword列表。
    sql语句: create table user_stopword(value varchar(30))engine=innodb
    set global innndb_ft_server_stopword_table="test/user_stopword"

  • 当前innodb的全文检索还存在以下的限制

    • 每张表只能有一个全文检索的索引
    • 由多列组合而成的全文检索的索引列必须使用相同的字符集和排序规则
    • 不支持没有单词界定符的语言，如中文，日语，韩文等。

全文检索

MySQL数据库支持全文检索的查询，其语法为： match（col1，col2） against （expr [search_modifier]）
search modifier ：{ in natural language mode | with query expansion | in boolean mode | with query expansion}
  • 数据库通过 match() ... against()... 语法支持全文检索的查询，match制定了需要被查询的列，against制定了使用何种方法去查询。
查询模式的详解

  • Natural langeuage ： 表示睡袋有指定word的文档。

    语句：select * from tbl_name where match(col) against ('hello' in natural language mode);

    查询返回的结果是根据相关性进行降序排序的，相关性最高的再第一位。

    相关性的计算条件为： word是否在文档出现 ，word在文档出现的次数，word在索引列的数量，多少个文档包含该word

    语句需要注意事项： 查询的word应该不在stopword列表中，查询word的字符长度应该在区间[innodb_ft_min_token_size,..._max_size ]内。两个参数默认为3，84.

  • Boolean： 具有操作符，可以对查询字符进行表达式拼接的模式。

    其操作符有：+ 表示必须存在，-表示word 必须排除，no operator 表示可选，但是如果出现，相关性会更高，。

    @distance 表示查询的多个单词之间的距离是否在distance之内，> 出现该单词增加相关性 < 出现该单词降低相关性 ~ 允许出现该单词单吃出现是负相关性 * 表示该单词开头的单词，如like* " 表示短语

  • Query expansion ：简单理解为扩展查询.通过短语 with query expansion或 in natural language mode with query expansion 可以开启blind query expansion。

    该查询分为两个阶段：第一个阶段： 根据搜索的单词进行全文索引查询。第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询。。

    由于query expansion的全文检索可能带来许多非相关性的查询，因此使用时需要非常谨慎。

avatar
文章
阅读
粉丝