首页技术文章正文

Java培训实战教程之mysql优化

更新时间:2016年01月20日15时11分来源:传智播客Java培训学院浏览次数:

Java培训过程中精点、难点知识解析
1. mysql引擎1.1. 引擎类型

MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事务安全表，其他存储引擎都是非事务安全表。

MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。

innoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引

MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。

1.2. 如何选择合适的存储引擎

选择标准: 根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择

多种存储引擎进行组合.

下面是常用存储引擎的适用环境:

1.MyISAM：默认的 MySQL 插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一

2.InnoDB：用于事务处理应用程序，具有众多特性，包括ACID 事务支持。

3.Memory：将所有数据保存在 RAM 中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

4.Merge：允许 MySQL DBA 或开发人员将一系列等同的MyISAM 表以逻辑方式组合在一起，

并作为 1 个对象引用它们。对于诸如数据仓储等 VLDB 环境十分适合

2. 设置高速缓存

注：可以通过order by语句测试缓存，order by语句执行速度慢！

2.1. 设置高速缓存2.1.1. 查看高速缓存是否支持

SHOW VARIABLES LIKE 'have_query_cache';

2.1.2. 设置和查询高速缓存大小

SET GLOBAL query_cache_size = 41984; #40K

SHOW VARIABLESLIKE 'query_cache_size';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| query_cache_size | 41984 |

+------------------+-------+

2.1.3. 缓存开启的方式

查看是否开启

SHOW VARIABLES LIKE'query_cache_type';

开启

SET SESSION query_cache_type =ON;

如果查询缓存大小设置为大于0，query_cache_type变量影响其工作方式。这个变量可以设置为下面的值：

0或OFF：将阻止缓存或查询缓存结果。

1或ON：将允许缓存，以SELECTSQL_NO_CACHE开始的查询语句除外。

2或DEMAND：仅对以SELECTSQL_CACHE开始的那些查询语句启用缓存。

出自Java培训

另外：

GLOBAL：设置所有链接的客户端

session：设置单个客户端

2.1.4. 设置缓存结果的最大值最小值

如果不设置缓存的上线下线，查询结果过大将不会缓存。

查询上线：

SHOW VARIABLES LIKE 'query_cache_limit';

设置上下线：

SET GLOBAL query_cache_limit=10485760; #10M

SET GLOBAL query_cache_min_res_unit=41984;

2.1.5. 查询高速缓冲状态和维护

可以使用下面的语句检查MySQL服务器是否提供查询缓存功能：

SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| have_query_cache | YES |

+------------------+-------+

FLUSH QUERY CACHE：语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。

RESET QUERY CACHE：语句从查询缓存中移出所有查询。FLUSH TABLES语句也执行同样的工作。

SHOW STATUS：为了监视查询缓存性能，使用SHOWSTATUS查看缓存状态变量，例如：

mysql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+--------+

| Qcache_free_blocks | 36 |

| Qcache_free_memory | 138488 |

| Qcache_hits | 79570 |

| Qcache_inserts | 27087 |

| Qcache_lowmem_prunes | 3114 |

| Qcache_not_cached | 22989 |

| Qcache_queries_in_cache | 415 |

| Qcache_total_blocks | 912 |

+-------------------------+--------+

QCACHE_free_blocks：空闲内存块的数量。

QCACHE_free_memory：空闲内存的大小。

QCACHE_hits：查询缓存被访问的次数（命中数）。

QCACHE_inserts：加入到缓存的查询数量。

QCACHE_lowmem_prunes：由于内存较少从缓存删除的查询数量。

QCACHE_not_cached：非缓存查询数(不可缓存，或由于query_cache_type设定值未缓存)。

Qcache_queries_in_cache：登记到缓存内的查询的数量。

Qcache_total_blocks：查询缓存内的总块数。

2.2. 高速缓存语句要求

下面的两个查询被查询缓存认为是不相同的：

SELECT * FROM tbl_name

Select * from tbl_name

查询必须是完全相同的(逐字节相同)才能够被认为是相同的。

2.3. 不缓存的语句

如果一个查询包含下面函数中的任何一个，它不会被缓存：

BENCHMARK()

CONNECTION_ID()

CURDATE()

CURRENT_DATE()

CURRENT_TIME()

CURRENT_TIMESTAMP()

CURTIME()

DATABASE()

带一个参数的ENCRYPT()

FOUND_ROWS()

GET_LOCK()

LAST_INSERT_ID()

LOAD_FILE()

MASTER_POS_WAIT()

NOW()

RAND()

RELEASE_LOCK()

SYSDATE()

不带参数的UNIX_TIMESTAMP()

USER()

3. EXPLAIN执行计划3.1. 简介

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。

挑一个你的SELECT语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。

EXPLAIN

SELECT * FROM userinfo u INNER JOIN jobinfo j ON u.jobinfoId=j.id;

查看执行计划：

参数解释：

id：查询的序号

select_type:select类型，simple表示简单的查询

table：引用的表

type：链接类型，all表示全表扫描，没有使用索引。

possible_keys：查询时可以使用的索引

key：查询时正在使用的索引

key_len：索引的长度

rows：查询的行数，乘积即为笛卡尔积

Extra：该列包含MySQL解决查询的详细信息。

3.1.1. 参数详解

id：这是SELECT的查询序列号。

select_type：SELECT类型，可以为以下任何一种：

SIMPLE：简单SELECT(不使用UNION或子查询)

PRIMARY：最外面的SELECT

UNION：UNION中的第二个或后面的SELECT语句

DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT：UNION的结果。

SUBQUERY:子查询中的第一个SELECT

DEPENDENT SUBQUERY:子查询中的第一个SELECT，取决于外面的查询

DERIVED:导出表的SELECT(FROM子句的子查询)

table:输出的行所引用的表。

type:联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：

system表仅有一行(=系统表)。

const表最多有一个匹配行，它将在查询开始时被读取。

eq_ref比较的时候，“=”前后的变量都加了索引。

ref:前面的表加了索引。

index：该联接类型与ALL相同，只是索引树被扫描。

ALL：全表扫描。

possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。

key:显示MySQL实际决定使用的索引。如果没有选择索引，键是NULL。

key_len:显示MySQL决定使用的索引长度。如果索引是NULL，则长度为NULL。

ref：显示使用哪个列或常数与key一起从表中选择行。

rows：显示MySQL认为它执行查询时必须检查的行数。

Extra：该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：

Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

Not exists：MySQL能够对查询进行LEFTJOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。

range checkedfor each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。

Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行

Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。

Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。

Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。

3.2. 优化方案3.2.1. 查看匹配的列类型和长度是否相同

查看两张表链接的列的类型和长度是否相同，不同改为相同

ALTER TABLE 表名 MODIFY 列名 BIGINT(20);源自java培训

3.3. 为相关联的列设置索引

查看索引：

SHOW INDEX FROM tbl_name;

创建索引：

ALTER TABLE 表名 ADD INDEX 索引名 (索引列) ;

删除索引：

drop index 索引名 on 表名;

显示使用索引：

USE INDEX

在你查询语句中表名的后面，添加 USE INDEX 来提供你希望 MySQ 去参考的索引列

表，就可以让 MySQL 不再考虑其他可用的索引。

Eg:SELECT * FROM mytable USE INDEX(mod_time, name) ...

IGNORE INDEX

如果你只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作

为 Hint。

Eg:SELECT * FROM mytale IGNORE INDEX(priority) ...

FORCE INDEX

为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 Hint。

Eg:SELECT * FROM mytable FORCE INDEX(mod_time) ...

3.4. 不使用索引的情况

下列情况下，Mysql 不会使用已有的索引：

1.如果 mysql 估计使用索引比全表扫描更慢，则不使用索引。例如：如果 key_part1

均匀分布在 1 和 100 之间，下列查询中使用索引就不是很好：

SELECT * FROM table_name where key_part1> 1 and key_part1 < 90

2.如果使用内存表并且 where 条件中不用＝索引列，其他>、<、 >=、 <=均不使用

索引；

3.如果 like 是以％开始；

4.对 where 后边条件为字符串的一定要加引号，字符串如果为数字 mysql 会自动转为

字符串，但是不使用索引。

3.5. 查看索引使用情况

语法：

mysql> show status like 'Handler_read%';

如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，

该值较高。通常说明表索引不正确或写入的查询没有利用索引。

4. 其他优化4.1. 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你也许会去检查返回的记录数。在这种情况下，加上 LIMIT 1 可以增加性能。这样一来，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

例如：

如果你想在登陆时验证用户名密码是否存在，你可以这样写

SELECT 1 FROM jobinfo WHERENAME ='zhangsan' AND PASSWORD = '1234' LIMIT1;

而不是

SELECT * FROM jobinfo WHERE NAME='zhangsan' AND PASSWORD = '1234';

4.2. 为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。

4.3. 避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

4.4. 永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的，并设置上自动增加的AUTO_INCREMENT标志。

就算是你 users 表有一个主键叫“email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……

4.5. 尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。

首先，问问你自己“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？如果你觉得它们之间没有什么区别，那么你就不要使用NULL。

不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。

4.6. Prepared Statements

Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。

Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。

在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。

4.7. 把IP地址存成 INT

在java培训过程中很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。

我们必需要使用NT，因为 IP地址会使用整个32位的无符号整形。

而你的查询，你可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用 INET_NTOA() 把一个整形转成一个字符串IP。

SELECT INET_ATON('192.168.0.1') FROMjobinfo;

SELECT INET_NTOA(3232235521) FROM jobinfo;

4.8. 固定长度的表会更快

在Java培训中过程中的一个真实的案例，如果表中的所有字段都是“固定长度”的，整个表会被认为是“static”或 “fixed-length”。例如，表中没有如下类型的字段：VARCHAR，TEXT，BLOB。

只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

4.9. 垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。

示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？这样会让你的表有更好的性能，大多的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。

示例二：你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。

4.10. 拆分大的 DELETE 或 INSERT 语句

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止响应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

Apache会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。

如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你导致WEB服务Crash，还可能会让你的整台服务器马上挂了。

所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。下面是一个示例：

while (1) {

//每次只做1000条

"DELETEFROM logs WHERE log_date <= '2009-11-01' LIMIT 1000";

if (select 1 FROM logs WHERE log_date <= '2009-11-01' LIMIT 1==0) {

// 没得可删了，退出！

break;

}

// 每次都要休息一会儿

usleep(50000);

}

4.11. 选择正确的存储引擎

在 MySQL 中有多个存储引擎 MyISAM 和 InnoDB等，每个引擎都有利有弊。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁”，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

4.12. 越小的列会越快

对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。

如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用SMALLINT 或是更小的 TINYINT 会更经济一些。

4.13. 使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

MySQL也有一个“建议”告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时，这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。

4.14. 从 PROCEDUREANALYSE() 取得建议

语法：SELECT * FROM student LIMIT 1,1 PROCEDURE ANALYSE(1);

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。

一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。

4.15. SHOW STATUS的其他参数

通过 SHOW STATUS可以提供服务器状态信息，SHOW STATUS 可以根据需要显示 session 级别的统计结果和 global级别的统计结果。

以下几个参数对 Myisam 和 Innodb 存储引擎都计数：

1.Com_select执行 select 操作的次数，一次查询只累加1；

2.Com_insert 执行 insert 操作的次数，对于批量插入的insert 操作，只累加一次；

3.Com_update 执行 update 操作的次数；

4.Com_delete执行 delete 操作的次数；

以下几个参数是针对 Innodb 存储引擎计数的，累加的算法也略有不同：

1.Innodb_rows_read 查询返回的行数；

2.Innodb_rows_inserted 执行 Insert 操作插入的行数；

3.Innodb_rows_updated 执行 update 操作更新的行数；

4.Innodb_rows_deleted执行 delete 操作删除的行数；

通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。

对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

此外，以下几个参数便于我们了解数据库的基本情况：

1.Connections 试图连接 Mysql 服务器的次数

2.Uptime服务器工作时间（秒）

3.Slow_queries 慢查询的次数

4.16. 定位执行效率较低的 SQL 语句

SHOW PROCESSLIST;

命令的输出结果显示了有哪些线程在运行，可以帮助识别出有问题的查询语句。

如果有 SUPER 权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的 MySQL帐户运行的线程）。

得到数据形式如下（只截取了三条）：

mysql> show processlist;

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

|207|root |192.168.0.20:51718 |mytest | Sleep | 5 | | NULL

|208|root |192.168.0.20:51719 |mytest | Sleep | 5 | | NULL

|220|root |192.168.0.20:51731 |mytest |Query |84 |Locked|

select bookname,culture,value,type from book where id=001

id ，不用说了吧，一个标识，你要 kill 一个语句的时候很有用。

user 列，显示当前用户，如果不是 root ，这个命令就只显示你权限范围内的 sql 语句。 host 列，显示这个语句是从哪个 ip 的哪个端口上发出的。可以用来追踪出问题语句的用户。

db 列，显示这个进程目前连接的是哪个数据库。

command 列，显示当前连接的执行的命令，一般就是休眠（ sleep ），查询（ query ），连接（ connect）。

time 列，此这个状态持续的时间，单位是秒。

state 列，显示使用当前连接的 sql 语句的状态，很重要的列，后续会有所有的状态的描述，请注意， state 只是语句执行中的某一个状态，一个 sql 语句，已查询为例，可能需要经过 copying to tmp table ， Sorting result ， Sending data 等状态才可以完成

info 列，显示这个 sql 语句，因为长度有限，所以长的 sql 语句就显示不全，但是一个判断问题语句的重要依据。

这个命令中最关键的就是 state 列， mysql 列出的状态主要有以下几种：

　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 ，等待其他线程关闭数据表。

　 Killed　发送了一个 kill 请求给某线程，那么这个线程将会检查 kill 标志位，同时会放弃下一个 kill 请求。MySQL 会在每次的主循环中检查 kill 标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么 kill 请求会在锁释放时马上生效。

　 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　修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。

　 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 tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE,ANALYZE TABLE, 或 OPTIMIZE TABLE 。

　 waiting for handler insert　 INSERT DELAYED 已经处理完了所有待处理的插入操作，正在等待新的请求。

　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。

　当MySQL繁忙的时候运行show processlist，会发现有很多行输出，每行输出对应一个MySQL连接。怎么诊断发起连接的进程是哪个？它当前正在干嘛呢？

首先，需要通过TCP Socket而不是Unix Socket连接MySQL，这样在show processlist的输出中就会有来源端口号。如下，

mysql> show processlist;

+——–+——–+—————–+——+———+——+——-+——————+

+——–+——–+—————–+——+———+——+——-+——————+

+——–+——–+—————–+——+———+——+——-+——————+

在Host列有来源IP和端口号，然后我们从连接机器查看端口号是谁打开的，

[root@localhost ~]# netstat -ntp | grep35558

… 124.115.0.68:35558 ESTABLISHED 18783/httpd

可知进程18783发起的MySQL连接来源端口是35558，然后就可以用strace观察这个进程了。

4.17. 优化 group by 语句

默认情况下，MySQL 排序所有 GROUP BY col1，col2，....。

查询的方法如同在查询中指定 ORDER BY col1，col2，...。

如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

如果查询包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL

禁止排序，出自传智播客Java培训精点难点知识总结中

例如：

SELECT jobName FROM jobinfo GROUP BYjobName ORDER BY NULL;

4.18. 优化 order by 语句

1、order by 后的字段，如果要走索引，须与where 条件里的某字段建立复合索引！！或者说orcer by后的字段如果要走索引排序，它要么与where 条件里的字段建立复合索引【这里建立复合索引的时候，需要注意复合索引的列顺序为（where字段，order by 字段），这样才能满足最左列原则，原因可能是order by字段并能算在where 查询条件中！】，要么它自身要在where 条件里被引用到！

2、表a

id为普通字段,上面建有索引

select * from a order by id (用不上索引)

select id from a order by id (能用上索引)

select * from a where id=XX order byid (能用上索引)

意思是说order by 要避免使用文件系统排序，要么把order by的字段出现在select 后，要么使用order by字段出现在where 条件里，要么把order by字段与where 条件字段建立复合索引！
Java培训学院始终认为你将会是最优秀的存在，看好你，加油！！！
本文版权归传智播客Java培训学院所有，欢迎转载，转载请注明作者出处。谢谢！
作者：传智播客Java培训学院
首发：http://www.itcast.cn/javaee

上一篇：Java培训实战教程之论Socket的重要性 下一篇：Java培训实战教程之SQL SERVER函数