微信号:java_bj

介绍:从算法基础到常用框架的知识体系,从初级程序员到高级架构师的成长之路,从创业小团队到Google、BAT的工作机会,始于JAVA而又不止于JAVA.JAVAer在北京,我们一起成长.

数据库系列五:关于SQL,你应该知道的事儿

2016-08-04 07:11 FieldSheng
点击上方“Java北京”关注我们



 一、SQL是一种编程语言吗?

SQL,Structured Query Language,结构化查询语言,是一种声明式的语言(SQL语言声明的是结果集的属性,计算机会根据SQL所声明的内容来从数据库查找出符合声明的数据,而不是像传统编程思维那样去指示计算机如何操作)。在最新的《2016年7月TIOBE编程语言排行榜》中,Oracle数据库对SQL语句的扩展PL/SQL(Procedural Language/SQL,过程化SQL)排在第18位。

具体来看,SQL包含6个部分:

  1. DQL(Data Query Language,数据查询语言):SELECT语句等

  2. DML(Data Manipulation Language,数据操作语言):INSERT、UPDATE、DELETE等

  3. TPL(Transaction Process Language,事务处理语言):BEGINTRANSACTION,COMMIT和ROLLBACK

  4. DCL(Data Control Language,数据控制语言):GRANT,REVOKE等

  5. DDL(Data Definition Language,数据定义语言):CREATE TABLE,DROP TABLE,创建索引等

  6. CCL(Cursor Control Language,指针控制语言):DECLARECURSOR,FETCH INTO和UPDATE WHERE CURRENT等

 

二、SQL语句需要大写吗?

首先,各类数据库对SQL语句的大小写是不敏感的(引号内的字符串除外);其次,有些数据库如Oracle对SQL语句进行词法和语法分析时会将小写转换成大写,统一将SQL语句大写只是减少了本来就占比很小的编译时间,而无关SQL的执行效率,而且,SQL的效率主要在于业务逻辑的复杂程度以及对SQL的优化。

如此来看,SQL语句的大小写对SQL执行效率即使有影响,也是微乎其微的。通常情况下,SQL语句的大小写规范更多的是为了可读性和可维护性,建议是:关键字大写,非关键字小写,table name以及field name为了可读性都建议用小写(统一大小写规范还能更有效地使用缓存——Oracle里面有共享游标的概念)。

 

三、SQL语句执行顺序是怎样的?

SQL语句的执行顺序与语法顺序并不一致,SQL语句的语法顺序为:

SELECT [DISTINCT]

FROM

<join_type> JOIN <right_table>

ON

WHERE

GROUP BY

HAVING

UNION

ORDER BY

LIMIT

实际执行顺序为:

FROM

ON

<join_type> JOIN <right_table>

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

UNION

ORDER BY

LIMIT

说明:

  1. select是在where等语句之后执行的,所以在where语句中不能使用select中设置的别名。

  2. 第一步执行的是FROM,将数据从硬盘加载到数据缓冲区,以便对这些数据进行操作。

  3. UNION子查询中可以使用ORDERBY进行排序,但是并不能说明UNION合并之后仍然保持排序后的顺序。如下:

(SELECT * FROM trans_credit_tmp t1 WHERE t1.ID BETWEEN 1 AND10 ORDER BY t1.id DESC)

UNION

(SELECT * FROM trans_credit_tmp t2 WHERE t2.ID BETWEEN 11 AND20 ORDER BY t2.id DESC);

此union第一个子查询结果为:

第二个子查询结果为:

 

UNION之后的结果为:


很坑爹有木有?!所以,排序一定要放到union之后再做。


四、连接

关于笛卡尔乘积:

在数学中,两个集合X和Y的笛卡尓积(Cartesian Product),又称直积,表示为X × Y,第一个对象是X的成员,第二个对象是Y的成员。假设集合A={a, b},集合B={0, 1, 2},则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

各种连接类型如下图:


注:mysql不支持FULL OUTER JOIN

 

五、索引

索引的作用类似于图书的目录,我们根据目录中的页码可以快速翻到目标页面。当数据库表中的记录达到一定数量时,如果对全表进行扫描查询,会非常耗时,这时候通过建立索引,可以查询效率将会有指数级别的提高。

现在的数据库索引实现方式主要有B-Tree、Hash以及Bitmap。位图索引主要适用于字段值固定以及值的区分度非常低的情况,比如性别、状态等;散列索引根据对应键的hash值来找到最终的索引项,单值查询时会比较快;最常用的B树索引,在数据库中维护一个排序的树结构(实际使用的是B树的变种B+/B-树等)。

由上,建立索引实际上是在数据库中维护了一个额外的数据结构来加快查找速度。所以,设置索引是有代价的,一方面增加了数据库的存储空间,另一方面在插入和修改数据时要花费较多的时间来重建索引(指的是非聚集索引)。

关于索引的具体实现原理,后面我会再写文章详细说明。

关于建索引的一些原则和注意点:

  1. 尽量选择区分度高的列作为索引,区分度的公式是count(distinctcol)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

  2. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

  3. 何时建立索引应当遵循以下原则:该表常用来在索引列上查询,该表不常更新、插入、删除等操作,查询出来的结果记录数应控制在原表的2%~4%。

  4. 索引并不是越多越好,索引固然可以提高相应的SELECT的效率,但同时也降低了insert 及 update 的效率,因为INSERT或UPDATE时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。

  5. 应尽可能的避免更新clustered索引数据列,因为clustered索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered索引数据列,那么需要考虑是否应将该索引建为 clustered索引。

  6. 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、BETWEEN、LIKE)就停止匹配,比如a = 1 AND b = 2 AND c > 3 AND d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

  7. =和in可以乱序,比如a = 1 AND b = 2 AND c = 3 建立(a,b,c)索引可以任意顺序,MySql的查询优化器会帮你优化成索引可以识别的形式

  8. 索引列不能参与计算,保持列“干净”,比如FROM_UNIXTIME(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = UNIX_TIMESTAMP(’2014-05-29’);

 

六、SQL语句优化实践

  1. 横向:只返回需要的字段,避免SELECT*。

  2. 纵向:合理利用where过滤,尽量避免全表扫描,考虑在WHERE和ORDER BY的字段上建索引。

  3. 参照上面建索引的几大原则。

  4. 应尽量避免在 WHERE 子句中对字段进行 null 值判断,否则将导致引擎无法使用索引而进行全表扫描,如:SELECT id FROM t WHERE num is null;可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:SELECT id FROM t WHERE num=0;

  5. 应尽量避免在 WHERE 子句中使用!=或<>操作符,否则将导致引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

  6. 应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:SELECT id FROM t WHERE num=10 OR num=20;可以改成(SELECT id FROM t WHERE num=10) union all (SELECT id FROM t WHERE num=20);

  7. 尽量避免在索引过的字符数据中使用非打头字母搜索。这也使得引擎无法利用索引。见如下例子:

    SELECT * FROM t1 WHERE name LIKE ‘%L%’;

    SELECT * FROM t1 WHERE SUBSTING(name, 2, 1)=’L’;

    SELECT * FROM t1 WHERE name LIKE ‘L%’;

    即使name字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作。

  8. 应尽量避免在 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, GETDATE()) > 21;

    应改为:

    SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy, -21, GETDATE());

    SELECT id FROM t WHERE DATEDIFF(day,createdate,’2005-11-30′)=0 – ‘2005-11-30’;

    应改为:

    SELECT id FROM t WHERE createdate >= ’2005-11-30′ AND createdate<‘2005-12-1′;

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

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

  10. 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如: SELECT name FROM employee WHERE salary > 60000;在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

  11. 能用UNION ALL就不要用UNION:UNION ALL不执行SELECT DISTINCT函数,这样就会减少很多不必要的资源。

  12. 充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在 WHERE 子句中将连接条件完整的写上,有可能大大提高查询速度。例:

    SELECT SUM(a.amount) FROM account a,card b WHERE a.card_no=b.card_no

    SELECT SUM(a.amount) FROM account a,card b WHERE a.card_no=b.card_no AND a.account_no=B.account_no;

    第二句将比第一句执行快得多。

  13. 避免使用DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的等耗费资源的操作,因为带有这些操作的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。

  14. 第三小节提到的统一SQL书写规范:大小写、空格等,避免查询分析器对实际上相同的SQL语句进行重复解析。

  15. 关于IN、EXISTS、NOT IN、NOT EXISTS

    外表大内表小建议用IN,外表小内表大用EXISTS;尽量用NOT EXISTS,尽量不用NOT IN(NOT IN内外表都要做全表扫描,没用到索引;而NOT EXISTS仍能用到索引,所以不论哪个表大,用NOT EXISTS都比NOT IN要快)。例如,表A(小表),表B(大表),cc列上有索引,语句SELECT * FROM A WHERE A.cc IN(SELECT B.cc FROM B)比较低效,因为A是小表,cc索引优势不明显,另外B表全表查询索引没能充分利用;SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE B.cc=A.cc)比较高效,遍历A,但是小表次数也少,同时发挥了B表索引的作用。

  16. 一对多表查询时,避免使用DISTINCT,可以考虑用EXISTS替换,例如,用户表user,订单表order,查询有过购买行为的用户id,SELECT DISTINCT t1.id FROM user t1, order t2 WHERE t1.id=t2.user_id;性能比较低,换用EXISTS后性能比较高:SELECT t1.id FROM user t1 WHERE EXISTS(SELECT 1 FROM order t2 WHERE t2.user_id=t1.id)。

  17. HAVING的条件一般用于集合函数的比较,一般提交直接写在WHERE条件里面即可。

  18. 对于连续的数值,能用 BETWEEN就不要用 IN了:SELECT id FROM t WHERE num BETWEEN 1 AND 3

  19. 当然,要学会使用EXPLAIN命令分析SQL性能





是时候关注一个只分享干货的公众号了

长按二维码 关注我们

JAVA北京(java_bj)



 
Java北京 更多文章 万亿级调用系统:微信序列号生成器架构设计及演变 为什么大型网站前端使用 PHP 后台逻辑用 Java? CAS集群解决方案 一种大批量数据(文件解析)的处理方案 京东618:多中心交易平台系统高压下的高可用性
猜您喜欢 justjavac 的编程之路:「懒惰」是程序员最大的美德 C语言之香港赌马 Android 核心分析 之五 -----IPC框架分析 Binder,Service,Service manager Java虚拟机之垃圾收集器(5) 一次特殊的验证码识别