Mysql优化

首先我们需要明确我们什么时候需要用到数据库：
1. 当缓存并不能解决你的问题，比如写操作，事务操作
2. 缓存的创建或过期需要通过数据库。

其次，我们可能需要一个专业的工具来指导我们优化：
mysqlreport
这是作为一个Mysql第三方的状态报告工具，其实就是将一下两行命令所获得的数据以一种更加人性化的方法呈现到我们眼前：

mysql> show status;mysql> show engine innodb status; 

你完全可以在自己电脑上先使用这个工具，这时候我们将逐条讲解工具为我们呈现的数据

s-db:~ # mysqlreport MySQL 5.0.37-log         uptime 1 15:11:47      Fri Apr 24 15:35:49 2009__ Key ______________________________________________________________Buffer used   100.37M of   2.00G  %Used:   4.90  Current       1.02G            %Usage:  51.07Write hit      99.26%Read hit       98.71%__ Questions ________________________________________________________Total          58.09M   411.7/s  DMS          30.62M   217.0/s  %Total:  52.71  Com_         21.23M   150.4/s           36.54  COM_QUIT      6.14M    43.5/s           10.58  +Unknown    104.71k     0.7/s            0.18Slow 1 s           722      0.2/s           0.04  %DMS:   0.08  Log:  ONDMS              30.62M   217.0/s           52.71  SELECT       26.20M   185.7/s           45.10         85.56  UPDATE        3.72M    26.3/s            6.40         12.14  INSERT      649.28k     4.6/s            1.12          2.12  DELETE       54.13k     0.4/s            0.09          0.18  REPLACE       1.88k     0.0/s            0.00          0.01Com_            21.23M   150.4/s           36.54  change_db    21.18M   150.1/s           36.45  show_variab   8.94k     0.1/s            0.02  show_status   8.93k     0.1/s            0.02__ SELECT and Sort ___________________________________________________Scan            1.52M    10.8/s %SELECT:   5.80Range         865.18k     6.1/s            3.30Full join       3.18k     0.0/s            0.01Range check         0       0/s            0.00Full rng join       0       0/s            0.00Sort scan       2.34M    16.6/sSort range      2.10M    14.9/sSort mrg pass     739     0.0/s__ Table Locks ______________________________________________________Waited          9.17k     0.1/s  %Total:   0.03Immediate      32.03M   227.0/s__ Tables ___________________________________________________________Open              512 of  512    %Cache: 100.00Opened          2.78M    19.7/s__ Connections ______________________________________________________Max used           98 of  100      %Max:  98.00Total           6.15M    43.6/s__ Created Temp _____________________________________________________Disk table    332.75k     2.4/sTable           2.25M    16.0/s    Size:  32.0MFile            1.48k     0.0/s__ Threads __________________________________________________________Running             2 of    5Cached              0 of    0      %Hit:      0Created         6.15M    43.6/sSlow                0       0/s__ Aborted __________________________________________________________Clients         5.70k     0.0/sConnects           36     0.0/s__ Bytes ____________________________________________________________Sent            2.74G   19.4k/sReceived        3.70G   26.2k/s__ InnoDB Buffer Pool ________________________________________________Usage           1.00G of   1.00G  %Used: 100.00Read hit       99.84%Pages  Free              1            %Total:   0.00  Data         65.16k                     99.43 %Drty:   4.00  Misc            374                      0.57  Latched           0                      0.00Reads          78.05M   553.2/s  From file   125.51k     0.9/s            0.16  Ahead Rnd      1740     0.0/s  Ahead Sql         7     0.0/sWrites         16.06M   113.8/sFlushes         1.30M     9.2/sWait Free           0       0/s__ InnoDB Lock ______________________________________________________Waits            1441     0.0/sCurrent             0Time acquiring  Total          1178 ms  Average           0 ms  Max              39 ms__ InnoDB Data, Pages, Rows __________________________________________Data  Reads       142.84k     1.0/s  Writes        1.26M     8.9/s  fsync       191.53k     1.4/s  Pending    Reads           0    Writes          0    fsync           0Pages  Created       1.05k     0.0/s  Read        173.91k     1.2/s  Written       1.30M     9.2/sRows  Deleted           0       0/s  Inserted    149.59k     1.1/s  Read          3.72G   26.4k/s  Updated       2.51M    17.8/s

首先最上面的uptime中我们可以看到MySQL数据库的运行时间，而报告中的各项数据统计，都是通过这段运行时间的累计数据计算而得，所以，我们希望这段时间尽可能地长，至少覆盖站点高负载的时段，这样我们采集的数据才具有较好的代表性。

索引的优化说明

对于索引的介绍这里就不做过多的重复了。

我们需要知道的是数据库中大概分为两种扫描模式：
1. 全表扫描
2. 索引扫描

大多数时候索引扫描会比全表扫描有着更好的性能，但是并非绝对，当我们需要一个表中绝大多数数据（这个数量可能是60%以上）的时候，全表扫描就可能获得比索引扫描更好的性能，相信这并不难理解，毕竟我们阅读一本书的时候，如果向获取书中绝大部分的知识可能一页一页阅读更加迅速。

当然，或许有时候数据库的查询优化器会帮助我们鉴别什么时候我们需要使用这些索引，但是我认为这应该是当我们创建一个表的时候需要考虑的内容。

索引有挺多种的，除了普通索引，还有唯一索引，主键，全文索引等，但是，它们只要是索引，都会具备索引扫描的功能，不同的是它们可能会满足我们一些额外的需求。

需要特别强调的是：构建索引，构建什么索引，在哪个键构建索引，这是我们自己的事，没有什么工具能够帮助我们完成这些本是我们的工作。

一般来说，如果一个字段出现在查询语句中基于行的选择、过滤或排序条件中，那么为该字段建立索引便是有价值的，但这也不是绝对的。我们很难给出一个描述了所有情况的列表供你参考，因为查询的过程非常复杂，它可能包含多列索引、组合索引以及联合查询等情况，所以，关键在于掌握分析方法，这样你便能够应付任何的困境。

这时候有一个分析方法是我们必须掌握的：explain。它只复杂查询语句的分析。

我们或许可以看看实例：

CREATE TABLE `test` (  `id` int(11) NOT NULL auto_increment,  `name` varchar(255) NOT NULL,  PRIMARY KEY  (`id`)) 

然后我填充了一些记录，内容是什么并不重要，我们这里只是用explain来分析查询语句是否使用了索引。

让我们先使用主键试试

mysql> explain select * from test where id=1;+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 

这里我们可以看到：
- type为const，意味着这次查询通过索引直接找到一个匹配行，所以优化器认为它的时间复杂度为常量。
- key为PRIMARY，意味着这次查询使用了主键索引。

现在让我们换成普通属性：

mysql> explain select * from test where name='colin';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 

这时候的type就变成了ALL，表示全表扫描了。

也许我们加上索引情况会有所不同吧？
mysql> alter table test add key name(name);

mysql> explain select * from test where name='colin';+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+|  1 | SIMPLE      | test  | ref  | name          | name | 257     | const |    1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 

意料之中～

也许……我们可以试试模糊查询：

mysql> explain select * from test where name like '%colin';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 

看来此时的索引是帮不上忙了（少用模糊查询）

对于包含group by的查询，数据库一般需要先将记录分组后放置在新的临时表中，然后分别对它们进行函数计算，比如count()、sum()或max()等。当有恰当的索引存在时，group by有时也可以使用索引来取代创建临时表，这当然是我们所希望的。以下这个SQL语句便利用了normal_key索引，避免了创建临时表。

mysql> explain select count(id) from key_t where key1=777 group by key3;+----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------------------------------------------+| id | select_type | table | type | possible_keys | key        | key_len | ref   | rows | Extra                                                     |+----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------------------------------------------+|  1 | SIMPLE      | key_t | ref  | normal_key    | normal_key | 4       | const | 2154 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------------------------------------------+

先等等，我们还有一个重点

组合索引

（就如同书目录的章和节）

最左前缀：
顾名思义，最左优先，当我们创建了一个(key1, key2, key3...)的索引的时候会按照如下的索引，key1,(key1, key2),(key1, key2, key3)…

这时候就会有个坑了……

先让我们来看看这条查询：

mysql> select * from key_t where key2=777 limit 10;+--------+------+------+------+| id     | key1 | key2 | key3 |+--------+------+------+------+| 327233 |  643 |  777 |  781 | | 686994 |  765 |  777 |  781 | | 159907 |  766 |  777 |  782 | |  61518 |  769 |  777 |  780 | | 274629 |  769 |  777 |  780 | | 633439 |  769 |  777 |  780 | | 774191 |  769 |  777 |  780 | | 109562 |  769 |  777 |  781 | | 130013 |  769 |  777 |  781 | | 139458 |  769 |  777 |  781 | +--------+------+------+------+10 rows in set (0.38 sec) 

380ms！没错，可能你会想，我们并没有使用索引，它是全表查询，但是……：

mysql> explain select * from key_t where key2=777 limit 10;+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows    | Extra                    |+----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+|  1 | SIMPLE      | key_t | index | NULL          | normal_key | 12      | NULL | 1000417 | Using where; Using index | +----+-------------+-------+-------+---------------+------------+---------+------+---------+--------------------------+ 

对不起，它用了normal_key，仔细看上面十行数据似乎都是按照key1字段来排序的，事实也是如此，这说明查询是依据normal_key来扫描而不是数据本身扫描，在Innodb类型表中数据的存储顺序是按照主键来排序的。

下面让我们将这个表转换为MyISAM类型来看看：

mysql> alter table key_t type=myisam;Query OK, 1000000 rows affected, 1 warning (33.49 sec)Records: 1000000  Duplicates: 0  Warnings: 0 

然后查询：

mysql> select * from key_t_myisam where key2=777 limit 10;+------+------+------+------+| id   | key1 | key2 | key3 |+------+------+------+------+| 1035 |  771 |  777 |  781 | | 3175 |  771 |  777 |  781 | | 4126 |  771 |  777 |  781 | | 5443 |  770 |  777 |  780 | | 6066 |  771 |  777 |  781 | | 6267 |  770 |  777 |  780 | | 6317 |  770 |  777 |  780 | | 6496 |  771 |  777 |  781 | | 8262 |  770 |  777 |  780 | | 9083 |  771 |  777 |  780 | +------+------+------+------+10 rows in set (0.00 sec)

看看分析结果：

mysql> explain select * from key_t_myisam where key2=777 limit 10;+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | SIMPLE      | key_t | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where | +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

这才是货真价实的全表扫描啊！

我们足可以想象当组合索引不能直接发挥作用的时候，反而给查询带来了巨大的负担，一个包含多个字段的组合索引的尺寸可能已经超过了数据本身。

如果你希望结果仍然按照key1排序，这不是什么问题，你可以增加一个包含（key2,key1）字段的组合索引，注意它们的顺序，（key1,key2）索引和（key2,key1）索引完全不同，你必须根据需要来进行抉择，而优化器对此无能为力。

以上我们只是举了个简单的例子，并不是鼓励你用全表扫描取代索引扫描，而是希望借此强调，一定要根据查询的需要来设计有针对性的组合索引，因为在实际应用中，很多查询不像例子中的那么简单，一个量身定制的组合索引肯定要比全表扫描更加高效，这是毋庸置疑的。

慢查询工具分析

我们很容易对所有查询语句进行分析，并建立适当的索引，但这只是纸上谈兵，当到了运行环境后，随着实际数据的积累，查询计算的开销也逐渐增加，也许你会发现有些索引设计得并不理想，不可否认，错误是在所难免的。可是，在运行环境上对各种查询进行explain分析显然很不现实，更难的是你不知道何时去分析哪些查询。

最好的办法是对每一条查询语句的时间进行记录然后再对其中比较慢的查询去分析，通过Web应用的日志功能可能也是一个比较不错的主意。

但是我们这里是介绍数据库，数据库中还有一种方法可以帮助我们达到我们所想要的目的，它提供了慢查询日志，可以将执行时间超过预设值的所有查询记录到日志中，以供后期分析。

在Mysql中的my.cnf开启慢查询工具

long_query_time = 1log-slow-queries = /data/var/mysql_slow.log 

这意味着MySQL会自动将执行时间超过1秒的查询记录在指定路径的mysql_slow.log文件中。除此之外，你还可以将所有没有使用索引的查询也记录下来，只需增加以下选项即可：
log-queries-not-using-indexes
而在之前提及的mysqlreport中，我们也可以看到有关慢查询的统计：
Slow 1 s 722 0.2/s 0.04 %DMS: 0.08 Log: ON

我们可以使用第三方工具mysqlsla，它有着比之其他工具更加清晰的统计风格。

我们还有一个优化策略，使用缓存，缓存索引，这样使用到索引的时候就不需要在磁盘中读取数据了。
在mysqlreport中也有这方面的信息：

__ Key ______________________________________________________________Buffer used   100.37M of   2.00G  %Used:   4.90  Current       1.02G            %Usage:  51.07Write hit      99.26%Read hit       98.71%

这里的缓存和之前介绍的其他缓存在本质上没什么两样，Write hit和Read hit分别代表了写缓存的命中率和读缓存的命中率，这里都在98%以上，比较正常。

当然最后在索引这儿提一下使用它的代价（我们大多数优化策略其实都需要付出一些代价的）
首先是空间，索引使用空间比之普通文件较大，但是在如今存储空间不值钱的年代这完全不是事。
其次，当建立索引的字段发生更新时，会引发索引本身的更新，这将产生不小的计算开销。
最后，索引需要我们花费一些额外的时间来维护。

锁

锁机制是保证当有多个用户并发访问数据库某个资源的时候，并发访问的一致性。

我们可以认为查询的时间开销主要包括两部分，即
1. 查询本身的计算时间
2. 查询开始前的等待时间

所以说索引影响的是前者，而锁机制影响的是后者。显然，我们的目标很明确，那就是减少等待时间。

在mysqlreport中也有相应的记录：

__ Table Locks ______________________________________________________Waited          9.17k     0.1/s  %Total:   0.03Immediate      32.03M   227.0/s

Waited表示有多少次查询需要等待表锁定；Immediate表示有多少次查询可以立即获得表锁定，同时后面还有一个比例，表示等待表锁定的查询次数占所有查询次数的百分比，这里是0.03%，非常好，但为什么这么低呢？这需要了解MyISAM的表锁定机制。

MyISAM的表锁定可以允许多个线程同时读取数据，比如select查询，它们之间是不需要锁等待的。但是对于更新操作（如update操作），它会排斥对当前表的所有其他查询，包括select查询。除此之外，更新操作有着默认的高优先级，这意味着当表锁释放后，更新操作将先获得锁定，然后才轮到读取操作。也就是说，如果有很多update操作排着长队，那么对当前表的select查询必须等到所有的更新都完成之后才能开始。

如果你的站点主要依靠用户创造内容，那么频繁的数据更新在所难免，它将会给select等读取操作带来很大的影响，你可能会需要innodb的行锁定来为你提高一定的性能。

但是我们需要认清楚，行锁定只是能够提高你在update上的等待时间，但是却不能加速update的时间，当你的update太过密集的时候，你的磁盘读写速度会称为限制你的性能的一个门槛，这种情形下或许行锁定的消耗就决定它并不是最好的选择了。

事务

事务可以说是吸引大家选择innodb的一个主要原因，但是，在选择它的时候你需要先问问自己，你是否真的这么需要事务，如果没有事务，你有什么不能解决的问题，毕竟事务对于性能也是一大消耗。

Innodb的实现方式是预写日志方式(WAL),当有事务提交时，也就是只有当事务日志写入磁盘后才更新数据和索引，这样即使数据库崩溃，也可以通过事务日志来恢复数据和索引。Innodb首先将它写到内存中的事务日志缓冲区，随后当事务日志写入磁盘时，Innodb才更新实际数据和索引。这里有一个关键点，那就是事务日志何时写入磁盘。

为此，MySQL提供了一个配置选项，它有三个可选的值：
- innodb_flush_log_at_trx_commit = 1
表示事务提交时立即将事务日志写入磁盘，同时数据和索引也立即更新。这符合事务的持久性原则。
- innodb_flush_log_at_trx_commit = 0
表示事务提交时不立即将事务日志写入磁盘，而是每隔1秒写入磁盘文件一次，并且刷新到磁盘，同时更新数据和索引。这样一来，如果mysqld崩溃，那么在内存中事务日志缓冲区最近1秒的数据将会丢失，这些更新将永远无法恢复。
- innodb_flush_log_at_trx_commit = 2
表示事务提交时立即写入磁盘文件，但是不立即刷新到磁盘，而是每隔1秒刷新到磁盘一次，同时更新数据和索引。在这种情况下，即使mysqld崩溃后，位于内核缓冲区的事务日志仍然不会丢失，只有当操作系统崩溃的时候才会丢失最后1秒的数据。

显然，将innodb_flush_log_at_trx_commit设置为0可以获得最佳性能，同时它的数据丢失可能性也最大。

另一个重要的配置选项是Innodb数据和索引的内存缓冲池大小，MySQL提供了innodb_buffer_pool_size选项来设置这个数值，如果你在MySQL中大量使用Innodb类型表，则可以将缓冲池大小设置为物理内存的80%，并持续关注它的使用率，这时候mysqlreport又提供了方便。

__ InnoDB Buffer Pool ________________________________________________Usage           1.00G of   1.00G  %Used: 100.00Read hit       99.84%

使用查询缓存

查询缓存的目的很简单，将select查询的结果缓存在内存中，以供下次直接获取。在默认情况下，MySQL是没有开启查询缓存的，我们可以进行以下配置：

query_cache_size = 268435456query_cache_type = 1query_cache_limit = 1048576

这样一来，MySQL将拥有256MB的内存空间来缓存查询结果。对于以select查询为主的应用，查询缓存理所当然地起到性能提升的作用，不论是Innodb还是MyISAM，查询缓存都可以很好地工作，因为它在逻辑中位于比较高的层次。

但是，查询缓存有一个需要注意的问题，那就是缓存过期策略，MySQL采用的机制是，当一个数据表有更新操作（比如update或者insert）后，那么涉及这个表的所有查询缓存都会失效。这的确令人比较沮丧，但是MySQL这样做是不希望引入新的开销而自找麻烦，所以“宁可错杀一千，不可放过一个”。这样一来，对于select和update混合的应用来说，查询缓存反而可能会添乱

关于mysqlreport中查询缓存的报告：

__ Query Cache ______________________________________________________Memory usage   38.05M of 256.00M  %Used:  14.86Block Fragmnt   4.29%Hits           12.74k    33.3/sInserts        58.21k   152.4/sInsrt:Prune  58.21k:1   152.4/sHit:Insert     0.22:1

如果你的应用中对于密集select的数据表很少更新，很适合于使用查询缓存。

临时表

我们或许看到一些explain查询在分析时出现Using temporary的状态，这意味着查询过程中需要创建临时表来存储中间数据，我们需要通过合理的索引来避免它。另一方面，当临时表在所难免时，我们也要尽量减少临时表本身的开销，通过mysqlreport报告中的Created Temp部分，我们可以看到：

_ Created Temp _____________________________________________________Disk table    864.89k     2.0/sTable           7.06M    16.1/s    Size:  32.0MFile            9.22k     0.0/s

MySQL可以将临时表创建在磁盘（Disk table）、内存（Table）以及临时文件（File）中，显然，在磁盘上创建临时表的开销最大，所以我们希望MySQL尽量不要在磁盘上创建临时表。

在MySQL的配置中，我们可以通过tmp_table_size选项来设置用于存储临时表的内存空间大小，一旦这个空间不够用，MySQL将会启用磁盘来保存临时表，你可以根据mysqlreport的统计尽量给临时表设置较大的内存空间。

线程池

我们知道，MySQL采用多线程来处理并发的连接，通过mysqlreport中的Threads部分，我们可以看到线程创建的统计结果：

 Threads _____________________________________________________Running             2 of    5Cached              0 of    0      %Hit:      0Created         6.15M    43.6/sSlow                0       0/s

也许你会觉得创建线程的消耗不值一提，但是我们所谓优化都是在你系统繁忙下的救命稻草。

一个比较好的办法是在应用中尽量使用持久连接，这将在一定程度上减少线程的重复创建。另一方面，从上面的Cached=0可以看出，这些线程并没有被复用，我们可以在my.cnf中设置以下选项：
thread_cache_size = 100

抛弃关系型数据库

我们应该知道，在关系型数据库里指导我们设计数据库表的时候可以根据范式来设计，我们一般会选择第三范式，简单地说，第三范式要求在一个数据表中，非主键字段之间不能存在依赖关系，这样一来，它可以避免更新异常、插入异常和删除异常，保证关系的一致性，并且减少数据冗余。

其实我们并不需要去刻意设计，只要你设计表的时候思维正常，大多数时候都是第三范式。

但是对于某些应用这样做可能会极大降低我们的性能
比如对于这样两张表
（用户ID，好友ID，好友昵称）
（用户ID，用户昵称，用户邮箱，注册时间，联系电话）
它们肯定不符合第三范式，因为好友昵称依赖与好友ID，但是这两个很容易是同时取出，而修改好友昵称的机会少得可怜，甚至你的应用可能根本不想支持，这时候抛弃范式来设计就会有着更好的效果。

还有一个比较经典的例子：
社交网站中的好友Feed功能，你一定非常熟悉，当你的好友或者关注的人发生一些事件的时候，你会在自己的空间看到这些动态，看起来很简单的功能，你将如何实现呢？如果在你每次刷新空间的时候都要对每个好友的最新事件进行一番查询，甚至使用可怕而昂贵的join联合查询，当你有500个好友的时候，开销可想而知。这个时候，出于性能的考虑，可以使用反范式化设计，将这些动态为所有关注它的用户保存一份副本，当然，这会带来大量的写开销，但是这些写操作完全可以异步进行，而不影响用户的体验。

这时候如果使用NOSQL来存储这些冗余的副本可能会给你带来意想不到的性能的提升。

下面是一些启发性优化策略：
Mysql性能优化20+条经验

最后参考资料：
《构建高性能Web站点》，推荐必读书