Mysql数据库的设计和优化

在JAVA开发中数据库的学习也是我们需要了解的，截下来几篇文章都是关于数据库的设计和应用，那么java课程培训机构废话不多说开始学习吧！

数据库的设计

数据库设计是基础，数据库优化是建立在设计基础之上的。好的数据库一定拥有好的设计。

数据库设计的目标是为用户和各种应用系统提供一个信息基础设施和高效的运行环境。

数据库的三大范式

第一范式1NF:所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

第二范式2Nf:第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式3Nf:所有字段必须与主键直接相关，而不是间接相关。也可以理解为字段不要和其他非主键字段相关

注意:这三个范式尽可能去遵守,不是一定要墨守成规这只是让我们设计的表的时候,越靠近这些范式,可以使字段尽量的减小冗余但是有时候也可以根据实际需要小小的违背一下但是第三范式违反一下还可以接受,但是第一范式别违反

数据库设计的步骤

需求分析阶段

准确了解与分析用户需求(包括数据与处理)。是整个设计过程的基础，是最困难、最耗费时间的一步。

概念结构设计阶段

是整个数据库设计的关键--设计数据库的E-R模型图，确认需求信息的正确和完整

Entity_Relationship---实体之间的关系

一对一

一对多

多对一

尽量不要使用 or 使用or会引起全表扫描 将大大降低查询效率

alice like % &abigale& % 会使索引不起作用(针对sqlserver)

经过实践验证 charindex()并不比前面加%的like更能提高查询效率 并且charindex()会使索引失去作用（指sqlserver数据库）

字段提取要按照 需多少 提多少 的原则 避免 select 尽量使用 select 字段 字段 字段 实践证明 每少提取一个字段 数据的提取速度就会有相应的提升 提升的速度还要看您舍弃的字段的大小来判断

order by按聚集索引列排序效率最高 一个sqlserver数据表只能建立一个聚集索引 一般默认为ID 也可以改为其它的字段

能使用exists和not exists尽量使用 避免使用in或not in

能使用表连接尽量使用 避免使用exists和not exists

SET NOCOUNT ON

正确使用UNION和UNION ALL

慎用SELECT DISTINCT

少用游标

使用表的别名(Alias)

当在SQL语句中连接多个表时 请使用表的别名并把别名前缀于每个Column上 这样可以减少解析的时间并减少那些由Column歧义引起的语法错误

尽量少使用游标

原因很简单;就是游标的算法是最原始的计算机算法(和for if等语句一样 一条条搜索来算;效率极低);

而sql语句用的是集合运算;速度则快的多;如果用索引速度则很快(用了指针)

创建索引

a 聚集索引:

聚集索引是磁盘存储和逻辑显示是一样的

mssql表的主键一般是聚集索引;主键(每一条记录唯一确定);

创建的主键自动会是聚集索引;

如有一个非常大的表(有百万行);很长时间磁盘存储上会有类似碎片(磁盘填充率效率低;一般是频繁删除造成的);

要提高它的性能的最简洁办法是:把这个表的主键去掉再保存后;然后重新设主键再保存;

(这个表就会在磁盘上重新整理排序;性能当然会提高哟)

b 非聚集索引:

非聚集索引是在外面建立小的附加表(一种树形结构;大多数是B或B+树);

读(遍历select等sql语句)表特快;但写(update;delete insert等sql语句)表性能会略微下降

针对数据量大的表建议非聚集索引不要超过 个(节省额外磁盘负担)

不要给类似 性别 列创建索引

死锁:

是指有线程在读一条记录;别的线程读这条记录就要等待;

在mssql中只要长期占那条记录的线程去掉;死锁就会解除

在mssql中锁是针对每一行记录(所以性能不错)

经常产生锁的原因有:

a 在sql语句中使用事务语句(特别是事务中当查询比较耗时)

b 在前台的应用程序的connetion冲突(未关闭)

c 多表联合查询(尤其是在打开大的数据集时)

sql语句优化

a is null not or in 不会用索引

b 避免在索引列上使用计算或函数处理(索引会大失性能) 还有 % ;有的甚至会全失索引性能

c SELECT中避免使用 (宁可把需要字段列出来;而不要用去把所有的字段都列出来)

d 避免相关子查询(select中套select)

e where的条件中 =＞exists＞in (指性能)

f order by group by having distinct 等语句要慎用(因为它们效率不高;它们是先把数据到临时表中再进行处理的)

g 聚集索引如有 个字段组成(tt 和tt );tt 在前面;where的条件中如只用tt 字段来判断;就会用到一半的聚集索引;

where的条件中如tt 和tt 字段都用来判断了;就会全用到聚集索引;

where的条件中如只用tt 字段来判断;就会用不到聚集索引了;

尽量不要使用TEXT数据类型

除非你使用TEXT处理一个很大的数据 否则不要使用它 因为它不易于查询 速度慢 用的不好还会浪费大量的空间

一般的 VARCHAR可以更好的处理你的数据

尽量不要使用临时表

尽量不要使用临时表 除非你必须这样做 一般使用子查询可以代替临时表 使用临时表会带来系统开销

如果前台的代码你是使用数据库连接池而临时表却自始至终都存在 SQL Server提供了一些替代方案 比如Table数据类型

尽量少使用外键和触发器

因为在mssql中这些功能的性能做得不是很好;随便动一下表(它就会到相关的表去搞判断;有很多情况并不需要);在后台消耗资源大

lishixinzhi/Article/program/Oracle/201311/16744

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

--前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数据库的设计和优化全部的内容，包括:Mysql数据库的设计和优化、数据库的查询优化方法分析、谁总结下sqlserver数据库优化知识的详解和具体 *** 作最优方案！等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！