MySQL 建表的优化策略 小结_MySQL
程序员文章站
2024-02-19 16:19:16
...
bitsCN.com
目录
1. 字符集的选择 1
2. 主键 1
3. 外键 2
4. 索引 2
4.1. 以下情况适合于创建索引 2
4.2. 以下的情况下不适合创建索引 3
4.3. 联合索引 3
4.4. 索引长度 4
5. 特殊字段 4
5.1. 冗余字段 4
5.2. 分割字段 4
5.3. BLOB和CLOB 5
6. 特殊 5
6.1. 表格分割 5
6.2. 使用非事务表类型 5
1. 字符集的选择
如果确认全部是中文，不会使用多语言以及中文无法表示的字符，那么GBK是首选。
采用UTF-8编码会占用3个字节，而GBK只需要2个字节。
2. 主键
尽可能使用长度短的主键
系统的自增类型AUTO_INCREMEN, 而不是使用类似uuid()等类型。如果可以使用外键做主键，则更好。比如1：1的关系，使用主表的id作为从表的主键。
主键的字段长度需要根据需要指定。
tinyint 从 2的7次方-1 ：-128 到 127
smallint 从 2的15次方-1 ：-32768 到 32767
mediumint 表示为 2的23次方-1： 从 -8388608 到8388607
int 表示为 2的31次方-1
bigint 表示为 2的63次方-1
在主键上无需建单独的索引，因为系统内部为主键建立了聚簇索引。
允许在其它索引上包含主键列。
3. 外键
外键会影响插入和更新性能，对于批量可靠数据的插入，建议先屏蔽外键检查。
对于数据量大的表，建议去掉外键，改由应用程序进行数据完整性检查。
尽可能用选用对应主表的主键作作为外键，避免选择长度很大的主表唯一键作为外键。
外键是默认加上索引的
4. 索引
创建索引，要在适当的表，适当的列创建适当数量的适当索引。在查询优先和更新优先之间做平衡。
4.1. 以下情况适合于创建索引
在经常需要搜索的列上，可以加快搜索的速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
4.2. 以下的情况下不适合创建索引
对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。
如果表数据很少，比如每个省按市做汇总的表，一般低于2000，且数据量基本没有变化。此时增加索引无助于查询性能，却会极大的影响更新性能。
当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当对修改性能的要求远远大于检索性能时，不应该创建索引。
4.3. 联合索引
在特定查询里，联合索引的效果高于多个单一索引，因为当有多个索引可以使用时，MySQL只能使用其中一个。
在查询里，同时用到了联合索引包含的前几个列名，都会使用到联合索引，否则将部分或不会用到。比如我们有一个firstname、 lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用 fname_lname_age索引：
firstname，lastname，age
firstname，lastname
firstname
从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。
4.4. 索引长度
对于CHAR或者Varchar的列，索引可以根据数据的分布情况，用列的一部分参与创建索引。
create index idx_t_main on t_main(name(3));
这里就是指定name的前三个字符参与索引，而不是全部
最大允许的长度为1000个字节，对已GBK编码则是500个汉字
5. 特殊字段
5.1. 冗余字段
就是用空间换取时间。如果大表查询里经常要join某个基础表，且这个数据基本不变，比如人的姓名，城市的名字等。一旦基础表发生变动，则需要更新所有涉及到的冗余表。
5.2. 分割字段
如果经常出现以某个字段的某个局部进行检索和汇总(substring())，可以考虑将这一部分独立出来。
比如统计姓名里，每种姓氏的人数，可以考虑实现就按照姓和名分别保存，而不是一个字段。
还有就是某些上下级结构的实现，也可以考虑将不同的级别放在不同的字段里。
5.3. BLOB和CLOB
此类字段一般数据量很大，建议设计上数据库可以只保存其外部连接，而数据以其它方式保存，比如系统文件。
6. 特殊
6.1. 表格分割
如果一个表有许多的列，但平时参与查询和汇总的列却并不是很多，此时可以考虑将表格拆分成2个表，一个是常用的字段，另一个是很少用到的字段。
6.2. 使用非事务表类型
MySQL支持多种表类型，其中InnoDB类型是支持事物的，而MyISAM类型是不支持的，但MyISAM速度更快。对于某些数据，比如地理行政划分，民族等不可能参与事务的数据，可以考虑用MyISAM类型的表格。
但InnoDB的表，将无法用MyISAM表数据做外键约束了。
MyISAM表参与的事务，其InnoDB表可以正常的提交和回滚，但不影响MyISAM表。bitsCN.com
1. 字符集的选择 1
2. 主键 1
3. 外键 2
4. 索引 2
4.1. 以下情况适合于创建索引 2
4.2. 以下的情况下不适合创建索引 3
4.3. 联合索引 3
4.4. 索引长度 4
5. 特殊字段 4
5.1. 冗余字段 4
5.2. 分割字段 4
5.3. BLOB和CLOB 5
6. 特殊 5
6.1. 表格分割 5
6.2. 使用非事务表类型 5
1. 字符集的选择
如果确认全部是中文，不会使用多语言以及中文无法表示的字符，那么GBK是首选。
采用UTF-8编码会占用3个字节，而GBK只需要2个字节。
2. 主键
尽可能使用长度短的主键
系统的自增类型AUTO_INCREMEN, 而不是使用类似uuid()等类型。如果可以使用外键做主键，则更好。比如1：1的关系，使用主表的id作为从表的主键。
主键的字段长度需要根据需要指定。
tinyint 从 2的7次方-1 ：-128 到 127
smallint 从 2的15次方-1 ：-32768 到 32767
mediumint 表示为 2的23次方-1： 从 -8388608 到8388607
int 表示为 2的31次方-1
bigint 表示为 2的63次方-1
在主键上无需建单独的索引，因为系统内部为主键建立了聚簇索引。
允许在其它索引上包含主键列。
3. 外键
外键会影响插入和更新性能，对于批量可靠数据的插入，建议先屏蔽外键检查。
对于数据量大的表，建议去掉外键，改由应用程序进行数据完整性检查。
尽可能用选用对应主表的主键作作为外键，避免选择长度很大的主表唯一键作为外键。
外键是默认加上索引的
4. 索引
创建索引，要在适当的表，适当的列创建适当数量的适当索引。在查询优先和更新优先之间做平衡。
4.1. 以下情况适合于创建索引
在经常需要搜索的列上，可以加快搜索的速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
4.2. 以下的情况下不适合创建索引
对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。
如果表数据很少，比如每个省按市做汇总的表，一般低于2000，且数据量基本没有变化。此时增加索引无助于查询性能，却会极大的影响更新性能。
当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当对修改性能的要求远远大于检索性能时，不应该创建索引。
4.3. 联合索引
在特定查询里，联合索引的效果高于多个单一索引，因为当有多个索引可以使用时，MySQL只能使用其中一个。
在查询里，同时用到了联合索引包含的前几个列名，都会使用到联合索引，否则将部分或不会用到。比如我们有一个firstname、 lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用 fname_lname_age索引：
firstname，lastname，age
firstname，lastname
firstname
从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。
4.4. 索引长度
对于CHAR或者Varchar的列，索引可以根据数据的分布情况，用列的一部分参与创建索引。
create index idx_t_main on t_main(name(3));
这里就是指定name的前三个字符参与索引，而不是全部
最大允许的长度为1000个字节，对已GBK编码则是500个汉字
5. 特殊字段
5.1. 冗余字段
就是用空间换取时间。如果大表查询里经常要join某个基础表，且这个数据基本不变，比如人的姓名，城市的名字等。一旦基础表发生变动，则需要更新所有涉及到的冗余表。
5.2. 分割字段
如果经常出现以某个字段的某个局部进行检索和汇总(substring())，可以考虑将这一部分独立出来。
比如统计姓名里，每种姓氏的人数，可以考虑实现就按照姓和名分别保存，而不是一个字段。
还有就是某些上下级结构的实现，也可以考虑将不同的级别放在不同的字段里。
5.3. BLOB和CLOB
此类字段一般数据量很大，建议设计上数据库可以只保存其外部连接，而数据以其它方式保存，比如系统文件。
6. 特殊
6.1. 表格分割
如果一个表有许多的列，但平时参与查询和汇总的列却并不是很多，此时可以考虑将表格拆分成2个表，一个是常用的字段，另一个是很少用到的字段。
6.2. 使用非事务表类型
MySQL支持多种表类型，其中InnoDB类型是支持事物的，而MyISAM类型是不支持的，但MyISAM速度更快。对于某些数据，比如地理行政划分，民族等不可能参与事务的数据，可以考虑用MyISAM类型的表格。
但InnoDB的表，将无法用MyISAM表数据做外键约束了。
MyISAM表参与的事务，其InnoDB表可以正常的提交和回滚，但不影响MyISAM表。bitsCN.com