MySQL性能优化之道

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 '%人%'

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

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.

12.

13.

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

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