1.in和not in子查询优化

not in 是不能命中索引的,所以以下子查询性能很低。

如果是确定且有限的集合时,可以使用。如 IN (0,1,2)。

用 exists或 notexists代替

select *  from test1  where EXISTS (select * from test2  where id2 = id1 )
select * FROM test1 where NOT EXISTS (select * from test2 where id2 = id1 )

尽量用join代替

 select id1 from test1 INNER JOIN test2 ON id2 = id1
select id1 from test1 LEFT JOIN test2 ON id2 = id1 where id2 IS NULL

 2.模式匹配like '%xxx%'优化

like 'xxx%可以用到索引,但是like '%xxx%却无法用到索引。

EXPLAIN
SELECT 书名 FROM cbbd WHERE 书名 LIKE '%人%'

MySQL性能优化之道-LMLPHP

EXPLAIN
SELECT 书名 FROM cbbd WHERE 书名 LIKE '人%'

MySQL性能优化之道-LMLPHP

 3.大表高效取分页数据

SELECT * FROM cbbd a INNER JOIN (SELECT id FROM cbbd  LIMIT 800000,5) b ON a.id=b.id

4.COUNT(*)统计函数如何加快速度

在mysql 5.7中,对于select count(*) from table 的优化,可以设置为:

select count(*) from table where uid>0

其中uid为辅助索引,辅助索引不存放数据,而是有一个指针指向对应的数据块,因此速度更快;

从实际业务角度优化:既然我们知道innodb引擎并不适合做 select count(*)查询,那么我们回过头来看看实际业务的需求是怎样的?

select count(*) from cbbd

采用这样的查询是因为需要查出实时参与活动的总人数,但是这里的“实时”是否一定要实时,采用准实时,用户会反感吗?或者即使有一定的偏差,普通用户能感知吗?如果我们得出答案,这个实时的参与数只要准实时,允许一定的偏差,那么就好办了:

select id from  cbbd  order by id desc limit 1

我们只要查最新的一条记录,id是自增字段,取当前的这个id值就可以大约知道总参与人数了(注意我们的项目里并不会删除参与记录)。

5.or条件优化

当使用or的时候是不会用到索引的

SELECT * FROM aladdin_resource WHERE state = 1 OR state = 2;

解决办法就是用union替换or

select * from aladdin_resource where state=1 union select * from aladdin_resource where state=2;

6.ON DUPLICATE KEY UPDATE

mysql中高效的主键冲突处理,冲突则执行update,不冲突则执行insert

注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

IF (SELECT * FROM ipstats WHERE ip='192.168.0.1') {
UPDATE ipstats SET clicks=clicks+1 WHERE ip='192.168.0.1';
} else {
INSERT INTO ipstats (ip, clicks) VALUES ('192.168.0.1', 1);
}

以上几行代码可以用以下一行代码取代

INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1;

7.去掉不必要的降序可以提升性能

8.用where子句替换having子句

9.合理使用索引

合理使用单列所引和联合索引

字段使用函数将不能使用索引

10.致命的无引号导致全表扫描,无法用到索引

11.

MySQL性能优化之道-LMLPHP

12.

MySQL性能优化之道-LMLPHP

MySQL性能优化之道-LMLPHP

13.

MySQL性能优化之道-LMLPHP

14. Multi-Range Read 多范围读(MRR)

MySQL 5.6版本提供了很多性能优化的特性,其中之一就是 Multi-Range Read 多范围读(MRR) , 它的作用针对基于辅助/第二索引的查询,减少随机IO,并且将随机IO转化为顺序IO,提高查询效率。

相关参数

我们可以通过参数 optimizer_switch 的标记来控制是否使用MRR,当设置mrr=on时,表示启用MRR优化。mrr_cost_based 表示是否通过 cost base的方式来启用MRR.如果选择mrr=on,mrr_cost_based=off,

则表示总是开启MRR优化。参数read_rnd_buffer_size 用来控制键值缓冲区的大小。

https://www.cnblogs.com/Terry-Wu/p/11609743.html

05-17 07:52