MySQL 建表的优化策略 小结
2022-11-12 09:17:10
内容摘要
这篇文章主要为大家详细介绍了MySQL 建表的优化策略 小结，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！目录1. 字符集的选择 12. 主键 1
文章正文
这篇文章主要为大家详细介绍了MySQL 建表的优化策略 小结，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
目录1. 字符集的选择 12. 主键 13. 外键 24. 索引 24.1. 以下情况适合于创建索引 24.2. 以下的情况下不适合创建索引 34.3. 联合索引 34.4. 索引长度 45. 特殊字段 45.1. 冗余字段 45.2. 分割字段 45.3. BLOB和CLOB 56. 特殊 56.1. 表格分割 56.2. 使用非事务表类型 51. 字符集的选择如果确认全部是中文，不会使用多语言以及中文无法表示的字符，那么GBK是首选。采用UTF-8编码会占用3个字节，而GBK只需要2个字节。2. 主键尽可能使用长度短的主键系统的自增类型AUTO_INCREMEN, 而不是使用类似uuid()等类型。如果可以使用外键做主键，则更好。比如1：1的关系，使用主表的id作为从表的主键。主键的字段长度需要根据需要指定。tinyint 从 2的7次方-1 ：-128 到 127smallint 从 2的15次方-1 ：-32768 到 32767mediumint 表示为 2的23次方-1： 从 -8388608 到8388607int 表示为 2的31次方-1bigint 表示为 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，agefirstname，lastnamefirstname从另一方面理解，它相当于我们创建了(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表。注：关于MySQL 建表的优化策略 小结的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
