mysql 性能优化

        MySQL性能优化就是通过合理安排资源，调整系统参数使MySQL运行更快、更节省资源。MySQL性能优化包括查询速度优化、数据库结构优化、MySQL服务器优化等。 

1，优化简介

        优化MySQL 数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。

        MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘TO的读写速度；通过优化操作系统调度策略,提高MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

        在MySQL中可以使用SHOW STATUS语句查询一些MySQL数据库的性能参数。SHOWSTATUS语句语法如下：

SHOW STATUS LIKE 'value';

其中，value是要查询的参数值，一些常用的性能参数如下：

        Connections：连接MySQL服务器的次数。

        Uptime： MySQL服务器的上线时间。

        Slow_queries：慢查询的次数。

        Com_select：查询操作的次数。

        Com_insert：插入操作的次数。

        Com_update：更新操作的次数。

        Com_delete：删除操作的次数。

        如果查询MySQL服务器的连接次数，可以执行如下语句：

SHOW STATUS LIKE 'Connections';

        如果查询MySQL服务器的慢查询次数，可以执行如下语句：

SHOW STATUS LIKE 'SLow_queries';

        查询其他参数的方法和两个参数的查询方法相同。慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

2，优化查询

        查询是数据库中最频繁的操作，提高查询速度可以有效地提高MySQL数据库的性能。

1，分析查询语句

        通过对查询语句的分析，可以了解查询语句的执行情况，找出查询语句执行的瓶颈，从而优化查询语句。MySQL中提供了EXPLAIN语句和 DESCRIBE语句，用来分析查询语句。

EXPLAIN

EXPLAIN语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT select_options

        使用EXTENED关键字，EXPLAIN语句将产生附加信息。select_options是 SELECT 语句的查询选项，包括FROM WHERE子句等。

        执行该语句，可以分析EXPLAIN后面的SELECT 语句的执行情况，并且能够分析出所查询的表的一些特征。

        【例1】使用EXPLAIN语句来分析1个查询语句，执行如下语句：

EXPLAIN SELECT * FROM fruits;

下面对查询结果进行解释。

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

        select_type：表示SELECT语句的类型。它可以是以下几种取值：

                SIMPLE 表示简单查询，其中不包括连接查询和子查询；

                PRIMARY 表示主查询,或者是最外层的查询语句；

                UNION 表示连接查询的第2个或后面的查询语句；

                DEPENDENT UNION 连接查询中的第2个或后面的SELECT语句，取决于外面的查询；

                UNION RESULT 连接查询的结果；

                SUBQUERY 子查询中的第1个SELECT语句；

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

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

        table：表示查询的表。

        type：表示表的连接类型。

下面按照从最佳类型到最差类型的顺序给出各种连接类型：

        system：该表是仅有一行的系统表。这是const连接类型的一个特例。

        const：数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const 表查询速度很快因为它们只读取一次。const 用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。

在下面的查询中，tbl_name可用于const表：

SELECT * from tb1 name WHERE primary_key=1;
SELECT * from tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;

EXPLAIN SELECT * FROM fruits WHERE f_id = 'a2';

        eq _ref：对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE 或 PRIMARYKEY时，即可使用这种类型。

        eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。

在下面的例子中，MySQL可以使用eq_ref连接来处理ref_tables：

SELECT * FROM ref_table, other_table
WHERE ref_table.key_column = other_table.column;


SELECT * FROM ref_table.other_table
WHERE ref_table.key_column_ part1=other_table.column 
AND ref_table.key_column_part2=1;

        ref：对于来自前面z的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况，或者查询中使用了索引列的左子集，即索引中左边的部分列组合。ref可以用于使用=或<=>操作符的带索引的列。

在下面的例子中，MySQL可以使用ref连接来处理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;


SELECT * FROM ref_table, other_table
WHERE ref_table.key_column=other_table.column;


SELECT * FROM ref_table, other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column part2=1;

        ref_or_null：该连接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该连接类型的优化。

在下面的例子中，MySQL可以使用ref_or_null连接来处理ref_tables：

SELECT * FROM ref table
wHERE key_column=expr OR key_column IS NULL;

        index_merge：该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

        unique_subquery：该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

         unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

        index_subquery：该连接类型类似于unique_subquery，可以替换N子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

                range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len 包含所使用索引的最长关键元素。

当使用=、○、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，类型为range。

下面介绍几种检索指定行情况：

SELECT * FROM tbl_name
WHERE key column 10;


SELECT * FROM tbl_name
WHERE key column BETWEEN 10 and 20;


SELECT * FROM tbl_name
WHERE key_column IN(10,20,30);


SELECT* FROM tbl_name
WHERE key_partl= 10 AND key_part2 IN (10,20,30);

        index：该连接类型与ALL相同，除了只扫描索引树。这通常比ALL快，因为索引文件通常比数据文件小。

        ALL：对于前面的表的任意行组合，进行完整的表扫描。如果表是第一个没标记const的表，这样不好，并且在其他情况下很差。通常可以增加更多的索引来避免使用ALL连接。

        possible_keys：指出MySQL能使用哪个索引在该表中找到行。如果该列是NULL，则没有相关的索引．在这种情况下，可以通过检查WHERE子句看它是否引用某些列或适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能。

        key表示查询实际使用到的索引，如果没有选择索引，该列的值是NULL要想强制 MySQL使用或忽视 possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见SELECT语法。

         key_len：表示MySQL选择的索引字段按字节计算的长度，如果键是NULL，则长度为NULL。注意通过key_len值可以确定MySQL将实际使用一个多列索引中的几个字段。

        ref：表示使用哪个列或常数与索引一起来查询记录。

rows：显示MySQL在表中进行查询时必须检查的行数。·Extra：表示MySQL在处理查询时的详细信息。

        DESCRIBE语句的使用方法与EXPLAIN 语句是一样的，并且分析结果也是一样的。DESCRIBE语句的语法形式如下：

DESCRIBE SELECT select_options

DESCRIBE可以缩写成DESC。

2，索引对查询速度的影响

        MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此，索引对查询的速度有着至关重要的影响。使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能

        如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数,达到提高查询速度的目的。

        【例2】下面是查询语句中不使用索引和使用索引的对比。首先，分析未使用索引时的查询情况,EXPLAIN语句执行如下：

EXPLAIN SELECT * FROM fruits WHERE f_name='apple';

        可以看到，rows列的值是16，说明“SELECT * FROM fruits WHEREf_name='apple;”这个查询语句扫描了表中的16条记录。

        然后，在fruits表的f_name字段上加上索引。执行添加索引的语句及结果如下：

CREATE INDEX index_fruits_name ON fruits(f_name);

        现在，再分析上面的查询语句。执行的EXPLAIN语句及结果如下：

EXPLAIN SELECT * FROM fruits WHERE f_name='apple';

        结果显示，rows列的值为1。这表示这个查询语句只扫描了表中的一条记录，其查询速度自然比扫描15条记录快。而且 possible_keys 和 key的值都是index_name，这说明查询时使用了index_name索引。

3，使用索引查询

        索引可以提高查询的速度。但并不是使用带有索引的字段查询时，索引都会起作用。

        使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。

1，使用LIKE 关键字的查询语句

        在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。下面将举例说明。

        【例3】查询语句中使用LIKE关键字，并且匹配的字符串中含有“%”字符，EXPLAIN语句执行如下：

EXPLAIN SELECT * FROM fruits WHERE f_name like '%X';

        已知f_name字段上有索引 index_name。第1个查询语句执行后，rows列的值为16，表示这次查询过程中扫描了表中所有的16条记录；第2个查询语句执行后，rows列的值为4，表示这次查询过程扫描了4条记录。第1个查询语句索引没有起作用，因为第1个查询语句的LIKE关键字后的字符串以“%”开头，而第2个查询语句使用了索引 index_name。

2，使用多列索引的查询语句

        MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第1个字段时，索引才会被使用。

        【例4】本例在表fruits 中 f_id、f_price字段创建多列索引，验证多列索引的使用情况。

CREATE INDEX index_fruits_id_price ON fruits(f_id, f_price);

查询：

EXPLAIN SELECT * FROM fruits WHERE f_id='12';

EXPLAIN SELECT * FROM fruits WHERE f_price=5.2;

        从第1条语句查询结果可以看出，“f_id='2'”的记录有1条。第1条语句共扫描了1条记录，并且使用了索引index_fruits_id_price。从第2条语句查询结果可以看出，rows列的值是16，说明查询语句共扫描了16条记录，并且 key列值为NULL，说明“SELECT * FROM fruits WHERE f_price=5.2;”语句并没有使用索引。因为f_price字段是多列索引的第2个字段，只有查询条件中使用了f_id字段才会使index_id _price索引起作用。

3，使用OR关键字的查询语句

        查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则,查询将不使用索引。

【例5】查询语句使用OR关键字的情况：

EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or s_id=101;

EXPLAIN SELECT * FROM fruits WHERE f_name='apple' or f_id='12';

        因为s_id字段上没有索引，第1条查询语句没有使用索引，总共查询了16条记录；第2条查询语句使用了f_name和f_id这两个索引，因为 id字段和 name字段上都有索引，查询的记录数为2条。

4，优化子查询

        MySQL 从4.1版本开始支持子查询，使用子查询可以进行SELECT 语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

        在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能会更好。连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成查询工作。

3，优化数据库结构

        一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1，将字段很多的表分解成多个表

        对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

        【例6】假设会员表存储会员登录认证信息，该表中有很多字段，如 id、姓名、密码、地址、电话、个人描述字段。其中地址、电话、个人描述等字段并不常用。可以将这些不常用字段分解出另外一个表。将这个表取名叫members_detail。表中有 member_id、address 、telephone、description等字段。其中member_id是会员编号，address字段存储地址信息，telephone字段存储电话信息，description字段存储会员个人描述信息。这样就把会员表分成两个表，分别为members表和 members_detail表。

创建这两个表的SQL语句如下：

CREATE TABLE members (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_name varchar(255) DEEAULT NULL,
  password varchar(255) DEFAULT NULL,
  last_login_time datetime DEEAULT NULL,
  last_login_ip varchar(255) DEFAULT NULL,
  PRIMARY KEY(id)
);


CREATE TABLE members_detail(
  member_id int(11) NOT NULL DEFAULT 0,
  address varchar{255) DEEAULT NULL,
  telephone varchar (16) DEFAULT NULL,
  description text
);

这两个表的结构如下：

DESC members;
DESC members_detail;

        如果需要查询会员的详细信息，可以用会员的id来查询。如果需要将会员的基本信息和详细信息同时显示，可以将members 表和 members_detail 表进行联合查询，查询语句如下：

SELECT * FROM members m
LFET JOIN members_detail md ON m.id=md.member_id;

        通过这种分解，可以提高表的查询效率。对于字段很多且有些字段使用不频繁的表，可以通过这种分解的方式来优化数据库的性能。

2，增加中间表

        对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

        首先，分析经常联合查询表中的字段。然后，使用这些字段建立一个中间表，并将原来联合查询的表的数据插入到中间表中。最后，可以使用中间表来进行查询了。

【例7】会员信息表和会员组信息表的SQL语句如下：

CREATE TABLE vip(
  id int(11) NOT NULLAUTO_INCREMENT,
  user_name varchar(255) DEFAULT NULL,
  password varchar(255) DEFAULT NULL,
  group_id INT(11) DEFAULT 0,
  PRIMARY KEY (id)
);


CREATE TABLE vip_group (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255） DEEAULT NULL,
  remark varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

查询会员信息表和会员组信息表。

DESC vip;


DESC vip_group;

        已知现在有一个模块需要经常查询带有会员组名称、会员组备注（remark)、会员用户名信息的会员信息。根据这种情况可以创建一个temp_vip表。temp_vip表中存储用户名(user_name)，会员组名称( group_name）和会员组备注(group_remark)信息。

        创建表的语句如下：

CREATETABLE temp_vip (
  id int(11) NOT NULLAUTO_INCREMENT,
  user_name varchar(255) DEFAULT NULL,
  group_name varchar (255) DEFAULT NULL,
  group_remark varchar(255) DEEAULT NULL,
  PRIMARY KEY (id)
);

        接下来，从会员信息表和会员组表中查询相关信息存储到临时表中：

INSERT INTO temp_vip(user_name, group_name, group_remark)
SELECT v.username, vg.name, vg.remark
FROM vip as v, vip_group as vg
WHERE v.group_id = vg.id;

        以后，可以直接从temp_vip表中查询会员名、会员组名称和会员组备注，而不用每次都进行联合查询。这样可以提高数据库的查询速度。

3，增加冗余字段

        设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。

        表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。例如，员工的信息存储在staff表中,部门信息存储在department 表中。通过staff 表中的department_id字段与department表建立关联关系。如果要查询一个员工所在部门的名称，必须从 staff表中查找员工所在部门的编号(department _id)，然后根据这个编号去department 表查找部门的名称。如果经常需要进行这个操作，连接查询会浪费很多时间。可以在 staff 表中增加一个冗余字段department_name，该字段用来存储员工所在部门的名称，这样就不用每次都进行连接操作了。

        冗余字段会导致一些问题。比如，冗余字段的值在一个表中被修改了，就要想办法在其他表中更新该字段。否则就会使原本一致的数据变得不一致。分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。从数据库性能来看，为了提高查询速度而增加少量的冗余大部分时候是可以接受的。是否通过增加冗余来提高数据库性能，这要根据实际需求综合分析。

4，优化插入记录的速度

        插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化。

1，MyISAM引擎的表优化

        对于MyISAM引擎的表，常见的优化方法如下：

1，禁用索引

        对于非空表，插入记录时，MySQL 会根据表的索引对插入的记录建立索引。如果插入大量数据,建立索引会降低插入记录的速度。为了解决这种情况。可以在插入记录之前禁用索引数据插入完毕后再开启索引。禁用索引的语句如下：

ALTER TABLE table_name DISABLE KEYS;

        其中 table_name是禁用索引的表的表名。重新开启索引的语句如下：

ALTER TABLE table_name ENABLE KEYS;

        对于空表批量导入数据，则不需要进行此操作，因为MyISAM 引擎的表是在导入数据之后才建立索引的。

2，禁用唯一性检查

        插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。禁用唯一性检查的语句如下：

SET UNIQUE_CHECKS = 0;

开启唯一性检查的语句如下：

SET UNIQUE_CHECKS = 1;

3，使用批量插入

        插入多条记录时，可以使用一条INSERT语句插入一条记录;也可以使用一条INSERT语句插入多条记录。使用一条INSERT语句插入一条记录的情形如下：

INSERT INTO fruits VALUES ('x1', '101', 'mongo2', '5.7');
INSERT INTO fruits VALUES ('x2', '101', 'mongo3', '5.7');
INSERT INTO fruits VALUES ('x3', '101', 'mongo4', '5.7');

使用一条INSERT语句插入多条记录的情形如下：

INSERT INTO fruits VALUES
('Xl', '101”, 'mongo2', '5.7'),
('X2', '101', 'mongo3', '5.7',
('x3', '101', 'mongo4', '5.7');

第2种情形的插入速度要比第1种情形快。

4，使用LOAD DATA INFILE批量导入

        当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOADDATA INFILE语句导入数据的速度比INSERT语句快。

2，InnoDB引擎的表优化

        对于InnoDB引擎的表，常见的优化方法如下：

1，禁用唯一性检查

        插入数据之前执行set unique_checks=0来禁止对唯一索引的检查,数据导入完成之后再运行set unique_checks=1。这个和MyISAM引擎的使用方法一样。

2，禁用外键检查

        插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。禁用外键检查的语句如下：

SET foreign_key_checks=0;

恢复对外键的检查语句如下：

SET foreign_ key_checks=1;

3，禁止自动提交

        插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。禁止自动提交的语句如下：

set autocommit=0;

恢复自动提交的语句如下：

set autocommit=1;

5，分析表、检查表和优化表

        MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布；检查表主要是检查表是否存在错误；优化表主要是消除删除或者更新造成的空间浪费。

1，分析表

        MySQL 中提供了ANALYZE TABLE语句分析表 ANALYZE TABLE语句的基本语法如下;

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]...

        LOCAL 关键字是NO_WRITE_TO_BINLOG关键字的别名，二者都是执行过程不写入二进制日志，tbl_name为分析的表的表名，可以有一个或多个。

        使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB、BDB和 MyISAM类型的表。

        【例8】使用ANALYZE TABLE来分析employee表，执行的语句及结果如下：

ANALYZE TABLE employee;

上面结果显示的信息说明如下：

        · Table：表示分析的表的名称。

        · Op：表示执行的操作。analyze表示进行分析操作.

        · Msg_type：表示信息类型，其值通常是状态（ status ) 、信息( info )、注意（note ) ,警告( warning )和错误( error)之一.

         · Msg_text：显示信息。

2，检查表

        MySQL中可以使用CHECK TABLE语句来检查表CHECK TABLE语句能够检查InnoDB和 MyISAM类型的表是否存在错误。对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。该语句的基本语法如下：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...option= {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

        其中，tbl_name是表名；option参数有5个取值，分别是QUICK、FAST、MEDIUM、EXTENDED 和 CHANGED。

各个选项的意义分别是：

        QUICK：不扫描行,不检查错误的连接。

        FAST：只检查没有被正确关闭的表。

        CHANGED：只检查上次检查后被更改的表和没有被正确关闭的表。

        MEDIUM：扫描行，以验证被删除的连接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。

        EXTENDED对每行的所有关键字进行一个全面的关键字查找这可以确保表是100%一致的，但是花的时间较长。

        option只对 MyISAM类型的表有效，对InnoDB类型的表无效。CHECK TABLE语句在执行过程中也会给表加上只读锁。

3，优化表

        MySQL中使用OPTIMIZE TABLE语句来优化表。该语句对InnoDB 和 MyISAM类型的表都有效。但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。OPTILMIZE TABLE语句的基本语法如下：

OPTIMIZE[LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ..

        LOCAL| NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志；tbl_name是表名。

        通过OPTIMIZE TABLE 语句可以消除删除和更新造成的文件碎片。OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。

        提示：一个表使用了TEXT或者BLOB 这样的数据类型，如果已经删除了表的一大部分，或者已经对含有可变长度行的表（含有VARCHAR，BLOB或TEXT列的表）进行了很多更新，则应使用OPTIMIZETABLE来重新利用未使用的空间，并整理数据文件的碎片。在多数的设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定的表运行。

4，优化 MySQL服务器

        优化 MySQL服务器主要从两个方面来优化，一方面是对硬件进行优化；另一方面是对MySQL服务的参数进行优化。这部分的内容需要较全面的知识，一般只有专业的数据库管理员才能进行这一类的优化。对于可以定制参数的操作系统，也可以针对MySQL进行操作系统优化。

1，优化服务器硬件

        服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈直接决定MySQL数据库的运行速度和效率。针对性能瓶颈，提高硬件配置，可以提高MySQL数据库的查询、更新的速度。

        (1）配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的速度比磁盘IO快得多，可以通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘IO。

        (2）配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。

        (3）合理分布磁盘IO，把磁盘IO分散在多个设备上，以减少资源竞争，提高并行操作能力。

        (4）配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。

2，优化 MySQL的参数

        通过优化MySQL 的参数可以提高资源利用率,从而达到提高MySQL服务器性能的目的。

        MySQL服务的配置参数都在my.cnf或者 my.ini文件的[MySQLd]组中。下面对几个对性能影响比较大的参数进行详细介绍。

        key_buffer_size：表示索引缓冲区的大小。索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好处理的索引（对所有读和多重写)。当然，这个值也不是越大越好，它的大小取决于内存的大小。如果这个值太大，导致操作系统频繁换页，也会降低系统性能。

        table_cache：表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。

        query_cache_size：表示查询缓冲区的大小。该参数需要和query_cache_type配合使用。当query_cache_type值是0 时，所有的查询都不使用查询缓冲区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓冲区内存。当query_cache_type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定sQL_NO_CACHE , 如 SELECT SQL_NO_CACHE * FROM tbl_name 。当query_cache_type=2时，只有在查询语句中使用SQL_CACHE 关键字，查询才会使用查询缓冲区。使用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。

        sort_buffer_size ：表示排序缓存区的大小。这个值越大，进行排序的速度越快。

        read_buffer_size ：表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节 ）。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read_buffer_size=n可以临时设置该参数的值。

        read_rnd_buffer_size ：表示为每个线程保留的缓冲区的大小，与read_buffer_size相似。但主要用于存储按特定顺序读取出来的记录。也可以用SET SESSION read_rnd_buffer_size=n来临时设置该参数的值。如果频繁进行多次连续扫描，可以增加该值。

        innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。

        max_connections：表示数据库的最大连接数。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。

i        nnodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。值为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘;值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘;值为2时表示每次提交事务时将数据写入日志文件，每隔Ⅰ秒将日志文件写入磁盘。该参数的默认值为1。默认值Ⅰ安全性最高，但是每次事务提交或事务外的指令都需要把日志写入( flush )硬盘，是比较费时的; 0值更快一点，但安全方面比较差;2值日志仍然会每秒写入到硬盘，所以即使出现故障，一般也不会丢失超过1~2秒的更新。

        back_log：表示在MySQL暂时停止回答新请求之前的短时间内，多少个请求可以被存在堆栈中。换句话说，该值表示对到来的TCP/IP连接的侦听队列的大小。只有期望在一个短时间内有很多连接，才需要增加该参数的值。操作系统在这个队列大小上也有限制。设定back_log高于操作系统的限制将是无效的。

        interactive_timeout：表示服务器在关闭连接前等待行动的秒数。

        sort_buffer_size：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2097144(2MB).thread_cache_size：表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。

        wait_timeout：表示服务器在关闭一个连接时等待行动的秒数。默认数值是28 800.合理地配置这些参数可以提高MySQL服务器的性能。除上述参数以外，还有innodb_log_buffer_size、innodb_log_file_size等参数。配置完参数以后，需要重新启动MySQL服务才会生效。

5，疑问解答

疑问1：是不是索引建立得越多越好?

        合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段才建立索引。

疑问2∶为什么查询语句中的索引没有起作用?

        在一些情况下，查询语句中使用了带有索引的字段。但索引并没有起作用。例如，在WHERE条件的LIKE关键字匹配的字符串以“%”开头，这种情况下索引不会起作用。又如，WHERE条件中使用OR关键字连接查询条件，如果有1个字段没有使用索引，那么其他的索引也不会起作用。如果使用多列索引，但没有使用多列索引中的第1个字段，那么多列索引也不会起作用。

疑问3∶如何使用查询缓冲区?

        查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句比较多、更新语句比较少的情况。默认情况下查询缓冲区的大小为0，也就是不可用。可以修改query_cache_size 以调整查询缓冲区大小;修改 query_cache_type 以调整查询缓冲区的类型。在 my.ini中修改query_cache_size和 query_cache_type的值如下所示：

query_cache_size=512M
query_cache_type=1

        query_cache_type=1表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE 关键字时，才不会使用查询缓冲区。可以使用FLUSH QUERY CACHE语句来刷新缓冲区，清理查询缓冲区中的碎片。

总结：

        大概介绍下性能优化的方向。详细的，在mysql进阶那边进行展开介绍。

        上一篇： 《mysql 触发器》

        下一篇： 《mysql 日志》