数据库 sql语句优化

 

--------------------------------------------------------------------------------

 

写操作（write）

1) 如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

Insert into test values(1,2),(1,3),(1,4)… 2) 如果你从不同客户插入很多行，能通过使用INSERT DELAYED语句得到更高的速度。Delayed的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入。

3) 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

4) 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用。

5) 当从一个文本文件装载一个表时，使用LOADDATA INFILE。这通常比使用很多INSERT语句快20倍。

6) 根据应用情况使用replace语句代替insert。

7) 根据应用情况使用ignore关键字忽略重复记录。

关于update

a) 尽量不要修改主键字段。

b) 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。

c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。

d) 避免UPDATE将要复制到其他数据库的列。

e) 避免UPDATE建有很多索引的列。

f) 避免UPDATE在WHERE子句条件中的列。

 

读操作（read）

1，不要再索引字段上进行运算，否则索引会失效。

尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1WHERE F1=100*2

 

2，避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符

因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists.

 

3，尽量使用数字型字段

设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

 

4，合理使用EXISTS,NOTEXISTS子句

1.SELECT SUM(T1.C1) FROM T1 WHERE (SELECTCOUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)

2.SELECT SUM(T1.C1) FROM T1WHEREEXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)

两者产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。如果你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，而且浪费服务器资源。可以用EXISTS代替。如： IF (SELECT COUNT(*) FROM table_name WHERE column_name = ‘xxx’)可以写成：IFEXISTS (SELECT * FROM table_name WHERE column_name = ‘xxx’)

 

5，关于操作符

   a，能够用BETWEEN的就不要用IN

   b，能够用DISTINCT的就不用GROUP BY（关键词 distinct用于返回唯一不同的值。）

   c，尽量不要用SELECTINTO语句

常用于创建表的备份复件或者用于对记录进行存档 select * into new_table from old_table

    d，尽量避免在索引过的字符数据中，使用非打头字母搜索

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERESUBSTING(NAME,2,1)=’L’

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作，不要习惯性的使用‘%L%’这种方式(会导致全表扫描)，如果可以使用`L%’相对来说更好;

   e,能用UNION ALL就不要用UNION

    e1,UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源

   e2,在跨多个不同的数据库时使用UNION是一个有趣的优化方法，UNION从两个互不关联的表中返回数据，这就意味着不会出现重复的行，同时也必须对数据进行排序，我们知道排序是非常耗费资源的，特别是对大表的排序。

   e3,UNION ALL可以大大加快速度，如果你已经知道你的数据不会包括重复行，或者你不在乎是否会出现重复的行，在这两种情况下使用UNION ALL更适合。此外，还可以在应用程序逻辑中采用某些方法避免出现重复的行，这样UNION ALL和UNION返回的结果都是一样的，但UNION ALL不会进行排序。

 

6，不要在选择的栏位上放置索引

    a，应该在条件选择的语句上合理的放置索引，比如where，order by

    b，SELECTid,title,content,cat_id FROM article WHERE cat_id = 1;

上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引，那作用就相当大了。

 

7，ORDER BY语句的MySQL优化

   a，ORDER BY语句的MySQL优化

   b，WHERE +ORDER BY + LIMIT组合的索引优化

   c，WHERE + IN +ORDER BY + LIMIT组合的索引优化

   d，WHERE+ORDERBY多个栏位+LIMIT

 

 

Mysql group by语句的优化

默认情况下，MySQL排序所有GROUP BY col1, col2, ....，查询的方法如同在查询中指定ORDERBY col1, col2, ...。如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。

如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。例如： INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BYNULL;

Mysql join语句的优化

这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。

创建索引

1、合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。

2、索引越多，更新数据的速度越慢。

3、尽量在采用MyIsam作为引擎的时候使用索引（因为MySQL以BTree存储索引），而不是InnoDB。但MyISAM不支持Transcation。

4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度，而程序瓶颈并不能顺利解决，那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。

5、习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。(扩展:explain用法：在select语句前加上explain就可以了).

 

表的设计

1,据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如float和int、char和varchar、binary和varbinary是不兼容的。

2,通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担。

3.能够分开的操作尽量分开处理，提高每次的响应速度。

4,在数据窗口使用SQL时，尽量把使用的索引放在选择的首列。

5,在查询时，不要过多地使用通配符如SELECT * FROM T1语句,在可能的情况下尽量限制尽量结果集行数。

6,不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集的SQL语句需要更大的开销。

7,按照特定顺序提取数据的查找。