首页»武松娱乐»MySQL使用与优化总结

MySQL使用与优化总结

来源:豌豆 发布时间:2013-10-22 阅读次数:
  这篇文章总结了工作中用到MySQL的一些常见问题,解决方案;合适的使用场景和优化方案。

 存储引擎的选择:MyISAM vs InnoDB

MyISAM:支持全文索引;使用表级锁;读并发性能好。 InnoDB:支持事务和外键;使用行级锁;写并发性能较好。   在实际应用场景中,我们一般都使用InnoDB作为默认的存储引擎,除了支持事务和行锁是比较重要的两个原因外,其实MyISAM在实际应用场景中意义也不大,看看下面几个原因:
  1. 全文索引完全可以(也应该)用第三方软件来替代,比如:Sphinx;
  2. 读性能高的特点完全可以用前端缓存来替代,这已经是互联网应用的标配了;
  3. 表级锁在并发写操作多时会严重影响读操作(写优先);

 使用与优化

  DB的优化

  1. 建立合适的索引: 尽量让所有查询都走索引,这个效果是很明显的。
  2. 表空间优化: 在删除或更新比较频繁的表上,如果包含varchar,text之类的字段,需要定期地执行表空间优化,optimaize table xxx,整理磁盘碎片,回收表数据和索引数据占用的空闲空间;
  3. 配置参数优化: innodb_buffer_pool_size innodb表数据和索引数据的内存缓冲大小,很关键,可以有效减少磁盘IO。 innodb_flush_log_at_trx_commit 决定事务日志怎么记录,这个对性能提升也很关键,在线下批量写数据时可以考虑设置为0.或者写操作频繁但允许故障时丢失极少量数据的情况也可以考虑。 query_cache 这个参数有些微妙,因为query cache在数据表中有任何数据修改时就会失效,对于写操作频繁的表来说,有可能还会降低性能。对于读操作为主的表来说,效果还是很明显的,但是通常场景下我们都依赖于前端缓存,所以对于这个参数的设置来说,还要看具体业务场景。 max_connections 控制并发连接数,不能太大,否则后果很严重。
  4. 拆分与扩容: 库拆分:一般是把同一实例上的武松娱乐分到多个实例上来分担压力(这种比较简单,做一份复制,应用端改个ip就行),或者是把一个库里面的部分表单独放到另一个实例库中(这种比较麻烦,需要应用端配合修改程序)。 表拆分:也分两种,一种是把一些字段的拆出到新表里,比如按业务分,或者是像text之类的大字段拆分。另一种是表记录数太大,超出了单表承受能力,需要水平扩展到多张表。表拆分比较麻烦,都需要应用端配合修改程序。

  SQL的优化

  1. 尽量用上索引,能用主键查询最好了
  2. 尽量缩小扫描范围,经典场景就是limit分页偏移量的优化,其实在实际业务场景下很有很多类似的场景,我们完全可以按id号或者时间限制来显著缩小查询扫描范围
  3. 尽量减少表连接查询,最好是单表查询(表连接可能用上临时表,对DB消耗很大;而单表查询可以快速返回,把计算操作放到前端应用去做,减少DB压力)。如果前端并发没有控制好的话,性能较差的表连接查询可能会拖死DB
  4. 尽量做等值查询,不等条件查询和逆向查询不走索引
  5. 用union替代or、in操作,后面两个不走索引
  6. 不做前置模糊查询,不走索引
  7. 排序和分组操作尽量在应用端做,减少DB的CPU压力
  8. 在查询列上不作函数运算:select concat('foo','bar') as str from xxx;

  应用的优化

  1. 尽量不在DB端做运算,能在应用端做的事就不依赖DB
  2. text/blob之类的数据尽量不在DB中存储,可以采用其它key/value型的存储
  3. 大sql拆分成小sql查,不做表连接
  4. 用好连接池,减少连接开销(这里要注意连接池的空闲时间与武松娱乐空闲时间的配置)
  5. 有冷热数据的场景,尽量均摊压力
  6. 考虑读写分离(这里要注意slave的延时,master写频繁的情况下,slave延时也是另人很头疼的,对数据一致性敏感的应用场景是有隐患的)
  7. 最后提一下,preparedstatement,最大的作用是防止SQL注入。预编译功能也可以尝试使用,但是要开启前端sql缓存才好,这个还是看具体应用场景吧,大多数互联网应用还没到靠这个功能来提升性能的情况。

 简单故障排查技巧

  慢查询排查

  1. 日志查看:slow.log,这个是mysql配置文件里设置的,要开启。
  2. 实时查看:select * from information_schema.processlist where time > 2; 处理方式:
    1. 慢查询日志分析工具:mysqlsla,mysqldumpslow等,对症下药进行SQL优化;
    2. 实时的慢查询如果影响了应用的响应,可以直接kill掉查询线程。执行kill [thread_id]即可。

  Lock情况排查

  确定武松娱乐有锁住情况看两个地方,在processlist中可以看到state那一列有lock相关的状态,这里只能看到一个状态,最主要是通过下面这个命令来查看show innodb engine status,这里会显示详细的锁和事务发生的信息。至于怎么解决,要看应用端怎么来控制了。

  Slave延时排查

  在slave实例上执行show slave status查看slave的状态,主要关注以下三个: Slave_IO_Running: Yes // 负责读取binlog的线程是否正常运行 Slave_SQL_Running: Yes // 负责在slave上执行sql的线程是否正常运行 Seconds_Behind_Master: 0 // slave比master延时多长时间,单位:秒   如果出现IO和SQL线程状态为No的情况,那说明slave同步已经停止了,可以通过Last_Error这个看到最近的错误。如果要恢复slave,一般两种操作:一是重做slave,保证数据更准确;一种是跳过出错的sql,stop slave;set global sql_slave_skip_counter=1;start slave;,这是跳过一条sql,也可跳过多条,这种方式可能导致slave数据不一致。

 监控

  内置命令

status show global status show variables

  外部监控

  第三方的监控工具,可以提供图形化的界面。cacti,ganglia等开源软件都提供了监控mysql的插件。

 简单说说mysql高可用

  两种方式:   方式一:使用MySQL Cluster:读扩展性好,写性能会有一定下降。不是很成熟,线上慎用。   方式二:Master + Slave配合虚拟IP + LVS + keepalived实现简单的高可用,这种方案的隐患就是:虚拟ip切换间隙会有短暂不可用;slave提升到master会有失败的情况;

 最后

  武松娱乐一般都存储了应用的关键数据,可以说是一个公司产品的生命,所以数据的安全也非常重要,要做好权限控制(严格控制权限,尽量防止误操作造成数据丢失),及时备份数据(异地,多机房),对于核心敏感数据还要做好保密工作。
QQ群: WEB武松娱乐官方总群(83010142) 加群密码:关注下方微信公众号,发送消息 mm 获取
提示:更多精彩内容关注微信公众号:全栈武松娱乐中心(fsder-com)
网友评论(共0条评论) 正在载入评论......
理智评论文明上网,拒绝恶意谩骂 发表评论 / 共0条评论
登录会员中心
武松娱乐官网