高mysql千万级大数据SQL查询优化几条经验
时间:2019-05-14 17:21 来源:linux.it.net.cn 作者:IT
本文主要内容：
1：查询语句where 子句使用时候优化或者需要注意的
2：like语句使用时候需要注意
3：in语句代替语句
4：索引使用或是创建需要注意
假设用户表有一百万用户量。也就是1000000.num是主键
1：对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by 涉及的列上创建索引。
因为：索引对查询的速度有着至关重要的影响。
2：尽量避免在where字句中对字段进行null值的判断。否则将会导致引擎放弃使用索引而进行全表扫描。
例如：select id from user where num is null 。可以将num是这个字段设置默认值0.确保表中没有null值，然后在进行查询。
sql如下：select id from user where num=0;
（考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引）
3：应尽量避免在where子句中使用!=或者是<>操作符号。否则引擎将放弃使用索引，进而进行全表扫描。
4：应尽量避免在where子句中使用or来连接条件，否则导致放弃使用索引而进行全表扫描。可以使用 union 或者是 union all代替。
例如： select id from user where num =10 or num =20 这个语句景导致引擎放弃num索引，而要全表扫描来进行处理的。
可以使用union 或者是 union all来代替。如下：
select id from user where num = 10;
union all
select id from user where num =20;
(union 和 nuion all 的区别这里就不赘述了)
5：in 和 not in 也要慎用，否则将会导致全表扫描。
in 对于连续的数组，可以使用between ...and.来代替。
例如：
select id from user where num in (1,2,3);
像这样连续的就可以使用between ...and...来代替了。如下：
select id from user where num between 1 and 3;
6：like使用需注意
下面这个查询也将导致全表查询：
select id from user where name like '%三'；
如果想提高效率，可以考虑到全文检索。比如solr或是luncene
而下面这个查询却使用到了索引：
select id from user where name like '张%'；
7：where子句参数使用时候需注意
如果在where子句中使用参数，也会导致全表扫描。因为sql只会在运行时才会解析局部变量。但优化程序不能将访问计划的选择推迟到运行时；必须在编译时候进行选择。然而，如果在编译时建立访问计划，变量的值还是未知大，因而无法作为索引选择输入项。
如下面的语句将会进行全表扫描：
select id from user where num = @num
进行优化，我们知道num就是主键。是索引。
所以可以改为强制查询使用索引：
select id from user where (index(索引名称)) where num = @num;
8：尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
例如：select id from user where num/2=100
应修改为：
select id from user where num = 100*2;
9：尽量避免爱where子句中对字段进行函数操作，这将导致引擎放弃索引，而进行全表扫描。
例如：
select id from user substring(name,1,3) = 'abc' ，这句sql的含义其实就是，查询name以abc开头的用户id
(注：substring(字段，start,end)这个是mysql的截取函数)
应修改为：
select id from user where name like 'abc%';
10：不要在where子句中的"="左边进行函数、算术运算或是使用其他表达式运算，否则系统可能无法正确使用索引
11：复合索引查询注意
在使用索引字段作为条件时候，如果该索引是复合索引，那么必须使用该索引中的第一个字段作为条件时候才能保证系统使用该所以，否则该索引将不会被使用，并且应尽可能的让字段顺序和索引顺序一致。
12：不要写一些没意义的查询。
例如：需要生成一个空表结构和user表结构一样(注：生成的新 new table的表结构和 老表 old table 结构一致)
select col1,col2,col3.....into newTable from user where 1=0
上面这行sql执行后不会返回任何的结果集，但是会消耗系统资源的。
应修改为：
create table newTable (....)这种语句。
13：很多时候用exists 代替 in是一个很好的选择。
比如：
select num from user where num in(select num from newTable);
可以使用下面语句代替：
select num from user a where exists(select num from newTable b where b.num = a.num );
14：并不是所有索引对查询都有效，sql是根据表中数据进行查询优化的，当索引lie(索引字段)有大量重复数据的时候，sql查询可能不会去利用索引。如一表中字段 sex、male、female 几乎各一半。那么即使在sex上创建了索引对查询效率也起不了多大作用。
15：索引创建需注意
并非索引创建越多越好。索引固然可以提高相应的查询效率，但是同样会降低insert以及update的效率。因为在insert或是update的时候有可能会重建索引或是修改索引。所以索引怎样创建需要慎重考虑，视情况而定。一个表中所以数量最好不要超过6个。若太多，则需要考虑一些不常用的列上创建索引是否有必要。
(责任编辑：IT)
| 本文主要内容： 1：查询语句where 子句使用时候优化或者需要注意的 2：like语句使用时候需要注意 3：in语句代替语句 4：索引使用或是创建需要注意 假设用户表有一百万用户量。也就是1000000.num是主键 1：对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by 涉及的列上创建索引。 因为：索引对查询的速度有着至关重要的影响。 2：尽量避免在where字句中对字段进行null值的判断。否则将会导致引擎放弃使用索引而进行全表扫描。 例如：select id from user where num is null 。可以将num是这个字段设置默认值0.确保表中没有null值，然后在进行查询。 sql如下：select id from user where num=0; （考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引） 3：应尽量避免在where子句中使用!=或者是<>操作符号。否则引擎将放弃使用索引，进而进行全表扫描。 4：应尽量避免在where子句中使用or来连接条件，否则导致放弃使用索引而进行全表扫描。可以使用 union 或者是 union all代替。 例如： select id from user where num =10 or num =20 这个语句景导致引擎放弃num索引，而要全表扫描来进行处理的。 可以使用union 或者是 union all来代替。如下： select id from user where num = 10; union all select id from user where num =20; (union 和 nuion all 的区别这里就不赘述了) 5：in 和 not in 也要慎用，否则将会导致全表扫描。 in 对于连续的数组，可以使用between ...and.来代替。 例如： select id from user where num in (1,2,3); 像这样连续的就可以使用between ...and...来代替了。如下： select id from user where num between 1 and 3; 6：like使用需注意 下面这个查询也将导致全表查询： select id from user where name like '%三'； 如果想提高效率，可以考虑到全文检索。比如solr或是luncene 而下面这个查询却使用到了索引： select id from user where name like '张%'； 7：where子句参数使用时候需注意 如果在where子句中使用参数，也会导致全表扫描。因为sql只会在运行时才会解析局部变量。但优化程序不能将访问计划的选择推迟到运行时；必须在编译时候进行选择。然而，如果在编译时建立访问计划，变量的值还是未知大，因而无法作为索引选择输入项。 如下面的语句将会进行全表扫描： select id from user where num = @num 进行优化，我们知道num就是主键。是索引。 所以可以改为强制查询使用索引： select id from user where (index(索引名称)) where num = @num; 8：尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。 例如：select id from user where num/2=100 应修改为： select id from user where num = 100*2; 9：尽量避免爱where子句中对字段进行函数操作，这将导致引擎放弃索引，而进行全表扫描。 例如： select id from user substring(name,1,3) = 'abc' ，这句sql的含义其实就是，查询name以abc开头的用户id (注：substring(字段，start,end)这个是mysql的截取函数) 应修改为： select id from user where name like 'abc%'; 10：不要在where子句中的"="左边进行函数、算术运算或是使用其他表达式运算，否则系统可能无法正确使用索引 11：复合索引查询注意 在使用索引字段作为条件时候，如果该索引是复合索引，那么必须使用该索引中的第一个字段作为条件时候才能保证系统使用该所以，否则该索引将不会被使用，并且应尽可能的让字段顺序和索引顺序一致。 12：不要写一些没意义的查询。 例如：需要生成一个空表结构和user表结构一样(注：生成的新 new table的表结构和 老表 old table 结构一致) select col1,col2,col3.....into newTable from user where 1=0 上面这行sql执行后不会返回任何的结果集，但是会消耗系统资源的。 应修改为： create table newTable (....)这种语句。 13：很多时候用exists 代替 in是一个很好的选择。 比如： select num from user where num in(select num from newTable); 可以使用下面语句代替： select num from user a where exists(select num from newTable b where b.num = a.num ); 14：并不是所有索引对查询都有效，sql是根据表中数据进行查询优化的，当索引lie(索引字段)有大量重复数据的时候，sql查询可能不会去利用索引。如一表中字段 sex、male、female 几乎各一半。那么即使在sex上创建了索引对查询效率也起不了多大作用。 15：索引创建需注意 并非索引创建越多越好。索引固然可以提高相应的查询效率，但是同样会降低insert以及update的效率。因为在insert或是update的时候有可能会重建索引或是修改索引。所以索引怎样创建需要慎重考虑，视情况而定。一个表中所以数量最好不要超过6个。若太多，则需要考虑一些不常用的列上创建索引是否有必要。 (责任编辑：IT) |