Mysql 查询性能优化

msyqls 性能优化

先前写过一篇关于Mysql入库性能调优的文章，既然入库了，数据必然是要使用的，查询并以某种可视化的方式展现数据是很常见的使用方式。（如：D3.JS，Echarts，Highchart, i2 或是其他的可视化工具）。

寒天一滴水，点点在心头。这里记录下Mysql优化的一些学习笔记。

对于查询的优化，首先应该避免全表扫描，所以在where 及 order by涉及到的列上建索引。

避免使用null值的判断，否则会导致全表扫描。应该在入库的时候设置正确的数据类型，以及默认值。比如 where col=0 ; 而不应该用 where col is null;
如果可以，避免使用范围查询，如： !=, > ,< , in , not in , between 等操作符，这些会导致查询不走索引，造成全表扫描。
由于Mysql索引符合最左匹配原则，所以 like ‘%str%’ 是不会走索引的。而 like ‘str%’ 这个确实会走索引的。所以，尽量不要再mysql中做全文检索的操作，这种工作可以用solr，elasticsearch 这种搜索引擎来处理。
避免在 where 子句中对字段进行函数操作，这会导致放弃索引而造成全表扫描。
对于 in 的操作可以考虑使用 join…on 来关联查询
group by col 默认的情况下，group by 会对col进行排序，这就是为什么在使用 explain的时候在extr这列会有 filesort 。所以，如果仅仅只需要分组，而不需要排序的话，在 group by 后面加上 order by null ( 如: select username , count(1) as cnt from user group by username order by null ;) 。这样会快很多。因为少了filesort。 filesort是很慢的。
整数类型查询不使用引号。字符串查询加上引号，否则不会使用索引。对于字符串的查询不合理的做法：select xx from tb where str=111; ；合理的查询语句应该是： select xx from tb where str='111'
对于select语句，尽量是需要什么就查什么，不要一下子就习惯性的使用 select * from t;，而应该只把你想要的查出来就好了。 select name from t ; ；我个人还有一个习惯，在对一张未知的表进行查询之前，习惯性的会先查总数 select count(1) from t; ，如果总数不是太大，就可以直接查所有了。不然就使用limit限制一下查询条数。
说到limit，也有一点值得说的，比如你知道某一个查询结果只会有一条记录，那么可以在查询语句上限制 limit 1; 。比如在有几条记录，我们想要根据时间，取到最近的一条记录。如果不适用limit的话，我们可能会这样查:select xtime from t where xtime=(select max(xtime) from t) ; 。所以，用limit的话就变成 select xtime from t order by xtime limit 1 ; 。响应文章开头所属的，对于order by 的列记得建个索引。建索引语法:create index your_idx on tablename(colNmae) l

总结：多用 EXPLAIN 分析查询语句

(附)查询缓存相关的命令

-- 设置查询缓存:

-- 查询缓存是否开启， on:表示开启， off:表示关闭
> select @@query_cache_type;
> set session query_cache_type=off;
> set session query_cache_type=on;

-- 查询缓存的大小:
> select @@global.query_cache_size;
> set @@global.query_cache_size=1000000;

-- 查询缓存的上限:
> select @@global.query_cache_limit;
> set @@global.query_cache_limit=5000000;

<<< 捐赠 >>>

转载请注明出处！原文地址： http://webinglin.github.io

Reference:

http://webinglin.github.io

Mysql 查询性能优化

(附)查询缓存相关的命令

Reference:

留言