MySQL
MySQL执行流程
客户端向MySQL发起请求(一般使用数据库连接池减少线程的开关)
MySQL Server开始执行
-
连接器: 控制用户的连接
-
分析器: 词法分析, 语法分析
-
优化器: 优化SQL语句, 规定执行流程
- 可以查看SQL语句的执行计划, 可以采用对用的优化点, 来加快查询
-
执行器: SQL语句的实际执行组件
存储引擎: 不同的存放位置, 不同的文件格式
-
InnoDB: 磁盘
- 支持事务, 性能比较好. 具有自动崩溃恢复机制, 支持行锁, 创建数据库时, 默认是innodb
-
MyISAM: 磁盘
- 5.1及老版本默认的, 支持表锁. 不支持事务, 不支持行锁, 崩溃后不能保证安全恢复. 只针对读操作, 表比较小. 表锁导致的性能问题
-
memory: 内存
-
访问速度快, 重启或崩溃数据丢失. 可以当成redis使用, hash索引, 查找单个数据速度快, 支持表锁, 不支持TEXT/BLOG大文本, 并且是固定长度, 比较浪费空间
-
查找单个数据速度快
-
查找单个数据速度快
-
查找单个数据速度快
-
-
-
Archive
- 只能insert和select. 缓存所有写, 并且zlib插入数据库中. 适合日志的采集
-
blackhole
- 不会存储任何的数据, 不会有任何保存. insert into, 表的日志. 备份表(插入日志. 不推荐使用)
-
Ferderated
- 一个远程到mysql服务器的客户端连接(SQL -> Ferderated -> 远程另外一台服务执行): 默认禁掉
打开profile, 查看SQL语句执行流程所耗费的时间
-
set profiling=1;
-
show profile all for query [ID]
-
select * from xxx;
新版本出现更好的分析引擎performance_schema
查看MySQL客户端连接数信息
- show processlist;
schema与数据优化
数据类型的优化
-
更小的通常更好
一般情况下，应该尽量使用可以正确存储数据的最小数据类型 。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
-
简单就好
简单数据类型的操作通常需要更少的CPU周期。
例如，整型比字符操作代价更低，因为字符集和校对规则使字符比较复杂与整型比较。
例如，使用Mysql的內建类型而不是字符串来存储日期和时间。
例如：用整型存储IP地址。-
整数int的类型区分
- tinyint 8bit 1个字符
- smallint 16bit 2个字符
- mediumint 24bit 3个字符
- int 32bit 4个字符
- bigint 64bit 8个字符
-
记录货币使用什么字段类型
-
decimal
decial是精准的货币类型, 但是在存储的时候转成字符串, 但是性能有影响
-
double
不精准
-
bigint
将系统中的货币*100存成整数类型
-
-
谈一谈字符串类型
-
varchar
可变长的字符串, 当存储不知道具体长度时, 使用varchar, 节约空间.
并且额外需要1-2个字节需要记录字符串的长度-
varchar位数的区别 #进行中
varchar(5) 和 varchar(200) 在存储的时候有没有差异?
答: 没有差异, 因为是变长的但是如果varchar(255)大于255以上的时候, 记录长度的会使用2个字节, 剩下的没有差别
55:29
-
-
char
char是定长的, 当存储的字段的值是固定的情况下
-
-
-
尽量避免NULL
如果计划在列上建索引，就应该尽量避免设计成可为NULL 的列。因为一列中, 每一个NULL值都是唯一的(NULL = NULL 结果是false)InnoDB使用单独的一位（bit）存储NULL值，所以对于稀疏数据 有很好的空间效率。并且NULL值很难被优化
合理使用范式和反范式
-
范式
-
优点
-
范式化的更新通常比反范式要快
-
当数据较好的范式化后, 很少或者没有重复的数据
-
范式化的数据比较小, 可以放在内充中, 操作比较快
-
减少数据的冗余
-
-
缺点
- 通常需要进行关联
-
-
反范式
-
优点
-
所有的数据都在同一张表中, 可以避免关联
-
可以设计有效的索引
-
-
缺点
- 表格内的冗余较多, 删除数据时候会造成表有用的信息丢失
-
使用MySQL枚举类型替代字符串: 减少空间占用, 原理是枚举中有对应的字符串, 实际存储是MySQL生成的整形值, 比如("aaa"), 那么aaa在数据库中存储的就可能是1
datetime和timestamp
-
timestamp
-
占用4个字节
-
时间范围:1970-01-01到2038-01-19
-
精确到秒
-
采用整形存储
-
依赖数据库设置的时区
-
自动更新timestamp列的值
-
-
date
-
占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节
-
使用date类型还可以利用日期时间函数进行日期之间的计算
-
date类型用于保存1000-01-O1到9999-12-31之间的日期
-
-
datetime
-
占用8个字节
-
与时区无关，数据库底层时区配置，对datetime无效
-
可保存到毫秒
-
可保存时间范围大
-
不要使用字符串存储日期类型，占用空间大，损失日期类型函数的便捷性
-
文本和字符串类型
-
varchar
-
使用最小的符合需求的长度。
-
varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
-
varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小。
-
varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时时，都会导致锁表。
-
应用场景
-
存储长度波动较大的数据，如:文章，有的会很短有的会很长
-
字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
-
适合保存多字节字符，如:汉字，特殊字符等
-
-
-
char固定长度的字符串
-
最大长度:255
-
会自动删除未尾的空格
-
检索效率、写效率会比varchar高，以空间换时间
-
应用场景
-
存储长度波动不大的数据，如: md5摘要
-
存储短字符串、经常更新的字符串
-
-
通过索引优化
主键的选择
-
代理主键
- 与业务无关的，无意义的数字序列
-
自然主键
- 事物属性中的自然唯一标识
-
推荐使用代理主键
-
它们不与业务耦合，因此更容易维护
-
一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本*
-