说下你对索引的理解

索引是一个排好序的数据结构，它可以帮助我们快速查询/更新/分组/排序/去重数据，索引可以看作是一本新华字典，我们可以根据字典目录，去查找某个字或者偏旁去查找某一类字，但假如没有索引的话，那就相当于我们这本字典没有目录页，如果想要查找某个字需要手动遍历去翻全部的字典页，费时费力。

这个字典目录就相当于索引树中的目录节点，所在的字典内容页就相当于索引树存储真实数据的叶子节点，新华字典就相当于

索引的结构

概念

索引是的结构是b+树，对于主键索引来说目录主要是两部分，索引值 + 叶号，目录叶是辅助检索的。最后一层才是叶子结点，只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表，如下是一个三层B+树主键索引的结构
在这里插入图片描述

使用索引查找的流程

索引通常比较大，所以一般都是存储在磁盘中的，而相比于内存运输，从磁盘读数据往往耗时更长，B+树这种矮胖的结构意味着较少且次数稳定的磁盘IO，因为索引占用的存储空间是很大的，生产环境中一个未做分表处理的表大小可能达到10G以上，所以我们不可能直接把索引直接加载到内层，可能利用主键索引查找数据的时候，实际情况是先读取B+树第一层，然后通过二分查找快速定位到索引指针在第二层中的位置，然后在第二层依然使用二分查找快速定位到所在节点，最后从第三层也就是最后一层的中通过二分查找定位到最终结果所在叶子节点，也就根据主键索引每次只需要三次IO即可查找到结果，查询效率高且稳定

B+树对比二叉搜索树&B树

每次IO加载的基本单位是页，页大小通常是4KB，B+树中一个节点大小的上限也就是页的大小，即4KB，那么加入我每条数据的真实大小是1024kb，非叶子节点的大小为32bit，那么第一层的头结点和第二层的非叶子节点的的度均为 4* 8192bit / 32bit ≈ 1000， 也就是一层和第二层非叶子节点均有1000个子节点，叶子节点的度为4 * 8192bit / 1024bit = 32

三层B+树大约有1000 * 1000 * 32 = 32000000，因为2的25次方约等于32000000，所以换算成二叉搜索树就是一颗25层高度的树，最坏的情况即需要25次IO，所以很明显二叉搜索树不适合做索引，这突出了B+树第一个优势，磁盘IO的稳定，且IO次数少

b树也是这种矮胖结构，IO但之所以最终采用b+树作为索引数据结构，还有如下优势，同样因为B+树只有叶子节点才存储数据，且叶子节点是一个双向链表结构，因为叶子节点是以双向链表的形式存储是因为索引的结构并非稳定的，会随着数据的变化被调整，所以不宜使用连续的磁盘空间，而是采用离散存储的方式，B+树的叶子节点存储了全部的关键字信息，并且叶子节点之间通过指针相连，所以B+树结构非常适合范围和遍历查询

B+树对比于Hash

然在某些特定的场景下，如等值查询，Hash索引可能会比B+Tree索引更有优势，但在大多数情况下，比如范围查询，排序等，分组，B+Tree索引会更加适合。而且Hash索引也存在一些缺点，如Hash碰撞等，所以即使是用来做查询的话也没有想象中那么好的性能

B+索引的劣势

主键一定要是顺序递增的，B+树的度 = 节点/ 真实数据大小，节点大小的上限是页的大小，是4KB，当一个节点的存储容量达到上限后会开辟一个新的页，存入后续插入的数据，但如果是随机插入，那么之前已经满之前已经存满的结点会出现页分裂，即一个节点分裂成两个节点，频繁的页分裂会导致索引维护成本大大提高

联合索引

如果是联合索引，假如我们一张表有除了主键id还有a,b,c，对abc三个字段创建联合索引，联合索引结构大体结构如下
在这里插入图片描述

使用联合索引查询 - 底层原理分析

select * from table where a=13 and b=16 and c=4;

以三层结构的B+树联合索引（abc三个字段）为例，第一次IO会把第一层那一个目录结点加载到内存，在内存中查找的时间复杂度是O(log n)，根据这个目录结点定位到第二层的对应的目录结点，第二次即把第二层的对应的目录结点加载到内存，经过这两次IO就能在第三层精准定位到最终结果所在的叶子结点，总共三次IO，每次IO只会加载一个页到内存，假如我这个查询没有命中任何索引的话，那么实际查找中会用到主键索引，在查到最终结果是在获取到最终结果前主键索引的全部叶子结点都要被加载到内存中，当然这并非是一次性加载的，而是逐步的加载的，为了防止内存被撑爆，这种情况下性能就很差（type=ALL），需要考虑对sql优化

范围查询导致联合索引失效问题底层原理分析

select * from table where a=13 and b > 16 and c=4;

这个地方，字段a和b是有效的，但字段c无效，第一次IO还是把第一层那一个目录结点加载到内存，首先根据a快速定位第二层目录页，我们这里假设a=13的字段在数据库中有5500个，这5500个数据在第二层的目录页上对应着6个连续的目录页，这里比较复杂，我们再分析下上个sql

select * from table where a=13 and b=16 and c=4;

上个sql对应的是b=16这个条件，在已经通过a过滤出的5500条数据，分布在6个连续的目录页，因为a都是相同的，那就意味着在在这5500条数据中b是按序排列的，所以我们b=16我们可以更轻松的过滤数据，假如这里通过(a=13 and b = 16)过滤出500条

回到本sql

本sql查询条件是b > 16,因为此时已经在索引上通过a字段快速过滤出的5500条数据了，在这5500条中b都是有序排列的，b>16我们我依然可以通过索引快速过滤，假如这里通过(a=13 and b > 16)过滤出4000条，但这里的c就不一定是顺序排列的，例如(13, 17,100),(13, 19,1),(13, 20, 180), 这里c分别是100，1，180，非顺序排列的，那这就意味着我们无法通过索引中的目录页去定位最底层数据叶的数据范围了，从高效检索的角度来看索引字段c就是无效的，但c字段在这里就彻底没用了？不一定

覆盖索引

当一个查询可以只通过联合索引，而不需要访问实际的表中的行，就叫做覆盖查询，产生这种效果的索引就叫覆盖索引。覆盖索引可以极大地提高查询性能，但同时也要求查询需要访问的列必须在索引中，否则就需要访问实际的表行。

如果是这种sql

select a,b,c from table where a=13 and b>16 and c=4;

select的字段也被联合索引覆盖，那这样都不需要再回表了，把通过联合索引中a和b字段过滤出的结果，也就是这4000条数据所在的数据页加载到内存，遍历这4000条数据查找出c=4的最终结果。

非覆盖的联合索引

回到上个sql

select * from table where a = 13 and b = 16 and c=4;

上个case中我们说过，索引上通过a字段快速过滤出的5500条数据了，假如我整个表才1万条数据，而在满足a=13的基础上， b = 16只有20调了，那么我这里创建索引，只对b这一个字段创建索引的话

（验证1）索引肯定是有效的，但效果还是比不上联合索引，例如a字段的重复性较高，比如a是性别字段，性别字段只有2个取值，是一个重复性相当高的字段，如果是只有a一个查询条件的sql，那么不应该单独为a创建索引，但a字段作为多个筛选字段的其中之一，可以与其他字段一起作为联合索引，效果更好，如果恰好查询字段与筛选字段一致，那可以用于构建覆盖索引，性能会更好

不满足最左前缀导致索引失效问题底层原理分析

看懂了上面的，那这个知识点就不攻自破了，紧接上述案例，联合索引（a,b,c）但这里查询条件中b缺失了

select * from table where a=13 and c=4;

首先，a字段在联合索引中是有效的，我们还是根据a字段过滤出5500条数据，但因为查询条件没有b，只剩c了，在这个联合索引上，通过a=13过滤出的这5500条数据中，b是有序的，但c无序，代入上面的案例，(13, 17,100),(13, 19,1),(13, 20, 180)，所以这里索引字段c从高效检索的角度来看就会失效

OR导致的索引部分失效

案例sql

select * from table where a = 1 or b = 2 or c =3

现在我有一个针对a，b，c字段创建的联合索引abc,这种情况下联合索引可能只对检查a=1的部分生效，原理同上

此外还会有比较复杂的情况，例如我们除了有索引abc，还对b，c字段创建了bc索引，c字段单独一个索引c，那么查询的时候，可能会分别在索引abc上查找a=1的值，索引bc上查找b=2的值，索引c上查找c=3的值，然后再将结果分别合并起来，最终type是index_merge,且仔细观察会发现实际使用的索引是大于1个的
在这里插入图片描述
也就是如果我们想针对上述案例sql加索引，我们可以对a，b，c三个字段分别创建索引

UNION

索引对order by语句的提升分析

select city,name,age from t where city = '杭州' order by name limit 1000;

对name加索引idx_name
因为数据库需要首先根据"city"字段全表扫描过滤数据，过滤后的数据也无法使用idx_name索引，所以索引无效

对city加索引idx_city
利用idx_city，从该索引上找到city = 杭州的所以字段，这在是idx_city是连续的，可以较为高效的获取到，避免全表扫描，但idx_city获取到了满足city = 杭州的所对应的主键后要进行回表，通过回表获取到了完整的数据后，将其存入sort_buffer，然后进行系统内排序，最后取前1000条

对city和name加索引index_city_name
利用index_city_name，从该索引上找到city = 杭州的所有字段，在索引index_city_name中city = 杭州的所有字段都是联系，且在这联系的字段中name也是排好序的，避免了全表扫描的同时，通过回表获取到了完整的数据直接取前1000条就是最终结果，不需要再做排序了

对name和city加索引index_name_city
因为这个复合索引是按照"name"和"city"的顺序创建的。在这种情况下，MySQL只能在索引中第一个字段"name"无法用于where条件的过滤，所以这个索引是无效的，不会被使用到。

对city和name和age加索引idx_city_name_age

在上述索引的基础上，避免了全表扫描与文件内排序，同时避免了回表，直接通过联合索引index_city_name_age即可完成本次搜索

索引失效

（验证二）

group by原理

# 案例sql
explain select city ,count(*) as num from staff where age = 30 group by city;

无索引的情况
创建有两个字段的临时表，分别是city和num，全表扫描staff，筛选出age == 30的字段，临时表中如果没有这个city ==x，就插入(X, 1),否则就将这一行的num值加 1，遍历完成后，再根据字段city做排序，排序的过程即按city字段的码值升序，得到结果集返回给客户端，这种情况下，会同时出现全表扫描、文件内排序、使用临时表，性能很差

注意，插入的X代表的是city == x当前一整行的数据，后续再遇到city ==x的话就不再插入了，而是将这一行的num值加 1，验证如下，会发现原表中多行city相等的行在group by后的结果集中只有第一条还存在

# sql1
select * from staff where age = 30 group by city;

如果是多个字段做排序呢？比如

# sql2
select * from staff where age = 30 group by city, department_id;

底层逻辑依然如上，只不过插入的时候，只是会根据city和department_id两个字段来判重

对age加索引idx_age
和上述大体一致，只不过不再做全表扫描，而是通过索引idx_age来更快的筛选出age == 30的字段，此处不会再做全表扫描，但依然会伴随着文件内排序、使用临时表

对city加索引idx_city
因为查询条件where age = 30，无法使用索引，故idx_city索引是无效的

对city和age加索引idx_age_city
不再做全表扫描，而是通过索引idx_age_city来更快的筛选出age == 30的字段,这些字段中，都是已经按city排好序的了，所以，mysql不需要在为此创建临时表，也不需要再做内排序了

limit原理

原理

# 案例sql
SELECT  * from tb_template limit m, n;

以上述sql为例，这里没有命中其它索引，所以实际使用的是主键索引，这里是直接从主键索引上取出前m+n条数据，然后丢弃掉前m条数据，只保留最后的n条。

验证过程

测试使用的tb_template共60000条数据

# sql1
SELECT  * from tb_template ;

耗时1秒左右

# sql2
SELECT * from tb_template limit 0, 10;

耗时0.02s左右

# sql3
SELECT * from tb_template limit 59991, 10;

耗时0.09s左右

# sql4
SELECT  * from tb_template where id >= 59901;

耗时0.02s左右

只看返回值结果的话，sql3和sql4是等价的，但执行速度却差距很大，他们的差距在于底层原理，sql3需要从主键索引上取出前m+n条数据，然后丢弃掉前m条数据，只保留最后的n条，sql4则是可以通过主键索引的目录页快速定位到id >= 59901的叶子节点，只取这部分数据，所以执行速度自然是比sql3快很大

所以sql3为何不能通过主键索引的目录页快速定位到id >= 59901的叶子节点呢？因为主键id不一定是顺序的，虽然实际开发中主键id通常是顺序自增的，但如果这个表存在删除操作，那么主键id就不是连续的，mysql只能是一点点的加载叶子节点到内存，取出叶子节点的数据，计数，然后把这部分数据从内存中丢弃，直到第59991，此时前面加载的59990条数据已经全部丢弃了，从59991开始读取10条数据，即为最终结果

limit优化

参考：文章1，文章2

# 案例sql
select * from emp  limit 10000,20 where age > 20;

因为默认的limit语法，mysql无法通过非叶子节点来快速定位，只能全表或者全索引遍历，如果是获取大表中最后面的几行数据性能就会很差，所以优化思路是使得非叶子节点是能起作用的

select * from emp where id > （记录上一次最大id） limit 返回条数

但实际开发中表的连续性大概率已经被破坏了，列如删除表中数据，就会破坏表中主键的连续性，那么就需要使用缓存来

select * from emp  where id>( select id from emp where age>20 limit 10000,1 ) limit 20 ;：

DISTINCT原理

MySQL 在处理 DISTINCT 时，首先会将查询的结果集进行排序，然后在结果集中去掉重复的行，所以如果DISTINCT的字段无法利用索引的话，那么mysql会使用临时表，影响查询效率

字段个数&字段大小对速度的影响

SELECT black_list_value from tb_black_list;

SELECT black_list_name from tb_black_list;

（验证三）这里虽然都是分别只提取一个字段，但是black_list_value是一个json，很大的字段，速度已经趋近于select * 了，而black_list_name只是一个普通小字段，仅仅select该字段速度远小于select大字段，目前来看应该是速度差在网络传输上，需要用自己计算机0网络传输的情况下再试一试

子查询用法总结

  • sc中多类数据耦合/比如sc成绩表中，一个sid平均出现3次，代表一个学生就大约有3门成绩的记录，对应三条数据，如果有一个需求是查询语文成绩大约数学成绩的同学，但我们需要把每名学生的sid、mathScore、chinaScore罗列出来，并在where中使用条件mathScore，chinaScore
select * from
(select sid,score from sc where cid = 1) china,
(select sid,score from sc where cid = 2) math where china.sid = math.sid and china.score > math.score;
  • 需要横跨三个有关联的表中取数据，本来需要做三表连接，但可以分别两两相连
-- explain select sid,
-- (select cname from course c where c.cid = sc.cid) as cname,
-- (select sname  from student s where s.sid = sc.sid) as sname, 
--  score from sc;
  • 需要查的数据表中有普通字段还有聚合字段

连表查询

粗暴多重循环法

通常用两个非索引建进线连接

缓存辅助双重循环法

通常用两个非索引建进线连接

索引辅助完成连表

左连接定义：left join，左表作为主表，右表作为从表，左表每行数据都要在右表做匹配，匹配成功的行主表和从表会合并到一起，没有匹配成功的行就用NULL来填充从表对应的列

驱动表与被驱动表
驱动表即要全表扫描，如果

所以这种情况下都左边的表都是作为驱动表，右边的表都是作为被驱动表，除非是右表中有着高效的索引可利用，此时会把左边的表都是作为被驱动表，右边的表都是作为驱动表

情况一：用两个唯一性主键做连接
左右表都是通过主键连接的，所以优化器无法从连表字段上做文章，所以这种情况下铁定是左边也就是主表作为被驱动表，右边的表都是作为驱动表，无论两个表的量级差别有多大

SELECT * from tb_template tt LEFT JOIN tb_template_classic ttc  on tt.id = ttc.id;
EXPLAIN SELECT * from tb_template_classic ttc LEFT JOIN  tb_template tt on tt.id = ttc.id

模版表tb_template和模版类型表tb_template_classic的主键均是id字段，id是自增非空的标准主键，其中tb_template是大表有54427条数据，tb_template_classic是小表，有2w条数据，经测试，主键连接的情况下，这两张表，谁做主表，谁就是驱动表，谁做从表，谁就是被驱动表

无论如何，驱动表和被驱动表的type分别是ALl和eq_ref,性能还是可以的

情况二：主外键连接，一定要让持有外键的表做主表，外键所在表作为次表
tb_template表的classic_id，是tb_template_classic表的主键

SELECT * from tb_template tt LEFT JOIN tb_template_classic ttc  on tt.classic_id = ttc.id;

这里tt作为驱动表，ttc作为被驱动表，因为这里连接用的分别是ttc的主键，和tt的一个普通字段（这里起到外键的作用），所以使用ttc作为被驱动表，type是eq_ref,tt作为驱动表，type是ALL

在这里插入图片描述
这种情况相当于一个单层循环，执行耗时约0.6秒

如果反过来会如何呢？

EXPLAIN SELECT * from tb_template_classic ttc LEFT JOIN tb_template tt  on tt.classic_id = ttc.id;

此处连表的方式是，ttc的每条数据都要在tt表中匹配，而匹配所用的是ttc表的classic_id和tt的id，这两个连表的字段是毫不相干的，所以这就需要双重遍历，即ttc表每一行字段的连接都要遍历tt表，而ttc表此处作为驱动表也是需要遍历的，分析sql如下
在这里插入图片描述
这种情况下性能是低的可怕的，执行耗时大约需要150秒

情况三：

EXPLAIN SELECT ttb.first_entity_id from tb_ticket_todo_task_202312 ttt LEFT JOIN tb_ticket_base_202312 ttb on ttt.id = ttb.first_entity_id;

在这里插入图片描述
上述的案例中，驱动表是作为外层的单层循环，通常情况下驱动表的性能级别是all，意味着往往需要全表遍历整改驱动表的聚簇索引做驱动，但存在特殊情况，如果我们目标筛选select相关字段和查询条件where相关字段都被我们创建的复合索引所覆盖，那么此时驱动表的性能级别会是index，即以遍历索引的形式来循环驱动，每次循环的内容即被驱动表通过走索引的形式找到对应的连表匹配值

其中，如果驱动条件走被驱动表的唯一性索引，性能是最佳的，此时被驱动表的性能级别是eq_ref，含义是针对驱动表中的每个连接条件，被驱动表的索引中有且仅有一行数据与之匹配，如果驱动条件走被驱动表的非唯一性普通索引，此时被驱动表的性能级别如果是ref，含义是驱动表中的每个连接条件，被驱动表的索引中有且可能有多行数据与之匹配。mysql内部策略是①判断两表的连表字段，如果一个字段是某个表的主键/唯一键，那么果断选取这个表作为被驱动表②如果该连接字段是两个表的非唯一性字段，那么默认小表驱动大表

情况4，驱动表的执行类型不一定是all

SELECT * FROM orders JOIN products ON orders.product_id = products.id WHERE products.name = 'iPhone';

忽略WHERE products.name = ‘iPhone’;的，那么应该是orders作为被驱动表，type为ALL，orders作为被驱动表，type为eq_ref,但假如该sql的name字段有索引，那么可能的情况是，mysql通过name字段的索引来查找所有名为’iPhone’的产品

总结

  • 可用索引一样的情况下，参见情况一，比如两表连接都是用的唯一性索引/主键索引，那么mysql倾向于用小表作为驱动表，大表作为被驱动表
  • 可用索引不一样的时候，会优先考虑索引使用情况，比如主外键连表操作，无论两张表大小差异如何，主表，也就是外键所在的表，会作为驱动表，性能为all，也就是全表扫描。外键所引用的表，也就是从表，作为被驱动表，性能为eq_ref
  • 可用索引不一样的时候，且sql中涉及到where的筛选字段有索引的话，mysql会倾向于用索引所在的表作为驱动表，即通过索引筛选出符合where条件的字段，然后对其遍历，

外键

in和exists的原理与选择

案例: 显示所有有员工的部门信息

select * from dept where exists( select * from employee where deptno=dept.deptno);

exists对外表采用遍历方式逐条查询，每次查询都会比较exists的条件语句，当exists里的条件语句返回记录行时则条件为真。此次返回当前遍历到的记录，反之，如果exists里的条件语句不能返回记录行，则丢弃当前遍历到的记录，执行过程为

  • 对dept表逐行遍历
  • 子查询会使用dept表被遍历到的当前行的数据作为查询条件
  • 如果有查找到数据，那么exists返回true，dept表被遍历到的当前行就可以被打印，反正则不被打印

总结：外查询的表相当于是驱动表，需要全表遍历，内查询的表则可以使用索引去提示查找效率，所以外表小内表大适合用exists

in

select * from dept where dept_id  in ( select dept_id from employee where deptno=dept.deptno);

执行流程

  • 先执行子查询，从employee取出 dept_id的结果集deptIdList
  • 执行外层查询，查询条件是外表dept的字段dept_id 在结果集deptIdList中，因为dept_id是dept表的主键，所以这里是外查询中dept表是可以通过dept的主键索引来查找的，效率较高，所以适合于外表大内表小的情况

临时表&文件内排序

Using temporary

表示在执行sql查询时会生成一个临时表存储中间结构、mysql有一个控制临时表大小的参数，超过临时表阈值就会在磁盘上生成，常见于group by、union、select子查询中，这种情况可以通过索引解决，方案就是对分组字段建立索引，建立索引后再次执行该命令可以直接通过走索引完成分组，union开发中通过用来代替or，以为or会导致索引失效，从sql语法层面用union代替or，并对union联合查询的查询条件加字段，出现临时表后性能较差，需要避免

Using filesort

首先，索引本身是个排好序的数据结构，建立索引后即相当于根据xxx字段建立起了已排好序的数据结构，那么在执行order by时直接用索引接口高效完成排序，反之，如果没有索引，就会产生文件内排序，filesort底层用的quicksort算法

同时出现临时表与文件内排序

group by

explain select city ,count(*) as num from staff group by city;

模糊查询

模糊查询导致索引失效，假如我们有一个表test,为该表的name字段创建了索引idx_name，这个表中有8行数据,name字段分别为“apple”, “bee”, “carrot”, “dog”, “elephant”, “flower”, “gorilla”, “house”，在B+树中，这些字符串将按字典顺序进行排序，如果两个字符串的首字母相同，那么会比较第二个字母，依此类推，按次规则，我们模拟将其创建为一颗三层的B+树，如下

                        [elephant]

                /                         \

      [apple, carrot]                        [elephant, gorilla]
     
     /        \                              /         \ 

[apple, bee] [carrot, dog]             [elephant, flower] [gorilla, house]

所以，如果是右模糊查询，可以根据非叶子节点大体定位到目标数据的范围

所以，阿里java开发规范中就就强制要求页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

SQL命令执行顺序

  • from t1：针对表t1生成虚拟表 vt1

    • from后面除了根特定的表名外，也完全可以把子查询语句返回的结果集作为虚拟表，多个子查询的表会得到按笛卡尔积获取到的表，并且可以通过where根据两表主外键或其他连接字段的条件来执行

  • right/full/left/inner join t2：确定要进行连接的表t2,选择连表策略与拼接策略

  • on t1.column = t2.colum：做连表操作，连表方案(三种连接方法，索引辅助连表又分两份)参考下文，以左连接为例，会把筛选后满足条件的数据筛选出，右边做条件过滤后及时没有和左表对应的，也会用null值填充后和左表数据拼接，总之，以左边全部的数据作为基准表，右边符合条件的作拼接

  • where：

    • 应用where筛选器后跟字段作为筛选条件，对上一步生产的虚拟表引用where筛选器，生成虚拟表vt4
    • 如果where用到了范围查询in，例如 select * from A where A.x in (select x from B where y>0 );，对于in来说是先执行子查询即通过B表的字段y大于0来从B表中做筛选生成虚拟表t1，在从A表查询出满足条件A.x位于虚拟表t1中的数据
    • 如果where用到了范围查询exists，例如 select * from A where exists (select * from B where y = A.x );，对于exists来说是先一行行的遍历表A，每遍历一行表A的数据就会把遍历到的那行数据的A.x字段传给子查询中的A.x然后执行子查询当前行有结果返回即为true，为true则外表的当前行即进行打印，否则为false，不打印

  • group by ：子句将中的唯一的值组合成为一组，得到虚拟表vt5。group by的特殊点在于它会以排序字段作为基准，排序字段可以不止一个，(group by a,b) a和b中存在非唯一性字段那么就会有一个重复,vt5的类似大致如下，生成的虚拟表其实是不符合关系型表达式的，如果没有相关的索引可用，就会生成临时表来做存储中间结果，对性能影响较大
    在这里插入图片描述

    • 排序字段如果是非唯一性的，那无论重复出现多少次都会被整合为一行，而其他行的字段都会被压缩为一个字段，基本只有该字段可以被用来做直接筛选了，其它字段只能在聚合后被查询
    • 排序字段如果是唯一性的，比如根据主键排序，那么就不会出现字段合并现象，可以在任意字段查询的基础上满足聚合查询的要求

    被合并的行这是通过聚合sum/avg/count查询，例如查询满足xxx条件的字段的占比(统计总数/总行数）,保留前两位

round(sum(if((end_time-start_time-b.duration) >=0,1,0) / count(*),2);

多条件判断，类似于java中的switch-case
round((sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end) / count((uvl.uid))),3) fans_growth_rat

from数据源

from 
tb_user_video_log uvl join tb_video_info vi
on uvl.video_id = vi.video_id
group by vi.author,month

根据年份/月份这种时间做分组，如果求单个月份的累计数目
  sum(case when if_follow = 1 then 1 when if_follow = 2 then -1 else 0 end) total_fans

根据年份/月份这种时间做分组，如果求当月截止到目前为止的数目
  sum(sum(case when if_follow=1 then 1
             when if_follow=2 then -1
             else 0 end)) over(partition by author order by date_format(start_time,'%Y-%m')) total_fans
  • avg/sum/count：执行聚合函数，为vt5生成超组，生成vt6
  • having筛选器，生成vt7。having筛选器是第一个也是为唯一一个应用到已分组数据的筛选器
  • select：处理select子句。将vt7中的在select中出现的列筛选出来。生成vt8
    • select筛选的字段可以通过子查询的方式返回，子查询中的筛选条件用到外层查询大约是表vt1中的字段作为查询条件，筛选出的数据必须是和外表vt1一一对应的。不能出现多对1的关系
  • distinct：应用distinct子句，对vt8进行去重，生成vt9。
  • order by：应用子句。按照order_by_condition排序vt9，此时返回的一个游标，而不是虚拟表
  • limit：应用limit选项。生成vt10返回结果给请求者即用户

mysql执行架构

  • MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等
  • MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。
  • MySQL 索引扫描：根据指定索引过滤条件（比如 where id = 1) ，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。
  • MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表

索引总结

优缺点是什么

索引可以大大提升我们的数据查询速度

  • 减少从磁盘IO次数的角度提升数据查询速度，没有索引，就相当于需要全部的数据都要分批次被加载到内存，而有了索引，且是覆盖索引的情况下，只需要把索引加载到内存即可，那这就意味着能显著减少能磁盘IO次数，减少内存使用，而磁盘IO往往是最耗时的项目,所以减少磁盘IO对提升检索速度意义非凡

  • 提升检索/排序/去重/分组速度,数据从磁盘加载到内存后

    • 如果我们查询时用不到索引，那么在数据从磁盘加载到内存后需要用遍历数据，也是On的复杂度，如果检索时能用到内存那么时间复杂度就是log2n，大大提升检索速度，且数据量越大提升效果越明显
    • 涉及到order by/group by的时候，可以避免内排序与临时表，节省cpu和内存资源

节省内存资源和减少服务器CPU开销
通过上面例子可知，如果查询没有命中任何索引的话，那么实际查找中会用到主键索引，在查到最终结果前主键索引的全部叶子结点都要被加载到内存中，当然这并非是一次性加载的，而是逐步的加载的，防止内存被撑爆。可见没有命中任何索引的话内存和cpi开销是相当大的。

此外，union/union all/group by/order by等操作会伴随着文件内排序和临时表，如果连表操作没用命中索引则会面临一个O(n的次方)的时间繁杂度，n的次方数就是连表的张数

索引的代价

  • 每创建一个索引意味着mysql底层会创建一颗B+树，树的每一个节点都是一页的空间16k，数据量越大占用的磁盘存储空间开销增大
  • 索引是一个已排好序的数据结构，聚簇索引B+树上的节点都是按照索引列的值由大到小形成了双向列表，而没和节点中的数据列也是按有小达到形成了单项列表，但执行删除、插入、更新是，索引内部需要重新维护索引的顺序、这就需要耗费一定的性能了，注意，顺序性是为了配合二分查找快速定位的
  • 如果一张表的索引多到一定程度，会影响mysql在性能优化时需要考虑更多的索引，影响优化效率

适合与不适合创建索引的情况

适合的情况

  • 字段具有唯一性或需要它是唯一的可以考虑建立唯一性索引，比如email，唯一性索引增删改时索引维护代价比较小但对性能提升很大
  • 频繁被作为where查询条件的字段，包括select、update、delete，因为无论是更新还是删除的前提都是先找到要操作的目标数据，同时，如果更新的是非索引字段那么提效更明显，因为这种情况下并不需要重新维护
  • 需要被group by分组和order by排序的索引
  • 需要做去重DISTINCT的查询字段
  • 需要去重的字段，因为索引已经对这个字段排好序了，去重效率会比常规情况高很多
  • 多表连接操作，小表作为驱动表，大表是被当做被驱动表，这样有效减少外层循环的次数，为被驱动表的连接条件增加索引，或者将已有的索引(主外键)当做被驱动表的连接条件，可以有效减少内层的循环次数，此外mysql5版本有join buffer，将驱动表缓冲存储到缓冲池中，避免频繁的从驱动表和被驱动表频繁的IO，尽量用join代替子查询，因为子查询执行效率不高①子查询过程中会生成临时表，浪费cpu资源②子查询生成的结果会存在临时表，且临时表是没有索引，查询效率比较低，尤其是子查询返回较大的结果集，查询效率更低

不适合的情况

  • 在where查询中用不到的字段就不要建索引了，及时select中用到的字段也不要再建索引了，因因为索引的目的就是大大提高查询速度,group by和order by字段也同理
  • 数据量小的、重复度高的字段尽量避免
  • 增删改为主的表不要索引
  • 不建议对无序的值做索引，比如uuid、md5字段，这样会大大增加索引维护的开销

索引失效场景与对应的优化建议

# 为a,b,c字段创建索引
CREATE INDEX idx_name ON table_name (a,b,c);
# 案例sql1
select * from table where a=1 and c=3;
# 案例sql2
select * from table where a=1 and b>2 and c=3;
  • 联合索引一定要遵循最左前缀法则，一个索引最多16个字段，对于多列索引，过滤条件必须按照索引建立的顺序依次满足，过滤条件中一旦跳过了某个字段，那么后续的索引字段均无法被使用，这是B+树结构决定的，如上述案例sql1，参考上面的联合索引结构图，因为联合索引是按照特定的顺序存储的，这里跳过了b字段，c字段就会失去检索价值，只有a字段是理论上能用的上这个索引的，建立联合索引时最好是包含全部的查询字段，也就是覆盖索引，这样查询性能是最好的，如果不能全部包含，那么优先选择过滤性高的字段，且过滤性越高的字段越靠左，即使无法做到全值匹配也要保证满足最左前缀匹配
  • 函数、计算、类型转化会导致索引失效，例如函数left本意是从表中一个个取出email字段取前三个做笔记，尽量避免出现运算、用其它方式代替函数，比如left取左前缀用右模糊查询%代替
  • 范围查询后面的索引字段也会失效，上面案例已经把原理讲的很清楚了，这里额外要注意的点是创建索引时，要根据实际情况，将精确查询的字段放在联合索引左边，将范围查询的字段放在联合索引索引最右边某些范围查询运算符会导致索引失效，比如!=、not in，那么索引失效，可以使用between来代替他们
  • 模糊查询中左模糊和全模糊匹配导致索引失效，可以用右模糊匹配，右模糊匹配相当于使用前缀去索引中匹配，同理，对应char类型的索引我们建立索引不必对其全字段建立索引，可以只对其前半部分建立索引
  • 使用where中用OR关系做多条件查询，导致索引失效，个人理解索引结构中是按字段排序的，and条件满足索引是因为通过满足上一个查询条件根据索引定位到分区范围再用下一个条件，此时可以通过union语法层面代替or，union的原理是将or条件划分为2个独立的sql，对拆分后的两个select的查询条件分别建立索引，这样又可以走索引了，但这样做并没有什么意义，因为union就算能走索引，速度依然很慢
  • order by 非覆盖索引，mysql优化前考虑回表代价比较大，所以如果仅对排序字段建立索引就mysql就会弃用，所以一定要加复合索引复合筛选字段和排序字段，排序字段在最右边
  • 100%出现的查询字段或者同等概率出现的查询字段，区分度越高的字段越靠左放，原理是通过减少索引判断次数提升索引性能

聚簇索引主键三要素

  • 不要选择过长的主键，因为无论是聚簇索引还是非主聚簇索引中都会存储主键值，而且一页空间是固定的大小为16k，如果主键过大，首先会使非叶子索引所能存储主键变少 => 然后导致树的层级变高=>增加IO次数，进而会大大增加查询时间
  • 从减少索引后续数据变更的维护成本较低考虑，最好选择单调递增的主键，如果用的是非单调的主键，比如uuid，md5会导致也分裂等问题产生
  • 必须是唯一和非空的，且不能被修改

索引下推(ICP)

把以上索引扫描和索引过滤合并在一起处理，过滤后的记录数据下推到存储引擎后的一种索引优化策略,好处是

  • 减少了上传到 MySQL SERVER 层的数据。
  • 减少了回表的操作次数。

Explain分析字段

ref

显示索引的哪一列被使用了，如果可能的话，最好是一个常数。即ref标识哪些列或常量被用于查找索引列上的值

1、这里表示常数100是用来在主键索引中查询，即查找主键id为100的数据
在这里插入图片描述
2、这里是d和e通过连表查询，对于e表来是说，它的外键id被用来在它的外键索引引上查找目标值，由于主键索引是union的，所以只需要1次即可定位;
在这里插入图片描述

rows

根据表统计信息和索引使用情况，推算出查找出所需记录需要耗费的时间

extra字段

常见Extra字段

  • Using filesort即查询过程中，mysql内部进线排序，没有通过索引，这里mysql底层会用xxx排序算法去做排序，相比如索引辅助排序比较耗费性能
  • Using temporary表示由于排序没有走索引、使用union、子查询连接查询(构建出虚拟表的情况)，因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，Using temporary比using filesort问题更严重，using filesort只是排序操作，而临时表在内存中创建然后再操作再内部操作分组
  • Using index: 查询操作中，待查询的字段和查询条件where都已经被索引覆盖，避免了回表，效率不错。
  • 只有Using where
  • 如果此时type=ALL 意味着没有用到索引或者索引失效，往往意味着全表扫描
  • 如果此时type=ref 意味着对查询条件字段创建了索引，但该条件字段由于是非唯一性字段，无法一锤定音，需要整体遍历去一个个对比是否满足查询条件并返回结果集，默认ICP是开启的，即MySQL 索引扫描的同时伴随索引过滤，过滤完毕后再回表查询，效率会有提升
  • Using index和Using where同时出现，代表待查询的目标字段已经被索引覆盖，不需要回表取值，只依靠索引就够了，但查询条件是非索引字段或者不满足最左前缀原则导致查询条件没法用到索引，那么这意味着无法直接通过索引来查询到符合条件的数据，也就是说需要遍历并加载整个索引后，将数据上传到mysql Server层，通过mysql Server层通过where去过滤
  • Using index condition:常见于复合索引中，由于左列的索引可能用到了范围查询，那么对于它后面的索引字段就意味着失效，因为此时索引要根据这个范围字段的范围去遍历并读取索引，此时开启了icp，从索引中取出的数据会直接在存储引擎侧过滤，而不是上传到Mysql Server层再去通过where过滤，好处如上介绍
  • using intersect;分别对and条件用的条件建立索引，那么查询时会用到这些索引，然后对得到的结果求交集，对where条件做覆盖索引可解决
  • using union;分别对or条件前后的字段用的条件建立索引，那么查询时会用到这些索引，然后对得到的结果求并集，对where条件做覆盖索引可解决
  • using sort_union;分别对or条件前后的字段用的条件建立索引，那么查询时会用到这些索引，但某个的索引可能是聚合查询，但根据条件查询时可能只用到聚合索引的前部分，也就是没有针对这个条件单独创建索引，mysql优化器只能用其它的符合最左前缀匹配的索引，效率会更低一些
  • NULL：表示查询条件有命中索引，但查询的目标字段没有被索引覆盖到需要回表

select_type

  • simple 简单查询，只有一个简单的select
select email from employees where salary = 11000;
  • derive：from表中包含子查询会被标记为derived衍生，mysql会执行这些临时表
select * from (select * from t1 where id = 1);
  • primary/subquery子查询，后面的select筛选出的的某个字段的值前一个select的where筛选条件，最外层的select的是primary主查询，外层的select是subquery子查询
explain select email from employees where salary = 11000;
  • union：第二个select出现在union后被标记为union，若union包含在from语句的子查询中，外层select会被标记为derived

type执行类型

system

一张表只要一行记录，系统表

constant

常量级，表示通过一次索引就可以找到了，将主键或者unique(唯一)索引作为where语句的查询条件，那么这类sql往往type的类型就是constant

select * from xxxTable where id = 1

eq_ref和ref

唯一性索引扫描，常见于连表查询，连表查询mysql底层会根据最优解来选择驱动表和被驱动表，选择策略参考下文，有三种，这里以索引辅助连表为例，索引辅助连表依然会细分多种策略，驱动表是作为外层的单层循环，通常情况下驱动表的性能级别是all，意味着往往需要全表遍历整改驱动表的聚簇索引做驱动，但存在特殊情况，如果我们目标筛选select相关字段和查询条件where相关字段都被我们创建的复合索引所覆盖，那么此时驱动表的性能级别会是index，即以遍历索引的形式来循环驱动，每次循环的内容即被驱动表通过走索引的形式找到对应的连表匹配值，其中，如果驱动条件走被驱动表的唯一性索引，性能是最佳的，此时被驱动表的性能级别是eq_ref，含义是针对驱动表中的每个连接条件，被驱动表的索引中有且仅有一行数据与之匹配，如果驱动条件走被驱动表的非唯一性普通索引，此时被驱动表的性能级别如果是ref，含义是驱动表中的每个连接条件，被驱动表的索引中有且可能有多行数据与之匹配。mysql内部策略是①判断两表的连表字段，如果一个字段是某个表的主键/唯一键，那么果断选取这个表作为被驱动表②如果该连接字段是两个表的非唯一性字段，那么默认小表驱动大表

eq_ref目前除了连表查询，除了上述连表查询案例情况，其它地方并未见过，使用非唯一性索引字段作为查询条件，案例如下，如果是非唯一性字段作为查询条件，那么type=ref，如果是唯一性字段作为查询条件，那么type=const
在这里插入图片描述

range

表示用到了索引的范围查询，扫描部分的索引内容，比全面扫描索引要强

explain select * from  employees where employee_id > 2000;

index

ALL

全表扫描，性能最差，通常是没有用到索引或者索引失效

explain select * from employees where email = "wangzh";

生成环境标准

要求性能要达到range及以上，

possible_keys

可能用到的索引

key_len

索引中使用的字节数

聚簇索引与非聚簇索引区别

聚簇索引优点

  • 单值查询速度上聚簇索引，因为非聚簇索引数据可能出现回表，如果不考虑回表数据类似
  • 聚簇索引处理范围查找和排序速度非常快，因为聚簇索引中的叶子节点存储的表数据行都是双向链表结构的，这种结构比较适合做范围查找和排序

聚簇索引限制

  • 一张表只能有一个聚簇索引，但可以有多个辅助索引
  • 对主键要求是比较严格的，因为聚簇索引本身是基于主键构造的，所以要去主键必须是唯一的，如果我们没有声明主键，那么mysql也会自动选取一个唯一性字段来，如果还没有唯一性字段，mysql也会自动建立一个作为主键索引
  • 虽然速度快，但维护困难，比如插入或删除数据时效率低于非聚簇索引，插入或删除数据时聚簇索引必须同步

MyIsam与Innodb区别

共同点

  • 底层都是B+树

区别

  • 速度方面(主键索引和非聚簇索引)：聚簇索引不考虑回表，而使用MyIsam必须要回表，所以速度上聚簇索引快于MyIsam，MyIsam相当于全部都是类似于辅助索引，辅助索引对比：Innodb辅助索引涉及到回表操作速度是慢于MyIsam的回表操作的、因为Innodb辅助索引的回表操作需要去主键索引上去取数据，而MyIsam的回表操作相当于直接从索引上获取的目标的地址值去取数据，Innodb的辅助索引速度慢与它的主键索引，也慢于它的辅助索引
  • 事务方面，innodb支持事务，所以计算数据行数的效率低于myisam，myisam因为不支持事务所以会缓存表的行数，而innodb支撑事务，不要的事务对应的表行数无法统计，myisam不支持事务
  • 主键与外键： InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败； InnoDB表必须有唯一索引且对主键的要求较为严格，需要同时满足非空、唯一性单调递增、占用存储空间较小的类型比如tinyInt
  • 存储方面/数据结构，myisam数据和索引是分开的，实际存储结构方面也是划分为数据、表格、索引三部分文件，innodb数据和索引是存储在一起的，聚簇索引文件是存储在主键索引的叶子节点上的，主键起到索引作用，实际存储中数据和索引是放一起的，用一个文件来存储
  • 锁方面：InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
  • 行数查询方面：innodb查询行数需要遍历索引统计节点个数，速度慢，myiasm速度比较快

sql优化总结

尽可能少的取数据

  • 避免使用*，秉承用多少字段就取多少字段的原则，做好分页，取的数据量越大多，耗时越长，每次IO只能取一页数据，取的数据量过大会伴随大量的IO操作，虽然
  • 分页

sql语法

索引优化

# mysql # 数据库 # java
Logo
永洪数据分析社区

永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。

更多推荐

cover

Python 数据分析与可视化：开启数据洞察之旅（5/10）

avatar 永洪数据分析社区

高级可视化图表分析实践——以《大侠立志传》武器系统为例

高级可视化图表分析实践——以《大侠立志传》武器系统为例

avatar 永洪数据分析社区

Dify+数据库+ECharts打造数据可视化图表，让数据自己说话！

今天分享一下如何利用Dify平台，结合强大的Echarts图表库，轻松搭建工作流。将数据库中的数据直接转化为精美的可视化图表，让数据开口说话。

avatar 永洪数据分析社区