时间：2021-07-01 10:21:17 帮助过：10人阅读
OLTP（on-line transaction processing）翻译为联机事务处理， OLAP（On-Line Analytical Processing）翻译为联机分析处理，从字面上来看OLTP是做事务处理，OLAP是做分析处理。从对数据库操作来看，OLTP主要是对数据的增删改，OLAP是对数据的查询。
OLTP查询一小部分数据，可能在10条之内，此时通过B+树索引查询数据才有意义，否则优化器也可能不选择索引
OLAP：通常对时间字段进行索引
对表上多个列进行索引，键值按列的顺序根据每一列的大小进行排序
例子：
create table t (
a int,
b int,
primary key(a),
key idx_a_b(a, b)
)engine = innodb;
select * from table where a = xxx and b = xxx;
select * from table where a = xxx;
上面两条查询语句是可以使用联合索引的
select * from table where b = xxx;
而这条便不能
联合索引也已经对第二个键值进行了排序处理，当查询第一个键值下的所有数据，此时第二个键值已进行排序，不需要额外的文件排序
直接从辅助索引中可以查询到的记录，不需要从聚集索引查询
辅助索引不包含整行的记录的所有信息，大小远小于聚集索引，减少IO操作
对于某些统计问题而言，辅助索引是很好的选择
多发生于范围查找、JOIN等操作
不能使用索引覆盖时，优化器选择辅助索引的情况是通过辅助索引查找的数据是少量的
关键字FORCE INDEX强制使用某个索引
索引提示index hint，显式告诉优化器使用哪个索引
优化器错误使用了某个索引，sql语句运行慢
sql语句可以选择的索引多，优化器选择执行计划时间的开销大于sql语句本身
索引器还是可能根据自己的判断选择其他索引，所以最可靠的是使用FORCE INDEX
目的是减少磁盘随机访问，转为较为顺序的访问，对应io密集型的sql查询有提升，可适用于range、ref、eq_ref
好处：
innodb和myisam引擎的范围查询和join查询操作，工作方式如下：
还可以将某些范围（如a一个范围,b一个等值）查询拆分为键值对（a的一个值对应b的等值），以此进行批量查询
是否启用：
mysql> show variables like ‘optimizer_switch‘\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on
1 row in set, 1 warning (0.00 sec)
mrr=on,mrr_cost_based=on
表示通过cost based的方式来决定是否启用优化，将mrr_cost_based改为off表示总是启用优化
参数read_rnd_buffer_size控制键值缓冲区大小，大于该值则对已经缓存的数据根据rowid进行排序，默认256K
mysql> show variables like ‘read_rnd_buffer_size‘;
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set, 1 warning (0.11 sec)
不支持时：根据索引来查找记录，再根据where来过滤记录
支持时：在取出索引的同时判断是否可以进行where过滤
ICP支持range、ref、eq_ref、ref_or_null类型的查询优化
时间复杂度O(1)
直接寻址表：
哈希表：
链表法解决哈希碰撞：
数据库一般采用除法散列的方法
h(k) = k mod m
对于缓冲池页的哈希表，每个page页有一个chain指针，指向相同哈希函数值的页
对于除法散列，m的取值为略大于 2 倍的缓冲池页数量的质数
例如innodb_buffer_pool_size为 10M，即 640 * 16KB
640 * 2 = 1280，略大的质数为1399，所以会分配1399个槽的哈希表
将查找的页转化为自然数：
表空间id space id，页偏移量 offset
关键字 K = space id << 20 + space id + offset
由数据库自身创造，只适合等值索引
innodb_adaptive_hash_index来控制是否开启
mysql> show variables like ‘innodb_adaptive_hash_index‘;
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON |
+----------------------------+-------+
1 row in set, 1 warning (0.00 sec)
在辅助表中存储了单词与单词自身在一个或多个文档所在位置之间的映射
通常是关联数组实现
inverted file index {单词，单词所在文档的ID}
full inverted index {单词，（单词所在文档ID，在具体文档中的位置）}
full inverted index还存储了单词的所在信息，虽然占用了更多的空间，但是能更好低定位数据，并扩充了一些其他搜索特性
采用full inverted index方式
将(DocumentID，Position)视为一个 ‘ilist‘
在全文检索表中有两列，word字段与ilist字段，在word字段上有索引
因为在ilist字段上有位置信息，因此可以进行proximity search（邻近搜索）
此表称辅助表（Auxiliary Table），是持久表，在innodb中有6张表，每张表根据word的Latin编码进行分区
FTS Index Cache，全文检索索引缓存，提高检索性能
红黑树，根据(word,ilist)排序，插入数据更新对应的表，但全文索引的更新在分词操作之后还在FTS Index Cache中，再批量写入Auxiliary Table中
查询时会将在FTS中的word字段合并到辅助表之后再进行
操作类似Insert Buffer操作，作用相似，提供性能
需要设置参数才能观察倒排索引的Auxiliary Table信息
set global innodb_ft_aux_table = ‘表名 / fts_a‘;
innodb总在事务提交时将分词写到FTS中，再延时批量写入磁盘，提高性能
关闭时FTS的数据会被同步到辅助表中，但是当宕机时部分数据可能未被同步，于是下次重启时，用户全文检索时innodb会继续读取未完成文档，进行分词操作，再写入FTS index Cache中
参数innodb_ft_cache_size控制FTS Index Cache大小，mysql5.6是32MB，8.0居然800 0000字节，奇奇怪怪
mysql> show variables like ‘innodb_ft_cache_size‘;
+----------------------+---------+
| Variable_name | Value |
+----------------------+---------+
| innodb_ft_cache_size | 8000000 |
+----------------------+---------+
1 row in set, 1 warning (0.00 sec)
增大会提高性能，但宕机后恢复时间需要更长
FTS Document ID：与word映射，类型为BIGINT UNSIGNED NOT NULL，会自动加上FTS_DOC_ID_INDEX的唯一索引
文档中分词的插入操作是在事务提交时完成，删除操作则是删除FTS Cache Index的记录，辅助表中的被删除的记录则是会被记下FTS Document ID，加入到DELETED auxiliary table中
设置innodb_ft_aux_table参数可以在information_schema架构下的表INNODB_FT_DELETED来查看删除的FTS Document ID
因为索引中的数据不会被删除，于是应用程序臃肿，可以使用optimize table命令将已经删除的记录彻底从索引中删除
optimize table进行的操作：
innodb_optimize_fulltext_only 为On则可进行删除操作innodb_ft_num_word_optimize可限制实际删除的分词数量，减少占用时间，默认2000
例子：
mysql> use learn_mysql;
Database changed
mysql> create table fts_a(
-> FTS_DOC_ID bigint unsigned auto_increment not null,
-> body text,
-> primary key(FTS_DOC_ID)
-> );
Query OK, 0 rows affected (1.28 sec)
mysql> insert into fts_a select null,‘pease porridge in the pot‘;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into fts_a select null,‘pease porridge hot,pease porridge cold‘;
Query OK, 1 row affected (0.42 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into fts_a select null, ‘Nine days old‘;
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into fts_a select null, ‘Some like it hot, some like it cold‘;
Query OK, 1 row affected (0.26 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into fts_a select null, ‘Some like it in the pot‘;
Query OK, 1 row affected (0.15 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into fts_a select null, ‘Nine days old‘;
Query OK, 1 row affected (0.34 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into fts_a select null, ‘I like cold days‘;
Query OK, 1 row affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> create FULLTEXT INDEX idx_fts on fts_a(body);
Query OK, 0 rows affected (3.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from fts_a;
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 1 | pease porridge in the pot |
| 2 | pease porridge hot,pease porridge cold |
| 3 | Nine days old |
| 4 | Some like it hot, some like it cold |
| 5 | Some like it in the pot |
| 6 | Nine days old |
| 7 | I like cold days |
+------------+----------------------------------------+
7 rows in set (0.03 sec)
mysql> set global innodb_ft_aux_table=‘learn_mysql/fts_a‘;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.INNODB_FT_INDEX_TABLE;
+----------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+----------+--------------+-------------+-----------+--------+----------+
| cold | 2 | 7 | 3 | 2 | 34 |
| cold | 2 | 7 | 3 | 4 | 31 |
| cold | 2 | 7 | 3 | 7 | 7 |
| days | 3 | 7 | 3 | 3 | 5 |
| days | 3 | 7 | 3 | 6 | 5 |
| days | 3 | 7 | 3 | 7 | 12 |
| hot | 2 | 4 | 2 | 2 | 15 |
| hot | 2 | 4 | 2 | 4 | 13 |
| like | 4 | 7 | 3 | 4 | 5 |
| like | 4 | 7 | 3 | 4 | 18 |
| like | 4 | 7 | 3 | 5 | 5 |
| like | 4 | 7 | 3 | 7 | 2 |
| nine | 3 | 6 | 2 | 3 | 0 |
| nine | 3 | 6 | 2 | 6 | 0 |
| old | 3 | 6 | 2 | 3 | 10 |
| old | 3 | 6 | 2 | 6 | 10 |
| pease | 1 | 2 | 2 | 1 | 0 |
| pease | 1 | 2 | 2 | 2 | 0 |
| pease | 1 | 2 | 2 | 2 | 19 |
| porridge | 1 | 2 | 2 | 1 | 6 |
| porridge | 1 | 2 | 2 | 2 | 6 |
| porridge | 1 | 2 | 2 | 2 | 19 |
| pot | 1 | 5 | 2 | 1 | 22 |
| pot | 1 | 5 | 2 | 5 | 20 |
| some | 4 | 5 | 2 | 4 | 0 |
| some | 4 | 5 | 2 | 4 | 18 |
| some | 4 | 5 | 2 | 5 | 0 |
+----------+--------------+-------------+-----------+--------+----------+
27 rows in set (0.00 sec)
mysql> delete from learn_mysql.fts_a where fts_doc_id=7;
Query OK, 1 row affected (0.19 sec)
mysql> select * from information_schema.INNODB_FT_deleted;
+--------+
| DOC_ID |
+--------+
| 7 |
+--------+
1 row in set (0.10 sec)
mysql> set global innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table learn_mysql.fts_a;
+-------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+----------+----------+----------+
| learn_mysql.fts_a | optimize | status | OK |
+-------------------+----------+----------+----------+
mysql> select * from information_schema.INNODB_FT_being_deleted;
+--------+
| DOC_ID |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)
mysql> insert into learn_mysql.fts_a select 7, ‘I like this days‘;
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID
stopword列表表示该列表中的word不需要对其进行索引分词操作
innodb有一张默认stopword列表，information_schema.innodb_ft_datault_stopword，36个词
可通过参数innodb_ft_serve_stopword_table来自定义
全文索引存在以下限制：
语法：
查询模式：
默认模式，表示查询带有word的文档
不使用全文索引：
mysql> select * from fts_a where body like ‘%pease%‘;
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 1 | pease porridge in the pot |
| 2 | pease porridge hot,pease porridge cold |
+------------+----------------------------------------+
2 rows in set (0.00 sec)
使用全文检索
mysql> select * from fts_a
where match(body)
against (‘Porridge‘ in natural language mode);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 2 | pease porridge hot,pease porridge cold |
| 1 | pease porridge in the pot |
+------------+----------------------------------------+
2 rows in set (0.22 sec)
分析：
mysql> explain select * from fts_a
-> where match(body)
-> against (‘Porridge‘ in natural language mode) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: fts_a
partitions: NULL
type: fulltext
possible_keys: idx_fts
key: idx_fts
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)
使用了全文索引的倒排索引
结果根据相关性降序排序，相关性的值是非负的浮点数，0表示没有任何相关性，四个计算条件
得到match()函数结果数量:
mysql> select count(*) from fts_a where match(body) against (‘Porridge‘ in natural language mode);
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.10 sec)
mysql> select count(if(match(body) against (‘Porridge‘ in natural language mode),1,null)) as count from fts_a;
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.05 sec)
第二句更快，第一句需要相关性排序
查看相关性：
mysql> select fts_doc_id,body,match(body) against (‘Porridge‘ in natural language mode) as relevance from fts_a;
+------------+----------------------------------------+--------------------+
| fts_doc_id | body | relevance |
+------------+----------------------------------------+--------------------+
| 1 | pease porridge in the pot | 0.2960100471973419 |
| 2 | pease porridge hot,pease porridge cold | 0.5920200943946838 |
| 3 | Nine days old | 0 |
| 4 | Some like it hot, some like it cold | 0 |
| 5 | Some like it in the pot | 0 |
| 6 | Nine days old | 0 |
+------------+----------------------------------------+--------------------+
6 rows in set (0.00 sec)
全文检索需要考虑某些因素：
相关性为0
区间外不搜索
in boolean mode
+ 表示该word必须存在
- 表示该word必须排除
没有符号表示该word是可选的，如果出现相关性会更高
@distance表示查询的多个单词之间的距离在distance之内，单位字节，此种方式称Proximity search
>表示出现word时增加相关性
<降低相关性
~相关性为负（允许为负）
*表示表示以该单词开头的单词
‘’表示短语
扩展查询，查询关键词短，需要隐含知识
如查询‘database‘时不仅需要查到包含‘database‘的文档，可能还需要包含MySQL，oracle，dbd2等等
with query expansion开启
两阶段查询：
数据库系统区别于文件系统的关键特性，锁机制用于管理对共享资源的并发访问
latch：
lock:
查看latch：
mysql> show engine innodb mutex;
+--------+----------------------------+---------+
| Type | Name | Status |
+--------+----------------------------+---------+
| InnoDB | rwlock: dict0dict.cc:1035 | waits=1 |
| InnoDB | sum rwlock: buf0buf.cc:781 | waits=4 |
+--------+----------------------------+---------+
2 rows in set (0.10 sec)
debug版本下的latch：
详细信息：
lock：
show engine innodb status\G
information_schema下的innodb_trx，innodb_locks，innodb_lock_waits
两种标准的行级锁：
S锁仅与S锁兼容，X锁与任何锁不兼容
兼容指对同一记录锁的兼容性情况
innodb支持多粒度锁定，允许行级和表级的锁同时存在，因为引入了意向锁，将锁定的对象分为多个层次，意味着事务希望在更细的粒度上加锁
将上锁的对象看成一棵树，如果对最下层的对象上锁，即对最细粒度的对象（行）上锁，首先需要对粗粒度的对象上锁
例如对页上的记录上X锁，则需要对页、表、数据库上意向锁IX，最后对记录上X锁，如果任何一个部分等待，则需要等待粗粒度锁的完成
假如对记录r上X锁之前，有事务对表1上了S锁，由于IX锁与S锁不兼容，所以需要等待S锁完成操作后解除
意向锁为表锁，两张意向锁：
兼容性如下：
通过innodb_trx表查看事务情况：
通过innodb_locks查看锁：
当范围查找时lock_data返回第一行的主键
当缓冲池刷出该值代表的页时，其同样会显示NULL
事务小时可根据上述两个表判断，当事务大时需要通过innodb_lock_waits来查看：
可以通过对innodb_trx，innodb_locks，innodb_lock_waits联合查询得到更直观信息
21-5-01_innodb内幕
标签：键值 次数 等等 filter 隔离级别 记录锁 global bat 工作方式