MySQL简单优化

转载 2018年04月15日 19:13:57

公司升级了服务器的配置,内存吃紧的问题得以解决,由此想到了几个优化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

mysql数据库简单优化措施

优化sql的一般步骤 通过show status了解各种sql的执行频率 定位执行效率低的sql语句 通过explain分析效率低的sql 通过show profile分析sql 通过trace分析优...
  • baidu_21483933
  • baidu_21483933
  • 2016-08-05 06:16:21
  • 398

mysql优化和简单的数据库设计

在工作过程中经历了几个不错的项目,所采用的数据库设计也是多种多样, 这里我简单的说一下自己的想法,有不对的地方欢迎探讨。 ===============分割线=====================...
  • u014017080
  • u014017080
  • 2016-09-30 16:41:51
  • 560

优化 MySQL 从InnoDB做3 个简单的小调整

优化 MySQL 从InnoDB做3 个简单的小调整1.没有两个数据库或者应用程序是完全相同的。这里假设我们要调整的数据库是为一个“典型”的 Web 网站服务的,优先考虑的是快速查询、良好的用户体验以...
  • my0592
  • my0592
  • 2018-02-11 08:29:14
  • 90

mysql简单优化的一些总结

mysql对cpu的利用特点: 5.1之前,多核支持较弱;5.1可利用4个核;5.5可利用24核;5.6可利用64个核;每个连接对应一个线程,每个并发query只能使用一个核 mysql对...
  • jh993627471
  • jh993627471
  • 2018-02-07 11:21:23
  • 31

转--MysQL简单优化

一、在编译时优化MySQL 如果你从源代码分发安装MySQL,要注意,编译过程对以后的目标程序性能有重要的影响,不同的编译方式可能得到类似的目标文件,但性能可能相差很大,因此,在编译安装MySQL适应...
  • xbh82
  • xbh82
  • 2006-06-24 11:59:00
  • 673

MySQL简单的优化

最近在看mysql的优化,就做了下笔记,就当给自己一次理论知识上的提升1)数据库优化通过不同的方式达到提高数据库性能的目的。 优化包括多个方面,比如查询速度、更新速度、mysql服务器性能等 2)优化...
  • MarioSum
  • MarioSum
  • 2017-11-19 18:09:44
  • 60

mysql5.7配置文件优化

mysql5.7的配置文件优化,引用了别人的模板,根据自己的情况修改了一些参数,加上注释,留着备用,感兴趣的朋友可以借鉴一下,如果无法启动可以查看一下error_log,修改相应的参数。[mysqld...
  • qq_34605594
  • qq_34605594
  • 2017-04-25 17:30:58
  • 7264

Mysql常用30种SQL查询语句优化方法

1、应尽量避免在 where 子句中使用!=或 2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 3、应尽量避免在 where 子句中对...
  • youthsunshine
  • youthsunshine
  • 2016-12-05 15:32:37
  • 6485

MySQL的几种优化方法

没索引与有索引的区别优点: 当表中有大量记录时,若要对表进行查询,如果没用建立索引,搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据...
  • u013474436
  • u013474436
  • 2015-11-18 16:13:12
  • 7151

MySQL/Oracle数据库优化总结(非常全面)

MySQL/Oracle数据库优化总结(非常全面)
  • baidu_37107022
  • baidu_37107022
  • 2017-08-21 21:05:30
  • 3488
收藏助手
不良信息举报
您举报文章:MySQL简单优化
举报原因:
原因补充:

(最多只允许输入30个字)