**目录** [TOC] 数据库主要的功能是帮你存储资料，而且要可以很方便的让你随时查询或者维护数据。但是在数据库运行一段时间，尤其是里面存储了大量数据的時候，你常发现在查询或维护数据的时候，要等待比较长的时间。所以数据库除了储存数据外，效率的问题也是很重要的。数据库在关于效率上的问题会比较复杂一些，跟软、硬体还有网路都有关，这裡只会讨论跟数据库有关的部份，而且会是比较基础的概念。 查询数据算是资料库中最常执行的工作，想要让查询数据的性能可以好一点，查询叙述本身就很重要。另外也可以依照需求建立增加效率的索引，建立正确的索引可以提高查询工作的效率；索引也可以在某些修改与删除工作上看到效果。 储存引擎在性能上也是一个很重要的因素，你会考虑资料库的大小与种类，还有使用者的数量，然后选择一个适合的储存引擎。 # 1 索引 ## 1.1 索引的种类 主索引键的应用很常见，而且一个表格通常会有一个，而且只能有一个。在一个表格中，设定为主索引键的栏位值不可以重复，而且不可以储存「NULL」值。因为这样的限制，所以很适合使用在类似编码、代号或身份证字号这类栏位。 唯一索引也称为「不可重复索引」，在一个表格中，设定为唯一索引的栏位值不可以重複，但是可以储存「NULL」值。这种索引适合用在类似员工资料表格中储存电子邮件帐号的栏位，因为员工不一定有电子邮件帐号，所以允许储存「NULL」值，可以每一个员工的电子邮件帐号都不可以重复。 非唯一索引用来增加查询与维护资料效率的索引。设定为非唯一索引的栏位值可以重复，也可以储存「NULL」值。 「FULLTEXT」索引只能用在「CHAR」、「VARCHAR」与「TEXT」型态的栏位，而且表格使用的储存引擎必须是「MyISAM」，一般会称为「全文检索」，可以提高搜索大量文字的性能。 「SPATIAL」索引是「SPATIAL」型态栏位专用的，而且表格使用的储存引擎必须是「MyISAM」。「FULLTEXT」与「SPATIAL」这两种索引不会在这裡讨论。 注：建立与管理索引的方式，在「表格与索引」中讨论。 ## 1.2 建立需要的索引 索引有两个主要的用途：主索引键与唯一索引可以避免重复的资料；主索引键、唯一索引与非唯一索引都可以增加资料库的效率。如果需要為了增加效率而建立索引的话，你可以使用下列最基本的原则： [](http://box.kancloud.cn/2015-07-17_55a91a2eb0413.png) 除了使用在「WHERE」子句中判断条件的栏位，还有「ORDER BY」与「GROUP BY」子句中指定的栏位，也都可以使用建立索引来增加效率。不过建立这样的索引的前提，还是你的表格会储存比较大量的资料，如果表格的资料量不大的话，建立索引反而会浪费储存的空间，效率也增加不多，而且还会让执行新增或修改时的效率变差。 如果想要為了增加效率而建立索引的话，你应该要考虑下列几点： * 最重要的，当然是不要建立没有必要的索引，例如上列讨论的情况 * 索引的栏位儘量不要有「NULL」值 * 虽然某个栏位很常使用在「WHERE」、「ORDER BY」或「GROUP BY」子句中，也不一定要建立索引。例如性别栏位的值只有两种(使用ENUM(‘M’, ‘F’)型态)，建立索引所增加的效率也不多 * 主索引键与唯一索引的效率会比非唯一索引好 ## 1.3 建立部份内容的索引 下列是一个用来示范用的表格，它可以储存一般的个人资料，在建立表格的时候，就先把身份证字号的栏位设定為主索引键： [](http://box.kancloud.cn/2015-07-17_55a91a32506ec.png) 在使用这个表格一段时间以后，如果储存的资料量很大，而且又很常使用姓名与地址栏位执行条件的判断，你应该会帮它们建立下列的索引： [](http://box.kancloud.cn/2015-07-17_55a91a3e99b2b.png) 為姓名栏位建立索引是比较没有问题的，不过地址栏位的长度有255个字元，这样的索引是比较没有效率的，而且你应该比较不会执行所有地址的条件判断，如果比较经常执行的条件判断，是类似「某某县某某市」的话，其实你只要建立部份内容的索引就好了： [](http://box.kancloud.cn/2015-07-17_55a91a3f33d4a.png) 虽然建立部份内容的索引可以减少索引的大小，不过你还要注意之前讨论的原则，就是建立索引的栏位值不应该有太多重复的值。以上列建立的索引来说，為地址栏位的前六个字元建立索引的话，应该就会有很多重复的值。所以你应该先「分析」表格中的资料： [](http://box.kancloud.cn/2015-07-17_55a91a41cbde9.png) 上列的叙述可以知道地址栏位是不是有很多重复的资料，為了建立部份内容的索引前，你也可以先使用下列的查询叙述来确认： [](http://box.kancloud.cn/2015-07-17_55a91a42ca398.png) 如果上列的查询结果，确认地址栏位的前六个字元有很多重复的资料，你可以增加字元的数量后再查询，直到你可以接受的数量后，再使用这个数量来建立部份内容的索引。 # 2 判断条件的设定 如果想要查询一个表格所有的资料，你就不会使用「WHERE」设定查询条件，那就只能请资料库读取表格中所有的资料后传回来，有没有索引就不会有效率上的影响。不过如果使用「WHERE」子句设定查询条件的话，就要儘量使用索引来增加查询的效率。以下列的表格来说： [](http://box.kancloud.cn/2015-07-17_55a91a438ef5e.png) 虽然你为生日栏位建立了索引，如果你在索引栏位使用函式或运算式的话： [](http://box.kancloud.cn/2015-07-17_55a91a45b59b5.png) 下列的叙述就会使用索引，虽然比较长一些，不过它执行的效率会比上列的叙述好一些： [](http://box.kancloud.cn/2015-07-17_55a91a492e536.png) MySQL资料库在下列的情况下，都会自动帮你执行转换的工作： [](http://box.kancloud.cn/2015-07-17_55a91a49b0e90.png) 虽然上列的查询叙述在执行后也可以传回你想要的资料，不过MySQL在处理每一笔资料的时候，都要帮你执行一次转换的工作，这样的写法是很没有效率的。所以你要尽可能避免这样的情形： [](http://box.kancloud.cn/2015-07-17_55a91a4a2155c.png) 另外在关联式数据库的设计下，你应该会很常执行类似下列叙述的结合查询： [](http://box.kancloud.cn/2015-07-17_55a91a556da45.png) 结合查询是一种很没有效率的查询，因为数据库要比对两个表格中，结合条件所设定的字段值，如果资料数量很多的话，这样的比对工作就会花很多时间。所以你通常会帮结合条件中的字段建立索引，以上列的查询来说，国家表格的“Code”字段已经是主索引键；而城市表格的“CountryCode”并没有建立索引，为了增加结合查询的效率，你可以建立下列的索引： [](http://box.kancloud.cn/2015-07-17_55a91a5632a6c.png) 如果经常使用国家名称执行条件判断的话，你可能会帮它建立一个索引： [](http://box.kancloud.cn/2015-07-17_55a91a56eac46.png) 使用完整的国家名称执行条件判断的话，因为使用索引执行搜寻，所以效率会比较好一些。可是如果使用字串样式执行条件判断的话，就不一定会使用索引了： [](http://box.kancloud.cn/2015-07-17_55a91a5776d1c.png) 有一些索引可能会包含多个字段： [](http://box.kancloud.cn/2015-07-17_55a91a5d202e3.png) 在查询的条件中，如果跟多个字段的索引有关的话，MySQL会依照索引字段的顺序来决定是否使用索引。以上列的例子来说，主索引键的顺序是CountryCode字段在前面，Language字段在后面，如果你的查询条件只有使用Language字段的话，这个索引就不会生效： [](http://box.kancloud.cn/2015-07-17_55a91a6210c60.png) # 4 EXPLAIN与查询叙述 MySQL数据库提供“EXMPLIN”指令，可以让你分析一个查询叙述。以下列的查询来说，你可以清楚的知道数据库在执行这个查询时后发生“full table scan”： [](http://box.kancloud.cn/2015-07-17_55a91a6e2d44e.png) 下列的查询叙述可以看出数据库使用索引来传回资料： [](http://box.kancloud.cn/2015-07-17_55a91a6ef41fa.png) 如果是包含有子查询的查询叙述，“EXPLAIN”也会分别帮你执行分析的工作： [](http://box.kancloud.cn/2015-07-17_55a91a7faa933.png) 使用“EXPLAIN”来检查在这章讨论索引的查询叙述： [](http://box.kancloud.cn/2015-07-17_55a91a860c195.png) 换成下列的查询叙述后，“EXPLAIN”会告诉你数据库使用索引来传回资料： [](http://box.kancloud.cn/2015-07-17_55a91a88c54fb.png) # 5 资料维护 当你使用“INSERT”、“UPDATE”或“DELETE”叙述执行资料维护的工作时，也要注意效率上的问题。在执行修改或删除资料的时候，除了要修改或删除表格中所有的资料以外，你都会加入条件的设定。在“UPDATE”和“DELETE”叙述中使用“WHERE”子句设定条件时，跟查询时候该注意的地方都一样，除了尽量使用索引来增加执行的效率，也要避免不必要的资料转换。 MySQL提供的“EXPLAIN”叙述，只可以为你分析一个查询叙述，它不可以使用在“SELECT”以外的叙述。不过你也可以这样作： [](http://box.kancloud.cn/2015-07-17_55a91a8a07734.png) MySQL提供使用一个“INSERT”叙述新增多笔资料的语法，如果你一次要新增多笔资料的话，使用这样的方式新增资料会是比较有效率的： [](http://box.kancloud.cn/2015-07-17_55a91a8acf48d.png) # 6 LIMIT子句 在查询和维护资料的时候，都有可能会使用“LIMIT”子句设定查询或维护资料的数量。“LIMIT”子句在某些应用上是非常方便的，不过要特别注意在效率上的问题。以下列的例子来说： [](http://box.kancloud.cn/2015-07-17_55a91a8c5ff09.png) 虽然这个查询叙述只有传回五笔资料，可以数据库总共读取了105笔资料，这样的查询会是比较没有效率的。你可以使用索引与“ORDER BY”子句来增加效率： [](http://box.kancloud.cn/2015-07-17_55a91a8cd0ddd.png) # 7 使用暂时表格 在执行比较复杂的查询工作时，在一个查询叙述中，可能会有结合查询、子查询和其它复杂的判断条件。一个看起来比较长而且比较复杂的查询，效率并一定比较不好。不过你可以使用一些比较特别的方式，进一步改善查询的复杂度与效率。以下列的查询来说： [](http://box.kancloud.cn/2015-07-17_55a91a8e9e10c.png) 上列的查询是一个不算太复杂的结合查询，如果还要在加上其它的条件判断的话，看起来就会更长一些： [](http://box.kancloud.cn/2015-07-17_55a91a8f24e6c.png) 如果还要再结合另外一个表格的话，这个查询看起来就真的很复杂了： [](http://box.kancloud.cn/2015-07-17_55a91a9077a82.png) 上列的查询看起来虽然复杂，不过如果都有可以使用的索引，它执行的效率也会是不错的。如果在查询工作中，很常使用第一个查询的结果，再加上不同的条件或结合，你就可以考虑使用下列的叙述，先建立好一个暂时的表格： [](http://box.kancloud.cn/2015-07-17_55a91aa5227e4.png) 因为查询的结果已经储存在“countrycapital”表格中，所以要加入其它的条件就变得简单多了： [](http://box.kancloud.cn/2015-07-17_55a91aa697bac.png) 如果要再结合另外一个表格的话，也会比较容易： [](http://box.kancloud.cn/2015-07-17_55a91aaa70824.png) 在“第九章、子查询、FROM子句与子查询”讨论到可以把一个查询放在“FROM”子句中： [](http://box.kancloud.cn/2015-07-17_55a91aad0dc29.png) 使用这样的方式虽然可以得到一样的查询结果，不过在你很常使用上列子查询来增加条件的情况下，每次执行不同条件的查询，数据库都要重新执行子查询叙述；先建立暂时的表格，再使用暂时表格执行查询的作法会是比较有效率的。 # 8 储存引擎 MySQL数据库是一种允许多个用户端同时使用的数据库管理系统，在多用户端的的运作环境下，数据库就使用“锁定、Locking”来避免资料的混乱： [](http://box.kancloud.cn/2015-07-17_55a91ab1415d4.png) MySQL提供的“MyISAM”和“InnoDB”两种储存引擎，使用不同的锁定方式来处理上列的情况。MyISAM使用的是“table-level”的锁定方式： [](http://box.kancloud.cn/2015-07-17_55a91ac32d074.png) MyISAM储存引擎使用的“table-level”锁定方式，适合使用在查询工作非常多，资料维护比较少的数据库，这样的数据库运作起来的效率会比较好。 InnoDB储存引擎使用的是“row-level”的锁定方式： [](http://box.kancloud.cn/2015-07-17_55a91aca7906b.png) InnoDB储存引擎使用的“row-level”锁定方式，适合使用在查询与资料维护工作都差不多的数据库，这样的数据库运作起来的效率会比较好。