提高mysql千万级大数据SQL查询优化几条经验(1

  1:对查询进行优化,应尽量避免全表扫描,首先应考虑在where及order by涉及的列上创建索引。

  2:尽量避免在where字句中对字段进行null值的判断。否则将会导致引擎放弃使用索引而进行全表扫描。

  (考虑如下情况,假设数据库中一个表有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代替。

  像这样连续的就可以使用between ...and...来代替了。如下:

  如果在where子句中使用参数,也会导致全表扫描。因为sql只会在运行时才会解析局部变量。但优化程序不能将访问计划的选择推迟到运行时;必须在编译时候进行选择。然而,如果在编译时建立访问计划,变量的值还是未知大,因而无法作为索引选择输入项。

  8:尽量避免在where子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

  9:尽量避免爱where子句中对字段进行函数操作,这将导致引擎放弃索引,而进行全表扫描。

  10:不要在where子句中的=左边进行函数、算术运算或是使用其他表达式运算,否则系统可能无法正确使用索引

  在使用索引字段作为条件时候,如果该索引是复合索引,那么必须使用该索引中的第一个字段作为条件时候才能保证系统使用该所以,否则该索引将不会被使用,并且应尽可能的让字段顺序和索引顺序一致。

  例如:需要生成一个空表结构和user表结构一样(注:生成的新 new table的表结构和老表 old table结构一致)

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

  并非索引创建越多越好。索引固然可以提高相应的查询效率,但是同样会降低insert以及update的效率。因为在insert或是update的时候有可能会重建索引或是修改索引。所以索引怎样创建需要慎重考虑,视情况而定。一个表中所以数量最好不要超过6个。若太多,则需要考虑一些不常用的列上创建索引是否有必要。

相关阅读