mysql数据库的优化

最新推荐文章于 2024-09-07 15:47:00 发布

江边小子

最新推荐文章于 2024-09-07 15:47:00 发布

阅读量285

点赞数

分类专栏： mysql 文章标签： SQL优化

本文链接：https://blog.csdn.net/wssc63262/article/details/99899296

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.MySQL如何优化

表的设计合理化（符合数据库三大范式)
添加适当索引【四种：普通索引、主键索引、唯一索引unique、全文索引】
SQL语句的优化
分表技术（水平分割、垂直分割）
读写 [写：update/delete/add/select 读：select] 分离注：看3
存储过程【模块化编程，可以提高速度】
对mysql配置优化【配置最大并发数my.ini（my.cnf），调整缓存大小】
mysql服务器硬件升级（配置好一点的服务器，MySQL的性能也会好很多）
定时的去清除不需要的数据，定时进行碎片整理（MyISAM)（数据文件：. myd 索引文件：. MYI 表定义文件：. frm）

2.数据库的设计

2.1什么是数据库范式

为了建立冗余较小、结构合理的数据库。设计数据库是必须要遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

2.2数据库的三大范式

1NF、列不可分；

2NF、不存在部分依赖；

3NF、不存在传递依赖。

第一范式：原子性，即字段不可以再分

如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）

第二范式：唯一性，不可以把多种数据保存在同一个表中，即一张表只能保存“一种”数据

不符合第二范式的表:学号, 姓名, 年龄, 课程名称, 成绩, 学分;

可能会存在问题：

数据冗余，每条记录都含有相同信息；

删除异常：删除所有学生成绩，就把课程信息全删除了；

插入异常：学生未选课，无法记录进数据库；

更新异常：调整课程学分，所有行都调整。

正确做法:

学生：Student(学号, 姓名, 年龄)；

课程：Course(课程名称, 学分)；

选课关系：StudentCourse(学号, 课程名称, 成绩)。

第三范式：直接性，每一列都和主键直接相关，而不能间接相关。（依赖不准传递）

不符合第三范式的表: 学号, 姓名, 年龄, 学院名称, 学院电话，因为存在依赖传递: (学号) → (学生)→(所在学院) → (学院电话) 。

可能会存在问题：

数据冗余:有重复值；

更新异常：有重复的冗余信息，修改时需要同时修改多条记录，否则会出现数据不一致的情况

正确做法：

学生：(学号, 姓名, 年龄, 所在学院)；

学院：(学院, 电话)。

介绍数据库三大范式：https://blog.csdn.net/chenyyhh92/article/details/51174343

3.分表分库

3.1垂直拆分

垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性

垂直拆分用于分布式场景。

3.2水平拆分

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分

通俗理解：水平拆分行，行数据拆分到不同表中，垂直拆分列，表数据拆分到不同表中

3.3通过取模方式进行分表

可以通过用户ID进行水平分割，不同的用户放在不同的表里。如用户ID通过2取模然后分别存入两张表里

4.SQL优化

问题所在是快速定位执行速度慢的SQL语句上。（定位慢查询到日志中）

4.1show status

使用show status使用show status查看MySQL服务器状态信息

常用命令

--mysql数据库启动了多少时间

show status like 'uptime';

show stauts like 'com_select' show stauts like 'com_insert' ...类推 update delete（显示数据库的查询，更新，添加，删除的次数）

show [session|global] status like .... 如果你不写 [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global)

//显示到mysql数据库的连接数

show status like 'connections ';

//显示慢查询次数

show status like 'slow_queries';

4.2慢查询

4.2.1什么是慢查询

MySQL默认10秒内没有响应SQL结果,则为慢查询

可以去修改MySQL慢查询默认时间

4.2.2如何修改慢查询

--查询慢查询时间 show variables like 'long_query_time'; --修改慢查询时间 set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值

4.3如何定位慢查询

初始化测试数据

创建表结构

 /*部门表*/
CREATE TABLE dept(
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*编号*/
dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名称*/
loc VARCHAR(13) NOT NULL DEFAULT "" /*地点*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
/*员工表*/
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
 
 
 
/*薪水*/
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
/*测试数据*/
 
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

创建函数

create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end
 
create FUNCTION rand_num()
RETURNS int(5)
BEGIN
 DECLARE i int default 0;
 set i =floor(10+RAND()*500);
 return i;
END

创建存储过程

delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
 set autocommit = 0; 
 repeat
 set i = i + 1;
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end $$
执行存储过程
call insert_emp (100001,40000000);

4.4如何将慢查询定位到日志中

在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以

bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在

my.ini 文件中记录的位置

#Path to the database root

datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"