数据库如何优化

body{

line-height:200%;

}

如何优化MySQL数据库

当MySQL数据库邂逅优化，它有好几个意思，今天我们所指的是性能优化。

我们究竟该如何对MySQL数据库进行优化呢？下面我就从MySQL对硬件的选择、Mysql的安装、myf的优化、MySQL如何进行架构设计及数据切分等方面来说明这个问题。

1服务器物理硬件的优化

1)磁盘(I/O)，MySQL每一秒钟都在进行大量、复杂的查询 *** 作，对磁盘的读写量可想而知，所以推荐使用RAID10磁盘阵列，如果资金允许，可以选择固态硬盘做RAID10；

2)cpu对Mysql的影响也是不容忽视的，建议选择运算能力强悍的CPU。

2MySQL应该采用编译安装的方式

MySQL数据库的线上环境安装，我建议采取编译安装，这样性能会较大的提升。

3MySQL配置文件的优化

1)skip

-name

-resolve，禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间；

2)back_log

=

384，back_log指出在MySQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中，对于Linux系统而言，推荐设置小于512的整数。

3)如果key_reads太大，则应该把myf中key_buffer_size变大，保持key_reads/key_read_requests至少在1/100以上，越小越好。

4MySQL上线后根据status状态进行适当优化

1)打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响也会很小。

2)MySQL服务器过去的最大连接数是245，没有达到服务器连接数的上限256，应该不会出现1040错误。比较理想的设置是：Max_used_connections/max_connections

100%

=85%

5MySQL数据库的可扩展架构方案

1)MySQL

cluster，其特点为可用性非常高，性能非常好，但它的维护非常复杂，存在部分Bug;

2)DRBD磁盘网络镜像方案，其特点为软件功能强大，数据可在底层块设备级别跨物理主机镜像，且可根据性能和可靠性要求配置不同级别的同步。

mysql数据库cpu飙升800%，基本上就两种原因：

访问量大，大到你8核cpu都承受不了；

慢查询，数据库执行sql语句 *** 作（查询数据、修改数据）会产生大量的逻辑读，将读出来的数据维护到临时表中（内存），系统需要消耗较多的cpu来维持内存与磁盘数据的一致性。

大多数情况下都是开发人员对sql的把握质量不够，导致慢sql查询的产生，进而影响数据库的整体运行状况。

大量行锁冲突、行锁等待或后台任务也有可能会导致实例的CPU使用率过高，但这些情况出现的概率非常低。

当我们的数据库性能下降的厉害或者cpu飙升时候，可以进行如下 *** 作定位问题：

查询mysql进程列表

showfullprocesslist;

获取到mysql当前使用的进程：

如果进程很多，说明请求量很大，需要区分是否正常业务流量，还是代码问题导致的。

查询慢查询日志

showvariableslike'%slow_query_log%';

找到慢查询日志文件/home/mysql/data3085/mysql/

slow_querylog

，即可找到慢查询日志信息，解决这些慢sql，你的cpu一定会降下来。

避免数据库cpu飙升

实际开发过程中，我们对数据库的使用一定要小心，不能等问题发生了再去排查问题解决问题，而是要预防问题的发生，并且在问题可能发生的情况下，提前介入，避免问题扩大化。平时开发过程中需要做好一些准备工作：

增加CPU使用率告警机制，比如使用率超过80%就短信告警；

所有的sql语句必须走索引，有DBA则由DBA统一调控，没有的话开发人员先执行explain看sql执行计划，必须走索引，属于强制规则；

新功能上线必须进行压测；

日常mysql运行监控，慢查日志查看，将隐患扼杀在摇篮之中。

以上就是一些mysql稳定运行的个人看法，大家还有什么好的建议，欢迎评论去交流讨论，批评指正～

一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

1，slow_query_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

2，long_query_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

3，slow_query_log_file

记录日志的文件名。

4，log_queries_not_using_indexes

这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

二、检测mysql中sql语句的效率的方法

1、通过查询日志

（1）、Windows下开启MySQL慢查询

MySQL在Windows系统中的配置文件一般是是myini找到[mysqld]下面加上

代码如下

log-slow-queries = F:/MySQL/log/mysqlslowquery。log

long_query_time = 2

（2）、Linux下启用MySQL慢查询

MySQL在Windows系统中的配置文件一般是是mycnf找到[mysqld]下面加上

代码如下

log-slow-queries=/data/mysqldata/slowquery。log

long_query_time=2

我们先来看第一个阶段，MySQL慢的诊断思路，一般我们会从三个方向来做：

第一个方向是MySQL内部的观测

第二个方向是外部资源的观测

第三个方向是外部需求的改造

11 MySQL 内部观测

我们来看MySQL内部的观测，常用的观测手段是这样的，从上往下看，第一部分是Processlist，看一下哪个SQL压力不太正常，第二步是explain，解释一下它的执行计划，第三步我们要做Profilling，如果这个SQL能再执行一次的话, 就做一个Profilling，然后高级的DBA会直接动用performance_schema ，MySQL 57 以后直接动用sys_schema，sys_schema是一个视图，里面有便捷的各类信息，帮助大家来诊断性能。再高级一点，我们会动用innodb_metrics进行一个对引擎的诊断。

除了这些手段以外，大家还提出了一些乱七八糟的手段，我就不列在这了，这些是常规的一个MySQL的内部的状态观测的思路。除了这些以外，MySQL还陆陆续续提供了一些暴露自己状态的方案，但是这些方案并没有在实践中形成套路，原因是学习成本比较高。

12 外部资源观测

外部资源观测这部分，我引用了一篇文章，这篇文章的二维码我贴在上面了。这篇文章是国外的一个神写的，标题是：60秒的快速巡检，我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检。一共十条命令，这是前五条，我们一条一条来看。

1uptime，uptime告诉我们这个机器活了多久，以及它的平均的负载是多少。

2dmesg -T | tail，告诉我们系统日志里边有没有什么报错。

3vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap有没有使用。

4 mpstat -P ALL，告诉我们CPU压力在各个核上是不是均匀的。

5pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。

我们来看一下后五条：

首先是iostat-xz 1，查看IO的问题，然后是free-m内存使用率，之后两个sar，按设备网卡设备的维度，看一下网络的消耗状态，以及总体看TCP的使用率和错误率是多少。最后一条命令top，看一下大概的进程和线程的问题。

这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。

13 外部需求改造

第三个诊断思路是外部的需求改造，我在这里引用了一篇文档，这篇文档是MySQL的官方文档中的一章，这一章叫Examples of Common Queries，文档中介绍了常规的SQL怎么写, 给出了一些例子。文章的链接二维码在slide上。

我们来看一下它其中提到的一个例子。

它做的事情是从一个表里边去选取，这张表有三列，article、dealer、price，选取每个作者的最贵的商品列在结果集中，这是它的最原始的SQL，非常符合业务的写法，但是它是个关联子查询。

关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的。

第三步，会教大家直接把子查询拿掉，然后转成这样一个SQL，这个就叫业务改造，前后三个SQL的成本都不一样，把关联子查询拆掉的成本，拆掉以后SQL会跑得非常好，但这个SQL已经不能良好表义了，只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式。

为什么它能够把一个关联子查询拆掉呢？

这背后的原理是关系代数，所有的SQL都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。

上面的这篇文档是一个大学的教材，它从头教了关于代数和SQL之间的关系。然后一步步推导怎么去简化这句SQL。

第一，MySQL本身提供了很多命令来观察MySQL自身的各类状态，大家从上往下检一般能检到SQL的问题或者服务器的问题。

第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，我们把服务器的资源全部都观察一圈就可以了。

第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写SQL，这个成本会下降的非常快，这个是常规的MySQL慢的诊断思路。

主要从以下角度思考优化方向：1，Mysql配置优化主要对查询缓存，mysql数据库连接时长，开启慢查询日志(开启后还要分析sql)等方面进行优化2

Myslq语句优化3

Mysql索引优化主要是需要注意索引数量和索引失效情况，重复索引4

Mysql引擎优化innodb引擎注重于事务，能保证数据一致性myisam引擎可以进行全文检索，但不是事务安全当初在黑马程序员学过，还用实例进行优化学习

以上就是关于数据库如何优化全部的内容，包括:数据库如何优化、mysql数据库cpu飙升800%，如何故障定位及优化、如何查找MySQL中查询慢的SQL语句等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！