当前位置:Gxlcms > 数据库问题 > MySql数据库优化-汇总

MySql数据库优化-汇总

时间:2021-07-01 10:21:17 帮助过:20人阅读

各位,不喜勿喷,和气生财~

数据库优化,是一种综合性的技术,不是通过某一种方式让数据库效率提高很多,而是通过各个方面的优化,来是数据库效率明显的稳步的提高。

主要包括以下:

1、库表的设计优化(三种范式)

2、SQL优化与慢查询

3、库表添加合适的索引

4、分表技术-水平分割与垂直分割

5、定时清楚垃圾数据,定时进行碎片整理

6、多用存储过程和触发器

7、优化MqSql配置

8、读写分离

除此之外,还有 MqSql服务器硬件升级

 

以下进行详细描述

一、库表设计

良好的数据库设计,能够节省数据库空间,保持数据完整性,方便应用程序的开发;(相反:数据冗余,空间浪费,插入更新繁杂或者异常)

设计数据库

1、充分了解需求:标识实体(具体存在的对象、东西,名词),标识实体属性,标识实体关系

以BBS论坛为例

实体:

用户(属性:昵称,密码,邮箱,生日,性别,登记,备注,积分,注册时间)

主贴(属性:标题,正文,发帖时间,状态,发帖人,回复数量,点击数)

回帖(属性:帖子编号,回帖人,回帖标题,回帖正文,回帖时间,点击数)

板块(属性:板块名称,版主,板块格言,点击数,发帖数)

 

2、实体关系

一对一,两个表的主键是公共字段

一对多,主键与非主键之间的关系

多对一,非主键与主键之间 的关系

多对多,非主键与非主键之间的关系

 

3、E-R图,实体-联系图(Entity Relationship Diagram),提供了表示实体类型、属性和联系的方法,用来描述现实世界的概念模型;

*1、创建表时,将实体转化为表,将属性转化为列,唯一标识一行数据的列可为主键,无合适字段做主键就用自动增加列, 将关系转化为主外键展示实体之间的关系;

*2、表结构规范化-三范式(1、列的原子性,确保每列都不能再分解成更基本的数据单位;2、非主键字段依赖主键字段,即表的列中若有重复数据且与主键无关,则可拆分表;3、不存在传递依赖,即若表的除主键外各个列间有直接关联,即非主键字段一个字段可以推导出另一个字段,则可拆分表)

范式举例:

山东理工,山东淄博;山大,山东济南;其中山东济南就可以拆分(第一范式)山东理工,山东,淄博;山大,山东,济南;

学号-主,姓名;ID,科目,成绩,学号,姓名;满足第二范式;但不满足第三范式;如果学号非主键,则满足第三范式;

 *但注意,也有第五第六等范式,范式越高表越多,查询效率一般就会降低,一般第三范式效率最高列。。

反三范式:学号,语文,数学,英语,总成绩;总成绩字段就是违反第三范式,适当的数据冗余允许,不然就查询效率低了:select sum(yw+sx+yy) from t_score或单独建表 学号,总成绩;

由此可见,数据库的性能效率比规范化更重要;

 

二、SQL优化与慢查询

1、数据库的增删改查

一般情况下,增删改总计占数据库的10%,而90%是查询操作;

2、show status的相关常用命令

#查看数据库的一些状态
show status;
#显示执行了多少条/次的增删改查
show stauts like ‘com_select‘;
show stauts like ‘com_insert‘;
show stauts like ‘com_delete‘;
show stauts like ‘com_update‘;
#[session|global] 默认是session会话级-只取出当前窗口的执行;global-从mysql启动到现在 
show global stauts like ‘com_select‘;

#查询当前MySQL本次启动后的运行统计时间(单位:秒)-存储引擎为MyISAM,且运行时间过长,则注意碎片整理
show status like ‘uptime‘;
#查看试图连接到MySQL(不管是否连接成功)的连接数
show status like ‘connections‘;
#查看线程缓存内的线程的数量。
show status like ‘threads_cached‘;

#慢查询
#查看查询时间超过long_query_time秒的查询的个数-即慢查询
show status like ‘slow_queries‘;
#可以显示当前慢查询时间(单位:秒)(默认10秒)
show variables like ‘long_query_time‘; 
#可以修改慢查询时间(单位:秒)
set long_query_time=1;

  

3、启动MqSql使用记录慢查询日志(2种)

#第一种:中括号[]内的部分是可选的,file_name表示日志文件路径
#在5.5及以上版本的MySQL中,使用如下命令启动:
mysqld --safe-mode --show-query-log[=1] [--show-query-log-file=file_name]
#在5.0、5.1等低版本的MySQL中,使用如下命令启动:
mysqld --log-slow-queries[=file_name]

#第二种:启动命令配置到my.ini中的[mysqld]节点
[mysqld]
#设置慢查询界定时间为1秒
long_query_time=1
#5.0、5.1等版本配置如下选项
log-slow-queries="mysql_slow_query.log"
#5.5及以上版本配置如下选项
slow-query-log=On
slow_query_log_file="mysql_slow_query.log"

  

三、库表添加合适的索引

 

MySql数据库优化-汇总

标签:注册时间   数据库空间   variables   总成绩   lan   rom   索引   存储引擎   汇总   

人气教程排行