MySQL-索引优化和查询优化

目录

一、数据库调优维度

二、索引失效的11个案例

三、关联查询优化

四、子查询优化

五、排序优化

六、分页优化

七、覆盖索引

八、索引下推


一、数据库调优维度

  • 索引失效、没有充分利用到索引--索引建立
  • 关联查询太多join(设计缺陷或不得已的需求)--SQL优化
  • 服务器调优及各个参数设置(缓冲、线程数等)--调整my.cnf
  • 数据过多--分库分表

二、索引失效的11个案例

  • 全值匹配我最爱
  • 最佳左前缀法则:过滤条件要使用索引必须按照索引建立时的顺序,以此满足,一旦跳过某个字段,索引后面的字段都无法使用
  • 主键插入顺序:需要依次递增,聚餐索引的叶子节点是按照主键递增顺序存储,存储到数据页最大值时会开辟新的数据页,如果这时候新增一个范围内的主键,需要将该主键后的数据进行数据页的重新划分;
  • 计算、函数导致索引失效:使用函数需要先作用函数然后将结果进行对比;
  • 类型转换(自动或手动):条件类型与数据库字段类型不匹配会导致索引失效;
  • 创建联合索引中,需要将范围索引放在最后:条件语句涉及到范围和其他索引查询时,如果先把范围条件放在前面,则范围条件对应的索引中后面的索引条件会全部失效;
  • 不等于索引失效:需要全表遍历然后进行对比,但是在select 后面查询索引字段,不需要回表时则会使用索引;
  • is null可以使用索引,is not null无法使用索引:所以实际开发中尽量增加is not null约束,int类型默认为0,字符串默认为空字符串'';
  • not like,like以通配符%开头索引失效:在select 后面使用索引字段,则索引生效,减少回表查找,优化器会选择覆盖索引,但是阿里开发手册规定页面搜索严谨左模糊或者全模糊,如果需要请走搜索引擎来解决;
  • or 前后存在非索引的列,索引失效:or前后都是索引列才能生效,前后相当于两条语句全部执行;
  • 数据库和表的字符集统一使用:字符不一致时涉及到字符转换,类型转换会导致索引失效。

三、关联查询优化

示例:

  • 左外连接:select * from 'type' left join book on type.card = book.card;

没有索引时:取type表中的第一条card,与book表中所有card匹配,取出book表中匹配的内容和type表全部内容,然后取type中的第二条再进行book中的card匹... 直到type中所有card都匹配完成;

给book添加索引后:从type表中取出card后,在book表中查找可以使用book表中的索引,避免对book表进行全表扫描。

注意:被驱动表需要添加索引并且查找条件在两个表中类型一致,前面的未必为驱动表,驱动器会解析成内连接。

  • 内连接:select * from type inner join book on type.card = book.card;

没有索引时:驱动表与被驱动表的选择取决于驱动器的计算,选择成本最低的(一般会选择数据量小的)为驱动表,然后进行匹配,匹配出两者的共同数据;

注意:对于内连接来讲,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表;两个表的链接条件都存在索引的情况下,会选择小表作为驱动表。小结果集驱动大结果集原则。

join原理:本质是嵌套循环匹配,查询优化器上面为驱动表,下面为被驱动表,从驱动表取出数据与被驱动表进行匹配,加索引为了减少内层表数据的匹配次数。为了防止索引失效进行两个表的全表扫描,引入了块嵌套循环利用缓冲池减少驱动表加载次数,将驱动表放到缓冲池中,被驱动表加载一次。

# 查看block_nested_loop状态,默认是开启状态
show variables like '%optimizer_switch%';
# 查看参数大小
show variables like '%join_buffer%';

小结:

  • 整体效率:INLJ>BNLJ>SNLJ;
  • 永远用小结果集驱动大结果集(本质建撒后外层循环的数据数量);
  • 为被驱动表匹配的条件增加索引(减少内层表循环的匹配次数);
  • 增大join buffer size的大小(一次缓存的数据越多,内层表的扫表次数越少);
  • 减少驱动表不必要的字段查询(字段越少,join buffer 所缓存的数据就越多);
  • mysql 8.0默认使用hash join,只能使用等值链接。

四、子查询优化

正常使用子查询时,mysql需要为内层查询建立一个临时表,临时表不会存在索引,然后外层查询从临时表中查询记录,查询完毕后,再撤销这些临时表。这样会消耗过多IO资源,产生大量的慢查询,对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

mysql中建议使用join代替子查询。

五、排序优化

1、为什么order by字段要加索引?

  • mysql中支持两种排序,FileSort和Index。
  • FileSort一般在内存中进行排序,占用cpu较多。
  • Index排序中,索引可以保证数据的有序性,不需要再进行排序。
  • where和order by子句中使用索引,where中避免全表扫描,order by为了避免使用FileSort排序。
  • 无法使用index时,需要对FileSort进行调优。
  • 数据量太大时,即使创建索引,但是由于需要进行回表查找,则索引会失效,增加limit时则会使用索引。
  • 排序条件一个升序,一个降序,索引会失效。

六、分页优化

  • 在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容;
explain select * from student t,(select id from student order by id limit 2000000,10) a where t.id = a.id;
  • 将Limit查询转换成某个位置的查询
    explain select * from student where id > 2000000 limit 10;

多使用Limit 1,找到一个结果之后就不会继续扫描,如果数据表对字段建立了唯一索引,那么通过索引进行查询,不会全表扫描就不需要加上limit 1。

七、覆盖索引

select 后查询索引条件,对于一些索引失效的案例可以继续使用索引,查找的是索引字段可以避免回表扫描。

减少是用select * 使用之后选要将* 转换为数据表的具体字段,浪费时间,而且无法使用覆盖索引。

1、覆盖索引的好处

  • 避免innodb表进行索引的二次查询(回表);
  • 可以把随机io变成顺序IO加快查询效率;

2、覆盖索引的弊端

  • 索引维护需要成本。

八、索引下推

  • 比如创建一个联合索引,在查询时只是用了前两个索引,这中情况会发生索引下推。
  • ICP的开启/关闭
# 打开索引下推
set optimizer_switch = 'index_condition_pushdown=off';
# 关闭索引下推
set optimizer_switch = 'index_condition_pushdown=on';
  • 当是用索引下推时,EXPLAIN中Extra列内容显示为Using index condition;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
索引优化是数据库性能优化的重要部分,可以大幅提高查询效率。索引是一种数据结构,用于加速对表中数据的查找和排序。在查询中使用索引可以避免全表扫描,提高查询速度。以下是索引优化的一些技巧: 1. 确定索引类型 MySQL 支持多种索引类型,包括 B-Tree 索引、Hash 索引、Full-Text 索引等。不同的索引类型适用于不同的场景。例如,B-Tree 索引适用于范围查询,而 Hash 索引适用于等值查询。因此,在创建索引时,需要根据实际情况选择合适的索引类型。 2. 确定索引字段 索引字段是指在哪些字段上创建索引。一般来说,需要在经常用于查询的字段上创建索引。但是,创建太多的索引也会影响性能,因为每个索引都需要占用存储空间,而且在插入、更新和删除数据时也会增加额外的开销。因此,需要权衡索引的数量和存储空间的使用。 3. 索引覆盖查询 索引覆盖查询是指查询结果可以从索引中直接获取,而不需要再访问数据表。这样可以避免访问数据表的开销,提高查询效率。要实现索引覆盖查询,需要在查询语句中包含索引字段,并且查询语句只查询索引字段。 4. 索引列的顺序 在创建索引时,需要注意索引列的顺序。一般来说,应该把区分度高的列放在前面。区分度是指该列中不同值的数量与总行数之比。区分度越高,表示该列的值越能区分不同的行,因此放在前面可以提高索引效率。 5. 索引的长度 索引的长度是指索引列中的字符数或字节数。索引的长度对查询效率有影响,因为索引长度越长,索引树的高度越高,查询时需要访问的磁盘块数也就越多。因此,需要根据实际情况选择合适的索引长度。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值