mysql性能优化

通用性能优化

关于通用性能优化，是在缓存、异步、批处理的范畴做优化。

批处理

关于mysql的应用优化（这里都是指innodb）：

写操作：批量insert、批量update。解释一下，例如一个支付场景，一个用户买了多个商品，一般我们会循环遍历来对表进行insert操作和update操作。如何优化？我们可以批量一次性的进行insert或是update操作。也就是说前端传来的集合的数据，我们要和数据库进行交互的时候，可以进行批量操作。针对mybatis可以拼接sql，来批量处理。 

为什么批量写性能要好呢？因为对于写操作，我们不会直接进行写，一般会用jdbc的preparestatement，传到mysql的驱动中，然后在客户端和服务端都进行sql预编译，那如果是foreach的话，当然所占时间和空间就会更多。如果是批量操作的话，时间和空间的节省就会很多了。再有就是网络消耗，这也不言而喻了。还有就是磁盘寻址的复杂度，如果insert操作是连续的数据的话，那么还需要寻址。

读操作：索引。对于读操作，mysql只有一个优化就是索引。

主键查询：千万条记录，1-10ms

唯一索引：千万条记录，10-100ms

非唯一索引：千万条记录，100-1000ms

无索引：百万条记录，1000ms+

mysql单机应用的性能优化

在应用上线之前，mysql会有配置部署的过程，对于分布式集群的场景中，大量机器连在mysql上，当然压力也就非常大。如何优化？

首先，这是mysql中的一些运行机制 

max_connection=1000，一台服务器连接mysql，会有一个socket来跟他连，再接下来进行增删改查的操作。那么我们可以扩大socket连接数。mysql默认连接数是100。

innodb_file_per_table=1，如果有事务，那么客户端会发送start transaction给执行引擎，执行引擎会运行语法分析器等操作磁盘的操作。为什么start transaction可以保证事务的ACID的特性呢？因为mysql的innodb有WAL机制，也就是说执行引擎在做所有的操作之前，先不操作数据，先记录日志（undo/redo日志）（write ahead log），然后操作数据（change data），然后提交事务。对于写日志来说，肯定是写到磁盘的，但是对于操作数据，可以现在内存中操作，然后在异步写进磁盘。对于日志操作是顺序写的操作，而对于数据不是，需要先寻址，然后再做修改。顺序写的性能要高效很多。对于mysql的database，永远都有两个文件：数据文件和日志文件，针对这样的架构，我们可以这样设置。假如一个应用有几百张表，这种情况下对应的数据文件就会非常大，因为所有表的数据都在这个文件中，那么自然寻址也会变得非常困难，这样设置的意思就是每个table对应一个文件。

innodb_buffer_pool_size=1G，这个buffer pool对应的就是在数据操作的时候的内存操作，当数据写入的时候先写buffer，那么如果我的buffer足够大，那么我命中缓存的概率就更高，因此我们可以增大buffer。对于读操作来说，如果buffer pool中有查询的组件，那么默认会读这里面的数据。

innodb_log_file_size=256M，log file就是日志文件，当事务不断执行的时候，这个文件就会不断地变大，当他超过设定值的时候，就会有一个重命名的操作，把这个文件变成历史文件，重新开一个日志文件，在这个间隙内，log buffer的flush操作是没有办法做的，那么往上一系列的关联，也就导致sql语句没办法操作，这是非常致命的，因此首先我们可以把这个文件改大，然后设置下面的参数。当然这个文件也不能设置的特别大，因为一旦应用挂掉，然后进行重启，重启后有恢复的过程，因为这个日志文件太大，那么就需要从文件中找到丢失的transaction的id，然后把所有的undo和redo再做一遍，那么这个代价就会很大。

innodb_log_buffer_size=16M，log buffer就是redo/undo日志的缓存，当上面操作发生时，我们有16M的缓冲空间，新文件生成后再写进新的文件中。这个也不能设置太大，因为设置太大，也会影响我们往文件中flush写入的速度。

innodb_flush_log_at_trx_commit=2（需要放在[mysqlId_safe]节点下），从上图可以看出，日志缓存会有往日志文件中flush的操作，那么什么时候触发就很关键了，默认这个数据是1，也就是说每次事务commint，就会往文件中写。还有一种情况是0，意思是说事务commint后，就会写在buffer内存中，mysql借助默认的轮询时间（1秒），把内存中的数据写在文件中，这种情况性能最高，但是也最不安全，因为如果mysql下一秒钟挂了，那么就丢失了这一秒钟内的数据。数值为2的时候，我们通过io流操作知道，其实写操作会有一个write写到操作系统中，然后flush才会写到磁盘中。那么2的时候代表，事务每次commit都会write进操作系统，但不会flush，然后mysql通过轮询来flush，这样就解决了如果mysql挂了也没事，只要操作系统不挂，都会写进去。这个参数非常影响性能，所以一定要注意。

innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G:auto extend，虽然通过上面的配置，把表拆开了，但是如果一个表中的数据非常大的时候，他仍然是个瓶颈，那我们这样的操作就是当文件大于1G的时候，就分文件，也就是mysql的文件分区。

mysql分布式性能优化

主从扩展：也就是读写分离，如何做？开启bin_log，设置主从同步账号，配置主从同步。bin_log是二进制日志，事务提交后，所有的除了读操作外的操作都会被执行引擎写到binlog中，那么也就是会记录这台机器上（主mysql）所有的数据库操作，从mysql机器通过账号连接到主mysql中（这个账号只有replicated权限），读取bin_log文件，然后进行变更。那么对于读操作来说，客户端可以直接读从mysql中的数据，那么负载就被均衡掉了。当然，这里还是有数据同步问题，没有办法做到强一致，因此读操作也要进行判断，通过jdbc的驱动强制路由到主mysql上。当然，一旦涉及主从，都会有分布式事务问题，永远不可能达到一致，所以如果用的话，还是需要谨慎。

 

当然对于语句上的优化，一定不能用模糊查询，一旦数据巨大，那么压力是非常大的，介于这种情况，可以借助搜索引擎。