MySQL 优化

mysql服务器和配置优化

• 存储层：存储引擎、字段类型选择、范式设计
• 设计层：索引、缓存、分区(分表)
• 架构层：多个mysql服务器设置，读写分离(主从模式)
• sql语句层：多个sql语句都可以达到目的的情况下，要选择性能高、速度快的sql语句

存储层

存储引擎

• innodb存储引擎：适合做修改、删除，并发性高，行锁，支持事务
• Myisam存储引擎：适合做查询、写入

字段类型选择

• 占据空间小、数据长度最好固定、数据内容最好为整型的

设计层

缓存

如果该sql语句被频繁执行获得数据(这些数据还不经常发生变化)，为了使得每次获得的信息速度较快，就可以把“执行结果”给缓存起来，供后续的每次使用

分表

水平分表

单个表中数据太多，将单个数据库表进行拆分，拆分成多个数据表，然后用户访问的时候，根据一定的算法，让用户访问不同的表，这样数据分散到多个数据表中，减少了单个数据表的访问压力。提升了数据库访问性能。

垂直分表

举例说明，在一个博客系统中，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。

• 存储引擎的使用不同，冷数据使用MyIsam 可以有更好的查询数据。活跃数据，可以使用Innodb ,可以有更好的更新速度。
• 对冷数据进行更多的从库配置，因为更多的操作是查询，这样来加快查询速度。对热数据，可以相对有更多的主库的横向分表处理。
• 对于一些特殊的活跃数据，也可以考虑使用memcache ,redis之类的缓存，等累计到一定量再去更新数据库.

服务器调整优化

• 关闭不必要的二进制日志和慢查询日志，仅在内存足够或开发调试时打开，慢查询会消耗过度消耗CPU，可以间歇性打开慢查询日志来定位新能瓶颈
• 因为生产环境中，数据库大多都是读操作，所以部署一主多从架构，主数据库负责写操作，并做双击热备，多台从数据库做负载均衡，负责读操作，主流的负载均衡器有LVS、HAProxy、Nginx。怎么来实现读写分离呢？大多数企业是在代码层面实现读写分离，效率比较高

服务器性能监控

super-smack：压力测试工具

• 连接数
• QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数
• TPS，Transactions Per Second：每秒处理事务数

通过show status查看运行状态，会有300多条状态信息记录，其中有几个值帮可以我们计算出QPS和TPS，如下：

• Uptime：服务器已经运行的实际，单位秒
• Questions：已经发送给数据库查询数
• Com_select：查询次数，实际操作数据库的
• Com_insert：插入次数
• Com_delete：删除次数
• Com_update：更新次数
• Com_commit：事务次数
• Com_rollback：回滚次数

那么，计算方法来了，基于Questions计算出QPS：

mysql> show global status like ``'Questions'``;

mysql> show global status like ``'Uptime'``;

  QPS = Questions / Uptime

  基于Com_commit和Com_rollback计算出TPS：

mysql> show global status like ``'Com_commit'``;

mysql> show global status like ``'Com_rollback'``;

mysql> show global status like ``'Uptime'``;

  TPS = (Com_commit + Com_rollback) / Uptime

  另一计算方式：基于Com_select、Com_insert、Com_delete、Com_update计算出QPS

mysql> show global status where Variable_name ``in``(``'com_select'``,``'com_insert'``,``'com_delete'``,``'com_update'``);

  等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPS

  TPS计算方法：

mysql> show global status where Variable_name ``in``(``'com_insert'``,``'com_delete'``,``'com_update'``);

  计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。

  经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com_*计算比较准确。

性能优化
数据库中查询记录时是否每次只能使用一个索引？
分表和分区的区别

本作品采用《CC 协议》，转载必须注明作者和本文链接