MySQL使用与优化总结_MySQL

MySQL优化

　　这篇文章总结了工作中用到MySQL的一些常见问题，解决方案；合适的使用场景和优化方案。

　存储引擎的选择：MyISAM vs InnoDB

    MyISAM：支持全文索引；使用表级锁；读并发性能好。

    InnoDB：支持事务和外键；使用行级锁；写并发性能较好。

　　在实际应用场景中，我们一般都使用InnoDB作为默认的存储引擎，除了支持事务和行锁是比较重要的两个原因外，其实MyISAM在实际应用场景中意义也不大，看看下面几个原因：

• 全文索引完全可以（也应该）用第三方软件来替代，比如：Sphinx；

• 读性能高的特点完全可以用前端缓存来替代，这已经是互联网应用的标配了；

• 表级锁在并发写操作多时会严重影响读操作（写优先）；

　使用与优化

　　DB的优化

• 建立合适的索引：

  尽量让所有查询都走索引，这个效果是很明显的。

• 表空间优化：

  在删除或更新比较频繁的表上，如果包含varchar,text之类的字段，需要定期地执行表空间优化,optimaize table xxx，整理磁盘碎片，回收表数据和索引数据占用的空闲空间;

• 配置参数优化：

  innodb_buffer_pool_size  innodb表数据和索引数据的内存缓冲大小，很关键，可以有效减少磁盘IO。
  innodb_flush_log_at_trx_commit 决定事务日志怎么记录，这个对性能提升也很关键，在线下批量写数据时可以考虑设置为0.或者写操作频繁但允许故障时丢失极少量数据的情况也可以考虑。
  query_cache 这个参数有些微妙，因为query cache在数据表中有任何数据修改时就会失效，对于写操作频繁的表来说，有可能还会降低性能。对于读操作为主的表来说，效果还是很明显的，但是通常场景下我们都依赖于前端缓存，所以对于这个参数的设置来说，还要看具体业务场景。
  max_connections 控制并发连接数，不能太大，否则后果很严重。

• 拆分与扩容：

  库拆分：一般是把同一实例上的数据库分到多个实例上来分担压力（这种比较简单，做一份复制，应用端改个ip就行），或者是把一个库里面的部分表单独放到另一个实例库中（这种比较麻烦，需要应用端配合修改程序）。
  表拆分：也分两种，一种是把一些字段的拆出到新表里，比如按业务分，或者是像text之类的大字段拆分。另一种是表记录数太大，超出了单表承受能力，需要水平扩展到多张表。表拆分比较麻烦，都需要应用端配合修改程序。

　　SQL的优化

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

　　应用的优化

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

　简单故障排查技巧

　　慢查询排查

• 日志查看：slow.log，这个是mysql配置文件里设置的，要开启。

• 实时查看：select * from information_schema.processlist where time > 2;

  处理方式：

  • 慢查询日志分析工具：mysqlsla,mysqldumpslow等，对症下药进行SQL优化；
  • 实时的慢查询如果影响了应用的响应，可以直接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会有失败的情况；

　最后

　　数据库一般都存储了应用的关键数据，可以说是一个公司产品的生命，所以数据的安全也非常重要，要做好权限控制（严格控制权限，尽量防止误操作造成数据丢失），及时备份数据（异地，多机房），对于核心敏感数据还要做好保密工作。