查询性能优化

使用连接(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不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。



索引优化

1、创建索引

对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引

2、复合索引

比如有一条语句是这样的:select * from users where area='beijing' and age=22;

如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

3、索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4、使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5、排序的索引问题

mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

6、like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

7、不要在列上进行运算

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

select * from users where adddate<‘2007-01-01';

8、不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id<>3则可使用id>3 or id<3来代替。


数据类型优化

更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通更快,因为它们占用更少的磁盘。内存和CPU缓存,并且处理时需要的CPU周期也更少。但是要确保没有低估需要存储的值的范围。如果无法确定哪个数据类型是最好的择你认为不会超过范围的最小类型。(如果系统不是很忙或者存储的数据量不者是在可以轻易修改设计的早期阶段,那之后修改数据类型也比较容易)

简单数据类型的操作通常需要更少的CPU周期。例如,整型比字符操作代价更小


简单就好

因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。这里有两个例一个是应该使用 MySQL内建的类型2而不是字符串来存储日期和时间,另外是应该用整型存储IP地址。


尽量避免NULL

这是因为可为NULL是列的默认属性3。通常情况下最好指定列为 NOT NULL,除非需要存储NULL值如果查询中包含可为NULL的列,对 MySQL来说更难优化,因为可为NULL的列得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间,在MySQL里也需要特殊处理。当可为NULL的列被索引时,每个索引记录需要一个外的字节,在 MyISAM里甚至还可能导致固定大小的索引(例如只有一个整数列索引)变成可变大小的索引。

通常把可为NULL的列 NOT改为 NULL带来的性能提升比较小,所以(调优时)没

必要首先在现有 schema中查找并修改掉这种情况,除非确定这会导致问题。但是

如果计划在列上建索引,就应该尽量避免设计成可为NULL的列。

当然也有例外,例如值得一提的是, InnoDB使用单独的位(bit)存储NULL值以对于稀疏数据有很好的空间效率。但这一点不适用于 MyISAM。

推荐文章:

数据清理-O2OA平台如何清除测试数据
2021-02-07
当O2OA业务开发、测试完成时,上线前通常需要清除已经存在的测试数据,或者演示数据。O2OA提供命令对指定模块的业务数据进行清除。清除测试数据清除已存在的业务数
服务集成-表单界面上自定义弹出选择器功能
2021-03-15
O2OA允许用户对表单进行自由的功能定制,可以增加自定义的组件和插件。本文介绍如果在表单界面上自定义弹出选择器的功能。
平台维护-待办待阅数据的查询操作和管理
2021-02-26
O2OA提供平台数据维护应用,用于对平台应用类数据的维护操作,用户可以在应用市场选择安装平台维护应用。本篇主要简单介绍系统待办、已办、待阅、已阅数据的查询和维护
快速入门-服务器总体介绍汇总
2021-02-07
一、服务器目录介绍二、服务器组成结构三、服务器所占端口四、微服务API五、服务内置对象(方便二次开发、实现2小时一个流程目标)六、架构图
系统集成-单点认证SSO配置之约定密钥
2021-03-15
O2OA平台提供灵活的系统集成方案,让企业信息平台与各类第三方系统进行集成。本篇主要介绍如何在O2OA中使用简单的约定密钥的方案与其他系统实现单点登入。
如何使用应用市场?
2021-02-18
工作管理,固定资产管理,移动门户,公文管理,用车管理,通知公告等,O2OA应用市场拥有丰富的可直接下载使用的模块。O2OA连接O2云之后,可以使用应用市场,免费
会议申请使用手册
2021-02-19
O2OA会议申请是使用平台流程管理能力开发的一个申请流程。员工的会议申请通过审批之后,可以同步到会议管理系统,由会议管理系统进行展现,提醒和跟踪。可以清晰地在会
开发知识-如何使用Tomcat架设webdav服务器
2021-12-21
O2OA服务器支持使用webdav协议来存储二进制文件.Tomcat默认是支持WebDAV的,且默认为禁用状态.可以开启Tomcat的webdav作为文件存储服
流程设计-流程表单事件功能介绍
2021-02-26
本文主要介绍如何在O2OA中进行审批流程表单或者工作流表单设计,O2OA主要采用拖拽可视化开发的方式完成流程表单的设计和配置,不需要过多的代码编写,业务人员可以
平台维护-自动执行平台数据的备份与恢复
2021-11-23
@服务器管理@O2OA@数据备份@数据恢复@自动备份@文件存储备份O2OA提供平台数据导出导入的数据迁移方案,开发者可以将平台的全部数据从一个环境平滑迁移到其他

results matching ""

    No results matching ""