文档章节

数据库性能优化(MySQL)

l
 liuhao_sh
发布于 2015/08/17 12:02
字数 2278
阅读 34
收藏 1

序:

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

11.1 查看数据库状态

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

11.2 正确使用索引

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

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

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

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

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

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

    explain只可分析查询语句,不能用于分析更新操作的语句。

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

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

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

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

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

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

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

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

11.3 锁定与等待

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

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

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

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

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

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

11.4 事务性表的性能

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

    事务日志写入磁盘的时机:

    innodb_flush_log_at_trx_commit=1时,代表事务提交时事务日志立即写入磁盘,同时更新数据和索引。

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

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

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

11.5 使用查询缓存

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

11.6 临时表

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

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

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

11.7 线程池

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

11.8 反范式设计

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

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

11.9 使用非关系数据库

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

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

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


© 著作权归作者所有

共有 人打赏支持
l
粉丝 1
博文 77
码字总数 102707
作品 0
徐汇
MySQL数据库的性能的影响分析及其优化

MySQL数据库的性能的影响 一. 服务器的硬件的限制 二. 服务器所使用的操作系统 三. 服务器的所配置的参数设置不同 四. 数据库存储引擎的选择 五. 数据库的参数配置的不同 六. (重点)数据库的...

Panda_Jerry
2017/11/04
0
0
读《面向程序员的数据库访问性能优化法则》

刚刚看了一下2014年中国数据库技术大会(DTCC)PPT,网易杭州研究院的一位同学介绍了一下数据库的优化与调优。 网易的Mysql优化做的很好,而且还写了本书和大家分享这些经验《深入浅出MySQL...

ifeixiang
2015/01/04
0
0
Mysql:优化分析(值得深思)

Mysql优化分析 Mysql作为现在的主流关系型数据库,在优化上值得深思啊! 不能只停留在如何使用Mysql,而不去思考如何优化的问题啊。 在谈到Mysql优化方面,Mysql在这方面有专门的文档说明这个...

王木东
07/13
0
0
你曾错过的2017十大MVP精彩好文,今天Repo回来!

“2017年度十大MVP评选”已圆满落幕,再次感谢这10位获奖专家及团队为知识传播的努力、对技术分享的热忱、为行业发展的贡献,在此将他们这一年来分享过的干货好文遴选出来,以便大家温故知新...

DBAplus社群
2017/11/20
0
0
MySQL 5.6 有什么新特性

昨天 MySQL 发布了 5.6.2 测试版本,详情请看这里。 今天我们来详细介绍一下 MySQL 5.6 有或者将会有什么样的改进。 MySQL 5.6 主要在查询性能的优化、InnoDB改进以支持高吞吐量的事务、NoS...

红薯
2011/04/13
1K
1

没有更多内容

加载失败,请刷新页面

加载更多

可爱的python测试开发库(python测试开发工具库汇总)

欢迎转载,转载请注明来源: github地址 谢谢点赞 本文地址 相关书籍下载 测试开发 Web UI测试自动化 splinter - web UI测试工具,基于selnium封装。 链接 selenium - web UI自动化测试。 链...

python测试开发人工智能安全
52分钟前
2
0
Shiro | 实现权限验证完整版

写在前面的话 提及权限,就会想到安全,是一个十分棘手的话题。这里只是作为学校Shiro的一个记录,而不是,权限就应该这样设计之类的。 Shiro框架 1、Shiro是基于Apache开源的强大灵活的开源...

冯文议
今天
1
0
linux 系统的运行级别

运行级别 运行级别 | 含义 0 关机 1 单用户模式,可以想象为windows 的安全模式,主要用于修复系统 2 不完全的命令模式,不含NFS服务 3 完全的命令行模式,就是标准的字符界面 4 系统保留 5 ...

Linux学习笔记
今天
2
0
学习设计模式——命令模式

任何模式的出现,都是为了解决一些特定的场景的耦合问题,以达到对修改封闭,对扩展开放的效果。命令模式也不例外: 命令模式是为了解决命令的请求者和命令的实现者之间的耦合关系。 解决了这...

江左煤郎
今天
3
0
字典树收集(非线程安全,后续做线程安全改进)

将500W个单词放进一个数据结构进行存储,然后进行快速比对,判断一个单词是不是这个500W单词之中的;来了一个单词前缀,给出500w个单词中有多少个单词是该前缀. 1、这个需求首先需要设计好数据结...

算法之名
昨天
15
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部