MySQL入门 (十九) : 效率

资料库主要的功能是帮你储存资料,而且要可以很方便的让你随时查询或维护资料。 但是在资料库运作一段时间,尤其是里面储存了大量资料的时候,你常会发现在查询或维护资料的时候,要等后比较长的时间。 所以资料库除了储存资料外,效率的问题也是很重要的。 资料库在关于效率上的问题会比较复杂一些,跟软、硬体还有网路都有关,这里只会讨论跟资料库有关的部份,而且会是比较基础的概念。

查询资料算是资料库中最常执行的工作,想要让查询资料的效率可以好一点,查询叙述本身就很重要。 另外也可以依照需求建立增加效率的索引,建立正确的索引可以提高查询工作的效率;索引也可以在某些修改与删除工作上看到效果。

储存引擎在效率上也是一个很重要的因素,你会考虑资料库的大小与种类,还有使用者的数量,然后选择一个适合的储存引擎。

1 索引

1.1 索引的种类

主索引键的应用很常见,而且一个表格通常会有一个,而且只能有一个。 在一个表格中,设定为主索引键的栏位值不可以重复,而且不可以储存「NULL」值。 因为这样的限制,所以很适合使用在类似编码、代号或身份证字号这类栏位。

唯一索引也称为「不可重复索引」,在一个表格中,设定为唯一索引的栏位值不可以重复,但是可以储存「NULL」值。 这种索引适合用在类似员工资料表格中储存电子邮件帐号的栏位,因为员工不一定有电子邮件帐号,所以允许储存「NULL」值,可以每一个员工的电子邮件帐号都不可以重复。

非唯一索引用来增加查询与维护资料效率的索引。 设定为非唯一索引的栏位值可以重复,也可以储存「NULL」值。

「FULLTEXT」索引只能用在「CHAR」、「VARCHAR」与「TEXT」型态的栏位,而且表格使用的储存引擎必须是「MyISAM」,一般会称为「全文检索」,可以提高搜寻大量文字的效率。

「SPATIAL」索引是「SPATIAL」型态栏位专用的,而且表格使用的储存引擎必须是「MyISAM」。 「FULLTEXT」与「SPATIAL这两种索引不会在这里讨论。

注:建立与管理索引的方式,在「表格与索引」中讨论。

1.2 建立需要的索引

索引有两个主要的用途:主索引键与唯一索引可以避免重复的资料;主索引键、唯一索引与非唯一索引都可以增加资料库的效率。 如果需要为了增加效率而建立索引的话,你可以使用下列最基本的原则:

mysql_20_snap_01

除了使用在「WHERE」子句中判断条件的栏位,还有「ORDER BY」与「GROUP BY」子句中指定的栏位,也都可以使用建立索引来增加效率。 不过建立这样的索引的前提,还是你的表格会储存比较大量的资料,如果表格的资料量不大的话,建立索引反而会浪费储存的空间,效率也增加不多,而且还会让执行新增或修改时的效率变差。

如果想要为了增加效率而建立索引的话,你应该要考虑下列几点:

  • 最重要的,当然是不要建立没有必要的索引,例如上列讨论的情况
  • 索引的栏位尽量不要有「NULL」值
  • 虽然某个栏位很常使用在「WHERE」、「ORDER BY」或「GROUP BY」子句中,也不一定要建立索引。 例如性别栏位的值只有两种(使用ENUM(‘M’, ‘F’)型态),建立索引所增加的效率也不多
  • 主索引键与唯一索引的效率会比非唯一索引好

1.3 建立部份内容的索引

下列是一个用来示范用的表格,它可以储存一般的个人资料,在建立表格的时候,就先把身份证字号的栏位设定为主索引键:

mysql_20_snap_02

在使用这个表格一段时间以后,如果储存的资料量很大,而且又很常使用姓名与地址栏位执行条件的判断,你应该会帮它们建立下列的索引:

mysql_20_snap_03

为姓名栏位建立索引是比较没有问题的,不过地址栏位的长度有255个字元,这样的索引是比较没有效率的,而且你应该比较不会执行所有地址的条件判断,如果比较经常执行的条件判断,是类似「某某县某某市」的话,其实你只要建立部份内容的索引就好了:

mysql_20_snap_04

虽然建立部份内容的索引可以减少索引的大小,不过你还要注意之前讨论的原则,就是建立索引的栏位值不应该有太多重复的值。 以上列建立的索引来说,为地址栏位的前六个字元建立索引的话,应该就会有很多重复的值。 所以你应该先「分析」表格中的资料:

mysql_20_snap_05

上列的叙述可以知道地址栏位是不是有很多重复的资料,为了建立部份内容的索引前,你也可以先使用下列的查询叙述来确认:

mysql_20_snap_06

如果上列的查询结果,确认地址栏位的前六个字元有很多重复的资料,你可以增加字元的数量后再查询,直到你可以接受的数量后,再使用这个数量来建立部份内容的索引。

2 判断条件的设定

如果想要查询一个表格所有的资料,你就不会使用「WHERE」设定查询条件,那就只能请资料库读取表格中所有的资料后传回来,有没有索引就不会有效率上的影响。 不过如果使用「WHERE」子句设定查询条件的话,就要尽量使用索引来增加查询的效率。 以下列的表格来说:

mysql_20_snap_07

虽然你为生日栏位建立了索引,如果你在索引栏位使用函式或运算式的话:

mysql_20_snap_08

下列的叙述就会使用索引,虽然比较长一些,不过它执行的效率会比上列的叙述好一些:

mysql_20_snap_09

MySQL资料库在下列的情况下,都会自动帮你执行转换的工作:

mysql_20_snap_10

虽然上列的查询叙述在执行后也可以传回你想要的资料,不过MySQL在处理每一笔资料的时候,都要帮你执行一次转换的工作,这样的写法是很没有效率的。 所以你要尽可能避免这样的情形:

mysql_20_snap_11

另外在关联式资料库的设计下,你应该会很常执行类似下列叙述的结合查询:

mysql_20_snap_12

结合查询是一种很没有效率的查询,因为资料库要比对两个表格中,结合条件所设定的栏位值,如果资料数量很多的话,这样的比对工作就会花很多时间。 所以你通常会帮结合条件中的栏位建立索引,以上列的查询来说,国家表格的「Code」栏位已经是主索引键;而城市表格的「CountryCode」并没有建立索引,为了增加结合查询的效率,你可以建立下列的索引:

mysql_20_snap_13

如果经常使用国家名称执行条件判断的话,你可能会帮它建立一个索引:

mysql_20_snap_14

使用完整的国家名称执行条件判断的话,因为使用索引执行搜寻,所以效率会比较好一些。 可是如果使用字串样式执行条件判断的话,就不一定会使用索引了:

mysql_20_snap_15

有一些索引可能会包含多个栏位:

mysql_20_snap_16

在查询的条件中,如果跟多个栏位的索引有关的话,MySQL会依照索引栏位的顺序来决定是否使用索引。 以上列的例子来说,主索引键的顺序是CountryCode栏位在前面,Language栏位在后面,如果你的查询条件只有使用Language栏位的话,这个索引就不会生效:

mysql_20_snap_17

4 EXPLAIN与查询叙述

MySQL资料库提供「EXMPLIN」指令,可以让你分析一个查询叙述。 以下列的查询来说,你可以清楚的知道资料库在执行这个查询时后发生「full table scan」:

mysql_20_snap_18

下列的查询叙述可以看出资料库使用索引来传回资料:

mysql_20_snap_19

如果是包含有子查询的查询叙述,「EXPLAIN」也会分别帮你执行分析的工作:

mysql_20_snap_20

使用「EXPLAIN」来检查在这章讨论索引的查询叙述:

mysql_20_snap_21

换成下列的查询叙述后,「EXPLAIN」会告诉你资料库使用索引来传回资料:

mysql_20_snap_22

5 资料维护

当你使用「INSERT」、「UPDATE」或「DELETE」叙述执行资料维护的工作时,也要注意效率上的问题。 在执行修改或删除资料的时候,除了要修改或删除表格中所有的资料以外,你都会加入条件的设定。 在「UPDATE」和「DELETE」叙述中使用「WHERE」子句设定条件时,跟查询时候该注意的地方都一样,除了尽量使用索引来增加执行的效率,也要避免不必要的资料转换。

MySQL提供的「EXPLAIN」叙述,只可以为你分析一个查询叙述,它不可以使用在「SELECT」以外的叙述。 不过你也可以这样作:

mysql_20_snap_30

MySQL提供使用一个「INSERT」叙述新增多笔资料的语法,如果你一次要新增多笔资料的话,使用这样的方式新增资料会是比较有效率的:

mysql_20_snap_31

6 LIMIT子句

在查询和维护资料的时候,都有可能会使用「LIMIT」子句设定查询或维护资料的数量。 「LIMIT」子句在某些应用上是非常方便的,不过要特别注意在效率上的问题。 以下列的例子来说:

mysql_20_snap_32

虽然这个查询叙述只有传回五笔资料,可以资料库总共读取了105笔资料,这样的查询会是比较没有效率的。 你可以使用索引与「ORDER BY」子句来增加效率:

mysql_20_snap_33

7 使用暂时表格

在执行比较复杂的查询工作时,在一个查询叙述中,可能会有结合查询、子查询和其它复杂的判断条件。 一个看起来比较长而且比较复杂的查询,效率并一定比较不好。 不过你可以使用一些比较特别的方式,进一步改善查询的复杂度与效率。 以下列的查询来说:

mysql_20_snap_23

上列的查询是一个不算太复杂的结合查询,如果还要在加上其它的条件判断的话,看起来就会更长一些:

mysql_20_snap_24

如果还要再结合另外一个表格的话,这个查询看起来就真的很复杂了:

mysql_20_snap_25

上列的查询看起来虽然复杂,不过如果都有可以使用的索引,它执行的效率也会是不错的。 如果在查询工作中,很常使用第一个查询的结果,再加上不同的条件或结合,你就可以考虑使用下列的叙述,先建立好一个暂时的表格:

mysql_20_snap_26

因为查询的结果已经储存在「countrycapital」表格中,所以要加入其它的条件就变得简单多了:

mysql_20_snap_27

如果要再结合另外一个表格的话,也会比较容易:

mysql_20_snap_28

在「第九章、子查询、FROM子句与子查询」讨论到可以把一个查询放在「FROM」子句中:

mysql_20_snap_29

使用这样的方式虽然可以得到一样的查询结果,不过在你很常使用上列子查询来增加条件的情况下,每次执行不同条件的查询,资料库都要重新执行子查询叙述;先建立暂时的表格,再使用暂时表格执行查询的作法会是比较有效率的。

8 储存引擎

MySQL资料库是一种允许多个用户端同时使用的资料库管理系统,在多用户端的的运作环境下,资料库就使用「锁定、Locking」来避免资料的混乱:

mysql_20_snap_34

MySQL提供的「MyISAM」和「InnoDB」两种储存引擎,使用不同的锁定方式来处理上列的情况。 MyISAM使用的是「table-level」的锁定方式:

mysql_20_snap_35

MyISAM储存引擎使用的「table-level」锁定方式,适合使用在查询工作非常多,资料维护比较少的资料库,这样的资料库运作起来的效率会比较好。

InnoDB储存引擎使用的是「row-level」的锁定方式:

mysql_20_snap_36

InnoDB储存引擎使用的「row-level」锁定方式,适合使用在查询与资料维护工作都差不多的资料库,这样的资料库运作起来的效率会比较好。

翻译自: http://www.codedata.com.tw/database/mysql-tutorial-20-performance/

更多参考:

MySQL 超新手入门教程系列

MySQL入门 (一) : 资料库概论与MySQL的安装

MySQL入门 (二) : SELECT 基础查询

MySQL入门 (三) : 运算式与函式

MySQL入门 (四) : JOIN 与UNION 查询

MySQL入门 (五) : CRUD 与资料维护

MySQL入门 (六) : 字元集与资料库

MySQL入门 (七) : 储存引擎与资料型态

MySQL入门 (八) : 表格与索引

MySQL入门 (九) : 子查询 Subquery

MySQL入门 (十) : Views

MySQL入门 (十一) : Prepared Statements

MySQL入门 (十二) : Stored Routines 入门

MySQL入门 (十三) : Stored Routines 的变数与流程

MySQL入门 (十四) : Stored Routines 进阶

MySQL入门 (十五) : Triggers

MySQL入门 (十六) : 查询information_schema

MySQL入门 (十七) : 错误处理与查询

MySQL入门 (十八) : 汇入与汇出资料

 

本文: MySQL入门 (十九) : 效率

Loading

Add a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.