MySQL性能优化(二)

  • 2016-01-06
  • 918
  • 1

mysql

运维与架构优化

1)存储引擎使用

  • MySQL常用有两种存储引擎,一个是MyISAM,不支持事务处理,读性能处理快,表级别锁(属于较为致命的问题)。
  • 另一个是InnoDB,是目前主流的存储引擎(MySQL5.5以后版本中默认存储引擎),支持事务处理(ACID),设计目标为处理大容量数据发挥最大化性能,行级别锁。行级锁会带来更新的额外开销,但是通常情况下是值得的。

                 表锁:开销小,锁定粒度大,发生死锁概率高,相对并发也低。

                 行锁:开销大,锁定粒度小,发生死锁概率低,相对并发也高。

2)硬件配置内存优化

  • 加大物理内存,提高文件系统性能。linux内核会从内存中分配出缓存区(系统缓存和数据缓存)来存放热数据。也就是说物理内存越大,分配缓存区越大,缓存数据越多。所以理论上,内存越大,越多数据读取发生在内存,效率越高。
  • Query cache的使用
    • 如果前端请求重复度不高,或者应用层已经充分缓存重复请求,query cache不必设置很大,甚至可以不设置。
    • 如果前端请求重复度较高,无应用层缓存,query cache是一个很好的偷懒选择。
      • 对于中等以下规模数据库应用,偷懒不是一个坏选择。
      • 如果确认使用query cache,记得定时清理碎片,flush query cache.

3)分库&分表

  • 用分库&拆表是解决数据库容量问题的唯一途径。
  • 分库&拆表也是解决性能压力的最优选择。
  • 分库 –不同的数据表放到不同的数据库服务器中(也可能是虚拟服务器)
  • 拆表 –一张数据表拆成多张数据表,可能位于同一台服务器,也可能位于多台服务器(含虚拟服务器)。
  • 分表分为垂直拆分和水平拆分:
    垂直拆分:把原来的一个很多字段的表拆分多个表,解决表的宽度问题。你可以把不常用的字段单独放到一个表中,也可以把大字段独立放一个表中,或者把关联密切的字段放一个表中。
    水平拆分:把原来一个表拆分成多个表,每个表的结构都一样,解决单表数据量大的问题。

4)分区

  • 为什么要使用分区:
    假设表中包含多年的历史数据,数据是按照时间排序的,当我们查询近几个月的数据,大约有10亿记录。当表中数据远大于内存,并且使用的是传统硬盘。
    此时数据量过大,无法在每次查询的时候都扫描全表。且B-Tree索引也不起作用了。因此该场景就可用到分区。分区后,每个分区有自己单独的名称。对于DBA来讲,这些分区可以统一管理,也可以分开管理。
    但是对于应用程序来讲,分区的表和没有分区的表示一样的。换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理。理解分区时还可以将其当作索引的最初形态,以代价非常小的方式定位到需要的数据到哪一片“区域”。在这片“区域中”,此时可以做顺序扫描,可以建索引,还可以将数据都缓存到内存等。
  • 分区表原理:
    当select一个分区表时,分区层先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据。
    inesert、delete、update与以上类似,都是分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行数据操作。
  • 分区表类型:
    分区表分为RANGE,LIST,HASH,KEY四种类型
    创建分区表:
    CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL,
    PRIMARY KEY(id, order_day)
    ) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE);这段语句表示将表内数据按照order_dy的年份范围进行分区,2010年一个区,2011一个,2012一个,剩下的一个.
    要注意如果这么做,则order_day必须包含在主键中,且会产生一个问题,就是当年份超过阈值,到了2013,2014时,需要手动创建这些分区
    替代方法就是使用HASH
    CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DOUBLE NOT NULL,
    order_day DATETIME NOT NULL
    ) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);
    这种分区表示每100W条数据建立一个分区,且没有阈值范围的影响

5)主从复制与读写分离

6)缓存方案

  • 给数据库增加缓存系统,把热数据缓存到内存中,如果内存缓存中有要请求的数据就不再去数据库中返回结果,提高读性能。
  • 主流的分布式缓存系统有memcached、redis。关于这两者的搭建和使用,后面应另设文章讲述。
  • 并不是所有数据都适合被缓存,也并不是进入了缓存就意味着效率提升。命中率是第一要评估的数据。

如:前端请求先连接缓存,缓存未命中连接数据库,进行查询,未命中状态比单纯连接数据库查询多了一次连接和查询的操作;如果缓存命中率很低,则这个额外的操作非但不能提高查询效率,反而为系统带来了额外的负载和复杂性,得不偿失。

 

参考链接:

运维角度浅谈MySQL数据库优化

 


上一篇:  下一篇:

评论

还没有任何评论,你来说两句吧

Copyright © 2014-2016 lxlxw All Right Reserved