MySQL 优化总结 （三）

range checked for each record (key map: 35)

　　因为type对每张表是ALL，这个输出显示MySQL正在对所有表产生一个笛卡尔乘积;即每一个行的组合!这将花相当长的时间，因为必须检查每张表的行数的乘积!对于一个实例，这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大，你只能想象它将花多长时间……

这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15)，长度不匹配。

　　为了修正在列长度上的不同，使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符：

　　mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

　　现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)，再执行EXPLAIN语句产生这个结果：

　　table type possible_keys key key_len ref rows Extra

　　tt ALL AssignedPC, NULL NULL NULL 3872 Using

　　ClientID, where

　　ActualPC

　　do ALL PRIMARY NULL NULL NULL 2135

　　range checked for each record (key map: 1)

　　et_1 ALL PRIMARY NULL NULL NULL 74

　　range checked for each record (key map: 1)

　　et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

　　这不是完美的，但是好一些了：rows值的乘积少了一个因子74。这个版本在几秒内执行完。

　　第2种方法能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列的长度失配问题：

　　mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),

　　-> MODIFY ClientID VARCHAR(15);

　　EXPLAIN产生的输出显示在下面：

　　table type possible_keys key key_len ref rows Extra

　　et ALL PRIMARY NULL NULL NULL 74

　　tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using

　　ClientID, where

　　ActualPC

　　et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1

　　do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

　　这几乎很好了。

如果数据库慢了，想要看看mysql正在干什么，可以执行这个语句，可以列出mysql当前连接在执行的sql语句。　　

Id    User Host db    Command       Time       State       Info

2599       oa    SVCTAG-91FFF2X:3726      penguin   Sleep       7321              <null>

2956       rw_penguin    SVCTAG-6HKDF2X:5481     penguin   Sleep       2293              <null>

2957       rw_penguin    CYSY-DL580-2:3925     penguin   Sleep       2162              <null>

2963       rw_penguin    CYSY-DL580-2:4122     penguin   Sleep       2162              <null>

2965       rw_penguin    CYSY-DL580-2:4270     penguin   Sleep       2162              <null>

　　………………………………………………………………

　　以下内容摘自手册

　　SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程(也就是，与您正在使用的MySQL账户相关的线程)。如果您不使用FULL关键词，则只显示每个查询的前100个字符。

　　本语句报告TCP/IP连接的主机名称(采用host_name:client_port格式)，以方便地判定哪个客户端正在做什么。

　　如果您得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。MySQL保留一个额外的连接，让拥有SUPER权限的账户使用，以确保管理员能够随时连接和检查系统(假设您没有把此权限给予所有的用户)。

　　在来自SHOW PROCESSLIST的输出中常见的一些状态：

　　· Checking table

　　线程正在执行(自动)表格检查。

　　· Closing tables

　　意味着线程正在刷新更改后的表数据，并正在关闭使用过的表。这应该是一个快速的操作。如果不快，则您应该验证您的磁盘没有充满，并且磁盘没有被超负荷使用。

　　· Connect Out

　　连接到主服务器上的从属服务器。

　　· Copying to tmp table on disk

　　临时结果集合大于tmp_table_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器。

　　· Creating tmp table

　　线程正在创建一个临时表，以保持部分结果。

　　· deleting from main table

　　服务器正在执行多表删除的第一部分，只从第一个表中删除。

　　· deleting from reference tables

　　服务器正在执行多表删除的第二部分，从其它表中删除匹配的行。

　　· Flushing tables

　　线程正在执行FLUSH TABLES，并正在等待所有线程，以关闭表。

　　· FULLTEXT initialization

　　服务器正在准备执行一个自然语言全文本搜索。

· Killed

　　有人已经向线程发送了一个KILL命令。在下一次检查终止标记时，应放弃。该标记在MySQL的每个大循环中都检查，但是在有些情况下，线程终止只需要较短的时间。如果该线程被其它线程锁定，则只要其它线程接触锁定，终止操作就会生效。

　　· Locked

　　该查询被其它查询锁定。

　　· Sending data

　　线程正在为SELECT语句处理行，同时正在向客户端发送数据。

　　· Sorting for group

　　线程正在进行分类，以满足GROUP BY要求。

　　· Sorting for order

　　线程正在进行分类，以满足ORDER BY要求。

　　· Opening tables

　　线程正在试图打开一个表。这应该是非常快的过程，除非打开操作受到阻止。例如，一个ALTER TABLE或一个LOCK TABLE语句可以阻止打开一个表，直到语句完成为止。

　　· Removing duplicates

　　查询正在使用SELECT DISTINCT。使用时，在早期阶段，MySQL不能优化不同的操作。因此，MySQL要求一个额外的阶段，以便在把结果发送给客户端之前取消所有的复制行。

　　· Reopen table

　　线程得到一个表锁定，但是在得到锁定后被通知带下方的表结构已更改了。它已经释放了锁定，关闭了表，并试图重新打开它。

　　· Repair by sorting

　　修复代码正在使用一个分类来创建索引。

　　· Repair with keycache

　　修复代码正在通过关键缓存一个接一个地使用创建关键字。这比通过分类修复要慢很多。　　

· Searching rows for update

　　线程正在进行第一阶段，以在更新之前，查找所有匹配的行。如果UPDATE正在更改用于查找相关行的索引，则必须这么做。

　　· Sleeping

　　线程正在等待客户端，以向它发送一个新语句。

　　· System lock

　　线程正在等待得到一个用于表的外部系统锁定。如果您没有正在使用多个正在访问同一个表的mysqld服务器，则您可以使用--skip-external-locking选项禁用系统锁定。

　　· Upgrading lock

　　INSERT DELAYED管理程序正在试图得到一个表锁定，以插入行。

　　· Updating

　　线程正在搜索行，并正在更新这些行。

　　· User Lock

　　线程正在等待GET_LOCK()。

　　· Waiting for tables

　　线程得到一个通知，表的底层结构已经改变，需要重新打开表以得到新的结构。但是，为了能重新打开表，必须等待，直到所有其它的线程已经关闭了正在被质询的表。

　　如果其它线程已经对正在被质询的表使用了FLUSH TABLES或以下语句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE或OPTIMIZE TABLE;则会出现通知。

　　· waiting for handler insert

　　INSERT DELAYED管理程序已经处理了所有处于等待状态的插入，并正在等待新插入。

　　多数状态对应于非常快的操作。如果一个线程在这些状态下停留了数秒，则可能是有问题，需要进行调查。

　　有一些其它的状态，在前面的清单中没有提及，但是其中有很多状态对于查找服务器中的程序错误是有用的。

　　从这里可以看到是不是有表锁死了，是不是有些语句执行了很久，甚至可以通过kill id 语句灭了捣乱的连接。

 

剩下的问题是，默认情况，MySQL假设在tt.ActualPC列的值是均匀分布的，并且对tt表不是这样。幸好，很容易告诉MySQL来分析关键字分布：

　　mysql> ANALYZE TABLE tt;

　　现在联接是“完美”的了，而且EXPLAIN产生这个结果：

　　table type possible_keys key key_len ref rows Extra

　　tt ALL AssignedPC NULL NULL NULL 3872 Using

　　ClientID, where

　　ActualPC

　　et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

　　et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1

　　do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

　　注意在从EXPLAIN输出的rows列是一个来自MySQL联接优化器的“教育猜测”。你应该检查数字是否接近事实。如果不是，可以通过在SELECT语句里面使用STRAIGHT_JOIN并且试着在FROM子句以不同的次序列出表，可能得到更好的性能。

　　我们需要特别注意的是key、Extra字段。Key字段显示用了哪个索引，注意看是不是用了索引，如果用了是不是效率最高的。Extra字段会说明是否用了临时表，是否用了基于磁盘的临时表，是否用了文件排序，是否用了全索引扫描，where是有数据库engine产生的还是由数据库server限制的。