数据库性能优化(MySQL)

序&＃xff1a;

    即使有较长的缓存有效期和较理想的缓存命中率&＃xff0c;但是缓存的创建和缓存过期后的重建都是需要访问数据库的。对数据库写操作不是很容易引入缓存策略。

11.1 查看数据库状态

    可以通过show status、show innodb status 来查看MySQL数据库的状态&＃xff0c;使用mysqlreport这个第三方工具可使数据库状态报告更好看(mysqlreport本质是通过MySQL内部命令和工具来统计状态的)。

11.2 正确使用索引

    在影响数据库查询性能的众多因素中&＃xff0c;索引绝对是一个重量级的因素&＃xff0c;如果索引使用不当&＃xff0c;则数据库的其它优化可能无济于事。

    索引是用于快速定位到表记录所在地址的一种数据结构(BTree、Hash、RTree等)。通过索引去查找记录即为索引扫描。

    索引扫描不一定比全表扫描性能更好&＃xff0c;要看情况。查询优化器会为一次查询是否使用索引以及决定使用哪个索引&＃xff0c;当然&＃xff0c;有时查询优化器也会犯错误。

    数据库的索引需要定位到每行记录&＃xff0c;所有索引项的数量也会非常多&＃xff0c;通过索引列表查找某索引项也会存在一定的小开销。

    除了普通索引外还有唯一索引、主键索引、非空索引、全文索引等&＃xff0c;不同的索引只是约束和用途不一样。像唯一索引在插入数据时就会增加一定的开销&＃xff0c;因为它每插入一次都要判断是否重复。

    一般如果一个字段出现在查询语句基于行的选择、分组和排序&＃xff0c;那么为该字段建立索引可能是有价值的。

    explain只可分析查询语句&＃xff0c;不能用于分析更新操作的语句。

    在explain中&＃xff0c;若type为const&＃xff0c;说明查询可以通过索引直接找到匹配行&＃xff0c;key为PRIMARY说明使用了主键索引。若type为all,说明使用了全表扫描&＃xff0c;索引未使用上&＃xff0c;此时的key 为空。若type为ref&＃xff0c;说明查询的结果可能有多个匹配行。若type为index&＃xff0c;说明查询只需要在索引中扫描即可。

    一次查询对一个数据表只能使用一个索引&＃xff0c;不能进行索引效应叠加。

    最左前缀是使用组合索引的最基本原则。

    非顺序的索引类型如hash对order by是无效的。

    对于包含group by的查询&＃xff0c;数据库一般是先将记录分组后放到临时表中&＃xff0c;然后对其进行函数运算。这时若有恰当索引时&＃xff0c;可使用索引来代替临时表的使用。

    可以使用慢查询配置来记录查询慢的语句&＃xff0c;也可以记录未使用索引的查询语句。

    为了节省查找索引的时间&＃xff0c;可以将索引缓存起来放到内存中&＃xff0c;这样最理想的情况&＃xff0c;索引可以直接在内存中查找而不需要访问磁盘。MyISAM表包括3个文件&＃xff0c;分别是.frm、.MYI、.MYD。也即MyISAM表类型只缓存索引不缓存数据文件。由于存在索引写缓存机制&＃xff0c;MyISAM表类型对于索引的写操作存在延迟。

    在使用索引时也要考虑到其代价&＃xff0c;索引会占据更多的磁盘空间&＃xff0c;有时甚至比数据文件还要大。当在建立了索引的字段上进行更新时&＃xff0c;其索引也需要更新&＃xff0c;这个开销可不小。索引也需要花时间来维护。

11.3 锁定与等待

    锁机制是影响查询性能的另一个因素&＃xff0c;当多个并发用户同时访问同一资源时&＃xff0c;数据库为保证并发访问的一致性&＃xff0c;使用数据库锁来协调访问。

    查询时间的开销包括查询本身的计算时间和查询前的等待时间&＃xff0c;索引影响的是前者&＃xff0c;锁机制影响的是后者。

    MySQL为MyISAM表类型提供的是表锁。表锁允许多个线程同时读取数据(select)&＃xff0c;但对于更新操作会排斥所有其它的查询包括select&＃xff0c;而且更新操作具有默认的高优先级。

    如果大部分为查询操作&＃xff0c;只有少许更新操作&＃xff0c;则不会存在太多的锁等待。

    MySQL为InnoDB表类型提供的是行锁。行锁可以带来update和select不同线程对不同的行记录可以并发地进行。

    行锁并不一定比表锁快&＃xff0c;开销不一定比表锁小&＃xff0c;尤其是涉及全表扫描时行锁的开销更大。

11.4 事务性表的性能

    InnoDB除了支持行锁外&＃xff0c;它还支持事务&＃xff0c;InnoDB实现事务的方法是通过预写日志的方式。当有事务提交时&＃xff0c;InnoDB将它写入到内存的事务日志缓冲区中&＃xff0c;随后将事务日志写入磁盘&＃xff0c;从而更新实际的数据和索引。

    事务日志写入磁盘的时机&＃xff1a;

    innodb_flush_log_at_trx_commit&＃61;1时&＃xff0c;代表事务提交时事务日志立即写入磁盘&＃xff0c;同时更新数据和索引。

    innodb_flush_log_at_trx_commit&＃61;0时&＃xff0c;代表事务提交时事务日志不立即写入磁盘&＃xff0c;而是每隔1秒写入磁盘文件一次&＃xff0c;并刷新到磁盘同时更新数据和索引。

    innodb_flush_log_at_trx_commit&＃61;2时&＃xff0c;代表事务提交时事务日志立即写入磁盘文件&＃xff0c;每隔1秒刷新到磁盘同时更新数据和索引。

    通过以上3种时机可以对比出它们的可靠性和性能。

11.5 使用查询缓存

    查询缓存就是将select查询结果放在内存中&＃xff0c;key是select语句&＃xff0c;value是该查询语句的结果。不论是MyISAM还是InnoDB引擎&＃xff0c;查询缓存都可以很好地工作&＃xff0c;起到提升性能的作用。查询缓存要注意缓存过期策略&＃xff0c;在MySQL中&＃xff0c;若一个表中有更新操作&＃xff0c;则该表的所有查询缓存将失效。因此&＃xff0c;对于select密集型更新很少的应用很适合使用查询缓存。

11.6 临时表

    在explain查询语句时&＃xff0c;有时可以看到Using temporary状态&＃xff0c;这说明查询过程使用了临时表来存储中间数据&＃xff0c;可以通过合理使用索引来避免创建临时表情况。若临时表的使用不可避免&＃xff0c;那么也应该尽量减少临时表本身的开销。

    MySQL的临时表可以创建在磁盘、内存和临时文件中。当然&＃xff0c;创建在磁盘上的开销最大。有时在使用show processlist可以看到查询状态中有Coping to tmp table on disk&＃xff0c;这说明MySQL在将临时表从内存中复制到磁盘上以节省内存空间。

    可以通过tmp_table_size选项来设置用于存储临时表的内存空间大小。一旦空间不够用才会使用磁盘来存储。

11.7 线程池

    MySQL使用多线程来处理并发连接。为减少重复线程的创建可以尽量使用持久连接或将连接缓存起来(通过在my.cnf中配置thread_table_size&＃61;个数来设置)。

11.8 反范式设计

    所谓范式就是对关系数据库中的关系的要求或约束&＃xff0c;有不同程序的要求就有不同的范式。通常遵循到3NF即可&＃xff0c;3NF就是非主键字段之间不能存在依赖关系&＃xff0c;这样可以避免删除、更新、插入异常&＃xff0c;保持关系的一致性&＃xff0c;减少数据冗余。

    反范式化就是违背关系设计的要求或约束&＃xff0c;用于减少读取数据的开销&＃xff0c;增加一定的数据冗余&＃xff0c;但这样同时也增加了写数据的开销&＃xff0c;因为要保持冗余数据的一致性。当然&＃xff0c;为了保证数据库写性能可以异步写数据。若不想反范式则可以使用非关系型数据库。

11.9 使用非关系数据库

    key-value数据库使用半结构化存储数据&＃xff0c;所有数据只有一个索引即key&＃xff0c;可以将反范式化引发的数据副本保存到key-value数据库中&＃xff0c;这样比关系数据库具有更出色的并发性能。

    MemcacheDB在性能方面比较出色&＃xff0c;是一个分布式的key-value数据库&＃xff0c;使用Memcache协议&＃xff0c;这意味着使用了Memcache的web应用可以不进行任何的修改而迁移到MemcacheDB上。

    不是所有的应用都适合用key-value数据库&＃xff0c;该用关系查询的时候还是得用关系数据库&＃xff0c;key-value数据库只是为避免反范式化引发的写数据开销方案之一。当然&＃xff0c;MemcacheDB封装了Berkeley DB的复制功能&＃xff0c;可以通过主从复制来扩展MemcacheDB的规模&＃xff0c;提升可用性。