最近一段时间都学习mysql,将重要的知识点总结如下:
一、字段、表、索引设计规范
1、字段设计规范
① 字段类型优先选择符合存储需要的最小类型
字段类型优先级:整型>date;time >enum>char;varchar>blob
原因:整型,time运算快,节省内存;enum列内部是用整型存储的,char,varchar要考虑字符集的转换和排序的校对集,速度慢;blob无法使用临时表。
② 够用就行(如smallint,varchar(N))
原因:大的字段浪费内存,影响速度,如varchar(10),varchar(300),虽然存储的内容一样,但是,在表联查时,varchar(300)要花更多内存
③ 尽量避免使用允许为null()
原因:null不利于索引,要用特殊的字节标注,在磁盘上占的空间其实更大
例子:建两个相同字段的表,一个允许为null,一个不允许。可以发现为null的索引更大些。
④ 避免使用ENUM类型
修改ENUM值需要使用ALTER语句
ENUM类型的ORDER BY操作效率低,需要额外操作
禁止使用数值作为ENUM的枚举值
⑤ 使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间
TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
TIMESTAMP 占用4字节和INT相同,但比INT可读性高
超出TIMESTAMP取值范围的使用DATETIME类型存储
⑥ 金额类数据使用decimal
Decimal类型为精准浮点数,在计算时不会丢失精度
占用空间由定义的宽度决定,每4个字节可以存储9位数字,并且小数点要占用一个字节
可用于存储比bigint更大的整型数据
2、表设计规范
① 定长与变长分离
核心且常用字段,宜建成定长,放在一张表.
而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.
② 常用字段和不常用字段要分离.
需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.
3、索引设计规范
① 限制每张表中索引的数量,建议单张表中的索引不超过5个
索引并不是越多越好!索引可以提高效率同样可以降低效率
因为mysql优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加mysql优化器生成执行计划的时间,同样会降低查询性能
② 禁止给表的每一列都建立单独索引
5.6版本之前,一个sql只能使用到一个表中的一个索引,5.6以后,虽然有了合并索引的优化方式,但是还是远远没有使用一个联合索引的查询方式好
③禁止给表的每一列都建立单独索引
5.6版本之前,一个sql只能使用到一个表中的一个索引,5.6以后,虽然有了合并索引的优化方式,但是还是远远没有使用一个联合索引的查询方式好
④每个Innodb表必须有一个主键
Innodb是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的
每个表都可以有多个索引,但是表的存储顺序只能有一种Innodb是按照主键索引的顺序来组织表的,不要使用更新频繁的列作为主键
不要使用UUID,MD5,HASH,字符串列作为主键(无法保证数据的顺序增长),主键建议使用自增ID值
二、事务
事务保证一组原子性的操作,要么全部成功,要么全部失败。一旦失败,回滚之前的所有操作。MySql采用自动提交,如果不是显式的开启一个事务,则每个查询都作为一个事务。
1、事务的四大特性
①原子性
事务的原子性&#x