mysql查询语句优化

联表查询:

1️⃣SELECT ta.sid, ta.instance_id, ta.user_id, *******
FROM t_action ta, t_instance ti
WHERE    ta.status = 3 *******
AND ta.instance_id = ti.sid
ORDER BY ta.sid DESC
LIMIT 10

 这句explain的结果就是: ta:Using index; Using temporary; Using filesort    ti:Using where

 

说明:这里的ta和ti表 关联查询 ta表的instance_id和 ti的sid字段关联,再通过SID排序

因为ta表里是没有game_id的 所以order by的时候 会把ti表的结果给缓存起来

但因为ti表要缓存的数据很多,最后多出的又要放进磁盘内存中,所以就有了using filesort,最后再把磁盘的一并释放掉;每次查询都如此往复,自然而然查询会慢下来.

 

解决方案:将这个查询语句拆分成两句去查询或者在ta表中野加一个game_id字段 

 

2️⃣SELECT COUNT(sid)

 

FROM T_***
WHERE sid > 0

因为公司数据库mysql用的是InnoDB而不是myisam, 如果是myisam数据库就会很快,而InnoDB需要全盘扫描会很慢; 

两者区别: 

对于count()查询来说MyISAM更有优势

因为MyISAM存储了表中的行数记录,执行SELECT COUNT() 的时候可以直接获取到结果,而InnoDB需要扫描全部数据后得到结果。

但是注意一点:对于带有WHERE 条件的 SELECT COUNT()语句两种引擎的表执行过程是一样的,都需要扫描全部数据后得到结果

 

解决方案:不要一下子给出结果,进行定时任务缓存处理,当对该表进行修改操作时将缓存删除; 

 

 

另外一些优化的例子:

1)尽量少用负条件查询;

假设我们有一个Order表,表中有一个字段是Status,这个字段有4个值,分别是0=待支付、1=待发货、2=待收货、3=已完成。

这时,我们要查询所有已经支付的订单,很多人就会写这样的SQL:

select * from Order where Status != 0

这就是一个不好的习惯了。负向条件查询(例如:!=、not in、not exists)都是不能使用索引的,当Order表中的数据到达一定量级时,这个查询的效率会急剧的下降。所以,正确的写法应该是:

select * from Order where Status in (1,2,3)

2)尽量少用前导模糊查询;

假设我们现在要根据用户的订单号(OrderNo)查询用户的订单,如果是直接通过SQL查询的话,尽量不要使用前导模糊查询,也就是:

select * from Order where OrderNo like '%param'

或者

select * from Order where OrderNo like '%param%'

因为,前导模糊查询是无法命中索引的,所以,会整个数据库去检索,效率相当的差,而非前导模糊查询则是可以使用索引的。

因此,我们尽量不要把通配符放在前面,改成下面这样:

select * from Order where OrderNo like 'param%'

3)尽量不要在条件字段上加运算;

假设,现在有一个需求,是要查询2018年全年的订单数据,我们就需要通过创建时间(CreateTime)来进行检索,但是,有些程序员就喜欢这样写SQL:

select * from Order where Year(CreateTime)=2018

然后,每次执行时就会发现,查询的速度异常的慢,导致了大量的请求挂起甚至超时。这是因为,我们即使在CreateTime上建立了索引,但是,如果使用了运算函数,查询一样会进行全表的检索。

所以,我们可以改成这样:

select * from Order where CreateTime > '2018-1-1 00:00:00'

4)当查询允许null的列值时,需要特别注意;

我们在创建表的字段时,如果这个字段需要作为索引时,尽量不要允许Null。因为,单列索引不会存Null值,复合索引不存所有索引列都为Null的值,所以如果列允许为Null,可能会得到“不符合预期”的结果集。

例如:我们有一个User表,其中有UserName字段记录了用户的名字,并且添加了索引。

不要这样写SQL 改掉这些坏习惯

现在我们执行了这样一个查询:

select * from User where UserName != '小倩'

但结果是这样的

不要这样写SQL 改掉这些坏习惯

 那位UserName为Null的数据并没有能包括进来。因此,如果我们想要包含这个用户的话,最好能够设置一个默认值。

5)复合索引使用时需要注意顺序;

登录,肯定是我们使用得最多的一个查询了,为了保证效率,我们为LoginID和Password加上了复合索引。

当我们使用

select * from User where LoginID = '{LoginID}' and Password = '{Password}'
select * from User where Password = '{Password}' and LoginID = '{LoginID}'

查询时,都是能够准备的命中索引。当我们使用:

select * from User where LoginID = '{LoginID}' 

查询时,也是能够命中索引的。但是,当我们使用

select * from User where Password = '{Password}' 

查询时,确无法命中索引,这是什么原因呢?

这是由于,复合索引对于查询的顺序是非常的铭感的,所以,符合索引中包含了几种规则,其中就有全列匹配和最左前缀匹配。

当所有列都能够匹配时,虽然查询的顺序上有不同,但是查询优化器会将顺序进行调整,以满足适合索引的顺序,所以,顺序的颠倒是没有问题的。

但是,如果所有列不能匹配时,就必须满足最左前缀匹配了,也就是,必须按照从左到右的顺序进行排列。因此,当我们建立是索引是<LoginID, Password>时,where Password = '{Password}' 就不满足最左前缀规则,无法命中索引了。

 6)结果唯一时别愣着;

 

通常,我们设计User表时,并不会把LoginID作为主键,但是,LoginID确会在业务逻辑中验证唯一性,因此,如果使用

 

select * from User where LoginID = '{LoginID}'

查询时,结果一定只有一条。但是,数据库是不知道的,即使找到了这唯一的一条结果,他也会一直继续,直到扫描完所有的数据。

因此,在执行这样的查询时,我们可以优化一下,改成:

select * from User where LoginID = '{LoginID}' limit 1

这样,当查询到结果时,就不会再继续了。

OVER! 

 

 

 

posted on 2018-11-12 10:59  断剑重铸之时  阅读(223)  评论(0编辑  收藏  举报