专栏首页Golang语言社区数据库性能优化(MySQL)

数据库性能优化(MySQL)

序:

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

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的规模,提升可用性。

本文分享自微信公众号 - Golang语言社区(Golangweb),作者:tujiyue

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-09-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • [译]使用 Go 语言读写Redis协议

    原文: Reading and Writing Redis Protocol in Go 翻译整理: smallnest, 译文连接: 使用 Go 语言读写Re...

    李海彬
  • Go语言创建、初始化数组的各种方式

    //数组初始化的各种方式 func arraySliceTest0201() { //创建数组(声明长度) var array...

    李海彬
  • Golang语言学习-并发

    goroutine: 由GO运行环境管理的轻量级线程 channel: 有类型的管道,操作符为 <- 数据流向箭头指向的方向 使用make(chan ...

    李海彬
  • [别被脱裤系列]2 还没深入数据库就浅出了

    (3) 索引列处于不同的位置对索引影响比较大。比如在WHERE子句中,对索引字段进行计算会造成索引失效。

    我是程序员小贱
  • 微服务化的数据库设计与读写分离

    数据库永远是应用最关键的一环,同时越到高并发阶段,数据库往往成为瓶颈,如果数据库表和索引不在一开始就进行良好的设计,则后期数据库横向扩展,分库分表都会遇到困难。

    互扯程序
  • MySQL索引事务

    索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现。

    海盗船长
  • MySQL优化总结

    存储引擎:MySQL中的数据、索引以及其他对象是如何存储的,是一套文件系统的实现。

    KEN DO EVERTHING
  • 单机数据库优化的一些实践

    数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。...

    哲洛不闹
  • 数据优化查询的意义

    数据通20847430
  • 数据 优化查询的目的

    数据通

扫码关注云+社区

领取腾讯云代金券