MySQL简单优化

公司升级了服务器的配置，内存吃紧的问题得以解决，由此想到了几个优化MySQL的简单方法。首先明确两件事，我们的业务场景是常见的高并发web服务，查询速度是重中之重。还有就是优化前一定要备份数据库，不然出问题就只能跑路了。

1.使用固态硬盘

这个优化方法毫无技术含量，完全就是花钱买性能，但是不得不说这个方法相当的简单、粗暴、有效。借助固态硬盘高频的读写速度可以大幅提升MySQL的各项性能

2.添加索引

当数据量达到一定级别时，添加合适的索引是必须的。MySQL是B+树或者其变种树，将数据按照索引的数据结构排序以优化查询效果。如果表中的数据要频繁的插入、删除时，这些操作会损害索引，导致索引占用了很大的无效空间。我就见过一张数据表的所以比数据量大很多，查询速度超慢甚至不及不添加索引，原因就是频繁的插入删除导致的，这种场景需要定期删除索引然后重新生成索引

3.去掉外键

大学期间，老师强调了使用外键来约束数据一致性是很有必要的。上学时我也严格奉行了这一观点，毕竟这可以让我少写代码，把一致性问题抛给数据库。工作后结合真正的业务场景，数据库常常成为性能瓶颈，而服务并不会成为性能瓶颈，所以将数据一致性问题的解决方案写入服务中以此减少数据库压力很有必要。

4.使用InnoDB

很多事实都表明InnoDB比MyISAM更具优势。InnoDB对内存的使用更全面，内存的访问速度显然比磁盘要快

5.设置InnoDB内存

innodb_buffer_pool_size参数代表分配给InnoDB的内存大小，分配内存时要给操作系统留下足够的内存。根据同事的经验之谈是可以为innodb_buffer_pool_size分配服务器80%的内存，当然了，这么做的前提条件是你的服务上基本只有MySQL，如果服务器上存在数据解析等其他很费内存的服务时，innodb_buffer_pool_size数值要酌情减少

6.设置InnoDB多任务

如果分配给innodb_buffer_pool_size的内存大于2G时，我们可以考虑将InnoDB的缓冲池划分为多个，我们可以修改配置中的innodb_buffer_pool_instances参数。对于高并发服务，性能瓶颈往往是多线程访问MySQL，划分更多的缓冲池可以有效的缓解这个问题。当然缓冲池也不是越多越好，每个缓冲池的内存过低的话就不能发挥出多个缓冲池的优势了。官方的建议是每个缓冲池需要至少1G的内存。

 

最后提醒大家，对MySQL的配置修改需要重启MySQL才能生效

 

https://card.weibo.com/article/v3/editor#/history/186963
https://card.weibo.com/article/v3/editor#/history/186608
https://card.weibo.com/article/v3/editor#/history/186424
https://card.weibo.com/article/v3/editor#/history/185801
https://card.weibo.com/article/v3/editor#/history/185781
https://card.weibo.com/article/v3/editor#/history/185463
https://card.weibo.com/article/v3/editor#/history/174288
https://card.weibo.com/article/v3/editor#/history/174119
https://card.weibo.com/article/v3/editor#/history/173561
https://card.weibo.com/article/v3/editor#/history/173336
https://card.weibo.com/article/v3/editor#/history/173082
https://card.weibo.com/article/v3/editor#/history/184630
https://card.weibo.com/article/v3/editor#/history/183057
https://card.weibo.com/article/v3/editor#/history/184940
https://card.weibo.com/article/v3/editor#/history/186882
https://card.weibo.com/article/v3/editor#/history/186884
https://card.weibo.com/article/v3/editor#/history/186966
https://card.weibo.com/article/v3/editor#/history/187201
https://card.weibo.com/article/v3/editor#/history/187391
https://card.weibo.com/article/v3/editor#/history/174940
https://card.weibo.com/article/v3/editor#/history/187695
https://card.weibo.com/article/v3/editor#/history/185492
https://card.weibo.com/article/v3/editor#/history/187410
https://card.weibo.com/article/v3/editor#/history/174972
https://card.weibo.com/article/v3/editor#/history/187714
https://card.weibo.com/article/v3/editor#/history/185531
https://card.weibo.com/article/v3/editor#/history/188021
https://card.weibo.com/article/v3/editor#/history/175465
https://card.weibo.com/article/v3/editor#/history/187712
https://card.weibo.com/article/v3/editor#/history/188523
https://card.weibo.com/article/v3/editor#/history/188120
https://card.weibo.com/article/v3/editor#/history/176032
https://card.weibo.com/article/v3/editor#/history/176073
https://card.weibo.com/article/v3/editor#/history/176022
https://card.weibo.com/article/v3/editor#/history/188239
https://card.weibo.com/article/v3/editor#/history/188643
https://card.weibo.com/article/v3/editor#/history/186373
https://card.weibo.com/article/v3/editor#/history/188251
https://card.weibo.com/article/v3/editor#/history/188665
https://card.weibo.com/article/v3/editor#/history/186385
https://card.weibo.com/article/v3/editor#/history/186508
https://card.weibo.com/article/v3/editor#/history/188788
https://card.weibo.com/article/v3/editor#/history/188382
https://card.weibo.com/article/v3/editor#/history/186627
https://card.weibo.com/article/v3/editor#/history/176292
https://card.weibo.com/article/v3/editor#/history/176523
https://card.weibo.com/article/v3/editor#/history/188817
https://card.weibo.com/article/v3/editor#/history/188818
https://card.weibo.com/article/v3/editor#/history/189232
https://card.weibo.com/article/v3/editor#/history/189237
https://card.weibo.com/article/v3/editor#/history/186919
https://card.weibo.com/article/v3/editor#/history/186921
https://card.weibo.com/article/v3/editor#/history/176408
https://card.weibo.com/article/v3/editor#/history/188683
https://card.weibo.com/article/v3/editor#/history/189175
https://card.weibo.com/article/v3/editor#/history/186813
https://card.weibo.com/article/v3/editor#/history/176522
https://card.weibo.com/article/v3/editor#/history/176562
https://card.weibo.com/article/v3/editor#/history/188886
https://card.weibo.com/article/v3/editor#/history/189283
https://card.weibo.com/article/v3/editor#/history/186984
https://card.weibo.com/article/v3/editor#/history/188906
https://card.weibo.com/article/v3/editor#/history/176578
https://card.weibo.com/article/v3/editor#/history/189299
https://card.weibo.com/article/v3/editor#/history/189300
https://card.weibo.com/article/v3/editor#/history/187004
https://card.weibo.com/article/v3/editor#/history/187005
https://card.weibo.com/article/v3/editor#/history/176613
https://card.weibo.com/article/v3/editor#/history/176614
https://card.weibo.com/article/v3/editor#/history/176687
https://card.weibo.com/article/v3/editor#/history/176692
https://card.weibo.com/article/v3/editor#/history/189480
https://card.weibo.com/article/v3/editor#/history/189482
https://card.weibo.com/article/v3/editor#/history/189063
https://card.weibo.com/article/v3/editor#/history/189067
https://card.weibo.com/article/v3/editor#/history/176713
https://card.weibo.com/article/v3/editor#/history/187171