Mysql优化总结(一)

未结 0 2949
VIP1 2019年09月20日
悬赏:20积分

一,前言
​ 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。

​ MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

MySQL是开源的,所以不需要支付任何费用。
MySQL使用标准的SQL数据语言形式。
MySQL可以处理拥有千万条记录的大型数据库。
MySQL支持多种语言,如C,PHP,Java等。
  本篇博客是总结MySQL存储机制及SQL优化方面的内容。

二,MySQL逻辑架构


​ 分析:

第一层是大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。如连接处理,授权认证等。
第二层架构中,大多数的MYSQL的核心服务功能都在这一层,包括查询解析、分析、优化、缓存以及所有的内置函数(日期时间等),所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等层。
第三层包含了存储引擎。存储引擎负责Mysql中的数据的存储和提取。
Mysql支持各种不同的存储引擎,每个存储引擎都有它的优势和劣势。服务器通过API和存储引擎进行通信。这些API接口屏蔽了不同存储引擎之间的差异,使的这些差异对上层的查询过程透明。存储引擎不会解析SQL(InnoDB是个例外,它会解析外键定义),不同的存储引擎之间也不能互相通信,而只是简单的响应上层服务器的请求。
​ 缓存:当客户端请求到来时,先去查询缓存中是否存在数据,没有再去数据库中查询。

分析器:对sql语句的分析,比如SELECT username FROM users; ,分析器分析出先执行FROM users,然后再执行SELECT username。
​ 优化器:上面只是举了一个简单的例子,而优化器作用在于选择那种执行方式,是执行SQL效率最快的。因为分析器对一条SQL语句会分析出多种不同的结果,而优化器就会对以上的分析结果选择它认为最优的结果来执行。但是,需要注意的是,优化器认为最优的执行方案,并不一定是我们认为的最优执行方案。因此再SQL语句量较大的情况下,就需要程序员手动对语句进行优化。

​ 下面再来说说MySQL的存储引擎。

三,存储引擎
​ 以博主的MySQL作为案例,执行select version();语句可以查看数据库版本。



​ 接着再来看看MySQL都支持哪些存储引擎。



​ 在倒数第二个,可以看出MySQL默认的存储引擎是InnoDB,再细看Transactions中只有InnoDB是支持事务的,其他存储引擎都是不支持的。这是在MySQL5.5之后支持的引擎,所以说在5.5之前MySQL是不支持事务的。

​ 最长用的两种存储引擎为InnoDB和Myisam,请看两者之间的区别(MySQL5.6)。

特点 InnoDB myisam
事务 支持事务 不支持
锁 行锁 表锁
B树索引 支持 支持
全文索引 不支持 支持
外键 支持(唯一支持的) 不支持
​ 那么如何选择两种存储引擎,可以从以下几个方面考虑。

​ 事务:

​ 如果应用需要事务支持,那么InnoDB是较号的选择。如果不需要事务,并且主要是select和insert操作,则MyISAM是个不错的选择,比如日志型系统。

​ 备份:

​ 备份的需求也会影响存储引擎的选择。如果需要在线热备份,则选择InnoDB就是基本的要求。

​ 数据恢复:

​ 数据量比较大的时候,系统崩溃后如何快速恢复是一个需要考虑的额问题。相对而言,MyISAM崩溃后发生损坏的概率比InnoDB高的多,而且恢复也慢,因此即使不需要事务,通常也应该选择InnoDB引擎。

​ 对于不同存储引擎的选择,对数据库的性能都会带来较大的影响,除了MySQL默认支持的存储引擎之外,还有很多第三方存储引擎。

​ 通常情况下,导致SQL性能下降还有以下几方面:

查询语句编写不合理
索引失效
关联查询数据太多
服务器的参数设置问题
下面总结一些关于SQL优化方面的内容。

四,数据类型优化
4.1,数据类型的选择原则。
​ 更小的通常更好:

​ 一般情况选择可以正确存储数据的最小数据类型。因为更小的数据类型通常更快,因为占用磁盘、内存和CPU缓存会越小。

​ 简单就好:

​ 简单数据类型的操作通常需要更少的CPU消耗。

​ 尽量避免NULL:

​ 一般情况下最好执行列为NOT NULL,除非特殊需要。因为如果查询中包含为NULL的列,从SQL优化角度考虑,对创建索引,索引的统计等都会带来较大的困难,且优化起来也是较为复杂的,因此说尽量避免NULL值。

4.2,具体类型的选择
​ 1,整数类型:选择合适的字节大小作为数据的类型。

tinyint:8位字节
smallint:16位字节
mediumint:24位字节
int:32位字节
bigint:64位字节
unsigned属性:添加unsigned属性表示该字段不允许负数,正数的上限大致可以提高一倍。
比如tinyint unsigned可以存储0-255的范围。而tinyint是-128~127的范围。有符号和无符号占用空间大小相同,具有相同的性能。
int(11)是指定整数类型的宽度,它不会限制值得合法范围,对于存储和计算而言,int(1)和int(20)没什么区别

​ 2,实数类型

float:32位
double:64位
decimal:decimel需要额外的空间和计算开销,所以应该尽量只对小数进行精准计算时才使用decimel,例如存储金额。
​ 3,字符串类型

varchar:是可变长的字符串,它比定长更节省空间。varchar需要使用1~2个额外字节记录字符串的长度。varchar节省了存储空间,所以对性能也有帮助,
但是由于长度可变,在update时可能使行变得比原来更长,这就导致需要进行额外的工作。至于如何进行空间增长取决于不同的存储引擎。当字符串列的最大长度比平均长度要大很多,并且列的更新很少时比较适合使用varchar。
char:定长字符串,mysql根据定义的字符串长度分配足够的空间。
char非常适合存储很短的字符串,或者值得长度都很接近的字段。例如char非常适合存放密码的md5值,因为这是一个定长的值。对于经常变更的字段,使用char也更为合适,因为定长的char类型不容易产生碎片。对于非常短的列,存储空间也更有优势,比如char(1)只会占用一个字节,而varchar(1)会用到两个字节,因为还有一个字节用来记录varchar的长度。
blob和text:两者都是用于存储很大的数据而设计的字符串数据类型,
分别采用二进制和字符的方式存储。
varchar(5)和varchar(200)存储'hello'的空间开销是一样的。但是varchar(5)对性能提升有很大的优势。更长的列会消耗更多的内存,因为mysql通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序等操作时会特别糟糕。所以最好的策略是只分配真正需要的空间

​ 4,时间和日期类型

datetime:能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到YYYYMMDDHHMMSS的整数中,使用8个字节的存储空间 。
timestamp:保存了从1970年1月1日以来的毫秒数,timestamp只使用了4个字节的存储空间,因此它的范围比datetime小的多;但是只能表示从1970年到2038年。另外timestamp也依赖于时区。
除了特殊行为之外,通常应该尽量使用timestamp,
因为它比datetime空间效率更高。

回帖
  • 还没有人回复
本周热议
没有相关数据
layui

微信扫码关注 网盟开源 公众号