关系数据库原理

实验6:MySQL查询优化


本次实验我们将学习优化SQL查询性能的几个主要步骤。 通过性能优化,你可以提高SQL查询执行的速度和效率。


一、实验目的

  1. 了解查询优化的基础。
  2. 掌握如何进行资源管理。
  3. 学习使用索引,清楚认识索引的优缺点,知道何时必须使用索引。
  4. 掌握查询优化的技巧,学习改进数据相关操作。


二、上机实验步骤

实验环境
【硬件环境】
【操作系统】Redhat CentOS 6.5
【数据库环境】MySQL 5.7

优化查询的原则

任何一位数据库程序员都会有这样的体会: 高通信量的数据库驱动程序中,一条糟糕的查询语句可对整个应 用程序的执行产生严重的影响,其不仅消耗更多的数据库时间, 且会对其他应用组件产生影响。优化查询性能很大程度上取决于 开发者的直觉。幸运的是,MySQL自带了一些辅助分析工具。 在查询优化中,使用索引、使用EXPLAIN分析查询以及 调整MySQL的内部配置均可达到优化查询的目的。


  1. 使用索引

    MySQL允许对数据库表进行索引,以此达到 迅速查找记录的目的,而无需一开始就扫描整表,因此能显著加快 查询速度。每个表最多可有16个索引,此外MySQL还支持多列索引 及全文索引(尽量不要使用)。


    为表添加索引非常简单,只需调用CREATE INDEX 并为索引指定它的域即可,以确保在WHERE或者HAVING子句中引用 这一域的SELECT查询语句运行速度比没有添加索引时快。要查看 某个数据库表具有的索引,可用SHOW INDEX FROM tablename命令。


    mysql> CREATE INDEX idx_name table(column_name); mysql> SHOW INDEX FROM table;
    1. 值得注意的是,索引有时也像一把双刃剑。 对表的每一域做索引通常没有必要,不但起不到加快查询速度的目的, 反而很可能适得其反,因为在向表中插入或者修改数据时,MySQL不 得不为这些行重新建立索引,这也成为了额外的开销。而另一方面, 如果不对表的每一域做索引,固然提高了插入和修改记录的速度, 但却导致了查询操作的速度变慢。这就需要我们把握好平衡。在设计 索引系统时,必须充分考虑表的主要功能。
    2. 索引从功能上分,主要有B-Tree/R-Tree/fulltext 等类型。从形式上分,主要类型包括普通索引/唯一索引(unique)/ 主键索引/复合(多列)索引。需要记住的是,包含NULL值的列不能 添加索引;
  2. 优化查询性能

    分析查询性能时,考虑EXPLAIN关键字同样非常管用。 EXPLAIN命令一般放在SELECT查询语句的前面,用来描述MySQL如何执行查询 操作,以及MySQL成功返回结果所需要执行的行数,例如:

    mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND'; +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index | | 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4079 | Using where | +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+

    上表中各个属性的含义是是:

    id 显示待分析的SELECT语句的ID。如果语句不包含子查询或者union,则ID均为1 select_type 显示SELECT语句的类型。SIMPLE说明是普通查询,不包含子查询和UNION。其他 取值说明是子查询还是UNION。 table 输出行引用的表名 type 显示联合查询使用了何种类型,从最好到最差的连接类型为const-->eq_reg-->ref -->fulltext-->ref_or_null-->index_merge-->unique_subquery--> index_subquery-->range-->index-->ALL,一般来说得保证查询至少达到range 级别,当然了最好能达到ref级别。 possible_keys 显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句 中选择一个合适的语句 key 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的 索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者 用IGNORE INDEX(indexname)来强制MYSQL忽略索引 key_len 使用的索引的长度。在不损失精确性的情况下,长度越短越好。对于多重主键,该值可以看出 实际使用了哪一部分 ref 显示索引的哪一列被使用了,如果可能的话,是一个常数 rows MYSQL认为必须检查的用来返回请求数据的行数 Extra 关于MYSQL如何解析查询的额外信息。坏的例子包括Using temporary和Using filesort, 意思MYSQL根本不能使用索引,结果是检索会很慢。如果是Using index则说明结果很理 想,只需采用索引树的信息即可得到结果。 ============================================================================================== Extra 列返回的描述的意义 Distinct 一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 Not exists MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了 Range checked for each Record(index map:#) 没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引, 并用它来从表中返回行。这是使用索引的最慢的连接之一 Using filesort 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对 返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 Using index 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的 全部的请求列都是同一个索引的部分的时候 Using temporary 看到这个的时候,查询需要优化了。MYSQL需要创建一个临时表来存储结果,这通常发 生在对不同的列集进行ORDER BY上,而不是GROUP BY上 used where 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的 全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题

    上面的查询是基于两个表的连接。EXPLAIN命令描 述了MySQL是如何处理连接这两个表的。很显然,当前计划要求MySQL处 理的是country表中的一条记录以及city表中的4079条记录。我们可以 为city表添加索引:

    mysql> CREATE INDEX idx_ccode ON city(countrycode);

    重新用EXPLAIN分析查询,可以看到显著的改进:

    mysql> EXPLAIN SELECT city.name, city.district FROM city, country WHERE city.countrycode = country.code AND country.code = 'IND'; +----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+ | 1 | SIMPLE | country | const | PRIMARY | PRIMARY | 3 | const | 1 | Using index | | 1 | SIMPLE | city | ref | idx_ccode | idx_ccode | 3 | const | 333 | Using where | +----+-------------+---------+-------+---------------+-----------+---------+-------+------+-------------+

    在上面的例子中,MySQL只需要扫描city表中的 333条记录可以了,其扫描记录较少了约90%!因而查询效率得到了极 大的提高。


  3. 调整内部变量

    MySQL可以通过调整其缺省设置以获得更优的性能 和更高的稳定性,需要优化的关键变量主要有:

    1. 索引缓冲区长度(key_buffer)
    2. 读缓冲区的长度(read_buffer_size)
    3. 打开表的数目的最大值(table_cache)
    4. 检索时间限制(long_query_time)

  4. 总的来说,要想使一个较慢速SELECT ... WHERE更快,应首先检查是否能增加一个索引。

    优化器是如何工作的

    MySQL查询优化器有几个目标,但是其中 最主要的目标是尽可能地使用索引,并且使用最严格的索引来 消除尽可能多的数据行。你的最终目标是提交SELECT语句查找 数据行,而不是排除数据行。优化器试图排除数据行的原因在 于它排除数据行的速度越快,那么找到与条件匹配的数据行也 就越快。如果能够首先进行最严格的测试,查询就可以执行地 更快。假设你的查询检验了两个数据列,每个列上都有索引:

    SELECT col3 FROM mytable
    WHERE col1 = ’some value’ AND col2 = ’some other value’;
    

    假设col1上的测试匹配了900个数据行,col2上的测试匹配了 300个数据行,而同时进行的测试只得到了30个数据行。先测试Col1 会有900 个数据行,需要检查它们找到其中的30个与col2中的值匹 配记录,其中就有870次是失败了。先测试col2会有300个数据行, 需要检查它们找到其中的30个与col1中的值匹配的记录,只有270次 是失败的,因此需要的计算和磁盘I/O更少。其结果是,优化器会先 测试col2,因为这样做开销更小。

    这是几个更好地利用索引的原则

    • 尽量比较数据类型相同的数据列。当你在比 较操作中使用索引数据列的时候,请使用数据类型相同的列。相同 的数据类型比不同类型的性能要高一些。
    • 尽可能地让索引列在比较表达式中独立。如果 你在函数调用或者更复杂的算术表达式条件中使用了某个数据列, MySQL就不会使用索引,因为它必须计算出每个数据行的表达式值。

    下面的WHERE子句显示了这种情况。它们的功能相同, 但是对于优化目标来说就有很大差异了:

    WHERE mycol > 4 / 2
    WHERE mycol * 2 > 4
    

    对于第一行,优化器把表达式4/2简化为2,接着使 用mycol上的索引来快速地查找小于2的值。对于第二个表达式,MySQL必须 检索出每个数据行的 mycol值,乘以2,接着把结果与4进行比较。在这种情 况下,不会使用索引。数据列中的每个值都必须被检索到,这样才能计算 出比较表达式左边的值。

    我们看另外一个例子。假设你对date_col列进行了索 引。如果你提交一条如下所示的查询,就不会使用这个索引:

    SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;
    

    这个表达式不会把1990与索引列进行比较;它会把 1990与该数据列计算出来的值比较,而每个数据行都必须计算出这个值。 其结果是,没有使用 date_col上的索引,因为执行这样的查询需要全表 扫描。怎么解决这个问题呢?只需要使用文本日期,接着就可以使用 date_col上的索引来查找列中匹配的值了:

    WHERE date_col < '1990-01-01'
    

    但是,假设你没有特定的日期。你可能希望找到 一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多 种方法--它们的效率并不同。下面就有三种:

    WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
    WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
    WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)
    

    对于第一行,不会用到索引,因为每个数据行都 必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff 和TO_DAYS(CURDATE())都是常量,因此在处理查询之前,比较表达式的 右边可以被优化器一次性计算出来,而不需要每个数据行都计算一次。 但是 date_col列仍然出现在函数调用中,它阻止了索引的使用。第三行 是这几个中最好的。同样,在执行查询之前,比较表达式的右边可以作 为常量一次性计算出来,但是现在它的值是一个日期。这个值可以直接 与date_col值进行比较,再也不需要转换成天数了。在这种情况下,会 使用索引。

    在LIKE模式的开头不要使用通配符。有些字符串搜 索使用如下所示的WHERE子句:

    WHERE col_name LIKE '%string%'
    

    如果你希望找到那些出现在数据列的任何位置的字符 串,这个语句就是对的。但是不要因为习惯而简单地把"%"放在字符串的两 边。如果你在查找出现在数据列开头的字符串,就删掉前面的"%"。假设你 要查找那些类似MacGregor或MacDougall等以"Mac"开头的名字。在这种情 况下,WHERE子句如下所示:

    WHERE last_name LIKE 'Mac%’
    

    优化器查看该模式中词首的文本,并使用索引找到 那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的 另一种形式:

    WHERE last_name >= 'Mac’ AND last_name < 'Mad'
    

    这种优化不能应用于使用了REGEXP操作符的模式匹配。 REGEXP表达式永远不会被优化。

  5. 上机任务

    1. 关注与字符串处理/日期时间相关的一些函数。
    2. 仍以DOINT数据库为例,为所有的表建立临时复制表。
    3. 删除复制表中索引前后分别用EXPLAIN分析其查询计划有何改变?
    4. 存在索引的条件下,改变查询条件的范围,观察其是否使用索引?例如,使用大于或者小于为条件查询pfam表。
    5. 分析连接查询(join)和嵌套查询(nested query)对查询计划的影响,结合实验3的一些例子。
    6. 为表GO的GoDesc字段建立全文索引(fulltext),并举例说明。其语法为: mysql> ALTER TABLE table type='MyISAM'; mysql> ALTER TABLE table ADD FULLTEXT idx_name (column); mysql> SELECT * FROM table WHERE match(column) against('some query text');

      注意,只有MyISAM引擎才支持全文索引。下图是不同存储引擎的特性:

    7. 考虑为GoDesc建立前缀索引(prefix index),也就是说用该列的前面一定长度的字符串建立索引。 观察与对整个字符串建立索引相比是否存在优势?建立前缀索引的方式是怎么样的?查看help create index, 你会得到你的答案。(注意关键词length
    8. 将临时表合并为一张表,说明该表是否满足1NF?2NF?3NF?BCNF?如果不满足需要如何修改才能 满足该范式?
    9. 建立一个表,包含3个域(fields)c1, c2, c3, 为(c1,c2,c3)建立多列索引,对c1、c2、c3 进行单列条件查询,观察是否使用索引。如果对(c1,c2,c3)进行联合查询呢?(c1,c2)?(c2,c3)?

三、实验报告

完成以上练习,并将实验报告以tar.gz附件的形式发送到ricket.woo AT gmail.com, 文件名为5150809XXXlab6.rar。实验报告的内容应包括实验环境,实验内容和结 果/讨论。报告提交的截至日期是2017年5月18日。