我的MYSQL学习心得（十六） 优化

一步一步走来已经写到了第十六篇了~

 

这一篇主要介绍MYSQL的优化，优化MYSQL数据库是DBA和开发人员的必备技能

MYSQL优化一方面是找出系统瓶颈，提高MYSQL数据库整体性能；另一方面需要合理的结构设计和参数调整，以提高

用户操作响应的速度；同时还有尽可能节省系统资源，以便系统可以提供更大负荷的服务

 

如果大家看过我写的两篇文章，那么学习MYSQL的索引就不会太难，因为是相通的

SQLSERVER聚集索引与非聚集索引的再次研究（上）

SQLSERVER聚集索引与非聚集索引的再次研究（下）

 

其实MYSQL也有SQLSERVER堆表的概念

myisam允许没有任何索引和主键的表存在，个人觉得没有主键的myisam表都属于堆表，因为MYSQL不支持非主键的聚集索引。

innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)

详细参考：MyISAM vs InnoDB：MySQL存储引擎详解

不过《MyISAM vs InnoDB：MySQL存储引擎详解》文章也有一点错误，意向共享锁就是表锁，其实是不对的

 

1、优化简介

mysql优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。

例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调度策略，提高mysql在高负荷情况下

的负载能力；优化表结构、索引、查询语句等使查询响应更快

 

在mysql中，可以使用show status语句查询一些mysql的性能参数

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 [EXTENDED] SELECT SELECT_OPTION

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

 

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

 

使用EXPLAIN语句来分析1个查询语句

USE TEST;
EXPLAIN EXTENDED SELECT * FROM PERSON;

下面对结果进行解释

· id

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

 

· select_type

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

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

PRIMARY：表示主查询，或者是最外层的查询语句（多表连接的时候）

UNION：表示连接查询的第二个或后面的查询语句

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

UNION RESULT：UNION连接查询的结果

SUBQUERY：子查询中的第一个SELECT语句

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

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

 

· table

表示查询的表

 

· type

表示表的联接类型

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

（1）system

表仅有一行(=系统表)。这是const联接类型的一个特例。

（2）const

表最多只有一个匹配行，它将在查询开始时被读取。余下的查询优化中被作为常量对待。const表查询速度很快，因为它们只读取一次。

const用于常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。

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

SELECT * from tbl_name WHERE primary_key=1;SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2;

 

（3）eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。

它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY时。

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;

 

（4）ref

对于每个来自于前面的表的任意行组合，将从该表中读取所有匹配的行。

如果联接只使用索引键的最左边的前缀，或如果索引键不是UNIQUE或PRIMARY KEY，则使用ref。

如果使用的键仅仅匹配少量行，该联接类型是不错的。

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;

 

（5）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;

 

（6） index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了所用到的索引的清单，key_len列包含了所用到的索引的最长长度。

 

（7） unique_subquery

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

value IN (SELECT primary_key FROM single_table WHERE some_expr)

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

 

（8） index_subquery

该联接类型类似于unique_subquery，不过索引类型不需要是唯一索引，可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

 

（9） range

只检索给定范围的行，使用一个索引来检索行数据。key列显示使用了哪个索引，key_len显示所使用索引的长度。

在该类型中ref列为NULL。

当使用=、<>、>、>=、<、<=、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_part1= 10 AND key_part2 IN (10,20,30);

 

（10）  index

该联接类型与ALL相同，除了扫描索引树。其他情况都比ALL快，因为索引文件通常比数据文件小。

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

 

（11）   ALL

对于每个来自于先前的表的行组合，进行完整的表扫描。

如果第一个表没标记为const，这样执行计划就不会很好。

通常可以增加更多的索引来摆脱ALL，使得行能基于前面的表中的常数值或列值被检索出。

 

possible_keys

possible_keys列指出MySQL能供给使用的索引键有哪些。注意，该列完全独立于EXPLAIN输出所示的表的次序。

这意味着在possible_keys中的某些索引键实际上不能按生成的表次序使用。

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

如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

如果要查询一张表有什么索引，可以使用

SHOW INDEX FROM tbl_name

 

key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，那么可能列的值是NULL。

要想强制MySQL使用或忽略possible_keys列中的索引，在查询中可以使用

FORCE INDEX -- 强逼使用某个索引

USE INDEX --使用某个索引

IGNORE INDEX -- 忽略某个索引

对于MyISAM引擎和BDB引擎的表，运行 ANALYZE TABLE 可以帮助优化器选择更好的索引。

对于MyISAM表，可以使用myisamchk --analyze。

 

key_len

key_len列显示MySQL决定使用的索引键的长度（按字节计算）。如果键是NULL，则长度为NULL。

注意通过key_len值我们可以确定MySQL将实际使用一个多索引键索引的几个字段。

 

ref

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

 

rows

rows列显示MySQL预估执行查询时必须要检索的行数。

 

Extra

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

Distinct

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

 

Not exists

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

下面是一个可以这样优化的查询类型的例子：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。

如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。

 

range checked for each record (index map: #)

MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。

对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来获取行。

这并不很快，但比执行没有索引的联接要快得多。

可以参考一下这篇文章：一个用户SQL慢查询分析，原因及优化

里面就提到了range checked for each record 

 

Using filesort

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。

通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。

然后关键字被排序，并按排序顺序检索行

如果是order by操作就会用到这个Using filesort，当然filesort不是指使用文件来排序，大家不要误会了。。。

 

Using index

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

 

Using temporary

为了解决查询，MySQL需要创建一个临时表来容纳结果。

典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

一般用到临时表都会看到 Using temporary

 

Using where

WHERE子句用于限制哪一个行匹配下一个表或发送到客户端。

除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。

 

Using index for group-by

类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，

而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

 

DESCIBE语句的使用方法与EXPLAIN语句是一样的，并且分享结果也是一样的DESCIBE语句的语法如下

DESCRIBE SELECT select_options

DESCIBE可以缩写成DESC

 

 

（2）索引对查询速度的影响

mysql中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询速度

因此索引对查询速度有着至关重要的影响。

 

如果查询没有索引，查询语句将扫描表中所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，

查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。

 

下面是查询语句中不使用索引和使用索引的对比，首先分析未使用索引的查询情况，EXPLAIN语句执行如下

EXPLAIN SELECT `ID`,`name` FROM `test`.`emp` WHERE `name` ='nihao'

可以看到，rows列的值是3说“SELECT `ID`,`name` FROM `test`.`emp` WHERE `name` ='nihao'” 语句扫描了表中的3条记录

 

然后在emp表加上索引

CREATE INDEX ix_emp_name ON emp(name)

 

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

结果显示，rows列的值为1。这表示这个查询语句只扫描了表中的一条记录，其他查询速度自然比扫描3条记录快。

而且possible_keys 和key的值都是ix_emp_name ，这说明查询时使用了ix_emp_name 索引

如果表中记录有100条、1000条、10000条优势就显现出来了

 

 

 （3）使用索引查询

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

下面的几种情况跟跟SQLSERVER一样，有可能用不到索引

（1）使用like关键字的查询语句

使用like关键字进行查询的时候，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引

才会起作用

 

使用like关键字，并且匹配字符串中含有“%”字符，EXPLAIN语句如下

USE test;
EXPLAIN SELECT * FROM `test`.`emp` WHERE `name` LIKE '%x';

 

USE test;
EXPLAIN SELECT * FROM `test`.`emp` WHERE `name` LIKE 'x%';

name上有索引ix_emp_name

第一个查询type为ALL，表示要全表扫描

第二个查询TYPE为index，表示会扫描索引

 

like 关键字是否能利用上索引跟SQLSERVER是一样的

我之前写过一篇文章：like语句百分号前置会使用到索引吗？

 

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

mysql可以为多个字段创建索引。一个索引可以包括16个字段（跟SQLSERVER一样）对于多列索引，只有查询条件中使用了

这些字段中的第一个字段时，索引才会被使用，这个字段叫：前导索引或前导列

 

在表person中name，age字段创建多列索引，验证多列索引的情况

CREATE INDEX ix_person_name_age ON `person` (name,age)

EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `Name` ='suse'

EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `age` =12

 

从第一条查询看出，WHERE `Name` ='suse'的记录有一条，扫描了一条记录并且使用了ix_person_name_age 索引

从第二条记录可以看出，rows列的值为4，说明共扫描了4条记录，并且key列值为NULL，说明EXPLAIN SELECT ID,Name,Age,job FROM `person` WHERE `age` =12

语句并没有使用索引。因为age字段是多列索引的第二个字段，只有查询条件中使用了name字段才会使用ix_person_name_age 索引

 

这个跟SQLSERVER是一样的，详细请看：SQLSERVER聚集索引与非聚集索引的再次研究（下）

 

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

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

 

查询语句使用OR关键字的情况

我们再创建一个索引

CREATE INDEX ix_person_age ON `person` (age)

 

EXPLAIN SELECT Name,Age FROM `person` WHERE `Name` ='SUSE' OR `job`='SPORTMAN'

EXPLAIN SELECT Name,Age FROM `person` WHERE `AGE` =2 OR `Name` ='SUSE' 

大家要注意，这里跟刚才不一样，这次我们select的字段只有name和age，而不是select出全部字段

因为并没有在job这个字段上建立索引，所以第一个查询使用的是全表扫描

第二个查询因为name字段和age字段都有索引，那么mysql可以利用这两个索引的其中之一，这里是ix_person_name_age索引来查找记录

利用索引来查找记录会快很多

 

（4）优化子查询

mysql从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件

子查询可以一次性完成很多逻辑需要多个步骤才能完成的SQL操作。子查询虽然使查询语句灵活，但是执行效率不高。

执行子查询时，mysql需要为内层查询语句结果建立一个临时表。然后外层查询语句从临时表中查询记录

查询完毕后，再撤销临时表。因此，子查询的速度会受到一定影响，如果查询的数据量特别大，这种影响就会更大。

 

在mysql中，可以使用连接（join）查询来代替子查询。连接查询不需要建立临时表，其速度比子查询快，如果查询中使用索引的话，性能会更好。

所以很多网上的文章都说尽量使用join来代替子查询，虽然网上也说mysql5.7对于子查询有很大的改进，但是如果不是使用mysql5.7还是需要注意的

 

如果系统中join语句特别多还需要注意修改my.ini或my.cnf文件中的join_buffer_size大小，预防性能问题

---

优化数据库结构

 

一个好的数据库设计方案对于数据库的性能常常起到事半功倍的效果。

数据库结构的设计需要考虑数据冗余、查询和更新速度、字段的数据类型是否合理等多方面

 

（1）将字段很多的表拆分成多个表

有时候有些字段使用频率很低或者字段的数据类型比较大，那么可以考虑垂直拆分的方法，把不常用的字段和大字段拆分出去

 

（2）增加中间表

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

然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

 

（3）增加冗余字段

设计数据库表时应尽量遵循范式理论，尽可能减少冗余字段，但是现今存储硬件越来越便宜，有时候查询数据的时候需要join多个表

这样在高峰期间会影响查询的效率，我们需要反范式而为之，增加一些必要的冗余字段，以空间换时间

需要这样做会增加开发的工作量和维护量，但是如果能换来可观的性能提升，这样做也是值得的

 

（4）优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。

根据实际情况，可以分别进行优化

 

对于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 INTO emp(id,name) VALUES (1,'suse'); INSERT INTO emp(id,name) VALUES (2,'lily'); INSERT INTO emp(id,name) VALUES (3,'tom');

 

第二种情况

INSERT INTO emp(id,name) VALUES (1,'suse'),(2,'lily'),(3,'tom')

第二种情况要比第一种情况要快

 

4、使用LOAD DATA INFILE批量导入

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

 

 

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

1、禁用唯一性检查

插入数据时，mysql会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。

为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕之后再开启

禁用唯一性检查的语句如下：

SET UNIQUE_CHECKS=0;

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

SET UNIQUE_CHECKS=1;

 

2、禁用外键约束

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

SET FOREIGN_KEY_CHECKS=0;

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

SET FOREIGN_KEY_CHECKS=1;

 

3、禁止自动提交

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

或显式指定事务

USE test;

START TRANSACTION; INSERT INTO emp(name) VALUES('ming'); INSERT INTO emp(name) VALUES('lily'); commit;

 

（5）分析表、检查表、优化表、修复表和CHECKSUM表

mysql提供了分析表、检查表和优化表的语句

分析表主要是分析关键字的分布；

检查表主要是检查表是否存在错误；

优化表主要是消除删除或者更新造成的空间浪费

修复表主要对myisam表文件进行修复

CHECKSUM表主要对表数据传输前和传输后进行比较

 

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类型的表

 

使用ANALYZE TABLE 来分析emp表，执行语句如下：

ANALYZE TABLE emp;

上面结果显示说明

table：表示分析的表名

op：表示执行的操作，analyze表示进行分析操作

msg_type：表示信息类型其值通常是状态（status）、信息（info）、注意（note）、警告（warning）和错误（error）之一

msg_text：显示信息

实际上分析表跟SQLSERVER里的更新统计信息是差不多的

主要就是为了索引的基数更加准确，从而使查询优化器能够更加准确的预估行数

 

emp表的记录行数是18

分析表之后，Cardinality 基数更加准确了

 

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：只检查没有被正确关闭的表

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

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

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

 

option只对myisam表有效，对innodb表无效。check table语句在执行过程中也会给表加上只读锁。

 

3、优化表

mysql中使用OPTIMIZE TABLE语句来优化表。该语句对INNODB和MYISAM表都有效。但是，OPTIMIZE TABLE语句只能优化表中的

VARCHAR、BLOB、TEXT类型的字段

OPTIMIZE 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列的表）

进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。在多数设置中，根本不需要运行OPTIMIZE TABLE。

即使对可变长度的行进行了大量更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定表进行OPTIMIZE TABLE

 

OPTIMIZE TABLE语句类似于SQLSERVER的重建索引和收缩数据文件的功能

 

4、修复表

mysql中使用Repair Table来修复myisam表，只对MyISAM和ARCHIVE类型的表有效。

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE TBL_NAME [,tbl_name]...[option]... option={QUICK|EXTENDED|USE_FRM}

选项的意思分别是：

QUICK：最快的选项，只修复索引树。
EXTENDED：最慢的选项，需要逐行重建索引。
USE_FRM：只有当MYI文件丢失时才使用这个选项，全面重建整个索引。

与Analyze Table一样，Repair Table也可以使用local来取消写入binlog。

 

 

5、Checksum 表

数据在传输时，可能会发生变化，也有可能因为其它原因损坏，为了保证数据的一致，我们可以计算checksum（校验值）。

使用MyISAM引擎的表会把checksum存储起来，称为live checksum，当数据发生变化时，checksum会相应变化。

语法如下：

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

quick：表示返回存储的checksum值

extended：表示重新计算checksum

如果没有指定选项，则默认使用extended。

 

Checksum 表主要用来对比在传输表数据之前和表数据之后，表的数据是否发生了变化，例如插入了数据或者删除了数据，或者有数据损坏

CHECKSUM值都会改变。

---

优化MYSQL服务器

水电费优化mysql服务器主要从两个方面入手，一方面是对硬件进行优化；另一方面是对mysql服务器的参数进行优化

 

1、优化服务器硬件

服务器的硬件性能直接决定着MYSQL数据库的性能。硬件的性能瓶颈直接决定MYSQL数据库的运行速度和效率。

优化服务器硬件的几种方法

（1）配置较大的内存。足够大的内存，是提高mysql数据库性能之一。内存速度比磁盘I/O快得多，可以通过增加系统缓冲区容量，使数据库

在内存停留时间更长，以减少磁盘I/O

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

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

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

 

 

2、优化MYSQL的参数

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

MYSQL服务器的配置参数都在my.cnf或者my.ini文件的[mysqld]组中。

下面对几个对性能影响较大的参数进行介绍

我们先看一下与网络连接的性能配置项及对性能的影响。
● max_conecctions：整个 MySQL 允许的最大连接数；
这个参数主要影响的是整个 MySQL 应用的并发处理能力，当系统中实际需要的连接量大于
max_conecctions 的情况下，由于 MySQL 的设置限制，那么应用中必然会产生连接请求的等待，
从而限制了相应的并发量。所以一般来说，只要 MySQL 主机性能允许，都是将该参数设置的尽
可能大一点。一般来说 500 到 800 左右是一个比较合适的参考值
● max_user_connections：每个用户允许的最大连接数；
上面的参数是限制了整个 MySQL 的连接数，而 max_user_connections 则是针对于单个用户的连
接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供 MySQL 数据存储服
务，或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外，其他方面和
max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数，对于普通的应用来
说，完全没有做太多的限制，可以尽量放开一些。
● net_buffer_length：网络包传输中，传输消息之前的 net buffer 初始化大小；
这个参数主要可能影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大
小，所以造成的影响主要是当我们的每次消息都很大的时候 MySQL 总是需要多次申请扩展该缓
冲区大小。系统默认大小为 16KB，一般来说可以满足大多数场景，当然如果我们的查询都是非
常小，每次网络传输量都很少，而且系统内存又比较紧缺的情况下，也可以适当将该值降低到
8KB。
● max_allowed_packet：在网络传输中，一次传消息输量的最大值；
这个参数与 net_buffer_length 相对应，只不过是 net buffer 的最大值。当我们的消息传输量
大于 net_buffer_length 的设置时，MySQL 会自动增大 net buffer 的大小，直到缓冲区大小达
到 max_allowed_packet 所设置的值。系统默认值为 1MB，最大值是 1GB，必须设定为 1024 的倍
数，单位为字节。
● back_log：在 MySQL 的连接请求等待队列中允许存放的最大连接请求数。
连接请求等待队列，实际上是指当某一时刻客户端的连接请求数量过大的时候，MySQL 主线程没
办法及时给每一个新的连接请求分配（或者创建）连接线程的时候，还没有分配到连接线程的
所有请求将存放在一个等待队列中，这个队列就是 MySQL 的连接请求队列。当我们的系统存在
瞬时的大量连接请求的时候，则应该注意 back_log 参数的设置。系统默认值为 50，最大可以设
置为 65535。当我们增大 back_log 的设置的时候，同时还需要主义 OS 级别对网络监听队列的限
制，因为如果 OS 的网络监听设置小于 MySQL 的 back_log 设置的时候，我们加大“back_log”设
置是没有意义的。
上面介绍了网络连接交互相关的主要优化设置，下面我们再来看看与每一个客户端连接想对应的连
接线程。
在 MySQL 中，为了尽可提高客户端请求创建连接这个过程的性能，实现了一个 Thread Cache 池，将
空闲的连接线程存放在其中，而不是完成请求后就销毁。这样，当有新的连接请求的时候，MySQL 首先会
检查 Thread Cache 池中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，
才创建新的连接线程。在 MySQL 中与连接线程相关的系统参数及状态变量说明如下：
● thread_cache_size：Thread Cache 池中应该存放的连接线程数。
当系统最初启动的时候，并不会马上就创建 thread_cache_size 所设置数目的连接线程存放在
Thread Cache 池中，而是随着连接线程的创建及使用，慢慢的将用完的连接线程存入其中。当
存放的连接线程达到 thread_cache_size 值之后，MySQL 就不会再续保存用完的连接线程了。
如果我们的应用程序使用的短连接，Thread Cache 池的功效是最明显的。因为在短连接的数据
库应用中，数据库连接的创建和销毁是非常频繁的，如果每次都需要让 MySQL 新建和销毁相应
的连接线程，那么这个资源消耗实际上是非常大的，而当我们使用了 Thread Cache 之后，由于
连接线程大部分都是在创建好了等待取用的状态，既不需要每次都重新创建，又不需要在使用
完 之 后 销 毁 ， 所 以 可 以 节 省 下 大 量 的 系 统 资 源 。 所 以 在 短 连 接 的 应 用 系 统 中 ，
thread_cache_size 的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请求
数。
而如果我们使用的是长连接的时候，Thread Cache 的功效可能并没有使用短连接那样的大，但
也并不是完全没有价值。因为应用程序即使是使用了长连接，也很难保证他们所管理的所有连
接都能处于很稳定的状态，仍然会有不少连接关闭和新建的操作出现。在有些并发量较高，应
用服务器数量较大的系统中，每分钟十來次的连接创建与关闭的操作是很常见的。而且如果应
用服务器的连接池管理不是太好，容易产生连接池抖动的话，所产生的连接创建和销毁操作将
会更多。所以即使是在使用长连接的应用环境中，Thread Cache 机制的利用仍然是对性能大有
帮助的。只不过在长连接的环境中我们不需要将 thread_cache_size 参数设置太大，一般来说
可能 50 到 100 之间应该就可以了。
● thread_stack：每个连接线程被创建的时候，MySQL 给他分配的内存大小。
当 MySQL 创建一个新的连接线程的时候，是需要给他分配一定大小的内存堆栈空间，以便存放
客户端的请求 Query 以及自身的各种状态和处理信息。不过一般来说如果不是对 MySQL 的连接线
程处理机制十分熟悉的话，不应该轻易调整该参数的大小，使用系统的默认值（192KB）基本上
可以所有的普通应用环境。如果该值设置太小，会影响 MySQL 连接线程能够处理客户端请求的
Query 内容的大小，以及用户创建的 Procedures 和 Functions 等


计算出系统新建连接连接的 Thread
Cache 命中率，也就是通过 Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率，如
下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100% 我们可以通过上面的这个运算公式计算一下上面环境中的 Thread Cache 命中率：Thread_Cache_Hit = (127 - 12) / 127 * 100% = 90.55% 一般来说，当系统稳定运行一段时间之后，我们的 Thread Cache 命中率应该保持在 90%左右甚至更
高的比率才算正常。可以看出上面环境中的 Thread Cache 命中比率基本还算是正常的。 Table Cache 相关的优化
我们先来看一下 MySQL 打开表的相关机制。由于多线程的实现机制，为了尽可能的提高性能，在
MySQL 中每个线程都是独立的打开自己需要的表的文件描述符，而不是通过共享已经打开的表的文件描述
符的机制来实现。当然，针对于不同的存储引擎可能有不同的处理方式。如 MyISAM 表，每一个客户端线
程打开任何一个 MyISAM 表的数据文件都需要打开一个文件描述符，但如果是索引文件，则可以多个线程
共享同一个索引文件的描述符。对于 Innodb 的存储引擎，如果我们使用的是共享表空间来存储数据，那
么我们需要打开的文件描述符就比较少，而如果我们使用的是独享表空间方式来存储数据，则同样，由
于存储表数据的数据文件较多，则同样会打开很多的表文件描述符。除了数据库的实际表或者索引打开
以外，临时文件同样也需要使用文件描述符，同样会占用系统中 open_files_limit 的设置限额。
为了解决打开表文件描述符太过频繁的问题，MySQL 在系统中实现了一个 Table Cache 的机制，和前
面介绍的 Thread Cache 机制有点类似，主要就是 Cache 打开的所有表文件的描述符，当有新的请求的时
候不需要再重新打开，使用结束的时候也不用立即关闭。通过这样的方式来减少因为频繁打开关闭文件
描述符所带来的资源消耗。我们先看一看 Table Cache 相关的系统参数及状态变量。
在 MySQL 中我们通过 table_cache（从 MySQL5.1.3 开始改为 table_open_cache），来设置系统中为
我们 Cache 的打开表文件描述符的数量。通过 MySQL 官方手册中的介绍，我们设置 table_cache 大小的时
候应该通过 max_connections 参数计算得来，公式如下：
table_cache = max_connections * N；
其中 N 代表单个 Query 语句中所包含的最多 Table 的数量。但是我个人理解这样的计算其实并不是太
准确，分析如下：
首先，max_connections 是系统同时可以接受的最大连接数，但是这些连接并不一定都是 active 状
态的，也就是说可能里面有不少连接都是处于 Sleep 状态。而处于 Sleep 状态的连接是不可能打开任何 Table 的。
其次，这个 N 为执行 Query 中包含最多的 Table 的 Query 所包含的 Table 的个数也并不是太合适，因
为我们不能忽略索引文件的打开。虽然索引文件在各个连接线程之间是可以共享打开的连接描述符的，
但总还是需要的。而且，如果我 Query 中的每个表的访问都是通过现通过索引定位检索的，甚至可能还
是通过多个索引，那么该 Query 的执行所需要打开的文件描述符就更多了，可能是 N 的两倍甚至三倍。
最后，这个计算的公式只能计算出我们同一时刻需要打开的描述符的最大数量，而 table_cache 的
设置也不一定非得根据这个极限值来设定，因为 table_cache 所设定的只是 Cache 打开的描述符的数量的
大小，而不是最多能够打开的量的大小。


join_buffer_size ：当我们的 Join 是 ALL ， index ， rang 或者 index_merge 的时候使用的
Buffer；
实际上这种 Join 被称为 Full Join。实际上参与 Join 的每一个表都需要一个 Join Buffer，所以在 Join 出现的时候，至少是两个。Join Buffer 的设置在 MySQL 5.1.23 版本之前最大为 4GB，但是从 5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4BG 的限制。系统默认是 128KB。
● sort_buffer_size：系统中对数据进行排序的时候使用的 Buffer；
Sort Buffer 同样是针对单个 Thread 的，所以当多个 Thread 同时进行排序的时候，系统中就会出现
多个 Sort Buffer。一般我们可以通过增大 Sort Buffer 的大小来提高 ORDER BY 或者是 GROUP BY 的处理性能。系统默认大小为 2MB，最大限制和 Join Buffer 一样，在 MySQL 5.1.23 版本之前最大
为 4GB，从 5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4GB 的限制。
如果应用系统中很少有 Join 语句出现，则可以不用太在乎 join_buffer_size 参数的大小设置，但是
如果 Join 语句不是很少的话，个人建议可以适当增大 join_buffer_size 的设置到 1MB 左右，如果内存充
足甚至可以设置为 2MB。对于 sort_buffer_size 参数来说，一般设置为 2MB 到 4MB 之间可以满足大多数
应用的需求。当然，如果应用系统中的排序都比较大，内存充足且并发量不是特别的大的时候，也可以
继续增大 sort_buffer_size 的设置。在这两个 Buffer 设置的时候，最需要注意的就是不要忘记是每个
Thread 都会创建自己独立的 Buffer，而不是整个系统共享的 Buffer，不要因为设置过大而造成系统内存
不足。

配置完参数之后，需要重启MYSQL服务才能生效

---

如何使用查询缓冲区

查询缓冲区可以提高查询的速度，但是这种方式只适合查询语句多、更新较少的情况。默认情况下查询缓冲区的大小为0，也就是不可用

可以修改query_cache_size以调整查询缓冲区大小；修改 query_cache_type以调整查询缓冲区的类型。

在my.ini中修改query_cache_size和query_cache_type的值如下所示

[mysqld] query_cache_size=512M
query_cache_type=1

query_cache_type=1表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE关键字时，才不会使用查询缓冲区。

可以使用FLUSH QUERY CACHE语句来刷新缓冲区，清理查询缓冲区中的碎片

 

注意：开启查询缓冲区是有风险的，如果命中率不高，或者更新修改语句较多，都会使查询缓冲区失效，从而使命中率更加低

建议使用memcached等软件来做二级缓存，除非系统中修改语句较少，命中率较高，这样才会看到明显的性能提升

 

---

总结

本文阐述了MYSQL的性能优化面的内容，虽然网上对于MYSQL优化的资料很多