数据库如何优化

body{

line-height:200%;

}

如何优化MySQL数据库

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

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

1服务器物理硬件的优化

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

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太大，则应该把mycnf中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磁盘网络镜像方案，其特点为软件功能强大，数据可在底层块设备级别跨物理主机镜像，且可根据性能和可靠性要求配置不同级别的同步。

给你一些我常用的脚本！至于方案，我觉得完全在于积累经验！

--前10名其他等待类型

SELECT TOP 10

from sysdm_os_wait_stats

ORDER BY wait_time_ms DESC

SELECT FROM sysdm_os_wait_stats WHERE wait_type like 'PAGELATCH%'

OR wait_type like 'LAZYWRITER_SLEEP%'

--CPU的压力

SELECT scheduler_id, current_tasks_count, runnable_tasks_count

FROM sysdm_os_schedulers

WHERE scheduler_id < 255

--表现最差的前10名使用查询

SELECT TOP 10 ProcedureName = ttext,

ExecutionCount = sexecution_count,

AvgExecutionTime = isnull ( stotal_elapsed_time / sexecution_count, 0 ),

AvgWorkerTime = stotal_worker_time / sexecution_count,

TotalWorkerTime = stotal_worker_time,

MaxLogicalReads = smax_logical_reads,

MaxPhysicalReads = smax_physical_reads,

MaxLogicalWrites = smax_logical_writes,

CreationDateTime = screation_time,

CallsPerSecond = isnull ( sexecution_count / datediff ( second , screation_time, getdate ()), 0 )

FROM sysdm_exec_query_stats s

CROSS APPLY sysdm_exec_sql_text( ssql_handle ) t ORDER BY

smax_physical_reads DESC

SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 ,

SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,

SUM(signal_wait_time_ms) 10 / SUM (wait_time_ms) 100 AS [signal_wait_percent信号等待％],

SUM(wait_time_ms - signal_wait_time_ms) 10 / SUM (wait_time_ms) 100 AS [resource_wait_percent资源等待％]

FROM sysdm_os_wait_stats

--一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈。

--查看进程所执行的SQL语句

if (select COUNT() from masterdbosysprocesses) > 500

begin

select text,CROSS APPLY mastersysdm_exec_sql_text(asql_handle) from mastersyssysprocesses a

end

select text,a from mastersyssysprocesses a

CROSS APPLY mastersysdm_exec_sql_text(asql_handle)

where aspid = '51'

dbcc inputbuffer(53)

with tb

as

(

select blocking_session_id,

session_id,db_name(database_id) as dbname,text from mastersysdm_exec_requests a

CROSS APPLY mastersysdm_exec_sql_text(asql_handle)

),

tb1 as

(

select a,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage8 as 'memory_usage(KB)',

total_scheduled_time,reads,writes,logical_reads

from tb a inner join mastersysdm_exec_sessions b

on asession_id=bsession_id

)

select a,connect_time,client_tcp_port,client_net_address from tb1 a inner join mastersysdm_exec_connections b on asession_id=bsession_id

--当前进程数

select from masterdbosysprocesses

order by cpu desc

--查看当前活动的进程数

sp_who active

--查询是否由于连接没有释放引起CPU过高

select from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -10, getdate())

and login_time < dateadd(minute, -10, getdate())

--强行释放空连接

select 'kill ' + rtrim(spid) from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -60, getdate())

and login_time < dateadd(minute, -60, getdate())

--查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from mastersysprocesses order by cpu desc,physical_io desc

--查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, psize_in_bytes,[sql][text]

FROM sysdm_exec_cached_plans p OUTER APPLY sysdm_exec_sql_text (pplan_handle) sql

ORDER BY usecounts,psize_in_bytes desc

SELECT top 25 qttext,qsplan_generation_num,qsexecution_count,dbid,objectid

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(sql_handle) as qt

WHERE plan_generation_num >1

ORDER BY qsplan_generation_num

SELECT top 50 qttext AS SQL_text ,SUM(qstotal_worker_time) AS total_cpu_time,

SUM(qsexecution_count) AS total_execution_count,

SUM(qstotal_worker_time)/SUM(qsexecution_count) AS avg_cpu_time,

COUNT() AS number_of_statements

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) as qt

GROUP BY qttext

ORDER BY total_cpu_time DESC --统计总的CPU时间

--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

-- 计算可运行状态下的工作进程数量

SELECT COUNT() as workers_waiting_for_cpu,sscheduler_id

FROM sysdm_os_workers AS o

INNER JOIN sysdm_os_schedulers AS s

ON oscheduler_address=sscheduler_address

AND sscheduler_id<255

WHERE ostate='RUNNABLE'

GROUP BY sscheduler_id

--表空间大小查询

create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100))

insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused '''''

select from #tb

go

SELECT

表名,

记录数,

cast(ltrim(rtrim(replace(保留空间,'KB',''))) as int)/1024 保留空间MB,

cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 使用空间MB,

cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024/102400 使用空间GB,

cast(ltrim(rtrim(replace(索引使用空间,'KB',''))) as int)/1024 索引使用空间MB,

cast(ltrim(rtrim(replace(未用空间,'KB',''))) as int)/1024 未用空间MB

FROM #tb

WHERE cast(ltrim(rtrim(replace(使用空间,'KB',''))) as int)/1024 > 0

--order by 记录数 desc

ORDER BY 使用空间MB DESC

DROP TABLE #tb

--查询是否由于连接没有释放引起CPU过高

select from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -10, getdate())

and login_time < dateadd(minute, -10, getdate())

--强行释放空连接

select 'kill ' + rtrim(spid) from masterdbosysprocesses

where spid> 50

and waittype = 0x0000

and waittime = 0

and status = 'sleeping '

and last_batch < dateadd(minute, -60, getdate())

and login_time < dateadd(minute, -60, getdate())

----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)

select spid,cmd,cpu,physical_io,memusage,

(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text

from mastersysprocesses order by cpu desc,physical_io desc

----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局

SELECT TOP 100 usecounts, objtype, psize_in_bytes,[sql][text]

FROM sysdm_exec_cached_plans p OUTER APPLY sysdm_exec_sql_text (pplan_handle) sql

ORDER BY usecounts,psize_in_bytes desc

SELECT top 25 qttext,qsplan_generation_num,qsexecution_count,dbid,objectid

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(sql_handle) as qt

WHERE plan_generation_num >1

ORDER BY qsplan_generation_num

SELECT top 50 qttext AS SQL_text ,SUM(qstotal_worker_time) AS total_cpu_time,

SUM(qsexecution_count) AS total_execution_count,

SUM(qstotal_worker_time)/SUM(qsexecution_count) AS avg_cpu_time,

COUNT() AS number_of_statements

FROM sysdm_exec_query_stats qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) as qt

GROUP BY qttext

ORDER BY total_cpu_time DESC --统计总的CPU时间

--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

-- 计算可运行状态下的工作进程数量

SELECT COUNT() as workers_waiting_for_cpu,sscheduler_id

FROM sysdm_os_workers AS o

INNER JOIN sysdm_os_schedulers AS s

ON oscheduler_address=sscheduler_address

AND sscheduler_id<255

WHERE ostate='RUNNABLE'

GROUP BY sscheduler_id

SELECT creation_time N'语句编译时间'

,last_execution_time N'上次执行时间'

,total_physical_reads N'物理读取总次数'

,total_logical_reads/execution_count N'每次逻辑读次数'

,total_logical_reads N'逻辑读取总次数'

,total_logical_writes N'逻辑写入总次数'

, execution_count N'执行次数'

, total_worker_time/1000 N'所用的CPU总时间ms'

, total_elapsed_time/1000 N'总花费时间ms'

, (total_elapsed_time / execution_count)/1000 N'平均时间ms'

,SUBSTRING(sttext, (qsstatement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(sttext)

ELSE qsstatement_end_offset END

- qsstatement_start_offset)/2) + 1) N'执行语句'

FROM sysdm_exec_query_stats AS qs

CROSS APPLY sysdm_exec_sql_text(qssql_handle) st

where SUBSTRING(sttext, (qsstatement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(sttext)

ELSE qsstatement_end_offset END

- qsstatement_start_offset)/2) + 1) not like '%fetch%'

ORDER BY total_elapsed_time / execution_count DESC;

mysql的优化大的有两方面：

1、配置优化

配置的优化其实包含两个方面的： *** 作系统内核的优化和mysql配置文件的优化

1)系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、TCP处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；

2)mysql配置的优化，一般来说包含：IO处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从同步的相关参数即可，网上的相关配置文件很多，大同小异，常用的设置大多修改这些差不多就够用了。

2、sql语句的优化

1）  尽量稍作计算

Mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的。

2）尽量少 join

MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈

3）尽量少排序

排序 *** 作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。

对于MySQL来说，减少排序有多种办法，比如：

通过利用索引来排序的方式进行优化

减少参与排序的记录条数

非必要不对数据进行排序

4）尽量避免 select

在数据量少并且访问量不大的情况下，select 没有什么影响，但是量级达到一定级别的时候，在执行效率和IO资源的使用上，还是有很大关系的，用什么字段取什么字段，减少不必要的资源浪费。

5）尽量用 join 代替子查询

虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

添加索引或者使用工具，比如Apache Spark

先安装 Apache Spark，查询数据库的速度可以提升10倍。

在已有的 MySQL 服务器之上使用 Apache Spark （无需将数据导出到 Spark 或者 Hadoop 平台上），这样至少可以提升 10 倍的查询性能。使用多个 MySQL 服务器（复制或者 Percona XtraDB Cluster）可以让我们在某些查询上得到额外的性能提升。你也可以使用 Spark 的缓存功能来缓存整个 MySQL 查询结果表。

思路很简单：Spark 可以通过 JDBC 读取 MySQL 上的数据，也可以执行 SQL 查询，因此我们可以直接连接到 MySQL 并执行查询。那么为什么速度会快呢？对一些需要运行很长时间的查询（如报表或者BI），由于 Spark 是一个大规模并行系统，因此查询会非常的快。MySQL 只能为每一个查询分配一个 CPU 核来处理，而 Spark 可以使用所有集群节点的所有核。在下面的例子中，我们会在 Spark 中执行 MySQL 查询，这个查询速度比直接在 MySQL 上执行速度要快 5 到 10 倍。

另外，Spark 可以增加“集群”级别的并行机制，在使用 MySQL 复制或者 Percona XtraDB Cluster 的情况下，Spark 可以把查询变成一组更小的查询（有点像使用了分区表时可以在每个分区都执行一个查询），然后在多个 Percona XtraDB Cluster 节点的多个从服务器上并行的执行这些小查询。最后它会使用map/reduce 方式将每个节点返回的结果聚合在一起形成完整的结果。

以上就是关于数据库如何优化全部的内容，包括:数据库如何优化、谁总结下sqlserver数据库优化知识的详解和具体 *** 作最优方案！、mysql 优化包括哪些内容等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！