Mysql优化--存储层

winterSky 2018-08-28 AM 1122℃ 0条

1. 优化的方面
① 存储层:数据表”存储引擎”选取、字段类型选取、逆范式(3范式)
② 设计层:索引、分区/分表
③ 架构层:分布式部署(主从模式/共享)
④ sql语句层:结果一样的情况下,要选择效率高、速度快、节省资源的sql语句执行


2. 存储引擎
myisam
①mysiam存储引擎数据表,每个数据表都有三个文件.frm(结构文件) .MYD(数据文件) *.MYI(索引文件)
② 数据存入的顺序
给数据表写入数据,主键id值有大、有小,没有固定顺序:
数据写入时候,没有按照主键id值给予排序存储,该特点导致数据写入的速度非常快。
该特点决定myisam的使用场合是:dedecms(content manage system)新闻信息系统/phpcms/discuz/微博系统等等。这些系统常用操作就是“写入/查看”。
③ 并发性
mysiam的并发性较比innodb要稍逊色
因为数据表是“表锁”
④ 压缩机制
如果一个myisam数据表存储的数据非常多,就会占据很大的硬盘空间,为了节省空间,可以把这个myisam数据表给进行压缩处理。
压缩后的数据表需要根据最新的数据位置把索引重新建立一次。

根据压缩后的数据把索引重建建立起来。
重建索引的工具:myisamchk.exe  -rq  表名

只读特性

压缩的数据表不能再写入数据了,必须解压后才可以。
具体解压:myisamchk.exe  --unpack  表名 
(解压缩的同时,索引会自动重建)

innodb

①innodb存储引擎 每个数据表有单独的“结构文件” *.frm
默认情况下,所有数据表的 索引/数据 共享一个文件 ibdata1
② 单独设置数据/索引”文件
要为每个innodb数据表单独设置其“数据/索引”文件
最后每个innodb数据表形成的两种格式文件:.frm 数据/索引文件.ibd
③ 数据存入顺序
该innodb数据表,数据的写入顺序 与 存储的顺序不一致,需要按照主键的顺序把记录摆放到对应的位置上去,速度比Myisam的要稍慢。
④ 并发性
并发性高,多人同时请求,速度快、效率高。
锁机制:行锁,每次只锁住一条记录信息。
⑤ myisam和innodb的取舍
myisam: 写入数据非常快,适合使用场合dedecms/phpcms/discuz/微博系统等写入、读取操作多的系统。
innodb: 适合业务逻辑比较强的系统,修改 操作较多的,例如ecshop、crm、办公系统、商城系统


3. 字段选取
①选取占据空间小的字段
②内容长度固定字段
char() 的运行速度快 ,例如char(20) 实际存储16个字符,分配20个空间
varchar()运行速度少慢 ,例如varchar(20) 实际存储16个字符,分配16个空间
char()运行速度快的原因:
char类型不给计算内容长度,把所有空间都进行分配。
varchar类型给计算内容长度,根据内容长度适当分配空间,空间有节省。
③整型存储
在mysql中最好把数据变为“整型”进行存储,这样占据空间小、运行速度快。

把时间信息都变为整型的进行存储。

集合(多选):set(‘篮球’,’排球’,’足球’,’棒球’) 篮球,足球,棒球
枚举(单选):enum(‘男’,’女’,’保密’)
建议使用集合、枚举类型,他们底层内部使用的整型进行存储

ip地址也可以转换为整型存储。
mysql: inet_aton(ip) inet_ntoa(数字)
php: ip2long(ip) long2ip(数字)


总结
1.存储引擎
存储数据格式,不同存储引擎有自己特点

mysiam
文件:每个数据表都有三个文件(结构、索引、数据)
写入存储数据顺序:存储的顺序与写入顺序一致
并发性:锁机制为“表锁”, 并发性不高
压缩处理:不频繁发生变化的数据适合做压缩

innodb
文件:结构有独立文件,数据与索引合并为一个文件
写入存储数据顺序:根据主键id值的顺序进行数据存储
并发性:并发性好,“行锁”

2.字段类型选取
a)占据空间小的
b)最好char类型(相比varchar)
c)数据变为整型存储


三范式
① 字段的原子性,是唯一的,不能再分隔

② 每一行都能被唯一的区分,强调一个表要有主键
③ 与数据库的冗余有关。一个表不能包含其他表的非关键字信息。也就是说你有其他表的主键作为自己的外键,不能再拿人家的其他字段

标签: none

非特殊说明,本博所有文章均为博主原创。

评论啦~