避免索引失效的一些原则

1.复合索引,不要跨列或无序使用,否则会使组合索引失效
复合索引,尽量使用全索引匹配

2.不要在索引上进行任何操作(计算、函数、类型转换),否则索引失效

EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid = 2;
//用到了at2个索引
EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid*2 = 2;
//用到了a1个索引
EXPLAIN SELECT * FROM book WHERE authorid*2 = 1 AND typeid*2 = 2;
//用到了0个索引
EXPLAIN SELECT * FROM book WHERE authorid*2 = 1 AND typeid = 2;
//用到了0个索引
//原因:对于复合索引,左边失效了右边全部失效。(a,b,c)中b失效,那b,c都失效,a没有失效。(如果不是复合索引,则没有关系)

在这里插入图片描述

DROP INDEX idx_atb ON book;
ALTER TABLE book ADD INDEX idx_authroid(authroid);
ALTER TABLE book ADD INDEX idx_typeid(typeid);
EXPLAIN SELECT * FROM book WHERE authorid*2 = 1 AND typeid = 2;

在这里插入图片描述
3.复合索引不能使用不等于(!=或<>)或is null(is not null),否则自身以及右侧索引全部失效。
EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid = 2;
在这里插入图片描述
只用了一个索引,原因:
–SQL优化,是一种概率层面的优化。至于是否实际使用了我们的优化,需要通过explain进行推测
EXPLAIN SELECT * FROM book WHERE authorid != 1 AND typeid = 2;
在这里插入图片描述
上面的结论是针对复合索引,对单独的索引没有上述影响。

体验概率情况:
  原因是服务层中有SQL优化器,可能影响我们的优化。

show index from book;
drop index idx_typeid on book;
drop index idx_authorid on book;
alter table book add index idx_book_at(authorid,typeid);

在这里插入图片描述

explain select * from book where authorid = 1 and typeid = 2;
//复合索引全部使用,不同于单独索引
explain select * from book where authorid >1 and typeid = 2;
//复合索引中如果有大于号,则自身和右侧索引全部失效。
explain select * from book where authorid =1 and typeid > 2;
//复合索引at全部使用(**体验概率**情况)

在这里插入图片描述
—明显的概率问题—

explain select * from book where authorid < 1 and typeid = 2;//复合索引at中只用到了一个索引
explain select * from book where authorid < 4 and typeid = 2;//复合索引at全部失效

在这里插入图片描述
  明显发现改个数字索引的使用情况都不一样,说明sql优化索引的情况是个概率问题,我们用的优化手段通常都是大部分适用的结论,但由于sql优化器的原因,该结论不是100%正确。
一般而言,范围查询(> < in),之后的索引失效。

4.思考:那到底有没有100%使用索引的情况呢?
答案:有,索引覆盖(using index)—尽量使用索引覆盖

5.like尽量以“常量”开头,不要以‘%’开头,否则索引失效。

select * from xx where name like '%x%';//name索引失效
show index from teacher;

explain select * from teacher where tname like '%x%';//tname索引失效
explain select * from teacher where tname like 'x%';//tname索引有效
explain select tname from teacher where tname like '%x%';//如果必须使用like '%x%',可以使用索引覆盖,挽救一部分(range->index)。

在这里插入图片描述

6.尽量不要使用类型转换(显示、隐式),否则索引失效。

desc teacher;//tname是字符串
show index from teacher;//tname是索引
explain select * from teacher where tname = 'abc';
explain select * from teacher where tname = 123;//程序将123->'123',即进行了类型转换,因此索引失效

在这里插入图片描述
7.尽量不要使用or,否则索引失效

explain select * from teacher where tname = '' and tcid > 1;
explain select * from teacher where tname = '' or tcid > 1;//甚至让左边的索引失效

在这里插入图片描述
8.一些其他的优化方法
(1)exist和in
select … from table exist/in (子查询);
如果主查询的数据集大,则使用in,效率高。
如果子查询的数据集大,则使用exist,效率高。
exist语法
  将主查询的结果,放到子查询结果中进行条件校验(看子查询是否有数据与主查询结果相对应,如果有数据则校验成功),如果符合校验,则保留数据。

select tname from teacher where exists (select * from teacher);
//这里子查询中就有主查询的结果
//等价于select tname from teacher;
select tname from teacher where exists (select * from teacher where tid = 999);
//没有数据

在这里插入图片描述
(2)order by优化—using firesort
有两种算法:双路排序、单路排序(根据IO的次数)
MySQL4.1之前,默认使用双路排序
  双路:扫描两次磁盘(1.从磁盘读取排序字段,对排序字段进行排序(在buffer(缓存区)中进行排序);2.扫描其他字段)。IO较消耗性能
MySQL4.1之后,默认使用
单路排序

  只读取一次(全部字段),在buffer中进行排序。但这单路排序会有一定的隐患(不一定真的是“单路 1次IO”,有可能多次IO—原因:如果数据量特别大,则无法将所有字段的数据一次性读取完毕,因此会进行“分片读取、多次读取”)。

注意:单路排序比双路排序会占用更多的buffer。
  单路排序在使用时,如果数据量大,可以考虑调大buffer的容量大小:set max_length_for_sort_data = 1024 单位byte
如果max_length_for_sort_data值太低,则MySQL会自动从单路->双路(太低:需要排序的列的总大小超过了max_length_for_sort_data定义的字节数)

提高order by查询的策略:
a.选择使用单路、多路;调整buffer的容量大小;
b.避免select * …(->程序还要去计算表示哪些字段)
c.复合索引不要跨列使用,避免using firesort
d.保证全部的排序字段 排序的一致性(都是升序或降序)

9.SQL排查 —慢查询日志
  MYSQL提供的一种日志记录,用于记录MySQL中响应时间超过阈值的SQL语句(long_query_time,默认10秒)—一个sql10秒都没查询出来,就是慢sql,需要优化。
慢查询日志默认时关闭的;建议:开发调优时打开,而最终部署时关闭。
检查是否开启了慢查询日志:show variables like ‘%slow_query_log%’;
临时开启:set global slow_query_log = 1;—在内存中开启
exit;
service mysql restart;
show variables like ‘%slow_query_log%’;—off
永久开启:
/etc/my.cnf中追加配置:
vim /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slog.log
慢查询阈值:
show variables like ‘%long_query_time%’;
临时设置阈值:
set global long_query_time = 5;—设置完毕后,重新登陆后起效(不需要重启服务)
永久设置阈值:
/etc/my.cnf中追加配置:
vim /etc/my.cnf
[mysqld]
long_query_time=3;

展开阅读全文

Windows版YOLOv4目标检测实战:训练自己的数据集

04-26
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值