首先我们需要明确我们什么时候需要用到数据库：
当缓存并不能解决你的问题，比如写操作，事务操作
缓存的创建或过期需要通过数据库。
其次，我们可能需要一个专业的工具来指导我们优化：mysqlreport
1 2 mysql> show status; mysql> show engine innodb status;
你完全可以在自己电脑上先使用这个工具，这时候我们将逐条讲解工具为我们呈现的数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 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.07 Write 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.18 Slow 1 s 722 0.2/s 0.04 %DMS: 0.08 Log: ON DMS 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.01 Com_ 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.80 Range 865.18k 6.1/s 3.30 Full join 3.18k 0.0/s 0.01 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 2.34M 16.6/s Sort range 2.10M 14.9/s Sort mrg pass 739 0.0/s __ Table Locks ______________________________________________________ Waited 9.17k 0.1/s %Total: 0.03 Immediate 32.03M 227.0/s __ Tables ___________________________________________________________ Open 512 of 512 %Cache: 100.00 Opened 2.78M 19.7/s __ Connections ______________________________________________________ Max used 98 of 100 %Max: 98.00 Total 6.15M 43.6/s __ Created Temp _____________________________________________________ Disk table 332.75k 2.4/s Table 2.25M 16.0/s Size: 32.0M File 1.48k 0.0/s __ Threads __________________________________________________________ Running 2 of 5 Cached 0 of 0 %Hit: 0 Created 6.15M 43.6/s Slow 0 0/s __ Aborted __________________________________________________________ Clients 5.70k 0.0/s Connects 36 0.0/s __ Bytes ____________________________________________________________ Sent 2.74G 19.4k/s Received 3.70G 26.2k/s __ InnoDB Buffer Pool ________________________________________________ Usage 1.00G of 1.00G %Used: 100.00 Read hit 99.84% Pages Free 1 %Total: 0.00 Data 65.16k 99.43 %Drty: 4.00 Misc 374 0.57 Latched 0 0.00 Reads 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/s Writes 16.06M 113.8/s Flushes 1.30M 9.2/s Wait Free 0 0/s __ InnoDB Lock ______________________________________________________ Waits 1441 0.0/s Current 0 Time 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 0 Pages Created 1.05k 0.0/s Read 173.91k 1.2/s Written 1.30M 9.2/s Rows Deleted 0 0/s Inserted 149.59k 1.1/s Read 3.72G 26.4k/s Updated 2.51M 17.8/s
首先最上面的uptime中我们可以看到MySQL数据库的运行时间，而报告中的各项数据统计，都是通过这段运行时间的累计数据计算而得，所以，我们希望这段时间尽可能地长，至少覆盖站点高负载的时段，这样我们采集的数据才具有较好的代表性。
索引的优化说明 对于索引的介绍这里就不做过多的重复了。
我们需要知道的是数据库中大概分为两种扫描模式：
全表扫描
索引扫描
大多数时候索引扫描会比全表扫描有着更好的性能，但是并非绝对，当我们需要一个表中绝大多数数据（这个数量可能是60%以上）的时候，全表扫描就可能获得比索引扫描更好的性能，相信这并不难理解，毕竟我们阅读一本书的时候，如果向获取书中绝大部分的知识可能一页一页阅读更加迅速。
当然，或许有时候数据库的查询优化器会帮助我们鉴别什么时候我们需要使用这些索引，但是我认为这应该是当我们创建一个表的时候需要考虑的内容。
索引有挺多种的，除了普通索引，还有唯一索引，主键，全文索引等，但是，它们只要是索引，都会具备索引扫描的功能，不同的是它们可能会满足我们一些额外的需求。
需要特别强调的是：构建索引，构建什么索引，在哪个键构建索引，这是我们自己的事，没有什么工具能够帮助我们完成这些本是我们的工作。
一般来说，如果一个字段出现在查询语句中基于行的选择、过滤或排序条件中，那么为该字段建立索引便是有价值的，但这也不是绝对的。我们很难给出一个描述了所有情况的列表供你参考，因为查询的过程非常复杂，它可能包含多列索引、组合索引以及联合查询等情况，所以，关键在于掌握分析方法，这样你便能够应付任何的困境。
这时候有一个分析方法是我们必须掌握的：**explain**。它只复杂查询语句的分析。
我们或许可以看看实例：
1 2 3 4 CREATE TABLE `test` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`))
然后我填充了一些记录，内容是什么并不重要，我们这里只是用explain来分析查询语句是否使用了索引。
让我们先使用主键试试
1 2 3 4 5 6 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，意味着这次查询使用了主键索引。
现在让我们换成普通属性：
1 2 3 4 5 6 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);
1 2 3 4 5 6 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 | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
意料之中～
也许……我们可以试试模糊查询：
1 2 3 4 5 6 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索引，避免了创建临时表。
1 2 3 4 5 6 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)…
这时候就会有个坑了……
先让我们来看看这条查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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！没错，可能你会想，我们并没有使用索引，它是全表查询，但是……：
1 2 3 4 5 6 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类型来看看：
1 2 mysql> alter table key_t type=myisam; Query OK, 1000000 rows affected, 1 warning (33.49 sec)Records: 1000000 Duplicates: 0 Warnings: 0
然后查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 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)
看看分析结果：
1 2 3 4 5 6 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开启慢查询工具
1 2 long_query_time = 1 log-slow-queries = /data/var/mysql_slow.log
这意味着MySQL会自动将执行时间超过1秒的查询记录在指定路径的mysql_slow.log文件中。除此之外，你还可以将所有没有使用索引的查询也记录下来，只需增加以下选项即可：log-queries-not-using-indexesmysqlreport中，我们也可以看到有关慢查询的统计：Slow 1 s 722 0.2/s 0.04 %DMS: 0.08 Log: ON
我们可以使用第三方工具mysqlsla，它有着比之其他工具更加清晰的统计风格。
我们还有一个优化策略，使用缓存，缓存索引，这样使用到索引的时候就不需要在磁盘中读取数据了。
1 2 3 4 5 __ Key ______________________________________________________________ Buffer used 100.37M of 2.00G %Used: 4.90 Current 1.02G %Usage: 51.07 Write hit 99.26% Read hit 98.71%
这里的缓存和之前介绍的其他缓存在本质上没什么两样，Write hit和Read hit分别代表了写缓存的命中率和读缓存的命中率，这里都在98%以上，比较正常。
当然最后在索引这儿提一下使用它的代价（我们大多数优化策略其实都需要付出一些代价的）
锁 锁机制是保证当有多个用户并发访问数据库某个资源的时候，并发访问的一致性。
我们可以认为查询的时间开销主要包括两部分，即
查询本身的计算时间
查询开始前的等待时间
所以说索引影响的是前者，而锁机制影响的是后者。显然，我们的目标很明确，那就是减少等待时间。
在mysqlreport中也有相应的记录：
1 2 3 __ Table Locks ______________________________________________________ Waited 9.17k 0.1/s %Total: 0.03 Immediate 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 = 1innodb_flush_log_at_trx_commit = 0innodb_flush_log_at_trx_commit = 2
显然，将innodb_flush_log_at_trx_commit设置为0可以获得最佳性能，同时它的数据丢失可能性也最大。
另一个重要的配置选项是Innodb数据和索引的内存缓冲池大小，MySQL提供了innodb_buffer_pool_size选项来设置这个数值，如果你在MySQL中大量使用Innodb类型表，则可以将缓冲池大小设置为物理内存的80%，并持续关注它的使用率，这时候mysqlreport又提供了方便。
1 2 3 __ InnoDB Buffer Pool ________________________________________________ Usage 1.00G of 1.00G %Used: 100.00 Read hit 99.84%
使用查询缓存 查询缓存的目的很简单，将select查询的结果缓存在内存中，以供下次直接获取。在默认情况下，MySQL是没有开启查询缓存的，我们可以进行以下配置：
1 2 3 query_cache_size = 268435456 query_cache_type = 1 query_cache_limit = 1048576
这样一来，MySQL将拥有256MB的内存空间来缓存查询结果。对于以select查询为主的应用，查询缓存理所当然地起到性能提升的作用，不论是Innodb还是MyISAM，查询缓存都可以很好地工作，因为它在逻辑中位于比较高的层次。
但是，查询缓存有一个需要注意的问题，那就是缓存过期策略，MySQL采用的机制是，当一个数据表有更新操作（比如update或者insert）后，那么涉及这个表的所有查询缓存都会失效。这的确令人比较沮丧，但是MySQL这样做是不希望引入新的开销而自找麻烦，所以“宁可错杀一千，不可放过一个”。这样一来，对于select和update混合的应用来说，查询缓存反而可能会添乱
关于mysqlreport中查询缓存的报告：
1 2 3 4 5 6 7 __ Query Cache ______________________________________________________ Memory usage 38.05M of 256.00M %Used: 14.86 Block Fragmnt 4.29% Hits 12.74k 33.3/s Inserts 58.21k 152.4/s Insrt:Prune 58.21k:1 152.4/s Hit:Insert 0.22:1
如果你的应用中对于密集select的数据表很少更新，很适合于使用查询缓存。
临时表 我们或许看到一些explain查询在分析时出现Using temporary的状态，这意味着查询过程中需要创建临时表来存储中间数据，我们需要通过合理的索引来避免它。另一方面，当临时表在所难免时，我们也要尽量减少临时表本身的开销，通过mysqlreport报告中的Created Temp部分，我们可以看到：
1 2 3 4 _ Created Temp _____________________________________________________ Disk table 864.89k 2.0/s Table 7.06M 16.1/s Size: 32.0M File 9.22k 0.0/s
MySQL可以将临时表创建在磁盘（Disk table）、内存（Table）以及临时文件（File）中，显然，在磁盘上创建临时表的开销最大，所以我们希望MySQL尽量不要在磁盘上创建临时表。
在MySQL的配置中，我们可以通过tmp_table_size选项来设置用于存储临时表的内存空间大小，一旦这个空间不够用，MySQL将会启用磁盘来保存临时表，你可以根据mysqlreport的统计尽量给临时表设置较大的内存空间。
线程池 我们知道，MySQL采用多线程来处理并发的连接，通过mysqlreport中的Threads部分，我们可以看到线程创建的统计结果：
1 2 3 4 5 Threads _____________________________________________________ Running 2 of 5 Cached 0 of 0 %Hit: 0 Created 6.15M 43.6/s Slow 0 0/s
也许你会觉得创建线程的消耗不值一提，但是我们所谓优化都是在你系统繁忙下的救命稻草。
一个比较好的办法是在应用中尽量使用持久连接，这将在一定程度上减少线程的重复创建。另一方面，从上面的Cached=0可以看出，这些线程并没有被复用，我们可以在my.cnf中设置以下选项：thread_cache_size = 100
抛弃关系型数据库 我们应该知道，在关系型数据库里指导我们设计数据库表的时候可以根据范式来设计，我们一般会选择第三范式，简单地说，第三范式要求在一个数据表中，非主键字段之间不能存在依赖关系，这样一来，它可以避免更新异常、插入异常和删除异常，保证关系的一致性，并且减少数据冗余。
其实我们并不需要去刻意设计，只要你设计表的时候思维正常，大多数时候都是第三范式。
但是对于某些应用这样做可能会极大降低我们的性能
还有一个比较经典的例子：
这时候如果使用NOSQL来存储这些冗余的副本可能会给你带来意想不到的性能的提升。
下面是一些启发性优化策略：Mysql性能优化20+条经验
最后参考资料：