Mysql优化

Wesley13
• 阅读 753

一、存储过程

1.1、什么是存储过程

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

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

1.2、存储过程有哪些特性

  有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;

  函数的普遍特性:模块化,封装,代码复用;

  速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;

1.3、创建存储过程

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as

  porcedureName

1.4、调用存储过程

call porcedureName ();

1.5、存储过程优缺点

  优点:

    1.在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器。但这一点便利被许多人滥用,在正式服务器上修改存储过程,而没有经过完整的测试,后果非常严重。

    2.执行速度快。存储过程经过编译之后会比单独一条一条执行要快。但这个效率真是没太大影响。如果是要做大数据量的导入、同步,我们可以用其它手段。

    3.减少网络传输。存储过程直接就在数据库服务器上跑,所有的数据访问都在服务器内部进行,不需要传输数据到其它终端。但我们的应付服务器通常与数据库是在同一内网,大数据的访问的瓶颈会是硬盘的速度,而不是网速。

    4.能够解决presentation与数据之间的差异。

    5.方便DBA优化。所有的SQL集中在一个地方,DBA会很高兴。这一点算是ORM的软肋。不过按照CQRS框架的思想,查询是用存储过程还是ORM,还真不是问题——DBA对数据库的优化,ORM一样会受益。况且放在ORM中还能用二级缓存,有些时候效率还会更高。

  缺点:

    1.SQL本身是一种结构化查询语言,加上了一些控制(赋值、循环和异常处理等),但不是OO的,本质上还是过程化的,面对复杂的业务逻辑,过程化的处理会很吃力。

    2.不便于调试。基本上没有较好的调试器,很多时候是用print来调试。

    3.没办法应用缓存。虽然有全局临时表之类的方法可以做缓存,但同样加重了数据库的负担。如果缓存并发严重,经常要加锁,那效率实在堪忧。

    4.无法适应数据库的切割(水平或垂直切割)。数据库切割之后,存储过程并不清楚数据存储在哪个数据库中。

二、MYSQL如何优化

  1、表的设计合理化(符合3NF(3大范式))

  2、添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

3、SQL语句优化

  4、分表技术(水平分割、垂直分割)

  5、读写[写: update/delete/add]分离

  6、存储过程 [模块化编程,可以提高速度]

  7、对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]

8、mysql服务器硬件升级

  9、定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

2.1、数据库设计

  数据库三大范式:

    第一范式:1NF是对属性的原子性约束,要求属性(列)具有原子性,不可再分解;(只要是关系型数据库都满足1NF)

    第二范式:2NF是对记录的惟一性约束,表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现,主键不能包含业务逻辑。

    第三范式:3NF是对字段冗余性的约束,它要求字段没有冗余。 没有冗余的数据库设计可以做到。

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

2.2、SQL优化

  使用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';

2.3、慢查询

  MySQL默认10秒内没有响应SQL结果,则为慢查询,可以去修改MySQL慢查询默认时间

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

2.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/"

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这