mysql数据库性能优化详解,mysql数据库设计和优化

　　Yyds干货库存

　　数据类型优化首先，让我们介绍一下这个模式：

　　Schema(读作“skee-muh”或“skee-mah”，中文称为模式)是数据库的组织和结构。

　　mysql支持的数据类型很多，所以选择合适的数据类型对于高性能非常重要。不管存储的是什么类型的数据，这里有一些你需要记住的简单原则。

　　较小的通常更好更简单。尽量避免空整数类型。我们有以下整数类型：

　　TINYINT

　　短整型

　　中等大小整数

　　（同Internationalorganizations）国际组织

　　大整数

　　8位

　　16位

　　24位

　　32位

　　64位

　　它们的存储空间范围从-2(N-1)到2(N-1)减一。n是数字的个数

　　integer类型有一个可选的unsigned属性，这意味着不允许负值，这大约可以使正数的上限增加一倍。

　　实数，实数，是有小数部分的数。然而，它不仅仅是为了存储小数部分；也可以使用decimal来存储大于bigint的整数。

　　Decimal(18，9)小数点两边存储9位数，一个用9个字节；小数点前的数用4个字节，小数点后的数用4个字节，小数点本身占一个字节。

　　由于额外的空间和计算开销，我们应该尽量只在精确计算小数的时候使用decimal——比如存储财务数据，但是当数据量很大的时候，我们可以用bigint代替decimal，根据小数的位数将存储的数据乘以相应的倍数。

　　varchar字符串类型

　　用于存储可变长度的字符串。如果MySQL是用ROW_FORMAT=FIXED创建的，那么每一行都会使用定长存储，这样会浪费空间。

　　Varchar需要使用1或2个额外的字节来记录字符串的长度。例如，varchar(10)的列需要11个字节的存储空间。

　　但是，由于行更长，更新可能会使行比原始行更长，从而导致额外的工作。例如，Myisam会将行分割成不同的片段并存储它们，而innodb需要分割页面，以便行能够适合页面。

　　茶

　　存储字符值时，字符类型是固定长度的。Mysql将删除所有尾随空格。

　　Char适合存储非常短的字符串，或者所有的值都接近相同的长度。示例：char非常适合存储密码的md5值。

　　对于频繁变化的数据，char也比varchar好，因为定长char类型不容易出现碎片。

　　和blobtext。

　　两者都是为存储大数据而设计的字符串数据类型，分别以二进制和字符方式存储。

　　MySQL将每个blob和文本值视为独立的对象。存储引擎在存储时通常会做特殊处理。当blob和text值过大时，innodb会使用一个特殊的外部存储区域进行存储。此时，每个值需要1-4个字节来存储行中的指针，然后实际值将被存储在外部存储区中。

　　BLOBTEXT和BlobTEXT家族唯一的区别是Blob类型存储二进制数据没有排序规则或字符集，而Text类型有字符集和排序规则。

　　使用枚举而不是字符串类型。

　　有时，您可以使用枚举列来代替常见的字符串类型。枚举可以将一些不重复的字符串存储到预定义的集合中。Mysql在存储枚举时非常紧凑，会根据列表值的个数压缩成一两个字节。Mysql内部将列表中每个值的位置保存为整数，并将“数字-字符串”映射关系的查找表保存在。表的frm文件。

　　让我们看看下面的例子：

　　创建表enum_test(

　　e枚举(鱼，苹果，狗)不为空

　　);

　　插入enum_test(e)值( fish )，( dog )，( apple )；从enum_test中选择e 0；

　　从enum_test中选择e；

　　所以用数字作为enum枚举常数，这种二元性很容易导致混淆，比如enum (1 ， 2 ， 3 )。所以尽量不要这样用。

　　另一件令人惊讶的事情是：

　　枚举字段按内部存储的整数而不是定义的字符串排序。

　　枚举最糟糕的是字符串列表是固定的，必须使用ALTER TABLE来添加或删除字符串。因此，对于一系列将来可能会改变的字符串，枚举并不是一个好主意。

　　和日期时间类型日期时间。

　　这种类型可以保存大范围的值，从1001到9999，精度为秒。它将日期和时间封装成YYYYMMDDHHMMSS格式的整数，与时区无关。使用8字节的存储空间。

　　TIMRSTAMP

　　就像它的名字一样，时间戳类型保存自1970年1月1日午夜(GMT)以来的秒数。它与unix时间戳相同。时间戳值使用4个字节的存储空间，因此它的范围比datetime小得多。只能说明从1970年到2038年，MySQL提供了FROM_UnixTIME()函数将Unix时间戳转换为date，提供了Unix_TIMESTAMP()函数将date转换为Unix时间戳。

　　位数据类型位

　　将mysql bit视为字符串类型，而不是数字类型。当检索bit(1)的值时，结果是包含二进制0或1的字符串，而不是ascii代码的0，1。

　　设置

　　如果需要保存很多真/假值，可以考虑将这些列合并成一个集合数据类型，在mysql中用一系列打包的位来表示。这样可以有效利用空间，MySQL有FIND_IN_SET()和FIELD()这样的函数，方便在查询中使用。

　　它的主要缺点是更改列定义的成本很高：需要alter TABLE，对于大型表来说，这是一个非常昂贵的操作。

　　选择标识符特殊类型数据的MySQL模式设计中缺陷过多的列。

　　MySQL的存储引擎API工作时，需要在服务器层和存储引擎层之间复制行缓冲格式的数据，然后在服务器层将缓冲的内容解码成列。将编码列从行缓冲区转换为行数据结构的成本非常高。转换的成本取决于列的数量。当我们研究一个CPU使用率非常高的案例时，我们发现客户使用了一个非常宽的表，但实际上只会使用少量的列，此时转换的成本非常高。

　　MySQL将每个关联操作限制在最多61个表，这是一个粗略的体验。如果您希望查询快速执行并具有良好的并发性，那么最好将单个查询关联到12个表中。

　　全能列举

　　注意放置过度使用的枚举

　　不要列举所有的数字，这是不礼貌的。

　　变相枚举

　　枚举允许将一组定义值中的单个值存储在一个列中，而set column允许将一组定义值中的一个或多个值存储在一个列中。

　　例如

　　创建表格.（

　　is_default set(Y ， N) NOT NULL default N

　　)这里需要注意的是，这种真与假的情况不会同时出现，所以无疑要用枚举来代替这个集合。

　　这不是本发明

　　我们以前写过避免null的好处，并建议应尽可能考虑替代方案。比如我们可以用0或者一些特殊字符代替null。

　　但是遵循这个原则，不要走极端。当您真正需要表示未知值时，不要害怕使用null。

　　范式与反范式范式：

　　范式是符合特定级别的关系模式的集合。关系数据库中的关系必须满足一定的要求，不同的范式满足不同的要求。

　　第一范式(1NF)

　　在任何关系数据库中，第一范式(1NF) [2]是对关系模式的基本要求，不满足第一范式(1NF)的数据库不是关系数据库。

　　第二范式(2NF)

　　它建立在第一范式(1NF)的基础上，即要满足第二范式(2NF)，必须先满足第一范式(1NF)。第二种范式(2NF)要求数据库表中的每个[实例]或行必须是唯一可区分的。为了进行区分，通常需要在表中添加一列来存储每个实例的唯一标识。这个唯一的属性列称为[主键]或主键，主键。

　　范例的优点和缺点优点：

　　规范化更新比反规范化更新快。当数据被很好地规范化时，只有很少或更少的重复数据，因此只需要修改更少的数据。规范化的表通常更小，可以更好地存储在内存中，所以操作会更快。较少的重复数据意味着我们将在select中使用较少的distinct或group by语句。缺点：

　　需要关联反规范化的优点和缺点。反规范化模式很好地避免了相关性，因为所有数据都在一个表中。

　　混合规范化和反规范化反规范化数据的最常见方法是复制或缓存，并将相同的特定列存储在不同的表中。我们还可以使用触发器来更新缓存值，这使得实现这样的方案更加容易。

　　缓存表和汇总表有时，提高性能的最佳方法是将派生的冗余数据存储在同一个表中。但是，有时需要创建一个完全独立的汇总表或缓存表。

　　我们使用术语缓存表来表示存储数据的表，这些数据可以相对简单地从模式中的其他表获得。另一方面，术语“汇总表”是使用group by语句聚合数据的表。

　　使用汇总表比扫描表中的所有行要有效得多。

　　相反，缓存对于优化搜索和检索查询语句是有效的。这些查询语句通常需要特殊的表和索引结构。例如，可能需要许多不同的索引组合来加速各种类型的查询。这些矛盾的需求有时需要创建一个只包含主表中间列的缓存表。有用的伎俩是我？您可以使用不同的存储引擎。比如主表使用innodb，我们是否可以使用myisam作为缓存表的引擎，这样会产生更小的索引空间和全文搜索？

　　使用缓存表和汇总表时，我必须决定是实时维护数据还是定期重建数据吗？这更取决于应用程序，但是定期的重建不仅仅是为了节省资源，也是为了保持表中没有那么多片段和索引，而且是按照完整的顺序组织的。

　　当然，为了安全起见，我们在重建这些表的时候也会使用一个影子表，保证数据在运算过程中也可以使用。

　　物化视图counter table counter table是一个经常使用的东西，我们可以使用单独的表来帮助避免查询缓存失效。

　　让我们向您展示一些更先进的技术：

　　比如我们有一个counter表，记录了这个网站的点击次数，但是每次修改的时候都会有一个全局互斥，导致这些事务只能串行执行。如果我们想要更好的性能，我们可以将计数器保存在多行中，并且一次随机选择一行来更新它。我们像这样更新这个计数表：

　　创建表hit_counter(

　　slot tinyint无符号非空主键，

　　无符号整数不为空

　　)ENGINE=InnoDB我们提前在表中添加100行数据，选择一个随机槽进行更新：

　　更新hit_counter SET cnt=cnt 1其中slot=RAND()* 100；为了统计结果，我们使用以下聚合查询：

　　从hit_counter中选择SUM(cnt );我们的一个共同要求是每隔一段时间启动一个新的计数器。我们像这样修改表格：

　　创建表daily_hit_counter(

　　日日期不为空，

　　槽tinyint unsigned not null，

　　cnt int unsigned不为空，

　　主键(日、时段)

　　)ENGINE=InnoDB这样，我们就不用去老师的行程，而是使用on duplicate key update语句(存在就更新，不存在就插入)。

　　插入每日命中计数器(日、槽、计数)

　　值(当前日期，RAND()*100，1)

　　在重复键更新时，CNT=CNT 1；如果希望减少表中的行数，避免表变得太大，可以编写一个在一个周期中执行的任务，将所有结果合并到槽0中，并删除所有其他槽：

　　更新daily_hit_counter为c

　　内部联接(

　　选择日，总和(计数)作为计数，最小(时隙)作为最大时隙

　　来自每日点击率计数器

　　按天分组

　　)作为x使用(天)

　　设置c.cnt=IF(c.slot=x.mslot，x.slot，0)，

　　c.slot=IF (c.slot=x.mslot，0，c . slot)；从daily_hit_counter中删除，其中槽0和计数=0；加速alter TABLE操作MySQL一直是大表alter TABLE的一大难题。Mysql通过创建一个具有新结构的空表，然后将旧表中的数据插入到新表中，来执行修改表结构的大部分操作。

　　对于常见场景，只有两种场景可以使用：

　　首先，在不提供服务的机器上执行ALTER TABLE操作，然后与提供服务的主库切换shadow copy:用所需的与源表无关的表结构创建一个新表，然后通过重命名和删除表来交换两个表。并非所有的alter TABLE操作都会导致表重建。例如，有两种方法可以更改或删除列的默认值(一种是快速的，另一种是慢速的)。

　　慢吞吞道：

　　更改表格sakila.film

　　修改列rental _ duration TINYINT(3)NOT NULL默认为5；这种方法很慢，因为modify方法会导致表的重建。

　　更改表格sakila.film

　　ALTER COLUMN rental _ duration SET DEFAULT 5；这个alter方法非常快，因为它直接修改。不涉及表格数据的固定文件。所以这个操作速度极快。

　　版权归作者所有：原创作品来自博主小二上九8，转载请联系作者取得转载授权，否则将追究法律责任。

郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。