技术文章

当前位置:首页>帮助手册>技术文章

数据优化知识点

时间:2022-03-04   
内容导读

本篇整理数据库查询与索引优化的要点:说明用连接替代子查询的改写方式与适用场景,并给出创建索引、复合索引的最左前缀原则、避免空值列与非必要排序等做法。文中配有示例语句,并说明过度索引带来的写入开销,适合在数据量增长后做性能排查时逐条对照,也可作为开发与运维沟通时的检查清单,减少盲目加索引的情况,也便于在压测时定位瓶颈所在。

查询性能优化

使用连接(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平台软件

兰德网络 O2OA 平台软件

开源、自主可控的协同办公底座

研发与运营

纵横翱途(杭州)科技有限公司

企业级协同应用 · 工作流引擎 · AI 智能体

www.o2oa.net | 400-888-0545

上一篇:使用GBase8安全数据库配置样例

下一篇:使用Eclipse导入o2server源码