查询性能优化

使用连接(JOIN)来代替子查询(Sub-Queries)

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

DELETEFROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

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

SELECTFROMcustomerinfo

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。

推荐文章:

开发知识-平台中使用Druid数据库连接及监控
2021-02-07
一、o2oa数据库连接及使用原理o2oa使用阿里的druid与数据库进行连接及管控,在平台启动时druid管控了一个共享数据库连接池,默认的最大连接数是50(如
移动办公-将平台集成到企业微信(WeChat)
2021-02-25
O2OA平台拥有配套的原生开发的安卓和IOS移动APP,可以以自建应用的方式集成到企业微信,同步企业微信的企业通讯录作为本地组织人员架构,并且可以将待办等通知直
系统集成-SSO与单点认证
2021-02-26
O2OA平台提供灵活的系统集成方案,让企业信息平台与各类第三方系统进行集成。本篇主要介绍如何在O2OA中与其他系统实现单点登入。
系统架构-基于nginx快速集群部署-端口分发
2021-03-23
O2OA平台使用分布式架构设计,提供灵活的扩展方案用于对服务器的负载能力进行扩展,保障系统的高可用性。本篇主要介绍如何基于nginx端口分发的方式快速集群部署。
权限管理-平台组织人员权限管理
2021-02-25
O2OA开发平台组织架构管理组件不局限于传统的组织,群组,角色,人员的关系,在减小授权管理的复杂性基础上,通过独特的职位管理,汇报关系管理思想,增强了架构管理的
开发知识-人大金仓数据安装
2021-02-07
一、安装程序  1、在安装前要创建一个帐号,不能用root帐号安装。  2、安装执行./setup.sh -iconsole    3、接受“此许可协议条款”,
开发知识-神通数据库安装
2021-02-07
一、安装配置界面上:勾选择“兼容Oracle模式”,库字符集“UTF8”,页大小"32K"成功创建神通数据库实例后,系统通常为该实例默认创建三个用户:SYSDB
系统配置-配置服务器连接O2云
2021-03-05
O2OA拥有云端应用市场以及配套的移动办公APP,支持IOS和安卓端。用户可在连接O2云之后,使用APP使用移动办公。移动办公APP开放源代码,不会产生任何费用
开发知识-中标麒麟安装人大金仓详细步骤
2021-03-24
环境说明操作系统版本:NeoKylinLinuxAdvancedServerreleaseV7Update6(Chromium)1.前期准备工作1.1数据库下载
平台维护-流转中工作数据的查询、操作和管理
2021-02-26
O2OA提供平台数据维护应用,用于对平台应用类数据的维护操作,用户可以在应用市场选择安装平台维护应用。本篇主要简单介绍流转中工作数据的查询、操作和管理功能。

results matching ""

    No results matching ""