MySQL重要知识点（总结）

最近一段时间都学习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