一, 前言
数据库 (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 两种数据结构的原理, 由于放在一起篇幅较长, 所以就分成两个博客, 这些内容会在下一篇博客中进行详细总结.
以上内容均是自主学习总结, 如有不适之处, 欢迎留言指正.
来源: https://www.cnblogs.com/fenjyang/p/11552412.html