sql---MySql如何编写高效的SQL

    最近应团队要求,研究整理了下,mysql相关的优化，有些是根据实际java项目中碰到的情况经验之谈。欢迎讨论~

 

    SQL 语言是一种强大而且灵活的语言，在使用 SQL 语言来执行某个关系查询的时候，用户可以写出很多不同的 SQL 语句来获取相同的结果。也就是说，语法不同的SQL语句，有可能在语义上是完全相同的。但是尽管这些 SQL 语句最后都能返回同样的查询结果，它们执行所需要的时间却有可能差别很大。

一．优化目标

  1. 减少 IO 次数

IO永远是数据库最容易产生瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是IO操作所占用的，减少IO次数是SQL优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

  1. 降低 CPU 计算

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by， group by，distinct等操作都十分占用CPU资源(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

二．MySQL的语句执行顺序

MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入。

 

下面具体分析一下查询处理的每一个阶段。

  1. FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
  2. ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
  3. JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3，如果包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
  4. WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
  5. GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5。
  6. CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6。
  7. HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被插入到虚拟表VT7中。
  8. SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
  9. DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9。
  10. ORDER BY: 将虚拟表VT9中的记录进行排序操作，产生虚拟表VT10。
  11. LIMIT：取出指定行的记录，产生虚拟表VT11，并将结果返回。
    1. 合理使用索引

编写高效SQL的一些实践

使用索引的优点：

①   可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

②   可以加速表和表之间的连接。

③   在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

使用索引的缺点：

①   创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

②   索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。

③   当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

创建索引的准则：

①   在经常需要搜索的列上，可以加快搜索的速度。

②   在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。

③   在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

④   在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

⑤   在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该创建索引的的这些列具有下列特点：

 

①   对于那些在查询中很少使用列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

②   对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

③   当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

 

如下第二、第三句SQL，使用索引与不使用索引，查询速度相差巨大。（索引可用在select后，也可用于on，where后的条件中）

SELECT * FROM test1; -- 1.863 2.008 2.062（260万条数据） -- 此处有个select*的误区，实际上不加索引的select* 比select col速度来得快

SELECT NAME FROM test1; -- 4.989 5.001 4.855 （260万条数据, name不加索引）

SELECT NAME FROM test1; -- 1.227 1.476 1.403 （260万条数据, name加索引）

大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作block或者page)为单位，一般为4KB，8KB…… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。

所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。

当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取 a，b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。

大多数时候并不会影响到 IO 量，但是当还存在order by操作的时候，select子句中的字段多少会在很大程度上影响到排序效率。

  1. 尽量早过滤

Where条件中，越精确的条件（能够剔除大部分结果的条件）放前面。

又如，在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省IO操作所消耗的时间。

  1. 尽量少的条件

如下面这句SQL，若where条件中col1=1，col2=2是能够精确查找结果的最简条件，则无需加入冗余的其他条件。

SELECT * FROM table where col1=1 and col2=2 and col3=3

  1. 将数据库的压力转移至后台

数据库的资源是宝贵的，我们可以将不必要的操作转移至Java端处理。如判空，字段值拼接，字符串TRIM，日期格式化等等。

如下SQL，均可由Java处理。

SELECT IFNULL(SUM(col),0) from table;

SELECT CONCAT(col, 'hello world') from table;

SELECT TRIM(col) from table;

SELECT DATE_FORMAT(col,'%d %b %Y %T:%f') from table;

  1. 尽量避免向客户端返回大数据量。

若从数据库一次性返回的数据量过大，应该考虑相应需求是否合理，是否可以通过分页等方法处理。

  1. 用Union ALL代替OR（比较少用，一般都用OR）

SELECT * FROM test1 WHERE NAME = 'test1_1' OR NAME = 'test1_2'; -- 3.179 2.902 2.806（100万数据测试）

SELECT * FROM test1 WHERE NAME = 'test1_1'

union all

SELECT * FROM test1 WHERE NAME = 'test1_2'; -- 2.335 2.485 2.432（100万数据测试）

  1. 能用UNION ALL就不要用UNION

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

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

SELECT id FROM table WHERE NAME LIKE '%L%';

SELECT id FROM table WHERE NAME LIKE 'L%';

即使NAME字段建有索引， 第一个查询无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第二个查询能够使用索引来加快操作。

  1. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

SELECT * FROM T1 WHERE F1/2=100

应改为:

SELECT * FROM T1 WHERE F1=100*2

 

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)= '5378'

应改为:

SELECT * FROM RECORD WHERE CARD_NO LIKE '5378%'

 

SELECT member_number, first_name, last_name FROM members

WHERE DATEDIFF(yy,datofbirth,now()) > 21

应改为:

SELECT member_number, first_name, last_name FROM members

WHERE dateofbirth < DATEADD(yy,-21,now())

  1. 尽量用 join 代替子查询

虽然 Join 性能并不佳，但是和MySQL的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

  1. 很多时候用 exists是一个好的选择（in与exists效率在不同场景效率有高有低，但not exists比not in的效率高）

select num from a where num in (select num from b);

用下面的语句替换：

 select num from a where exists (select 1 from b where num=a.num)

  1. 有时候表设计时做恰当的字段冗余是有必要的

比如说有用户id字段的表，用户在查询时如果经常需要同时获得用户名，此时可以将用户名当一个冗余字段在该表中存储，这样就可以不做连接即可获得用户名。

要求：该冗余字段的值一般不变或者很少变化。

  1. 能用GROUP BY的就不用DISTINCT

使用GROUP BY去重比DISTINCT效率高。

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

用下面的语句替换：

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

  1. 14.  在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
  2. 恰当安排 Group By 子句中列的顺序

通常情况下，SQL 语句中的 GROUP BY 子句会导致数据库不得不通过一个排序（SORT）操作来实现对数据的分组，而排序被认为是一个比较耗费 CPU 和内存的操作。实际上某些情况下，如果写法得当，当中的排序操作是可以避免的。具体来说，在写 GROUP BY 子句的时候，应该考虑到数据库中已经存在的索引的情况。如果 GROUP BY 子句中所有的列恰好包括在某个索引的键（Key column）的范围之内而且是处于开始的位置，那么在写 GROUP BY 子句的时候，就应该按照该索引上键的先后顺序来写 GROUP BY 子句。

比如说有如下的 SQL 语句：

 SELECT C2, C3, C1, AVG(C4)

 FROM T1

 GROUP BY C2, C3, C1

一般情况下，GROUP BY C2, C3, C1这样的写法都会导致数据库的一个排序操作。但假定表 T1 上已经存在一个索引 IX1(C1, C2, C3, C4), 这里注意到 GROUP BY 子句中引用到的列（C2，C3，C1）正好是索引 IX1 中的前三个键，那么就可以通过改变 GROUP BY 子句中列的顺序的办法来避免这个排序操作。

可以把 SQL 语句改写为如下所示：

 SELECT C1, C2, C3, AVG(C4)

 FROM T1

 GROUP BY C1, C2, C3

通过这样改变 GROUP BY 子句中列的顺序使其与索引 IX1 中的键顺序一致，数据库就可以利用 IX1 来访问其已经排序的键值并直接返回进行下一步操作，从而避免额外的排序操作，从而带来查询性能上的提高。

  1. varchar/nvarchar与 char/nchar

char/nchar为固定长度，如果某个字段中字符长度已知固定，使用char/nchar效率比varchar/nvarchar效率高。

  1. 17.  如果表中某个字段存储的都是数字，那么该字段请设计为数字型字段，而不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  2. count(*)  count(1)  count(id)的效率

四．一些误区

SELECT COUNT(*) FROM test1; -- 0.749 0.752 0.750

SELECT COUNT(1) FROM test1; -- 1.578 0.900 1.455

SELECT COUNT(id) FROM test1; -- 0.740 0.763  0.751

 

很多人为了统计记录条数，就使用count(1)和count(primary_key)而不是count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，因为数据库对count(*)计数操作做了一些特别的优化。

 

  1. count(column) 和 count(*)，count(1) 是一样的

count(column) 和 count(*)，count(1)是一个完全不一样的操作，所代表的意义也完全不一样。

count(column) 是表示结果集中有多少个column字段不为空的记录。

count(*)，count(1)是表示整个结果集有多少条记录。

  1. 应尽量避免在 where 子句中对字段进行 null 值判断

误区：应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致数据库放弃使用索引而进行全表扫描，降低查询速度。可以在col上设置默认值，确保表中col列没有null值，然后查询。

以下实测结果表明：在未加索引的情况下，判空操作比判非空默认值操作速度快得多，加索引的情况下两种情况差不多。

SELECT id from table where col is null –- 1.549 1.884 1.818 （table col 加入索引，260万数据）

SELECT id from table where col is null –- 2.003 1.963 1.975 （table col 未加入索引，260万数据）

SELECT id from table where col = ‘0’  -– 1.626 1.854 1.532 （table col 加入索引，260万数据）

SELECT id from table where col = ‘0’  -– 4.407 5.256 5.434  （table col 未加入索引，260万数据）

 

  1. order by 一定需要排序操作

索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。

实际上，利用索引来优化有排序需求的 SQL，是一个非常重要的优化手段。

 

以上是我在word里面整理的，并且实际在mysql中执行过的结果。下面是补充和总结：

一   Mysql数据库名和表名在 Windows 中是大小写不敏感的，而在大多数类型的 Unix/Linux 系统中是大小写敏感的。

MySQL大小写敏感可以通过配置文件的lower_case_table_names参数来控制。

WINDOWS：

编辑MySQL安装目录下的my.ini 文件，在[mysqld]节下 添加 lower_case_table_names=0 (备注：为0时大小写敏感，为1时大小写不敏感，默认为1)，可以实现MySql按照建表Sql语句的大小写状态来定义表名。

LINUX：

编辑/etc/my.cnf文件,在[mysqld]节下 添加 lower_case_table_names 参数，并设置相应的值 (备注：为0时大小写敏感，为1时大小写不敏感，默认为0)

 

二  数据库的存储----表结构,表数据，索引

 

三  索引利于查询速度，却不利于删除和修改，所以那些频繁修复和更新的字段不宜建立索引

 

四  %% like查询, not in,not exist  都无法应用索引

 

五 用了"聚合函数"查询,也无法应用索引；复合索引,一般都是看第一个条件索引

 

六  查询尽量用具体的字段，而不是直接select * ；聚合函数不适用大量数据查询过滤；

count(*)---会吧null空列也统计进去

count(列)---只会统计非空字段

ps:在mysql5.6中 发现count(*)速度比count(1)和count(列) 更快 ？

 

七  查询条件尽量少,越精确的条件,应该放在前面，先过滤掉大数据;可以用多条sql语句来实现一条查询结果,提高查询性能。

 

八  Mysql很少采用类似SqlServer中大量使用的 “存储过程”来实现业务逻辑（存储过程也不是mysql的优势）；更多的是用 简单的查询，而把复杂的查询业务，直接在程序代码中实现，压力转义(java中有很多优秀的orm框架)。 mysql虽然没有sqlserver和oracle强大，但是它是开源免费，可以部署多台sql服务器,实现分表分库,集群，从而实现以量换性能。

 

九  InnoDB，是MySQL的数据库引擎之一，为MySQL AB发布binary的标准之一。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。

 

 

 

posted @ 2016-04-14 12:01  JavAndroidJSql  阅读(1129)  评论(0)  编辑  收藏  举报