构建优化之城：MySQL 数据建模、数据类型优化与索引常识全面解析_mysql从数据模型优化

前言

MySQL 调优是必备的技能，从系统层面来看，MySQL 基于磁盘交互，是它的瓶颈所在，大量依赖于可靠性、持久化操作，都需要经过 DB，基于它能有调优能力，能最大化提升自己的竞争力；该博文会从以下几方面来对 MySQL 调优作系统的介绍

1. 数据建模方案及数据类型优化
2. 索引优化
3. 大数据量查询优化
4. 海量数据解耦优化处理

数据建模方案、数据类型优化

存储引擎选择

存储引擎的选择是经常会被忽视的问题，因为在绝大部分场景下，都不需要指定存储引擎，直接会使用 MySQL 默认的存储引擎：InnoDB

若不作任何设置的话，默认的存储引擎就是 InnoDB，但也可以通过修改配置文件的方式进行修改操作，如：在 /etc/my.cnf 文件追加内容

[mysqld]
default-storage-engine=InnoDB
1
2

可以通过以上配置写上存储引擎类型，MySQL 支持的存储引擎如下：

之前在介绍 performance_schema 时，它本身就是一种存储引擎，但是在日常工作中 InnoDB、MyISAM 用的最多，它们两者的区别如下：

1. 索引类型：数据、索引文件放在一起的叫做聚簇索引，数据、索引文件不放在一起的叫做非聚簇索引，InnoDB 存储引擎中既有聚簇索引也有非聚簇索引，而 MyISAM 只有非聚簇索引
2. 支持事务：MyISAM 不支持事务，InnoDB 支持事务
3. 支持表、行锁：MyISAM 默认情况下只支持表锁、不支持行锁，但 InnoDB 它既支持表锁也支持行锁，这其中就牵扯到一个锁粒度问题，在进行加锁控制时，肯定是粒度越小越好；粒度越小，锁定的数据范围越小，即并行度越高，效率越高。所以一般在实际代码开发中，进行事务操作时，会把大事务以编程式事务的方式去进行处理，能够精确控制，小事务使用声明式事务处理即可.
4. 支持外键：MyISAM 不支持外键，InnoDB 支持外键
5. 支持全文索引：MyISAM 支持，InnoDB 5.6 版本之后才支持，全文索引很好理解，举例说明如下：

假设，数据库中有一张文章表，里面存在一个列 content，代表内容的意思，若在当前表里需要检索出包含 java 关键字的文章，此时可以利用全文索引的方式来进行分词操作，但是在企业里面一般情况下不会使用全文索引处理，当涉及到分词操作时会选择使用 ES.

6. 适合操作类型：MyISAM 支持大量查询操作， InnoDB 适合操作大量 Insert、Update、Delete 操作

合理使用范式、反范式

范式：会遵循第三范式原则设计表结构，不会产生多的冗余列，即都会进行 Join 表查询；反范式：在表中新增冗余列避免多查一张表，现在的主流设计都是 范式+反范式 设计结合使用

范式优点如下：

1. 范式化的更新通常比反范式更快
2. 当数据模型较好的范式化后，很少或没有重复的数据
3. 范式化的数据比较小，可以放在内存中，操作比较快

范式的缺点：必须要进行表关联查询

反范式优点如下：

1. 所有的数据都在同一张表中，可以避免关联
2. 可以设计有效的索引

当然反范式也有缺点，表内冗余的字段较多，删除数据时会造成表有些有用的信息丢失

总结：在企业中为了能够更好的提高整个项目的运行效率，一般情况下范式、反范式是要配合使用的，或者说是混合使用的

1. 在一个网站实例中，网站允许用户发送消息，并且一些用户是付费用户；若想要查看付费用户最近 10 条信息，在 user、message 表中都存储在用户类型（account_type）而不用完全的反范式化；这避免了完全反范式化的插入、删除问题，因为即使没有消息时也不会丢失用户的信息；这样也不会把 user_message 表搞太大，有利于高效地获取数据
2. 从父表冗余一些字段数据到子表的理由是排序的需要
3. 若缓存衍生值也是有用的，若需要显示每个用户发了多少消息（类似论坛的）可以每次执行一个昂贵的子查询来计算并显示它，也可以在 user 表建一个 num_message 字段，当用户发送消息时更新这个值即可.

字符集选择

一般在设置字符集时都是默认给它指定 utf-8，若你要存储中文时，不建议设置成 utf-8，因为中文支持的字节数可能是 2 个、3 个、4 个，而 utf-8 只支持 3 个字节存储，因此在进行插入 emoji 表情，如： 本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】