优化 MySQL:查询和索引

Tags: database mysql

介绍索引

你遇到过这种场景,数据库太慢了!查询队列阻塞,backlog 增长,新的连接被拒绝。管理层打算花费数百万美元“升级”到其他的数据库系统,然而这写问题仅仅是没有正确的使用 MySQL,糟糕的表定义或者没有使用 MySQL 索引是性能问题的主要原因之一,而修正这些问题往往能得到显著的性能提升,考虑下面极端的情况:

CREATE TABLE employee (
   employee_number char(10) NOT NULL,
   firstname varchar(40),
   surname varchar(40),
   address text,
   tel_no varchar(25),
   salary int(11),
   overtime_rate int(10) NOT NULL
);

要查找员工 Fred Jone(员工编号 101832) 的薪水,你可以执行:

SELECT salary FROM employee WHERE employee_number = '101832';

MySQL 不知道如何来找到记录,它甚至不知道是否能找到一个匹配或者这有多个匹配,所以它只能查询整张表(有可能上千条数据)来查找 Fred 的薪水。

MySQL 的索引是一个单独的已排序文件,并且仅包含你需要排序的字段,如果你为 employee_number 创建索引,MySQL 能非常快的查询到相应的数据(索引很像书的目录)。

在我们修复上面表的表结构前,让我告诉你一个查询优化工作中的一个小秘密:EXPLAINEXPLAIN 显示(解释)你的查询时如何执行的,将这个关键字放到 SELECT 前面,你可以看到索引是否被正确使用,以及将执行那类 JOIN,比如:

EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875';

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

上面的输出代表什么呢?

  • table:将输出哪个表(或哪些表,如果你的查询连接了很多表的话)

  • type:这列很重要,它告诉我们使用了那种类型的 "join",从最好到最差的顺序是:system > const > eq_ref > ref > range > index > all

  • possible_keys:显示这个表可能使用的索引有哪些

  • key:哪个索引最终被使用了

  • key_len:被使用的 key 的长度,越短越好

  • ref:告诉我们使用了哪个列或常量

  • rows:MySQL 获得数据需要查看多少行

  • extra:额外的信息,如果你看到 'using temporary' 或者 'using filesort' 的话就很糟糕了。

看上去我们的查询差到不行,不能再差了!这里没有可用的索引(keys),MySQL 需要遍历所有数据(当前只有两条,设想如果有 200 万条会怎样!)

好吧,让我们像上面说的那样添加索引,然后重新执行 EXPLAIN ,我们得到:

+----------+-------+---------------+---------+---------+-------+------+-------+
| table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----------+-------+---------------+---------+---------+-------+------+-------+
| employee | const | PRIMARY       | PRIMARY |      10 | const |    1 |       |
+----------+-------+---------------+---------+---------+-------+------+-------+

上面的查询非常好(应该是好到不能再好),"join" 的类型(在这个简单例子中不是真正的 join)是 "const",它代表只有一行数据匹配查询,查找数据使用的是主键,并且 MySQL 认为查找仅需要检查一行数据。这意味着在你阅读这个执行计划的时间里 MySQL 可以执行上千(或者上万)次这样的查询。

深入索引

了解索引是如何工作的可以让你更加有效的使用它们。首先,要注意的是当你为表添加索引后,表更新后索引也需要更新,这就是为性能需要付出的代价。不过除了你的系统运行的插入比检索更多并且需要快速插入以外这不是一个很大的代价。

如果你希望检索多个条件会怎样呢?(你将会看到,仅当你对 WHERE 子句中用到的字段添加索引才行)。查询:

SELECT firstname FROM employee;

不会使用任何索引,针对 firstname 的索引是无效的,不过:

SELECT firstname FROM employee WHERE surname="Madida";

会使用为 surname 创建的索引

让我们看一些更复杂的例子,使用 EXPLAIN 可以帮助我们优化查询。我们想要找到所有加班工资的一半小于 $20 的员工,当知道要做什么以后,我们可以参考 where 子句中使用的字段来选择正确的索引字段:overtime_rate:

ALTER TABLE employee ADD INDEX(overtime_rate);

现在让我们执行查询:

EXPLAIN SELECT firstname FROM employee WHERE overtime_rate/2<20;

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    2 | where used |
+----------+------+---------------+------+---------+------+------+------------+

貌似不太好!每条员工记录都会被读取,为什么?仔细看看 overtime_rate/2 子查询,因为要计算 overtime_rate / 2 每个 overtime_rate(自然代表每条记录)都需要被读取,所以我们要尝试仅单独使用被索引的字段,并且不要在这个字段上执行任何计算。如何做到呢?你需要你在学校里的代数知识来拯救你了!你知道 x / 2 = yx = y * 2 是等价的,我们可以这样重写查询来检查 overtime_rate 是否小于 20 * 2

EXPLAIN SELECT firstname FROM employee WHERE overtime_rate<20*2;

+--------+-------+---------------+---------------+---------+------+------+----------+
|table   | type  | possible_keys | key           | key_len | ref  | rows |Extra     |
+--------+-------+---------------+---------------+---------+------+------+----------+
|employee| range | overtime_rate | overtime_rate |       4 | NULL |    1 |where used|
+--------+-------+---------------+---------------+---------+------+------+----------+

好很多了!MySQL 可以仅执行一次 20 * 2,并且依据索引来查询这个结果,这里的原理是让你被索引的字段在对比中独立,是的 MySQL 可以使用字段的索引进行查询,并且不要在这些字段上进行计算。

你可能会说这不公平,应该将上面的例子定义为 "哪些员工的加班薪水小于 $40",但是用户可能在实际工作中使用这种类型的查询。

最左前缀

surname 排序是一个普通的需求,因此为 surname 创建索引是有意义的。不过在我们的例子中员工表包含了数千个斯威士兰籍员工,并且都姓 Dlamini,那么同样我们需要为 firstname 增加索引。好消息是,MySQL 使用最左前缀(leftmost prefixing),这代表多字段索引 A, B, C 不仅仅可以用于 a, b, c 字段组合的检索,也可以用于 a, b 以及仅仅 a 的检索。

在我们的例子中,这代表下面类型的索引:

ALTER TABLE employee ADD INDEX(surname,firstname);

可以用于下面的查询:

EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida';

同样也可以用于:

EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida' and firstname="Mpho";

他们的结果都是:

+--------+------+-------------------+---------+---------+-------+------+-----------+
|table   | type | possible_keys     | key     | key_len | ref   | rows |Extra      |
+--------+------+-------------------+---------+---------+-------+------+-----------+
|employee| ref  | surname,surname_2 | surname |      41 | const |    1 |where used |
+--------+------+-------------------+---------+---------+-------+------+-----------+

然而,对于查询:

EXPLAIN SELECT overtime_rate FROM employee WHERE firstname='Mpho';

并不会使用任何索引,因为 firstname 并不在索引的最左边:

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| employee | ALL  | NULL          | NULL |    NULL | NULL |    3 | where used |
+----------+------+---------------+------+---------+------+------+------------+

如果你需要这种类型的查询,你需要为 firstname 增加单独的索引。

查询优化器 OPTIMIZE 和 ANALYZE

MySQL 内部用来决定查询使用 key 或 any 的魔法是查询优化器(query optimizer),它会快速扫描索引来确定哪个索引最适合。比如检索艺术家 “Savuka” 的 CD “Third World Child”,这里有两个索引,一个按艺术家名字字母排序,另一个按唱片集排序。通过快速扫描,你发现这里有 20000 个艺术家以及 400000 个唱片集,因此你决定依据艺术家进行检索。不过,如过你知道这里只有 50 张 “Savuka” 的唱片集,而且 “Third World Child” 是唯一以 “T” 开头的唱片集,你肯定会调整检索条件,你可以运行下面命令得到类似的优化信息:

ANALYZE TABLE tablename;

它会保存分布在表中的 key(ANALYZEmyisamchk -amyismachk --analyze 是相同的)

很多删除和更新语句会在表中留下碎片(尤其当你使用 varchar 或特别的 text/blob 字段),这意味着我需要更多的不必要的磁盘 I/O 操作,并且在读取时磁头需要跳过这些碎片。命令:

OPTIMIZE TABLE tablename

可以解决这个问题,对于保养良好的系统这两个命令需要经常执行。

另一个要素是,大部分人不会利用短索引的优势,你不需要为所有的字段建立索引。我们的 firstnamesurname 字段每个是 40 个字符,这代表我们上面创建的索引会有 80 个字符。在这张表中插入数据会额外写入 80 个字符,并且检索时需要操作 80 个字符的数据块(磁盘 I/O 是主要的硬件瓶颈),需要尝试降低索引的大小 - 对于上面的例子,最好使用:

ALTER TABLE employee ADD INDEX(surname(20),firstname(20));

现在我们更新的索引只有之前的一半大小,并且检索时会查找更小的索引。两者都很快(除非你的索引过于小,假设书的索引,我们没有针对全文建立索引而 仅针对第一个字母建立索引!我们会耗费非常多的时间来检索 'semaphore' 和 'saxophone' ,不要做这样的事情!)

这同样适用于原始字段定义。现今,磁盘空间非常充足,我们基本不用担心空间问题。不过小通常意味着快,因此在 firstname 不会大于 20 个字符的情况下将 surnamefirstname 字段定义为 CHAR(255) 绝对是一个错误!我们不喜欢名字呗截断,但是要记得条件变化时我们可以随时 ALTER 字段来分配更多的字符。我同样建议使用 VARCHAR 它比 CHAR 更好(使用可变长度字符串而不是固定长度字符串),尽管因为会产生更多的碎片很多人不建议使用它,我会通过频繁的执行 OPTIMIZE 来克服这个问题。

优化插入

大部分系统需要为查询进行高度优化来让新站点这可以支持每天数百万的查询,但是这些数据是从大的文本中批量插入的,因此,我们需要优化插入而不影响每天数百万的数据访问性能。

假设我们希望将使用 '|' 分隔的文本插入到数据库中,考虑下面一段 PHP 代码:

// open the file for reading
if (!($fp = fopen("datafile.txt","r"))) {
    // display error        
    print "\nUnable to open datafile.txt for writing";

    // end the running of the program 
    exit();                                       
}

// loop through the file line by line
while (!feof ($fp)) {      
    // loop through the file line by line             
    $sline = fgets($fp, 4096);

    // remove the newline    
    $sline = chop($sline);

    // split the line on "|", populating the ind. variables
    list($eno,$fname,$sname,$telno,$salary) = split("|",$code);

    $db->query("insert into employee(employee_number,firstname,surname, tel_no, salary 
            values($eno,'$fname','$sname','$tel_no', $salary)");
} // end while loop

这代码可以工作,但是会非常慢。索引缓存会在每次插入后被刷新。直到最近, MyISAM 表类型(MySQL默认的表类型)不允许数据在插入的同事被读取。新的表类型允许,不过仅当没有执行删除操作时(很想高负载数据库系统),因此在每次执行 插入时会锁定整张表。对于小型站点这没什么问题,不过当你每秒钟要处理成百上千个查询时,你很快就需要注意 backlog 了!

当然,这有解决方案,最好的方式是是哟更 MySQL 的 LOAD DATA INFILE 来插入数据。这非常快(20 倍),而且是对于那些还挂着的系统来说是唯一的方式。

语法很简单,并且代码也变得很简单:

$db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");

如果你没有特别指定,LOAD DATA INFILE 默认的是:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

如同原始插入一样,如果字段顺序不同或者就像上面的例子一样你不需要像每个字段插入数据时,你需要指定字段列表。不过为每个查询指定字段列表是一个好的实践,因为在系统演进过程中,如果表新增了字段,你肯定不想每次都重新修正你的 INSERTSELECT * 语句吧。

如果你发现这个语句工作的不正确,你需要仔细看看文本文件的格式,我遇到的大部分 LOAD DATE 问题都是因为文本文件格式不正确,每行中的每个字段需要正确的分隔。

你可能不会总是从文本文件插入数据,可能你的应用需要持续的插入不相关的数据,这里有一些方法能确保不会严重的影响检索性能:首先是 INSERT LOW PRIORITY,它会等到没有读取等待、等待系统间隙。当然,如果你的数据库处于高峰时期,没有任何执行间隙,那么客户端执行的 insert low priority 会变成生长的蜘蛛网。这时候可选的额方案是 INSERT DELAYED,客户端会被立即释放,并且插入语句会被放入队列(等待队列中其他等待的 INSERT DELAYED 语句执行完毕),这代表着不会有任何有意义的信息返回给客户端(比如自增字段的值),因为在客户端被释放时 INSERT DELAYED 还未执行。另外,要注意在诸如断电等异常情况下会丢失队列中未执行的插入语句。这两种方法你都无法知道数据是否成功插入,因此我建议你谨慎使用。

结论

不仅仅在插入数据时需要快速,有些时候你需要在清除数据时也要很快,不要执行:

DELETE FROM classifieds;

应该使用:

TRUNCATE TABLE classifieds;

区别在于,DELETE 一行行删除数据,有可能会执行一百万次!注意这不适用于 MySQL 4.0 以前的情况(如果你在一张非空表上执行 DELETE 后结果返回 0 条记录,你应该使用的是老版本的 MySQL),为了修复这个问题 MySQL 是的 DELETE 一行行的删除数据并返回删除了多少航数据的信息,不过 TRUNCATE 仍然执行快速删除。

这里简要的介绍了查询和索引的优化,一旦你熟悉了这些内容,你会发现你一些复杂的查询执行的并不够快。MySQL 官网包含很多有用的信息,并且也有很多优秀的书籍。但是千万别忘记 EXPLAIN!最好的方式是尝试通过不同的方式重新编写查询,并检查哪一个执行的更高效。通过实践,你会发现为什么这个查询会比另一个更快,祝你好运!

本文链接:http://www.4byte.cn/learning/37784/you-hua-mysql-cha-xun-he-suo-yin.html



相关文章