分享一些关于优化数据库的方法 以及SQL优化小结 (一)

技术分享 2019年01月26日 阅读 192 我也庸俗

1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。

例如,在定义邮政编码这个字段时,如果将其设置为CHAR(255),显然给数据库增加了不必要的空间,甚至使用VARCHAR这种类型也是多余的,因为CHAR(6)就可以很好的完成任务了。同样的,如果可以的话,我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOTNULL,这样在将来执行查询的时候,数据库不用去比较NULL值。 对于某些文本字段,例如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。

2、使用连接(JOIN)来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。例如,我们要将客户基本信息表中没有任何订单的客户删除掉,就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来,然后将结果传递给主查询,如下所示:

DELETEFROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。例如,假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

如果使用连接(JOIN)..来完成这个查询工作,速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下:

SELECT*FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

连接(JOIN)..之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询,它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来创建查询的时候,我们只需要用UNION作为关键字把多个select语句连接起来就可以了,要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

SELECTName,PhoneFROMclientUNION

SELECTName,BirthDateFROMauthorUNION

SELECTName,SupplierFROMproduct

4、事务

尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询,但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操作就会变得不确定起来。设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的情况:第一个表中成功更新后,数据库突然出现意外状况,造成第二个表中的操作没有完成,这样,就会造成数据的不完整,甚至会破坏数据库中的数据。要避免这种情况,就应该使用事务,它的作用是:要么语句块中每条语句都操作成功,要么都失败。换句话说,就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始,COMMIT关键字结束。在这之间的一条SQL操作失败,那么,ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时,它可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很大的应用系统中。由于在事务执行的过程中,数据库将会被锁定,因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟。

其实,有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book';

...

UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES

这里,我们用一个select语句取出初始数据,通过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前,不会有其它的访问来对inventory进行插入、更新或者删除的操作。

我也庸俗 我也庸俗 开发工程师@有赞科技公司

写了 159279 字,被 1 人关注,共写了 71 篇笔记

孤独了忙碌的人
推荐文章:
  • 大数据领域Flink 与 Spark之间的区别?

    学而不思则罔 思而不学则殆,2020年砥砺前行!前言大家都知道已经2020年了,也到了新的一年。作为一个主营电商的公司,年底都会很忙。所以最近的更新进度也停滞不前,本来准备大侃PHP设计模式的,但是因...

    豆浆大叔 1个月前 3 吐槽 161 围观 技术分享
  • php如何实现钩子与实践案例

    前言学而不思则罔,思而不学则殆。30则而立,头顶正则脱光!昨天晚上,突然想起了PHP中的钩子如何使用?说实话,像dz,wordpress,TP,CI框架都已经集成了Hook钩子,尽管我不怎么使用框架以...

    豆浆大叔 2个月前 0 吐槽 61 围观 技术分享
  • Linux无法显示ip地址的解决办法

    今天想趁着有时间,用虚拟机调试一下lua脚本和其他的功能,结果启动虚拟机使用xshell连接不上,然后使用终端查看IP地址无法查看到,记录一下排查错误流程。查看IP地址使用ip addr 或者 ifc...

    豆浆大叔 2个月前 0 吐槽 119 围观 技术分享
  • 高并发性能指标QPS,TPS,RT,并发数,吞吐量是指什么?

    QPS,每秒查询QPS:Queries Per Second意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。互联网中,作为域名系...

    豆浆大叔 2个月前 0 吐槽 259 围观 技术分享
  • 分享一些PHP常用的小算法

    下面分享一些最常见的算法,用PHP如何实现,拓展下知识面。冒泡排序function bubble_sort($arr) { $n=count($arr); for($i=0;$i<$n-1;$...

    我也庸俗 3个月前 0 吐槽 106 围观 技术分享
表情
  • [:821l1001:]
  • [:821l1002:]
  • [:821l1003:]
  • [:821l1004:]
  • [:821l1005:]
  • [:821l1006:]
  • [:821l1007:]
  • [:821l1008:]
  • [:821l1009:]
  • [:821l1010:]
  • [:821l1011:]
  • [:821l1012:]
  • [:821l1013:]
  • [:821l1014:]
  • [:821l1015:]
  • [:821l1016:]
  • [:821l1017:]
  • [:821l1018:]
  • [:821l1019:]
  • [:821l1020:]
  • [:821l1021:]
  • [:821l1022:]
  • [:821l1023:]
  • [:821l1024:]
  • [:821l1025:]
  • [:821l1026:]
  • [:821l1027:]
  • [:821l1028:]
  • [:821l1029:]
  • [:821l1030:]
  • [:821l1031:]
  • [:821l1032:]
  • [:821l1033:]
  • [:821l1034:]
  • [:821l1035:]
  • [:821l1036:]
  • [:821l1037:]
  • [:821l1038:]
  • [:821l1039:]
  • [:821l1040:]
  • [:821l1041:]
  • [:821l1042:]
  • [:821l1043:]
  • [:821l1044:]
  • [:821l1045:]
  • [:821l1046:]
  • [:821l1047:]
  • [:821l1048:]
  • [:821l1049:]
  • [:anger:]
  • [:applause:]
  • [:awkward:]
  • [:brokenheart:]
  • [:clown:]
  • [:confused:]
  • [:decline:]
  • [:diggingmouth:]
  • [:eyebrows:]
  • [:grinning:]
  • [:haha:]
  • [:ill:]
  • [:kiss:]
  • [:lascivious:]
  • [:laugh:]
  • [:love:]
  • [:lovely:]
  • [:rhinorrhea:]
  • [:smile:]
  • [:solid:]
  • [:strong:]
  • [:sweat:]
  • [:tearcollapse:]
  • [:tongue:]
  • [:uncomfortable:]
  • [:weak:]
  • [:worry:]
Tips:支持Markdown语法

0 个评论

抢座沙发~~~~
开发工程师 @ 有赞科技公司

登录

第三方账号登录:
GitHub
微信
微博