Mysql数据库调优

 

第二篇 优化篇

 

第1章 SQL优化

 

1.1 优化SQL的一般步骤

1.1.1 通过show status和应用特点了解各种SQL的执行频率

 

通过SHOW STATUS可以提供服务器状态信息，也可以使用mysqladmin extended-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　select查询返回的行数；

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 慢查询的次数

1.1.2 定位执行效率较低的SQL语句：

 

可以通过以下两种方式定位执行效率较低的SQL语句：

1.   可以通过慢查询日志定位那些执行效率较低的sql语句，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。可以链接到管理维护中的相关章节。

2.   慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。

1.1.3 通过EXPLAIN分析低效SQL的执行计划：

 

通过以上步骤查询到效率低的SQL后，我们可以通过explain或者desc 获取MySQL如何执行SELECT语句的信息，包括select语句执行过程表如何连接和连接的次序。

explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

 

mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006;

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

| select_type   | table | type  | possible_keys| key            | key_len   | rows  | Extra     |

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

| SIMPLE    | b     | index | PRIMARY   | PRIMARY   | 4     |    1  | Using index   |

| SIMPLE    | a     | ALL   | NULL      | NULL      | NULL  |   12  | Using where   |

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

2 rows in set (0.02 sec)

 

select_type：   select 类型

table：         输出结果集的表

type：          表示表的连接类型

当表中仅有一行是type的值为system是最佳的连接类型；

当select操作中使用索引进行表连接时type的值为ref；

当select的表连接没有使用索引时，经常会看到type的值为ALL，表示对该表进行了全表扫描，这时需要考虑通过创建索引来提高表连接的效率。

possible_keys： 表示查询时,可以使用的索引列.

key：           表示使用的索引

key_len：       索引长度

rows：          扫描范围

Extra：         执行情况的说明和描述

 

1.1.4 确定问题，并采取相应的优化措施：

 

经过以上步骤，基本可以确认问题出现的原因，可以根据情况采取相应的措施，进行优化提高执行的效率。

例如上面的例子，我们确认是对a表的全表扫描导致效率的不理想，我们对a表的year字段创建了索引，查询需要扫描的行数明显较少。

 

mysql> explain select sum(moneys) from sales a,companys b where a.company_id = b.id and a.year = 2006;

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

| select_type   | table | type  | possible_keys| key            | key_len   | rows  | Extra     |

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

| SIMPLE    | b     | index | PRIMARY   | PRIMARY   | 4     |    1  | Using index   |

| SIMPLE    | a     | ref       | year      | year      | 4     |    3  | Using where   |

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

2 rows in set (0.02 sec)

1.2 索引问题

1.2.1 索引的存储分类

 

myisam表的数据文件和索引文件是自动分开的；innodb的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。

创建索引语法如下：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[USING index_type]

ON tbl_name (index_col_name,...)

index_col_name:

col_name [(length)] [ASC | DESC]

 

索引的存储类型目前只有两种（btree和hash），具体和表的模式相关：

myisam     btree

innodb      btree

memory/heap hash，btree

mysql目前不支持函数索引，只能对列的前一部分（length）进行索引，例：

create index ind_test on table1(name(5))，

对于char和varchar列，使用前缀索引将大大节省空间。

10.2.2 MySQL如何使用索引

 

索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT操作性能的最佳途径。

查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。

 

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

1.   如果mysql估计使用索引比全表扫描更慢，则不使用索引。例如：如果key_part1均匀分布在1和100之间，下列查询中使用索引就不是很好：

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

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

3.   如果不是索引列的第一部分；

4.   如果like是以％开始；

5.   对where后边条件为字符串的一定要加引号，字符串如果为数字mysql会自动转为字符串，但是不使用索引。

10.2.3 查看索引使用情况

 

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

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。

 

语法：

mysql> show status like 'Handler_read%';

1.3 两个简单实用的优化方法

1.3.1 定期分析表：

 

ANALYZE TABLE

语法：

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

本语句用于分析和存储表的关键字分布。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM, BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当。

CHECK TABLE

语法：

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。

CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。

CHECKSUM TABLE

语法：

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

报告一个表校验和。

1.3.2 使用optimize table：

 

OPTIMIZE TABLE

语法：

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

如果已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

1.4 常用SQL的优化

1.4.1 大批量插入数据：

 

1.   对于Myisam类型的表，可以通过以下方式快速的导入大量的数据。

ALTER TABLE tblname DISABLE KEYS;

loading the data

ALTER TABLE tblname ENABLE KEYS;

这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

 

2.   而对于Innodb类型的表，这种方式并不能提高导入数据的效率。对于Innodb类型的表，我们有以下几种方式可以提高导入的效率：

a.   因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。

b.   在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

c.   如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

1.4.2 优化insert语句：

 

3.   如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

Insert into test values(1,2),(1,3),(1,4)…

4.   如果你从不同客户插入很多行，能通过使用INSERT DELAYED语句得到更高的速度。Delayed的含义是让insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完后才进行插入；

5.   将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；

6.   如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对myisam表使用；

7.   当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍；

8.   根据应用情况使用replace语句代替insert；

9.   根据应用情况使用ignore关键字忽略重复记录。

1.4.3 优化group by语句：

 

默认情况下，MySQL排序所有GROUP BY col1，col2，....。查询的方法如同在查询中指定ORDER BY  col1，col2，...。如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。

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

例如：

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

1.4.4 优化order by语句：

 

在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

     例如：下列sql可以使用索引。

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

 

但是以下情况不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

--order by的字段混合ASC和DESC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

--用于查询行的关键字与ORDER BY中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2；

--对不同的关键字使用ORDER BY：

1.4.5 优化join语句：

 

Mysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN).. 替代。

假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

 

如果使用连接(JOIN).. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT * FROM customerinfo

LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID

WHERE salesinfo.CustomerID IS NULL 

 

连接(JOIN).. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

1.4.6 mysql如何优化or条件：

 

对于or子句，如果要利用索引，则or之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

1.4.7 查询优先还是更新（insert、update、delete）优先：

 

MySQL还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。

我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。

下面我们提到的改变调度策略的方法主要是针对Myisam存储引擎的，对于Innodb存储引擎，语句的执行是由获得行锁的顺序决定的。

 

MySQL的默认的调度策略可用总结如下：

1.   写入操作优先于读取操作。

2.   对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。

3.   对某张数据表的多个读取操作可以同时地进行。

MySQL提供了几个语句调节符，允许你修改它的调度策略：

1.   LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。

2.   HIGH_PRIORITY关键字应用于SELECT和INSERT语句。

3.   DELAYED关键字应用于INSERT和REPLACE语句。

 

如果写入操作是一个LOW_PRIORITY（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。这种调度修改可能存在LOW_PRIORITY写入操作永远被阻塞的情况。

SELECT查询的HIGH_PRIORITY（高优先级）关键字也类似。它允许SELECT插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。另外一种影响是，高优先级的SELECT在正常的SELECT语句之前执行，因为这些语句会被写入操作阻塞。

如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么请使用--low-priority-updates选项来启动服务器。通过使用INSERT HIGH_PRIORITY来把INSERT语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。

1.4.8 使用SQL提示：

 

SELECT SQL_BUFFER_RESULTS ...

将强制MySQL生成一个临时结果集。只要所有临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。 

当处理一个会让客户端耗费点时间才能处理的大结果集时，可以考虑使用SQL_BUFFER_RESULT 提示字。这样可以告诉MySQL将结果集保存在一个临时表中，这样可以尽早的释放各种锁。

l  USE INDEX

在你查询语句中表名的后面，添加USE INDEX 来提供你希望MySQ去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

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

l  IGNORE INDEX

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

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

l  FORCE INDEX

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

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

1.5 其他优化措施

1.         使用持久的连接数据库以避免连接开销。

2.         经常检查所有查询确实使用了必要的索引。

3.         避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题。

4.         对于没有删除的行操作的MyISAM表，插入操作和查询操作可以并行进行，因为没有删除操作的表查询期间不会阻塞插入操作．对于确实需要执行删除操作的表，尽量在空闲时间进行批量删除操作，避免阻塞其他操作。

5.         充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。

6.         对经常访问的可以重构的数据使用内存表，可以显著提高访问的效率。

7.         通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份期间对应用的影响，可以在复制服务器上执行备份操作。

8.         表的字段尽量不使用自增长变量，在高并发情况下该字段的自增可能对效率有比较大的影响，推荐通过应用来实现字段的自增长。

第2章 优化数据库对象

 

2.1 优化表的数据类型

表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费存储也浪费内存。

我们可以使用PROCEDURE ANALYSE()对当前已有应用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。

语法：

SELECT * FROM tbl_name PROCEDURE ANALYSE();

SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDURE ANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。

在对字段类型进行优化时，可以根据统计信息并结合应用的实际情况对其进行优化。

2.2 通过拆分，提高表的访问效率

这里我们所说的拆分，主要是针对Myisam类型的表，拆分的方法可以分成两种情况：

1.         纵向拆分：

纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。

2.         横向拆分：

横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam表的读取和更新导致的锁问题。

2.3 逆规范化

数据库德规范化设计强调数据的独立性，数据应该尽可能少地冗余，因为存在过多的冗余数据，这就意味着要占用了更多的物理空间，同时也对数据的维护和一致性检查带来了问题。

但是对于查询操作很多的应用，一次查询可能需要访问多表进行，如果通过冗余纪录在相同表中，更新的代价增加不多，但是查询操作效率可以有明显提高，这种情况就可以考虑通过冗余数据来提高效率。

2.4 使用冗余统计表

使用create temporary table语法，它是基于session的表，表的数据保存在内存里面，当session断掉后，表自然消除。

对于大表的统计分析，如果统计的数据量不大，利用insert。。。select将数据移到临时表中比直接在大表上做统计要效率更高。

2.5 选择更合适的表类型

1、如果应用出现比较严重的锁冲突，请考虑是否更改存储引擎到innodb，行锁机制可以有效的减少锁冲突的出现。

2、如果应用查询操作很多，且对事务完整性要求不严格，则可以考虑使用Myisam存储引擎。

更多存储引擎选择的原则，请参考开发篇的相关章节。

第3章 锁问题

 

3.1 获取锁等待情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'Table%';

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

| Variable_name       | Value |

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

| Table_locks_immediate   | 105       |

| Table_locks_waited  | 3     |

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

2 rows in set (0.00 sec)

 

可以通过检查Innodb_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

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

| Variable_name               | Value |

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

| Innodb_row_lock_current_waits   | 0     |

| Innodb_row_lock_time            | 2001  |

| Innodb_row_lock_time_avg        | 667       |

| Innodb_row_lock_time_max    | 845       |

| Innodb_row_lock_waits       | 3     |

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

5 rows in set (0.00 sec)

 

另外，针对Innodb类型的表，如果需要察看当前的锁等待情况，可以设置InnoDB Monitors，然后通过Show innodb status察看，设置的方式是：

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

监视器可以通过发出下列语句来被停止：

DROP TABLE innodb_monitor;

设置监视器后，在show innodb status的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等等，便于进行进一步的分析和问题的确定。打开监视器以后，默认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以我们在确认问题原因之后，要记得删除监控表以关闭监视器。或者通过使用--console选项来启动服务器以关闭写日志文件。

3.2 什么情况下使用表锁

表级锁在下列几种情况下比行级锁更优越：

1.   很多操作都是读表。

2.   在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：

3.   UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

4.   DELETE FROM tbl_name WHERE unique_key_col=key_value;

5.   SELECT 和 INSERT 语句并发的执行，但是只有很少的 UPDATE 和 DELETE 语句。

6.   很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。

3.3 什么情况下使用行锁

行级锁定的优点：

1.   当在许多线程中访问不同的行时只存在少量锁定冲突。

2.   回滚时只有少量的更改。

3.   可以长时间锁定单一的行。

行级锁定的缺点：

1.   比页级或表级锁定占用更多的内存。

2.   当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

3.   如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。

4.   用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

3.4 insert …select …带来的问题

当使用insert...select...进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。

对于那些从oracle迁移过来的应用，需要特别的注意，因为oracle并不存在类似的问题，所以在oracle的应用中insert...select...操作非常的常见。例如：有时候会对比较多的纪录进行统计分析，然后将统计的中间结果插入到另外一个表，这样的操作因为进行的非常少，所以可能并没有设置相应的索引。如果迁移到mysql数据库后不进行相应的调整，那么在进行这个操作期间，对需要select的表实际上是进行的全表扫描导致的所有记录的锁定，将会对应用的其他操作造成非常严重的影响。

究其主要原因，是因为mysql在实现复制的机制时和oracle是不同的，如果不进行select表的锁定，则可能造成从数据库在恢复期间插入结果集的不同，造成主从数据的不一致。如果不采用主从复制，关闭binlog并不能避免对select纪录的锁定，某些文档中提到可以通过设置innodb_locks_unsafe_for_binlog来避免这个现象，当这个参数设置为true的时候，将不会对select的结果集加锁，但是这样的设置将可能带来非常严重的隐患。如果使用这个binlog进行从数据库的恢复，或者进行主数据库的灾难恢复，都将可能和主数据库的执行效果不同。

因此，我们并不推荐通过设置这个参数来避免insert...select...导致的锁，如果需要进行可能会扫描大量数据的insert...select操作，我们推荐使用select...into outfile和load data infile的组合来实现，这样是不会对纪录进行锁定的。

3.5 next-key锁对并发插入的影响

在行级锁定中，InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定：当它搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。

InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定，另一个用户 不能紧接在R之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。

可以用next-key锁定在你的应用程序上实现一个唯一性检查：如果你以共享模式读数据，并且没有看到你将要插入的行的重复，则你可以安全地插入你的行，并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此，the next-key锁定允许你锁住在你的表中并不存在的一些东西。

3.6 隔离级别对并发插入的影响

REPEATABLE READ是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE语句只锁定找到的索引记录，而不锁定记录前的间隙。用其它搜索条件，这些操作采用next-key锁定，用next-key锁定或者间隙锁定锁住搜索的索引范围，并且阻止其它用户的新插入。

在持续读中，有一个与READ COMMITTED隔离级别重要的差别：在这个级别，在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句，这些SELECT语句对相互之间也是持续的。

READ COMMITTED隔离级别是一个有些象Oracle的隔离级别。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MOD语句仅锁定索引记录，而不锁定记录前的间隙，因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录，而不包括记录前的间隙。

在范围类型UPDATE和DELETE语句，InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入。这是很必要的，因为要让MySQL复制和恢复起作用，“幽灵行”必须被阻止掉。

如果应用是从基于ORACLE的应用迁移到MYSQL数据库的，那么建议使用该隔离级别提供数据库服务，因为该隔离级别是最接近ORACLE的默认隔离级别的，迁移可能遇到的锁问题最小。

3.7 如何减少锁冲突

1.   对Myisam类型的表：

1)   Myisam类型的表可以考虑通过改成Innodb类型的表来减少锁冲突。

2)   根据应用的情况，尝试横向拆分成多个表或者改成Myisam分区对减少锁冲突也会有一定的帮助。

2.   对Innodb类型的表：

1)   首先要确认，在对表获取行锁的时候，要尽量的使用索引检索纪录，如果没有使用索引访问，那么即便你只是要更新其中的一行纪录，也是全表锁定的。要确保sql是使用索引来访问纪录的，必要的时候，请使用explain检查sql的执行计划，判断是否按照预期使用了索引。

2)   由于mysql的行锁是针对索引加的锁，不是针对纪录加的锁，所以虽然是访问不同行的纪录，但是如果是相同的索引键，是会被加锁的。应用设计的时候也要注意，这里和Oracle有比较大的不同。

3)   当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，当表有主键或者唯一索引的时候，不是必须使用主键或者唯一索引锁定纪录，其他普通索引同样可以用来检索纪录，并只锁定符合条件的行。

4)   用SHOW INNODB STATUS来确定最后一个死锁的原因。查询的结果中，包括死锁的事务的详细信息，包括执行的SQL语句的内容，每个线程已经获得了什么锁，在等待什么锁，以及最后是哪个线程被回滚。详细的分析死锁产生的原因，可以通过改进程序有效的避免死锁的产生。

5)   如果应用并不介意死锁的出现，那么可以在应用中对发现的死锁进行处理。

6)   确定更合理的事务大小，小事务更少地倾向于冲突。

7)   如果你正使用锁定读，（SELECT ... FOR UPDATE或 ... LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。

8)   以固定的顺序访问你的表和行。则事务形成良好定义的查询并且没有死锁。

第4章 优化Mysql Server

 

4.1 查看Mysql server当前参数

1.   查看服务器参数默认值：

mysqld --verbose --help

2.   查看服务器参数实际值：

shell> mysqladmin variables

或者

mysql> SHOW VARIABLES;

3.   查看服务器运行状态值：

shell> mysqladmin extended-status

或者

mysql>SHOW STATUS；

4.2 影响Mysql性能的重要参数

4.2.1 key_buffer_size的设置

 

说明：键缓存(变量key_buffer_size)被所有线程共享；服务器使用的其它缓存则根据需要分配。此参数只适用于myisam存储引擎。

使用方法：

mysql5.1以前只允许使用一个系统默认的key_buffer

mysql5.1以后提供了多个key_buffer，可以将指定的表索引缓存入指定的key_buffer，这样可以更小的降低线程之间的竞争，相关语法如下：

 

 

例如，下面的语句将表t1、t2和t3的索引分配给名为hot_cache的 键高速缓冲：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

 

可以用SET GLOBAL参数设置语句或使用服务器启动选项设置在CACHE INDEX语句中引用的键高速缓冲的大小来创建键高速缓冲。例如：

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

 

 

要想删除键高速缓冲，将其大小设置为零：

mysql> SET GLOBAL keycache1.key_buffer_size=0;

请注意不能删除默认键高速缓冲。删除默认键高速缓冲的尝试将被忽略

 

CACHE INDEX在一个表和 键高速缓冲之间建立一种联系，但每次服务器重启时该联系被丢失。如果你想要每次服务器重启时该联系生效，一个发办法是使用选项文件：包括配置 键高速缓冲的变量设定值，和一个init-file选项用来命名包含待执行的CACHE INDEX语句的一个文件。例如：

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

每次服务器启动时执行mysqld_init.sql中的语句。该文件每行应包含一个SQL语句。下面的例子分配几个表，分别对应hot_cache和cold_cache：

CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache

CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache

 

要想将索引预装到缓存中，使用LOAD INDEX INTO CACHE语句。例如，下面的语句可以预装表t1和t2索引的非叶节点(索引块)：

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

 

键高速缓冲可以通过更新其参数值随时重新构建。例如：

mysql> SET GLOBAL cold_cache。key_buffer_size=4*1024*1024；

 

如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。

4.2.2 table_cache的设置

 

说明：数据库中打开表的缓存数量。table_cache与max_connections有关。例如，对于200个并行运行的连接，应该让表的缓存至少有200 * N，这里N是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。

 

设置技巧：

可以通过检查mysqld的状态变量Opened_tables确定表缓存是否太小：

mysql> SHOW STATUS LIKE 'Opened_tables';

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

| Variable_name | Value |

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

| Opened_tables | 2741  |

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

如果值很大，即使你没有发出许多FLUSH TABLES语句，也应增加表缓存的大小。

4.2.3 innodb_buffer_pool_size的设置：

 

缓存InnoDB数据和索引的内存缓冲区的大小。你把这个值设得越高，访问表中数据需要得磁盘I/O越少。在一个专用的数据库服务器上，你可以设置这个参数达机器物理内存大小的80%。尽管如此，还是不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。

4.2.4 innodb_flush_log_at_trx_commit的设置：

 

0：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

1：在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。

2：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。对日志文件每秒刷新一次。

默认值是1，也是最安全的设置，即每个事务提交的时候都会从log buffer写到日志文件，而且会实际刷新磁盘，但是这样性能有一定的损失。如果可以容忍在数据库崩溃的时候损失一部分数据，那么设置成0或者2都会有所改善。设置成0，则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失1秒钟的事务，这种方式是最不安全的，也是效率最高的。设置成2的时候，因为只是没有刷新到磁盘，但是已经写入日志文件，所以只要操作系统没有崩溃，那么并没有丢失数据，比设置成0更安全一些。

在mysql的手册中，为了确保事务的持久性和复制设置的耐受性、一致性，都是建议将这个参数设置为1的。

4.2.5 innodb_additional_mem_pool_size：

 

InnoDB用来存储数据目录信息和其它内部数据结构的内存池的大小。默认值是1MB。应用程序里的表越多，你需要在这里分配越多的内存。如果InnoDB用光了这个池内的内存，InnoDB开始从操作系统分配内存，并且往MySQL错误日志写警告信息。没有必要给这个缓冲池分配非常大的空间，在应用相对稳定的情况下，这个缓冲池的大小也相对稳定。

4.2.6 innodb_table_locks：

 

InnoDB重视LOCK TABLES，直到所有其它线程已经释放他们所有对表的锁定，MySQL才从LOCK TABLE .. WRITE返回。默认值是1，这意为LOCK TABLES让InnoDB内部锁定一个表。在使用AUTOCOMMIT=1的应用里，InnoDB的内部表锁定会导致死锁。可以通过设置innodb_table_locks=0 来消除这个问题。

4.2.7 innodb_lock_wait_timeout：

 

Mysql可以自动的监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用来在出现类似情况的时候对锁定进行的后续处理。默认值是50秒，根据应用的需要进行调整。

4.2.8 innodb_support_xa：

 

通过该参数设置是否支持分布式事务，默认值是ON或者1，表示支持分布式事务。如果确认应用中不需要使用分布式事务，则可以关闭这个参数，减少磁盘刷新的次数并获得更好的InnoDB性能。

4.2.9 innodb_doublewrite：

 

默认地，InnoDB存储所有数据两次，第一次存储到doublewrite缓冲，然后存储到确实的数据文件。如果对性能的要求高于对数据完整性的要求，那么可以通过--skip-innodb-doublewrite关闭这个设置。

4.2.10 innodb_log_buffer_size:

 

默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

4.2.11 innodb_log_file_size：

 

在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。

第5章 I/O问题

 

磁盘搜索是巨大的性能瓶颈。当数据量变得非常大以致于缓存性能变得不可能有效时，该问题变得更加明显。对于大数据库，其中你或多或少地随机访问数据，你可以确信对读取操作需要至少一次硬盘搜索，写操作需要多次硬盘搜索。要想使该问题最小化，应使用搜索次数较少的磁盘。

5.1 使用磁盘阵列或虚拟文件卷分布I/O

分条意味着你有许多磁盘，将第1个块放到第1个硬盘，第2个块放到第2个磁盘，并且第N块在（N mod number_of_disks）磁盘上等等。这意味着如果正常数据大小小于分条大小（或完全匹配），能够得到最佳性能。分条完全取决于操作系统和分条大小，因此用不同的条纹大小对应用程序进行基准测试。

分条的不同速度完全依赖于参数。依赖于怎样设置条纹参数和硬盘数量，可以根据不同数量级别得到不同的标准。你必须进行选择以便优化随机或顺序存取。

1.   为了高可靠性你可能想使用RAID 0+1（条纹加镜像），但在这种情况下，需要2*N块磁盘来保持N个磁盘的数据。如果你肯为它花钱，这可能是最好的选项。然而，你可能还必须投资一部分资金到卷管理软件中以便有效地管理它。

2.   一个较好的选择是根据数据类型的重要性程度改变RAID级别。例如，保存可以在RAID 0硬盘上重新生成的不太重要的数据，但保存 真正重要数据（例如主机信息和日志）到RAID 0+1或RAID N硬盘。如果你有许多写操作，RAID N可能会存在问题，因为需要时间来更新校验位。

5.2 使用Symbolic Links分布I/O

可以将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符号链接进行替换。推荐的方法只需要将数据库通过符号链接指到不同的磁盘。符号链接表仅作为是最后的办法。

符号链接一个数据库的方法是，首先在一些有空闲空间的硬盘上创建一个目录，然后从MySQL数据目录中创建它的一个符号链接。

shell> mkdir /dr1/databases/test

shell> ln -s /dr1/databases/test /path/to/datadir

注意：只有MyISAM表完全支持符号链接。对于其它表类型，如果试图在操作系统中的文件上用前面的任何语句使用符号链接，可能会出现奇怪的问题。

对于MyISAM表的符号链接的处理如下：

1.   在数据目录指，一定会有表定义文件、数据文件和索引文件。数据文件和索引文件可以移到别处和在数据目录中符号链接替代。表定义文件不能进行符号链接替换。

2.   可以分别通过符号链接将数据文件和索引文件指到不同的目录。

3.   如果mysqld没有运行，符号链接可以从服务器命令行使用ln -s手动完成。同样，通过使用DATA DIRECTORY和INDEX DIRECTORY选项创建表，你可以指示运行的MySQL服务器执行符号链接。

4.   myisamchk不用数据文件或索引文件替换符号链接。它直接工作在符号链接指向的文件。任何临时文件创建在数据文件或索引文件所处的目录中。

5.   注释：当你删掉一个表时，如果该表使用了符号链接，符号链接和该符号链接指向的文件都被删除掉。这就是你不应以系统root用户运行mysqld或允许系统用户对MySQL数据库目录有写访问权限的原因。

6.   如果你用ALTER TABLE ... RENAME重命名一个表并且不将表移到另一个数据库，数据库目录中的符号链接被重新命名为一个新名字并且数据文件和索引文件也相应地重新命名。

7.   如果你用ALTER TABLE ... RENAME移动一个表到另一个数据库，表移动到另一个数据库目录。旧的符号链接和其所指向的文件被删除。换句话说，新表不再被链接。

8.   如果不使用符号链接，你应对mysqld使用--skip-symbolic-links选项以确保没有人能够使用mysqld来删除或重新命名数据目录之外的文件。

 

表符号链接还不支持以下操作：

1.   ALTER TABLE忽略DATA DIRECTORY 和INDEX DIRECTORY表选项。

2.   BACKUP TABLE 和RESTORE TABLE不考虑符号链接。

3.   .frm文件必须绝不能是一个符号链接（如前面所述，只有数据和索引文件可以是符号链接）。如果试图这样做（例如，生成符号链接）会产生不正确的结果。

第6章 应用优化

 

6.1 使用连接池

对于访问数据库来说，建立连接的代价比较昂贵，因此，我们有必要建立"连接池"以提高访问的性能。我们可以把连接当作对象或者设备，池中又有许多已经建立的连接，访问本来需要与数据库的连接的地方，都改为和池相连，池临时分配连接供访问使用，结果返回后，访问将连接交还。

6.2 减少对Mysql的访问

6.2.1 避免对同一数据做重复检索：

 

应用中需要理清楚对数据库的访问逻辑，需要对相同表的访问，尽量集中在相同sql访问，一次提取结果，减少对数据库的重复访问。

6.2.2 使用mysql query cache：

 

作用：查询缓存存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询，服务器从查询缓存中重新得到查询结果，而不再需要解析和执行查询。

适用范围：不发生数据更新的表。当表更改（包括表结构和表数据）后，查询缓存值的相关条目被清空。

查询缓存的主要参数设置：

show variables like ‘%query_cache%’;

have_query_cache表明服务器在安装使已经配置了高速缓存

query_cache_size表明缓存区大小，单位为M

query_cache_type的变量值从0到2，含义分别为

0或者off（缓存关闭）

1或者on（缓存打开，使用sql_no_cache的select除外）

2或者demand（只有带sql_cache的select语句提供高速缓存）

在 SHOW STATUS 中，你可以监视查询缓存的性能：

变量

含义

Qcache_queries_in_cache

在缓存中已注册的查询数目

Qcache_inserts

被加入到缓存中的查询数目

Qcache_hits

缓存采样数数目

Qcache_lowmem_prunes

因为缺少内存而被从缓存中删除的查询数目

Qcache_not_cached

没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)

Qcache_free_memory

查询缓存的空闲内存总数

Qcache_free_blocks

查询缓存中的空闲内存块的数目

Qcache_total_blocks

查询缓存中的块的总数目

6.2.3 加cache层：

 

Cache（高速缓存）、Memory（内存）、Hard disk（硬盘）都是数据存取单元，但存取速度却有很大差异，呈依次递减的顺序。对于CPU来说，它可以从距离自己最近的Cache高速地存取数据，而不是从内存和硬盘以低几个数量级的速度来存取数据。而Cache中所存储的数据，往往是CPU要反复存取的数据，有特定的机制（或程序）来保证Cache内数据的命中率（Hit Rate）。因此，CPU存取数据的速度在应用高速缓存后得到了巨大的提高。

因为将数据写入高速缓存的任务由Cache Manager负责，所以对用户来说高速缓存的内容肯定是只读的。需要你做的工作很少，程序中的SQL语句和直接访问DBMS时没有分别，返回的结果也看不出有什么差别。而数据库厂商往往会在DB Server的配置文件中提供与Cache相关的参数，通过修改它们，可针对我们的应用优化Cache的管理。

6.3 负载均衡

6.3.1 利用mysql 复制分流查询操作：

 

利用mysql的主从复制可以有效的分流更新操作和查询操作，具体的实现是一个主服务器，承担更新操作，多台从服务器，承担查询操作，主从之间通过复制实现数据的同步。多台从服务器一方面用来确保可用性，一方面可以创建不同的索引满足不同查询的需要。

对于主从之间不需要复制全部表的情况，可以通过在主的服务器上搭建一个虚拟的从服务器，将需要复制到从服务器的表设置成blackhole引擎，然后定义replicate-do-table参数只复制这些表，这样就过滤出需要复制的binlog，减少了传输binlog的带宽。因为搭建的虚拟的从服务器只起到过滤binlog的作用，并没有实际纪录任何数据，所以对主数据库服务器的性能影响也非常的有限。

通过复制分流查询的存在的问题是主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在差异，造成查询结果的异议，应用在设计的时候需要有所考虑。

6.3.2 采用分布式数据库架构：

 

mysql从5.0.3开始支持分布式事务，当前分布式事务只对Innodb存储引擎支持。分布式的数据库架构适合大数据量，负载高的情况，有良好的扩展性和高可用性。通过在多台服务器之间分布数据实现在多台服务器之间的负载平均，提高了访问的执行效率。具体实现的时候，可以使用mysql的Cluster功能（NDB引擎）或者自己编写程序来实现全局事务。