mysql

优化Mysql Server

查看Mysql Server参数

mysql服务启动后我们可以通过show variables和show status命令查看mysql服务器静态参数值和动态运行状态信息。

其中前者是在数据库中启动后不会动态更改的值，比如缓冲区大小，字符集，数据文件名称等；

后者是数据库运行期间的动态变化的信息，比如锁等待，当前连接数等。

如果需要了解某个参数的详细定义，可以使用以下命令

1
mysql --verbose --help｜more

下面介绍的参数“key_buffer_size“ 和 ”table_cache“适用于myISAM存储引擎

后面介绍的适用于innoDB存储引擎

key_buffer_size

索引缓存的大小

table_cache

这个参数表示数据库用户打开表的缓存数量。

innodb_buffer_pool_size

mysqld中对于innodb_buffer_pool_size参数的定义如下，The size of the memory buffer InnoDB uses to cache data and indexes of its tables.

这个参数定义了innodb最大的缓存的大小和索引大小。这个值设置的越大，访问表中的数据的磁盘io就越少，在一个专用的数据库服务器上，可以设置这个参数达机器物理内存大小的百分之八十。不建议将这个参数设置的太大，因为对于物理内存的竞争可能在操作系统上导致内存调度。

innodb_flush_log_at_trx_commit

在mysqld中对于innodb_flush_log_at_trx_commit这个参数的定义如下Set to 0 (write and flush once per second), 1 (write and flush at each commit) or 2 (write at commit, flush once per second).

innodb_flush_log_at_trx_commit 参数的默认值是1，也是最安全的设置，即每个事务提交的时候都会从 log buffer 写到日志文件，而且会实际刷新磁盘，但是这样性能有一定的损失。 如果可以容忍在数据库崩溃的时候损失一部分数据， 那么设置成0 或者2都会有所改善。设置成 0，则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失 1 秒钟的事务，这种方式是最不安全的，也是效率最高的。设置成 2 的时候，因为只是没有刷新到磁盘，但是已经写入日志文件，所以只要操作系统没有崩溃，那么并没有丢失数据，比设置成 0 更安全一些。

innodb_support_xa

通过该参数设置是否支持分布式事务，默认值是on 或者1，表示支持分布式事务，如果确认应用中不需要使用分布式事务，则可以关闭这个参数，减少磁盘刷新的次数

innodb_log_buffer_size

磁盘io问题

作为应用系统的持久化层，不管数据库采取了什么样的cache机制，但数据库最终总是要将数据储存到可以长久保存的io设备–磁盘上，但磁盘的存取速度显然比cpu，ram的速度慢很多。对于比较大的数据库，磁盘的io总是成为一个数据库的性能瓶颈。

使用磁盘阵列

RAID（redundant Array of Inexpensive Disks)的缩写，通常叫做磁盘阵列，RAID就是按照一定策略将数据分布到若干物理磁盘上，这样不仅增强了数据存储的可靠性，同时可以提高数据读写的整体性能，因为通过分布实现了数据的并行读写。

常见的RAID级别以及特性

  1. RAID 0 也叫做条带化，按一定的条带大小将数据依次分布到各个磁盘，没有数据冗余

  2. RAID 1 也叫做磁盘镜像，将两个磁盘分为一组，所有数据都同于写入两个磁盘，但读的时候可以从任意一个磁盘读都可以

  3. RAID 10是RAID 0 和RAID 1的结合，先对于磁盘做镜像，再进行条带化，使其达到RAID 1 的可靠性和RAID 0的性能良好性能

  4. RAID 4，像RAID0一样进行条带化，但是不同的是需要新增一个磁盘，用来写Stripe的校验纠错数据

  5. RAID 5，对于RAID4的改进，将一个条带的校验纠错数据块也分布写到各个磁盘块儿上，而不是写到一个特定的磁盘上

如何选择RAID级别

了解各种 RAID 级别的特性后，我们就可以根据数据读写的特点、可靠性要求，以及投资预算等来选择合适的 RAID 级别，比如：

  1. 数据读写都很频繁，可靠性要求也很高，最好选择 RAID 10；

  2. 数据读很频繁，写相对较少，对可靠性有一定要求，可以选择 RAID5；

  3. 数据读写都很频繁，但可靠性要求不高，可以选择 RAID 0。

使用Symbolic Links进行分布io

mysql的数据库名称和表名称与文件系统中的目录名和文件名是对应的，默认情况下，创建的数据库和表都存放在参数datadir定义的目录下，这样如果不适用RAID或者逻辑卷，所有的表都存放在一个磁盘上，那么就无法发挥多磁盘并行写的优势，在这种情况下，我们就可以使用操作系统中的symbolic Links 将不同的数据库或者表，索引指向不同的物理磁盘。

将一个数据库指向不同的磁盘

1
2
mkidir test
ln -s test path/to/datadir

禁止操作系统更新文件的atime属性

atime是linux/unix系统下的一个文件属性，每当读取文件时，操作系统都会将读操作发生的时间回写到磁盘上，对于读写频繁的数据库文件来说，记录文件的访问时间一般没有任何用处，却会增加磁盘系统的负担，影响io性能，因此可以通过设置文件系统的mount属性，组织操作系统写atime信息，以减轻磁盘io的负担

用裸设备（RAW Device）存放InnoDB的共享表空间

在innodb缓存充足的情况下，可以考虑使用Raw Device来存放InnoDb共享表空间

一般的步骤如下：

  1. 修改mysql配置文件，在innodb_data_file_path参数重增加裸设备文件名并且指定newraw属性

  2. 启动mysql，使其完成分区初始化工作，然后关闭mysql，此时还不能创建或者修改InnoDb表。

  3. 将innodb_data_file_path中的newraw改成raw

  4. 重新启动就可以开始使用

应用优化

实际生产环境中，由于数据库服务器本身的性能局限，就必须对于前台的应用来进行一些优化，使得前台访问数据库的压力能够减到最小

使用连接池

对于访问数据库来说，建立连接的代价比较昂贵，因此我们有必要建立连接池来提高访问的性能，因为池子里的资源都已经预先创建好了，可以直接分配给应用使用，因此大大减少了创建新连接所耗费的资源，连接返回后，本次访问将连接交还给“连接池“，以供新的访问使用。

减少对于mysql的访问

避免对于同一个数据做重复检索

比如如果需要检索一个人的年龄和性别，那么就可以执行以下查询

1
select old,gender from users where userid = 123

同时第二次查询需要查询这个人的家庭地址那么我们可以

1
select address from users where userid = 123

其实上面的两个sql语句一遍就可以进行汇总结果，节省了查询的开销。

使用查询缓存

mysql的查询缓存的作用是进行储存select查询的文本以及结果，如果随后收到一个相同的查询，服务器回查询缓存中重新获得查询结果。而不是解析和执行查询。

查询缓存的适用对象是更新不频繁的表，当表的结构更改后，查询缓存值的相关条目将被清空。

增加cache层

不成熟的方案，数据如何进行更新，多久刷新一次

负载均衡

负载均衡可以在系统中的各个层面中进行实现，从前台的web服务器到中间层的应用服务器，最后到数据层的数据库服务器都可以进行使用。

利用Mysql复制分流查询操作

利用mysql的主从复制，可以有效的分流更新操作和查询操作

具体的实现是一个主服务器承担更新操作，而多台从服务器承担查询操作，主从通过复制实现数据的同步，多台从服务器一方面用来确保可用性，一方面可以创建不同的索引以满足不同的查询需要。

对于主从之间不需要复制全部表的情况，可以通过在主服务器上搭建一个虚拟的从服务器，将需要复制到从服务器的表设置成BlackHole 引擎，然后定义replicate-do-table 参数只复制这些表，这样就过滤出需要复制的 BINLOG，减少了传输BINLOG 的带宽。因为搭建的虚拟从服务器只起到过滤 BINLOG 的作用，并没有实际记录任何数据，所以对主数据库服务器的性能影响也非常得有限。

通过复制来分流查询是减少主数据库负载的一个常用方法，但是这种办法也存在一些问题，最主要的问题是当主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在比较大的延迟更新，从而造成查询结果和主数据库上有所差异。因此应用在设计的时候需要有所考虑。

采用分布式数据库架构

分布式的数据库适合大数据量，负载高的情况，具有良好的扩展性和高可用性，可以实现多台服务器之间的负载均衡，提高了访问的执行效率，具体的实现的时候可以使用mysql的cluster功能或者通过用户自己编写的程序来实现全局事务。

其他的优化措施

  1. 对于没有删除操作的myIsam表，插入和查询操作可以并行进行，因为没有删除操作的表查询期间不会阻塞插入操作，对于确实需要执行删除操作的表，尽量利用空闲时间进行批量删除操作，并且在进行删除操作后应该进行optimize操作，来进行消除由于删除操作带来的空洞。

  2. 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这会减少MySQL 需要做的语法分析从而提高插入速度。

  3. 表的字段尽量不使用自增长变量，在高并发情况下该字段的自增可能对效率有比较大的影响，推荐通过应用来实现字段的自增长。