Mysql优化总结(一)

  • 2019 年 10 月 3 日
  • 笔记

一,前言

​ 数据库(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空间效率更高。

五,总结

​ 下面将总结索引方面的优化,包括B-Tree和B+Tree两种数据结构的原理,由于放在一起篇幅较长,所以就分成两个博客,这些内容会在下一篇博客中进行详细总结。

​ 以上内容均是自主学习总结,如有不适之处,欢迎留言指正。

感谢阅读!