spring boot 使用mongodb联表查询并分页 mongodb联表查询效率怎么样

一、索引

1.索引的作用

提供了类似于书中目录的作用,目的是为了优化查询

2.索引的种类

B树索引
Hash索引
R树
Full text
GIS

二、Btree介绍及算法结构图

1.介绍

遍历--->二叉树--->平衡二叉树--->Balance Tree

1.1Btree查找算法图

1.2Btree种类

B-Tree
B+Tree：在范围查询方面提供了更好的性能(> < >= <= like)
B*Tree

四、MySQL B+Tree索引构建过程

1.聚簇索引BTree结构（InnoDB独有）

1.簇？
区 extent ===》 簇 ===》 64个pages ===》 1M

作用：
	有了聚簇索引之后，将来插入的数据行，在同一个区内，都会按照id值的顺序，有序在磁盘存储数据
	
构建前提：
	1.建表时，指定了主键列，MySQL，InnoDB会将主键作为聚簇索引列，比如id not null primary key
	2.没有指定主键，自动选择唯一键（unique）的列，作为聚簇索引
	3.以上都没有，生成隐藏聚簇索引

1.1画图说明聚簇索引的构建过程

#1.前提
(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

2.辅助索引BTree结构

1.说明：
使用普通列作为条件构建的索引，需要人为创建
2.作用：
优化非聚簇索引列之外的查询条件的优化

2.1画图说明

2.2辅助索引细分

#1.单列索引
#2.多列索引
#3.联合索引
说明：由多列构建一个索引，必须注意最左原则。idx(a,b.c)
1.查询条件中，必须包含最左列，上面的例子就是a列
2.建索引的时候，一定要选择重复值最少的列，作为最左列
--例如：idx（a,b,c）----> a  ab  abc
---全部覆盖：
select * from t1 where a= and b= and c=
select * from t1 where a in and b in  and c in=
select * from t1 where b= and c= and a=
select * from t1 where a and b order c
---部分覆盖：
select * from t1 where a= and b=
select * from t1 where a=
select * from t1 where a= and c=
select * from t1 where a= and b > < >= <= like  and  c =
select xxx from t1 where a  order by b
---不覆盖
bc
b
c
#4.前缀索引(取前五个字符去创建索引,模糊查询)
前缀索引是针对于，我们所选择的索引列值长度过长，会导致索引树高度增高
所以可以选择大字段的前面部分字符作为索引生成条件
会导致索引应用时，需要更多地索引数据页。
MySQL中建议索引树高度3-4层
mysql> alter table city add index id_d(district(5));

2.3 辅助索引(S)怎么构建B树结构的?

(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id  name  age  gender
select  *  from  t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

3. 聚集索引和辅助索引构成区别

聚簇索引只能有一个,非空唯一,一般时主键
辅助索引,可以有多个,时配合聚集索引使用的
聚簇索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构

4.B+tree索引树高度影响因素

1.索引字段较长----->前缀索引
2.数据行过多------->分区表，归档表（pt,archive）,分布式架构（大企业）
3.数据类型--------->选择合适的数据类型

五、索引的管理命令

1.什么时候创建索引

并不是将所有的列都创建索引，不是索引越多越好
按照业务语句的需求创建合适的索引
将索引建立在，经常 where，group by，order by，join on....的条件

#为什么不能乱建索引？
1.如果冗余索引过多，表的数据变化的时候，很有可能会导致索引频繁更新，会阻塞很多正常的业务更新的请求
2.索引过多，会导致优化器选择出现偏差

2.管理命令

1.查询表的索引情况
mysql> desc city;
key:  PRI聚簇索引（主键索引）   MUL（辅助索引）  UNI（唯一索引）
--详细查看索引情况：
mysql> show idenx from city;

2.建立索引
--分析业务语句：select * from city where name='shanghai';
--建立索引：
mysql> alter table city add index idx_na(name);
mysql> alter table city add index idx_n_C(name,countrycode);
mysql> alter table city add index id_d(district(5));---> 前缀索引

3.删除索引
mysql> alter table city drop index idx_na;

--可以完整的查看索引
mysql> explain  select * from city where name ='wuhan';
#type是ALL的话，就是全表扫描，不走索引，如果是ref的话就是走索引

3.压力测试

1.导入100W的测试表
mysql> source t100w.sql

2.压测命令(并没有建立任何索引)
mysqlslap --defaults-file=/etc/my.cnf 
--concurrency=100 --iterations=1 --create-schema='test'
--query="select * from test.t100W where k2='780p'" engine=innodb
number-of-queries=200 -uroot -p -verbose

3.建立合适索引
mysql> alter table t100w add index idx_k2(k2);

4.再次压测
mysqlslap --defaults-file=/etc/my.cnf 
--concurrency=100 --iterations=1 --create-schema='test'
--query="select * from test.t100W where k2='780p'" engine=innodb
number-of-queries=200 -uroot -p -verbose

问题

1.回表查询

--1.1数据库中的表长什么样？
MySQL用来存储数据行的逻辑结构，表的数据行最终存储到了很多page上
InnoDB存储引擎，会按照聚簇索引有序的组织存储表数据到各个区的连接页上
这些连续的数据页，成为了聚簇索引的叶子节点，可以认为聚簇索引就是原表数据
所以，回表就是回聚簇索引

--1.2什么时候回表？
辅助索引：将辅助索引列值+ ID主键值，构建辅助索引BTree结构
用户使用，辅助索引列，辅助索引作为条件查询时，首先扫描辅助索引的BTree树
	1.如果辅助索引能够完全覆盖我们的查询结果时，就不需要回表了
	2.如果不能完全覆盖到，只能通过得出的iD主键值，回到聚簇索引（回来）扫描，最终得到想要的结果

--1.3回表会带来什么影响？
	1.IO量级变大
	2.IOPS（IO次数）会增大
	3.随机IO会增大
	
--1.4怎么减少回表
	1.将查询尽可能用id主键查询
	2.设计合理的辅助索引
	3.更加青雀的查询条件+联合索引
	select * from t1 where name ='张珊' and addr ='北京';
	4.查询越精确，回表越少
	5.优化器算法：MRR（扩展内容）
#完全覆盖
select name,age,gender from t1 from student where name='张珊';

2.更新数据时，会对索引有影响吗，数据的变化会使索引实时更新吗？

#比如insert，update，delete数据
对于聚簇索引会立即更新
对于辅助索引，不是实时更新的

在InnoDB内存结构中，加入了insert buffer（会话），现在版本叫做change
change buffer 功能是临时缓冲辅助索引需要的数据更新
当我们需要查询新insert的数据，会在内存中进行merge（合并）操作，此时辅助
索引就是最新的

3.怎么知道用户在党文的时候，走了我们设置的索引，遇到双11那种大量访问是，索引设置不及时，应该如何解决，如何知道用户经常访问的数据时哪些？

双十一的时候，并发度太高，提前1-2周将热点商品数据，灌入到Tair（redis，memcached）集群中KAFKA

六、执行计划分析

1.什么是执行计划

select * from t1 where name='张珊';

分析的是优化器按照内置的cost计算算法，最终选择后的执行计划

#什么是cost？
代价，成本
对于计算机来讲，代价是什么？
IO，CPU，MEM

1.1查看执行计划

mysql> explain select * from world.city;
mysql> desc select * from world.city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4047 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

1.2执行计划显示结果的分析

table                  ：此次查询设计到的表
type                   ：查询类型：全表扫描，索引扫
possible_keys          ：可能会用到的索引
key                    ：最后选择的索引
key_len                ：索引覆盖长度
rows                   ：此次查询需要扫描的行数
Extra                  ：额外的信息

1.3输出信息介绍

1.3.1 table：此次查询用到的表，针对一个查询中多个表示，精确到问题表

SELECT country.name, FROM city JOIN country 
ON city.countrycode=country.code
WHERE city.population='CHN';

1.3.2type 查询类型

全表扫描：不用任何的索引  ALL
#例如以下的栗子：
mysql> desc select * from  city;
mysql> desc select * from city where 1=1;
mysql> desc select * from city where countrycode like '%ch%';
mysql> desc select * from city where countrycode not in('CHN','USA');

索引扫描：index < range < ref < eq_ref < const(system)--->越往右，级别就越高
--index:全索引扫描
mysql> select countrycode from world.city;

--range：索引范围查询：> < >= <= like in or between and 
mysql> desc select * from city where id < 10;
mysql> desc select * from city where countrycode like 'CH%';
mysql> desc select * from city where countrycode in ('CHN','USA'); -----> 可以改写为union all
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';

特殊情况：查询条件条件作为主键时
mysql> desc select * from city where id != 10;
mysql> desc select * from city where id not in (10,20);

--ref：辅助索引等值查询
mysql> desc select * from city where countrycode='CHN';

--eq_ref：多表链接中，非驱动表链接条件是主键或者是唯一键
A join B on a.xx=b.xx
SELECT country.name, 
FROM city 
JOIN country 
ON city.countrycode=country.code
WHERE city.population='CHN';

--const（system）：聚簇索引等值查询
mysql> desc select * from city where id=10;

1.3.3 possible_keys ，key

possible_keys：可能会走的索引，所有和此次查询有关的索引
key：          此次查询选择的索引

1.3.4：key_len 联合索引覆盖长度

#1.对于联合索引index(a,b,c)
我们希望将来的查询语句，对于联合索引应用越充分越好
key_len 可以帮助我们判断，此次查询，走了联合索引的几部分

===============================================
#2.key_len的计算：idx(a,b,c)
--假设某条查询可以完全覆盖三列联合索引，例如：
select * from t1 where a=  and  b=  and c=
key_len=a长度 + b长度 + c长度
---长度指的是什么？
长度收到：数据类型，字符集影响
长度指的是，列的最大储值字节长度
#数字： 
                    not null      没有not null
	tinyint         1               1+1
	int             4               4+1
	bigint          8               8+1
key_len:
a int not null  -----> 4
a int           -----> 5

#字符：utf8 ------> 一个字符最大占3个字节
                 not null        没有not null
char(10)         3*10            3*10+1
varchar(10)      3*10+2          3*10+2+1
b char(10)   not null  30
b char(10)             31
c varchar(10) not null 32
c varchar(10)          33

#栗子：
create table t1(
a inrt not null，          4       
b int,                     5
c char(10) not null,       40
d varchar(10)              43
)charset = utf8mb4
index(a,b,c,d)
--查询完全覆盖到4列索引，key_len是多少？
key_len=92
mysql> desc select * from t1 where a=1 and b=1 and c='a' and d='a';
mysql> desc select * from t1 where a=1 and b=1 and c='a';
mysql> desc select * from t1 where a=1 and b=1;
mysql> desc select * from t1 where a=1;

1.3.5 extra

using filesort：表示此次查询使用到了文件排序，说明在查询中的排序操作：order by group by distinct...
mysql> desc select * from city where countrycode='CHN' order by population;

2.索引应用规范

2.1建立索引的原则（运维规范）

2.1.0 说明

为了使索引对的使用效率更高，在创建索引时，必须考虑在那些字段上创建索引和创建什么类型的索引，那么索引设计原则又是怎么样的？

2.1.1

建表时一定要有主键，一般是个无关列

2.1.2 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录
例如：学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快的确实某个学生的信息
如果使用姓名的话，可能存在同名现象，从而降低查询速度

优化方案

1.如果非得使用重复值较多的列作为查询条件（例如：男女），可以将表逻辑拆分
2.可以将此列和其他的查询类，做联和索引
select count(*) from world.city;
select count(distinct countrycode,population) from world.city;
select counnt(distinct,countrycode,population) from world.city;

2.1.3

*** 为经常需要where，order by，group by,join on等操作的字段
排序操作会浪费很多时间
where A B C   ----->  A B C 
in
where A group by B order C 
A,B,C

#如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引

2.1.4

尽量使用户前缀来做索引

2.1.5限制索引的数目

索引的数目不是越多越好
可能会产生的问题：
1.每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大
2.修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新变的浪费时间
3.优化器的负担会很重，有可能会影响到优化器的选择
percona-toolkit中有个工具，专门分析索引是否有用

2.1.6删除不再使用或者很少使用的索引（percona tollkit）pt-duplicate-checker

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要
数据库管理员应当定期找出这些索引，将他们删除，从而减少索引对更新操作的影响
9.1.7  大表加索引，要在业务不繁忙期间操作
9.1.8  尽量少在经常更新值的列上建索引
9.1.9  建索引原则
1.必须要有主键，如果没有可以作为主键条件的列，创建无关列
2.经常作为where 条件列 order by group by join on distinct的条件（业务：产品功能+用户行为）
3.最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引
4.列值长度较长的索引列，我们建议使用前缀索引
5.降低索引条目，一方面不要创建没用索引，不常使用的索引清理，percona，toolkit
6.索引维护要避开业务繁忙期

2.2 不走索引的情况（开发情况）

2.2.1没有查询条件，或者查询条件没有建立索引

select * from tab;      全表扫描
select * from tab where1=1；
在业务数据库中，特别是数据量比较大的表
是没有全表扫描这种需求
1.对用户查看是非常痛苦的
2.对服务器来讲是毁灭性的
1）select * from tab;
SQL改写成一下语句：
select * from tab where name ='zhangsan'    name列没有索引
#改：
1.换成有索引的列作为查询条件
2.将name列建索引

2.2.2查询结果集是原表中的大部分数据，应该是15-30%

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了
与数据库的预读能力有关，以及一些参数有关。

#例如：tab表id，name id：1-100W ，id列有（辅助索引）
select * from tab where id > 500000;
如果业务允许，可以使用limit控制

怎么改写？
结合业务判断，有没有更好的方式，如果没有很好的改写方案
尽量不要在mysql存放这个数据了，放到redis里面

2.2.3索引本身失效，统计数据不真实

索引有自我维护的能力
对于表内内容变化比较频繁的情况下，有可能会出现索引失效
一般是删除重建

现象：
有一条select语句平常查询的时候很快，突然有一天会很慢，回事什么原因？
select？----->索引失效，统计数据不真实
DML？-------->锁冲突

2.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括（+ - * / !）

栗子：
--错误的栗子：select * from test from where id-1=9；
--正确的栗子：select * from test where id=10；
算数运算
函数运算
子查询

2.2.5 隐藏转换导致索引失效，这一点应当引起重视，也是开发中经常会犯的错误，这样会导致索引失效，错误的栗子

mysql> alter table tab add index inx_tel(telnum);
mysql> desc tab;

2.2.6 <> not in 不走索引（辅助索引）

explain select * from teltab where telnum <> '110';
explain select * from teltab where telnum  not in ('110','119');
mysql> explain select * from  tab where telnum <> '15555';
单独的>,<，in 有可能走，也有可能不走，和结果采集有关，尽量结合业务添加limit；

--or或者in 尽量改成union,使用不同的条件，分别测试

explain select * from teltab where telnum='110'
union all
select * from teltab where telnum='119';

2.2.7 like "%_"百分号在最前面不走

explain select * from teltab where telnum like  '31%' 走range索引扫描
explain select * from teltab where telnum like '%110' 不走索引

---%linux%类的搜索需求，可以使用elasticearch或MongoDB 专门做搜索服务的数据库产品

3.扩展：优化器针对索引的算法

3.1MySQL索引的自由化-AHI（自使用哈希索引）

MySQL的InnoDB引擎，能够创建只有BTree

AHI的作用： 自动评估“热”的内存索引page，生成HASA索引帮助InnoDB加速读取索引页，加快索引读取的速度 ----> 相当于索引的索引

3.2MySQL索引的自由化-change buffer

比如insert，update，delete数据
对于聚簇索引会立即更新
对于辅助索引，不是实时更新的
在InnoDB内存结构中，加入了insert buffer，现在版本叫change buffer
change buffer 功能是临时缓冲辅助索引需要的数据更新
当我们需要查询新insert的数据，会在内存中进行merge（合并）操作，此时辅助索引就是最新的

3.3 ICP索引下推

作用：
解决了，联合索引只能部分应用情况
为了使减少没必要的数据页被扫描
将不走索引的条件，在engine层去数据之前先做将不走索引的条件，在engine层去数据之前先做C二次过滤
一些无关的数据就会被提前过滤掉

index(a,b.c) 
select * from t1 where a= and c=

在server层先做a列过滤条件的索引优化  1-10
在将c列的过滤下推到engine层先做过滤，加载数据页  6-9

优化器算法介绍

mysql> select @@optimizer_switch;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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,prefer_ordering_index=on |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

#如何修改？
1.my.cnf
2.set global optimizer_switch='batched_key_access=off';
3.hints
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

3.4 MRR（muti range read）

优化回表，减少回表次数，随机io会变成顺序io
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';

辅助索引   -----回表 ------> 聚簇索引
转换为
辅助索引  -------sort id ----回表 ----> 聚簇索引

3.5 SNLJ

A join B on A.xx = B.xx where ...
伪代码：
for each row in A matching range {
	for each row in B {
		A.xx = B.yy,send to client
	}
}
以上栗子，可以通过left join 强制驱动表

3.6 BNLJ

在A和B关联条件匹配是，不再一次一次进行循环
而是采用一次性将外层循环的关联值和非驱动表匹配，一次性返回结果

3.7 BKA

主要作用：使用来优化非驱动表的关联列有辅助索引
BNL+ MRR的功能
开启BKA的方法：
mysql> set global optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> set global optimizer_switch='batched_key_access=on';
数据重新登录才能生效