【读书笔记】MySQL存储引擎_第四天

索引与算法

• 索引太多，应用程序性能会受到影响
• 索引太少，查询性能会受到影响

一、 InnoDB存储引擎索引概述

InnoDB存储引擎支持以下几种常见的索引：

1. B+树索引
2. 全文索引
3. HASH索引

注意：

1. InnoDB的哈希索引是自适应的，InnoDB会根据表使用情况自动生成HASH索引，人不能干预
2. B+树是传统意义上的索引，是目前数据库最常用最有效的索引

二、 B+树

2.1 B+树的插入操作

我看过一个讲B+树很好的一个视频

视频中讲述了数据的插入，其中包括页的分裂，很详细生动

2.2 B+树的删除操作

• 当页中数据小于50%时会进行合并操作

三、B+树索引

• B+树索引本质就是B+树在数据库中的实现。
• B+树索引在数据库中有一个特点是高扇出性，因此数据库中B+树高度一般是2~4层，这就是说查找某个键值的行记录时最多只要进行2~4次IO操作。
• 一般的机械硬盘每秒至少100次IO，2~4次IO意味着查询时间只有0.02s到0.04s
• B+树索引分为聚集索引和辅助索引。这两个索引的区别仅仅是叶子节点存放的是不是一整行的信息（辅助索引放的时主键信息并不是完整的一行信息，查询真实的数据有一个回表的过程）

3.1 聚集索引

介绍：

表中的数据按照主键顺序存放。二聚集索引就是按照每张表的主键构建的一颗B+树，同时叶子节点存放的是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。

特点：

1. 每张表只能有一个聚集索引
2. 多数情况下，查询优化器倾向于采用聚集索引

实现：

MySQL [qiushibaike]> create table t(
    -> a int not null, 
    -> b varchar(8000),
    -> c int not null,
    -> primary key (a),
    -> key idx_c(c)
    -> )engine=INNODB;

插入数据：

insert into t select 1,repeat('a',7000),-1;
 
insert into t select 2,repeat('a',7000),-2;
 
insert into t select 3,repeat('a',7000),-3;
 
insert into t select 4,repeat('a',7000),-4;

B+树索引实际存储样子↓

3.2 辅助索引

• 辅助索引的叶子节点不是实际的数据，而是行数据的聚集索引键。
  
• 如图，辅助索引高度为3，主键索引高度也为3。所以具体查找到一个数据需要6次IO操作
• 对比前一次我们的分析，可以看到这次多了一个页。分析page offset为4的页，该页为非聚集索引所在页，通过工具hexdump分析可得：

因为只有4行数据，并且列c只有4个字节，因此在一个非聚集索引页中即可完成，整理分析可得下图所示的关系：

3.3 B+树索引的分裂

ps：对于索引的操作我就不在这里写了，百度一下都能搜得到，这里我着重记一下B+树索引分裂的实现吧。
数据库中B+树索引的分裂并不总是从页的中间记录开始，这样可能会导致空间的浪费，例如：

1, 2, 3, 4, 5, 6, 7, 8, 9

插入是根据自增顺序进行的，若这时插入10这条记录后需要进行页的分裂操作，那么根据B+树对半分裂的规则，会将记录5作为分裂点记录，分裂后得到下面两个页：

P1: 1, 2, 3, 4
P2: 5, 6, 7, 8, 9, 10

由于插入是顺序的，P1这个页中将不再会有记录被插入，从而导致空间的浪费，而P2又会再次分裂

InnoDB存储引擎的Page Header有以下几个部分用来保存插入的顺序信息：

• PAGE_LAST_INSERT：最后插入记录的位置。
• PAGE_DIRECTION：记录插入的方向。假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。
• PAGE_N_DIRECTION：假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。

通过这些信息，InnoDB存储引擎可以决定格式向左还是向右进行分裂，同时决定将分裂点记录为哪一个。若插入是随机的，则取页的中间记录作为分裂点的记录，这和之前介绍的相同。若往同一方向进行插入的记录条数为5，并且目前已经定位到的记录（InnoDB引擎插入时，首先需要进行定位，定位到的记录为待插入记录的前一条记录）之后还有3条记录，则分裂点的记录为定位到的记录后的第三条记录，否则分裂点记录就是待插入的记录。（这里的5和3是固定的，与具体例子无关）

来看一个向右分裂的例子，定位到的记录之后还有3条记录，则分裂点记录如下图所示：

以split record为分裂点记录最终向右分裂得到下图所示的情况：

对于下图的情况，分裂点就为插入记录本身，向右分裂后仅插入记录本身，这在自增插入时是普遍存在的一种情况：

四、Cardinality值

4.1 什么是Cardinality值

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

那么如何知道她是不是高选择性呢？
Cardinality值

Cardinality值表示索引中不重复记录的预估值，需要注意的是他是一个预估值，而不是一个准确值，在实际应用中，Cardinality/n_row_in_table应尽可能的接近1，如果非常小,那用户需要考虑是否还有必要创建这个索引。

4.2 InnoDB存储引擎的Cardinality统计

如果每次索引发生改变时就对其进行Cardinality统计，那么将会给数据库带来很大的负担。

因此，数据库对于Cardinality的统计都是通过采样的方法来完成的。

InnoDB存储引擎内部对更新Cardinality信息的策略为：

• 表中1/16的数据已经发生了改变
• stat_modified_counter > 2*10^9
  这里的stat_modified_counter表示发生变化的次数

默认InnoDB存储引擎对8个叶子节点进行采样，采样过程为：

1. 取得B+树索引中叶子节点的数量，记作A
2. 随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，记为P1,P2……P8
3. 根据采样信息给出Cardinality的预估值：Cardinality=(P1+P2+……+P8)*A/8。

五、B+树索引的使用

一，OLTP概念
OLTP的全称是On-line Transaction Processing，中文名称是联机事务处理。其特点是会有高并发且数据量级不大的查询，是主要用于管理事务（transaction-oriented)的系统。此类系统专注于short on-line-tansactions 如INSERT, UPDATE, DELETE操作。通常存在此类系统中的数据都是以实体对象模型来存储数据，并满足3NF(数据库第三范式)。

OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。

二，OLAP概念
OLAP的全称是 On-line Analytical Processing,中文名称是联机分析处理。其特点是查询频率较OLTP系统更低，但通常会涉及到非常复杂的聚合计算。 OLAP系统以维度模型来存储历史数据，其主要存储描述性的数据并且在结构上都是同质的。

OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

5.1 不同应用中B+树索引的使用

• 对于OLTP应用，B+树索引建立后，对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B+树索引才是有意义的，否则即使建立了，优化器也可能选择不适用索引。
• OLAP中索引的添加根据的应该是宏观的信息，而不是微观，因为最重要的到的结果是提供给决策者的。不过在OLAP应用中，通常会需要对时间字段进行索引，这是因为大多数统计需要根据时间维度来进行数据的筛选。

5.2 联合索引

联合索引 运用的是多个索引列。 创建方法跟单个索引一样。
这么做的好处就是
第一：是使用了B+树索引

第二：已经对第二个键值进行排序了
注意：但是对于单个列查询是不引起联合索引

5.3 覆盖索引

• 从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处就是辅助索引不包含正行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
• InnoDB在1.0之后 或者 MySQL 在5.0或者以下的不支持覆盖索引。

Explain select count(*) from buy_log;

Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。

explain select count(*) from buy_log where buy_date >= '2011-01-01' and buy_date <'2011-02-01'

如果同时出现Using where，代表使用索引来查找读取记录， 也是可以用到索引的，但是需要查询到数据表。(也就是没有用到索引覆盖，他多了一个回表的过程)

5.4 优化器选择不使用索引的情况

当执行 explain命令进行sql语句分析时,就会发现优化器并没有选择索引去查找数据,而是通过扫描聚集索引,也就是直接进行全表的扫描来得到的数据。 这种情况多发生与范围查找、JOIN链接操作等情况下。
可以通过show index from 表名查询索引

mysql> show index from user\G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: id_index
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

ERROR:
No query specified

我自己的数据比较少，实际上数据多的时候，索引类型多的时候，它会将所有的索引类型全部打印出来，包括联合主键。

5.5 索引提示

有时候不知道是否使用到所用，可以用explain命令对查询语句进行分析

mysql> EXPLAIN SELECT * FROM user WHERE name = 'admin' AND password = '123';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

列Extra提示的内容:

1. Using intersect(b,a)表示根据两个索引得到的结果进行求交的数学运算
2. Using where表示优化器需要通过索引回表查询数据；
3. Using index表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；
4. Using index condition 会过滤索引后找到所有符合索引条件的数据行，随后用 where子句中的其他条件去过滤这些数据行

如果我们想让优化器按照某个索引查询，那么我们有两种方法：USE INDEX和FORCE INDEX

SELECT * FROM t USE INDEX(a) WHERE a=1 AND b=2;

SELECT * FROM t FORCE INDEX(a) WHERE a=1 AND b=2;

区别在于：
USE INDEX只是告诉优化器可以选择该索引，实际上优化器会根据自己的判断进行选择。
FORCE INDEX则是表示优化器必须执行用户要求的索引。

5.6 Multi-Range Read优化

MySQL5.6版本开始支持Multi-Range Read (MRR)优化。其目的是为了减少磁盘的随机访问，并且将随机访问转化为顺序的数据访问。MRR适用于range，ref，eq_ref类型的查询。

MRR优化有以下几个好处：

1. MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找
2. 减少缓冲池中页被替换的次数
3. 批量处理对键值的查询操作。

对InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作MRR工作方式如下：

1. 讲查询得到的辅助索引键值存放在一个缓存中，这时缓存重点数据是根据辅助索引键值排序的。
2. 将缓存中的键值根据RowID进行排序。
3. 根据RowID的排序顺序来访问实际的数据文件。

如果InnoDB存储引擎或者MyISAM存储引擎的缓冲池不是足够大，既不能存放下一张表中的所有数据，此时频繁的离散读会导致缓存中的页被替换出缓冲池，然后又不断被读入缓冲池。如果按照主键访问，那就可以将重复来回动作降到最低。

SELECT * FROM salaries WHERE salary > 10000 AND salary < 40000;

上面这一个查询，如果启用Multi-Range Read时，用EXPLAIN进行分析的话，我们在EXTRA这一列除了看到Using index conditin外，还能看到一个Using MRR。实际对比下来，使用MRR执行时间能比不使用快上10倍不止。

Multi-Range Read还可以将某些范围查询，拆分成键值对，以此来进行批量数据查询。这样做的好处是可以再拆分过程中，直接过滤一些不符合查询条件的数据，比如：

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000 AND key_part2 = 1000;

表t中 ( key_part1,key_part2)的联合索引,因此索引根据key_part1,key_part2的位置关系排序。如果没有MRR，此时查询类型为Range，SQL优化器会将key_part1大于1000并且小于2000的数据都取出来，就算他们中的key_part2不等于1000，全部取出来之后，再根据key_part2=1000进行过滤。这导致无用数据大量取出。性能很低。

优化器会将查询条件拆分为（1000,10000）（1001,10000），…，（1999,10000），最后进行数据的查询。性能灰常高！
启动方法:optimizer_switch 中的标记(flag)来控制。 总是开启MRR。

SET @@optimizer_switch = 'mrr=on,mrr_cost_based=off';

read_rnd_buffer_size 用来控制键值的缓冲区大小,默认为256k;
查看命令:

select @@read_rnd_buffer_size\G;

5.7 Index Condition Pushdown (ICP) 优化

• MySQL5.6版本支持这种根据索引进行查询的优化方式.之前的MySQL版本不支持Index Condition Pushdown。
• 当进行索引查询时，首先根据索引来查找记录,然后再根据where条件来过滤记录。 在支持Index Condition Pushdown后，MySQL数据库会取出索引的同时，判断是否进行where条件的过滤，也就是将where的部分过滤操作放在了引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。

六、哈希算法

这个我觉得没啥好说的，就是根据等值进行查询

七、全文索引

全文检索(full_test search)是将存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术。它可以根据需要获取全文中有关的章、节、段、句等信息，也可以进行各种统计和分析。

InnoDB在1.2.X开始之前支持全文检索，其支持MyISAM存储引擎的全部功能，并且还支持其他的一些特性。

如：

select * from 表名 where title like '中午%'; 

这个语句是可以通过 B+树索引进行查询

如：

select * from 表名 where title like '%中午%'; 

这个就不是B+树能完成的工作了。

6.1 倒序索引

全文检索通常使用倒排索引（inverted index）来实现。倒排索引在辅助表（auxiliary table） 中存储了单词与单词自身在一个或者多个文档中所在位置之间的映射。利用相关数组实现，其拥有两种表现形式：

1. inverted file index，其表现形式为{ 单词,单词所在的文档的ID}
2. full inverted index,其表现形式为{ 单词,( 单词,单词所在的文档的位置)}

6.2 InnoDB全文索引

InnoDB存储引擎的全文检索还存在以下的限制：

1. 一个表只能有一个全文检索的索引。
2. 有多列组成的全文检索的索引列必须使用相同的字符集与排序规则。
3. 不支持没有单词界定符（delimiter） 的语言。如中文、日语、韩语等。

设置全文搜索:

ALTER TABLE  `表名` ADD FULLTEXT (`字段名`)

MySQL数据库之前全文检索(Full-Text Search)的查询，其语法为：

MATCH (col1,col2,....) AGAINST (expr [search_modifier])
search_modifier:
{
 IN NATURAL LANGUAGE MODE 
 | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
 | IN BOOLEAN MODE
 | WITH QUERY EXPANSION
}

全文检索沟通过MATCH函数进行查询，默认采用Natural Language模式，其表示查询带有指定word的文档。对于创建的表fts_a，查询body 字段中带有pease的文档，若不使用全文索引技术，则允许使用下述sql语句：

select * from fts_a where body like '%Pease%’;

显然上述sql语句不能使用B+树索引.若采用全文检索技术,可以用下面的sql语句进行查询:

select * from fts_a 
where match(body)
againist ('Pottidge' in natural language mode);

默认是in natural languagemode 所以可以省略:select * from cco_images where match(label) against (‘Romanesco’);

查询的范围结果是根据相关性（relevance）进行降序排序的，即相关性最高的结果放在第一位。相关性的值是一个非负的浮点数字，0表示没有任何的相关性。根据mysql官方文档可知，相关性计算根据以下四个条件：

1. word是否在文档中出现。
2. word在文档中出现的次数。
3. word在索引列中的数量。
4. 多少个文档包含该word。

参考

• https://www.cnblogs.com/muuu520/p/13182359.html
• 《MySQL技术内幕——InnoDB存储引擎》
• OLAP和OLTP基本概念和主要区别