3、索引优化

最新推荐文章于 2024-07-21 19:06:05 发布

方二华

最新推荐文章于 2024-07-21 19:06:05 发布

阅读量70

点赞数

分类专栏： # mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq407155634/article/details/129422410

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

为了提高mysql的查询速度，需要尽可能的使用到索引，否则mysql只能通过全表扫描，此时查询效率会很低，但是如果表内数据很少，全表扫描反而可能会更快。

mysql不一定会按照sql设定的索引查询，它的优化器会对本次查询做成本计算，从多个索引中选择一个索引，或是完全不用索引。一般优化器确定的查询方案都是最优的。用户也可以通过关键字强制使用索引，force index(index_name) ，忽略引擎的优化方案。

条件优化

全值匹配：对索引中所有列都赋值匹配（联合索引 index(a,b,c)）
- select * from t where index_a=1 and index_b =2 and index_c=3;
最左前缀法则：从索引的最左前列开始匹配，不能跳列
- select * from t where index_a=1 and index_b=2;
不在索引上做操作
- select * from t left(index,2) = 'aa'
尽量使用覆盖索引，减少select *
- select index from t;
不等于、not in、not exists无法使用索引
is null、is not null 一般情况也无法使用索引
like若以通配符开始，无法使用索引
匹配查询类型，虽然mysql能自动转换一些类型，但如果没转换，会导致索引失效
or 或 in 不一定会使用索引，在表的数量不多时，引擎会选择全表扫描，效率更高。
范围查询若范围过大，引擎会转为全表扫描，可以拆分成小范围多次查询
扫描表的数量多少，不代表性能如何，还要考虑是否回表查询

联合索引

联合索引可减少索引数量，从而减少回表次数
B+树的整体是按第一列排序的，当第一列固定时，第二列是有序的，当第二列固定时，第三列是有序的，所以只要按照左前缀原则，就可以一直保持有序查询
第一列若使用范围查询，会导致引擎忽略索引，因为联合索引的第一列主导了B+树的主次序，而范围查询可能会返回大量结果，在不是覆盖索引的情况下，会导致大量数据的回表查询，并且第一列不固定，第二列也无法保证有序。
第一列使用like 前缀匹配会使用到索引，和范围查询一样，第二列也无法保证有序，但是有索引下推，仍然可以用索引。
索引下推，在第二列无法保证有序性时使用，在5.6之后，在索引第一列匹配成功后，会继续尝试匹配第二列第三列得到结果集。在之前的版本中，匹配第一列后的结果集由于无法保证顺序性，也就无法索引检索，只能回表查询后，取到完整数据，再进行匹配。由于索引下推减少了很多回表查询，性能得到提升。
中间列若使用了范围查询，可能会导致后面的索引列无法使用。因为有序性会断层，无法保证后面列有序性。

order by 排序

Extra中using index使用索引排序，效率高； using filesort 使用文件排序，效率低。
要使用排序则需要order by 语句满足最左前列，或者与where 子句一起满足最左前列
- select * from t where a=1 order by b,c
若排序字段不在索引上，引擎会加载数据自己排序，出现Using filesort，效率低下。
若不是覆盖索引，在范围查询结果集较大时，仍可能出现文件排序，因为引擎为了减少回表次数。

group by

在group by之前，需要进行order by，所以等同于order by

分页查询

limit offset, rows

数据库查询offset+rows个数据，取最后的rows个数据返回。当offset很大时，性能很差

优化方式

通过主键限制，where id>100 limit 10; 主键和数据次序一一匹配，但主键必须是连续递增的，否则数据次序将有问题；
join优化，通过索引树查出分页数据id，然后再从聚集索引取数据；排序字段条件字段要在同一个索引中，否则无法利用索引，效率会更差；
- from t inner join (select id from t order by t_index limit 1000,10) t1 on t1.id = t.id

join关联查询

join查询需要在两个表中进行查询，第一张查询的表称为驱动表，第二张为被驱动表。

数据库实现join的算法：

nested loop join NLJ 嵌套循环连接

select * from t_a left join t_b on t_b.a_id=t_a.id

查询逻辑等同：

//逐行从驱动表(t_a)中取数据，每取一行立即从t_b取出关联行
while(t_a.hasNext()){ 
    //取出驱动表数据
    a = t_a.next(); 
    //将取出的关联字段从被驱动表(t_a)中取出a的数据
    b = t_b.getById(a.id); 
    //合并结果
    merge(a, b);
}
//得到结果集

若t_a、t_b各有1000条数据，且关联字段是索引，从t_a中进行了1000次取值，从t_b中进行了1000次取值，总共2000次取值。

若关联字段不是索引，就需要全表扫描，则总次数为1000*1000=1000000。

所以使用该算法的前提是需要关联字段是索引，否则过高的代价会被数据库优化器判断代价太高不被使用。

通过Extra 未出现Using join buff即可判断该sql是使用了NLJ方式查询的。

循环次数取决于驱动表，所以优化器会优先选择小表做驱动表，但left join（左驱动），rightjoin（右驱动）属于手动指定驱动表。

block nested loop join BLJ 基于块的嵌套循环连接

select * from t_a left join t_b on t_b.a_id=t_a.id

查询逻辑

//查出驱动表所有数据
as = t_a.findAll();
//将驱动表数据放到join_buffer中
join_buffer.add(as);
//逐行从被驱动表中取数据
while(t_b.hasNext()){
    //取一行被驱动表
    b = t_b.next();
    //在join_buffer内存块中和驱动表所有记录匹配
    a = join_buffer.search(b.a_id);
    merge(a, b);
}
//得到结果集

若t_a、t_b各有1000条数据，t_a取值1000次， t_b取值1000次，总取值2000次，内存块中数据匹配1000*1000=1000000次；

相比较NLJ没有索引的次数，速度要快很多。

若join_buffer不足以存放驱动表数据，将会分段执行整个过程。

通过Extra 出现Using join buff即可判断该sql是使用了BLJ方式查询的。

join的优化