Mysql 索引知识(二)

正题:
        上一篇我们讲到Msyql中,Explain关键字,相关的一些列,今天我们来补充一些,其他的。

一、为什么我们需要了解和学习,执行计划?

首先确定SQL调优的都是什么,在我们工作中,不会什么SQL上来就是调优,当我们发现当慢查询或SQL执行遇到瓶颈时,首先我们要分析SQL的执行计划,找到导致慢SQL的原因。

为什么要关注sql执行计划?

因为sql执行计划可以告诉我们很多关于如何优化sql的信息。如何通过SQL计划访问中的数据(是使用全表扫描还是索引查找)一个表中的多个表中可能有不同的索引。表的类型是什么,它们是子查询、关联查询等等。

-- 查看sql执行计划
explain SQL;
-- 查看表结构
desc 表名;
-- 查看建表语句
show create table 表名;

1、desc 表名(查看表结构):

如下 :

Field :你表中的字段名,

Type:你表中字段名的类型,

null:你表中的字段允不允许为空,

Key :你表中字段名是不是主键或者是不是索引,如下:PRI是主键,mul是索引

Defalut: 默认值是什么,如下create_time和update_time 是默认填的当前时间戳

Extra: 就是描述这个字段一些额外的信息,如下:我的id是自增的

2、show create table 表名:

Create Table列就是,建表语句

3、通过配置文件(Profile )查找查询成本消耗

-- 开启性能分析
set profiling=1;
 
-- 执行 SQL
select * from tb_user where id='123';
 
-- 获取 Query_ID
show profiles;
 
-- 查看详细的 profile 信息
show profile for query Query_ID;

3、通过 Optimizer Trace (优化器跟踪)表查看 SQL 执行计划树

--  开启 optimizer Trace 表查看 SQL 执行计划树
set session optimizer_trace='enabled=on';
 
-- 执行 SQL
select * from fork_business_detail where ODR_ID='123';
 
查询 information_schema.optimizer_trace 表,获取 SQL 查询计划树
 
-- 开启此项影响性能,记得用后关闭
set session optimizer_trace='enabled=off';

4、使用索引的一些情况


1.1、索引扫描
对于大表,如果扫描整个表,则返回查询需要很长时间,必须使用索引扫描来加快查询速度。但索引过多也会降低写入和修改的速度。而且,如果表数据和索引数据的比例失调,不利于后期的正常维护。(所以索引也不是越多越好,一定要选出最优最适合业务的列来作为索引)

1.2、哪些列是适合业务需要的索引
创建具有高选择性的索引也可以在状态列上创建索引(不是一定不可创建)。创建索引时,除了某些特殊情况外,应避免冗余索引。创建索引后,尽量不要过于频繁地修改它。对于可以在索引中完成的查询,不要返回到表。

与多个表关联的SQL必须在关联列上具有索引,并且具有一致的字段类型,以便MySQL在执行嵌套循环连接查找时可以使用索引并且不会因为字段类型不匹配而发生隐式转换,从而导致无法使用索引。

当关联多个表时,请尝试使用具有小结果集的表作为驱动表,小结果集的表不是小表。

1.3、模糊匹配查询
当查询条件完全模糊时,例如“%**%”,则不能使用索引,在这种情况下,必须加具有高选择性的其他列或 where 条件,作为加快查询速度的补充。对于这种全模糊匹配场景,可以在 ES (elasticsearch)中求解。

2、排序篇
order by/group by SQL涉及排序,必须在索引中包含排序字段,并使排序字段的排序顺序与索引列中的排序顺序相同,这可以避免排序或减少排序次数。

MySQL不关心 from 中出现的表的顺序,也不关心 where 中的顺序。

3、查询选择篇
MySQL也非常擅长处理简短的SQL。总体时间将更短,并且不会产生臃肿的SQL,这很难理解和优化。

尽量不要使用子查询重新扫描由子查询生成的临时表时,将没有要查询的索引,只能执行完整的表扫描

4、MySQL 内部优化 SQL
1、重新定义表的关联顺序。当查询多个表时,MySQL 优化器将自动选择驱动器表和表的联接顺序,基于成本规则,SQL 执行时间将大大减少

2、使用等效变化规则,MySQL 可以合并或减少一些比较,并删除一些总是正确或不正确的判断。

3、优化 count()、min() 和 max()。索引和列是否可以为空通常可以帮助 MySQL 优化此类表达式。例如,要查找最小值,只需查找索引树最左侧的第一条记录。
 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值