MySQL使用与优化总结

蔚落 2022-08-26 15:24 161阅读 0赞

# MySQL使用与优化总结 #

http://my.oschina.net/u/142836/blog/169415\#OSC\_h4\_7[点击打开链接][Link 1]

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

[mysql][] [ 使用][Link 2] [ 优化][Link 3] [ 总结][Link 4]

目录\[-\]

*  [存储引擎的选择：MyISAM vs InnoDB][MyISAM vs InnoDB]
 *  [使用与优化][Link 5]
 *  [DB的优化][DB]
 *  [SQL的优化][SQL]
 *  [应用的优化][Link 6]
 *  [简单故障排查技巧][Link 7]
 *  [慢查询排查][Link 1]
 *  [Lock情况排查][Lock]
 *  [Slave延时排查][Slave]
 *  [监控][Link 8]
 *  [内置命令][Link 9]
 *  [外部监控][Link 10]
 *  [简单说说mysql高可用][mysql 1]
 *  [最后][Link 11]

## 存储引擎的选择：MyISAM vs InnoDB ##

[view source][]

[print][] [?][Link 12]

<table> 
 <tbody> 
  <tr> 
   <td><code><span style="color:#ff0000">1</span></code></td> 
   <td><code><span style="color:#ff0000">MyISAM：支持全文索引；使用表级锁；读并发性能好。</span></code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code><span style="color:#ff0000">3</span></code></td> 
   <td><span style="color:#ff0000"><code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>InnoDB：支持事务和外键；使用行级锁；写并发性能较好。</code></span></td> 
  </tr> 
 </tbody> 
</table>

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

*  全文索引完全可以（也应该）用第三方软件来替代，比如：Sphinx；
 *  读性能高的特点完全可以用前端缓存来替代，这已经是互联网应用的标配了；
 *  表级锁在并发写操作多时会严重影响读操作（写优先）；

## 使用与优化 ##

#### DB的优化 ####

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

#### 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的状态，主要关注以下三个：

[view source][]

[print][] [?][Link 12]

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>Slave_IO_Running: Yes </code><code>// 负责读取binlog的线程是否正常运行</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>2</code></td> 
   <td><code>Slave_SQL_Running: Yes </code><code>// 负责在slave上执行sql的线程是否正常运行</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>3</code></td> 
   <td><code>Seconds_Behind_Master: </code><code>0</code> <code>// slave比master延时多长时间，单位：秒</code></td> 
  </tr> 
 </tbody> 
</table>

如果出现IO和SQL线程状态为No的情况，那说明slave同步已经停止了，可以通过`Last_Error`这个看到最近的错误。如果要恢复slave，一般两种操作：一是重做slave，保证数据更准确；一种是跳过出错的sql，`stop slave;set global sql_slave_skip_counter=1;start slave;`，这是跳过一条sql，也可跳过多条，这种方式可能导致slave数据不一致。

## 监控 ##

#### 内置命令 ####

[view source][]

[print][] [?][Link 12]

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>status</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>2</code></td> 
   <td><code>show global status</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><code>3</code></td> 
   <td><code>show variables</code></td> 
  </tr> 
 </tbody> 
</table>

#### 外部监控 ####

[view source][]

[print][] [?][Link 12]

<table> 
 <tbody> 
  <tr> 
   <td><code>1</code></td> 
   <td><code>第三方的监控工具，可以提供图形化的界面。cacti,ganglia等开源软件都提供了监控mysql的插件。</code></td> 
  </tr> 
 </tbody> 
</table>

## 简单说说mysql高可用 ##

两种方式：

方式一：使用MySQL Cluster：读扩展性好，写性能会有一定下降。不是很成熟，线上慎用。

方式二：Master + Slave配合虚拟IP + LVS + keepalived实现简单的高可用，这种方案的隐患就是：虚拟ip切换间隙会有短暂不可用；slave提升到master会有失败的情况；

## 最后 ##

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

[Link 1]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_7
[mysql]: http://www.oschina.net/search?scope=blog&q=mysql
[Link 2]: http://www.oschina.net/search?scope=blog&q=%E4%BD%BF%E7%94%A8
[Link 3]: http://www.oschina.net/search?scope=blog&q=%E4%BC%98%E5%8C%96
[Link 4]: http://www.oschina.net/search?scope=blog&q=%E6%80%BB%E7%BB%93
[MyISAM vs InnoDB]: http://my.oschina.net/u/142836/blog/169415#OSC_h2_1
[Link 5]: http://my.oschina.net/u/142836/blog/169415#OSC_h2_2
[DB]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_3
[SQL]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_4
[Link 6]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_5
[Link 7]: http://my.oschina.net/u/142836/blog/169415#OSC_h2_6
[Lock]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_8
[Slave]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_9
[Link 8]: http://my.oschina.net/u/142836/blog/169415#OSC_h2_10
[Link 9]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_11
[Link 10]: http://my.oschina.net/u/142836/blog/169415#OSC_h4_12
[mysql 1]: http://my.oschina.net/u/142836/blog/169415#OSC_h2_13
[Link 11]: http://my.oschina.net/u/142836/blog/169415#OSC_h2_14
[view source]: http://my.oschina.net/u/142836/blog/169415#viewSource
[print]: http://my.oschina.net/u/142836/blog/169415#printSource
[Link 12]: http://my.oschina.net/u/142836/blog/169415#about