MySQL千万级数据库查询怎么提高查询效率
时间:2019-05-14 17:24 来源:linux.it.net.cn 作者:IT
查询效率慢的原因：
1：没有加索引或者索引失效
where条件使用如下语句会索引失效：null、！=、<>、or连接、in（非要使用，可用关键字exist替代）和not in、'%abc%'；
使用参数：num=@num、表达式操作：where num/2=100、函数操作：where substring（name，1,3）=‘abc’-name；
--exist代替in
select id from table where num exist(1,2,3,4,5,6)
--where字句使用or连接条件的替代方案
select id from table where num=10
union
select id from table where num=20;
--连续的数值，能用between就不要用in
select 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：尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。（游标是很老的功能了，几乎废弃了。）
6：并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
7：尽量避免大事务操作，提高系统并发能力。
注意事项：
1：使用like时，一定要记得判空
... where name like ‘%’.变量名.'%'; (变量值是从外面传进来的)
如果：变量是空，就变成如下sql
...where name like '%%'; -- 这个条件造成的后果就是 ‘选出全部数据 or 更新全部数据 or 删除全部数据’ 相当于没有写条件，出现后是相当严重的问题。
2：like 配合 通配符：%和_ 的使用
通配符的分类:
%百分号通配符: 表示任何字符出现任意次数**(可以是0次)**.
_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.
like操作符:
LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.
注意: 如果在使用like操作符时,后面的没有使用通用匹配符效果是和=一致的,SELECT * FROM products WHERE products.prod_name like '1000';只能匹配的结果为1000,而不能匹配像JetPack 1000这样的结果.
1)%通配符使用:
匹配以"yves"开头的记录:(包括记录"yves")
SELECT * FROM products WHERE products.prod_name like 'yves%';
匹配包含"yves"的记录(包括记录"yves")
SELECT * FROM products WHERE products.prod_name like '%yves%';
匹配以"yves"结尾的记录(包括记录"yves",不包括记录"yves ",也就是yves后面有空格的记录,这里需要注意)
SELECT * FROM products WHERE products.prod_name like '%yves';
2)_通配符使用:
SELECT * FROM products WHERE products.prod_name like '_yves';
匹配结果为: 像"yyves"这样记录.
SELECT * FROM products WHERE products.prod_name like 'yves__';
匹配结果为: 像"yvesHe"这样的记录.(一个下划线只能匹配一个字符,不能多也不能少)
(责任编辑：IT)
查询效率慢的原因：
1：没有加索引或者索引失效
where条件使用如下语句会索引失效：null、！=、<>、or连接、in（非要使用，可用关键字exist替代）和not in、'%abc%'；
使用参数：num=@num、表达式操作：where num/2=100、函数操作：where substring（name，1,3）=‘abc’-name；
--exist代替in
select id from table where num exist(1,2,3,4,5,6)
--where字句使用or连接条件的替代方案
select id from table where num=10
union
select id from table where num=20;
--连续的数值，能用between就不要用in
select 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：尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。（游标是很老的功能了，几乎废弃了。）
6：并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
7：尽量避免大事务操作，提高系统并发能力。
注意事项：
1：使用like时，一定要记得判空
... where name like ‘%’.变量名.'%'; (变量值是从外面传进来的)
如果：变量是空，就变成如下sql
...where name like '%%'; -- 这个条件造成的后果就是 ‘选出全部数据 or 更新全部数据 or 删除全部数据’ 相当于没有写条件，出现后是相当严重的问题。
2：like 配合 通配符：%和_ 的使用
通配符的分类:
%百分号通配符: 表示任何字符出现任意次数**(可以是0次)**.
_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.
like操作符:
LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.
注意: 如果在使用like操作符时,后面的没有使用通用匹配符效果是和=一致的,SELECT * FROM products WHERE products.prod_name like '1000';只能匹配的结果为1000,而不能匹配像JetPack 1000这样的结果.
1)%通配符使用:
匹配以"yves"开头的记录:(包括记录"yves")
SELECT * FROM products WHERE products.prod_name like 'yves%';
匹配包含"yves"的记录(包括记录"yves")
SELECT * FROM products WHERE products.prod_name like '%yves%';
匹配以"yves"结尾的记录(包括记录"yves",不包括记录"yves ",也就是yves后面有空格的记录,这里需要注意)
SELECT * FROM products WHERE products.prod_name like '%yves';
2)_通配符使用:
SELECT * FROM products WHERE products.prod_name like '_yves';
匹配结果为: 像"yyves"这样记录.
SELECT * FROM products WHERE products.prod_name like 'yves__';
匹配结果为: 像"yvesHe"这样的记录.(一个下划线只能匹配一个字符,不能多也不能少)
(责任编辑：IT) |