当前位置:首页 > 笔记 > MYSQL千万级别数据库查询怎么提高查询效率

MYSQL千万级别数据库查询怎么提高查询效率

nyoung1个月前 (05-04)笔记65

 

 

 

一、查询效率慢的原因

1. 没有加索引或者索引失效

  • where条件使用如下语句会导致索引失效:null、!=、<>、or、in(非要使用,可用关键字exist替代)、not in、'%abc%;

  • 使用参数:num=@num、表达式操作:where num/2=100、函数操作:where substring(name,1,3)=‘abc’-name;

--exist代替inselect id from table where num exist(1,2,3,4,5,6)--where字句使用or连接条件的替代方案select id from table where num=10unionselect id from table where num=20; 
--连续的数值,能用between就不要用inselect id from table where num between 1 and 3; 
--使用参数的替代方案--如果在where子句中使用参数(num = @num;),也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引的输入项。select id from table where num=@num;--替代为:强制查询使用索引:select id from table with(index(索引名)) where num=@num; 
--使用表达式的替代方案select id from table where num/2=100;
替代为select id from table where num=100*2; 
--使用函数操作的替代方案select id from t where substring(name, 1, 3) = ’abc’–name; //以abc开头
替代为select id from t where name like ‘abc%’;//单个百分号

 

2:查询的数据量过大,返回不必要的行和列

只查询有用的字段,不要用*查询出所有字段。
采用多线程多次查询。如果查询条件是某段时间之类的范围条件,可以把时间条件切分,多次查询结果合并。

3:锁或者死锁

4: I/O吞吐量小,形成瓶颈效应。

5:内存不足。少造对象,对象只在需要使用时创建,不要在整个上下文传递。及时清理jvm内存。

6:网络速度慢。


二、一些SQL优化方法

1:如果索引是复合索引,必须使用该索引的第一个字段作为条件才能保证系统使用该索引,否则索引不会被引用,并且应尽可能的让字段顺序与索引顺序一致。

2:索引并不是越多越好,一个表索引最好不要超过6个。索引固然可以提高select效率,但是也降低了insert效率和update效率,因为insert和update会使索引重建,所以怎么建索引需要慎重考虑。

3:建表的一些优化:

尽量使用数字型字段,若数据只含有数值信息尽量不要设计成字符型,这会降低查询和连接的性能,并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每个字符,而对于数字型而言只需比较一次就够了。
尽量使用varchar/nvarchar代替char/nchar,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高一些。
4:任何地方都不要使用select * from table,用具体的字段列表代替*,不要返回用不到的任何字段。

5:并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

6:尽量避免大事务操作,提高系统并发能力。


发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。