数据库mysql调优

最新推荐文章于 2023-08-05 20:00:00 发布

胳膊太细睡不着

最新推荐文章于 2023-08-05 20:00:00 发布

阅读量118

点赞数

分类专栏：不试试怎么知道结果文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/m0_51628881/article/details/129417857

版权

不试试怎么知道结果专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、存储过程

什么是存储过程

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

ps:存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

存储过程有哪些特性:

有输入输出参数，可以声明变量;
有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能;
函数的普遍特性：模块化，封装，代码复用
速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤

创建一个存储过程

create procedure user_porced () 
begin 
    select name from users; 
end;

调用存储过程

call porcedureName ();

传参存储过程

create PROCEDURE user_porcedPa(
in a int(10)
)
BEGIN
 select * from users where age>a;
END;

call user_porcedPa(10);

1. 存储过程优缺点

优点

在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑（或bug），而不用重启服务器。但这一点便利被许多人滥用了。有人直接就在正式服务器上修改存储过程，而没有经过完整的测试，后果非常严重。
执行速度快。存储过程经过编译之后会比单独一条一条执行要快。但这个效率真是没太大影响。如果是要做大数据量的导入、同步，我们可以用其它手段。
减少网络传输。存储过程直接就在数据库服务器上跑，所有的数据访问都在服务器内部进行，不需要传输数据到其它终端。但我们的应付服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。
能够解决presentation与数据之间的差异，说得文艺青年点就是解决OO模型与二维数据持久化之间的阻抗。领域模型和数据模型的设计可能不是同一个人（一个是SA，另一个是DBA），两者的分歧可能会很大——这不奇怪，一个是以OO的思想来设计，一个是结构化的数据来设计，大家互不妥协——你说为了软件的弹性必须这么设计，他说为了效率必须那样设计，为了抹平鸿沟，就用存储过程来做数据存储的逻辑映射（把属性映射到字段）。好吧，已经有同学在叨咕ORM了。
方便DBA优化。所有的SQL集中在一个地方，DBA会很高兴。这一点算是ORM的软肋。不过按照CQRS框架的思想，查询是用存储过程还是ORM，还真不是问题——DBA对数据库的优化，ORM一样会受益。况且放在ORM中还能用二级缓存，有些时候效率还会更高。

缺点

SQL本身是一种结构化查询语言，加上了一些控制（赋值、循环和异常处理等），但不是OO的，本质上还是过程化的，面对复杂的业务逻辑，过程化的处理会很吃力。这一点算致命伤。
不便于调试。基本上没有较好的调试器，很多时候是用print来调试，但用这种方法调试长达数百行的存储过程简直是噩梦。好吧，这一点不算啥，C#/java一样能写出噩梦般的代码。
没办法应用缓存。虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。
无法适应数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。
精通SQL的新手越来越少——不要笑，这是真的，我面试过N多新人，都不知道如何创建全局临时表、不知道having、不知道聚集索引和非聚集索引，更别提游标和提交叉表查询了。好吧，这个缺点算是凑数用的，作为屌丝程序员，我们的口号是：没有不会的，只有不用的。除了少数有语言洁癖的人，我相信精通SQL只是时间问题。

2. 数据库设计: 数据库三大范式

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

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。
第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。没有冗余的数据库设计可以做到。

但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

3. SQL优化定位操作

如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)

show 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. 慢查询

什么是慢查询? -> MySQL默认10秒内没有响应SQL结果,则为慢查询

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

二、索引

创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

三、SQL语句优化技巧

1 . SQL优化技巧

使用group by 分组查询，可能会降低速度。因为group by默认分组后，还会排序，在其后面增加 order by null 就可以防止排序。

explain select * from emp  group by deptno order by null;

有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接，更ok!]

对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null
最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库。
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用 NULL。
不要以为 NULL 不需要空间

char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的
varchar 这样的变长字段， null 不占用空间。
可以在 num 上设置默认值 0，确保表中其列没有 null 值，然后这样查询：select id from t where num = 0

2 . MySQL数据引擎 myisam / innodb/ memory

myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的发帖表，回复表.
INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

MyISAM 和 INNODB的区别

事务安全（MyISAM不支持事务，INNODB支持事务）
查询和添加速度（MyISAM批量插入速度快）
支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
锁机制（MyISAM时表锁，innodb是行锁）
外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)

Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. （如果mysql重启的话，数据就不存在了）
在这里插入图片描述

Myisam注意事项：如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理。
举例说明:
create table test100(id int unsigned ,name varchar(32))engine=myisam;
insert into test100 values(1,’aaaaa’);
insert into test100 values(2,’bbbb’);
insert into test100 values(3,’ccccc’);
我们应该定义对myisam进行整理
optimize table test100;

3 . 数据库数据备份

手动方式

在环境变量中配置mysql环境变量

#cmd控制台:
mysqldump –u -账号 –密码 数据库 [表名1 表名2..]  > 文件路径

-- 案例: mysqldump -u -root root test > d:\temp.sql
#比如: 把temp数据库备份到 d:\temp.bak
mysqldump -u root -proot test > f:\temp.bak
#如果你希望备份是，数据库的某几张表
mysqldump -u root -proot test dept > f:\temp.dept.sql

如何使用备份文件恢复我们的数据

#mysql控制台
source d:\temp.dept.bak

自动方式

把备份数据库的指令，写入到 bat文件,
通过任务管理器去定时调用 bat文件.

# mytask.bat 内容是:
@echo off
F:\path\mysqlanzhuang\bin\mysqldump -u root -proot test dept > f:\temp.dept.sql

创建执行计划任务执行脚本。

四、分库分表

通俗理解：

水平拆分行，行数据拆分到不同表中
垂直拆分列，表数据拆分到不同表中

垂直拆分

垂直拆分就是要把表中字段，按模块划分到不同数据库表中。

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

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

水平拆分

水平拆分就是把一个表按照某种规则把数据划分到不同表或数据库里。

垂直切分没有解决单表大数据量的问题，而水平切分恰好就是把一个表的数据根据不同规则划分为多个表。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分。
具体根据业务需求，有的按照注册时间、取摸、账号规则、年份等。